Updating plsql

Posted by / 07-Feb-2017 12:51

Updating plsql

If ' N' is not specified, SQL Server converts the string to the code page that corresponds to the default collation of the database or column.

Any characters not found in this code page are lost.

You need to keep track of when events occurred, when people were born, and much more.-- 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.Below is an example how tu use the cursor together with update, notice the create or replace PROCEDURE pro_cedure( p_dept_id number ) IS CURSOR mycursor IS SELECT deptno, comm, extra FROM emp WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id FOR UPDATE; BEGIN FOR emp_rec IN mycursor LOOP UPDATE emp SET comm = extra WHERE CURRENT OF mycursor; INSERT INTO changed( deptno, oldval, newval, seq_nextval) VALUES( emp_rec.deptno, emp_rec.comm, emp_rec.extra, sequence_name.nextval ); END LOOP; END; Sorry i just edited , now there is only deptno 20, but how to insert if many comm= null gets updated..as i mentioned in code to insert into changed_comm , to be done in single procedure only.

updating plsql-18updating plsql-6updating plsql-71