#=======================================================================
#
Author : Lokanadham Thandlam
#=======================================================================
--Example 3–1 CHAR AND
VARCHAR2 Blank-Padding Difference
DECLARE
first_name CHAR (10 CHAR);
last_name VARCHAR2 (10 CHAR);
BEGIN
first_name := 'John ';
last_name := 'Chen ';
DBMS_OUTPUT.PUT_LINE
('*' || first_name || '*');
DBMS_OUTPUT.PUT_LINE
('*' || last_name || '*');
END;
/
--Example 3–2 Printing
BOOLEAN VALUES
CREATE PROCEDURE print_boolean (b BOOLEAN)
AS
BEGIN
DBMS_OUTPUT.put_line
(
CASE
WHEN b IS NULL THEN 'Unknown'
WHEN b THEN 'Yes'
WHEN NOT b THEN 'No'
END);
END;
/
BEGIN
print_boolean (TRUE);
print_boolean (FALSE);
print_boolean (NULL);
END;
/
--Example 3–3 SQL STATEMENT
Invokes PL/SQL FUNCTION WITH BOOLEAN Parameter
CREATE OR REPLACE FUNCTION f (x BOOLEAN, y PLS_INTEGER)
RETURN employees.employee_id%TYPE
AUTHID CURRENT_USER
AS
BEGIN
IF x
THEN
RETURN y;
ELSE
RETURN 2 * y;
END IF;
END;
/
DECLARE
name employees.last_name%TYPE;
b BOOLEAN := TRUE;
BEGIN
SELECT last_name
INTO name
FROM employees
WHERE employee_id = f (b, 100);
DBMS_OUTPUT.PUT_LINE
(name);
b := FALSE;
SELECT last_name
INTO name
FROM employees
WHERE employee_id = f (b, 100);
DBMS_OUTPUT.PUT_LINE
(name);
END;
/
--Example 3–4 PLS_INTEGER
Calculation Raises OVERFLOW EXCEPTION
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 PLS_INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
/
--Example 3–5 Preventing
Example 3–4 OVERFLOW
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
/
--Example 3–6 Violating
CONSTRAINT OF SIMPLE_INTEGER SUBTYPE
DECLARE
a SIMPLE_INTEGER := 1;
b PLS_INTEGER := NULL;
BEGIN
a := b;
END;
/
--SIMPLE_INTEGER Overflow
Semantics
DECLARE
n SIMPLE_INTEGER := 2147483645;
BEGIN
FOR j IN 1 .. 4
LOOP
n := n + 1;
DBMS_OUTPUT.PUT_LINE (TO_CHAR (n, 'S9999999999'));
END LOOP;
FOR j IN 1 .. 4
LOOP
n := n - 1;
DBMS_OUTPUT.PUT_LINE (TO_CHAR (n, 'S9999999999'));
END LOOP;
END;
/
---Example 3–7 USER-Defined
Unconstrained Subtypes Show Intended USE
DECLARE
SUBTYPE Balance IS NUMBER;
checking_account Balance (6, 2);
savings_account Balance (8, 2);
certificate_of_deposit Balance (8, 2);
max_insured CONSTANT Balance (8, 2) := 250000.00;
SUBTYPE Counter IS NATURAL;
accounts Counter := 1;
deposits Counter := 0;
withdrawals Counter := 0;
overdrafts Counter := 0;
PROCEDURE deposit (account IN OUT Balance, amount IN Balance)
IS
BEGIN
account := account + amount;
deposits := deposits + 1;
END;
BEGIN
NULL;
END;
/
--Example 3–8 USER-Defined
Constrained SUBTYPE Detects OUT-OF-RANGE VALUES
DECLARE
SUBTYPE Balance IS NUMBER (8, 2);
checking_account Balance;
savings_account Balance;
BEGIN
checking_account := 2000.00;
savings_account := 1000000.00;
END;
/
--Example 3–9 Implicit
Conversion BETWEEN Constrained Subtypes WITH Same Base TYPE
DECLARE
SUBTYPE Digit IS PLS_INTEGER RANGE 0 .. 9;
SUBTYPE Double_digit IS PLS_INTEGER RANGE 10 .. 99;
SUBTYPE Under_100 IS PLS_INTEGER RANGE 0 .. 99;
d Digit := 4;
dd Double_digit := 35;
u Under_100;
BEGIN
u := d; -- Succeeds; Under_100 range
includes Digit range
u := dd; -- Succeeds; Under_100 range includes
Double_digit range
dd := d; -- Raises error; Double_digit range does not include Digit range
END;
/
--Example 3–10 Implicit
Conversion BETWEEN Subtypes WITH Base TYPES IN Same Family
DECLARE
SUBTYPE Word IS CHAR (6);
SUBTYPE Text IS VARCHAR2 (15);
verb Word := 'run';
sentence1 Text;
sentence2 Text := 'Hurry!';
sentence3 Text := 'See Tom run.';
BEGIN
sentence1 := verb; -- 3-character value,
15-character limit
verb := sentence2; -- 5-character value,
6-character limit
verb := sentence3; -- 12-character value,
6-character limit
END;
/