<-- -->

Thursday, October 26, 2006

PL/SQL Introduction


This tutorial covers PL/SQL datatypes, PL/SQL variables, PL/SQL constants, PL/SQL collections, PL/SQL cursors, procedures, functions and packages and much more in best of its form. You will love to read the content of this tutorial. It is an ever increasing tutorial. I will keep on adding more PL/SQL content from time to time.


PL/SQL Block Structure


Below is the basic structure of the PL/SQL program:


Set serveroutput on

Declare

Var1 varchar2(20);

Begin

Var1 := "Hello World";

Dbms_output.put_line(var1);

Exception

When others then

Dbms_output.put_line(‘It is an exception’);

End;


In the declaration section all the variables and constants are defined.


In PL/SQL all the errors are handled in the Exception block.


Begin and End are mandatory statements indicating the begin and end of the PL/SQL Block.


DECLARING VARIABLES AND CONSTANTS:


Example of declaraing variables:


Var1 varchar2(100);

Hire_date Date;

Var2 number default 5;

Var3 number not null := 2;

Not null means a value may change but it can never be assigned Null.

Var4 varchar2(20) := Null;

Var5 varchar2(20) default Null;


Constants:


Var_constant constant number := 100;

Constants cannot be changed.


You must initialize constants at the time of declaration..


%TYPE and %ROWTYPE


%TYPE is used to declare a variable that is of the same type as a specified table’s column.


Emp_number emp.empno%type;

%ROWTYPE is used to declare a record(variable that represents the entire row of a table).


Emp_record emp%rowtype;

Another example of declaraing variable:


Declare

Name varchar2(30);

Select ename into name from emp where empno = 20;

Begin

Null;

End;


Any DML statements should be after Begin statement;


Begin

Delete from emp where empno = 29;

Commit;

End;

PL/SQL Collections


  • Records

  • PL/SQL tables

  • Varrays

  • Nested Tables

  • Records:


    It is a collection of variables:


    Type my_first_record is record (

    Name varchar2(20);

    Age number;

    Salary number;

    );

    Var_of_myfirstrecord my_first_record;

    PL/SQL Tables


    These are one dimensional arrays and are indexed by integers.


    Example:


    Type my_first_table is table of varchar2(10)

    Index by binary_integer;


    Var_of_table my_first_table;

    Var_of_table(1) := ‘hello world’;

    Var_of_table(2) := ‘bye’;

    Example:


    Type my_emp_table is table of emp%rowtype

    Index by binary_integer:


    Var_of_emp my_emp_table;

    Var1_of_emp my_emp_table;

    Var_of_emp(1).ename := ‘sachin’;

    Var_of_emp(1).empno := 20;

    Var_of_emp(1).sal := 3000;

    To delete individual records:


    Var_of_emp .delete(1);

    To remove all entries from a table:


    Var_of_emp.delete;

    Or

    Var_of_emp := var1_of_emp

    Where var1_of_emp does not contain any value, it is empty.

    COUNT method can be used to return number of records in a PL/SQL Table.


    Var_of_table.count

    First, Next and Last methods of PL/SQL Tables.


    First is for first index in the PL/SQL Tables.


    Last is for last index in the PL/SQL Tables.


    Next is for next index in the PL/SQL Tables.


    Example:


    SQL> set serveroutput on

    SQL> Declare

    2

    3 Type my_dept_table is table of varchar2(20)

    4 Index by binary_integer;

    5

    6 Var_of_dept my_dept_table;

    7 Index_value number;

    8

    9 Begin

    10

    11 For dept_rec in (select * from dept) loop

    12 Var_of_dept(dept_rec.deptno) := dept_rec.dname;

    13 End loop;

    14

    15 Index_value := var_of_dept.first;

    16 Loop

    17 Exit when index_value is null;

    18 Dbms_output.put_line (index_value || ' ' ||var_of_dept(index_value));

    19 Index_value := var_of_dept.next(index_value);

    20 End loop;

    21 End;

    22 /

    10 ACCOUNTING

    20 RESEARCH

    30 SALES

    40 OPERATIONS


    PL/SQL procedure successfully completed.


    Varrays:


    These can be stored in the columns of your tables. When you create them you must provide the maximum size for them. These are dense and Not sparse, which means there is no way to delete individual elements of a varrays.


    Example:


    Create type project_work_type as varray(20) of varchar2(30);


    Create table student_projects(

    Name varchar2(20),

    Id number,

    --each student can have upto 20 projects

    Projects project_work_type,

    );


    Example:


    Create type emp_type as object(

    Id number,

    Name varchar2(20)

    );


    Create type emp_varray_type as varray(5) of emp_type;


    Create table t1(

    T1_id number,

    T1_name varchar2(10),

    Employee emp_varray_type);


    Insert into t1(t1_id, t1_name, employee)

    Values (1, ’one’,emp_varray_type( emp_type(1,’a’),

    Emp_type(2,’b’))

    )


    Nested Tables


    These are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns.



    PL/SQL Sitemap     PL/SQL Resources


    2 Comments:

    At 9:23 PM, Blogger Sudhir DBAKings said...

    Nice post. Helps a lot. Thanks for your post.

    www.dbakings.com

     
    At 3:32 AM, Blogger Hariharan s said...

    Hi,Nice Sql Query in PL/sQL.Thanks....

    -Hari
    Theosoft

     

    Post a Comment

    << PL SQL Tutorials Home