ORACLE PROJECTS: PLSQL Language Fundamentals

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

# Author : Lokanadham Thandlam

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

--Example 2–1 Valid Case-Insensitive Reference to Quoted User-Defined Identifier

DECLARE

"HELLO" VARCHAR2 (10) := 'hello';

BEGIN

DBMS_OUTPUT.Put_Line (Hello);

END;

--Example 2–2 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier

DECLARE

"HELLO" VARCHAR2 (10) := 'hello';

BEGIN

DBMS_OUTPUT.Put_Line ("Hello");

END;

--Example 2–3 Reserved Word as Quoted User-Defined Identifier

DECLARE

"BEGIN" VARCHAR2 (15) := 'UPPERCASE';

"Begin" VARCHAR2 (15) := 'Initial Capital';

"begin" VARCHAR2 (15) := 'lowercase';

BEGIN

DBMS_OUTPUT.Put_Line ("BEGIN");

DBMS_OUTPUT.Put_Line ("Begin");

DBMS_OUTPUT.Put_Line ("begin");

END;

--Example 2–4 Neglecting Double Quotation Marks

DECLARE

"HELLO" VARCHAR2 (10) := 'hello'; -- HELLO is not a reserved word

"BEGIN" VARCHAR2 (10) := 'begin'; -- BEGIN is a reserved word

BEGIN

DBMS_OUTPUT.Put_Line (Hello); -- Double quotation marks are optional

DBMS_OUTPUT.Put_Line (BEGIN); -- Double quotation marks are required

END;

--Example 2–5 Neglecting Case-Sensitivity

DECLARE

"HELLO" VARCHAR2 (10) := 'hello'; -- HELLO is not a reserved word

"BEGIN" VARCHAR2 (10) := 'begin'; -- BEGIN is a reserved word

BEGIN

DBMS_OUTPUT.Put_Line (Hello); -- Identifier is case-insensitive

DBMS_OUTPUT.Put_Line ("Begin"); -- Identifier is case-sensitive

END;

--Example 2–6 Single-Line Comments

DECLARE

howmany NUMBER;

num_tables NUMBER;

BEGIN

-- Begin processing

SELECT COUNT (*)

INTO howmany

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables

num_tables := howmany; -- Compute another value

DBMS_OUTPUT.Put_Line (num_tables);

END;

--Example 2–7 Multiline Comments

DECLARE

some_condition BOOLEAN;

pi NUMBER := 3.1415926;

radius NUMBER := 15;

area NUMBER;

BEGIN

/* Perform some simple tests and assignments */

IF 2 + 2 = 4

THEN

some_condition := TRUE;

/* We expect this THEN to always be performed */

END IF;

/* This line computes the area of a circle using pi,

which is the ratio between the circumference and diameter.

After the area is computed, the result is displayed. */

area := pi * radius ** 2;

DBMS_OUTPUT.PUT_LINE ('The area is: ' || TO_CHAR (area));

END;

--Example 2–8 Whitespace Characters Improving Source Text Readability

DECLARE

x NUMBER := 10;

y NUMBER := 5;

MAX NUMBER;

BEGIN

IF x>y THEN max:=x;ELSE max:=y;END IF; -- correct but hard to read

-- Easier to read:

IF x > y

THEN

MAX := x;

ELSE

MAX := y;

END IF;

END;

--Example 2–9 Scalar Variable Declarations

DECLARE

part_number NUMBER (6); -- SQL data type

part_name VARCHAR2 (20); -- SQL data type

in_stock BOOLEAN; -- PL/SQL-only data type

part_price NUMBER (6, 2); -- SQL data type

part_description VARCHAR2 (50); -- SQL data type

BEGIN

NULL;

END;

--Example 2–10 Constant Declarations

DECLARE

credit_limit CONSTANT REAL := 5000.00; -- SQL data type

max_days_in_year CONSTANT INTEGER := 366; -- SQL data type

urban_legend CONSTANT BOOLEAN := FALSE; -- PL/SQL-only data type

BEGIN

NULL;

END;

--Example 2–11 Variable and Constant Declarations with Initial Values

DECLARE

hours_worked INTEGER := 40;

employee_count INTEGER := 0;

pi CONSTANT REAL := 3.14159;

radius REAL := 1;

area REAL := (pi * radius ** 2);

BEGIN

NULL;

END;

--Example 2–12 Variable Initialized to NULL by Default

DECLARE

counter INTEGER; -- initial value is NULL by default

BEGIN

counter := counter + 1; -- NULL + 1 is still NULL

IF counter IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('counter is NULL.');

END IF;

END;

--Example 2–13 Variable Declaration with NOT NULL Constraint

DECLARE

acct_id INTEGER (4) NOT NULL := 9999;

a NATURALN := 9999;

b POSITIVEN := 9999;

c SIMPLE_INTEGER := 9999;

BEGIN

NULL;

END;

--Example 2–14 Variables Initialized to NULL Values

DECLARE

null_string VARCHAR2 (80) := TO_CHAR ('');

address VARCHAR2 (80);

zip_code VARCHAR2 (80) := SUBSTR (address, 25, 0);

name VARCHAR2 (80);

valid BOOLEAN := (name != '');

BEGIN

NULL;

END;

--Example 2–15 Declaring Variable of Same Type as Column

DECLARE

surname emp.ename%TYPE;

BEGIN

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

END;

--Example 2–16 Declaring Variable of Same Type as Another Variable

DECLARE

name VARCHAR (25) NOT NULL := 'Smith';

surname name%TYPE := 'Jones';

BEGIN

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

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

END;

--Example 2–17 Scope and Visibility of Identifiers

-- Outer block:

DECLARE

a CHAR; -- Scope of a (CHAR) begins

b REAL; -- Scope of b begins

BEGIN

-- Visible: a (CHAR), b

-- First sub-block:

DECLARE

a INTEGER; -- Scope of a (INTEGER) begins

c REAL; -- Scope of c begins

BEGIN

-- Visible: a (INTEGER), b, c

NULL;

END; -- Scopes of a (INTEGER) and c end

-- Second sub-block:

DECLARE

d REAL; -- Scope of d begins

BEGIN

-- Visible: a (CHAR), b, d

NULL;

END; -- Scope of d ends

-- Visible: a (CHAR), b

END; -- Scopes of a (CHAR) and b end

--Example 2–18 Qualifying Redeclared Global Identifier with Block Label

<<outer>> -- label

DECLARE

birthdate DATE := TO_DATE ('09-AUG-70', 'DD-MON-YY');

BEGIN

DECLARE

birthdate DATE := TO_DATE ('29-SEP-70', 'DD-MON-YY');

BEGIN

IF birthdate = outer.birthdate

THEN

DBMS_OUTPUT.PUT_LINE ('Same Birthday');

ELSE

DBMS_OUTPUT.PUT_LINE ('Different Birthday');

END IF;

END;

--Example 2–19 Qualifying Identifier with Subprogram Name

CREATE OR REPLACE PROCEDURE check_credit (credit_limit NUMBER)

rating NUMBER := 3;

FUNCTION check_rating

RETURN BOOLEAN

rating NUMBER := 1;

over_limit BOOLEAN;

BEGIN

IF check_credit.rating <= credit_limit

THEN -- reference global variable

over_limit := FALSE;

ELSE

over_limit := TRUE;

rating := credit_limit; -- reference local variable

END IF;

RETURN over_limit;

END check_rating;

BEGIN

IF check_rating

THEN

DBMS_OUTPUT.PUT_LINE (

'Credit rating over limit ('

|| TO_CHAR (credit_limit)

|| '). '

|| 'Rating: '

|| TO_CHAR (rating));

ELSE

DBMS_OUTPUT.PUT_LINE (

'Credit rating OK. ' || 'Rating: ' || TO_CHAR (rating));

END IF;

END;

BEGIN

check_credit(1);

END;

--Example 2–20 Duplicate Identifiers in Same Scope

DECLARE

id BOOLEAN;

id VARCHAR2 (5); -- duplicate identifier

BEGIN

id := FALSE;

END;

--Example 2–21 Declaring Same Identifier in Different Units

DECLARE

PROCEDURE p

x VARCHAR2 (1);

BEGIN

x := 'a'; -- Assign the value 'a' to x

DBMS_OUTPUT.PUT_LINE ('In procedure p, x = ' || x);

END;

PROCEDURE q

x VARCHAR2 (1);

BEGIN

x := 'b'; -- Assign the value 'b' to x

DBMS_OUTPUT.PUT_LINE ('In procedure q, x = ' || x);

END;

BEGIN

END;

--Example 2–22 Label and Subprogram with Same Name in Same Scope

<<echo>>

DECLARE

x NUMBER := 5;

PROCEDURE echo

x NUMBER := 0;

BEGIN

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

DBMS_OUTPUT.PUT_LINE ('echo.x = ' || echo.x);

END;

BEGIN

echo;

END;

--Example 2–23 Block with Multiple and Duplicate Labels

<<compute_ratio>>

<<another_label>>

DECLARE

numerator NUMBER := 22;

denominator NUMBER := 7;

BEGIN

<<another_label>>

DECLARE

denominator NUMBER := 0;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Ratio with compute_ratio.denominator = ');

DBMS_OUTPUT.PUT_LINE (numerator / compute_ratio.denominator);

DBMS_OUTPUT.PUT_LINE ('Ratio with another_label.denominator = ');

DBMS_OUTPUT.PUT_LINE (numerator / another_label.denominator);

EXCEPTION

WHEN ZERO_DIVIDE

THEN

DBMS_OUTPUT.PUT_LINE (

'Divide-by-zero error: can''t divide '

|| numerator

|| ' by '

|| denominator);

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE ('Unexpected error.');

END another_label;

END compute_ratio;

--Example 2–24 Assigning Values to Variables with Assignment Statement

DECLARE -- You can assign initial values here

wages NUMBER;

hours_worked NUMBER := 40;

hourly_salary NUMBER := 22.50;

bonus NUMBER := 150;

country VARCHAR2 (128);

counter NUMBER := 0;

done BOOLEAN;

valid_id BOOLEAN;

emp_rec1 employees%ROWTYPE;

emp_rec2 employees%ROWTYPE;

TYPE commissions IS TABLE OF NUMBER

INDEX BY PLS_INTEGER;

comm_tab commissions;

BEGIN -- You can assign values here too

wages := (hours_worked * hourly_salary) + bonus;

country := 'France';

country := UPPER ('Canada');

done := (counter > 100);

valid_id := TRUE;

emp_rec1.first_name := 'Antonio';

emp_rec1.last_name := 'Ortiz';

emp_rec1 := emp_rec2;

comm_tab (5) := 20000 * 0.15;

END;

--Example 2–25 Assigning Value to Variable with SELECT INTO Statement

DECLARE

bonus NUMBER (8, 2);

BEGIN

SELECT sal * 0.10

INTO bonus

FROM emp

WHERE empno = 7499;

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

END;

--Example 2–26 Assigning Value to Variable as IN OUT Subprogram Parameter

DECLARE

emp_salary NUMBER (8, 2);

PROCEDURE adjust_salary (emp NUMBER, sal IN OUT NUMBER, adjustment NUMBER)

BEGIN

sal := sal + adjustment;

END;

BEGIN

SELECT sal

INTO emp_salary

FROM emp

WHERE empno = 7499;

DBMS_OUTPUT.PUT_LINE (

'Before invoking procedure, emp_salary: ' || emp_salary);

adjust_salary (100, emp_salary, 1000);

DBMS_OUTPUT.PUT_LINE (

'After invoking procedure, emp_salary: ' || emp_salary);

END;

--Example 2–27 Assigning Value to BOOLEAN Variable

DECLARE

done BOOLEAN; -- Initial value is NULL by default

counter NUMBER := 0;

BEGIN

done := FALSE; -- Assign literal value

WHILE done != TRUE -- Compare to literal value

LOOP

counter := counter + 1;

done := (counter > 500); -- Assign value of BOOLEAN expression

END LOOP;

END;

--Example 2–28 Concatenation Operator

DECLARE

x VARCHAR2 (4) := 'suit';

y VARCHAR2 (4) := 'case';

BEGIN

DBMS_OUTPUT.PUT_LINE (x || y);

END;

--Example 2–29 Concatenation Operator with NULL Operands

BEGIN

DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce');

END;

--Example 2–30 Controlling Evaluation Order with Parentheses

DECLARE

a INTEGER := 1 + 2 ** 2;

b INTEGER := (1 + 2) ** 2;

BEGIN

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

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

END;

--Example 2–31 Expression with Nested Parentheses

DECLARE

a INTEGER := ( (1 + 2) * (3 + 4)) / 7;

BEGIN

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

END;

--Example 2–32 Improving Readability WITH Parentheses

DECLARE

a INTEGER := 2 ** 2 * 3 ** 2;

b INTEGER := (2 ** 2) * (3 ** 2);

BEGIN

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

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

END;

--Example 2–33 Operator Precedence

DECLARE

salary NUMBER := 60000;

commission NUMBER := 0.10;

BEGIN

-- Division has higher precedence than addition:

DBMS_OUTPUT.PUT_LINE ('5 + 12 / 4 = ' || TO_CHAR (5 + 12 / 4));

DBMS_OUTPUT.PUT_LINE ('12 / 4 + 5 = ' || TO_CHAR (12 / 4 + 5));

-- Parentheses override default operator precedence:

DBMS_OUTPUT.PUT_LINE ('8 + 6 / 2 = ' || TO_CHAR (8 + 6 / 2));

DBMS_OUTPUT.PUT_LINE ('(8 + 6) / 2 = ' || TO_CHAR ( (8 + 6) / 2));

-- Most deeply nested operation is evaluated first:

DBMS_OUTPUT.PUT_LINE (

'100 + (20 / 5 + (7 - 3)) = ' || TO_CHAR (100 + (20 / 5 + (7 - 3))));

-- Parentheses, even when unnecessary, improve readability:

DBMS_OUTPUT.PUT_LINE (

'(salary * 0.05) + (commission * 0.25) = '

|| TO_CHAR ( (salary * 0.05) + (commission * 0.25)));

DBMS_OUTPUT.PUT_LINE (

'salary * 0.05 + commission * 0.25 = '

|| TO_CHAR (salary * 0.05 + commission * 0.25));

END;

--Example 2–34 PROCEDURE Prints BOOLEAN VARIABLE

CREATE OR REPLACE PROCEDURE print_boolean (b_name VARCHAR2, b_value BOOLEAN)

AUTHID DEFINER

BEGIN

IF b_value IS NULL

THEN

DBMS_OUTPUT.PUT_LINE (b_name || ' = NULL');

ELSIF b_value = TRUE

THEN

DBMS_OUTPUT.PUT_LINE (b_name || ' = TRUE');

ELSE

DBMS_OUTPUT.PUT_LINE (b_name || ' = FALSE');

END IF;

END;

--Example 2–35 AND OPERATOR

DECLARE

PROCEDURE print_x_and_y (x BOOLEAN, y BOOLEAN)

BEGIN

print_boolean ('x', x);

print_boolean ('y', y);

print_boolean ('x AND y', x AND y);

END print_x_and_y;

BEGIN

print_x_and_y (FALSE, FALSE);

print_x_and_y (TRUE, FALSE);

print_x_and_y (FALSE, TRUE);

print_x_and_y (TRUE, TRUE);

print_x_and_y (TRUE, NULL);

print_x_and_y (FALSE, NULL);

print_x_and_y (NULL, TRUE);

print_x_and_y (NULL, FALSE);

END;

--Example 2–36 OR Operator

DECLARE

PROCEDURE print_x_or_y (x BOOLEAN, y BOOLEAN)

BEGIN

print_boolean ('x', x);

print_boolean ('y', y);

print_boolean ('x OR y', x OR y);

END print_x_or_y;

BEGIN

print_x_or_y (FALSE, FALSE);

print_x_or_y (TRUE, FALSE);

print_x_or_y (FALSE, TRUE);

print_x_or_y (TRUE, TRUE);

print_x_or_y (TRUE, NULL);

print_x_or_y (FALSE, NULL);

print_x_or_y (NULL, TRUE);

print_x_or_y (NULL, FALSE);

END;

/* Formatted on 3/6/2014 5:41:00 PM (QP5 v5.163.1008.3004) */

--Example 2–37 NOT OPERATOR

DECLARE

PROCEDURE print_not_x (x BOOLEAN)

BEGIN

print_boolean ('x', x);

print_boolean ('NOT x', NOT x);

END print_not_x;

BEGIN

print_not_x (TRUE);

print_not_x (FALSE);

print_not_x (NULL);

END;

--Example 2–38 NULL VALUE IN Unequal Comparison

DECLARE

x NUMBER := 5;

y NUMBER := NULL;

BEGIN

IF x != y

THEN -- yields NULL, not TRUE

DBMS_OUTPUT.PUT_LINE ('x != y'); -- not run

ELSIF x = y

THEN -- also yields NULL

DBMS_OUTPUT.PUT_LINE ('x = y');

ELSE

DBMS_OUTPUT.PUT_LINE ('Can''t tell if x and y are equal or not.');

END IF;

END;

--Example 2–39 NULL Value in Equal Comparison

DECLARE

a NUMBER := NULL;

b NUMBER := NULL;

BEGIN

IF a = b

THEN -- yields NULL, not TRUE

DBMS_OUTPUT.PUT_LINE ('a = b'); -- not run

ELSIF a != b

THEN -- yields NULL, not TRUE

DBMS_OUTPUT.PUT_LINE ('a != b'); -- not run

ELSE

DBMS_OUTPUT.PUT_LINE ('Can''t tell if two NULLs are equal');

END IF;

END;

--Example 2–40 NOT NULL Equals NULL

DECLARE

x INTEGER := 2;

Y INTEGER := 5;

high INTEGER;

BEGIN

IF (x > y) -- If x or y is NULL, then (x > y) is NULL

THEN

high := x; -- run if (x > y) is TRUE

ELSE

high := y; -- run if (x > y) is FALSE or NULL

END IF;

IF NOT (x > y) -- If x or y is NULL, then NOT (x > y) is NULL

THEN

high := y; -- run if NOT (x > y) is TRUE

ELSE

high := x; -- run if NOT (x > y) is FALSE or NULL

END IF;

END;

--Example 2–41 Changing Evaluation Order of Logical Operators

DECLARE

x BOOLEAN := FALSE;

y BOOLEAN := FALSE;

BEGIN

print_boolean ('NOT x AND y', NOT x AND y);

print_boolean ('NOT (x AND y)', NOT (x AND y));

print_boolean ('(NOT x) AND y', (NOT x) AND y);

END;

--Example 2–42 Short-Circuit Evaluation

DECLARE

on_hand INTEGER := 0;

on_order INTEGER := 100;

BEGIN

-- Does not cause divide-by-zero error;

-- evaluation stops after first expression

IF (on_hand = 0) OR ( (on_order / on_hand) < 5)

THEN

DBMS_OUTPUT.PUT_LINE ('On hand quantity is zero.');

END IF;

END;

--Example 2–43 Relational Operators in Expressions

BEGIN

print_boolean ('(2 + 2 = 4)', 2 + 2 = 4);

print_boolean ('(2 + 2 <> 4)', 2 + 2 <> 4);

print_boolean ('(2 + 2 != 4)', 2 + 2 != 4);

print_boolean ('(2 + 2 ~= 4)', 2 + 2 ~= 4);

print_boolean ('(2 + 2 ^= 4)', 2 + 2 ^= 4);

print_boolean ('(1 < 2)', 1 < 2);

print_boolean ('(1 > 2)', 1 > 2);

print_boolean ('(1 <= 2)', 1 <= 2);

print_boolean ('(1 >= 1)', 1 >= 1);

END;

/* Formatted on 3/6/2014 5:59:11 PM (QP5 v5.163.1008.3004) */

--Example 2–44 LIKE OPERATOR IN Expression

DECLARE

PROCEDURE compare (VALUE VARCHAR2, pattern VARCHAR2)

BEGIN

IF VALUE LIKE pattern

THEN

DBMS_OUTPUT.PUT_LINE ('TRUE');

ELSE

DBMS_OUTPUT.PUT_LINE ('FALSE');

END IF;

END;

BEGIN

compare ('Johnson', 'J%s_n');

compare ('Johnson', 'J%S_N');

END;

--Example 2–45 Escape Character in Pattern

DECLARE

PROCEDURE half_off (sale_sign VARCHAR2)

BEGIN

IF sale_sign LIKE '50\% off!' ESCAPE '\'

THEN

DBMS_OUTPUT.PUT_LINE ('TRUE');

ELSE

DBMS_OUTPUT.PUT_LINE ('FALSE');

END IF;

END;

BEGIN

half_off ('Going out of business!');

half_off ('50% off!');

END;

--Example 2–46 BETWEEN Operator in Expressions

BEGIN

print_boolean ('2 BETWEEN 1 AND 3', 2 BETWEEN 1 AND 3);

print_boolean ('2 BETWEEN 2 AND 3', 2 BETWEEN 2 AND 3);

print_boolean ('2 BETWEEN 1 AND 2', 2 BETWEEN 1 AND 2);

print_boolean ('2 BETWEEN 3 AND 4', 2 BETWEEN 3 AND 4);

END;

--Example 2–47 IN Operator in Expressions

DECLARE

letter VARCHAR2 (1) := 'm';

BEGIN

print_boolean ('letter IN (''a'', ''b'', ''c'')',

letter IN ('a', 'b', 'c'));

print_boolean ('letter IN (''z'', ''m'', ''y'', ''p'')',

letter IN ('z', 'm', 'y', 'p'));

END;

--Example 2–48 IN Operator with Sets with NULL Values

DECLARE

a INTEGER; -- Initialized to NULL by default

b INTEGER := 10;

c INTEGER := 100;

BEGIN

print_boolean ('100 IN (a, b, c)', 100 IN (a, b, c));

print_boolean ('100 NOT IN (a, b, c)', 100 NOT IN (a, b, c));

print_boolean ('100 IN (a, b)', 100 IN (a, b));

print_boolean ('100 NOT IN (a, b)', 100 NOT IN (a, b));

print_boolean ('a IN (a, b)', a IN (a, b));

print_boolean ('a NOT IN (a, b)', a NOT IN (a, b));

END;

--Example 2–49 Equivalent BOOLEAN Expressions

DECLARE

done BOOLEAN;

BEGIN

-- These WHILE loops are equivalent

done := FALSE;

WHILE done = FALSE

LOOP

done := TRUE;

END LOOP;

done := FALSE;

WHILE NOT (done = TRUE)

LOOP

done := TRUE;

END LOOP;

done := FALSE;

WHILE NOT done

LOOP

done := TRUE;

END LOOP;

END;

--Example 2–50 Simple CASE Expression

DECLARE

grade CHAR (1) := 'B';

appraisal VARCHAR2 (20);

BEGIN

appraisal :=

CASE grade

WHEN 'A' THEN 'Excellent'

WHEN 'B' THEN 'Very Good'

WHEN 'C' THEN 'Good'

WHEN 'D' THEN 'Fair'

WHEN 'F' THEN 'Poor'

ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);

END;

--Example 2–51 Simple CASE Expression with WHEN NULL

DECLARE

grade CHAR (1); -- NULL by default

appraisal VARCHAR2 (20);

BEGIN

appraisal :=

CASE grade

WHEN NULL THEN 'No grade assigned'

WHEN 'A' THEN 'Excellent'

WHEN 'B' THEN 'Very Good'

WHEN 'C' THEN 'Good'

WHEN 'D' THEN 'Fair'

WHEN 'F' THEN 'Poor'

ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);

END;

--Example 2–52 Searched CASE Expression

DECLARE

grade CHAR (1) := 'B';

appraisal VARCHAR2 (120);

id NUMBER := 8429862;

attendance NUMBER := 150;

min_days CONSTANT NUMBER := 200;

FUNCTION attends_this_school (id NUMBER)

RETURN BOOLEAN

BEGIN

RETURN TRUE;

END;

BEGIN

appraisal :=

CASE

WHEN attends_this_school (id) = FALSE

THEN

'Student not enrolled'

WHEN grade = 'F' OR attendance < min_days

THEN

'Poor (poor performance or bad attendance)'

WHEN grade = 'A'

THEN

'Excellent'

WHEN grade = 'B'

THEN

'Very Good'

WHEN grade = 'C'

THEN

'Good'

WHEN grade = 'D'

THEN

'Fair'

ELSE

'No such grade'

END;

DBMS_OUTPUT.PUT_LINE ('Result for student ' || id || ' is ' || appraisal);

END;

--Example 2–53 Searched CASE Expression with WHEN ... IS NULL

DECLARE

grade CHAR (1); -- NULL by default

appraisal VARCHAR2 (20);

BEGIN

appraisal :=

CASE

WHEN grade IS NULL THEN 'No grade assigned'

WHEN grade = 'A' THEN 'Excellent'

WHEN grade = 'B' THEN 'Very Good'

WHEN grade = 'C' THEN 'Good'

WHEN grade = 'D' THEN 'Fair'

WHEN grade = 'F' THEN 'Poor'

ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);

END;

--Example 2–54 Predefined Inquiry Directives

CREATE OR REPLACE PROCEDURE p

AUTHID DEFINER

i PLS_INTEGER;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Inside p');

i := $$PLSQL_LINE;

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

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_LINE = ' || $$PLSQL_LINE);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT = ' || $$PLSQL_UNIT);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_OWNER = ' || $$PLSQL_UNIT_OWNER);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_TYPE = ' || $$PLSQL_UNIT_TYPE);

END;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Outside p');

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_LINE = ' || $$PLSQL_LINE);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT = ' || $$PLSQL_UNIT);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_OWNER = ' || $$PLSQL_UNIT_OWNER);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_TYPE = ' || $$PLSQL_UNIT_TYPE);

END;

--Example 2–55 Displaying Values of PL/SQL Compilation Parameters

BEGIN

DBMS_OUTPUT.PUT_LINE ('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_CCFLAGS = ' || $$PLSQL_CCFLAGS);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);

DBMS_OUTPUT.PUT_LINE (

'$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);

DBMS_OUTPUT.PUT_LINE ('$$PLSQL_WARNINGS = ' || $$PLSQL_WARNINGS);

DBMS_OUTPUT.PUT_LINE (

'$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);

END;

--Example 2–56 PLSQL_CCFLAGS Assigns VALUE TO Itself

ALTER SESSION SET

PLSQL_CCFlags = 'Some_Flag:1, Some_Flag:2, PLSQL_CCFlags:99'

BEGIN

DBMS_OUTPUT.PUT_LINE ($$Some_Flag);

DBMS_OUTPUT.PUT_LINE ($$PLSQL_CCFlags);

END;

--Example 2–57 STATIC Constants

CREATE PACKAGE my_debug

debug CONSTANT BOOLEAN := TRUE;

trace CONSTANT BOOLEAN := TRUE;

END my_debug;

CREATE PROCEDURE my_proc1

AUTHID DEFINER

BEGIN

$IF my_debug.debug

$THEN

DBMS_OUTPUT.put_line ('Debugging ON');

$ELSE

DBMS_OUTPUT.put_line ('Debugging OFF');

$END

END my_proc1;

CREATE PROCEDURE my_proc2

AUTHID DEFINER

BEGIN

$IF my_debug.trace

$THEN

DBMS_OUTPUT.put_line ('Tracing ON');

$ELSE

DBMS_OUTPUT.put_line ('Tracing OFF');

$END

END my_proc2;

--Example 2–58 Code FOR Checking DATABASE VERSION

BEGIN

$IF DBMS_DB_VERSION.VER_LE_10_1

$THEN -- selection directive begins

$ERROR 'unsupported database release' $END -- error directive

$ELSE

DBMS_OUTPUT.PUT_LINE (

'Release '

|| DBMS_DB_VERSION.VERSION

|| '.'

|| DBMS_DB_VERSION.RELEASE

|| ' is supported.');

-- This COMMIT syntax is newly supported in 10.2:

COMMIT WRITE IMMEDIATE NOWAIT;

$END -- selection directive ends

END;

--Example 2–59 Compiling Different Code for Different Database Versions

ALTER SESSION SET PLSQL_CCFLAGS = 'my_debug:FALSE, my_tracing:FALSE';----press F9 in Toad

CREATE OR REPLACE PACKAGE my_pkg

AUTHID DEFINER

SUBTYPE my_real IS $IF DBMS_DB_VERSION.VERSION < 10

$THEN

NUMBER;

$ELSE

BINARY_DOUBLE;

$END

my_pi my_real;

my_e my_real;

END my_pkg;

CREATE OR REPLACE PACKAGE BODY my_pkg

BEGIN

$IF DBMS_DB_VERSION.VERSION < 10

$THEN

my_pi := 3.14159265358979323846264338327950288420;

my_e := 2.71828182845904523536028747135266249775;

$ELSE

my_pi := 3.14159265358979323846264338327950288420d;

my_e := 2.71828182845904523536028747135266249775d;

$END

END my_pkg;

CREATE OR REPLACE PROCEDURE circle_area (radius my_pkg.my_real)

AUTHID DEFINER

my_area my_pkg.my_real;

my_data_type VARCHAR2 (30);

BEGIN

my_area := my_pkg.my_pi * (radius ** 2);

DBMS_OUTPUT.PUT_LINE (

'Radius: ' || TO_CHAR (radius) || ' Area: ' || TO_CHAR (my_area));

$IF $$my_debug

$THEN

SELECT DATA_TYPE

INTO my_data_type

FROM USER_ARGUMENTS

WHERE OBJECT_NAME = 'CIRCLE_AREA' AND ARGUMENT_NAME = 'RADIUS';

DBMS_OUTPUT.PUT_LINE (

'Data type of the RADIUS argument is: ' || my_data_type);

$END

END;

--Example 2–60 Displaying Post-Processed Source Textsource text

CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (

'PACKAGE', 'HR', 'MY_PKG'

);

ORACLE PROJECTS

MENUS

Thursday, 6 March 2014

PLSQL Language Fundamentals