Plsql program for updating
This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code.
Have you ever had one of those moments where you’ve just completed importing 1.7 million user records from flat files into an normalized set of ATG Personalization tables, plus some custom tables, and you only then realize that the passwords from the old system, while in the compatible md5sum format, are all UPPERCASE, while ATG uses all lowercase hashes? I’m happy to override ATG behavior when I should, but this just felt wrong.
The first thing we’ll need to do is write a SQL query that returns all of the data we need to update every row in the EMPLOYEES table that has a matching record(s) in the the NEW_SALARY_INFO table, keeping in mind that we need the maximum salary. DECLARE 2 CURSOR c_new_salary_info IS 3 SELECT e.employee_id employee_id, 4 MAX(nsi.salary) new_salary 5 FROM employees e, 6 new_salary_info nsi 7 WHERE e.employee_id = nsi.employee_id 8 GROUP BY e.employee_id; 9 10 TYPE t_new_salary_info IS TABLE OF c_new_salary_info%rowtype 11 INDEX BY PLS_INTEGER; 12 lt_new_salary_info t_new_salary_info; 13 14 v_rowcount PLS_INTEGER := 0; 15 BEGIN 16 OPEN c_new_salary_info; 17 18 LOOP 19 FETCH c_new_salary_info BULK COLLECT 20 INTO lt_new_salary_info LIMIT 1000; 21 22 EXIT WHEN lt_new_salary_info.COUNT = 0; 23 24 FORALL i IN 1..lt_new_salary_info.COUNT 25 UPDATE employees 26 SET employee_salary = lt_new_salary_info(i).new_salary 27 WHERE employee_id = lt_new_salary_info(i).employee_id; 28 29 v_rowcount := v_rowcount SQL%ROWCOUNT; 30 END LOOP; 31 32 CLOSE c_new_salary_info; 33 34 DBMS_OUTPUT. The first reason has already been noted, and that is the use of bulk operations and FORALL to reduce context switching between PL/SQL and SQL. The optimizer no longer has to worry about performing an update as part of the query and is able to simply join the two tables in a very efficient manner.Continue Reading → PL/SQL and SQL both are incredibly powerful languages that allow us to do some pretty amazing things with data.If you’ve been around the Oracle community for some time you’ve probably realized, either through your own experience or by reading articles, if you are able to accomplish a task using a single SQL statement then that is preferred over using procedural PL/SQL code that would run slower.
Search for plsql program for updating:
I fully endorse this approach and both use and reiterate it daily to my teams.