PL/SQL Introduction
 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

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.

Declaraing 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  Records

pl/sql tables  PL/SQL Tables

Varrays  Varrays

Nested tables  Nested Tables

Records 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 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 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 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.