PL SQL 2 Databases and transactional applications

https://drive.google.com/file/d/0BxYoN5rLDBaRMUJuMWs0c1ZrbVU/edit?usp=sharing
Course Objectives
Variable declaration (again)
operators
Conversions of data types
Nested blocks and variable scope
Control Structures

Example 1
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;

---------------------------------
Exampl 2
SET SERVEROUTPUT ON

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 3
DECLARE
acct_id INTEGER(4) NOT NULL := 9999;
a NATURALN := 9999;
b POSITIVEN := 9999;
c SIMPLE_INTEGER := 9999;
BEGIN
NULL;
END;

---------------------------------
Example 4
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
DBMS_OUTPUT.PUT_LINE('null_string is'||null_string);
IF valid is null THEN
DBMS_OUTPUT.PUT_LINE('name is NULL, not ""');
END IF;
END;

---------------------------------
Example 5
SET SERVEROUTPUT ON
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 6
BEGIN
DBMS_OUTPUT.PUT_LINE ('Today is '||'&sv_day');
DBMS_OUTPUT.PUT_LINE ('Tomorrow will be '||'&sv_day');
END;

Example 7
----------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE ('Today is '||'&&sv_day');
DBMS_OUTPUT.PUT_LINE ('The same day is '||'&sv_day');
END;

-----------------------------------
Example 8
DECLARE
a INTEGER := 2**2*3**2;
BEGIN
DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;

----------------------------------
Example 9
SET SERVEROUTPUT ON
-- outer_block
DECLARE
v_test NUMBER := 123;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer Block, v_test: '||v_test);
-- inner_block
DECLARE
v_test NUMBER := 456;
BEGIN
DBMS_OUTPUT.PUT_LINE('Inner Block, v_test: '||v_test);
DBMS_OUTPUT.PUT_LINE('In inner Block, ?outer_block.v_test is: '||
outer_block.v_test);
END inner_block;

----------------------------------
Example 10
DECLARE
v_num NUMBER := &sv_user_num;
BEGIN
IF MOD(v_num,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num|| ' is even number');
ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
END IF;
DBMS_OUTPUT.PUT_LINE ('Done');
END;

----------------------------------
Example 11
SET SERVEROUTPUT ON
DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN
DBMS_OUTPUT.PUT_LINE('x != y');
ELSIF x = y THEN
DBMS_OUTPUT.PUT_LINE('x = y');
ELSE
DBMS_OUTPUT.PUT_LINE('impossible to define if = or not');
END IF;
END;


----------------------------------
Example 12
SET SERVEROUTPUT ON
DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN
DBMS_OUTPUT.PUT_LINE('a = b');
ELSIF a != b THEN
DBMS_OUTPUT.PUT_LINE('a != b');
ELSE
DBMS_OUTPUT.PUT_LINE('umm, it is not known');
END IF;
END;

----------------------------------
Example 13
DECLARE
x INTEGER := 2;
y INTEGER := 5;
high INTEGER;
BEGIN
IF (x mayor y) THEN
high := x;
ELSE high := y;
END IF;
IF NOT (x menor y) THEN
high := y;
ELSE high := x;
END IF;
DBMS_OUTPUT.PUT_LINE('high is: '||to_char(high));
END;

----------------------------------
Example 14
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 15
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;