Oracle PL/SQL/Cursor/Cursor ROWCOUNT

Материал из SQL эксперт
Перейти к: навигация, поиск

Check SQL%ROWCOUNT after update statement

   <source lang="sql">
 

SQL> SQL> CREATE TABLE place (

 2    room_id          NUMBER(5) PRIMARY KEY,
 3    building         VARCHAR2(15),
 4    room_number      NUMBER(4),
 5    number_seats     NUMBER(4),
 6    description      VARCHAR2(50)
 7    );

Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20003, "Building 6", 150, 50, "Discussion Room A");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20004, "Building 6", 160, 50, "Discussion Room B");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20005, "Building 6", 170, 50, "Discussion Room C");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20006, "Music Building", 100, 10, "Music Practice Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20007, "Music Building", 200, 1000, "Concert Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20008, "Building 7", 300, 75, "Discussion Room D");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20009, "Building 7", 310, 50, "Discussion Room E");

1 row created. SQL> SQL> SQL> BEGIN

 2    UPDATE place
 3      SET number_seats = 100
 4      WHERE room_id = 99980;
 5    IF SQL%ROWCOUNT = 0 THEN
 6      INSERT INTO place (room_id, number_seats)
 7        VALUES (99980, 100);
 8    END IF;
 9  END;
10  /

PL/SQL procedure successfully completed. SQL> SQL> drop table place; Table dropped. SQL>

 </source>
   
  


Cursor attributes: FOUND NOTFOUND, ROWCOUNT

   <source lang="sql">
 

SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6)
10  );

Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    14-JUN-98 MGR                                        4      25000       1005
      1005 Viper                                              sdillon@a .ru                 11-JUN-08 PROG                                       1      20000       1006
      1006 Beck                                               clbeck@g.ru                   11-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   11-JUN-08 PROG                                       3      20000       1006
      1008 Oracle                                             oracle1@g.ru                  11-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> SQL> SQL> declare

 2      cursor emps
 3      is select *
 4           from employees
 5          where rownum < 6
 6          order by 1;
 7
 8      emp employees%rowtype;
 9      row number := 1;
10    begin
11      open emps;
12      fetch emps into emp;
13
14      loop
15        if emps%FOUND then
16          dbms_output.put_line("Looping over record " ||row|| " of " ||
17                               emps%ROWCOUNT);
18          fetch emps into emp;
19          row := row + 1;
20        elsif emps%NOTFOUND then
21          exit;
22        end if;
23      end loop;
24
25      if emps%ISOPEN then
26        close emps;
27      end if;
28    end;
29    /

PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL>

 </source>
   
  


Demonstrates the %ROWCOUNT cursor attribute by using a single-row implicit cursor based on the DUAL pseudotable

   <source lang="sql">

SQL> SQL> DECLARE

 2    n NUMBER;
 3  BEGIN
 4    SELECT 1 INTO n FROM dual;
 5    dbms_output.put_line("Selected ["||SQL%ROWCOUNT||"]");
 6  END;
 7  /

Selected [1] PL/SQL procedure successfully completed.

 </source>
   
  


IF csr_org%ROWCOUNT = 1 THEN

   <source lang="sql">
 

SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> DECLARE

 2       CURSOR csr_org IS
 3             SELECT empno, ename FROM   emp;
 4             num_total_rows NUMBER;
 5  BEGIN
 6           FOR idx IN csr_org LOOP
 7                 IF csr_org%ROWCOUNT = 1 THEN
 8                    dbms_output.put_line("-----");
 9                 END IF;
10                 dbms_output.put_line("Total Organizations = "||to_char(num_total_rows));
11                 num_total_rows := csr_org%ROWCOUNT;
12         END LOOP;
13         IF num_total_rows > 0 THEN
14           dbms_output.new_line;
15           dbms_output.put_line("Total Organizations = "||to_char(num_total_rows));
16         END IF;
17  END;
18  /

Total Organizations = Total Organizations = 1 Total Organizations = 2 Total Organizations = 3 Total Organizations = 4 Total Organizations = 5 Total Organizations = 6 Total Organizations = 7 Total Organizations = 8 Total Organizations = 9 Total Organizations = 10 Total Organizations = 11 Total Organizations = 12 Total Organizations = 13 Total Organizations = 14 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> --

 </source>
   
  


Implicit Cursor Attributes: If the previous UPDATE statement didn"t match any rows, insert a new row into the place table

   <source lang="sql">
 

SQL> SQL> CREATE TABLE place (

 2    room_id          NUMBER(5) PRIMARY KEY,
 3    building         VARCHAR2(15),
 4    room_number      NUMBER(4),
 5    number_seats     NUMBER(4),
 6    description      VARCHAR2(50)
 7    );

Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20003, "Building 6", 150, 50, "Discussion Room A");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20004, "Building 6", 160, 50, "Discussion Room B");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20005, "Building 6", 170, 50, "Discussion Room C");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20006, "Music Building", 100, 10, "Music Practice Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20007, "Music Building", 200, 1000, "Concert Room");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)

 2             VALUES (20008, "Building 7", 300, 75, "Discussion Room D");

1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)

 2             VALUES (20009, "Building 7", 310, 50, "Discussion Room E");

1 row created. SQL> SQL> BEGIN

 2    UPDATE place
 3      SET number_seats = 100
 4      WHERE room_id = 99980;
 5    IF SQL%ROWCOUNT = 0 THEN
 6      INSERT INTO place (room_id, number_seats)
 7        VALUES (99980, 100);
 8    END IF;
 9  END;
10  /

PL/SQL procedure successfully completed. SQL> SQL> drop table place; Table dropped.

 </source>
   
  


modified the cursor FOR LOOP presented earlier to include %ROWCOUNT:

   <source lang="sql">
 

SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> DECLARE

 2       CURSOR csr_org IS
 3             SELECT empno, ename FROM   emp;
 4             num_total_rows NUMBER;
 5     BEGIN
 6             FOR idx IN csr_org LOOP
 7                 dbms_output.put_line(idx.empno||" "||idx.ename);
 8                 num_total_rows := csr_org%ROWCOUNT;
 9             END LOOP;
10             IF num_total_rows > 0 THEN
11               dbms_output.new_line;
12               dbms_output.put_line("Total Organizations = "||to_char(num_total_rows));
13             END IF;
14  END;
15  /

7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER Total Organizations = 14 PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> SQL> --

 </source>
   
  


sql%rowcount macro

   <source lang="sql">

SQL> SQL> create table myTable2 ( cnt int ); Table created. SQL> SQL> insert into myTable2 values ( 0 ); 1 row created. SQL> SQL> create table t ( x int check ( x>0 ) ); Table created. SQL> SQL> create trigger t_trigger before insert or delete on t for each row

 2  begin
 3     if ( inserting ) then
 4          update myTable2 set cnt = cnt +1;
 5     else
 6          update myTable2 set cnt = cnt -1;
 7     end if;
 8     dbms_output.put_line( "fired and updated "  || sql%rowcount || " rows" );
 9  end;
10  /

Trigger created. SQL> SQL> create or replace procedure p

 2  as
 3  begin
 4          insert into t values ( 1 );
 5          insert into t values (-1 );
 6  end;
 7  /

Procedure created. SQL> select * from t; no rows selected SQL> select * from myTable2;

      CNT

        0

SQL> begin

 2          p;
 3  end;
 4  /

fired and updated 1 rows fired and updated 1 rows begin

ERROR at line 1: ORA-02290: check constraint (sqle.SYS_C0010265) violated ORA-06512: at "sqle.P", line 5 ORA-06512: at line 2

SQL> select * from t; no rows selected SQL> select * from myTable2;

      CNT

        0

SQL> SQL> SQL> begin

 2      p;
 3  exception
 4      when others then null;
 5  end;
 6  /

fired and updated 1 rows fired and updated 1 rows PL/SQL procedure successfully completed. SQL> select * from t;

        X

        1

SQL> select * from myTable2;

      CNT

        1

SQL> SQL> rollback; Rollback complete. SQL> SQL> begin

 2      savepoint sp;
 3      p;
 4  exception
 5      when others then
 6          rollback to sp;
 7  end;
 8  /

fired and updated 1 rows fired and updated 1 rows PL/SQL procedure successfully completed. SQL> select * from t; no rows selected SQL> select * from myTable2;

      CNT

        0

SQL> SQL> SQL> SQL> drop table t; Table dropped. SQL> drop table myTable2; Table dropped.

 </source>