PL/SQL NOTE

NO1.A  pl/sql block consists of four distinct section:
    1.block header :the optional block identifies a block name or type,if omitted,the block will be considered an anonymous block with no name;
    2.declaretion section: this optional section difines variables and cursors used in PL/SQL.it is not required when there is no variables or cursors used.
    3.execution section:this section contains SQL executable statements executed by PL/SQL runtime engine ;
    4.exception section

Figure 1 ,the structure of a PL/SQL



 
 

NO2.how to declare variables
The syntax for declaring a scalar variable is shows below:
1.declare an variable explicitly:
    variable_name type[constant][not null] [:= initial value]
2.declare an variable by reference to an existing table column
    variable_name [schema.]table_name.colunm_name%type
3.declare an variable by reference to previously defined pl/sql variable.
    variable_name pl/sql_variable%type

NO3.declare a record
 1.The syntax of declaring a record
       Type record_name is RECORD (
               variable_name type[constant][not null] [:=initial value]
               [, variable_name type[constant][not null] [:=initial value],……….]

       )


2.declare record based on a type

Syntax: record_name type

Once you declare a record type ,you can declare one or more record variable of that type using this syntax

Example;a record type may be declare as follows:

       Declare

       type emp_rec_type is RECORD(

              Empno NUMBER(4) NOT NULL,

              Ename VARCHAR2(10),

              Hiredate DATE,

              Sal NUMBER(7,2),

              Comm NUMBER(7,2));

            )

 

3.Instants of record type of emp_rec_type can the declare like that:

old_emp emp_rec_type ;

 

4.reference a record field
   Individual fields of a record can be referenced by a diot notation syntax show here:
    syntax:record_name.field_name

5.Ofen ,a record needs to be declared the same as the struture of a existing table in the database.PL/SQL provides the sample syntax show here to define such a record.

       syntax: record_name [schema.]table_name%rowtype

NO4.index-by table

       An index-by table type must be declare before you can declare an index-by table base on that type .

 

1.declare an index-by table type

        TYPE tabletype_name IS TABLE OF{

                type|variable_name%type|[schema.]table_name%rowtype

        } INDEX BY BINARY_INTEGER

2.declare an index-by table

        table_name tabletype_name ;

3.index-by table method

 

NO5.declare cursor