Pl/SQL Notes

Hi All,

Just noting down some plsql procedures



Example 1 

create or replace procedure load_data3 is

cursor Tab_order_detail is select * from order_details;
 
 BEGIN

         FOR l_detail in tab_order_detail
           insert into ORDER_DETAILS_STAGE
           values ( ORDER_DETAIL_SEQ.NEXTVAL,L_DETAIL.ORDER_DETAIL_CODE,L_DETAIL.ORDER_NUMBER,L_DETAIL.SHIP_DATE
           ,L_DETAIL.PRODUCT_NUMBER,L_DETAIL.PROMOTION_CODE,
           L_DETAIL.QUANTITY,L_DETAIL.UNIT_COST,L_DETAIL.UNIT_PRICE ,L_DETAIL.UNIT_SALE_PRICE);
         END LOOP;
 END;

Some Basic Procedure Samples 

CURSOR employee_id_cur

IS

  SELECT employee_id FROM plch_employees ORDER BY salary ASC;

l_employee_id employee_id_cur%ROWTYPE;

BEGIN

  OPEN employee_id_cur;

  LOOP

    FETCH employee_id_cur INTO l_employee_id;

    EXIT  WHEN employee_id_cur%NOTFOUND;

  END LOOP

----------------------------------------------------------------------------

same thing can be writen with for loop. Notice there is no need to define employee_rec. Oracle automatically recognises this

and assigne it data type as cursor

DECLARE

   CURSOR employees_in_10_cur

   IS

      SELECT *

        FROM employees

       WHERE department_id = 10;

BEGIN

   FOR employee_rec

   IN employees_in_10_cur

   LOOP

      DBMS_OUTPUT.put_line (

         employee_rec.last_name);

   END LOOP;

END;

-------------------------------------------------------------------------------------------------

same thing can be also writen as. here notice that we did not even name the cursor still oracle will give it a name and then create employee_rec of same data type. Also the good thing about Best of all, Oracle Database automatically optimizes cursor FOR loops to perform similarly to BULK COLLECT queries

BEGIN

   FOR employee_rec IN (

        SELECT *

          FROM employees

         WHERE department_id = 10)

   LOOP

      DBMS_OUTPUT.put_line (

         employee_rec.last_name);

   END LOOP;

END;

---------------------------------------------------------------------------------------------------

difference between function and procedure

create or replace function trial ( a IN number) return kk