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.
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>
an explicit cursor that selects data
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> --
Column value indexed cursor
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.
Combine for loop and if statement to check the value in cursor
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>
Cursor performance
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>
Delete from table where current of cursor
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>
Explicit Cursor Demo
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>
If statement and single piece value in cursor
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>
Implicit and Explicit Cursors
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> --
Use cursor to store the row count
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>
Use explicit cursor to fetch and store value to number variable
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.
Write an explicit cursor in a FOR loop and use the data
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 /