Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Note: Method 4 is the most complex method and is explained
However, some applications must accept (or build) and process a variety of SQL statements at runtime. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the statement's makeup is unknown until run time. Such statements can, and probably will, change from execution to execution. They are aptly called dynamic SQL statements.
Unlike static SQL statements, dynamic SQL statements are not embedded in your source program. Instead, they are stored in character strings input to, or built by, the program at runtime. They can be entered interactively or read from a file.
For example, your program might simply prompt users for a search condition to be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A more complex program might allow users to choose from menus listing SQL operations, table and view names, column names, and so on. Thus, dynamic SQL lets you write highly flexible applications.
However, some dynamic queries require complex coding, the use of special data structures, and more runtime processing. While you might not notice the added processing time, you might find the coding difficult, unless you fully understand dynamic SQL concepts and methods.
In practice, static SQL will meet most of your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Its use is suggested when one or more of the following is unknown at precompile time:
CLOSE DECLARE DESCRIBE EXECUTE FETCH INCLUDE OPEN PREPARE WHENEVER
In most cases, the character string can contain placeholders. They hold places in the SQL statement for actual host variables. Placeholders are not host variables. You do not need to declare them and can name them anything you like. For example, Oracle makes no distinction between the following two strings:
DELETE FROM emp WHERE mgr = :MGR_NUMBER AND job = :JOB_TITLE
DELETE FROM emp WHERE mgr = :M AND job = :J
The four methods are increasingly general. That is, Method 2 encompasses Method 1, Method 3 encompasses Methods 1 and 2, and so on. However, each method is most useful for handling a certain kind of SQL statement, as the following table shows:
DELETE FROM emp WHERE deptno = 20;
GRANT SELECT ON emp TO scott;
With Method 1, the SQL statement is parsed each time it is executed.
INSERT INTO emp (ename, job) VALUES (:EMP_NAME, :JOB_TITLE);
DELETE FROM emp WHERE empno = :EMP_NUMBER;
With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables.
SELECT deptno, MIN(sal), MAX(sal) FROM emp GROUP BY deptno;
SELECT ename, empno FROM emp WHERE deptno = :DEPT_NUMBER;
Method 3 is used for dynamic queries with a known select list.
INSERT INTO emp (<unknown>) VALUES (<unknown>);
SELECT <unknown> FROM emp WHERE deptno = 20;
Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables.
With Methods 2 and 3, the number and types of placeholders for input host variables and the number and datatypes of the input host variables must be known at precompile time.
Each succeeding method imposes fewer constraints on your application, but is more difficult to code. As a rule, use the simplest method you can. However, if a dynamic SQL statement will be executed repeatedly by Method 1, use Method 2 instead to avoid reparsing for each execution.
Method 4 provides maximum flexibility, but requires complex coding and a full understanding of dynamic SQL concepts. In general, use Method 4 only if you cannot use Method 1, 2, or 3.
The decision logic in Figure 8 - 1 will help you choose the right method.
Figure 8 - 1. Choosing a Dynamic SQL Method
DELETE FROM table_name WHERE column_name = constant;
CREATE TABLE table_name ...;
DROP INDEX index_name;
UPDATE table_name SET column_name = constant;
GRANT SELECT ON table_name TO username;
REVOKE RESOURCE FROM username;
This method is the standard way to perform dynamic DDL statements.
Method 1 parses, then immediately executes the SQL statement using the EXECUTE IMMEDIATE command. The command is followed by a character string containing the SQL statement to be executed. The string can be contained in a host variable, or it can be a literal.
The syntax of the EXECUTE IMMEDIATE statement is:
EXEC SQL EXECUTE IMMEDIATE { :HOST_STRING | STRING_LITERAL };
Using a host string variable, you use the EXECUTE IMMEDIATE command as follows:
COMMAND_STRING : constant string :=
"GRANT SELECT ON my_table TO MILLER";
...
EXEC SQL EXECUTE IMMEDIATE :COMMAND_STRING;
You can also use string literals, as the following example shows:
EXEC SQL EXECUTE IMMEDIATE "REVOKE RESOURCE FROM MILLER";
Because EXECUTE IMMEDIATE parses the input SQL statement before every execution, Method 1 is best for statements that are executed only once.
-- Copyright (c) 1994 by Oracle Corporation -- DYN1 : -- 1) Logon to ORACLE -- 2) Prompt for a new table name -- 3) Create the duplicate of emp with the new table name with text_io; procedure DYN1 is USERNAME : constant STRING := "SCOTT"; PASSWORD : constant STRING := "TIGER"; CREATE_COMMAND : STRING(1..39) := "CREATE TABLE AS SELECT * FROM EMP"; CMD_LEN : NATURAL; SQL_ERROR : exception; begin EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; -- logon EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; TEXT_IO.PUT("Please enter a name for a new EMP table"); TEXT_IO.PUT(" (5 characters or less): "); TEXT_IO.GET_LINE(CREATE_COMMAND(14..18), CMD_LEN); EXEC SQL EXECUTE IMMEDIATE :CREATE_COMMAND; TEXT_IO.NEW_LINE; TEXT_IO.PUT("The table """ & CREATE_COMMAND(14..18)); TEXT_IO.PUT(""" was created successfully."); -- Note: The COMMIT was actually done at the time the -- command was executed, since all DDL commands -- involve an implicit COMMIT. EXEC SQL COMMIT RELEASE; exception when SQL_ERROR => EXEC SQL WHENEVER SQLERROR CONTINUE; TEXT_IO.PUT_LINE("** ORACLE ERROR OCCURED **"); TEXT_IO.PUT_LINE(ORACLE.ERROR.MESSAGE); EXEC SQL ROLLBACK RELEASE; end DYN1;
With Method 2, the SQL statement can contain placeholders for input host variables and indicator variables. You can PREPARE the SQL statement once, then EXECUTE it repeatedly using different values of the host variables. Furthermore, you need not rePREPARE the SQL statement after a COMMIT or ROLLBACK unless you log off and reconnect.
You can also use EXECUTE for nonqueries with Method 4. For more information about dynamic SQL Method 4, see chapter 9, "Implementing Dynamic SQL Method 4."
The syntax of the PREPARE statement follows:
EXEC SQL PREPARE statement_name
FROM { :HOST_STRING | STRING_LITERAL };
PREPARE parses the SQL statement and gives it a name.
The statement name is an identifier used by the precompiler, not a host or program variable, and should not be declared as a program variable. It simply designates the PREPAREd statement you want to EXECUTE.
The syntax of the EXECUTE statement is
EXEC SQL EXECUTE statement_name [USING host_variable_list];
where the host variable list syntax is
:HOST_VARIABLE1[[ ][ INDICATOR]:INDICATOR_VAR1]
[, :HOST_VARIABLE2[[ ][ INDICATOR]:INDICATOR_VAR2], ...]
Note: The optional keyword INDICATOR can be used to separate a host variable and an indicator variable.
EXECUTE executes the parsed SQL statement, using the values supplied for each input host variable.
In the following example, the input SQL statement contains the placeholder N:
...
EMP_NUMBER : integer;
DELETE_STMT : constant string :=
"DELETE FROM emp WHERE empno = :N";
...
-- sql_stmt is the name of the prepared statement, not a variable
EXEC SQL PREPARE sql_stmt FROM :DELETE_STMT;
loop
PUT("Enter employee number: ");
INTEGER_TEXT_IO.GET(EMP_NUMBER);
exit when EMP_NUMBER = 0;
-- no reparsing needed
EXEC SQL EXECUTE sql_stmt USING :EMP_NUMBER;
end loop;
...
With Method 2, the types of input host variables must be known at precompile time. In the last example, EMP_NUMBER was declared as type integer. It could also have been declared as type float or even string, because Oracle supports conversions from each of these datatypes to NUMBER (the datatype of the empno column in the EMP table).
Every placeholder in the PREPAREd dynamic SQL statement must correspond to a host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd statement, each appearance must correspond to a host variable in the USING clause.
The names of the placeholders need not match the names of the host variables. However, the order of the placeholders in the PREPAREd dynamic SQL statement must match the order of corresponding host variables in the USING clause.
If one of the host variables in the USING clause is an array, all must be arrays.
To specify null values, you can associate indicator variables with host variables in the USING clause. For more information, see "Using Indicator Variables" .
-- Copyright (c) 1994 by Oracle Corporation -- DYN2 : -- 1) Logon to ORACLE -- 2) Load a string with an UPDATE statement -- 3) Prompt user to finish the WHERE clause -- of the UPDATE statement -- 4) Prepare the concatenated command string -- 5) Execute the prepared statement -- 6) Print the rows processed with text_io, varchar_text_io, integer_text_io; procedure DYN2 is use text_io, integer_text_io; ORACLE_ID : ORACLE.VARCHAR(20); ERR_MESSAGE : ORACLE.VARCHAR(70); COMMAND_STR : STRING(1..70); COMMAND_LEN : NATURAL; COMMISSION : NATURAL; SQL_ERROR : exception; begin EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; -- Logon using VARCHARs ORACLE_ID.BUFFER(1..11) := "SCOTT/TIGER"; ORACLE_ID.LENGTH := 11; EXEC SQL CONNECT :ORACLE_ID; -- Note that the "PLACE_HOLDER" in the UPDATE statement -- is not a program variable. The precompiler recognizes -- that there needs to be a variable bound in that position -- and it will locate the actual variable from the USING -- clause of the EXEC SQL EXECUTE statement. -- If there happened to be more than one placeholder, -- they would be bound in order of occurrence in the SQL -- statement to the same number and order of variables in the -- USING clause. COMMAND_STR(1..42) := "UPDATE EMP SET COMM = :PLACE_HOLDER WHERE "; PUT_LINE("Please enter a WHERE clause for the following: "); NEW_LINE; PUT(" " & COMMAND_STR(1..42) & "? "); GET_LINE(COMMAND_STR(43..60), COMMAND_LEN); EXEC SQL PREPARE S1 FROM :COMMAND_STR(1..COMMAND_LEN); PUT("Set the commission to (enter an integer): "); GET(COMMISSION); -- Execute the prepared statement S1 using the variable -- COMMISSION in the place of PLACE_HOLDER. If there were -- no variables in the statement, the USING clause could be -- omitted and/or Dynamic SQL Method #1 could be used. EXEC SQL EXECUTE S1 USING :COMMISSION; -- Now print the SQLROWS to see how many rows were updated. -- Note: ORACLE.SQLROWS is identical to SQLCA.SQLERRD(3). PUT("You just updated "); PUT(ORACLE.SQLROWS, WIDTH => 2); PUT(" row"); if ORACLE.SQLROWS /= 1 then PUT_LINE("s."); else PUT_LINE("."); end if; EXEC SQL COMMIT RELEASE; exception when SQL_ERROR => PUT_LINE("** ORACLE ERROR OCCURED **"); NEW_LINE; -- get the error message (in a VARCHAR) using -- the error message procedure ORACLE.ERROR.MESSAGE(ERR_MESSAGE); VARCHAR_TEXT_IO.PUT_LINE(ERR_MESSAGE); EXEC SQL ROLLBACK RELEASE; end DYN2;
For Method 3, the number of columns in the query select list and the number of placeholders for input host variables must be known at precompile time. However, the names of database objects such as tables and columns need not be specified until runtime. Clauses that limit, group, and sort query results (such as WHERE, GROUP BY, and ORDER BY) can also be specified at runtime.
With Method 3, you use the following sequence of embedded SQL statements:
PREPARE statement_name FROM { :HOST_STRING | STRING_LITERAL };
DECLARE cursor_name CURSOR FOR statement_name;
OPEN cursor_name [USING host_variable_list];
FETCH cursor_name INTO host_variable_list;
CLOSE cursor_name;
Each of these statements is described in the following sections.
SELECT_STMT := "SELECT mgr, job FROM emp WHERE sal < :SALARY";
EXEC SQL PREPARE sql_stmt FROM :SELECT_STMT;
Commonly, the query's WHERE clause is input from a terminal at runtime, or is generated by the application.
Remember, SQL_STMT is an identifier used by the precompiler, not a program variable. However it identifies the SQL statement, and it must be unique.
Continuing with our example, DECLARE defines a cursor named EMP_CURSOR and associates it with SQL_STMT, as follows:
EXEC SQL DECLARE EMP_CURSOR CURSOR FOR SQL_STMT;
Like SQL_STMT, EMP_CURSOR is an identifier used by the precompiler, not a program variable. However, it must be unique. If you declare two cursors using the same statement name, the Pro*Ada Precompiler considers the two cursors synonymous.
In our example, OPEN allocates EMP_CURSOR and assigns the host variable salary to the WHERE clause, as follows:
EXEC SQL OPEN emp_cursor USING :SALARY;
In our example, FETCH returns a row from the active set and assigns the values of columns MGR and JOB to host variables MGR_NUMBER and JOB_TITLE, as follows:
EXEC SQL FETCH emp_cursor INTO :MGR_NUMBER, :JOB_TITLE;
In our example, CLOSE disables EMP_CURSOR, as follows:
EXEC SQL CLOSE emp_cursor;
The sequence of statements used in the example follows:
SELECT_STMT := "SELECT mgr, job FROM emp WHERE sal < :SALARY";
EXEC SQL PREPARE sql_stmt FROM :SELECT_STMT;
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
EXEC SQL OPEN emp_cursor USING :SALARY;
EXEC SQL FETCH emp_cursor INTO :MGR_NUMBER, :JOB_TITLE;
EXEC SQL CLOSE emp_cursor;
-- Copyright (c) 1994 by Oracle Corporation -- DYN3 : -- 1) Declare the CONNECT BY statement -- 2) Associate a cursor with the statement -- 3) Logon to ORACLE -- 4) Assign a value to the host variable and complete -- the statement text -- 5) OPEN the cursor (executing the statement) -- 6) FETCH all the rows -- 7) CLOSE the cursor and log off with text_io, integer_text_io, varchar_text_io; procedure DYN3 is use text_io, integer_text_io; function "="(LEFT, RIGHT : ORACLE.UNSIGNED_SHORT) return BOOLEAN renames ORACLE."="; ONAME : constant STRING := "SCOTT"; OPASSWORD : constant STRING := "TIGER"; ENAME : STRING(1..10); JOB : STRING(1..9); SAL : STRING(1..6); BOSS : ORACLE.VARCHAR(10); EXCLUDE : ORACLE.VARCHAR(10); SEL_STATEMENT : STRING(1..142); SEL_LEN : integer; SQL_ERROR : exception; -- This precompiler directive is used because the PREPARE is -- done after the DECLARE CURSOR (forward reference). -- Since the DECLARE statements are directives (no code is -- generated), they may be placed with variable declarations -- but must be present within the same physical file as the -- remaining cursor operations (i.e. OPEN, FETCH, etc.). EXEC SQL DECLARE S STATEMENT; EXEC SQL DECLARE C1 CURSOR FOR S; EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; begin -- now start main code section EXEC SQL CONNECT :ONAME IDENTIFIED BY :OPASSWORD; SEL_STATEMENT(1..142) := "SELECT ename, job, sal " & "FROM emp " & "WHERE ename != UPPER(:EXCLUDE) " & "CONNECT BY PRIOR empno = mgr " & "START WITH ename = UPPER(:BOSS) " & "ORDER BY "; loop PUT("List information about employees who work for " & "(CR to exit): "); VARCHAR_TEXT_IO.GET_LINE(BOSS); -- Note that this is using ORACLE."=" (See above). exit when BOSS.LENGTH = 0; -- Let's exclude the boss from the list. EXCLUDE := BOSS; PUT("Order by which column in the emp table ? "); GET_LINE(SEL_STATEMENT(136..142), SEL_LEN); EXEC SQL PREPARE S FROM :SEL_STATEMENT(1..SEL_LEN); EXEC SQL OPEN C1 USING :EXCLUDE, :BOSS; PUT("These people work for "); VARCHAR_TEXT_IO.PUT_LINE(BOSS); NEW_LINE; PUT_LINE(" NAME SALARY JOB"); PUT_LINE("---------- -------- ---------"); FETCH_LOOP : loop declare DONE_FETCHING : exception; begin EXEC SQL WHENEVER NOT FOUND raise DONE_FETCHING; EXEC SQL FETCH C1 INTO :ENAME, :JOB, :SAL; PUT_LINE(" " & ENAME & SAL & " " & JOB); exception when DONE_FETCHING => NEW_LINE; PUT(ORACLE.SQLROWS); PUT(" employee"); if ORACLE.SQLROWS /= 1 then PUT("s"); end if; PUT_LINE(" returned."); NEW_LINE; exit; end; end loop FETCH_LOOP; EXEC SQL CLOSE C1; end loop; exception EXEC SQL WHENEVER SQLERROR CONTINUE; when SQL_ERROR => PUT_LINE("** ORACLE ERROR OCCURRED **"); PUT_LINE(ORACLE.ERROR.MESSAGE); EXEC SQL ROLLBACK RELEASE; end DYN3;
However, there are two differences in the way the precompiler handles SQL and PL/SQL:
You must put all host variables in the USING clause. When the PL/SQL string is EXECUTEd, host variables in the USING clause replace corresponding placeholders in the PREPAREd string. Although the precompiler treats all PL/SQL host variables as input host variables, values are assigned correctly. Input (program) values are assigned to input host variables, and output (column) values are assigned to output host variables.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |