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