PL/SQL LEARNING NOTES (1)

1. About PL/SQL
Stands for "Procedural Language extension to SQL"
Is Oracle Corporation's standard data access language for relational databases
Prodides a block structure for executable units of code
2. PL/SQL Block Structure
DECLARE (Optional)
-Variables, cursors, User-defined conditions
BEGIN (mandatory)
-SQL statements
-PL/SQL statements
EXCEPTION (Optional)
-Actions to perform when errors occur
END
3. Block Types
Anonymous
Procedure
-Procedure name IS
Function
-Function name RETURN datatype is
4. Execute an Anonymous Block (first set serverout on)
1 declare
2 v_fname varchar2 (20 );
3 begin
4 select first_name into v_fname from employees where Maid = 100;
5 dbms_output.put_line ('the result is '| v_fname );
6 end;
7/
5. Declaring an initializing PL/SQL Variables
Syntax: identifier [constant] datatype [not null] [: = | DEFAULT expr];
6. Types of Variables
PL/SQL variables
-Scalar
-Composite
-Reference
Non-PL/SQL variables: Blind variables
-Large object (LOB)
7. Base Scalar Data Types
-CHAR
-VARCHAR2
-NUMBER
-BINARY_INTEGER
-PLS_INTEGER
-BOOLEAN
-BINARY_FLOAT
-BINARY_DOUBLE
8.% TYPE Attribute
-A database column definition
-Another declare variable
Syntax
-Identifier table. column_name % TYPE
Examples
-Emp_lname employee. last_name % TYPE
9. Printing Blind variables
SQL> variable B _emp_salary number
SQL> variable B _emp_salary number
SQL> begin
2 select salary into: B _emp_salary
3 from employees where employee_id = 178;
4 end;
5/
PL/SQL procedure successfully completed.
 
SQL> print B _emp_salary
 
B _EMP_SALARY
------------
7000
Select first_name, last_name from employees
2 where salary =: B _emp_salary;
 
FIRST_NAME LAST_NAME
---------------------------------------------
Oliver Tuvault
Sarath Sewall
Kimberely Grant


Focus on data structures and databases.