ORACLE PROJECTS: PL/SQL Sub Programs

--Example 8–23 Equivalent Invocations WITH Different Notations IN Anonymous BLOCK

DECLARE

emp_num NUMBER (6) := 120;

bonus NUMBER (6) := 50;

PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER)

BEGIN

UPDATE employees

SET salary = salary + amount

WHERE employee_id = emp_id;

END raise_salary;

BEGIN

-- Equivalent invocations:

raise_salary (emp_num, bonus); -- positional notation

raise_salary (amount => bonus, emp_id => emp_num); -- named notation

raise_salary (emp_id => emp_num, amount => bonus); -- named notation

raise_salary (emp_num, amount => bonus); -- mixed notation

END;

--Example 8–24 Equivalent Invocations WITH Different Notations IN SELECT STATEMENTS

CREATE OR REPLACE FUNCTION compute_bonus (emp_id NUMBER, bonus NUMBER)

RETURN NUMBER

AUTHID DEFINER

emp_sal NUMBER;

BEGIN

SELECT salary

INTO emp_sal

FROM employees

WHERE employee_id = emp_id;

RETURN emp_sal + bonus;

END compute_bonus;

SELECT compute_bonus (120, 50) FROM DUAL; -- positional

SELECT compute_bonus (bonus => 50, emp_id => 120) FROM DUAL; -- named

SELECT compute_bonus (120, bonus => 50) FROM DUAL; -- mixed

--Example 8–25 Resolving PL/SQL PROCEDURE Names

DECLARE

PROCEDURE swap (n1 NUMBER, n2 NUMBER)

num1 NUMBER;

num2 NUMBER;

FUNCTION balance (bal NUMBER)

RETURN NUMBER

x NUMBER := 10;

PROCEDURE swap (d1 DATE, d2 DATE)

BEGIN

NULL;

END;

PROCEDURE swap (b1 BOOLEAN, b2 BOOLEAN)

BEGIN

NULL;

END;

BEGIN -- balance

swap (num1, num2);

RETURN x;

END balance;

BEGIN -- enclosing procedure swap

NULL;

END swap;

BEGIN -- anonymous block

NULL;

END; -- anonymous block

--Example 8–26 Overloaded Subprogram

DECLARE

TYPE date_tab_typ IS TABLE OF DATE

INDEX BY PLS_INTEGER;

TYPE num_tab_typ IS TABLE OF NUMBER

INDEX BY PLS_INTEGER;

hiredate_tab date_tab_typ;

sal_tab num_tab_typ;

PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER)

BEGIN

DBMS_OUTPUT.PUT_LINE ('Invoked first version');

FOR i IN 1 .. n

LOOP

tab (i) := SYSDATE;

END LOOP;

END initialize;

PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER)

BEGIN

DBMS_OUTPUT.PUT_LINE ('Invoked second version');

FOR i IN 1 .. n

LOOP

tab (i) := 0.0;

END LOOP;

END initialize;

BEGIN

initialize (hiredate_tab, 50);

initialize (sal_tab, 100);

END;

--Example 8–27 Overload ERROR Causes COMPILE-TIME ERROR

CREATE OR REPLACE PACKAGE pkg1

AUTHID DEFINER

PROCEDURE s (p VARCHAR2);

END pkg1;

--Example 8–28 Overload ERROR Compiles Successfully

CREATE OR REPLACE PACKAGE pkg2

AUTHID DEFINER

SUBTYPE t1 IS VARCHAR2 (10);

SUBTYPE t2 IS VARCHAR2 (10);

PROCEDURE s (p t1);

PROCEDURE s (p t2);

END pkg2;

--Example 8–29 Invoking Subprogram IN Example 8–28 Causes COMPILE-TIME ERROR

CREATE OR REPLACE PROCEDURE p

AUTHID DEFINER

a pkg2.t1 := 'a';

BEGIN

pkg2.s (a); -- Causes compile-time error PLS-00307

END p;

--Example 8–30 Correcting Overload ERROR IN Example 8–28

CREATE OR REPLACE PACKAGE pkg2

AUTHID DEFINER

SUBTYPE t1 IS VARCHAR2 (10);

SUBTYPE t2 IS VARCHAR2 (10);

PROCEDURE s (p1 t1);

PROCEDURE s (p2 t2);

END pkg2;

--Example 8–31 Invoking Subprogram IN Example 8–30

CREATE OR REPLACE PROCEDURE p

AUTHID DEFINER

a pkg2.t1 := 'a';

BEGIN

pkg2.s (p1 => a); -- Compiles without error

END p;

--Example 8–32 PACKAGE SPECIFICATION WITHOUT Overload ERRORS

CREATE OR REPLACE PACKAGE pkg3

AUTHID DEFINER

PROCEDURE s (p1 VARCHAR2);

PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2');

END pkg3;

--Example 8–33 Improper Invocation OF Properly Overloaded Subprogram

CREATE OR REPLACE PROCEDURE p

AUTHID DEFINER

a1 VARCHAR2 (10) := 'a1';

a2 VARCHAR2 (10) := 'a2';

BEGIN

pkg3.s (p1 => a1, p2 => a2); -- Compiles without error

pkg3.s (p1 => a1); -- Causes compile-time error PLS-00307

END p;

--Example 8–34 Implicit Conversion OF PARAMETERS Causes Overload ERROR

CREATE OR REPLACE PACKAGE pack1

AUTHID DEFINER

PROCEDURE proc1 (a NUMBER, b VARCHAR2);

PROCEDURE proc1 (a NUMBER, b NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY pack1

PROCEDURE proc1 (a NUMBER, b VARCHAR2)

BEGIN

NULL;

END;

PROCEDURE proc1 (a NUMBER, b NUMBER)

BEGIN

NULL;

END;

BEGIN

pack1.proc1 (1, '2'); -- Compiles without error

pack1.proc1 (1, 2); -- Compiles without error

pack1.proc1 ('1', '2'); -- Causes compile-time error PLS-00307

pack1.proc1 ('1', 2); -- Causes compile-time error PLS-00307

END;

--Example 8–35 Recursive FUNCTION Returns n Factorial (n!)

CREATE OR REPLACE FUNCTION factorial (n POSITIVE)

RETURN POSITIVE

AUTHID DEFINER

BEGIN

IF n = 1

THEN -- terminating condition

RETURN n;

ELSE

RETURN n * factorial (n - 1); -- recursive invocation

END IF;

END;

BEGIN

FOR i IN 1 .. 5

LOOP

DBMS_OUTPUT.PUT_LINE (i || '! = ' || factorial (i));

END LOOP;

END;

--Example 8–36 Recursive FUNCTION Returns nth Fibonacci NUMBER

CREATE OR REPLACE FUNCTION fibonacci (n PLS_INTEGER)

RETURN PLS_INTEGER

AUTHID DEFINER

fib_1 PLS_INTEGER := 0;

fib_2 PLS_INTEGER := 1;

BEGIN

IF n = 1

THEN -- terminating condition

RETURN fib_1;

ELSIF n = 2

THEN

RETURN fib_2; -- terminating condition

ELSE

RETURN fibonacci (n - 2) + fibonacci (n - 1); -- recursive invocations

END IF;

END;

BEGIN

FOR i IN 1 .. 10

LOOP

DBMS_OUTPUT.PUT (fibonacci (i));

IF i < 10

THEN

DBMS_OUTPUT.PUT (', ');

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE (' ...');

END;

--Example 8–37 Declaring AND Defining RESULT-Cached FUNCTION

CREATE OR REPLACE PACKAGE department_pkg

AUTHID DEFINER

TYPE dept_info_record IS RECORD

(

dept_name departments.department_name%TYPE,

mgr_name employees.last_name%TYPE,

dept_size PLS_INTEGER

);

-- Function declaration

FUNCTION get_dept_info (dept_id NUMBER)

RETURN dept_info_record

RESULT_CACHE;

END department_pkg;

CREATE OR REPLACE PACKAGE BODY department_pkg

-- Function definition

FUNCTION get_dept_info (dept_id NUMBER)

RETURN dept_info_record

RESULT_CACHE

rec dept_info_record;

BEGIN

SELECT department_name

INTO rec.dept_name

FROM departments

WHERE department_id = dept_id;

SELECT e.last_name

INTO rec.mgr_name

FROM departments d, employees e

WHERE d.department_id = dept_id AND d.manager_id = e.employee_id;

SELECT COUNT (*)

INTO rec.dept_size

FROM EMPLOYEES

WHERE department_id = dept_id;

RETURN rec;

END get_dept_info;

END department_pkg;

-- Global Configuration Settings

DROP TABLE global_config_params;

CREATE TABLE global_config_params

(

name VARCHAR2 (20), -- parameter NAME

val VARCHAR2 (20), -- parameter VALUE

PRIMARY KEY (name)

);

-- Application-Level Configuration Settings

CREATE TABLE app_level_config_params

(

app_id VARCHAR2 (20), -- application ID

name VARCHAR2 (20), -- parameter NAME

val VARCHAR2 (20), -- parameter VALUE

PRIMARY KEY (app_id, name)

);

-- Role-Level Configuration Settings

CREATE TABLE role_level_config_params

(

role_id VARCHAR2 (20), -- application (role) ID

name VARCHAR2 (20), -- parameter NAME

val VARCHAR2 (20), -- parameter VALUE

PRIMARY KEY (role_id, name)

);

--Example 8–38 RESULT-Cached FUNCTION Returns Configuration Parameter Setting

CREATE OR REPLACE FUNCTION get_value (p_param VARCHAR2,

p_app_id NUMBER,

p_role_id NUMBER)

RETURN VARCHAR2

RESULT_CACHE

AUTHID DEFINER

answer VARCHAR2 (20);

BEGIN

-- Is parameter set at role level?

BEGIN

SELECT val

INTO answer

FROM role_level_config_params

WHERE role_id = p_role_id AND name = p_param;

RETURN answer; -- Found

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL; -- Fall through to following code

END;

-- Is parameter set at application level?

BEGIN

SELECT val

INTO answer

FROM app_level_config_params

WHERE app_id = p_app_id AND name = p_param;

RETURN answer; -- Found

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL; -- Fall through to following code

END;

-- Is parameter set at global level?

SELECT val

INTO answer

FROM global_config_params

WHERE name = p_param;

RETURN answer;

END;

CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)

RETURN NUMBER

RESULT_CACHE

AUTHID DEFINER

BEGIN

IF (n = 0) OR (n = 1)

THEN

RETURN 1;

ELSE

RETURN fibonacci (n - 1) + fibonacci (n - 2);

END IF;

END;

--Example 8–39 RESULT-Cached FUNCTION Handles SESSION-Specific SETTINGS

CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER)

RETURN VARCHAR

RESULT_CACHE

AUTHID DEFINER

date_hired DATE;

BEGIN

SELECT hire_date

INTO date_hired

FROM HR.EMPLOYEES

WHERE EMPLOYEE_ID = emp_id;

RETURN TO_CHAR (date_hired);

END;

--If a common format is acceptable to all sessions, specify a format mask, removing the dependency on NLS_DATE_FORMAT. For example:

--TO_CHAR(date_hired, 'mm/dd/yy');

--Add a format mask parameter to get_hire_date. For example:

CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER, fmt VARCHAR)

RETURN VARCHAR

RESULT_CACHE

AUTHID DEFINER

date_hired DATE;

BEGIN

SELECT hire_date

INTO date_hired

FROM HR.EMPLOYEES

WHERE EMPLOYEE_ID = emp_id;

RETURN TO_CHAR (date_hired, fmt);

END;

--In Example 8–40, assume that a table, config_tab, has a VPD policy that translates this query:

SELECT VALUE

FROM config_tab

WHERE name = param_name;

--To this query:

SELECT VALUE

FROM config_tab

WHERE name = param_name AND app_id = SYS_CONTEXT ('Config', 'App_ID');

--Example 8–40 Result-Cached Function Handles Session-Specific Application Context

CREATE OR REPLACE FUNCTION get_param_value (

param_name VARCHAR,

appctx VARCHAR DEFAULT SYS_CONTEXT ('Config', 'App_ID'))

RETURN VARCHAR

RESULT_CACHE

AUTHID DEFINER

rec VARCHAR (2000);

BEGIN

SELECT val

INTO rec

FROM config_tab

WHERE name = param_name;

RETURN rec;

END;

--Example 8–41 Caching One NAME AT A TIME (Finer Granularity)

CREATE OR REPLACE FUNCTION get_product_name_1 (prod_id NUMBER,

lang_id VARCHAR2)

RETURN NVARCHAR2

RESULT_CACHE

AUTHID DEFINER

result_ VARCHAR2 (50);

BEGIN

SELECT translated_name

INTO result_

FROM OE.Product_Descriptions

WHERE PRODUCT_ID = prod_id AND LANGUAGE_ID = lang_id;

RETURN result_;

END;

--Example 8–42 Caching Translated Names One LANGUAGE AT A TIME (Coarser Granularity)

CREATE OR REPLACE FUNCTION get_product_name_2 (prod_id NUMBER,

lang_id VARCHAR2)

RETURN NVARCHAR2

AUTHID DEFINER

TYPE product_names IS TABLE OF NVARCHAR2 (50)

INDEX BY PLS_INTEGER;

FUNCTION all_product_names (lang_id VARCHAR2)

RETURN product_names

RESULT_CACHE

all_names product_names;

BEGIN

FOR c IN (SELECT *

FROM OE.Product_Descriptions

WHERE LANGUAGE_ID = lang_id)

LOOP

all_names (c.PRODUCT_ID) := c.TRANSLATED_NAME;

END LOOP;

RETURN all_names;

END;

BEGIN

RETURN all_product_names (lang_id) (prod_id);

END;

--Example 8–43 PL/SQL Anonymous BLOCK Invokes EXTERNAL PROCEDURE

-- Publish Adjuster.raiseSalary as standalone PL/SQL procedure:

CREATE OR REPLACE PROCEDURE raise_salary (empid NUMBER, pct NUMBER)

LANGUAGE JAVA

NAME 'Adjuster.raiseSalary (int, float)'; -- call specification

BEGIN

raise_salary (120, 10); -- invoke Adjuster.raiseSalary by PL/SQL name

END;

--Example 8–44 PL/SQL STANDALONE PROCEDURE Invokes EXTERNAL PROCEDURE

-- Java call specification:

CREATE PROCEDURE java_sleep (milli_seconds IN NUMBER)

LANGUAGE JAVA

NAME 'java.lang.Thread.sleep(long)';

CREATE OR REPLACE PROCEDURE sleep (milli_seconds IN NUMBER)

AUTHID DEFINER

BEGIN

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.get_time ());

java_sleep (milli_seconds);

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.get_time ());

END;

ORACLE PROJECTS

MENUS

Tuesday, 14 January 2014

PL/SQL Sub Programs - 2