Oracle PL/SQL/Cursor/Cursor ROWCOUNT
Содержание
- 1 Check SQL%ROWCOUNT after update statement
- 2 Cursor attributes: FOUND NOTFOUND, ROWCOUNT
- 3 Demonstrates the %ROWCOUNT cursor attribute by using a single-row implicit cursor based on the DUAL pseudotable
- 4 IF csr_org%ROWCOUNT = 1 THEN
- 5 Implicit Cursor Attributes: If the previous UPDATE statement didn"t match any rows, insert a new row into the place table
- 6 modified the cursor FOR LOOP presented earlier to include %ROWCOUNT:
- 7 sql%rowcount macro
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>