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