Dynamic SQL动态SQL.ppt

Dynamic SQL What Will I Learn? Recall the stages through which all SQL statements pass Describe the reasons for using dynamic SQL to create a SQL statement List four PL/SQL statements supporting Native Dynamic SQL Describe the benefits of EXECUTE IMMEDIATE over DBMS_SQL for Dynamic SQL Why Learn It? In this lesson, you learn to construct and execute SQL statements dynamically—in other words, at run time using the Native Dynamic SQL statements in PL/SQL. Dynamically executing SQL and PL/SQL code extends the capabilities of PL/SQL beyond query and transactional operations. The lesson also compares Native Dynamic SQL to the DBMS_SQL package, which provides similar capabilities. Execution Flow of SQL All SQL statements in the database go through various stages: Parse: pre-execution “is this possible?” checks including syntax, object existence, privileges and so on Bind: getting the actual values of any variables referenced in the statement Execute: the statement is executed Fetch: results are returned to the user. Some stages may not be relevant for all statements—for example, the fetch phase is applicable to queries but not DML. Execution Flow of SQL in PL/SQL Subprograms When a SQL statement is included in a PL/SQL subprogram, the parse and bind phases are normally done at compile time, ie when the procedure, function or package body is CREATEd. What if the text of the SQL statement is not known when the procedure is created ? How could the Oracle server parse it ? It couldn’t. For example, suppose we want to DROP a table, but the user will enter the table name at execution time: Dynamic SQL We use dynamic SQL to create a SQL statement whose text is not completely known in advance. Dynamic SQL: ? Is constructed and stored as a character string within a subprogram. ? Is a SQL statement with varying column data, or different conditions with or without placeholders (bind variables) ? Enables data-definition, data-control, or session-control statements to be written a