8-5. Writing a Dynamic Delete Statement
Problem
You need to create a procedure that will delete rows from a table. However, the exact SQL for deleting the rows is not known until runtime. For instance, you need create a procedure to delete an employee from the EMPLOYEES table, but rather than limit the procedure to accepting only employee ID numbers for employee identification, you also want to accept an e-mail address. The procedure will determine whether an e-mail address or an ID has been passed and will construct the appropriate DELETE statement.
Solution
Use native dynamic SQL to process a string that is dynamically created based upon values that are passed into the procedure. In the following example, a procedure is created that will build a dynamic SQL string to delete an employee record. The DELETE statement syntax may vary depending upon what type of value is passed into the procedure. Valid entries include EMPLOYEE_ID values or EMAIL values.
CREATE OR REPLACE PROCEDURE delete_employee(emp_value IN VARCHAR2) AS
is_number NUMBER := 0;
valid_flag BOOLEAN := FALSE;
sql_stmt VARCHAR2(1000);
emp_count NUMBER := 0;
BEGIN
sql_stmt := 'DELETE FROM EMPLOYEES ';
-- DETERMINE IF emp_value IS NUMERIC, IF SO THEN QUERY
-- DATABASE TO FIND OCCURRENCES OF MATCHING EMPLOYEE_ID
IF LENGTH(TRIM(TRANSLATE(emp_value, ' +-.0123456789', ' '))) IS NULL THEN
SELECT COUNT(*)
INTO emp_count
FROM EMPLOYEES
WHERE EMPLOYEE_ID = emp_value;
IF emp_count > 0 THEN
sql_stmt := sql_stmt || 'WHERE EMPLOYEE_ID = :emp_val';
valid_flag := TRUE;
END IF;
ELSE
SELECT COUNT(*)
INTO emp_count
FROM EMPLOYEES
WHERE EMAIL = upper(emp_value);
IF emp_count > 0 THEN
sql_stmt := sql_stmt || 'WHERE EMAIL = :emp_val';
valid_flag := TRUE;
ELSE
valid_flag := FALSE;
END IF;
END IF;
IF valid_flag = TRUE THEN
EXECUTE IMMEDIATE sql_stmt
USING emp_value;
DBMS_OUTPUT.PUT_LINE('Employee has been deleted'),
ELSE
DBMS_OUTPUT.PUT_LINE('No matching employee found, please try again'),
END IF;
END;
The procedure can be called by passing in either an EMPLOYEE_ID value or an EMAIL value. If a matching employee record is found, then it will be deleted from the database table.
How It Works
Dynamic SQL can be used to execute DELETE statements as well. In the solution to this recipe, a dynamic SQL string is built that will remove an employee entry that contains a matching EMPLOYEE_ID or EMAIL value that is passed into the procedure as a parameter. The parameter is checked to find out whether it is a numeric or alphanumeric value by using a combination of the LENGTH, TRIM, and TRANSLATE functions. If it is numeric, then it is assumed to be an EMPLOYEE_ID value, and the database is queried to see whether there are any matches. If the parameter is found to be alphanumeric, then it is assumed to be an EMAIL value, and the database is queried to see whether there are any matches. If matches are found in either case, then a dynamic SQL string is built to DELETE the matching record from the database.
In this example, native dynamic SQL is used to perform the database operation. The DBMS_SQL package can also be used to perform this task using the same techniques that were demonstrated in Recipe 8-3.