Oracle PL/SQL/Cursor/Explicit Cursor
Содержание
- 1 An explicit cursor fetch loop.
- 2 an explicit cursor that selects data
- 3 Column value indexed cursor
- 4 Combine for loop and if statement to check the value in cursor
- 5 Cursor performance
- 6 Delete from table where current of cursor
- 7 Explicit Cursor Demo
- 8 If statement and single piece value in cursor
- 9 Implicit and Explicit Cursors
- 10 Use cursor to store the row count
- 11 Use explicit cursor to fetch and store value to number variable
- 12 Write an explicit cursor in a FOR loop and use the data
An explicit cursor fetch loop.
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> DECLARE
2 myLecturerID lecturer.id%TYPE; 3 firstName lecturer.first_name%TYPE; 4 v_LastName lecturer.last_name%TYPE; 5 6 v_Major lecturer.major%TYPE := "Computer Science"; 7 8 CURSOR cursorValue IS 9 SELECT id, first_name, last_name 10 FROM lecturer 11 WHERE major = v_Major; 12 BEGIN 13 OPEN cursorValue; 14 LOOP 15 FETCH cursorValue INTO myLecturerID, firstName, v_LastName; 16 17 EXIT WHEN cursorValue%NOTFOUND; 18 END LOOP; 19 CLOSE cursorValue; 20 END; 21 /
PL/SQL procedure successfully completed. SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
an explicit cursor that selects data
<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> 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; -- EXIT statement exits the LOOP, not the IF stmt
22 end if;
23 end loop;
24
25 if emps%ISOPEN then
26 close emps;
27 end if;
28 end;
29 /
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> drop table employees; Table dropped. SQL> SQL> --
</source>
Column value indexed cursor
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
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> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> declare
2 l_dname dept.dname%type; 3 l_deptno dept.deptno%type; 4 l_ename emp.ename%type; 5 cursor c1 is 6 select deptno, dname from dept order by deptno; 7 cursor c2( p_deptno in number ) 8 is 9 select ename 10 from emp 11 where deptno = p_deptno and sal = (select max(sal) from emp where deptno = p_deptno); 12 begin 13 open c1; 14 loop 15 fetch c1 into l_deptno, l_dname; 16 exit when c1%notfound; 17 open c2(l_deptno); 18 fetch c2 into l_ename; 19 close c2; 20 dbms_output.put_line 21 ( l_deptno || ", " || l_dname || ", " || l_ename ); 22 end loop; 23 close c1; 24 end; 25 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
Combine for loop and if statement to check the value in cursor
<source lang="sql">
SQL> CREATE TABLE products(
2 name VARCHAR2(50), 3 price NUMBER(8,2), 4 min_price NUMBER(8,2) 5 );
Table created. SQL> SQL> SQL> create or replace procedure print_products as
2 cursor dataCursor is select name, price from products; 3 begin 4 for i in dataCursor LOOP 5 if i.price > 50 then 6 dbms_output.put_line(i.name ||" Price: "|| i.price); 7 else 8 dbms_output.put_line(i.name || " Product under 50"); 9 end if; 10 END LOOP; 11 end; 12 /
Procedure created. SQL> SQL> execute print_products PL/SQL procedure successfully completed. SQL> SQL> drop table products; Table dropped. SQL>
</source>
Cursor performance
<source lang="sql">
SQL> SQL> SQL> create table myTable as select * from all_objects; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "myTable" ); PL/SQL procedure successfully completed. SQL> SQL> create table myTable2 as select * from myTable; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "myTable2" ); PL/SQL procedure successfully completed. SQL> SQL> set timing on SQL> SQL> update myTable set object_name = lower(object_name); 12599 rows updated. Elapsed: 00:00:00.60 SQL> declare
2 type ridArray is table of rowid; 3 type vcArray is table of myTable2.object_name%type; 4 5 l_rids ridArray; 6 l_names vcArray; 7 8 cursor c is select rowid, object_name from myTable2; 9 begin 10 open c; 11 loop 12 fetch c bulk collect into l_rids, l_names LIMIT 100; 13 forall i in 1 .. l_rids.count 14 update myTable2 set object_name = lower(l_names(i)) where rowid = l_rids(i); 15 commit; 16 exit when c%notfound; 17 end loop; 18 close c; 19 end; 20 /
PL/SQL procedure successfully completed. Elapsed: 00:00:01.05 SQL> SQL> set timing off SQL> SQL> SQL> drop table myTable; Table dropped. SQL> drop table myTable2; Table dropped. SQL>
</source>
Delete from table where current of cursor
<source lang="sql">
SQL> SQL> CREATE TABLE myTable(
2 e INTEGER, 3 f INTEGER 4 );
Table created. SQL> SQL> DELETE FROM myTable; 0 rows deleted. SQL> INSERT INTO myTable VALUES(1, 3); 1 row created. SQL> INSERT INTO myTable VALUES(2, 4); 1 row created. SQL> SQL> DECLARE
2 a myTable.e%TYPE; 3 b myTable.f%TYPE; 4 CURSOR myTableCursor IS 5 SELECT e, f 6 FROM myTable 7 WHERE e < f 8 FOR UPDATE; 9 BEGIN 10 OPEN myTableCursor; 11 LOOP 12 FETCH myTableCursor INTO a, b; 13 EXIT WHEN myTableCursor%NOTFOUND; 14 DELETE FROM myTable WHERE CURRENT OF myTableCursor; 15 INSERT INTO myTable VALUES(b, a); 16 END LOOP; 17 18 CLOSE myTableCursor; 19 20 END; 21 22 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
Explicit Cursor Demo
<source lang="sql">
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> -- explicit cursor SQL> SQL> SQL> set serverout on SQL> SQL> declare
2 cursor myCursor (pid in number) is select * from employee where id = pid;
3
4 myEmployee employee%rowtype;
5 begin
6 dbms_output.put_line("Getting employees ID= 03");
7 open myCursor(03);
8 loop
9 fetch myCursor into myEmployee;
10 exit when myCursor%notfound;
11 dbms_output.put("Employee id " || myEmployee.id || " is ");
12 dbms_output.put_line(myEmployee.first_name || " " || myEmployee.last_name);
13 end loop;
14 close myCursor;
15
16 dbms_output.put_line("Getting employees ID= 01");
17 open myCursor(01);
18 loop
19 fetch myCursor into myEmployee;
20 exit when myCursor%notfound;
21 dbms_output.put("Employee id " || myEmployee.id || " is ");
22 dbms_output.put_line(myEmployee.first_name || " " || myEmployee.last_name);
23 end loop;
24 close myCursor;
25 end;
26 /
Getting employees ID= 03 Employee id 03 is James Smith Getting employees ID= 01 Employee id 01 is Jason Martin PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
If statement and single piece value in cursor
<source lang="sql">
SQL> CREATE TABLE products(
2 name VARCHAR2(50), 3 price NUMBER(8,2) 4 );
Table created. SQL> SQL> SQL> declare
2 cursor get_data is select name, price from products; 3 begin 4 for i in get_data 5 LOOP 6 if i.price > 50 then 7 dbms_output.put_line(i.name || " Price: " || i.price); 8 end if; 9 END LOOP; 10 end; 11 /
PL/SQL procedure successfully completed. SQL> SQL> drop table products; Table dropped. SQL> SQL>
</source>
Implicit and Explicit Cursors
<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> 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> SQL> SQL> declare
2 cursor cursorValue
3 is select e.last_name name, e.salary
4 from employees e, jobs j
5 order by salary;
6
7 name varchar2(200);
8 salary number(9,2);
9 begin
10 for c1 in (select j.job_title, j.min_salary, j.max_salary,
11 avg(e.salary) avg_salary
12 from employees e, jobs j
13 where e.job_id = j.job_id
14 group by j.job_title, j.min_salary, j.max_salary
15 order by j.job_title) loop
16 dbms_output.put_line(c1.job_title||"s, average $"||c1.avg_salary);
17 end loop;
18
19 open cursorValue;
20 fetch cursorValue into name, salary;
21 dbms_output.put_line(chr(13) || chr(13));
22 dbms_output.put_line("cursorValue");
23 while cursorValue%FOUND loop
24 dbms_output.put_line(name || " makes $" || salary);
25 fetch cursorValue into name, salary;
26 end loop;
27 close cursorValue;
28 end;
29 /
Administration Assistants, average $27500 Administration Vice Presidents, average $21000 Presidents, average $20000 cursorValue Wells makes $20000 Java makes $20000 Beck makes $20000 Viper makes $20000 Wells makes $20000 Oracle makes $20000 Java makes $20000 Beck makes $20000 Viper makes $20000 Oracle makes $20000 Viper makes $20000 Beck makes $20000 Java makes $20000 Oracle makes $20000 Wells makes $20000 Bliss makes $24000 Bliss makes $24000 Bliss makes $24000 Kyte makes $25000 Kyte makes $25000 Kyte makes $25000 Lawson makes $30000 Lawson makes $30000 Lawson makes $30000 PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL> drop table jobs; Table dropped. SQL> SQL> --
</source>
Use cursor to store the row count
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> SQL> declare
2 l_emp_count number;
3 i number; -- We will use this as our counter
4
5 CURSOR cursorValue IS
6 select count(*)
7 from lecturer;
8 begin
9 OPEN cursorValue;
10 FETCH cursorValue INTO l_emp_count;
11
12 FOR i IN 1 .. l_emp_count LOOP
13 dbms_output.put_line("Employee " || i);
14 END LOOP;
15
16 CLOSE cursorValue;
17 end;
18 /
Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Employee 8 Employee 9 Employee 10 Employee 11 Employee 12 PL/SQL procedure successfully completed. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> SQL>
</source>
Use explicit cursor to fetch and store value to number variable
<source lang="sql">
SQL> create table myTable(
2 x number, 3 y char(100) 4 );
Table created. SQL> SQL> insert into myTable
2 select rownum, "padding" 3 from all_objects 4 where rownum < 10001;
10000 rows created. SQL> SQL> SQL> SQL> create or replace procedure explicit is
2 cursor explicit_cur is 3 select 1 4 from myTable 5 where x = 1; 6 dummy number; 7 begin 8 for i in 1 .. 500 loop 9 open explicit_cur; 10 fetch explicit_cur 11 into dummy; 12 close explicit_cur; 13 end loop; 14 end; 15 /
Procedure created. SQL> SQL> drop table myTable; Table dropped.
</source>
Write an explicit cursor in a FOR loop and use the data
<source lang="sql">
SQL> SQL> DECLARE
2 CURSOR c IS SELECT item_title FROM item;
3 BEGIN
4 FOR i IN c LOOP
5 dbms_output.put_line("The title is ["||i.item_title||"]");
6 END LOOP;
7 END;
8 /
</source>
