SQL> SQL> CREATE TABLE employees 2 ( employee_id 3 last_name 4 email 5 hire_date 6 job_id 7 department_id 8 salary 9 manager_id 10 ); Table created.

number(10) varchar2(50) varchar2(30), date, varchar2(30), number(10), number(6), number(6)

not null, not null,

SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary,department_id ,manager_id) 2 values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN2002','MGR', 30000,1 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id ,manager_id) 2 values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 ); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id ,manager_id) 2 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1004, 'Kyte', 'YourName@a.com', SYSDATE3650, 'MGR',25000 ,4, 1005); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1005, 'Dillon', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j

ob_id, salary, department_id,manager_id) 2 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG ', 20000, 2, null); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG ', 20000, 3, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, ' DBA', 20000, 4, 1006); 1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMEN T_ID SALARY MANAGER_ID ----------- -------------------------------------------------- ----------------------------- --------- ------------------------------ ------------ ---------- ---------1001 Lawson lawson@g .com 01-JAN-02 MGR 1 30000 1004 1002 Wells wells@g. com 01-JAN-02 DBA 2 20000 1005 1003 Bliss bliss@g. com 01-JAN-02 PROG 3 24000 1004 1004 Kyte YourName @a.com 13-JUN-98 MGR 4 25000 1005 1005 Dillon sdillon@a .com 10-JUN-08 PROG 1 20000 1006 1006 Beck clbeck@g .com 10-JUN-08 PROG 2 20000 1007 Java java01@g .com 10-JUN-08 PROG 3 20000 1006 EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMEN T_ID SALARY MANAGER_ID ----------- -------------------------------------------------- --------

---------------------- --------- ------------------------------ ------------ ---------- ---------1008 Oracle oracle1@ g.com 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 E mployees'); 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>he %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', 17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); 7902, TO_DATE('

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', 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('1MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9JUN-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('12JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); 1 row created. 7839, TO_DATE('

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23JAN-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 OMM DEPTNO ---------- ------------ ---------7369 SMITH 20 7499 ALLEN 300 30 7521 WARD 500 30 7566 JONES 20 7654 MARTIN 400 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 0 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 14 rows selected. SQL> SQL> JOB MGR HIREDATE SAL C

--------- ---------- --------- ---------- ------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 7902 17-DEC-80 7698 20-FEB-81 7698 22-FEB-81 7839 02-APR-81 7698 28-SEP-81 7839 01-MAY-81 7839 09-JUN-81 7566 09-DEC-82 17-NOV-81 7698 08-SEP-81 7788 12-JAN-83 7698 03-DEC-81 7566 03-DEC-81 7782 23-JAN-82 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1

SQL> -- The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be u sed 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>

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; REATE 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 / 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.comm / 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 /

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 4 5 6 7 8 9 10

cursor get_data is select name from products; begin for i in get_data LOOP dbms_output.put_line(i.name); END LOOP; end; /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table products; Table dropped.

declare 2 cursor emps 3 is select * 4 from employees 5 where rownum < 6 6 order by 1; 7 8 emp employees%rowtype; 9 row number := 1; 10 begin 11 open emps; 12 fetch emps into emp; 13 14 loop 15 if emps%FOUND then 16 dbms_output.put_line('Looping over record ' ||row|| ' of ' || 17 emps%ROWCOUNT); 18 fetch emps into emp; 19 row := row + 1; 20 elsif emps%NOTFOUND then 21 exit; 22 end if; 23 end loop; 24 25 if emps%ISOPEN then 26 close emps; 27 end if; 28 end; 29 / PL/SQL procedure successfully completed. SQL> SQL> drop table employees;

Table dropped. SQL> DECLARE 2 CURSOR csr_org IS 3 SELECT empno, ename FROM emp; 4 num_total_rows NUMBER; 5 BEGIN 6 FOR idx IN csr_org LOOP 7 dbms_output.put_line(idx.empno||' '||idx.ename); 8 num_total_rows := csr_org%ROWCOUNT; 9 END LOOP; 10 IF num_total_rows > 0 THEN 11 dbms_output.new_line; 12 dbms_output.put_line('Total Organizations = '|| to_char(num_total_rows)); 13 END IF; 14 END; 15 / BEGIN 2 UPDATE place 3 SET number_seats = 100 4 WHERE room_id = 99980; 5 IF SQL%ROWCOUNT = 0 THEN 6 INSERT INTO place (room_id, number_seats) 7 VALUES (99980, 100); 8 END IF; 9 END; 10 / eclare 2 cursor myCursor (pid in number) is select * from employee where id = pid; 3 4 myEmployee employee%rowtype; 5 begin 6 dbms_output.put_line('Getting employees ID= 03'); 7 open myCursor(03); 8 loop 9 fetch myCursor into myEmployee; 10 exit when myCursor%notfound; 11 dbms_output.put('Employee id ' || myEmployee.id || ' is '); 12 dbms_output.put_line(myEmployee.first_name || ' ' || myEmplo yee.last_name); 13 end loop; 14 close myCursor; 15 16 dbms_output.put_line('Getting employees ID= 01'); 17 open myCursor(01); 18 loop 19 fetch myCursor into myEmployee; 20 exit when myCursor%notfound; 21 dbms_output.put('Employee id ' || myEmployee.id || ' is '); 22 dbms_output.put_line(myEmployee.first_name || ' ' || myEmplo yee.last_name);

23 24 25 26

end loop; close myCursor; end; /

mplicit and Explicit Cursors

SQL> SQL> CREATE TABLE employees 2 ( employee_id 3 last_name 4 email 5 hire_date 6 job_id 7 department_id 8 salary 9 manager_id 10 ); Table created.

number(10) varchar2(50) varchar2(30), date, varchar2(30), number(10), number(6), number(6)

not null, not null,

SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary,department_id ,manager_id) 2 values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN2002','MGR', 30000,1 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id ,manager_id) 2 values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 ); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id ,manager_id) 2 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1004, 'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005); 1 row created.

SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, ' PROG', 20000, 1, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id,manager_id) 2 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG ', 20000, 2, null); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG ', 20000, 3, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, j ob_id, salary, department_id, manager_id) 2 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, ' DBA', 20000, 4, 1006); 1 row created. SQL> SQL> CREATE TABLE JOBS ( 2 JOB_ID VARCHAR2 (10) NOT NULL, 3 JOB_TITLE VARCHAR2 (35) CONSTRAINT JOB_TITLE_NN 4 MIN_SALARY NUMBER (6), 5 MAX_SALARY NUMBER (6), 6 CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ; Table created. SQL> SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VAL UES ( 'DBA', 'President', 20000, 40000); 1 row created. SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VAL UES ( 'PROG', 'Administration Vice President', 15000, 30000); 1 row created. SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VAL UES ( 'MGR', 'Administration Assistant', 3000, 6000);

NOT NULL,

1 row created. SQL> SQL> SQL> SQL> declare 2 cursor cursorValue 3 is select e.last_name name, e.salary 4 from employees e, jobs j 5 order by salary; 6 7 name varchar2(200); 8 salary number(9,2); 9 begin 10 for c1 in (select j.job_title, j.min_salary, j.max_salary, 11 avg(e.salary) avg_salary 12 from employees e, jobs j 13 where e.job_id = j.job_id 14 group by j.job_title, j.min_salary, j.max_salary 15 order by j.job_title) loop 16 dbms_output.put_line(c1.job_title||'s, average $'|| c1.avg_salary); 17 end loop; 18 19 open cursorValue; 20 fetch cursorValue into name, salary; 21 dbms_output.put_line(chr(13) || chr(13)); 22 dbms_output.put_line('cursorValue'); 23 while cursorValue%FOUND loop 24 dbms_output.put_line(name || ' makes $' || salary); 25 fetch cursorValue into name, salary; 26 end loop; 27 close cursorValue; 28 end; 29 / Administration Assistants, average $27500 Administration Vice Presidents, average $21000 Presidents, average $20000 cursorValue Wells makes $20000 Java makes $20000 Beck makes $20000 Viper makes $20000 Wells makes $20000 Oracle makes $20000 Java makes $20000 Beck makes $20000 Viper makes $20000 Oracle makes $20000 Viper makes $20000 Beck makes $20000 Java makes $20000 Oracle makes $20000 Wells makes $20000

Bliss makes $24000 Bliss makes $24000 Bliss makes $24000 Kyte makes $25000 Kyte makes $25000 Kyte makes $25000 Lawson makes $30000 Lawson makes $30000 Lawson makes $30000 PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL> drop table jobs; Table dropped. SQL> SQL> --

An explicit cursor fetch loop.

SQL> SQL> SQL> CREATE TABLE lecturer ( 2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 ); Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_cr edits) 2 VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8 ); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10004, 'Man', 'Kyte','Economics', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10005, 'Pat', 'Poll','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10006, 'Tim', 'Viper','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10007, 'Barbara', 'Blues','Economics', 7); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10008, 'David', 'Large','Music', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits)

2 1 row created.

VALUES (10010, 'Rose', 'Bond','Music', 7);

SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3); 1 row created. SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

DECLARE myLecturerID lecturer.id%TYPE; firstName lecturer.first_name%TYPE; v_LastName lecturer.last_name%TYPE; v_Major lecturer.major%TYPE := 'Computer Science';

CURSOR cursorValue IS SELECT id, first_name, last_name FROM lecturer WHERE major = v_Major; BEGIN OPEN cursorValue; LOOP FETCH cursorValue INTO myLecturerID, firstName, v_LastName; EXIT WHEN cursorValue%NOTFOUND; END LOOP; CLOSE cursorValue; END; /

PL/SQL procedure successfully completed. SQL> SQL> drop table lecturer; Table dropped. SQL>

Use cursor to store the row count

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_cre dits) 2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_cr edits) 2 VALUES (10002, 'Mar', 'Wells','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8 ); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10004, 'Man', 'Kyte','Economics', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10005, 'Pat', 'Poll','History', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10006, 'Tim', 'Viper','History', 4); 1 row created.

SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10007, 'Barbara', 'Blues','Economics', 7); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10008, 'David', 'Large','Music', 4); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10010, 'Rose', 'Bond','Music', 7); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8); 1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_cre dits) 2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3); 1 row created. SQL> SQL> SQL> SQL> declare 2 l_emp_count number; 3 i number; -- We will use this as our counter 4 5 CURSOR cursorValue IS 6 select count(*) 7 from lecturer; 8 begin 9 OPEN cursorValue;

10 FETCH cursorValue INTO l_emp_count; 11 12 FOR i IN 1 .. l_emp_count LOOP 13 dbms_output.put_line('Employee ' || i); 14 END LOOP; 15 16 CLOSE cursorValue; 17 end; 18 / Employee 1 Employee 2 Employee 3 Employee 4 Employee 5 Employee 6 Employee 7 Employee 8 Employee 9 Employee 10 Employee 11 Employee 12 PL/SQL procedure successfully completed. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> SQL>

Column value indexed cursor

SQL> SQL> SQL> 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('17DEC-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('22FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2APR-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('1MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09DEC-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('8SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created. SQL> INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC1981', 'DD-MON-YYYY'), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT( 2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); 1 row created. SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); 1 row created. SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); 1 row created. SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); 1 row created. SQL> SQL> declare 2 l_dname dept.dname%type; 3 l_deptno dept.deptno%type; 4 l_ename emp.ename%type; 5 cursor c1 is 6 select deptno, dname from dept order by deptno; 7 cursor c2( p_deptno in number ) 8 is 9 select ename 10 from emp 11 where deptno = p_deptno and sal = (select max(sal) from emp w

here 12 13 14 15 16 17 18 19 20 21 22 23 24 25

deptno = p_deptno); begin open c1; loop fetch c1 into l_deptno, l_dname; exit when c1%notfound; open c2(l_deptno); fetch c2 into l_ename; close c2; dbms_output.put_line ( l_deptno || ', ' || l_dname || ', ' || l_ename ); end loop; close c1; end; /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.

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)) wh ere 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>

Combine for loop and if statement to check the value in cursor

SQL> CREATE TABLE products(

2 3 4 5

name price min_price );

VARCHAR2(50), NUMBER(8,2), NUMBER(8,2)

Table created. SQL> SQL> SQL> create or replace procedure print_products as 2 cursor dataCursor is select name, price from products; 3 begin 4 for i in dataCursor LOOP 5 if i.price > 50 then 6 dbms_output.put_line(i.name ||' Price: '|| i.price); 7 else 8 dbms_output.put_line(i.name || ' Product under 50'); 9 end if; 10 END LOOP; 11 end; 12 / Procedure created. SQL> SQL> execute print_products PL/SQL procedure successfully completed. SQL> SQL> drop table products; Table dropped. SQL>

SQL> SQL> SQL> DECLARE 2 lv_test_bln BOOLEAN; 3 BEGIN 4 lv_test_bln := 3 BETWEEN 1 and 10; 5 IF lv_test_bln THEN 6 DBMS_OUTPUT.PUT_LINE('Result: TRUE'); 7 ELSE 8 DBMS_OUTPUT.PUT_LINE('Result: FALSE'); 9 END IF; 10 END; 11 / Result: TRUE PL/SQL procedure successfully completed.

SQL> SQL>

Between...and' operator for text value

SQL> SQL> SQL> BEGIN 2 IF 'C' BETWEEN 'A' AND 'D' THEN 3 DBMS_OUTPUT.PUT_LINE('TRUE'); 4 ELSE 5 DBMS_OUTPUT.PUT_LINE('FALSE'); 6 END IF; 7 END; 8 / TRUE PL/SQL procedure successfully completed. SQL>

Between...and' operator for Date type

SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 TRUE

DECLARE lv_test_date DATE := '01-FEB-99'; BEGIN IF lv_test_date BETWEEN '31-JAN-99' AND '10-FEB-99' THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END; /

PL/SQL procedure successfully completed. SQL>

Between...and' operator for Date type

SQL> SQL> SQL> DECLARE 2 lv_test_date DATE := '01-FEB-99';

3 BEGIN 4 IF lv_test_date BETWEEN '31-JAN-99' AND '10-FEB-99' THEN 5 DBMS_OUTPUT.PUT_LINE('TRUE'); 6 ELSE 7 DBMS_OUTPUT.PUT_LINE('FALSE'); 8 END IF; 9 END; 10 / TRUE PL/SQL procedure successfully completed. SQL>

Between...and' operator converts text to date type

SQL> SQL> SQL> DECLARE 2 lv_test_date VARCHAR2(10) := '01-FEB-99'; 3 BEGIN 4 IF lv_test_date BETWEEN '31-JAN-99' AND '10-FEB-99' THEN 5 DBMS_OUTPUT.PUT_LINE('TRUE'); 6 ELSE 7 DBMS_OUTPUT.PUT_LINE('FALSE'); 8 END IF; 9 END; 10 / FALSE PL/SQL procedure successfully completed. SQL>