Oracle PL/SQL Tutorial/Cursor/Introduction
Содержание
- 1 A Cursor for counting
- 2 An example of cursor variable assignment
- 3 An example of opening the cursorValue cursor
- 4 Assigning different queries to the same cursor variable
- 5 Create a cursor for update
- 6 Cursor performance
- 7 Cursors
- 8 Cursor to reference whole table
- 9 First Cursor Example
- 10 Nested cursor
- 11 OPEN Cursor for fetching
- 12 select first row to a cursor
- 13 To prepare the comma-separated list
A Cursor for counting
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> declare
2 cursor c_countEmps is select count(*) from employee;
3 v_out NUMBER;
4 begin
5 open c_countEmps;
6 fetch c_countEmps into v_out;
7 close c_countEmps;
8 DBMS_OUTPUT.put_line("number of emps is:"||v_out);
9 end;
10 /
number of emps is:8 PL/SQL procedure successfully completed. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
An example of cursor variable assignment
<source lang="sql">
SQL> SQL> create table product(
2 product_id number(4) not null, 3 product_description varchar2(20) not null 4 );
Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> DECLARE
2 TYPE rc is REF CURSOR; 3 refCursorValue1 rc; 4 refCursorValue2 rc; 5 myRecord product%ROWTYPE; 6 BEGIN 7 OPEN refCursorValue1 FOR SELECT * from product; 8 9 refCursorValue2 :=refCursorValue1; 10 LOOP 11 FETCH refCursorValue2 INTO myRecord; 12 EXIT WHEN refCursorValue2%NOTFOUND; 13 dbms_output.put_line(to_char(myRecord.product_id)||" "|| 14 rpad(myRecord.product_description,20," ")); 15 END LOOP; 16 CLOSE refCursorValue2; 17 END; 18 /
1 Java 2 Oracle 3 C# 4 Javascript 5 Python PL/SQL procedure successfully completed. SQL> SQL> drop table product; Table dropped.</source>
An example of opening the cursorValue cursor
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> SQL> DECLARE
2 CURSOR cursorValue IS SELECT ename FROM employees; 3 4 v_product_description VARCHAR2(20); 5 v_company_short_name VARCHAR2(30); 6 7 BEGIN 8 OPEN cursorValue; 9 /*...<Process the cursor resultset>...*/ 10 null; 11 END; 12 /
PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL></source>
Assigning different queries to the same cursor variable
<source lang="sql">
SQL> SQL> SQL> SQL> create table product(
2 product_id number(4) not null, 3 product_description varchar2(20) not null 4 );
Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> create table company(
2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 );
Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> create table org_company_site(
2 company_id number(8) not null, 3 site_no number(4) not null 4 );
Table created. SQL> insert into org_company_site values (1001,1); 1 row created. SQL> insert into org_company_site values (1002,2); 1 row created. SQL> insert into org_company_site values (1003,3); 1 row created. SQL> insert into org_company_site values (1004,1); 1 row created. SQL> insert into org_company_site values (1004,2); 1 row created. SQL> insert into org_company_site values (1004,3); 1 row created. SQL> insert into org_company_site values (1005,1); 1 row created. SQL> insert into org_company_site values (1005,4); 1 row created. SQL> insert into org_company_site values (1005,5); 1 row created. SQL> insert into org_company_site values (1006,1); 1 row created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE p_print_report(p_report_no NUMBER,p_title VARCHAR2)
2 IS
3 TYPE rc IS REF CURSOR;
4 refCursorValue rc;
5 v_product_description VARCHAR2(20);
6 v_company_short_name VARCHAR2(30);
7 BEGIN
8 IF (p_report_no =1)THEN
9 OPEN refCursorValue FOR SELECT h.product_description,o.rupany_short_name
10 FROM company o,product h
11 WHERE o.product_id =h.product_id
12 AND 1 < (SELECT count(os.site_no)
13 FROM org_company_site os
14 WHERE os.rupany_id =o.rupany_id);
15 ELSIF (p_report_no =2)THEN
16 OPEN refCursorValue FOR SELECT h.product_description,o.rupany_short_name
17 FROM company o,product h
18 WHERE o.product_id =h.product_id
19 AND NOT EXISTS
20 (SELECT *
21 FROM company o1
22 WHERE o1.rupany_id =o.rupany_id
23 AND o1.product_id =2 );
24 END IF;
25 dbms_output.put_line(p_title);
26 dbms_output.put_line(rpad("-",length(p_title),"-"));
27 dbms_output.put_line(rpad("Hierarchy",20," ")||" "||rpad("Description",30," "));
28 dbms_output.put_line(rpad("-",20,"-")||" "||rpad("-",30,"-"));
29 LOOP
30 FETCH refCursorValue INTO v_product_description,v_company_short_name;
31 EXIT WHEN refCursorValue%NOTFOUND;
32 dbms_output.put_line(rpad(v_product_description,20," ")||" "||
33 rpad(v_company_short_name,30," "));
34 END LOOP;
35 CLOSE refCursorValue;
36 END p_print_report;
37 /
Procedure created. SQL> SQL> SQL> drop table org_company_site; Table dropped. SQL> SQL> drop table company; Table dropped. SQL> SQL> drop table product; Table dropped. SQL> SQL></source>
Create a cursor for update
<source lang="sql">
SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25) PRIMARY KEY, 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE 6 );
Table created. SQL> SQL> SQL> INSERT INTO product VALUES ("Product 1", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 3", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 4", 25, 10000, null); 1 row created. SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04"); 1 row created. SQL> INSERT INTO product VALUES ("Product 6", 45, 1, TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M.")); 1 row created. SQL> SQL> SQL> SQL> DECLARE
2 CURSOR product_cur IS 3 SELECT * FROM product 4 FOR UPDATE OF product_price; 5 BEGIN 6 FOR product_rec IN product_cur 7 LOOP 8 UPDATE product 9 SET product_price = (product_rec.product_price * 0.97) 10 WHERE CURRENT OF product_cur; 11 END LOOP; 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table product; Table dropped. SQL> SQL></source>
Cursor performance
<source lang="sql">
SQL> SQL> SQL> create table myTable as select * from all_objects; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "myTable" ); PL/SQL procedure successfully completed. SQL> SQL> create table myTable2 as select * from myTable; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "myTable2" ); PL/SQL procedure successfully completed. SQL> SQL> set timing on SQL> SQL> update myTable set object_name = lower(object_name); 12599 rows updated. Elapsed: 00:00:00.60 SQL> declare
2 type ridArray is table of rowid; 3 type vcArray is table of myTable2.object_name%type; 4 5 l_rids ridArray; 6 l_names vcArray; 7 8 cursor c is select rowid, object_name from myTable2; 9 begin 10 open c; 11 loop 12 fetch c bulk collect into l_rids, l_names LIMIT 100; 13 forall i in 1 .. l_rids.count 14 update myTable2 set object_name = lower(l_names(i)) where rowid = l_rids(i); 15 commit; 16 exit when c%notfound; 17 end loop; 18 close c; 19 end; 20 /
PL/SQL procedure successfully completed. Elapsed: 00:00:01.05 SQL> SQL> set timing off SQL> SQL> SQL> drop table myTable; Table dropped. SQL> drop table myTable2; Table dropped. SQL></source>
Cursors
You use a cursor when you have a SELECT statement that returns more than one row from the database.
A cursor is basically a set of rows that you can access one at a time.
You retrieve the rows into the cursor using your SELECT statement and then fetch the rows from the cursor.
You may follow five steps when using a cursor:
- Declare variables to store the column values from the SELECT statement.
- Declare the cursor, specifying your SELECT statement.
- Open the cursor.
- Fetch the rows from the cursor.
- Close the cursor.
The syntax for declaring a cursor is as follows:
<source lang="sql">
CURSOR cursor_name IS
SELECT_statement;
where cursor_name specifies the name of the cursor.</source>
Cursor to reference whole table
<source lang="sql">
SQL> SQL> create table big_table as select * from all_objects; Table created. SQL> SQL> alter session set sql_trace=true; Session altered. SQL> SQL> declare
2 cursor c is select * from big_table; 3 l_rec big_table%rowtype; 4 begin 5 open c; 6 fetch c into l_rec; 7 close c; 8 end; 9 /
PL/SQL procedure successfully completed. SQL> SQL> drop table big_table; Table dropped. SQL></source>
First Cursor Example
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 3 v_employeeID employee.id%TYPE; 4 v_FirstName employee.first_name%TYPE; 5 v_LastName employee.last_name%TYPE; 6 7 8 v_city employee.city%TYPE := "Vancouver"; 9 10 11 CURSOR c_employee IS 12 SELECT id, first_name, last_name FROM employee WHERE city = v_city; 13 BEGIN 14 15 16 OPEN c_employee; 17 LOOP 18 19 FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName; 20 DBMS_OUTPUT.put_line(v_employeeID); 21 DBMS_OUTPUT.put_line(v_FirstName); 22 DBMS_OUTPUT.put_line(v_LastName); 23 24 EXIT WHEN c_employee%NOTFOUND; 25 END LOOP; 26 27 28 CLOSE c_employee; 29 END; 30 /
02 Alison Mathews 03 James Smith 04 Celia Rice 05 Robert Black 08 James Cat 08 James Cat PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Nested cursor
<source lang="sql">
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> 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> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2
3 cv_emp SYS_REFCURSOR;
4 v_title BOOKS.TITLE%TYPE;
5 v_emp emp%ROWTYPE;
6 v_counter PLS_INTEGER := 0;
7
8 CURSOR book_cur IS SELECT b.title,CURSOR (SELECT * FROM emp a WHERE a.id = b.emp1 OR a.id = b.emp2 OR a.id = b.emp3)
9 FROM books b
10 WHERE isbn = "1";
11
12 BEGIN
13
14 DBMS_OUTPUT.ENABLE(1000000);
15
16 OPEN book_cur;
17
18 LOOP
19 FETCH book_cur INTO v_title, cv_emp;
20 EXIT WHEN book_cur%NOTFOUND;
21
22 v_counter := 0;
23
24 DBMS_OUTPUT.PUT_LINE("Title from the main cursor: "||v_title);
25
26 LOOP
27 FETCH cv_emp INTO v_emp;
28 EXIT WHEN cv_emp%NOTFOUND;
29
30 v_counter := v_counter + 1;
31
32 DBMS_OUTPUT.PUT_LINE("emp"||v_counter||": "||v_emp.fname||" "||v_emp.lname);
33 END LOOP;
34 END LOOP;
35
36 CLOSE book_cur;
37
38 END;
39 /
Title from the main cursor: Oracle emp1: Enn F emp2: C D emp3: A B PL/SQL procedure successfully completed. SQL> drop table emp; Table dropped. SQL> drop table books; Table dropped.</source>
OPEN Cursor for fetching
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> DECLARE
2 3 CURSOR c_employee IS SELECT * from employee; 4 BEGIN 5 -- Assign to bind variables before the cursor OPEN. 6 -- Open the cursor. 7 OPEN c_employee; 8 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
select first row to a cursor
<source lang="sql">
SQL> SQL> create table big_table as select * from all_objects; Table created. SQL> SQL> declare
2 cursor c is select /*+ FIRST_ROWS(1) */ * from big_table order by owner; 3 l_rec big_table%rowtype; 4 begin 5 open c; 6 fetch c into l_rec; 7 close c; 8 end; 9 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table big_table; Table dropped. SQL></source>
To prepare the comma-separated list
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> create or replace function f_getEmps(i_deptNo VARCHAR)
2 return VARCHAR2 3 is 4 cursor c_emp is 5 select first_Name from employee where id = i_deptNo; 6 v_out VARCHAR2(4000); 7 begin 8 for r_emp in c_emp loop 9 if v_out is null then 10 v_out:=r_emp.first_Name; 11 else 12 v_out:=v_out||", "||r_emp.first_Name; 13 end if; 14 end loop; 15 return v_out; 16 end; 17 /
Function created. SQL> SQL> select id, first_name, f_getEmps(id) emps
2 from employee;
ID FIRST_NAME EMPS
01 Jason Jason 02 Alison Alison 03 James James 04 Celia Celia 05 Robert Robert 06 Linda Linda 07 David David 08 James James
8 rows selected. SQL> / SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
