Sunday, 24 November 2013

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


No comments:

Post a Comment