PL/SQL 12-1: Using Dynamic SQL

 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;

Post a Comment

0 Comments