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
1
Early binding
2
Late binding
3
Binding during the compiled phase is early binding.
4
Binding during the runtime phase is late binding.
5
In early binding compile phase will take longer because of binding
work but the
execution is faster.
6
In late binding it will shorten the compile phase but lengthens the
execution time.
7
PL/SQL by
default uses early binding.
8
Binding also involves checking the database for permissions to
access the object
Referenced.
DYNAMIC SQL
1
If you use DDL in
pl/sql it validates the permissions and existence if requires during compile
time which makes invalid.
2
We can avoid this by using Dynamic SQL.
3
Dynamic SQL allows you to create a SQL statement dynamically at
runtime.
Two
techniques are available for Dynamic SQL.
1
Native Dynamic SQL
2
DBMS_SQL package
USING NATIVE DYNAMIC SQL
USING EXECUTE IMMEDIATE
Ex:
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
Ex:
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
Ex:
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
Ex:
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;
Output:
SQL> exec p(100)
Student Marks = 200
Student Marks = 300
Student Marks = 400
QUERIES WITH EXECUTE IMMEDIATE
Ex:
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;
Output:
Dname = ACCOUNTING
Loc = NEW YORK
VARIABLE NAMES
Ex:
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
Ex:
DECLARE
V1 number;
V2 varchar(2);
BEGIN
Select no,name into v1,v2 from student where marks = 100;
END;
DML AND RECORDS
Ex:
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
Ex:
DECLARE
srow student%rowtype;
BEGIN
srow.no := 7;
srow.name := 'cc';
srow.marks := 500;
insert into student values srow;
END;
RECORD BASED UPDATES
Ex:
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
Ex:
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;
Output:
No = 8
No = dd
No = 500
USING DBMS_SQL PACKAGE
DBMS_SQL is used to execute dynamic
SQL from with in PL/SQL. Unlike native dynamic SQL, it is not built directly
into the language, and thus is less efficient. The DBMS_SQL package allows you
to directly control the processing of a statement within a cursor, with
operations such as opening and closing a cursor, parsing a statement, binding
input variable, and defining output variables.
Ex1:
DECLARE
cursor_id number;
flag number;
v_stmt varchar(50);
BEGIN
cursor_id := dbms_sql.open_cursor;
v_stmt := 'create table stud(sno number(2),sname varchar(10))';
dbms_sql.parse(cursor_id,v_stmt,dbms_sql.native);
flag := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
dbms_output.put_line('Table created');
END;
Output:
Table
created
SQL> desc stud
Name
Null? Type
-----------------------------------------------------------------
-------- --------
SNO
NUMBER(2)
SNAME
VARCHAR2(10)
Ex2:
CREATE OR REPLACE PROCEDURE
DBMS_SQL_PROC(v1
student.no%type,
v2 student.marks%type) is
cursor_id number;
flag number;
v_update varchar(50);
BEGIN
cursor_id := dbms_sql.open_cursor;
v_update := 'update student set marks
= :smarks where no = :sno';
dbms_sql.parse(cursor_id,v_update,dbms_sql.native);
dbms_sql.bind_variable(cursor_id,':sno',v1);
dbms_sql.bind_variable(cursor_id,':smarks',v2);
flag := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
END DBMS_SQL_PROC;
Output:
SQL> select * from student; -- before execution
NO NA MARKS
----
------ -- ----------
1
a 100
2
b 200
3
c 300
SQL> exec dbms_sql_proc(2,222)
SQL> select * from student; -- after execution
NO NA MARKS
----
------ -- ----------
1
a 100
2
b 222
3
c 300
FORALL STATEMENT
This
can be used to get the data from the database at once by reducting the
number of context switches which is a transfer of control between PL/SQL and SQL engine.
Syntax:
Forall
index_var in
[ Lower_bound..upper_bound
|
Indices
of indexing_collection |
Values
of indexing_collection ]
SQL statement;
FORALL WITH NON-SEQUENTIAL ARRAYS
Ex:
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.
You can rectify it in one of the two
following ways.
USGAGE OF INDICES OF TO AVOID THE ABOVE
BEHAVIOUR
This will be used when you have a
collection whose defined rows specify which rows in the binding array you would
like to processed.
Ex:
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;
Ouput:
SQL> select * from student -- before execution
NO NA MARKS
---------- ------------
1
a 100
2
b 200
3
c 300
SQL> select * from student -- after execution
NO NA MARKS
---------- ------------
1
a 900
2
b 900
3
c 900
USGAGE OF VALUES OF TO AVOID THE ABOVE
BEHAVIOUR
This will be used when you have a
collection of integers whose content identifies the position in the binding
array that you want to be processed by the FORALL statement.
Ex:
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;
Ouput:
SQL> select * from student -- before execution
NO NA MARKS
---------- ------------
1
a 100
2
b 200
3
c 300
SQL> select * from student -- after execution
NO NA MARKS
---------- ------------
1
a 900
2
b 900
3
c 900
POINTS ABOUT BULK BINDS
1
Passing the entire PL/SQL
table to the SQL engine in one step is known as bulk
bind.
2
Bulk binds are done using the forall statement.
3
If there is an error processing one of the rows in bulk DML
operation, only that row is rolled back.
POINTS ABOUT RETURING CLAUSE
1
This will be used only with DML statements to return data into PL/SQL variables.
2
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.
3
With out going for another SELECT using RETURNING clause we will get the data which
will avoid a call to RDBMS
kernel.
No comments:
Post a Comment