collections

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.

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

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.
*(REFERENCE FROM http://docs.oracle.com/cd/B10500_01) USED IN THIS ANSWER BY RAKESH PATEL.)

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. 
*(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.

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 ;
/
*(REFERENCE FROM http://docs.oracle.com/cd/B10500_01) USED IN THIS ANSWER BY RAKESH PATEL.)



Post a Comment