PU Chapter 08 Design Considerations for PL/SQL Code Scripts
Example (PU_08_01)
CREATE OR REPLACE PACKAGE error_pkg IS
fk_err EXCEPTION;
seq_nbr_err EXCEPTION;
PRAGMA EXCEPTION_INIT (fk_err, -2292);
PRAGMA EXCEPTION_INIT (seq_nbr_err, -2277);
END error_pkg;
/
CREATE OR REPLACE PROCEDURE PU_08_01
(p_department_id departments.department_id%TYPE)
IS
fk_err EXCEPTION;
PRAGMA EXCEPTION_INIT(fk_err, -2292);
BEGIN
DELETE
FROM departments
WHERE department_id = p_department_id;
DBMS_OUTPUT.PUT_LINE('The Department no '||p_department_id ||' is deleted.');
EXCEPTION
--WHEN fk_err THEN
WHEN error_pkg.fk_err THEN
DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||
TO_CHAR(p_department_id)|| '. Employees exist. ');
END PU_08_01;
-------------------------------------------------------------------------------------
Example (PU_08_02)
CREATE OR REPLACE PACKAGE constant_pkg IS
c_order_received CONSTANT VARCHAR(2) := 'OR';
c_order_shipped CONSTANT VARCHAR(2) := 'OS';
c_min_sal CONSTANT NUMBER(3) := 900;
END constant_pkg;
/
BEGIN
UPDATE employees
SET salary = salary + 200
WHERE salary <= constant_pkg.c_min_sal;
END;
-------------------------------------------------------------------------------------
Example (PU_08_03)
CREATE PROCEDURE employee_sal(id NUMBER) IS
emp employees%ROWTYPE;
FUNCTION tax(salary VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN salary * 0.825;
END tax;
BEGIN
SELECT * INTO emp
FROM EMPLOYEES WHERE employee_id = id;
DBMS_OUTPUT.PUT_LINE('Tax: '||tax(emp.salary));
END;
-------------------------------------------------------------------------------------
Example (PU_08_04)
CREATE TABLE C_1 (cust_id number (4), total_sales NUMBER (8,2));
CREATE TABLE C_2 (cust_id number(4), sales_date date);
CREATE OR REPLACE PROCEDURE cust_log (p_cust_id number)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO c_2 values (p_cust_id, sysdate);
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE cust_sales (p_cust_id number, p_total number)
IS
BEGIN
cust_log(p_cust_id);
INSERT INTO c_1 VALUES (p_cust_id, p_total);
END;
-------------------------------------------------------------------------------------
Example (PU_08_05)
CREATE PROCEDURE update_salary(emp_id NUMBER) IS
name employees.last_name%TYPE;
new_sal employees.salary%TYPE;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = emp_id
RETURNING last_name, salary INTO name, new_sal;
END update_salary;
-------------------------------------------------------------------------------------
Example (PU_08_06)
CREATE PROCEDURE raise_salary(percent NUMBER) IS
TYPE numlist IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
id numlist;
BEGIN
id(1) := 100; id(2) := 102;
id(3) := 106; id(3) := 110;
FORALL i IN id.FIRST .. id.LAST
UPDATE employees
SET salary = (1 + percent/100) * salary
WHERE manager_id = id(i);
END;
EXECUTE raise_salary(10)
-------------------------------------------------------------------------------------
Example (PU_08_07)
CREATE TABLE num_table (n NUMBER);
DECLARE
TYPE NumList IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
nums NumList;
BEGIN
nums(1) := 1;
nums(2) := 3;
nums(3) := 5;
nums(4) := 7;
nums(5) := 11;
FORALL i IN nums.FIRST .. nums.LAST
INSERT INTO num_table (n) VALUES (nums(i));
FOR i IN nums.FIRST .. nums.LAST
LOOP
dbms_output.put_line('Inserted ' ||
SQL%BULK_ROWCOUNT(i) || ' row(s)'
|| ' on iteration ' || i);
END LOOP;
END;
/
-------------------------------------------------------------------------------------
Example (PU_08_08)
CREATE OR REPLACE PROCEDURE get_departments(loc NUMBER) IS
TYPE dept_tabtype IS
TABLE OF departments%ROWTYPE;
depts dept_tabtype;
BEGIN
SELECT * BULK COLLECT INTO depts
FROM departments
WHERE location_id = loc;
FOR I IN 1 .. depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(depts(i).department_id
||' '||depts(i).department_name);
END LOOP;
END;
-------------------------------------------------------------------------------------
Example (PU_08_09)
CREATE OR REPLACE PROCEDURE get_departments(loc NUMBER) IS
CURSOR dept_csr IS SELECT * FROM departments
WHERE location_id = loc;
TYPE dept_tabtype IS TABLE OF dept_csr%ROWTYPE;
depts dept_tabtype;
BEGIN
OPEN dept_csr;
FETCH dept_csr BULK COLLECT INTO depts;
CLOSE dept_csr;
FOR I IN 1 .. depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(depts(i).department_id
||' '||depts(i).department_name);
END LOOP;
END;
-------------------------------------------------------------------------------------
