Examples of Dynamic SQL

Before explaining the details of DBMS_SQL, let's look at a few concrete examples. 

When you issue a SQL statement via DBMS_SQL, you will have to write much more code than you would by simply executing a native SQL statement, such as an implicit cursor created with a SELECT statement. To get a feel for the differences between these two approaches, consider the following code. 

This first procedure uses native SQL to give every employee in the specified department a raise: 

PROCEDURE  giveraise (dept_in IN INTEGER, raise_in IN NUMBER) IS 

BEGIN 

   UPDATE employee SET salary = salary + raise_in WHERE department_id = dept_in; 

END; 

The following procedure does the same thing, but with DBMS_SQL. Given the volume of code (and the subsequent overhead), you should only use DBMS_SQL when your SQL statement is truly dynamic or involves DDL. 

PROCEDURE giveraise (dept_in IN INTEGER, raise_in IN NUMBER) IS 

   cursor_handle INTEGER; 

   emps_updated INTEGER; 

   BEGIN /* Create a cursor to use for the dynamic SQL */ 

      cursor_handle := DBMS_SQL.OPEN_CURSOR; 

      /* || Construct the SQL statement and parse it in Version 7 mode. 

         || Notice that the statement includes two bind variables; these 

         || are "placeholders" in the SQL statement. */ 

      DBMS_SQL.PARSE (cursor_handle, 'UPDATE employee SET salary = salary + :raise_amount ' || 'WHERE department_id = :dept', DBMS_SQL.V7); 

      /* Now I must supply values for the bind variables */ 

      DBMS_SQL.BIND_VARIABLE (cursor_handle, 'raise_amount', raise_in); 

      DBMS_SQL.BIND_VARIABLE (cursor_handle, 'dept', dept_in); /* Execute the SQL statement */ 

      emps_updated := DBMS_SQL.EXECUTE (cursor_handle); /* Close the cursor */ 

      DBMS_SQL.CLOSE_CURSOR (cursor_handle); 

      

      EXCEPTION 

         WHEN OTHERS THEN /* Clean up on failure too. */ 

             DBMS_SQL.CLOSE_CURSOR (cursor_handle); 

         END; 

Truly dynamic SQL occurs when you literally construct the SQL statement from runtime variable values. This is shown in the next example. The create_index procedure creates an index where the name of the index, the name of the table, and the column on which the index is to be created are passed as parameters to the procedure. 

This action would be impossible without DBMS_SQL for two reasons: this is a DDL call and the SQL statement isn't known until the procedure is called. 

/* Filename on companion disk: creind.sp */ 

CREATE OR REPLACE PROCEDURE create_index (index_in IN VARCHAR2, table_in IN VARCHAR2, column_in in VARCHAR2) 

IS 

   cursor_handle INTEGER; 

   feedback INTEGER; 

   BEGIN 

   /* Create a cursor to use for the dynamic SQL */ 

   cursor_handle := DBMS_SQL.OPEN_CURSOR; 

   /* Construct the SQL statement and parse it in native mode. */ 

   DBMS_SQL.PARSE (cursor_handle, 'CREATE INDEX ' || index_in || ' ON ' || table_in || '( ' || column_in || ')', DBMS_SQL.NATIVE); 

   /* You should always execute your DDL! */ 

   feedback := DBMS_SQL.EXECUTE (cursor_handle); 

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);

 END create_index; 

Fuentes.

Artículo:   "2. Executing Dynamic SQL and PL/SQL" Publicado en https://docstore.mik.ua/. Consultado el 10/10/2022.

URL: https://docstore.mik.ua/orelly/oracle/bipack/ch02_01.htm