Database Programming with PL/SQL
12-1: Using Dynamic SQL
Practice Activities
Vocabulary
SQL statements that cannot be parsed at compile time, but must be parsed at run time. The text of these statements can, and probably will, change from execution to execution. - Native Dynamic SQL
A statement that prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. - EXECUTE IMMEDIATE
Try It / Solve It
1. Name three of the states that SQL statements go through.
Parse
Bind
Execute
2. List three reasons why using the EXECUTE IMMEDIATE command is preferable to using the DBMS_SQL package.
Is easier to use
Requires less code
Often executes faster because there are fewer statements to execute
3. The DEPARTMENTS and EMPLOYEES tables have two columns in common: department_id and
manager_id. Create a procedure that uses a single EXECUTE IMMEDIATE call to select and display a
department_id from either table, where the manager_id is 205. The procedure should accept the table
name as an input parameter and display the department_id from that table. Remember to handle any
possible exceptions that might be raised because we are selecting into a single variable, not using a cursor in this case. Test your procedure twice, once with each of the two tables.
CREATE OR REPLACE PROCEDURE display
(p_table_name VARCHAR2,p_manager_id NUMBER, p_department_id OUT NUMBER) IS
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'SELECT department_id FROM ' || p_table_name || ' WHERE manager_id = ' || p_manager_id;
EXECUTE IMMEDIATE sql_stmt INTO p_department_id;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too many rows returned');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No department with that manager');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Some Error');
END;
DECLARE
v_department_id NUMBER(3);
BEGIN
display('DEPARTMENTS', 205, v_department_id);
DBMS_OUTPUT.PUT_LINE(v_department_id);
END;
DECLARE
v_department_id NUMBER(3);
BEGIN
display('EMPLOYEES', 205, v_department_id);
DBMS_OUTPUT.PUT_LINE(v_department_id);
END;
4. Create a procedure called how_many_rows to display the table name of any chosen table, and the number of rows it contains. Test the procedure on the COUNTRIES and REGIONS tables.
DECLARE
v_department_id NUMBER(3);
BEGIN
display('DEPARTMENTS', 205, v_department_id);
DBMS_OUTPUT.PUT_LINE(v_department_id);
END;
CREATE OR REPLACE PROCEDURE how_many_rows
(p_table_name VARCHAR2, p_number_rows OUT NUMBER) IS
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'SELECT count(*) FROM ' || p_table_name;
EXECUTE IMMEDIATE sql_stmt INTO p_number_rows;
END;
DECLARE
v_number_rows NUMBER(4);
BEGIN
how_many_rows('WF_COUNTRIES', v_number_rows);
DBMS_OUTPUT.PUT_LINE(v_number_rows);
END;
DECLARE
v_number_rows NUMBER(4);
BEGIN
how_many_rows('WF_REGIONS', v_number_rows);
DBMS_OUTPUT.PUT_LINE(v_number_rows);
END;
5. Create a copy of the COUNTRIES table by executing the following SQL statement:
CREATE TABLE copy_countries AS SELECT * FROM countries;
Create a procedure that deletes all the rows from a chosen table and displays how many rows have been
deleted. Test your procedure on the COPY_COUNTRIES table.
CREATE OR REPLACE PROCEDURE del_rows (p_table_name VARCHAR2, p_number_rows OUT NUMBER) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM ' || p_table_name;
p_number_rows := SQL%ROWCOUNT;
END;
DECLARE
v_number_rows NUMBER(4);
BEGIN
del_rows('COPY_COUNTRIES', v_number_rows);
DBMS_OUTPUT.PUT_LINE(v_number_rows);
END;
0 Comments