Oracle PL/SQL/Cursor/Cursor FOUND
Содержание
Check cursor%found with if statement in for loop
SQL> create table myTable( x primary key ) organization index as select 1 from dual;
Table created.
SQL>
SQL> create or replace
2 procedure explicit2 is
3 cursor explicit_cur is select 1 from myTable;
4 dummy number;
5 begin
6 open explicit_cur;
7 for i in 1 .. 500 loop
8 fetch explicit_cur into dummy;
9 if explicit_cur%found then
10 dbms_output.put_line("I worked");
11 end if;
12 end loop;
13 close explicit_cur;
14 end;
15 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
Cursor found property
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> declare
2 cursor myCursor is select * from employee where rownum < 6 order by 1;
3
4 myEmployee employee%rowtype;
5 row number := 1;
6 begin
7 open myCursor;
8 fetch myCursor into myEmployee;
9
10 loop
11 if myCursor%FOUND then
12 dbms_output.put_line("Looping over record " ||row|| " of " || myCursor%ROWCOUNT);
13 fetch myCursor into myEmployee;
14 row := row + 1;
15 elsif myCursor%NOTFOUND then
16 exit;
17 end if;
18 end loop;
19
20 if myCursor%ISOPEN then
21 close myCursor;
22 end if;
23 end;
24 /
Looping over record 1 of 1
Looping over record 2 of 2
Looping over record 3 of 3
Looping over record 4 of 4
Looping over record 5 of 5
PL/SQL procedure successfully completed.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
if cursor%found, if cursor%notfound
SQL>
SQL> create table t ( object_id primary key, object_name )
2 organization index
3 as
4 select object_id, object_name from all_objects;
Table created.
SQL>
SQL> create or replace procedure explicit
2 as
3 l_object_name t.object_name%type;
4 l_dummy t.object_name%type;
5
6 cursor c( l_object_id in number ) is select object_name from t where object_id = l_object_id;
7 begin
8 for i in 1 .. 30000
9 loop
10 open c(i);
11 fetch c into l_object_name;
12 if ( c%notfound ) then
13 l_object_name := null;
14 end if;
15 fetch c into l_dummy;
16 if ( c%found ) then
17 raise too_many_rows;
18 end if;
19 close c;
20 end loop;
21 end;
22 /
Procedure created.
SQL>
SQL>
SQL> drop table t;
Table dropped.
While Cursor%FOUND
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
4 FROM emp;
5 v_org_rec csr_org%ROWTYPE;
6 BEGIN
7 IF (NOT csr_org%ISOPEN) THEN
8 OPEN csr_org;
9 END IF;
10 FETCH csr_org INTO v_org_rec;
11 WHILE (csr_org%FOUND) LOOP
12 dbms_output.put_line(v_org_rec.empno||" "||v_org_rec.ename);
13 FETCH csr_org INTO v_org_rec;
14 END LOOP;
15 IF (csr_org%ISOPEN) THEN
16 CLOSE csr_org;
17 END IF;
18 END;
19 /
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
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> --
while cursorName%found, loop
SQL>
SQL>
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,sysdate, 123.12, sysdate+1, "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,sysdate, 50.98, sysdate+2, "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,sysdate, 35.99, sysdate+3, "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,sysdate, 19.95, sysdate+4, "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,sysdate, 10.95, sysdate+5, "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (6, 9,sysdate-3, 22.95, sysdate+6, "1 pm", "VS", 2, "Mike", "Happy Birthday" );
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (7, 9,sysdate-4, 35.95, sysdate+7, "12 noon", "VS", 2, "Jason", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (8, 12,sysdate-6, 35.95, sysdate+8, "12 noon", "DI",3, "Lite", "Happy New Year");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (9, 12,sysdate-9, 75.95, sysdate+9, "12 noon", "CA",7, "Sara", "Happy New Year" );
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (10, 4, sysdate, 19.95, sysdate, "2:30 pm", "VG",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (11, 2, sysdate, 30.00, sysdate+2, "1:30 pm", "VG",2, "Mary", "Happy New Year");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (12, 7, sysdate-3, 21.95, sysdate-2, "3:30 pm", "CA",2, "Mary", "Happy New Year");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (13, 7, sysdate, 21.95, sysdate, "3:30 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL>
SQL> create table sale(
2 gift_id integer
3 ,product_id integer
4 ,quantity number(4,0)
5 ,price number(7,2)
6 ,primary key (gift_id ,product_id)
7 );
Table created.
SQL>
SQL> insert into sale(gift_id, product_id, quantity, price) values(1, 2, 10, 23.00 );
1 row created.
SQL> insert into sale(gift_id, product_id, quantity, price) values(2, 1, 1, 23.11 );
1 row created.
SQL> insert into sale(gift_id, product_id, quantity, price) values(11, 8, 1, 30.00);
1 row created.
SQL>
SQL>
SQL> --set termout on
SQL> DECLARE
2 giftIDValue sale.gift_id%type;
3 totalValue sale.price%type;
4 cursor c1 is
5 select gift_id, sum(price * quantity) from sale group by gift_id;
6 begin
7 open c1;
8 fetch c1 into giftIDValue, totalValue;
9 while c1%found loop
10 update gift
11 set total_price = totalValue
12 where gift_id = giftIDValue;
13 fetch c1 into giftIDValue, totalValue;
14 end loop;
15 close c1;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table gift;
Table dropped.
SQL> drop table sale;
Table dropped.
SQL>
while programmers%FOUND loop
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));
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> CREATE TABLE JOBS (
2 JOB_ID VARCHAR2 (10) NOT NULL,
3 JOB_TITLE VARCHAR2 (35) CONSTRAINT JOB_TITLE_NN NOT NULL,
4 MIN_SALARY NUMBER (6),
5 MAX_SALARY NUMBER (6),
6 CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ;
Table created.
SQL>
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( "DBA", "President", 20000, 40000);
1 row created.
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( "PROG", "Administration Vice President", 15000, 30000);
1 row created.
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( "MGR", "Administration Assistant", 3000, 6000);
1 row created.
SQL>
SQL> declare
2 cursor programmers
3 is select e.last_name name, e.salary
4 from employees e, jobs j
5 where e.job_id = j.job_id
6 and j.job_title = "Programmer"
7 order by salary;
8
9 name varchar2(200);
10 salary number(9,2);
11 begin
12 for c1 in (select j.job_title, j.min_salary, j.max_salary,
13 avg(e.salary) avg_salary
14 from employees e, jobs j
15 where e.job_id = j.job_id
16 group by j.job_title, j.min_salary, j.max_salary
17 order by j.job_title) loop
18 dbms_output.put_line(c1.job_title||"s, average $"||c1.avg_salary);
19 end loop;
20
21 open programmers;
22 fetch programmers into name, salary;
23 while programmers%FOUND loop
24 dbms_output.put_line(name || " makes $" || salary);
25 fetch programmers into name, salary;
26 end loop;
27 close programmers;
28 end;
29 /
Administration Assistants, average $27500
Administration Vice Presidents, average $21000
Presidents, average $20000
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees;
Table dropped.
SQL> drop table jobs;
Table dropped.
SQL>