Clauses In PL/SQL.

Clauses In PL/SQL:
Returning Clause:

• This clause is valid at the end of any DML statement.
• It is used to get information about the row or rows just processed.
• Variable is the PL/SQL or SQL expression, which can include columns or pseudocolumns of the current table.
• Variable is the PL/SQL variable into which the result will be stored.

BULK COLLECT Clause:

• It used to collect more than I row at a time.
• It is used as part of the SELECT INTO, FETCH INTO, or RETURNING INTO  Clause and will retrieve rows from the query into the indicated collections.  

BULK COLLECT clause with SELECT:
Illustrations

• Write a PL/SQL Program to delete the 30 department employees salary if the deleted employee salary is above 2500 insert that employee details into the “delete_log” table.

SQL>DECLARE
  TYPE name IS TABLE OF
    Emp.ename%TYPE INDEX BY BINARY_INTEGER;
    TYPE pays IS TABLE OF
    emp.sal%TYPE INDEX BY BINARY_INTEGER;
    n name;
    p pays;
BEGIN
    SELECT ename,sal BULK COLLECT INTO n,p
    FROM emp;
    FOR i IN 1..n.COUNT
    LOOP
      display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
    END LOOP;
END;

BULK COLLECT clause DELETE:
Table  delete_log
ename   varchar2(20),
basic   number(7,2),
dod     timestamp

• Write a PL/SQL program to hike the salary 35% of 20 department employees the hicked salary is above 2500 insert employee details into a trace table. (hint: create a ‘trace’ table column like ename varchar2(20),vsal number(10),dou  timestamp.).

SQL>DECLARE
    TYPE name IS TABLE OF
    emp.ename%TYPE INDEX BY BINARY_INTEGER;
    TYPE pays IS TABLE OF
    Emp.sal%TYPE INDEX BY BINARY_INTEGER;
    n name;
    p pays;
  BEGIN
    DELETE FROM emp
    WHERE deptno=30
    RETURNING ename,sal BULK COLLECT INTO n,p;
    FOR i IN 1..n.COUNT
    LOOP
       Display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
           IF p(i)>2500 THEN
             INSERT INTO delete_log
             VALUES(n(i),p(i),sysdate);
          END IF;
    END LOOP;
  END;

BULK COLLECT clause UPDATE:
SQL>DECLARE
   TYPE name IS TABLE OF
   emp.ename%TYPE INDEX BY BINARY_INTEGER;
   TYPE pays IS TABLE OF
   Emp.sal%TYPE INDEX BY BINARY_INTEGER;
   n name;
   p pays;
 BEGIN
   UPDATE emp SET sal=sal+sal*0.35
   WHERE deptno=20
   RETURNING ename,sal BULK COLLECT INTO n,p;
     FOR i IN 1..n.COUNT
       LOOP
         Display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
         IF p(i)>2500 THEN
         INSERT INTO trace VALUES(n(i),p(i),sysdate);
       END IF;
   END LOOP;
 END;

BULK COLLECT clause CURSOR
SQL>DECLARE
       TYPE name IS TABLE OF
     emp.ename%TYPE INDEX BY BINARY_INTEGER;
       TYPE pays IS TABLE OF
       emp.sal%TYPE INDEX BY BINARY_INTEGER;
      CURSOR c_bulkcollect IS
      SELECT ename,sal FROM EMP;
      n name;
       p pays;
  BEGIN
       OPEN c_bulkcollect;
       FETCH c_bulkcollect BULK COLLECT INTO n,p;
       FOR i IN 1..n.COUNT
        LOOP
           Display(RPAD(n(i),9,’ ‘)||’ ‘||p(i));
         END LOOP;
  END;