Plsql program for updating
For information on the use of PL/SQL records with SQL to update and insert data, see "Inserting PL/SQL Records into the Database" and "Updating the Database with PL/SQL Record Values".
For additional information on assigning values to PL/SQL variables, see "Assigning a SQL Query Result to a PL/SQL Variable".
-- EDIT --- how to store sequence values ------- I assume that the table create or replace PROCEDURE pro_cedure( p_dept_id number ) IS TYPE changed_table_type IS TABLE OF changed%ROWTYPE; changed_buff changed_table_type; BEGIN SELECT deptno, comm, extra, sequence_name.nextval BULK COLLECT INTO changed_buff FROM emp WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id FOR UPDATE; UPDATE emp SET comm = extra WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id; FORALL i IN 1 ..
changed_buff.count INSERT INTO changed VALUES changed_buff( i ); END; --- EDIT --- version with cursor for small sets of data ----- Yes, for small sets of data bulk collecting doesn't give significant increase of the speed, and plain cursor with for.is sufficient in such a case.
This appendix provides several PL/SQL programs to guide you in writing your own.You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except ), transaction control statements, functions, pseudocolumns, and operators.PL/SQL also conforms to the current ANSI/ISO SQL standard.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.If there is any type mismatch, variable assignments and comparisons may not work the way you expect.
Search for plsql program for updating:
changed_buff.count INSERT INTO changed VALUES changed_buff( i ); END; / can contain ten thousands and more rows, then this procedure might be slow, becasue it will consume a huge amount of PGA memory.