Writing Executable Statements

  1. Lexical Units in a PL/SQL Block

Lexical Units :

  1. Are building blocks of any PL/SQL Block
  2. Are sequences of characters including letters,numerals,tabs,spaces,returns
    and symbols
  3. Can be classified as:
  • Identifiers
  • Delimiters
  • Literals
  • Comments
  1. PL/SQL Block syntax and guidelines
  1. Literals:
  • Character and Date Literals must be enclosed in single quotation marks.

Ex.
v_name VARCHAR2(40):=’Hudson’;

  • Numbers can be simple values or scientific notation
  • Statements can continue over several lines.
  1. Commenting Code
  1. Prefix single-line comments with two hypens (–)
  2. Place Multiple-line comments between the symbols /* and */

Ex.
— PL/SQL Block having Single Line Comments, Multiple Line Comments —
— Declare the Variables —
DECLARE
v_empno NUMBER;
BEGIN — Executable Section of a PL/SQL Block —
/*
PL-SQL Block to
print the Employee No. of an Employee Smith
*/
SELECT EMPNO
INTO v_EMPNO
FROM EMP
WHERE ENAME = ‘SMITH’;
DBMS_OUTPUT.PUT_LINE(‘Employee Number of SMITH is :’||v_empno);
END;
/

  1. SQL Functions in PL/SQL

a.SQL Functions available in Procedural Statements:
1. Single-Row Number
2. Single-Row character
3. Data Type Conversion
4. Date
5. Timestamp
6. GREATEST and LEAST
7. Miscellaneous Functions
b.Not available in Procedural Statements:
1. DECODE
2. Group Functions

Ex.

— PL/SQL Block to display the Length of a String and Convert the Employee Name LOWER Case —
DECLARE
DESC_SIZE INTEGER(5);
PROD_DESCRIPTION VARCHAR2(70):=’You can use this Product with your Radio Frequency’;
emp_name VARCHAR2(15):=’SMITH’;
BEGIN
— Get the Length of the String in PROD Description —
desc_size:=LENGTH(PROD_DESCRIPTION);
DBMS_OUTPUT.PUT_LINE(‘Length of the String : ‘||desc_size);
emp_name:=LOWER(EMP_NAME);
DBMS_OUTPUT.PUT_LINE(‘Lower Case of Employee Name : ‘||emp_name);
END;
/

— PL/SQL Block to display the value using the GREATEST and LEAST SQL Functions —

DECLARE
v_great_result NUMBER;
v_least_result NUMBER;
BEGIN
SELECT GREATEST(100,200,300) INTO
v_great_result FROM DUAL;
SELECT LEAST(100,200,300) INTO
v_least_result FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Value returned by the GREATEST Function : '||v_great_result);
DBMS_OUTPUT.PUT_LINE('Value returned by the LEAST Function    : '||v_least_result);

END;
/

  1. Data Type Conversion
  1. Convert Data to comparable Data Types
  2. Data Type Conversions are of two types :
    a. Implicit Conversions
    b. Explicit Conversions
  3. Some Conversion Functions :
    a. TO_CHAR
    b. TO_DATE
    c. TO_NUMBER
    d. TO_TIMESTAMP

Ex.

— PL-SQL Block to have the Datatype Conversion —

DECLARE
DATE_OF_JOIN_DT DATE:=’02-FEB-2000′;
DATE_OF_JOIN_TD DATE:=TO_DATE(‘January 05,2000′,’Month DD,YYYY’);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Date Variable displayed using DATE :’||DATE_OF_JOIN_DT);
DBMS_OUTPUT.PUT_LINE(‘Date Variable displayed using TO_DATE :’||DATE_OF_JOIN_TD);
END;
/

  1. Nested Blocks

PL-SQL Blocks can be nested.
a. An executable section (BEGIN…END) can contain nested blocks.
b. An Exception section can contain nested blocks.

Ex.

— PL-SQL Block having a Nested Block —

DECLARE
OUTER_VARIABLE VARCHAR2(150):=’Global Variable in Main PL-SQL Block’;
BEGIN
DECLARE
INNER_VARIABLE VARCHAR2(150):=’Local Variable inside 1st Inner Block’;
BEGIN
DBMS_OUTPUT.PUT_LINE(INNER_VARIABLE);
DBMS_OUTPUT.PUT_LINE(OUTER_VARIABLE);
END;
DBMS_OUTPUT.PUT_LINE(OUTER_VARIABLE);
END;
/

— This PL/SQL Block will error out because the Inner Block Variable cannot be accessed in the Outer Block —
DECLARE
OUTER_VARIABLE VARCHAR2(150):=’Global Variable in Main PL-SQL Block’;
BEGIN
DECLARE
INNER_VARIABLE VARCHAR2(150):=’Local Variable inside 1st Inner Block’;
BEGIN
DBMS_OUTPUT.PUT_LINE(INNER_VARIABLE);
DBMS_OUTPUT.PUT_LINE(OUTER_VARIABLE);
END;
DBMS_OUTPUT.PUT_LINE(INNER_VARIABLE);
DBMS_OUTPUT.PUT_LINE(OUTER_VARIABLE);
END;
/

In the above PL/SQL Block the OUTER_VARIABLE Variable can be accessed anywhere inside any of the Inner Blocks defined in the Main PL/SQL Block.
i.e 1:n.. no.f of Inner Blocks.
But the Inner Variables scope is limited only to the Block where it is defined.

  1. Variable Scope and Visibility

Ex.

DECLARE
FATHER_NAME VARCHAR2(20):=’Patrick’;
DATE_OF_BIRTH DATE:=’20-APR-2012′;
BEGIN
DECLARE
CHILD_NAME VARCHAR2(20):=’Mike’;
DATE_OF_BIRTH DATE:=’12-DEC-2012′;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Father Name : ‘||father_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth : ‘||date_of_birth);
DBMS_OUTPUT.PUT_LINE(‘Child Name : ‘||child_name);
END;
DBMS_OUTPUT.PUT_LINE(‘Date of Birth :’||date_of_birth);
END;
/

DECLARE
FATHER_NAME VARCHAR2(20):=’Patrick’;
DATE_OF_BIRTH DATE:=’20-APR-2012′;
BEGIN
DECLARE
CHILD_NAME VARCHAR2(20):=’Mike’;
DATE_OF_BIRTH DATE:=’12-DEC-2012′;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Father Name : ‘||father_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth : ‘||date_of_birth);
DBMS_OUTPUT.PUT_LINE(‘Child Name : ‘||child_name);
END;
DECLARE
CHILD_NAME VARCHAR2(20):=’Mike’;
DATE_OF_BIRTH DATE:=’13-DEC-2012′;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Father Name : ‘||father_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth : ‘||date_of_birth);
DBMS_OUTPUT.PUT_LINE(‘Child Name : ‘||child_name);
END;
DBMS_OUTPUT.PUT_LINE(‘Date of Birth :’||date_of_birth);
END;
/

In the above PL/SQL Block

  1. The Variable Father’s Name declared in the Main Block will fetch the value as ‘Patrick’.
  2. The Variable Date of Birth declared in Main Block and Inner Block will fetch the value as 12-DEC-2012 when referred inside the Inner Block.
    When the same is referred in the Main Block it will fetch the value
    as ’20-APR-2012.
  1. Qualify an Identifier

Ex.

— PL/SQL Block using the CONSTANT keyword for a Variable in the Main Block
— And the Same Variable Name in the Inner Block —
— Beginning of Main Block —
DECLARE
father_name CONSTANT VARCHAR2(20):=’Patrick’;
date_of_birth DATE:=’20-APR-1972′;
BEGIN
— Beginning of Inner Block —
DECLARE
father_name VARCHAR2(20):=’Peter’;
child_name VARCHAR2(20):=’Mike’;
date_of_birth DATE:=’12-DEC-2002′;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Father”s Name: ‘||father_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘||date_of_birth);
DBMS_OUTPUT.PUT_LINE(‘Child”s Name: ‘||child_name);
END;
DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘||date_of_birth);
END;
/

— PL/SQL Block using the CONSTANT keyword for a Variable in the Main Block
— And the Same Variable Name in the Inner Block —
— And giving a different value for the CONSTANT Variable in the Main Block —
— This PL/SQL Block will error out —
— Beginning of Main Block —
DECLARE
father_name CONSTANT VARCHAR2(20):=’Patrick’;
date_of_birth DATE:=’20-APR-1972′;
BEGIN
father_name:=’Steven’;
DBMS_OUTPUT.PUT_LINE(‘Father”s Name: ‘||father_name);
— Beginning of Inner Block —
DECLARE
father_name VARCHAR2(20):=’Peter’;
child_name VARCHAR2(20):=’Mike’;
date_of_birth DATE:=’12-DEC-2002′;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Father”s Name: ‘||father_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘||date_of_birth);
DBMS_OUTPUT.PUT_LINE(‘Child”s Name: ‘||child_name);
END;
DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘||date_of_birth);
END;
/

— PL-SQL Block to qualify an Identifier —

<>
DECLARE
father_name VARCHAR2(20):=’Patrick’;
date_of_birth DATE:=’20-APR-1972′;
BEGIN
DECLARE
child_name VARCHAR2(20):=’Mike’;
date_of_birth DATE:=’12-DEC-2002′;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Father”s Name: ‘||father_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘||outer.date_of_birth);
DBMS_OUTPUT.PUT_LINE(‘Child”s Name: ‘||child_name);
DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘||date_of_birth);
END;
END;
/

In the above PL/SQL Block the Variable declared in the Main Block can be referred inside the Inner PL/SQL Block using the Identifiers.
The Main PL/SQL Block is given a name an identifier ‘outer’.
Using this identifier the variables can be referred inside the Inner Block normally without the use the identifier it would’nt have been possible.

  1. Operators in PL/SQL
  1. Logical Operators
  2. Arithmetic Operators
  3. Concatenation Operators
  4. Parentheses to control order of operations
  5. Exponential Operator (**)

The following Operators : Logical,Arithmetic,Concatenation are most commonly used in SQL and PLSQL too.

The Exponential Operator can only be used in PLSQL and not in SQL.

Ex.

  1. Increment the counter for a Loop
    loop_count:=loop_count + 1;
  2. Set the Range Value for a Salary Variable
    good_sal:=sal BETWEEN 50000 AND 150000
  3. Validate whether an Employee No.contains a Value
    valid :=(empno IS NOT NULL);

— Example to demonstrate a Exponential Operator in PL/SQL —
DECLARE
a NUMBER:=3;
b NUMBER:=2;
c_e NUMBER;
c_p NUMBER;
BEGIN
c_e:= a ** b; — 3 to the power of 2 (Very similiar to power(m,n)) —
c_p:=power(a,b);
DBMS_OUTPUT.PUT_LINE(‘Result out of Exponential Operator is : ‘||c_e);
DBMS_OUTPUT.PUT_LINE(‘Result out of Exponential Operator is : ‘||c_p);
END;
/

  1. Programming Guidelines

Make Code Maintenance easier by :

  1. Documenting code with comments
  2. Developing a case convention for the code
  3. Developing naming conventions for identifiers and other objects
  4. Enchancing readability by indenting
  1. Indenting Code

For Clarity,indent each level of Code

Ex.
BEGIN
IF x=0 THEN
Y:=1;
END IF;
END;
/

DECLARE
deptno dept.deptno%TYPE;
location_id dept.loc%TYPE;
BEGIN
SELECT deptno,
loc
INTO deptno,
location_id
FROM dept
WHERE dname= ‘SALES’;
END;
/

© 2024 AWS. Learn from Scratch