Oracle PL/SQL/Cursor/Cursor Fetch
Содержание
- 1 A WHILE cursor fetch loop.
- 2 Below is a function that demonstrates how to use the FETCH statement
- 3 Check Cursor%FOUND after fetching
- 4 Different BULK_COLLECT statements used for bulk binds
- 5 FETCH cursor BULK COLLECT
- 6 Fetch cursor data to number variable
- 7 Fetch cursor till cursorVariable%notfound
- 8 Fetch cursor to table collection of row type
- 9 Fetch cursor to two variables
- 10 Fetch cursor value to three variables
- 11 Fetches the records from a cursor variable that returns employee information
- 12 Fetch out cursor value and insert to another table
- 13 Fetch row by row
- 14 Fetch single column cursor to varchar2 variable
- 15 Fetch value from cursor
- 16 Fetch value in cursor until NOTFOUND
- 17 legal and illegal FETCH statements.
- 18 ORA-01002: fetch out of sequence
- 19 "ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop.
- 20 PLS-00394: wrong number of values in the INTO list of a FETCH statement
A WHILE 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> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "HIS", 101, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "HIS", 101, "D");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "MUS", 410, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "MUS", 410, "B");
1 row created.
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 CURSOR MyHistoryLecturer IS
3 SELECT id, first_name, last_name
4 FROM lecturer
5 WHERE major = "History";
6
7 myLecturer MyHistoryLecturer%ROWTYPE;
8 BEGIN
9 OPEN MyHistoryLecturer;
10
11 FETCH MyHistoryLecturer INTO myLecturer;
12
13 WHILE MyHistoryLecturer%FOUND LOOP
14 INSERT INTO myStudent (student_id, department, course)
15 VALUES (myLecturer.ID, "HIS", 301);
16
17 INSERT INTO MyTable (num_col, char_col)
18 VALUES (myLecturer.ID,
19 myLecturer.first_name || " " || myLecturer.last_name);
20
21 FETCH MyHistoryLecturer INTO myLecturer;
22 END LOOP;
23
24 CLOSE MyHistoryLecturer;
25 END;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Scott Lawson Computer Science 11.00
######## Mar Wells History 4.00
######## Jone Bliss Computer Science 8.00
######## Man Kyte Economics 8.00
######## Pat Poll History 4.00
######## Tim Viper History 4.00
######## Barbara Blues Economics 7.00
######## David Large Music 4.00
######## Chris Elegant Nutrition 8.00
######## Rose Bond Music 7.00
######## Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Sharon Clear Computer Science 3.00
12 rows selected.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>
Below is a function that demonstrates how to use the FETCH statement
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 Employee"s Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 $1,232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> -- Cursor without parameters (simplest)
SQL> CREATE OR REPLACE Function FindEmployeeSalary ( name_in IN varchar2 ) RETURN number
2 IS
3 mySalary number;
4
5 CURSOR c1
6 IS
7 SELECT salary
8 from employee
9 where first_name = name_in;
10
11 BEGIN
12
13 open c1;
14 fetch c1 into mySalary;
15
16 if c1%notfound then
17 mySalary := 0;
18 end if;
19
20 close c1;
21
22 RETURN mySalary;
23
24 END;
25 /
Function created.
SQL>
SQL> select FindEmployeeSalary(first_name) from employee;
FINDEMPLOYEESALARY(FIRST_NAME)
------------------------------
1234.56
6661.78
6544.78
2344.78
2334.78
4322.78
7897.78
6544.78
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Check Cursor%FOUND after fetching
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> DECLARE
2 CURSOR cur_employee_salary IS
3 SELECT sal
4 FROM emp
5 WHERE empno = 7369;
6 lv_cur_employee_salary cur_employee_salary%ROWTYPE;
7 BEGIN
8 OPEN cur_employee_salary;
9 FETCH cur_employee_salary INTO lv_cur_employee_salary;
10 IF cur_employee_salary%FOUND THEN
11 DBMS_OUTPUT.PUT_LINE("Record Found.");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("Record NOT Found.");
14 END IF;
15 CLOSE cur_employee_salary;
16 END;
17 /
Record Found.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
Different BULK_COLLECT statements used for bulk binds
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE;
3 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE;
4 v_Numbers t_Numbers := t_Numbers(1);
5 v_Strings t_Strings := t_Strings(1);
6 v_Numbers2 t_Numbers;
7 v_Strings2 t_Strings;
8
9 CURSOR c_char IS
10 SELECT char_col
11 FROM MyTable
12 WHERE num_col > 800
13 ORDER BY num_col;
14
15 BEGIN
16 v_Numbers.EXTEND(1500);
17 v_Strings.EXTEND(1500);
18 FOR v_Count IN 1..1000 LOOP
19 v_Numbers(v_Count) := v_Count;
20 v_Strings(v_Count) := "Element #" || v_Count;
21 IF v_Count > 500 THEN
22 v_Numbers(v_Count + 500) := v_Count;
23 v_Strings(v_Count + 500) := "Element #" || v_Count;
24 END IF;
25 END LOOP;
26
27 DELETE FROM MyTable;
28 FORALL v_Count IN 1..1500
29 INSERT INTO MyTable (num_col, char_col)
30 VALUES (v_Numbers(v_Count), v_Strings(v_Count));
31
32 SELECT num_col, char_col
33 BULK COLLECT INTO v_Numbers, v_Strings
34 FROM MyTable
35 ORDER BY num_col;
36
37 DBMS_OUTPUT.PUT_LINE(
38 "First query fetched " || v_Numbers.COUNT || " rows");
39
40 SELECT num_col
41 BULK COLLECT INTO v_Numbers2
42 FROM MyTable;
43
44 DBMS_OUTPUT.PUT_LINE(
45 "Second query fetched " || v_Numbers2.COUNT || " rows");
46
47 OPEN c_char;
48 FETCH c_char BULK COLLECT INTO v_Strings2;
49 CLOSE c_char;
50
51 DBMS_OUTPUT.PUT_LINE(
52 "Cursor fetch retrieved " || v_Strings2.COUNT || " rows");
53
54 END;
55 /
First query fetched 1500 rows
Second query fetched 1500 rows
Cursor fetch retrieved 400 rows
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
5 INDEX BY BINARY_INTEGER;
6 v_Numbers t_Numbers;
7 v_Strings t_Strings;
8 BEGIN
9 DELETE FROM MyTable;
10 FOR v_Outer IN 1..10 LOOP
11 FOR v_Inner IN 1..v_Outer LOOP
12 INSERT INTO MyTable (num_col, char_col)
13 VALUES (v_Outer, "Element #" || v_Inner);
14 END LOOP;
15 v_Numbers(v_Outer) := v_Outer;
16 END LOOP;
17
18 FORALL v_Count IN 1..5
19 DELETE FROM MyTable
20 WHERE num_col = v_Numbers(v_Count)
21 RETURNING char_col BULK COLLECT INTO v_Strings;
22
23 DBMS_OUTPUT.PUT_LINE("After delete:");
24 FOR v_Count IN 1..v_Strings.COUNT LOOP
25 DBMS_OUTPUT.PUT_LINE(
26 " v_Strings(" || v_Count || ") = " || v_Strings(v_Count));
27 END LOOP;
28 END;
29 /
After delete:
v_Strings(1) = Element #1
v_Strings(2) = Element #1
v_Strings(3) = Element #2
v_Strings(4) = Element #1
v_Strings(5) = Element #2
v_Strings(6) = Element #3
v_Strings(7) = Element #1
v_Strings(8) = Element #2
v_Strings(9) = Element #3
v_Strings(10) = Element #4
v_Strings(11) = Element #1
v_Strings(12) = Element #2
v_Strings(13) = Element #3
v_Strings(14) = Element #4
v_Strings(15) = Element #5
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
6 Element #1
6 Element #2
6 Element #3
6 Element #4
6 Element #5
6 Element #6
7 Element #1
7 Element #2
7 Element #3
7 Element #4
7 Element #5
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
7 Element #6
7 Element #7
8 Element #1
8 Element #2
8 Element #3
8 Element #4
8 Element #5
8 Element #6
8 Element #7
8 Element #8
9 Element #1
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
9 Element #2
9 Element #3
9 Element #4
9 Element #5
9 Element #6
9 Element #7
9 Element #8
9 Element #9
10 Element #1
10 Element #2
10 Element #3
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
10 Element #4
10 Element #5
10 Element #6
10 Element #7
10 Element #8
10 Element #9
10 Element #10
40 rows selected.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
FETCH cursor BULK COLLECT
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60));
Table created.
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE;
3 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE;
4 v_Numbers t_Numbers := t_Numbers(1);
5 v_Strings t_Strings := t_Strings(1);
6 v_Numbers2 t_Numbers;
7 v_Strings2 t_Strings;
8
9 CURSOR c_char IS
10 SELECT char_col
11 FROM MyTable
12 WHERE num_col > 800
13 ORDER BY num_col;
14
15 BEGIN
16 v_Numbers.EXTEND(1500);
17 v_Strings.EXTEND(1500);
18 FOR v_Count IN 1..1000 LOOP
19 v_Numbers(v_Count) := v_Count;
20 v_Strings(v_Count) := "Element #" || v_Count;
21 IF v_Count > 500 THEN
22 v_Numbers(v_Count + 500) := v_Count;
23 v_Strings(v_Count + 500) := "Element #" || v_Count;
24 END IF;
25 END LOOP;
26
27 DELETE FROM MyTable;
28 FORALL v_Count IN 1..1500
29 INSERT INTO MyTable (num_col, char_col)
30 VALUES (v_Numbers(v_Count), v_Strings(v_Count));
31
32 SELECT num_col, char_col
33 BULK COLLECT INTO v_Numbers, v_Strings
34 FROM MyTable
35 ORDER BY num_col;
36
37 DBMS_OUTPUT.PUT_LINE("First query fetched " || v_Numbers.COUNT || " rows");
38
39 SELECT num_col
40 BULK COLLECT INTO v_Numbers2
41 FROM MyTable;
42
43 DBMS_OUTPUT.PUT_LINE("Second query fetched " || v_Numbers2.COUNT || " rows");
44
45 OPEN c_char;
46 FETCH c_char BULK COLLECT INTO v_Strings2;
47 CLOSE c_char;
48
49 DBMS_OUTPUT.PUT_LINE("Cursor fetch retrieved " || v_Strings2.COUNT || " rows");
50
51 END;
52 /
First query fetched 1500 rows
Second query fetched 1500 rows
Cursor fetch retrieved 400 rows
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
Fetch cursor data to number variable
SQL> create table myTable ( x primary key ) organization index as select 1 from dual;
Table created.
SQL>
SQL> create or replace procedure explicit2 is
2 cursor explicit_cur is select 1 from myTable;
3 dummy number;
4 begin
5 open explicit_cur;
6 for i in 1 .. 50000 loop
7 fetch explicit_cur into dummy;
8 end loop;
9 close explicit_cur;
10 end;
11 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
Fetch cursor till cursorVariable%notfound
SQL> CREATE TABLE emp(
2 emp_ID NUMBER (6),
3 START_DATE DATE,
4 END_DATE DATE,
5 JOB_ID VARCHAR2 (10),
6 DEPARTMENT_ID NUMBER (4)
7 );
Table created.
SQL> CREATE OR REPLACE PROCEDURE promotion_review_1
2 IS
3 nempid NUMBER;
4 dstartdate DATE;
5 denddate DATE;
6 sjobid VARCHAR2 (20);
7 CURSOR empCursor IS SELECT emp_id, start_date, end_date, job_id FROM emp;
8 BEGIN
9 OPEN empCursor;
10 LOOP
11 FETCH empCursor INTO nempid, dstartdate, denddate, sjobid;
12 EXIT WHEN empCursor%NOTFOUND;
13 DBMS_OUTPUT.put_line ("emp "||nempid||" had job "||sjobid||" for "||(denddate - dstartdate)||" days.");
14 END LOOP;
15 CLOSE empCursor;
16 END;
17 /
Procedure created.
SQL> show errors
No errors.
SQL> drop table emp;
Table dropped.
Fetch cursor to table collection of row type
SQL>
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp
2 /
ID FNAME LNAME 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
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
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
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
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> DECLARE
2 TYPE emp_table_struct IS TABLE OF emp.fname%TYPE INDEX BY BINARY_INTEGER;
3 emp_table emp_table_struct;
4 CURSOR emp_cursor IS SELECT fname FROM emp ORDER BY id;
5 v_row NUMBER := 1;
6 BEGIN
7 OPEN emp_cursor;
8 LOOP
9 FETCH emp_cursor INTO emp_table(v_row);
10 EXIT WHEN emp_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(emp_table(v_row));
12 v_row := v_row + 1;
13 END LOOP;
14 CLOSE emp_cursor;
15 DBMS_OUTPUT.PUT_LINE("Total rows: "||emp_table.COUNT);
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- clean the table
SQL> drop table emp
2 /
Table dropped.
Fetch cursor to two variables
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 firstName VARCHAR2(20);
3 v_LastName VARCHAR2(20);
4 CURSOR c_lecturer IS
5 SELECT first_name, last_name FROM lecturer;
6 BEGIN
7 OPEN c_lecturer;
8 LOOP
9 FETCH c_lecturer INTO firstName, v_LastName;
10 EXIT WHEN c_lecturer%NOTFOUND;
11 END LOOP;
12 CLOSE c_lecturer;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL>
Fetch cursor value to three variables
SQL> CREATE TABLE gifts (
2 gift_id INTEGER CONSTRAINT gifts_pk PRIMARY KEY,
3 gift_type_id INTEGER ,
4 name VARCHAR2(30) NOT NULL,
5 description VARCHAR2(50),
6 price NUMBER(5, 2)
7 );
Table created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, "Flower", "Birthday", 19.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, "Computer", "New Year", 30.00);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, "iPod", "Birthday", 25.99);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, "iPhone", "New Year", 13.95);
1 row created.
SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, "Book", "Birthday", 49.99);
1 row created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_gift_id gifts.gift_id%TYPE;
3 v_name gifts.name%TYPE;
4 v_price gifts.price%TYPE;
5
6 CURSOR giftCursor IS SELECT gift_id, name, price FROM gifts ORDER BY gift_id;
7
8 BEGIN
9 OPEN giftCursor;
10
11 LOOP
12 FETCH giftCursor INTO v_gift_id, v_name, v_price;
13
14 EXIT WHEN giftCursor%NOTFOUND;
15
16 DBMS_OUTPUT.PUT_LINE("v_gift_id = " || v_gift_id || ", v_name = " || v_name ||", v_price = " || v_price);
17
18 END LOOP;
19
20 CLOSE giftCursor;
21
22 END;
23 /
v_gift_id = 1, v_name = Flower, v_price = 19.95
v_gift_id = 2, v_name = Computer, v_price = 30
v_gift_id = 3, v_name = iPod, v_price = 25.99
v_gift_id = 4, v_name = iPhone, v_price = 13.95
v_gift_id = 5, v_name = Book, v_price = 49.99
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table gifts;
Table dropped.
Fetches the records from a cursor variable that returns employee information
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> set serverout on;
SQL>
SQL> -- fetches the records from a cursor variable that returns employee information
SQL>
SQL> DECLARE
2 TYPE employee_ref_cursor_type IS REF CURSOR
3 RETURN employee%ROWTYPE;
4
5 employee_ref_cursor employee_ref_cursor_type;
6
7 employee_record employee%ROWTYPE;
8 BEGIN
9 OPEN employee_ref_cursor FOR SELECT * FROM employee;
10
11 LOOP
12 /* Fetch the first record from the cursor */
13 FETCH employee_ref_cursor INTO employee_record;
14
15 /* Exit when last record retrieved from cursor */
16 EXIT WHEN employee_ref_cursor%NOTFOUND;
17
18 DBMS_OUTPUT.PUT_LINE("Employee name is: "|| employee_record.first_name);
19 END LOOP;
20
21 CLOSE employee_ref_cursor;
22 END;
23 /
Employee name is: Jason
Employee name is: Alison
Employee name is: James
Employee name is: Celia
Employee name is: Robert
Employee name is: Linda
Employee name is: David
Employee name is: James
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Fetch out cursor value and insert to another table
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> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR MyHistoryLecturer IS
3 SELECT id, first_name, last_name
4 FROM lecturer
5 WHERE major = "History";
6 BEGIN
7 FOR myLecturer IN MyHistoryLecturer LOOP
8 INSERT INTO myStudent (student_id, department, course)
9 VALUES (myLecturer.ID, "HIS", 101);
10
11 INSERT INTO MyTable (num_col, char_col)
12 VALUES (myLecturer.ID,
13 myLecturer.first_name || " " || myLecturer.last_name);
14
15 END LOOP;
16
17 COMMIT;
18 END;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
10002 Mar Wells
10005 Pat Poll
10006 Tim Viper
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL> drop table lecturer;
Table dropped.
SQL>
Fetch row by row
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> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL> variable x refcursor
SQL> declare
2 r emp%rowtype;
3 begin
4 :x := emp_list;
5 loop
6 fetch :x into r;
7 exit when :x%notfound;
8 dbms_output.put_line(r.empno||","||r.hiredate);
9 end loop;
10 close :x;
11 end;
12 /
2,20-02-1981
3,22-02-1981
4,02-04-1981
5,28-09-1981
7,09-06-1981
8,09-12-1982
9,17-11-1981
10,08-09-1981
13,03-12-1981
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
Fetch single column cursor to varchar2 variable
SQL> CREATE TABLE products(
2 product_id NUMBER(6),
3 name VARCHAR2(50)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> declare
2 v_name varchar2(80);
3 cursor get_data is select name from products;
4 begin
5 open get_data;
6 fetch get_data into v_name;
7 dbms_output.put_line(v_name);
8 close get_data;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table products;
Table dropped.
Fetch value from cursor
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> declare
2 cursor employeeCursor
3 is select e.first_name || " " || e.last_name name, e.salary
4 from employee e;
5
6 name varchar2(200);
7 salary number(9,2);
8 begin
9 for c1 in (select avg(e.salary) avg_salary
10 from employee e) loop
11 dbms_output.put_line("average salary "||c1.avg_salary);
12 end loop;
13
14 open employeeCursor;
15 fetch employeeCursor into name, salary;
16
17 dbms_output.put_line(chr(13) || chr(13));
18 dbms_output.put_line("EmployeeS");
19 dbms_output.put_line("------------------------");
20
21 while employeeCursor%FOUND loop
22 dbms_output.put_line(name || " makes $" || salary);
23 fetch employeeCursor into name, salary;
24 end loop;
25 close employeeCursor;
26 end;
27 /
average salary 4071.7525
EmployeeS
------------------------
Jason Martin makes $1234.56
Alison Mathews makes $6661.78
James Smith makes $6544.78
Celia Rice makes $2344.78
Robert Black makes $2334.78
Linda Green makes $4322.78
David Larry makes $7897.78
James Cat makes $1232.78
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
Fetch value in cursor until NOTFOUND
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> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> DECLARE
2 TYPE t_Cursor IS REF CURSOR;
3 v_Cursor t_Cursor;
4 myLecturer lecturer%ROWTYPE;
5 v_Class session%ROWTYPE;
6 BEGIN
7 OPEN v_Cursor FOR
8 SELECT * FROM lecturer WHERE current_credits >= 8;
9
10 LOOP
11 FETCH v_Cursor INTO myLecturer;
12 EXIT WHEN v_Cursor%NOTFOUND;
13
14 DBMS_OUTPUT.PUT_LINE(myLecturer.first_name || " " || myLecturer.last_name ||
15 " has " || myLecturer.current_credits || " credits.");
16
17 END LOOP;
18
19 CLOSE v_Cursor;
20
21 OPEN v_Cursor FOR
22 SELECT * FROM session WHERE department = "ECN";
23 DBMS_OUTPUT.PUT_LINE("The Economics session are: ");
24 LOOP
25 FETCH v_Cursor INTO v_Class;
26 EXIT WHEN v_Cursor%NOTFOUND;
27
28 DBMS_OUTPUT.PUT_LINE(v_Class.description);
29 END LOOP;
30
31 CLOSE v_Cursor;
32 END;
33 /
Scott Lawson has 11 credits.
Jone Bliss has 8 credits.
Man Kyte has 8 credits.
Chris Elegant has 8 credits.
Rita Johnson has 8 credits.
The Economics session are:
Economics 203
Economics 101
PL/SQL procedure successfully completed.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
legal and illegal FETCH statements.
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> select * from session;
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
HIS 101.00
History 101
30.00 11.00 4.00 ########
HIS 301.00
History 301
30.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
CS 101.00
Computer Science 101
50.00 .00 4.00 ########
ECN 203.00
Economics 203
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
15.00 .00 3.00 ########
CS 102.00
Computer Science 102
35.00 3.00 4.00 ########
MUS 410.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
Music 410
5.00 4.00 3.00 ########
ECN 101.00
Economics 101
50.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
NUT 307.00
Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00
Music 100
100.00 .00 3.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
9 rows selected.
SQL>
SQL> DECLARE
2 v_Department session.department%TYPE;
3 v_Course session.course%TYPE;
4 CURSOR myAllSession IS
5 SELECT *
6 FROM session;
7 v_sessionRecord myAllSession%ROWTYPE;
8 BEGIN
9 OPEN myAllSession;
10
11 FETCH myAllSession INTO v_sessionRecord;
12
13 FETCH myAllSession INTO v_Department, v_Course;
14 END;
15 /
FETCH myAllSession INTO v_Department, v_Course;
*
ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored
SQL>
SQL> drop table session;
Table dropped.
SQL>
ORA-01002: fetch out of sequence
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 CURSOR myAllLecturer IS
3 SELECT *
4 FROM lecturer
5 FOR UPDATE;
6
7 myLecturer myAllLecturer%ROWTYPE;
8 BEGIN
9 OPEN myAllLecturer;
10 FETCH myAllLecturer INTO myLecturer;
11 COMMIT WORK;
12 FETCH myAllLecturer INTO myLecturer;
13 END;
14 /
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 12
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL>
SQL>
"ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE 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> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Scott Lawson Computer Science 11.00
######## Mar Wells History 4.00
######## Jone Bliss Computer Science 8.00
######## Man Kyte Economics 8.00
######## Pat Poll History 4.00
######## Tim Viper History 4.00
######## Barbara Blues Economics 7.00
######## David Large Music 4.00
######## Chris Elegant Nutrition 8.00
######## Rose Bond Music 7.00
######## Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------- -------------------- -------------------- ------------------------------ ---------------
######## Sharon Clear Computer Science 3.00
12 rows selected.
SQL>
SQL> DECLARE
2 CURSOR myAllLecturer IS
3 SELECT *
4 FROM lecturer
5 FOR UPDATE;
6
7 myLecturer myAllLecturer%ROWTYPE;
8 BEGIN
9 OPEN myAllLecturer;
10
11 FETCH myAllLecturer INTO myLecturer;
12
13 COMMIT;
14
15 FETCH myAllLecturer INTO myLecturer;
16 END;
17 /
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 15
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
PLS-00394: wrong number of values in the INTO list of a FETCH statement
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> DECLARE
2 v_Department session.department%TYPE;
3 v_Course session.course%TYPE;
4 CURSOR myAllSession IS
5 SELECT *
6 FROM session;
7 v_sessionRecord myAllSession%ROWTYPE;
8 BEGIN
9 OPEN myAllSession;
10
11 FETCH myAllSession INTO v_sessionRecord;
12
13 FETCH myAllSession INTO v_Department, v_Course;
14 END;
15 /
FETCH myAllSession INTO v_Department, v_Course;
*
ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>