The use of the DBMS_SQL(国外英文资料).docVIP

The use of the DBMS_SQL(国外英文资料) The use of the DBMS_SQL -- -- -- -- - Typically, DBMS_SQL packages are typically divided into several steps: Open cursor: open the cursor Parse cursor: parse the SQL statement you want to execute Bind variable: if you want to include variables in the SQL statement you want to execute, you need to bind the variable Execute: execute the SQL statement Close cursor: close the cursor after execution. If you also need to return the result set that executes SQL, you also need to use the define_column, define_array, and so on. -- -- -- -- - The following are detailed in different scenarios: Before you do the presentation, prepare some basic data The create table demo (a number, b number, c number); The begin For I in 1. 15 loop Insert into the demo values (dbms_random. Value 2) * 100, Round (dbms_random. Value, 2) * 100, Round (dbms_random.value, 2) * 100); End loop; Commit; The end; After the base data is complete, some specific scenarios are being analyzed. Execute the general select statement First, the most common ones: CREATE OR REPLACE PROCEDURE define_column (no IN NUMBER) IS Cursor_name INTEGER: = dbms_sql.open_cursor; When you initialize the parameter, you can open the cursor; Row_process INTEGER; V_b NUMBER; The BEGIN Parsing the SQL to be executed. Dbms_sql. Parse (cursor_name, select * from demo where a = : no, Dbms_sql. Native); The bind_variable can be omitted if no arguments are required in the SQL to be executed Dbms_sql. Bind_variable (cursor_name, no, no); If you need to return the result of the query statement, you must define the return field using the define_column function before exec; The first parameter of the define_column function is the cursor name defined initially, - the second parameter is refers to the need to return the field in which the column in the query results, in this case the returned field is the query results in the second column, the column b; The third parameter is the variable required to receive