ORACLE PROJECTS: PL/SQL - Sub Programs--1

#=======================================================================

# Writer : Lokanadham Thandlam

#=======================================================================

--Example 8–1 Declaring, Defining, AND Invoking A Simple PL/SQL PROCEDURE

DECLARE

first_name employees.first_name%TYPE;

last_name employees.last_name%TYPE;

email employees.email%TYPE;

employer VARCHAR2 (8) := 'AcmeCorp';

-- Declare and define procedure

PROCEDURE create_email ( -- Subprogram heading begins

name1 VARCHAR2, name2 VARCHAR2, company VARCHAR2) -- Subprogram heading ends

-- Declarative part begins

error_message VARCHAR2 (30) := 'Email address is too long.';

BEGIN -- Executable part begins

email := name1 || '.' || name2 || '@' || company;

EXCEPTION -- Exception-handling part begins

WHEN VALUE_ERROR

THEN

DBMS_OUTPUT.PUT_LINE (error_message);

END create_email;

BEGIN

first_name := 'John';

last_name := 'Doe';

create_email (first_name, last_name, employer); -- invocation

DBMS_OUTPUT.PUT_LINE ('With first name first, email is: ' || email);

create_email (last_name, first_name, employer); -- invocation

DBMS_OUTPUT.PUT_LINE ('With last name first, email is: ' || email);

first_name := 'Elizabeth';

last_name := 'MacDonald';

create_email (first_name, last_name, employer); -- invocation

END;

--Example 8–2 Declaring, Defining, AND Invoking A Simple PL/SQL FUNCTION

DECLARE

-- Declare and define function

FUNCTION square (original NUMBER) -- parameter list

RETURN NUMBER -- RETURN clause

-- Declarative part begins

original_squared NUMBER;

BEGIN -- Executable part begins

original_squared := original * original;

RETURN original_squared; -- RETURN statement

END;

BEGIN

DBMS_OUTPUT.PUT_LINE (square (100)); -- invocation

END;

--Example 8–3 Execution Resumes AFTER RETURN STATEMENT IN FUNCTION

DECLARE

x INTEGER;

FUNCTION f (n INTEGER)

RETURN INTEGER

BEGIN

RETURN (n * n);

END;

BEGIN

DBMS_OUTPUT.PUT_LINE (

'f returns ' || f (2) || '. Execution returns here (1).');

x := f (2);

DBMS_OUTPUT.PUT_LINE ('Execution returns here (2).');

END;

--Example 8–4 FUNCTION WHERE NOT Every Execution PATH Leads TO RETURN STATEMENT

CREATE OR REPLACE FUNCTION f (n INTEGER)

RETURN INTEGER

AUTHID DEFINER

BEGIN

IF n = 0

THEN

RETURN 1;

ELSIF n = 1

THEN

RETURN n;

END IF;

END;

--Example 8–5 Function Where Every Execution Path Leads to RETURN Statement

CREATE OR REPLACE FUNCTION f (n INTEGER)

RETURN INTEGER

AUTHID DEFINER

BEGIN

IF n = 0

THEN

RETURN 1;

ELSIF n = 1

THEN

RETURN n;

ELSE

RETURN n * n;

END IF;

END;

BEGIN

FOR i IN 0 .. 3

LOOP

DBMS_OUTPUT.PUT_LINE ('f(' || i || ') = ' || f (i));

END LOOP;

END;

--Example 8–6 Execution Resumes AFTER RETURN STATEMENT IN PROCEDURE

DECLARE

PROCEDURE p

BEGIN

DBMS_OUTPUT.PUT_LINE ('Inside p');

RETURN;

DBMS_OUTPUT.PUT_LINE ('Unreachable statement.');

END;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Control returns here.');

END;

--Example 8–7 Execution Resumes AFTER RETURN STATEMENT IN Anonymous BLOCK

BEGIN

DBMS_OUTPUT.PUT_LINE ('Inside inner block.');

RETURN;

DBMS_OUTPUT.PUT_LINE ('Unreachable statement.');

END;

DBMS_OUTPUT.PUT_LINE ('Inside outer block. Unreachable statement.');

END;

--Example 8–8 NESTED Subprograms Invoke EACH OTHER

DECLARE

-- Declare proc1 (forward declaration):

PROCEDURE proc1 (number1 NUMBER);

-- Declare and define proc2:

PROCEDURE proc2 (number2 NUMBER)

BEGIN

proc1 (number2);

END;

-- Define proc 1:

PROCEDURE proc1 (number1 NUMBER)

BEGIN

proc2 (number1);

END;

BEGIN

NULL;

END;

--Example 8–9 Formal PARAMETERS AND Actual PARAMETERS

DECLARE

emp_num NUMBER (6) := 120;

bonus NUMBER (6) := 100;

merit NUMBER (4) := 50;

PROCEDURE raise_salary (emp_id NUMBER, -- formal parameter

amount NUMBER -- formal parameter

)

BEGIN

UPDATE employees

SET salary = salary + amount -- reference to formal parameter

WHERE employee_id = emp_id; -- reference to formal parameter

END raise_salary;

BEGIN

raise_salary (emp_num, bonus); -- actual parameters

/* raise_salary runs this statement:

UPDATE employees

SET salary = salary + 100

WHERE employee_id = 120; */

raise_salary (emp_num, merit + bonus); -- actual parameters

/* raise_salary runs this statement:

UPDATE employees

SET salary = salary + 150

WHERE employee_id = 120; */

END;

--Example 8–10 Actual Parameter Inherits ONLY NOT NULL FROM SUBTYPE

DECLARE

SUBTYPE License IS VARCHAR2 (7) NOT NULL;

n License := 'DLLLDDD';

PROCEDURE p (x License)

BEGIN

DBMS_OUTPUT.PUT_LINE (x);

END;

BEGIN

p ('1ABC123456789'); -- Succeeds; size is not inherited

p (NULL); -- Raises error; NOT NULL is inherited

END;

--Example 8–11 Actual Parameter AND RETURN VALUE Inherit ONLY RANGE FROM SUBTYPE

DECLARE

FUNCTION test (p INTEGER)

RETURN INTEGER

BEGIN

DBMS_OUTPUT.PUT_LINE ('p = ' || p);

RETURN p;

END test;

BEGIN

DBMS_OUTPUT.PUT_LINE ('test(p) = ' || test (0.66));

END;

--Example 8–12 FUNCTION Implicitly Converts Formal Parameter TO Constrained SUBTYPE

DECLARE

FUNCTION test (p NUMBER)

RETURN NUMBER

q INTEGER := p; -- Implicitly converts p to INTEGER

BEGIN

DBMS_OUTPUT.PUT_LINE ('p = ' || q); -- Display q, not p

RETURN q; -- Return q, not p

END test;

BEGIN

DBMS_OUTPUT.PUT_LINE ('test(p) = ' || test (0.66));

END;

--Example 8–13 Avoiding Implicit Conversion OF Actual PARAMETERS

CREATE OR REPLACE PROCEDURE p (n NUMBER)

AUTHID DEFINER

BEGIN

NULL;

END;

DECLARE

x NUMBER := 1;

y VARCHAR2 (1) := '1';

BEGIN

p (x); -- No conversion needed

p (y); -- z implicitly converted from VARCHAR2 to NUMBER

p (TO_NUMBER (y)); -- z explicitly converted from VARCHAR2 to NUMBER

END;

--Example 8–14 Parameter VALUES BEFORE, During, AND AFTER PROCEDURE Invocation

CREATE OR REPLACE PROCEDURE p (a PLS_INTEGER, -- IN by default

b IN PLS_INTEGER,

c OUT PLS_INTEGER,

d IN OUT BINARY_FLOAT)

AUTHID DEFINER

BEGIN

-- Print values of parameters:

DBMS_OUTPUT.PUT_LINE ('Inside procedure p:');

DBMS_OUTPUT.PUT ('IN a = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (a), 'NULL'));

DBMS_OUTPUT.PUT ('IN b = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (b), 'NULL'));

DBMS_OUTPUT.PUT ('OUT c = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (c), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('IN OUT d = ' || TO_CHAR (d));

-- Can reference IN parameters a and b,

-- but cannot assign values to them.

c := a + 10; -- Assign value to OUT parameter

d := 10 / b; -- Assign value to IN OUT parameter

END;

DECLARE

aa CONSTANT PLS_INTEGER := 1;

bb PLS_INTEGER := 2;

cc PLS_INTEGER := 3;

dd BINARY_FLOAT := 4;

ee PLS_INTEGER;

ff BINARY_FLOAT := 5;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Before invoking procedure p:');

DBMS_OUTPUT.PUT ('aa = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (aa), 'NULL'));

DBMS_OUTPUT.PUT ('bb = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (bb), 'NULL'));

DBMS_OUTPUT.PUT ('cc = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (cc), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('dd = ' || TO_CHAR (dd));

p (aa, -- constant

bb, -- initialized variable

cc, -- initialized variable

dd -- initialized variable

);

DBMS_OUTPUT.PUT_LINE ('After invoking procedure p:');

DBMS_OUTPUT.PUT ('aa = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (aa), 'NULL'));

DBMS_OUTPUT.PUT ('bb = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (bb), 'NULL'));

DBMS_OUTPUT.PUT ('cc = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (cc), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('dd = ' || TO_CHAR (dd));

DBMS_OUTPUT.PUT_LINE ('Before invoking procedure p:');

DBMS_OUTPUT.PUT ('ee = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (ee), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('ff = ' || TO_CHAR (ff));

p (1, -- literal

(bb + 3) * 4, -- expression

ee, -- uninitialized variable

ff -- initialized variable

);

DBMS_OUTPUT.PUT_LINE ('After invoking procedure p:');

DBMS_OUTPUT.PUT ('ee = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (ee), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('ff = ' || TO_CHAR (ff));

END;

--Example 8–15 OUT AND IN OUT Parameter VALUES AFTER Unhandled EXCEPTION

DECLARE

j PLS_INTEGER := 10;

k BINARY_FLOAT := 15;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Before invoking procedure p:');

DBMS_OUTPUT.PUT ('j = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (j), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('k = ' || TO_CHAR (k));

p (4,

k); -- causes p to exit with exception ZERO_DIVIDE

EXCEPTION

WHEN ZERO_DIVIDE

THEN

DBMS_OUTPUT.PUT_LINE ('After invoking procedure p:');

DBMS_OUTPUT.PUT ('j = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (j), 'NULL'));

DBMS_OUTPUT.PUT_LINE ('k = ' || TO_CHAR (k));

END;

--Example 8–16 OUT Formal Parameter OF RECORD TYPE WITH Non-NULL DEFAULT VALUE

CREATE OR REPLACE PACKAGE r_types

AUTHID DEFINER

TYPE r_type_1 IS RECORD (f VARCHAR2 (5):= 'abcde');

TYPE r_type_2 IS RECORD (f VARCHAR2 (5));

END;

CREATE OR REPLACE PROCEDURE p (x OUT r_types.r_type_1,

y OUT r_types.r_type_2,

z OUT VARCHAR2)

AUTHID CURRENT_USER

BEGIN

DBMS_OUTPUT.PUT_LINE ('x.f is ' || NVL (x.f, 'NULL'));

DBMS_OUTPUT.PUT_LINE ('y.f is ' || NVL (y.f, 'NULL'));

DBMS_OUTPUT.PUT_LINE ('z is ' || NVL (z, 'NULL'));

END;

DECLARE

r1 r_types.r_type_1;

r2 r_types.r_type_2;

s VARCHAR2 (5) := 'fghij';

BEGIN

p (r1, r2, s);

END;

--Example 8–17 Aliasing FROM GLOBAL VARIABLE AS Actual Parameter

DECLARE

TYPE Definition IS RECORD

(

word VARCHAR2 (20),

meaning VARCHAR2 (200)

);

TYPE Dictionary IS VARRAY (2000) OF Definition;

lexicon Dictionary := Dictionary (); -- global variable

PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary -- formal NOCOPY parameter

)

BEGIN

word_list (1).word := 'aardvark';

END;

BEGIN

lexicon.EXTEND;

lexicon (1).word := 'aardwolf';

add_entry (lexicon); -- global variable is actual parameter

DBMS_OUTPUT.PUT_LINE (lexicon (1).word);

END;

--Example 8–18 Aliasing FROM Same Actual Parameter FOR Multiple Formal PARAMETERS

DECLARE

n NUMBER := 10;

PROCEDURE p (n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER)

BEGIN

n2 := 20; -- actual parameter is 20 only after procedure succeeds

DBMS_OUTPUT.put_line (n1); -- actual parameter value is still 10

n3 := 30; -- might change actual parameter immediately

DBMS_OUTPUT.put_line (n1); -- actual parameter value is either 10 or 30

END;

BEGIN

p (n, n, n);

DBMS_OUTPUT.put_line (n);

END;

--Example 8–19 Aliasing FROM CURSOR VARIABLE Subprogram PARAMETERS

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

c1 EmpCurTyp;

c2 EmpCurTyp;

PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp,

emp_cv2 IN OUT EmpCurTyp)

emp_rec employees%ROWTYPE;

BEGIN

OPEN emp_cv1 FOR SELECT * FROM employees;

emp_cv2 := emp_cv1; -- now both variables refer to same location

FETCH emp_cv1 INTO emp_rec; -- fetches first row of employees

FETCH emp_cv1 INTO emp_rec; -- fetches second row of employees

FETCH emp_cv2 INTO emp_rec; -- fetches third row of employees

CLOSE emp_cv1; -- closes both variables

FETCH emp_cv2 INTO emp_rec; -- causes error when get_emp_data is invoked

END;

BEGIN

get_emp_data (c1, c2);

END;

--Example 8–20 PROCEDURE WITH DEFAULT Parameter VALUES

DECLARE

PROCEDURE raise_salary (emp_id IN employees.employee_id%TYPE,

amount IN employees.salary%TYPE := 100,

extra IN employees.salary%TYPE := 50)

BEGIN

UPDATE employees

SET salary = salary + amount + extra

WHERE employee_id = emp_id;

END raise_salary;

BEGIN

raise_salary (120); -- same as raise_salary(120, 100, 50)

raise_salary (121, 200); -- same as raise_salary(121, 200, 50)

END;

--Example 8–21 FUNCTION Provides DEFAULT Parameter VALUE

DECLARE

global PLS_INTEGER := 0;

FUNCTION f

RETURN PLS_INTEGER

BEGIN

DBMS_OUTPUT.PUT_LINE ('Inside f.');

global := global + 1;

RETURN global * 2;

END f;

PROCEDURE p (x IN PLS_INTEGER := f ())

BEGIN

DBMS_OUTPUT.PUT_LINE (

'Inside p. ' || ' global = ' || global || ', x = ' || x || '.');

DBMS_OUTPUT.PUT_LINE ('--------------------------------');

END p;

PROCEDURE pre_p

BEGIN

DBMS_OUTPUT.PUT_LINE ('Before invoking p, global = ' || global || '.');

DBMS_OUTPUT.PUT_LINE ('Invoking p.');

END pre_p;

BEGIN

pre_p;

p (); -- default expression is evaluated

pre_p;

p (100); -- default expression is not evaluated

pre_p;

p (); -- default expression is evaluated

END;

--Example 8–22 Adding Subprogram Parameter WITHOUT Changing Existing Invocations

--CREATE PROCEDURE :

CREATE OR REPLACE PROCEDURE print_name (FIRST VARCHAR2, LAST VARCHAR2)

AUTHID DEFINER

BEGIN

DBMS_OUTPUT.PUT_LINE (FIRST || ' ' || LAST);

END print_name;

--Invoke PROCEDURE:

BEGIN

print_name ('John', 'Doe');

END;

--RESULT:

--John Doe

--ADD third parameter WITH DEFAULT VALUE:

CREATE OR REPLACE PROCEDURE print_name (FIRST VARCHAR2,

LAST VARCHAR2,

mi VARCHAR2 := NULL)

AUTHID DEFINER

BEGIN

IF mi IS NULL

THEN

DBMS_OUTPUT.PUT_LINE (FIRST || ' ' || LAST);

ELSE

DBMS_OUTPUT.PUT_LINE (FIRST || ' ' || mi || '. ' || LAST);

END IF;

END print_name;

--Invoke PROCEDURE:

BEGIN

print_name ('John', 'Doe'); -- original invocation

print_name ('John', 'Public', 'Q'); -- new invocation

END;

ORACLE PROJECTS

MENUS

Tuesday, 14 January 2014

PL/SQL - Sub Programs--1