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
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>
Cursor attributes: FOUND NOTFOUND, ROWCOUNT
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>
Demonstrates the %ROWCOUNT cursor attribute by using a single-row implicit cursor based on the DUAL pseudotable
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.
IF csr_org%ROWCOUNT = 1 THEN
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> --
Implicit Cursor Attributes: If the previous UPDATE statement didn"t match any rows, insert a new row into the place table
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.
modified the cursor FOR LOOP presented earlier to include %ROWCOUNT:
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> --
sql%rowcount macro
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.