Chapter 03 Writing Executable Statements 01

Objectives

After completing this lesson,you should be able to do the following:

• Identify lexical units in a PL/SQL block.
• Use build-in SQL functions in PL/SQL
• Describe when implict conversions take place and when explict conversion have to be dealt with
• Write nested blocks and qualify variables with labels
• Write readable code with appropriate indentation
• Use sequences in PL/SQL expressions.

Agenda

• Writting executable statements in a PL/SQL block
• Writting nested blocks
• Using operators and developing readable code.

Lexical units in a PL/SQL Block

Lexical units:

• Are building blocks of any PL/SQL block.
• Are sequences of characters including letters,numerals,tables spaces,returns,and symbols
• Can be classified as:
  • Identifiers:v_fname,c_percent
  • Delimiters:;,+-
  • Literals:John,428,True
  • Comments:--,/**/

PL/SQL Block Syntax and Guidelines

• Using Literals
  • -Character and date literals must be enclosed in single quotation marks.
  • -Numbers can be simple values or in scientific notaion.

v_name := 'Henderson';

• Formatting Code:Statements can span several  lines.

Commenting Code

• Prefix single-line comments with two hyphens(--).
• Place a block comment between the symbols /* and */.

Example:

DECLARE
...
v_annual_sal NUMBER (9,2);
BEGIN
    /*
        Compute the annual salary based on the monthly salary input from the user 
    */
    v_annual_sal := mothly_sal * 12;
--The following line displays the annual salary
DBMS_OUTPUT.PUT_LINE(v_annual_sal);
    
    
END;
/

SQL Functions in PL/SQL

• Available in procedural statements:
  • -Single-row functions
• Not available in procedural statements:
  • -DECODE
  • -Group functions(MIN,SUM,MAX只针对SQL，不针对PL/SQL)

SQL Functions in PL/SQL:Example

• Get the length of a string:

DECLARE
        v_desc_size INTEGER(5);
        v_prod_description VARCHAR(70) := 'You can use this product with your radios for higher frequency';
BEGIN
        --get the length of the string in prod description
        v_desc_size := LENGTH(v_prod_description);

        DBMS_OUTPUT.PUT_LINE('The size of the production is ' ||  v_desc_size);
END;
/

SQL> @getlength.sql
The size of the production is 62

PL/SQL procedure successfully completed.

• Get the number of months an employee has worked:

DECLARE
        v_tenure NUMBER(8,2);
BEGIN
        SELECT MONTHS_BETWEEN(CURRENT_DATE,hire_date) INTO v_tenure
        FROM employees
        WHERE employee_id = 100;

        DBMS_OUTPUT.PUT_LINE('The number of months an employee has worked is ' || v_tenure);
END;

/

SQL> @months_between.sql
The number of months an employee has worked is 97.27

PL/SQL procedure successfully completed.

Using Sequences in PL/SQL Expressions

• Starting in 11g:

DECLARE
        v_new_id NUMBER;
BEGIN
        v_new_id :=EMPLOYEES_SEQ.NEXTVAL;

        DBMS_OUTPUT.PUT_LINE('Starting 11g,The Next Sequence is ' || v_new_id);
END;
/
SQL> @starting_11g_sequence.sql
Starting 11g,The Next Sequence is 211

PL/SQL procedure successfully completed.

DECLARE
        v_new_id NUMBER := EMPLOYEES_SEQ.NEXTVAL;
BEGIN
        DBMS_OUTPUT.PUT_LINE('SIMPLE METHOD TO GET THE NUMBER OF SEQUENCE ,THE NUMBER IS ' || v_new_id);
END;
/
SQL> @starting_11g_sequence02.sql
SIMPLE METHOD TO GET THE NUMBER OF SEQUENCE ,THE NUMBER IS 212

PL/SQL procedure successfully completed.

• Before 11g:

DECLARE
        v_new_id NUMBER;
BEGIN
        SELECT EMPLOYEES_SEQ.NEXTVAL INTO v_new_id FROM DUAL;

        DBMS_OUTPUT.PUT_LINE('The next Sequence Num Is :' || v_new_id);
END;
/
SQL> @before_11g_sequence.sql
The next Sequence Num Is :210

PL/SQL procedure successfully completed.

Data Type Conversion

• Converts data to comparable data types
• Is of two types:
  • -Implicit conversion
  • -Explicit conversion
• Functions:
  • -TO_CHAR
  • -TO_DATE
  • -TO_NUMBER
  • -TO_TIMESTAMP

Data Type Conversion

--implict data type conversion

v_date_of_joining DATE := '02-Feb-2000';

--error in data type conversion

v_date_of_joining DATE := 'February 02,2000';

--explicit data type conversion

v_date_of_joining DATE ：= TO_DATE('February 02,2000','Month DD,YYYY');

DECLARE
        a_number NUMBER;
BEGIN
        a_number := '125';
        a_number := a_number + 3;
        DBMS_OUTPUT.PUT_LINE(to_char(a_number,'9999'));
END;
/
SQL> @conversion.sql
128

PL/SQL procedure successfully completed.

Nested Blocks

PL/SQL blocks can be nested.

• An executable section(BEGIN ...END) can contain nested blocks.
• An exception section can contain nested blocks.

DECLARE
        v_outer_variable VARCHAR2(20) := 'GLOBAL VARIABLE';
BEGIN
        DECLARE
                v_inner_variable VARCHAR2(20) := 'LOCAL VARIABLE';
        BEGIN
                DBMS_OUTPUT.PUT_LINE(v_inner_variable);
                DBMS_OUTPUT.PUT_LINE(v_outer_variable);
        END;

        DBMS_OUTPUT.PUT_LINE(v_outer_variable);
END;
/
SQL> @nested.sql
LOCAL VARIABLE
GLOBAL VARIABLE
GLOBAL VARIABLE

PL/SQL procedure successfully completed.