Wednesday 10 October 2012

Sql and plsql Material



For Sql and Pl Sql Material click on below link

http://documents.club-oracle.com/downloads.php?do=file&id=6989

Forall example with plsql table type in Oracle


set echo on

DECLARE

   TYPE emp_aat IS TABLE OF emp%ROWTYPE
      INDEX BY PLS_INTEGER;
   aa_emps emp_aat;

BEGIN

   FORALL i IN 1 .. aa_emps.COUNT
      UPDATE emp
      SET    sal = aa_emps(i).sal * 1.1
      WHERE  empno = aa_emps(i).empno;

END;
/

Forall Example with Record Type In Oracle


set serveroutput on
set echo on

DECLARE

   TYPE subset_rt IS RECORD
   ( empno    emp.empno%TYPE
   , ename    emp.ename%TYPE
   , hiredate emp.hiredate%TYPE
   , deptno   emp.deptno%TYPE );

   TYPE subset_aat IS TABLE OF subset_rt
      INDEX BY PLS_INTEGER;

   aa_subset subset_aat;

BEGIN

   /* Some "source" data... */
   SELECT ROWNUM, owner, created, 20
   BULK   COLLECT INTO aa_subset
   FROM   all_objects
   WHERE  ROWNUM <= 10;

   /* Record-based insert and subset of columns... */
   FORALL i IN 1 .. aa_subset.COUNT
      INSERT INTO (SELECT empno, ename, hiredate, deptno FROM emp)
      VALUES aa_subset(i);

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.');

END;
/