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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.