Re: Increment at counter

Użytkownik "Allan Martin" <allan.martin_at_saic.com> napisał w wiadomości news:3bd6837c_at_cpns1.saic.com...
> Hi,
> I'm trying to insert into a number variable the amount of
> records that I insert into a table. ie. I want to increment the counter by
> 1 every time a row is inserted. I'd then display the amount at the end.
>
> I have tried this over and over again and have even bought a book from
> AMAZON to help me in the future.
>
> Does anyone know what to do as my book will take more that 20 days to
arrive
> from
> America?
>
> I've got what I had in mind below.
>
> Thanks a lot,
> Allan
>
> DECLARE
>
> v_counter NUMBER := 0;
>
> BEGIN
>
> insert into tbl1
> (v1,
> v2,
> v3)
> select distinct
> v1,
> v2,
> v3
> -- v_counter = v_counter + 1;
> from theTable;
>
> commit;
>
> dbms_output.put_line('1. || v_counter || ' records inserted.');
>
> END;
> /

There is my solution: it works on delete, update as well.

Example:

Lets say i got table test. (no constraints on thiz table)

----sql code begin...

BEGIN

• rewrite all rows into same table

 INSERT INTO TEST
  SELECT * FROM TEST;

• How many rows proceed?

 Dbms_Output.Put_Line(SQL%rowcount||' rows inserted.');

END;
/

--sql code ends.

don't forget to put
 SET SERVEROUTPUT ON. Received on Thu Oct 25 2001 - 03:51:47 CDT