Oracle PL/SQL Tutorial/Cursor/Introduction — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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>
An example of cursor variable assignment
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.
An example of opening the cursorValue cursor
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>
Assigning different queries to the same cursor variable
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>
Create a cursor for update
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>
Cursor performance
SQL>
SQL>
SQL> create table myTable as select * from all_objects;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats( user, "myTable" );
PL/SQL procedure successfully completed.
SQL>
SQL> create table myTable2 as select * from myTable;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats( user, "myTable2" );
PL/SQL procedure successfully completed.
SQL>
SQL> set timing on
SQL>
SQL> update myTable set object_name = lower(object_name);
12599 rows updated.
Elapsed: 00:00:00.60
SQL> declare
2 type ridArray is table of rowid;
3 type vcArray is table of myTable2.object_name%type;
4
5 l_rids ridArray;
6 l_names vcArray;
7
8 cursor c is select rowid, object_name from myTable2;
9 begin
10 open c;
11 loop
12 fetch c bulk collect into l_rids, l_names LIMIT 100;
13 forall i in 1 .. l_rids.count
14 update myTable2 set object_name = lower(l_names(i)) where rowid = l_rids(i);
15 commit;
16 exit when c%notfound;
17 end loop;
18 close c;
19 end;
20 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.05
SQL>
SQL> set timing off
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
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:
CURSOR cursor_name IS
SELECT_statement;
where
cursor_name specifies the name of the cursor.
Cursor to reference whole table
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>
First Cursor Example
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>
Nested cursor
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.
OPEN Cursor for fetching
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.
select first row to a cursor
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>
To prepare the comma-separated list
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>