OPEN profile_cur FOR sql Stmt; LOOP FETCH profile_cur INTO profile_rec; EXIT WHEN profile_cur%NOTFOUND; Quo To Pur Days( profile_rec.personid , profile_rec. IF changed Flag = TRUE THEN people_tab(x).personid := profile_rec.personid; people_tab(x).quotopurdays := profile_rec.quotopurdays; ...; ...; x := x 1; changed Flag := FALSE; END IF; END LOOP; CLOSE profile_cur; FORALL bulk Num IN 1..people_tab.
COUNT UPDATE profile SET quotopurdays = people_tab(bulk Num). WHERE personid = people_tab(bulk Num); COMMIT; Oracle will not allow a FORALL update to use a PL table of records in the update as above etc..
In this chapter, we will discuss Procedures in PL/SQL.
A subprogram is a program unit/module that performs a particular task.
Information is transmitted between a PL/SQL program and the database through variables. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation.
Every variable has a specific type associated with it. In this case, it is essential that the variable have the same type as the relation column.
It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. profile_cur has 19 columns and has 1-2 million rows.An UPDATE in the cursor loop will update 1 row and 19 columns at once.It is created with the CREATE PROCEDURE or the CREATE FUNCTION statement.It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.