Oracle PL/SQL/Cursor/CURSOR FOR Loop — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 A function that uses a CURSOR FOR Loop
- 2 Assign value from cursor to a table collection by index
- 3 A subquery in the FROM clause of a cursor within a cursor FOR loop
- 4 Calculation based on cursor value
- 5 Create a cursor in for statement
- 6 For each row in a cursor
- 7 Output value in cursor one by one
- 8 Simple Loop and fetch cursor
- 9 The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be used to guide a loop
- 10 Use "for loop" to loop through the cursor
- 11 Use pl/sql, cursor and for loop to count table
A function that uses a CURSOR FOR Loop
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>
SQL> -- a function that uses a CURSOR FOR Loop:
SQL>
SQL> CREATE OR REPLACE Function TotalIncome( name_in IN varchar2 ) RETURN Number
2 IS
3 myNumber number(6);
4
5 cursor c1 is
6 select salary
7 from employee
8 where first_name like name_in;
9
10 BEGIN
11
12 myNumber := 0;
13
14 FOR myEmployee in c1
15 LOOP
16 myNumber := myNumber + myEmployee.salary;
17 END LOOP;
18
19 RETURN myNumber;
20
21 END;
22 /
Function created.
SQL>
SQL> select TotalIncome("%a%") from dual;
TOTALINCOME("%A%")
------------------
23579
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Assign value from cursor to a table collection by index
SQL>
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from emp
2 /
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto
Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver
Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver
Tester
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver
Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver
Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York
Tester
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York
Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver
Tester
8 rows selected.
SQL> CREATE OR REPLACE PROCEDURE myProcedure
2 AS
3 CURSOR myCursor IS SELECT id, fname FROM emp;
4 TYPE firstNameType IS TABLE OF emp.fname%type INDEX BY emp.id%type;
5 ch firstNameType;
6 i integer := 0;
7 imax integer;
8 BEGIN
9 FOR j IN myCursor LOOP
10 i := i + 1;
11 ch(i) := j.fname;
12 END LOOP;
13 dbms_output.put_line(ch("James"));
14 END myProcedure;
15 /
Procedure created.
SQL>
SQL> exec myProcedure;
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table emp
2 /
Table dropped.
SQL>
SQL>
SQL>
A subquery in the FROM clause of a cursor within a cursor FOR loop
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> select * from job;
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- Cursors with subqueries
SQL>
SQL> -- A subquery in the FROM clause of a cursor within a cursor FOR loop:
SQL>
SQL> BEGIN
2 /* The FROM Clause of the SELECT statement contains a
3 subquery that select the total number of employees
4 enrolled in each dept */
5
6 FOR job_record IN
7 (SELECT jobtitle FROM job where empno = (SELECT empno FROM employee where ename="James")
8 )
9 LOOP
10
11 DBMS_OUTPUT.PUT_LINE(job_record.jobtitle);
12 END LOOP;
13 END;
14 /
Dediator
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Calculation based on cursor value
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL> create or replace procedure PROCESS_EMP is
2 cursor C is select empno, sal, comm from emp;
3 newSalary number := 10000;
4 begin
5 for i in c loop
6 newSalary := newSalary + i.rum / i.sal;
7 end loop;
8 exception
9 when zero_divide then
10 if C%ISOPEN then
11 close C;
12 end if;
13 end;
14 /
Procedure created.
SQL> drop table emp;
Table dropped.
Create a cursor in for statement
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "YourName@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Dillon", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL> select * from employees;
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte YourName@a.ru 13-JUN-98 MGR 4 25000 1005
1005 Dillon sdillon@a .ru 10-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 10-JUN-08 PROG 2 20000
1007 Java java01@g.ru 10-JUN-08 PROG 3 20000 1006
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1008 Oracle oracle1@g.ru 10-JUN-08 DBA 4 20000 1006
8 rows selected.
SQL>
SQL>
SQL> begin
2 for cur1 in (select employee_id from employees) loop
3 dbms_output.put_line(cur1.employee_id || " is a value in Employees");
4 end loop;
5 end;
6 /
1001 is a value in Employees
1002 is a value in Employees
1003 is a value in Employees
1004 is a value in Employees
1005 is a value in Employees
1006 is a value in Employees
1007 is a value in Employees
1008 is a value in Employees
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
For each row in a cursor
SQL> CREATE TABLE emp(
2 emp_ID NUMBER (6),
3 START_DATE DATE,
4 END_DATE DATE,
5 JOB_ID VARCHAR2 (10),
6 DEPARTMENT_ID NUMBER (4)
7 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE promotion_review_2
2 IS
3 CURSOR empCursor IS SELECT emp_id, start_date, end_date, job_id FROM emp;
4 BEGIN
5 FOR jh_rec IN empCursor
6 LOOP
7 DBMS_OUTPUT.put_line(jh_rec.emp_id||" had job "||jh_rec.job_id||" for "|| (jh_rec.end_date - jh_rec.start_date)||" days.");
8 END LOOP;
9 END;
10 /
Procedure created.
SQL> drop table emp;
Table dropped.
Output value in cursor one by one
SQL> CREATE TABLE products(
2 product_id NUMBER(6),
3 name VARCHAR2(50)
4 );
Table created.
SQL>
SQL>
SQL> set serveroutput on
SQL> declare
2 nameValue varchar2(80);
3 cursor get_data is select name from products;
4 begin
5 for i in get_data
6 LOOP
7 dbms_output.put_line(i.name);
8 END LOOP;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table products;
Table dropped.
Simple Loop and fetch 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, "E", "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> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
2 v_emp emp%ROWTYPE;
3
4 CURSOR auth_cur IS SELECT * FROM emp;
5 BEGIN
6 OPEN auth_cur;
7 LOOP
8 FETCH auth_cur INTO v_emp;
9 EXIT WHEN auth_cur%NOTFOUND;
10
11 DBMS_OUTPUT.PUT_LINE(v_emp.lname);
12 END LOOP;
13
14 CLOSE auth_cur;
15 END;
16 /
B
D
F
H
Z
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be used to guide a loop
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Emp
2 /
Hit a key to continue
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL>
SQL> -- The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be used to determine when a loop --
SQL> -- should be exited.
SQL>
SQL> DECLARE
2 CURSOR myEmpCursor IS
3 SELECT empno, ename
4 FROM emp
5 ORDER BY empno;
6
7 myID emp.empno%TYPE;
8 myName emp.ename%TYPE;
9 BEGIN
10
11 OPEN myEmpCursor;
12
13 /* Use a basic loop to fetch rows from the cursor and
14 print each row on the screen until all rows are
15 fetched */
16
17 LOOP
18 FETCH myEmpCursor INTO myID, myName;
19
20 /* Use the %NOTFOUND cursor attribute to determine
21 when to exit the basic loop */
22
23 EXIT WHEN myEmpCursor%NOTFOUND;
24
25 DBMS_OUTPUT.PUT_LINE(myID||" "||myName);
26 END LOOP;
27 CLOSE myEmpCursor;
28 END;
29 /
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Emp
2 /
Table dropped.
SQL>
SQL>
SQL>
Use "for loop" to loop through the cursor
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
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver
Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver
Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York
Tester
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York
Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver
Tester
8 rows selected.
SQL>
SQL>
SQL> -- cursor for loop
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 begin
3 for my_emp_rec in (select id, first_name
4 from employee)
5 loop
6 dbms_output.put("ID #" || my_emp_rec.id);
7 dbms_output.put_line(" is named " || my_emp_rec.first_name);
8 end loop;
9 end;
10 /
ID #01 is named Jason
ID #02 is named Alison
ID #03 is named James
ID #04 is named Celia
ID #05 is named Robert
ID #06 is named Linda
ID #07 is named David
ID #08 is named James
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Use pl/sql, cursor and for loop to count table
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL>
SQL> declare
2 cnt pls_integer := 0;
3 begin
4 for i in (select ename from emp) loop
5 cnt := cnt + 1;
6 end loop;
7 dbms_output.put_line(cnt);
8 end;
9 /
9
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.