Dynamic SQL with FOR UPDATE clause


Many times we can hear that the Native Dynamic SQL can not be implemented with FOR UPDATE clause in a PL/SQL program.

In this article I will proof that we can associate a dynamic SQL statement with a weak ref cursor variable using FOR UPDATE clause.

Let us consider the following – simple- case study:
We would like to modify the salary column of an employees like table
(This means that the structure of the table is identical with HR’s employees table):
We increase the salary for those employees whose salary is less than a particular value.

Let’s see the code:

CREATE OR REPLACE PROCEDURE din_sql_upd
(p_tname VARCHAR2,p_sal NUMBER)
IS
TYPE c_emp IS REF CURSOR;
c  c_emp;
r EMPLOYEES%ROWTYPE;
v_updated_rows INTEGER:=0;
BEGIN
OPEN c FOR 'SELECT * FROM '||p_tname||' WHERE salary < '
|| p_sal ||' FOR UPDATE';
LOOP
FETCH c INTO r;
EXIT WHEN C%NOTFOUND;  
DBMS_OUTPUT.PUT_LINE(RPAD(R.LAST_NAME,20)||' Salary: '||r.salary);
IF r.salary < p_sal THEN
EXECUTE IMMEDIATE
'UPDATE '||p_tname ||' SET salary =salary*2 WHERE employee_ID=:r '
USING r.employee_id;
v_updated_rows:=v_updated_rows+1;
END IF;
END LOOP;
CLOSE c;
DBMS_OUTPUT.PUT_LINE(CHR(10)||v_updated_rows||' modifications made.');
END din_sql_upd;
/
EXECUTE din_sql_upd('employees',2500)


You can observe that the UPDATE statement does not contain
the WHERE CURRENT OF clause, because it is PROHIBITED!
This program demonstrates one of a kind workaround for solving this problem:
We used simply the primary key instead of WHERE CURRENT OF.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: