c
1)
Difference between function, procedure and package.
Package-->It stores and compiles all the functions and procedures declared
in SGA(System Global Area).Whenever stored procedures or functions are called
from this package,it will get them from SGA.So it perfomance is definetly high.
Procedures-->( perform an action):They are subprograms which perform certain action.They cannot be called from select statement.
Functions-->( Function used to compute a value and return a value ) :They are subprograms which return a value.They can be used in select statement and also its return value can be assigned to a variable.
Procedures-->( perform an action):They are subprograms which perform certain action.They cannot be called from select statement.
Functions-->( Function used to compute a value and return a value ) :They are subprograms which return a value.They can be used in select statement and also its return value can be assigned to a variable.
procedure
function
1>procedure is used for performing 1> its used for computing value
action and returning value
2> procedure may or maynot return 2> function must and should
value return value
3>it cant be used inside sql statement 3>it can be called inside sql
statement
4> its cant be called as part of expression 4>its always called as part of
expression
1>procedure is used for performing 1> its used for computing value
action and returning value
2> procedure may or maynot return 2> function must and should
value return value
3>it cant be used inside sql statement 3>it can be called inside sql
statement
4> its cant be called as part of expression 4>its always called as part of
expression
Package is faster when you working on specific project
and all your functions and procedure are in that package.
because package load into memory and remain in memory till the place available so execution is fast.
for calling realted procedure, function and global variable.
but if you have a small and misc work. then you should use single function or procedure
it will take a small memory and load fast
because package load into memory and remain in memory till the place available so execution is fast.
for calling realted procedure, function and global variable.
but if you have a small and misc work. then you should use single function or procedure
it will take a small memory and load fast
2) what are collections. what are
collection attributes.?
A collection is an ordered group of
elements, all of the same type. It is a general concept that encompasses lists,
arrays, and other familiar data types. Each element has a unique subscript that
determines its position in the collection.
collections can have
only one dimension, you can model multi-dimensional arrays by creating
collections whose elements are also collections.
*To use collections in an application, you define one or more
PL/SQL types, then define variables of those types. You can define
collection types in a procedure, function, or package. You can pass collection
variables as parameters, to move data between client-side applications and
stored subprograms.
To look up data that is more complex than single values, you can
store PL/SQL records or SQL object types in collections. Nested tables and
varrays can also be attributes of object types.
PL/SQL offers these
collection types:
·
Index-by tables, also known as associative arrays, let you look up
elements using arbitrary numbers and strings for subscript values. (They are
similar to hash tables in other programming languages.)
·
Nested tables hold
an arbitrary number of elements. They use sequential numbers as subscripts. You
can define equivalent SQL types, allowing nested tables to be stored in
database tables and manipulated through SQL.
·
Varrays (short for variable-size arrays) hold a fixed number of
elements (although you can change the number of elements at runtime). They use
sequential numbers as subscripts. You can define equivalent SQL types, allowing
varrays to be stored in database tables. They can be stored and retrieved
through SQL, but with less flexibility than nested tables.
Collection attribute:
Table_type.first
Table_type.last
Table_type.count
Table_type.delete
3) what is difference
between record type and table type.
·
Record Type: This is a user defined data type that
groups a bunch of fields together into a single structure. In C the reserved
word struct is used to define this.
EX:
type t_rec is record(empno emp.empno%type
,dname dept.dname%type
,ename
emp.ename%type;
,loc
dept.loc%type
,message varchar2(1000));
·
Table type: is better
and more accurately known as a collection or an array. an array is typically a
list of records ("standard variables"). A collection is typically a
list of objects ("object variables/pointers")
Ex:
type t1 is table of
emp.empno%type index by binary_integer;
type t1 is table of
VARCHAR2(100) index by binary_integer;
type t1 is table of
emp%rowtype index by binary_integer;
type t1 is table of
cursor%rowtype index by binary_integer;
4) what is bulk binding?
"bulk binds" was added
to PL/SQL back in Oracle 8i. Bulk binds enable a PL/SQL program to fetch many
rows from a cursor in one call instead of fetching one row at a time. Bulk
binds also allow many similar DML statements to be executed with one call
instead of requiring a separate call for each. For certain types of PL/SQL
programs, using bulk binds will reduce CPU usage and make the code run faster.A context switch occurs every time the PL/SQL engine calls the SQL engine to parse, execute, or fetch from a cursor. Since context switches use CPU time, reducing the number of context switches will reduce the amount of CPU time used. In addition, the SQL engine can often reduce the number of logical reads required when multiple rows are fetched in one call. Reducing logical reads also saves CPU time.
Using bulk binding we can reduce the context switched between sql and plsql engines…
*(REFERENCE FROM http://www.dbspecialists.com/files/presentations/bulk_binds.html
USED IN THIS ANSWER BY RAKESH
PATEL.)
5) Difference between bulk
collect and forall
Using bulk collect we can
fetch the records at once from the sql engine.
Bulk Collect is for quickly loading a cursor
generally into a table type variable within a procedure.
ex. Fetch curs BULK_COLLECT into table_var LIMIT 200;
FORALL is for processing the table variable once it is loaded. FORALL is much faster than looping through a table index.
ex.
FORALL j in t_tbl.first..t_tbl.last
insert into some_table
values t_tbl(j);
This is much faster than:
For j in t_tbl.first..t_tbl.last loop
insert into some_table
values t_tbl(j);
end loop;
NOTE: FORALL statement is limited to performing one and only one DML statement.
ex. Fetch curs BULK_COLLECT into table_var LIMIT 200;
FORALL is for processing the table variable once it is loaded. FORALL is much faster than looping through a table index.
ex.
FORALL j in t_tbl.first..t_tbl.last
insert into some_table
values t_tbl(j);
This is much faster than:
For j in t_tbl.first..t_tbl.last loop
insert into some_table
values t_tbl(j);
end loop;
NOTE: FORALL statement is limited to performing one and only one DML statement.
*(REFERENCE FROM http://dba-blog.blogspot.com/2005/08/using-of-bulk-collect-and-forall-for.htmlUSED
IN THIS ANSWER BY RAKESH PATEL.)
Ex:
create or replace procedure xx_emp_dtls(p_deptno number)
is
cursor c_emp
is select empno,ename,sal
from emp
where deptno =
nvl(p_deptno,deptno);
type t_emp is table of c_emp%rowtype index by binary_integer;
r_emp t_emp;
begin
open c_emp;
fetch c_emp bulk collect into r_emp;
close c_emp;
for i in 1..r_emp.count
loop
dbms_output.put_line('ename'||r_emp(i).ename);
for r_dept in (select
dname from dept where deptno = r_emp(i).deptno)
loop
dbms_output.put_line('dname is '||r_dept.dname);
end loop;
end loop;
exception
when others then
dbms_output.put_line('Error
in the main procedure'||sqlerrm);
End xx_emp_dtls;
/
create or replace procedure xx_emp_dtls(p_deptno number)
is
cursor c_dept
is select
dname,loc,deptno
from dept
where deptno =
nvl(p_deptno,deptno);
cursor c_emp(v_deptno number)
is select empno,ename,sal
from emp
where deptno = v_deptno;
type t_dept is table of c_dept%rowtype index by binary_integer;
r_dept t_dept;
type t_emp is table of c_emp%rowtype index by binary_integer;
r_emp t_emp;
begin
open c_dept;
fetch c_dept bulk collect into r_dept;
close c_dept;
for i in 1..r_dept.count
loop
dbms_output.put_line('dname is '||r_dept(i).dname);
open
c_emp(r_dept(i).deptno);
fetch c_emp bulk
collect into r_emp;
close c_emp;
for j in 1..
r_emp.count
loop
dbms_output.put_line('ename'||r_emp(j).ename);
end loop;
r_emp.delete;
end loop;
exception
when others then
dbms_output.put_line('Error
in the main procedure'||sqlerrm);
End xx_emp_dtls;
/
create or replace procedure xx_emp_dtls(p_deptno number)
is
cursor c_dept
is select
deptno,dname,loc
from dept
where deptno =
nvl(p_deptno,deptno);
cursor c_emp(v_deptno number)
is select empno,ename,sal
from emp
where deptno =
v_deptno;
type t_dept is table of c_dept%rowtype index by binary_integer;
r_dept t_dept;
type t_emp is table of c_emp%rowtype index by binary_integer;
r_emp t_emp;
begin
open c_dept;
fetch c_dept bulk collect into r_dept;
close c_dept;
for i in 1..r_dept.count
loop
dbms_output.put_line('dname is '||r_dept(i).dname);
open c_emp(r_dept(i).deptno);
fetch c_emp bulk
collect into r_emp;
close c_emp;
for j in 1..
r_emp.count
loop
dbms_output.put_line('ename'||r_emp(j).ename);
end loop;
r_emp.delete;
end loop;
FORALL i in
1..r_dept.count
insert into
xx_dept_test values r_dept(i);
exception
when others then
dbms_output.put_line('Error
in the main procedure'||sqlerrm);
End xx_emp_dtls;
/
create or replace procedure xx_emp_dtls(p_deptno number)
is
cursor c_dept
is select deptno,dname,loc
from dept
where deptno =
nvl(p_deptno,deptno);
cursor c_emp(v_deptno
number)
is select empno,ename,sal
from emp
where deptno =
v_deptno;
type t_dept is table of
c_dept%rowtype index by binary_integer;
r_dept t_dept;
type t_emp is table of
c_emp%rowtype index by binary_integer;
r_emp t_emp;
begin
open c_dept;
fetch c_dept bulk collect
into r_dept;
close c_dept;
for i in 1..r_dept.count
loop
dbms_output.put_line('dname is '||r_dept(i).dname);
open
c_emp(r_dept(i).deptno);
fetch c_emp bulk
collect into r_emp;
close c_emp;
for j in 1..
r_emp.count
loop
dbms_output.put_line('ename'||r_emp(j).ename);
end loop;
r_emp.delete;
end loop;
FORALL i in
1..r_dept.count
-- insert into xx_dept_test values r_dept(i);
insert into (select
deptno,dname,loc
from
x_dept_test1) values r_dept(i);
exception
when others then
dbms_output.put_line('Error
in the main procedure'||sqlerrm);
End xx_emp_dtls;
/
6) What is ref cursor?
A ref cursor in
Oracle PL/SQL is much like an ordinary PL/SQL cursor in that it acts as a
pointer to the result set of the cursor with which it is associated. However,
the difference is that a ref cursor can be assigned to different result sets
whereas a cursor is always associated with the same result set. Cursors and ref
cursors are not interchangeable.
The real purpose of ref cursors is to be able to share cursors and result sets between the client and the Oracle server or between different subroutines. For example you might open a cursor in an Oracle Forms client and then continue working with the cursor on the server or you might open a cursor in say a Java program and then continue working with it in a PL/SQL stored procedure.
Ref cursors also come in two variants: strongly typed and weakly typed depending on how likely you are (or want to) reuse a cursor variable. Weak ref cursor types can be associated with any query whereas strong ref cursor types can only be associated with cursors of the same type.
The real purpose of ref cursors is to be able to share cursors and result sets between the client and the Oracle server or between different subroutines. For example you might open a cursor in an Oracle Forms client and then continue working with the cursor on the server or you might open a cursor in say a Java program and then continue working with it in a PL/SQL stored procedure.
Ref cursors also come in two variants: strongly typed and weakly typed depending on how likely you are (or want to) reuse a cursor variable. Weak ref cursor types can be associated with any query whereas strong ref cursor types can only be associated with cursors of the same type.
Ex:
CREATE OR
REPLACE procedure emp_dtls_dynamic(p_flag IN varchar2)
is
type t_rec is
RECORD (empno emp.empno%type
,ename emp.ename%type
,sal
emp.sal%type
,comm
emp.comm%type
,dname dept.dname%type);
type t1 is
table of t_rec index by binary_integer;
r1 t1;
type t_ref is
REF CURSOR;
r_ref t_ref;
begin
IF p_flag = 'Y' then
OPEN r_ref for select empno,ename,sal,nvl(comm,100),dname
from emp e,dept d
where e.deptno = d.deptno
and sal <= 5000;
ELSE
OPEN r_ref for select
empno,ename,sal,nvl(comm,110),dname
from emp e,dept d
where e.deptno = d.deptno
and sal > 5000;
END IF;
FETCH r_ref bulk collect into r1;
close r_ref;
FOR I in 1..r1.count
LOOP
DBMS_OUTPUT.PUT_LINE('Ename
-->'||r1(i).ename);
DBMS_OUTPUT.PUT_LINE('salary
-->'||r1(i).sal);
DBMS_OUTPUT.PUT_LINE('dname
-->'||r1(i).dname);
DBMS_OUTPUT.PUT_LINE('comm
-->'||r1(i).comm);
END LOOP;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ocured in
procedure'||SQLERRM);
END ;
/
7) What is execute
immediate?
The
EXECUTE IMMEDIATE statement
executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to
issue SQL statements that cannot be represented directly in PL/SQL, or to build
up statements where you do not know all the table names, WHERE clauses, and so
on in advance.
CREATE OR REPLACE
procedure emp_dtls_dynamic(p_flag IN varchar2)
is
type t_rec is RECORD
(empno emp.empno%type
,ename emp.ename%type
,sal emp.sal%type
,comm emp.comm%type
,dname dept.dname%type);
type t1 is table of t_rec
index by binary_integer;
r1 t1;
type t_ref is REF CURSOR;
r_ref t_ref;
ln_flag char(1):='N';
begin
IF p_flag = 'Y' then
OPEN r_ref for select empno,ename,sal,nvl(comm,100),dname
from emp e,dept d
where e.deptno = d.deptno
and sal <= 2000;
ELSE
OPEN r_ref for select empno,ename,sal,nvl(comm,110),dname
from emp e,dept d
where e.deptno = d.deptno
and sal > 2000;
END IF;
FETCH r_ref bulk collect into r1;
close r_ref;
FOR I in 1..r1.count
LOOP
ln_flag := 'Y';
DBMS_OUTPUT.PUT_LINE('Ename
-->'||r1(i).ename);
DBMS_OUTPUT.PUT_LINE('salary
-->'||r1(i).sal);
DBMS_OUTPUT.PUT_LINE('dname
-->'||r1(i).dname);
DBMS_OUTPUT.PUT_LINE('comm
-->'||r1(i).comm);
END LOOP;
if ln_flag = 'Y' then
execute immediate 'truncate table emp_backup1';
else
dbms_output.put_line('No data found');
end if;
FORALL i in 1..r1.count
insert into emp_backup1 values r1(i);
commit;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ocured in
procedure'||SQLERRM);
END ;
/
CREATE OR REPLACE
procedure emp_dtls_dynamic(p_deptno number,p_flag IN varchar2)
is
type t_rec is RECORD
(empno emp.empno%type
,ename emp.ename%type
,sal emp.sal%type
,comm emp.comm%type
,dname dept.dname%type);
type t1 is table of t_rec
index by binary_integer;
r1 t1;
type t_ref is REF CURSOR;
r_ref t_ref;
ln_flag char(1):='N';
v_sql varchar2(32000);
begin
IF p_flag = 'Y' then
v_sql :=
'select empno,ename,sal,nvl(comm,100),dname
from emp e,dept d
where e.deptno = d.deptno
and sal <= 2000 and e.deptno
= nvl(:1,e.deptno)';
ELSE
v_sql :=
'select empno,ename,sal,nvl(comm,100),dname
from emp e,dept d
where e.deptno = d.deptno
and sal <= 2000
and e.deptno = nvl(:1,e.deptno)';
END IF;
execute immediate v_sql
bulk collect into r1 using p_deptno ;
FOR I in 1..r1.count
LOOP
ln_flag := 'Y';
DBMS_OUTPUT.PUT_LINE('Ename
-->'||r1(i).ename);
DBMS_OUTPUT.PUT_LINE('salary
-->'||r1(i).sal);
DBMS_OUTPUT.PUT_LINE('dname
-->'||r1(i).dname);
DBMS_OUTPUT.PUT_LINE('comm
-->'||r1(i).comm);
END LOOP;
if ln_flag = 'Y' then
execute immediate 'truncate table
emp_backup1';
else
dbms_output.put_line('No data found');
end if;
FORALL i in 1..r1.count
insert into emp_backup1 values r1(i);
commit;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ocured in
procedure'||SQLERRM);
END ;
/
No comments:
Post a Comment