Oracle PL/SQL/Cursor/Cursor Value
Содержание
- 1 A cursor FOR loop.
- 2 Cursor Variable Example 2
- 3 Cursor with a single value
- 4 Form sentences from database data
- 5 Read full table data from a cursor
- 6 Server-side cursor variables.
- 7 Single column cursor
- 8 Two identical queries to demonstrate the impact of changes
- 9 Update statement with cursor variable
- 10 Use Complex cursor to simplify the pl/sql logic
- 11 Use cursor to do full table scan
- 12 Use data referenced by cursor to update table
- 13 Use number functions when creating a cursor value
A cursor FOR loop.
<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> 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 cursorValue IS 3 SELECT id, first_name, last_name 4 FROM lecturer 5 WHERE major = "History"; 6 BEGIN 7 FOR myLecturerData IN cursorValue LOOP 8 INSERT INTO myStudent (student_id, department, course) 9 VALUES (myLecturerData.ID, "HIS", 301); 10 11 INSERT INTO MyTable (num_col, char_col) 12 VALUES (myLecturerData.ID, 13 myLecturerData.first_name || " " || myLecturerData.last_name); 14 END LOOP; 15 END; 16 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
- Mar Wells
- Pat Poll
- Tim Viper
SQL> SQL> drop table MyTable; Table dropped. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> drop table myStudent; Table dropped. SQL>
</source>
Cursor Variable Example 2
<source lang="sql">
SQL> SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 );
Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created. SQL> SQL> SQL> 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> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE ShowCursorVariable
2 (p_Table IN VARCHAR2) AS 3 4 TYPE myRoomType IS REF CURSOR; 5 6 cursorValue myRoomType; 7 8 v_Department session.department%TYPE; 9 v_Course session.course%TYPE; 10 v_RoomID place.room_id%TYPE; 11 v_Description place.description%TYPE; 12 BEGIN 13 IF p_Table = "session" THEN 14 OPEN cursorValue FOR 15 SELECT department, course 16 FROM session; 17 ELSIF p_table = "place" THEN 18 OPEN cursorValue FOR 19 SELECT room_id, description 20 FROM place; 21 ELSE 22 RAISE_APPLICATION_ERROR(-20000, 23 "Input must be ""session"" or ""place"""); 24 END IF; 25 26 LOOP 27 IF p_Table = "session" THEN 28 FETCH cursorValue INTO 29 v_Department, v_Course; 30 EXIT WHEN cursorValue%NOTFOUND; 31 32 INSERT INTO MyTable (num_col, char_col) 33 VALUES (v_Course, v_Department); 34 ELSE 35 FETCH cursorValue INTO 36 v_RoomID, v_Description; 37 EXIT WHEN cursorValue%NOTFOUND; 38 39 INSERT INTO MyTable (num_col, char_col) 40 VALUES (v_RoomID, SUBSTR(v_Description, 1, 60)); 41 END IF; 42 END LOOP; 43 44 CLOSE cursorValue; 45 46 COMMIT; 47 END ShowCursorVariable; 48 /
Procedure created. SQL> SQL> show error No errors. SQL> SQL> select * from MyTable; no rows selected SQL> SQL> drop table session; Table dropped. SQL> SQL> drop table place; Table dropped. SQL> SQL>
</source>
Cursor with a single value
<source lang="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 ID Employee"s Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
------------- ---------- --------- --------- ---------- ---------- ---------------
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
FINDEMPLOYEESALARY(FIRST_NAME)
6544.78
8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Form sentences from database data
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> CREATE TABLE books (
2 isbn CHAR(10) PRIMARY KEY, 3 category VARCHAR2(20), 4 title VARCHAR2(100), 5 num_pages NUMBER, 6 price NUMBER, 7 copyright NUMBER(4), 8 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("4", "Database", "SQL", 535, 39.99, 2002, 4, 5, 9);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("5", "Database", "Java", 487, 39.99, 2002, 10, 11);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("6", "Database", "JDBC", 592, 39.99, 2002, 12, 13);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("7", "Database", "XML", 500, 39.99, 2002, 1, 2, 3);
1 row created. SQL> SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 3 v_fname emp.fname%TYPE; 4 lnameValue emp.lname%TYPE; 5 v_row_count PLS_INTEGER := 0; 6 7 CURSOR auth_cur IS 8 SELECT a.fname, a.lname, count(b.title) 9 FROM emp a, books b 10 WHERE a.id = b.emp1 11 OR a.id = b.emp2 12 OR a.id = b.emp3 13 GROUP BY a.fname, a.lname 14 HAVING count(b.title) > 0 15 ORDER BY a.lname; 16 17 BEGIN 18 19 DBMS_OUTPUT.ENABLE(1000000); 20 21 OPEN auth_cur; 22 LOOP 23 FETCH auth_cur INTO v_fname, lnameValue, v_row_count; 24 EXIT WHEN auth_cur%NOTFOUND; 25 26 DBMS_OUTPUT.PUT_LINE(lnameValue||","||v_fname||" wrote "||v_row_count||" book(s)."); 27 END LOOP; 28 29 CLOSE auth_cur; 30 31 EXCEPTION 32 WHEN OTHERS 33 THEN 34 DBMS_OUTPUT.PUT_LINE(SQLERRM); 35 END; 36 /
B,A wrote 2 book(s). D,C wrote 2 book(s). F,Enn wrote 2 book(s). H,G wrote 2 book(s). Z,G wrote 2 book(s). PL/SQL procedure successfully completed. SQL> drop table books; Table dropped. SQL> drop table emp; Table dropped.
</source>
Read full table data from a cursor
<source lang="sql">
SQL> SQL> 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> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR giftCursor IS SELECT gift_id, name, price FROM gifts ORDER BY gift_id;
3 BEGIN
4 FOR v_gift IN giftCursor LOOP
5 DBMS_OUTPUT.PUT_LINE("gift_id = " || v_gift.gift_id ||", name = " || v_gift.name ||", price = " || v_gift.price);
6 END LOOP;
7 END;
8 /
gift_id = 1, name = Flower, price = 19.95 gift_id = 2, name = Computer, price = 30 gift_id = 3, name = iPod, price = 25.99 gift_id = 4, name = iPhone, price = 13.95 gift_id = 5, name = Book, price = 49.99 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table gifts; Table dropped.
</source>
Server-side cursor variables.
<source lang="sql">
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> SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 );
Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created. SQL> SQL> 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> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE ShowCursorVariable
2 (p_Table IN VARCHAR2) AS 3 4 TYPE myRoomType IS REF CURSOR; 5 6 cursorValue myRoomType; 7 8 v_Department session.department%TYPE; 9 v_Course session.course%TYPE; 10 v_RoomID place.room_id%TYPE; 11 v_Description place.description%TYPE; 12 BEGIN 13 IF p_Table = "session" THEN 14 OPEN cursorValue FOR 15 SELECT department, course 16 FROM session; 17 ELSIF p_table = "place" THEN 18 OPEN cursorValue FOR 19 SELECT room_id, description 20 FROM place; 21 ELSE 22 RAISE_APPLICATION_ERROR(-20000,"Input must be ""session"" or ""place"""); 23 END IF; 24 25 LOOP 26 IF p_Table = "session" THEN 27 FETCH cursorValue INTO 28 v_Department, v_Course; 29 EXIT WHEN cursorValue%NOTFOUND; 30 31 INSERT INTO MyTable (num_col, char_col) 32 VALUES (v_Course, v_Department); 33 ELSE 34 FETCH cursorValue INTO 35 v_RoomID, v_Description; 36 EXIT WHEN cursorValue%NOTFOUND; 37 38 INSERT INTO MyTable (num_col, char_col) 39 VALUES (v_RoomID, SUBSTR(v_Description, 1, 60)); 40 END IF; 41 END LOOP; 42 43 CLOSE cursorValue; 44 45 COMMIT; 46 END ShowCursorVariable; 47 /
Procedure created. SQL> SQL> SQL> drop table place; Table dropped. SQL> drop table session; Table dropped. SQL> drop table MyTable; Table dropped. SQL>
</source>
Single column cursor
<source lang="sql">
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.
</source>
Two identical queries to demonstrate the impact of changes
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 3 fnameValue emp.fname%TYPE; 4 5 6 7 CURSOR emp_cur1 IS SELECT fname FROM emp WHERE fname like "Ron%"; 8 9 CURSOR emp_cur2 IS SELECT fname FROM emp WHERE fname like "Ron%"; 10 11 BEGIN 12 13 OPEN emp_cur1; 14 UPDATE emp SET fname = "AAA" WHERE fname = "Ron"; 15 16 OPEN emp_cur2; 17 18 FETCH emp_cur1 INTO fnameValue; 19 DBMS_OUTPUT.PUT_LINE(fnameValue); 20 21 FETCH emp_cur2 INTO fnameValue; 22 DBMS_OUTPUT.PUT_LINE(fnameValue); 23 24 CLOSE emp_cur1; 25 CLOSE emp_cur2; 26 27 ROLLBACK; 28 29 END; 30 /
PL/SQL procedure successfully completed. SQL> drop table emp; Table dropped.
</source>
Update statement with cursor variable
<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> 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> 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> select * from myStudent; STUDENT_ID DEP COURSE G
--- -------- -
######## CS 102.00 A ######## CS 102.00 B ######## CS 102.00 C ######## HIS 101.00 A ######## HIS 101.00 B ######## HIS 101.00 B ######## HIS 101.00 A ######## HIS 101.00 C ######## HIS 101.00 C ######## HIS 101.00 E ######## HIS 101.00 B
STUDENT_ID DEP COURSE G
--- -------- -
######## HIS 101.00 A ######## HIS 101.00 D ######## HIS 101.00 A ######## NUT 307.00 A ######## NUT 307.00 A ######## MUS 410.00 B ######## MUS 410.00 E ######## MUS 410.00 B ######## MUS 410.00 B
20 rows selected. 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> 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> SQL> DECLARE
2 v_NumCredits session.num_credits%TYPE; 3 4 CURSOR c_Registeredlecturer IS 5 SELECT * 6 FROM lecturer 7 WHERE id IN (SELECT student_id 8 FROM myStudent 9 WHERE department= "HIS" 10 AND course = 101); 11 12 BEGIN 13 FOR myLecturerInfo IN c_Registeredlecturer LOOP 14 SELECT num_credits 15 INTO v_NumCredits 16 FROM session 17 WHERE department = "HIS" 18 AND course = 101; 19 20 UPDATE lecturer 21 SET current_credits = current_credits + v_NumCredits 22 WHERE id = myLecturerinfo.id; 23 24 COMMIT; 25 END LOOP; 26 END; 27 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myStudent; Table dropped. SQL> drop table session; Table dropped. SQL> drop table lecturer; Table dropped. SQL> SQL> SQL>
</source>
Use Complex cursor to simplify the pl/sql logic
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> CREATE TABLE books (
2 isbn CHAR(10) PRIMARY KEY, 3 category VARCHAR2(20), 4 title VARCHAR2(100), 5 num_pages NUMBER, 6 price NUMBER, 7 copyright NUMBER(4), 8 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 2009, 1, 2, 3);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 2009, 4, 5);
1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE emp_book_count
2 AS 3 fnameValue emp.fname%TYPE; 4 lnameValue emp.lname%TYPE; 5 v_row_count PLS_INTEGER := 0; 6 7 CURSOR auth_cur IS 8 SELECT a.fname, a.lname, count(b.title) 9 FROM emp a, books b 10 WHERE a.id = b.emp1 11 GROUP BY a.fname, a.lname 12 HAVING count(b.title) > 0 13 ORDER BY a.lname; 14 15 BEGIN 16 17 DBMS_OUTPUT.ENABLE(1000000); 18 19 OPEN auth_cur; 20 LOOP 21 FETCH auth_cur INTO fnameValue, lnameValue, v_row_count; 22 EXIT WHEN auth_cur%NOTFOUND; 23 24 DBMS_OUTPUT.PUT_LINE(lnameValue||","||fnameValue||" wrote "||v_row_count||" book(s)."); 25 END LOOP; 26 27 CLOSE auth_cur; 28 29 EXCEPTION 30 WHEN OTHERS 31 THEN 32 DBMS_OUTPUT.PUT_LINE(SQLERRM); 33 END; 34 /
Procedure created. SQL> show errors No errors. SQL> drop table books; Table dropped. SQL> drop table emp; Table dropped.
</source>
Use cursor to do full table scan
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 CURSOR auth_cur IS SELECT * FROM emp; 3 BEGIN 4 5 FOR v_emp IN auth_cur 6 LOOP 7 DBMS_OUTPUT.PUT_LINE(v_emp.lname); 8 END LOOP; 9 10 END; 11 /
B D F H Z PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped.
</source>
Use data referenced by cursor to update table
<source lang="sql">
SQL> create table sale(
2 gift_id integer 3 ,product_id integer 4 ,quantity number(4,0) 5 ,price number(7,2) 6 ,total_order_price number(9,2) 7 ,primary key (gift_id ,product_id) 8 );
Table created. SQL> -- order_item table inserts SQL> insert into sale(gift_id, product_id, quantity, price)values(1, 2, 10, 23.00 ); 1 row created. SQL> insert into sale(gift_id, product_id, quantity, price)values(2, 1, 1, 23.11 ); 1 row created. SQL> SQL> SQL> SQL> --set termout on SQL> DECLARE
2 giftIDValue sale.gift_id%type; 3 totalValue sale.price%type; 4 cursor c1 is 5 select gift_id, sum(price * quantity) from sale group by gift_id; 6 begin 7 open c1; 8 9 fetch c1 into giftIDValue, totalValue; 10 11 while c1%found loop 12 update sale set price = totalValue where gift_id = giftIDValue; 13 14 fetch c1 into giftIDValue, totalValue; 15 16 end loop; 17 18 close c1; 19 end; 20 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table sale; Table dropped.
</source>
Use number functions when creating a cursor value
<source lang="sql">
SQL> CREATE TABLE products(
2 product_id NUMBER(6), 3 name VARCHAR2(50), 4 price NUMBER(8,2), 5 min_price NUMBER(8,2) 6 );
Table created. SQL> SQL> create or replace function GetProductTaxIn (in_product_id number) return number is
2 priceValue number; 3 cursor dataCursor is select nvl(round(price * 1.15,2),0) from products where product_id = in_product_id; 4 begin 5 open dataCursor; 6 fetch dataCursor into priceValue; 7 return priceValue; 8 exception 9 when others then priceValue := 0; 10 return priceValue; 11 end; 12 /
Function created. SQL> SQL> select product_id, price, GetProductTaxIn(product_id)
2 from products 3
SQL> drop table products; Table dropped. SQL>
</source>
