In this tutorial, I am giving an Oracle PL/SQL program example to demonstrate Dynamic SQL using EXECUTE IMMEDIATE statement.
Oracle PL/SQL EXECUTE IMMEDIATE Dynamic SQL Example
The following PL/SQL procedure will update the record in the Employees table for an employee ID if a value is different from the actual value.
The Update statement will be prepared dynamically using a string, and then it will be executed using the EXECUTE IMMEDIATE statement. The SQL statement will be made only for the parameters passed and having new values.
For example, if you want to update the first_name and the salary of the employee, then pass the values for parameter i_id, first_name, and i_salary, rest parameter can be null. The update statement will be generated for these columns only.
CREATE OR REPLACE PROCEDURE update_emp_rec ( i_id IN employees.employee_id%TYPE, i_first IN employees.first_name%TYPE, i_last IN employees.last_name%TYPE, i_email IN employees.email%TYPE, i_phone IN employees.phone_number%TYPE, i_job IN employees.job_id%TYPE, i_salary IN employees.salary%TYPE, i_commission_pct IN employees.commission_pct%TYPE, i_manager_id IN employees.manager_id%TYPE, i_department_id IN employees.department_id%TYPE) AS emp_upd_rec employees%ROWTYPE; sql_string VARCHAR2 (1000); set_count NUMBER := 0; BEGIN SELECT * INTO emp_upd_rec FROM employees WHERE employee_id = i_id; sql_string := 'UPDATE EMPLOYEES SET '; IF i_first != emp_upd_rec.first_name THEN IF set_count > 0 THEN sql_string := sql_string || ', FIRST_NAME =''' || i_first || ''''; ELSE sql_string := sql_string || ' FIRST_NAME =''' || i_first || ''''; set_count := set_count + 1; END IF; END IF; IF i_last != emp_upd_rec.last_name THEN IF set_count > 0 THEN sql_string := sql_string || ', LAST_NAME =''' || i_last || ''''; ELSE sql_string := sql_string || ' LAST_NAME =''' || i_last || ''''; set_count := set_count + 1; END IF; END IF; IF UPPER (i_email) != emp_upd_rec.email THEN IF set_count > 0 THEN sql_string := sql_string || ', EMAIL =''' || UPPER (i_email) || ''''; ELSE sql_string := sql_string || ' EMAIL =''' || UPPER (i_email) || ''''; set_count := set_count + 1; END IF; END IF; IF UPPER (i_phone) != emp_upd_rec.phone_number THEN IF set_count > 0 THEN sql_string := sql_string || ', PHONE_NUMBER =''' || UPPER (i_phone) || ''''; ELSE sql_string := sql_string || ' PHONE_NUMBER =''' || UPPER (i_phone) || ''''; set_count := set_count + 1; END IF; END IF; IF i_job != emp_upd_rec.job_id THEN IF set_count > 0 THEN sql_string := sql_string || ', JOB_ID =''' || i_job || ''''; ELSE sql_string := sql_string || ' JOB_ID =''' || i_job || ''''; set_count := set_count + 1; END IF; END IF; IF i_salary != emp_upd_rec.salary THEN IF set_count > 0 THEN sql_string := sql_string || ', SALARY =' || i_salary; ELSE sql_string := sql_string || ' SALARY =' || i_salary; set_count := set_count + 1; END IF; END IF; IF i_commission_pct != emp_upd_rec.commission_pct THEN IF set_count > 0 THEN sql_string := sql_string || ', COMMISSION_PCT =' || i_commission_pct; ELSE sql_string := sql_string || ' COMMISSION_PCT =' || i_commission_pct; set_count := set_count + 1; END IF; END IF; IF i_manager_id != emp_upd_rec.manager_id THEN IF set_count > 0 THEN sql_string := sql_string || ', MANAGER_ID =' || i_manager_id; ELSE sql_string := sql_string || ' MANAGER_ID =' || i_manager_id; set_count := set_count + 1; END IF; END IF; IF i_department_id != emp_upd_rec.department_id THEN IF set_count > 0 THEN sql_string := sql_string || ', DEPARTMENT_ID =' || i_department_id; ELSE sql_string := sql_string || ' DEPARTMENT_ID =' || i_department_id; set_count := set_count + 1; END IF; END IF; sql_string := sql_string || ' WHERE employee_id = ' || i_id; IF set_count > 0 THEN DBMS_OUTPUT.put_Line (sql_string); EXECUTE IMMEDIATE sql_string; ELSE DBMS_OUTPUT.PUT_LINE ( 'No update needed, ' || 'all fields match original values'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No matching employee found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'Data entry error has occurred, ' || 'please check values and try again' || sql_string); END; /
Run The Procedure
The following is the PL/SQL code, to execute the above procedure to update the first_name and the salary for employee ID 199.
SET SERVEROUTPUT ON; DECLARE I_ID NUMBER; I_FIRST VARCHAR2 (20); I_LAST VARCHAR2 (25); I_EMAIL VARCHAR2 (25); I_PHONE VARCHAR2 (20); I_JOB VARCHAR2 (10); I_SALARY NUMBER; I_COMMISSION_PCT NUMBER; I_MANAGER_ID NUMBER; I_DEPARTMENT_ID NUMBER; BEGIN I_ID := 199; I_FIRST := 'Joseph'; I_LAST := NULL; I_EMAIL := NULL; I_PHONE := NULL; I_JOB := NULL; I_SALARY := 3099; I_COMMISSION_PCT := NULL; I_MANAGER_ID := NULL; I_DEPARTMENT_ID := NULL; UPDATE_EMP_REC (I_ID, I_FIRST, I_LAST, I_EMAIL, I_PHONE, I_JOB, I_SALARY, I_COMMISSION_PCT, I_MANAGER_ID, I_DEPARTMENT_ID); COMMIT; END; /
Output
UPDATE EMPLOYEES SET FIRST_NAME ='Joseph', SALARY =3099 WHERE employee_id = 199 PL/SQL procedure successfully completed.