#=======================================================================
# Writer
: Lokanadham
Thandlam
#=======================================================================
--Example 6–1 STATIC SQL STATEMENTS
DROP TABLE employees_temp;
CREATE
TABLE employees_temp
AS
SELECT employee_id, first_name, last_name FROM employees;
DECLARE
emp_id employees_temp.employee_id%TYPE := 299;
emp_first_name employees_temp.first_name%TYPE := 'Bob';
emp_last_name employees_temp.last_name%TYPE := 'Henry';
BEGIN
INSERT INTO employees_temp (employee_id, first_name, last_name)
VALUES (emp_id, emp_first_name, emp_last_name);
UPDATE employees_temp
SET first_name = 'Robert'
WHERE employee_id = emp_id;
DELETE FROM employees_temp
WHERE employee_id = emp_id
RETURNING first_name, last_name
INTO emp_first_name, emp_last_name;
COMMIT;
DBMS_OUTPUT.PUT_LINE
(emp_first_name || ' ' || emp_last_name);
END;
/
--Example 6–2 CURRVAL AND NEXTVAL
Pseudocolumns
DROP TABLE employees_temp;
CREATE
TABLE employees_temp
AS
SELECT employee_id, first_name, last_name FROM employees;
DROP TABLE employees_temp2;
CREATE
TABLE employees_temp2
AS
SELECT employee_id, first_name, last_name FROM employees;
DECLARE
seq_value NUMBER;
BEGIN
-- Generate initial sequence number
seq_value := employees_seq.NEXTVAL;
-- Print initial sequence number:
DBMS_OUTPUT.PUT_LINE
('Initial sequence value: ' || TO_CHAR
(seq_value));
-- Use NEXTVAL to create unique number
when inserting data:
INSERT INTO employees_temp (employee_id, first_name, last_name)
VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith');
-- Use CURRVAL to store same value
somewhere else:
INSERT INTO employees_temp2
VALUES (employees_seq.CURRVAL, 'Morgan', 'Smith');
/* Because NEXTVAL values might be
referenced
by different users and applications,
and some NEXTVAL values might not be stored in database,
there might be gaps in sequence. */
-- Use CURRVAL to specify record to
delete:
seq_value := employees_seq.CURRVAL;
DELETE FROM employees_temp2
WHERE employee_id = seq_value;
-- Udpate employee_id with NEXTVAL for
specified record:
UPDATE employees_temp
SET employee_id = employees_seq.NEXTVAL
WHERE first_name = 'Lynette' AND last_name = 'Smith';
-- Display final value of CURRVAL:
seq_value := employees_seq.CURRVAL;
DBMS_OUTPUT.PUT_LINE
('Ending sequence value: ' || TO_CHAR
(seq_value));
END;
/
--Example 6–3 SQL%FOUND Implicit CURSOR
ATTRIBUTE
DROP TABLE dept_temp;
CREATE
TABLE dept_temp
AS
SELECT * FROM departments;
CREATE
OR REPLACE
PROCEDURE p (dept_no NUMBER)
AUTHID CURRENT_USER
AS
BEGIN
DELETE FROM dept_temp
WHERE department_id = dept_no;
IF SQL%FOUND
THEN
DBMS_OUTPUT.PUT_LINE
(
'Delete succeeded for department number ' || dept_no);
ELSE
DBMS_OUTPUT.PUT_LINE
('No department number ' || dept_no);
END IF;
END;
/
BEGIN
p (270);
p (400);
END;
/
--Example 6–4 SQL%ROWCOUNT Implicit
CURSOR ATTRIBUTE
DROP TABLE employees_temp;
CREATE
TABLE employees_temp
AS
SELECT * FROM employees;
DECLARE
mgr_no NUMBER
(6) := 122;
BEGIN
DELETE FROM employees_temp
WHERE manager_id = mgr_no;
DBMS_OUTPUT.PUT_LINE
(
'Number of employees deleted: ' || TO_CHAR
(SQL%ROWCOUNT));
END;
/
--Example 6–5 Explicit CURSOR Declaration
AND Definition
DECLARE
CURSOR c1
RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2
IS
-- Declare and define c2
SELECT employee_id, job_id, salary
FROM employees
WHERE salary > 2000;
CURSOR c1
RETURN departments%ROWTYPE
IS
-- Define c1,
SELECT *
FROM departments -- repeating return type
WHERE department_id = 110;
CURSOR c3
RETURN locations%ROWTYPE; -- Declare c3
CURSOR c3
IS
-- Define c3,
SELECT *
FROM locations -- omitting return type
WHERE country_id = 'JP';
BEGIN
NULL;
END;
/
--Example 6–5 Explicit CURSOR Declaration
AND Definition
DECLARE
CURSOR c1
RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2
IS
-- Declare and define c2
SELECT employee_id, job_id, salary
FROM employees
WHERE salary > 2000;
CURSOR c1
RETURN departments%ROWTYPE
IS
-- Define c1,
SELECT *
FROM departments -- repeating return type
WHERE department_id = 110;
CURSOR c3
RETURN locations%ROWTYPE; -- Declare c3
CURSOR c3
IS
-- Define c3,
SELECT *
FROM locations -- omitting return type
WHERE
country_id = 'JP';
BEGIN
NULL;
END;
/
--Example 6–6 FETCH STATEMENTS Inside
LOOP STATEMENTS
DECLARE
CURSOR c1
IS
SELECT last_name, job_id
FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
CURSOR c2
IS
SELECT *
FROM employees
WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
ORDER BY job_id;
v_employees employees%ROWTYPE; -- record variable for row of table
BEGIN
OPEN c1;
LOOP -- Fetches 2 columns into variables
FETCH c1
INTO v_lastname, v_jobid;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(RPAD (v_lastname, 25, ' ') || v_jobid);
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE
('-------------------------------------');
OPEN c2;
LOOP
-- Fetches entire row into
the v_employees record
FETCH c2 INTO v_employees;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(
RPAD (v_employees.last_name, 25, ' ') || v_employees.job_id);
END LOOP;
CLOSE c2;
END;
/
--Example 6–7 Fetching Same Explicit
CURSOR INTO Different Variables
DECLARE
CURSOR c
IS
SELECT e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id AND e.manager_id = 100
ORDER BY last_name;
-- Record variables for rows of cursor
result set:
job1 c%ROWTYPE;
job2 c%ROWTYPE;
job3 c%ROWTYPE;
job4 c%ROWTYPE;
job5 c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO job1; -- fetches first row
FETCH c INTO job2; -- fetches second row
FETCH c INTO job3; -- fetches third row
FETCH c INTO job4; -- fetches fourth row
FETCH c INTO job5; -- fetches fifth row
CLOSE c;
DBMS_OUTPUT.PUT_LINE
(job1.job_title || ' (' || job1.job_id || ')');
DBMS_OUTPUT.PUT_LINE
(job2.job_title || ' (' || job2.job_id || ')');
DBMS_OUTPUT.PUT_LINE
(job3.job_title || ' (' || job3.job_id || ')');
DBMS_OUTPUT.PUT_LINE
(job4.job_title || ' (' || job4.job_id || ')');
DBMS_OUTPUT.PUT_LINE
(job5.job_title || ' (' || job5.job_id || ')');
END;
/
--Example 6–8 VARIABLE IN Explicit CURSOR
Query—No RESULT SET CHANGE
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER
:= 2;
CURSOR c1
IS
SELECT salary, salary * factor
FROM employees
WHERE job_id LIKE 'AD_%';
BEGIN
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1
INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('factor = ' || factor);
DBMS_OUTPUT.PUT_LINE
('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE
('sal_multiple = ' || sal_multiple);
factor := factor + 1; -- Does not affect sal_multiple
END LOOP;
CLOSE c1;
END;
/
--Example 6–9 VARIABLE IN Explicit CURSOR
Query—Result SET CHANGE
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER
:= 2;
CURSOR c1
IS
SELECT salary, salary * factor
FROM employees
WHERE job_id LIKE 'AD_%';
BEGIN
DBMS_OUTPUT.PUT_LINE
('factor = ' || factor);
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1
INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE
('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE c1;
factor := factor + 1;
DBMS_OUTPUT.PUT_LINE
('factor = ' || factor);
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1
INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE
('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE c1;
END;
/
--Example 6–10 Explicit CURSOR WITH
VIRTUAL COLUMN that Needs ALIAS
DECLARE
CURSOR c1
IS
SELECT employee_id, (salary * .05) raise
FROM employees
WHERE job_id LIKE '%_MAN'
ORDER BY employee_id;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(
'Raise for employee #'
|| emp_rec.employee_id
|| ' is $'
|| emp_rec.raise);
END LOOP;
CLOSE c1;
END;
/
--Example 6–11 Explicit CURSOR that
Accepts PARAMETERS
DECLARE
CURSOR c (job VARCHAR2, max_sal NUMBER)
IS
SELECT last_name, first_name, (salary - max_sal) overpayment
FROM employees
WHERE job_id = job AND salary > max_sal
ORDER BY salary;
PROCEDURE print_overpaid
IS
last_name_ employees.last_name%TYPE;
first_name_ employees.first_name%TYPE;
overpayment_ employees.salary%TYPE;
BEGIN
LOOP
FETCH c
INTO last_name_, first_name_, overpayment_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(
last_name_
|| ', '
||
first_name_
|| ' (by '
||
overpayment_
|| ')');
END LOOP;
END print_overpaid;
BEGIN
DBMS_OUTPUT.PUT_LINE
('----------------------');
DBMS_OUTPUT.PUT_LINE
('Overpaid Stock Clerks:');
DBMS_OUTPUT.PUT_LINE
('----------------------');
OPEN c ('ST_CLERK', 5000);
print_overpaid;
CLOSE c;
DBMS_OUTPUT.PUT_LINE
('-------------------------------');
DBMS_OUTPUT.PUT_LINE
('Overpaid Sales Representatives:');
DBMS_OUTPUT.PUT_LINE
('-------------------------------');
OPEN c ('SA_REP', 10000);
print_overpaid;
CLOSE c;
END;
/
--Example 6–12 CURSOR PARAMETERS WITH
DEFAULT VALUES
DECLARE
CURSOR c (
location NUMBER DEFAULT
1700)
IS
SELECT d.department_name, e.last_name manager, l.city
FROM departments d, employees e, locations l
WHERE l.location_id = location
AND l.location_id = d.location_id
AND d.department_id = e.department_id
ORDER BY d.department_id;
PROCEDURE print_depts
IS
dept_name departments.department_name%TYPE;
mgr_name employees.last_name%TYPE;
city_name locations.city%TYPE;
BEGIN
LOOP
FETCH c
INTO dept_name, mgr_name, city_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(dept_name || ' (Manager: ' || mgr_name || ')');
END LOOP;
END print_depts;
BEGIN
DBMS_OUTPUT.PUT_LINE
('DEPARTMENTS AT HEADQUARTERS:');
DBMS_OUTPUT.PUT_LINE
('--------------------------------');
OPEN c;
print_depts;
DBMS_OUTPUT.PUT_LINE
('--------------------------------');
CLOSE c;
DBMS_OUTPUT.PUT_LINE
('DEPARTMENTS IN CANADA:');
DBMS_OUTPUT.PUT_LINE
('--------------------------------');
OPEN c (1800);
-- Toronto
print_depts;
CLOSE c;
OPEN c (1900);
-- Whitehorse
print_depts;
CLOSE c;
END;
/
--Example 6–13 Adding Formal Parameter TO
Existing CURSOR
DECLARE
CURSOR c (job
VARCHAR2,
max_sal NUMBER,
hired DATE DEFAULT TO_DATE
('31-DEC-1999', 'DD-MON-YYYY'))
IS
SELECT last_name, first_name, (salary - max_sal) overpayment
FROM employees
WHERE job_id = job AND salary > max_sal AND hire_date > hired
ORDER BY salary;
PROCEDURE print_overpaid
IS
last_name_ employees.last_name%TYPE;
first_name_ employees.first_name%TYPE;
overpayment_ employees.salary%TYPE;
BEGIN
LOOP
FETCH c
INTO last_name_, first_name_, overpayment_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(
last_name_
|| ', '
||
first_name_
|| ' (by '
||
overpayment_
|| ')');
END LOOP;
END print_overpaid;
BEGIN
DBMS_OUTPUT.PUT_LINE
('-------------------------------');
DBMS_OUTPUT.PUT_LINE
('Overpaid Sales Representatives:');
DBMS_OUTPUT.PUT_LINE
('-------------------------------');
OPEN c ('SA_REP', 10000); -- existing reference
print_overpaid;
CLOSE c;
DBMS_OUTPUT.PUT_LINE
('------------------------------------------------');
DBMS_OUTPUT.PUT_LINE
('Overpaid Sales Representatives Hired
After 2004:');
DBMS_OUTPUT.PUT_LINE
('------------------------------------------------');
OPEN c ('SA_REP', 10000, TO_DATE ('31-DEC-2004', 'DD-MON-YYYY'));
-- new reference
print_overpaid;
CLOSE c;
END;
/
--Example 6–14 %ISOPEN Explicit CURSOR
ATTRIBUTE
DECLARE
CURSOR c1
IS
SELECT last_name, salary
FROM employees
WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF NOT c1%ISOPEN
THEN
OPEN c1;
END IF;
FETCH c1
INTO the_name, the_salary;
IF c1%ISOPEN
THEN
CLOSE c1;
END IF;
END;
--Example 6–15 %FOUND Explicit CURSOR
ATTRIBUTE
DECLARE
CURSOR c1
IS
SELECT last_name, salary
FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO my_ename, my_salary;
IF c1%FOUND
THEN
-- fetch succeeded
DBMS_OUTPUT.PUT_LINE
(
'Name = ' || my_ename || ', salary = ' || my_salary);
ELSE
-- fetch failed
EXIT;
END IF;
END LOOP;
END;
/
--Example 6–16 %NOTFOUND Explicit CURSOR
ATTRIBUTE
DECLARE
CURSOR c1
IS
SELECT last_name, salary
FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO my_ename, my_salary;
IF c1%NOTFOUND
THEN
-- fetch failed
EXIT;
ELSE
-- fetch succeeded
DBMS_OUTPUT.PUT_LINE
(
'Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/
--Example 6–17 %ROWCOUNT Explicit CURSOR
ATTRIBUTE
DECLARE
CURSOR c1
IS
SELECT last_name
FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND
OR c1%NOTFOUND
IS NULL;
DBMS_OUTPUT.PUT_LINE
(c1%ROWCOUNT
|| '. ' || name);
IF c1%ROWCOUNT = 5
THEN
DBMS_OUTPUT.PUT_LINE
('--- Fetched 5th row ---');
END IF;
END LOOP;
CLOSE c1;
END;
/
--Example 6–18 Implicit CURSOR FOR LOOP
STATEMENT
BEGIN
FOR item IN ( SELECT
last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%'
AND manager_id > 120
ORDER BY last_name)
LOOP
DBMS_OUTPUT.PUT_LINE
(
'Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
--Example 6–19 Explicit CURSOR FOR LOOP
STATEMENT
DECLARE
CURSOR c1
IS
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%'
AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE
(
'Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
--Example 6–20 PASSING PARAMETERS TO
Explicit CURSOR FOR LOOP STATEMENT
DECLARE
CURSOR c1 (job VARCHAR2, max_wage NUMBER)
IS
SELECT *
FROM employees
WHERE job_id = job AND salary > max_wage;
BEGIN
FOR person IN c1 ('ST_CLERK', 3000)
LOOP
-- process data record
DBMS_OUTPUT.PUT_LINE
(
'Name = '
|| person.last_name
|| ', salary = '
|| person.salary
|| ', Job Id = '
|| person.job_id);
END LOOP;
END;
/