PLSQL LEARNING CLASS6

SQL IN PL/SQL

The only statements allowed directly in pl/sql are DML and TCL.

BINDING

Binding a variable is the process of identifying the storage location associated with an identifier in the program.

Types of binding

• Early binding
• Late binding
• Binding during the compiled phase is early binding.
• Binding during the runtime phase is late binding.
• In early binding compile phase will take longer because of binding work but the execution

is faster.

• In late binding it will shorten the compile phase but lengthens the execution time.
• Pl/sql by default uses early binding.
• Binding also involves checking the database for permissions to access the object

Referenced.

DYNAMIC SQL

If you use DDL in pl/sql it validates the permissions and existence if requires during compile time which makes invalid.

We can avoid this by using Dynamic SQL.

Dynamic SQL allows you to create a SQL statement dynamically at runtime.

Two techniques are available for Dynamic SQL.

• Native Dynamic SQL
• DBMS_SQL package

USING NATIVE DYNAMIC SQL

Using execute immediate

Begin

Execute immediate ‘create table student(no number(2),name varchar(10))’;

or

Execute immediate (‘create table student(no number(2),name varchar(10))’);

End;

Using execute immediate with pl/sql variables

declare

v varchar(100);

begin

v := 'create table student(no number(2),name varchar(10))';

execute immediate v;

end;

Using execute immediate with bind variables and using clause

declare

v varchar(100);

begin

v := 'insert into student values(:v1,:v2,:v3)';

execute immediate v using 6,'f',600;

end;

Executing queries with open for and using clause

create or replace procedure p(smarks in number) is

s varchar(100) := 'select *from student where marks > :m';

type t is ref cursor;

c t;

v student%rowtype;

begin

open c for s using smarks;

loop

fetch c into v;

exit when c%notfound;

dbms_output.put_line('Student Marks = ' || v.marks);

end loop;

close c;

end;

Queries with execute immediate

declare

d_name dept.dname%type;

lc dept.loc%type;

v varchar(100);

begin

v := 'select dname from dept where deptno = 10';

execute immediate v into d_name;

dbms_output.put_line('Dname = '|| d_name);

v := 'select loc from dept where dname = :dn';

execute immediate v into lc using d_name;

dbms_output.put_line('Loc = ' || lc);

end;

Bind variables

Declare

V number := 500;

Begin

Update student set marks = v where; -- here v is bind variable

End;

Variable Names

Declare

Marks number(3) := 100;

Begin

Delete student where marks = marks; -- this will delete all the rows in the student table

End;

This can be avoided by using the labeled blocks.

<<my_block>>

Declare

Marks number(3) := 100;

Begin

Delete student where marks = my_block.marks; -- delete rows which has a marks of 100

End;

Getting data into pl/sql variables

Declare

V1 number;

V2 varchar(2);

Begin

Select no,name into v1,v2 from student where marks = 100;

End;

DML and Records

create or replace procedure p(srow in student%rowtype) is

begin

insert into student values srow;

end p;

declare

s student%rowtype;

begin

s.no := 11;

s.name := 'aa';

s.marks := 100;

p(s);

end;

Record based inserts

declare

srow student%rowtype;

begin

srow.no := 7;

srow.name := 'cc';

srow.marks := 500;

insert into student values srow;

end;

Record based updates

declare

srow student%rowtype;

begin

srow.no := 6;

srow.name := 'cc';

srow.marks := 500;

update student set row=srow where no = srow.no;

end;

Using records with returning clause

declare

srow student%rowtype;

sreturn student%rowtype;

begin

srow.no := 8;

srow.name := 'dd';

srow.marks := 500;

insert into student values srow returning no,name,marks into sreturn;

dbms_output.put_line('No = ' || sreturn.no);

dbms_output.put_line('No = ' || sreturn.name);

dbms_output.put_line('No = ' || sreturn.marks);

end;

Forall with non-sequential arrays

declare

type t is table of student.no%type index by binary_integer;

ibt t;

begin

ibt(1) := 1;

ibt(10) := 2;

forall i in ibt.first..ibt.last

update student set marks = 900 where no = ibt(i);

end;

The above program will give error like ‘element at index [2] does not exists.

Usage of indices of to avoid the above error

declare

type t is table of student.no%type index by binary_integer;

ibt t;

type t1 is table of boolean index by binary_integer;

ibt1 t1;

begin

ibt(1) := 1;

ibt(10) := 2;

ibt(100) := 3;

ibt1(1) := true;

ibt1(10) := true;

ibt1(100) := true;

forall i in indices of ibt1

update student set marks = 900 where no = ibt(i);

end;

declare

type t is table of student.no%type index by binary_integer;

ibt t;

type t1 is table of pls_integer index by binary_integer;

ibt1 t1;

begin

ibt(1) := 1;

ibt(10) := 2;

ibt(100) := 3;

ibt1(11) := 1;

ibt1(15) := 10;

ibt1(18) := 100;

forall i in values of ibt1

update student set marks = 567 where no = ibt(i);

end;

Bulk Binds

• Passing the entire pl/sql table to the SQL engine in one step is known as bulk bind.
• Bulk binds are done using the forall statement.
• If there is an error processing one of the rows in bulk DML operation, only that row is rolled back.

Returning clause

• This will be used only with DML statements to return data into pl/sql variables.
• This will be useful in situations like , when performing insert or update or delete if you want to know the data of the table which has been effected by the DML.
• With out going for another SELECT using RETURNING clause we will get the data which will avoid a call to RDBMS kernel.