DBMS_ SQL usage in ORACLE

The usage of DBMS_ SQL in ORACLE is applicable to select operations. If dynamic SQL statements are used, perform the following steps: open cursor ---> parse ---> define column ---> excute ---> fetch rows ---> close cursor; perform the following steps for dml operations (insert, update: open cursor ---> parse ---> bind variable ---> execute ---> close cursor; perform the following steps for the delete operation: open cursor ---> parse ---> execute ---> close cursor; example 1: create table test (n_id number, v_name varchar2 (50), d_insert_date d Ate); alter table test add constraint pk_id primary key (n_id); declare v_cursor number; v_ SQL varchar2 (200); v_id number; v_name varchar2 (50); v_date date; v_stat number; begin v_id: = 1; v_name: = 'test insert'; v_date: = sysdate; v_cursor: = dbms_ SQL .open_cursor; -- open the cursor v_ SQL: = 'insert into test (n_id, v_name, d_insert_date) values (: v_id,: v_name,: v_date) '; dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .na Tive); -- parse SQL dbms_ SQL .bind_variable (v_cursor, ': v_id', v_id); -- bind the variable forward (v_cursor,': v_name ', v_name); dbms_ SQL .bind_variable (v_cursor ,': v_date ', v_date); v_stat: = dbms_ SQL .execute (v_cursor); -- execute dbms_ SQL .close_cursor (v_cursor); -- close cursor commit; end; www.2cto.com Example 2: declare v_cursor number; v_ SQL varchar2 (200); v_id number; v_name varchar2 (50); v_stat number; begin v_name: =' Test update '; v_id: = 1; v_cursor: = dbms_ SQL .open_cursor; v_ SQL: = 'Update test set v_name =: v_name, d_insert_date =: v_date where n_id =: v_id '; dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .native); values (v_cursor, ': v_name', v_name); values (v_cursor, ': v_date', sysdate); values (v_cursor ,': v_id', v_id); v_stat: = dbms_ SQL .execute (v_cursor); dbms_ SQL .c Lose_cursor (v_cursor); commit; end; www.2cto.com Example 3: declare v_cursor number; v_ SQL varchar2 (200); v_id number; v_stat number; begin v_id: = 1; v_ SQL: = 'delete from test where n_id =: v_id'; v_cursor: = cursor; dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .native); cursor (v_cursor, ': v_id', v_id); v_stat: = dbms_ SQL .execute (v_cursor); dbms_ SQL .close_cursor (v_cursor); commit; End; example 4: declare v_cursor number; v_ SQL varchar2 (200); v_id number; v_name varchar2 (50); v_date varchar2 (10); v_stat number; begin v_ SQL: = 'select n_id, v_name, to_char (d_insert_date, ''yyyy-mm-dd'') from test'; v_cursor: = dbms_ SQL .open_cursor; -- open the cursor dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .native); -- resolve the cursor dbms_ SQL .define_column (v_cursor, 1, v_id); -- Define the column dbms_ SQL .define_column (v_cursor, 2, v_na Me, 50); -- Note: When the variable is of the varchar2 type, add the length www.2cto.com dbms_ SQL .define_column (v_cursor, 3, v_date, 10); v_stat: = dbms_ SQL .execute (v_cursor ); -- execute SQL loop exit when dbms_ SQL .fetch_rows (v_cursor) <= 0; -- fetch_rows moves the cursor in the result set. If the cursor does not reach the end, 1 is returned. Dbms_ SQL .column_value (v_cursor, 1, v_id); -- write the query results of the current row to the column defined above. Dbms_ SQL .column_value (v_cursor, 2, v_name); dbms_ SQL .column_value (v_cursor, 3, v_date); dbms_output.put_line (v_id | ':' | v_name | ':' | v_date ); end loop; end; using PL/SQL using dynamic SQL programming in PL/SQL programming process, there will be many places where dynamic SQL must be used, the DMBS_ SQL package provided by tb in the oracle system can help you solve the problem. (1) introduce the many functions and processes provided by the DBMS_ SQL system package. Here we briefly describe several frequently used functions: function open_cursor: open a dynamic cursor and return an integer; procedure close_cursor (c in out integer): closes a dynamic cursor. The parameter is the cursor opened by open_cursor. www.2cto.com procedure parse (c in integer, statement in varchar2, language_flag in integer ): the SQL statements provided by dynamic cursors are parsed. Parameter C indicates the cursor, statement indicates the SQL statement, and language-flag indicates the oracle version used to parse the SQL statement, which is V6, v7 and native (use native when you do not understand the connected database version); procedure define_column (c in integer, position in integer, column any datatype, [column_size in integer]): defines the corresponding values that can be obtained from a dynamic cursor. c indicates a dynamic cursor, positon indicates the position in the corresponding dynamic SQL statement (starting from 1), and column indicates the variable corresponding to the value, it can be of any type. column_size can only be used in types with column defined length, such as VARCHAR2 and CHAR. (There are many situations in this process, in this example, only the commonly used types are described.) function execute (c in integer): executes the cursor and returns an integer that represents the processing result (insert, delete, update makes sense, but can be ignored for select statements); function fetch_rows (c in integer): cyclically retrieves data from the cursor and returns an integer, 0 indicates that the cursor has been obtained. procedure column_value (c in integer, position in integer, value): assigns the obtained cursor data to the corresponding variable. c indicates the cursor, position is the position, and value is the corresponding variable; procedure bind_variable (c in integer, name in varchar2, value): defines the value of the corresponding field in the dynamic SQL statement (DML, c is the cursor, name is the field name, and value is the field value. The above are several functions and processes that are frequently used in the program, for other functions and procedures, see the Definition Statement dbmssql provided by oracle. SQL (2) general process www.2cto.com for general select operations, if dynamic SQL statements are used, the following steps are required: open cursor ---> parse ---> define column ---> excute ---> fetch rows ---> close cursor; perform the following steps for dml operations (insert, update: open cursor ---> parse ---> bind variable ---> execute ---> close cursor; perform the following steps for the delete operation: open cursor ---> parse ---> execute ---> close cursor; (3) in the specific case, I will analyze a program in my development system. This process is a stock technical curve computing program, which extracts the data from the real-time data table and calculates the curve according to the formula, tb calculates the data and saves the results to the Technical curve table. create or replace procedure R_Ma_Main (www.2cto.com pid varchar2, pend varchar2, pinterval varchar2, totab varchar2) is -- Define the array type Date_type is table of varchar2 (12) index by binary_integer; type Index_type is table of number index by binary_integer; TempDate Date_Type; -- Time Array TempIndex Index_Type; -- stock closing price array TempMa Index_Type; -- ma technical curve data cursor1 integer; -- cursor cursor2 integer; -- cursor rows_processed integer; -- execute the cursor to return TempInter integer; -- participate in the calculation of the number of values TempVal integer; -- computing time type TempSql varchar2 (500); -- dynamic SQL statement MyTime varchar2 (12 ); -- time MyIndex number; -- value MidIndex number; -- intermediate variable I integer: = 999; j integer; begin www.2cto.com TempInter: = to_number (substr (pinterval,); TempVal: = to_number (substr (pinterval, 5, 2); TempSql: = R_GetSql1 (pid, pend, TempVal); -- get the SQL statement of the selected data -- get the real-time data of the day, save them to the array in sequence: cursor1: = dbms_ SQL .open_cursor; -- create the cursor dbms_ SQL .parse (cursor1, TempSql, dbms_ SQL .native); -- parse dynamic SQL statements, take two fields, time and price, specifically, the time uses 14-bit varchar2 to represent dbms_ SQL .define_column (cursor1, 1, MyTime, 12); -- Define the variable dbms_ SQL .define_column (cursor1, 2, MyIndex) corresponding to each field in the SQL statement ); rows_processed: = dbms_ SQL .execute (cursor1); loop
If substring (cursor1)> 0 then begin dbms_ SQL .column_value (cursor1, 1, MyTime); dbms_ SQL .column_value (cursor1, 2, MyIndex); TempDate (I): = MyTime; TempIndex (I ): = MyIndex; I: = I-1; -- enter the array end in inverted order; else exit; end if; end loop; dbms_ SQL .close_cursor (cursor1 ); -- if the amount of data obtained is not enough, the program if I> 999-TempInter then goto JumpLess; end if; -- initializes the intermediate variable MidIndex: = 0; TempIndex (I): = 0; for j in I .. I + TempInter-1 loop MidIndex: = MidIndex + TempIndex (j); end loop; www.2cto.com -- calculate the ma value of the day data in sequence, and save it to the ma array for j in I + TempInter .. 999 loop MidIndex: = MidIndex-TempIndex (j-TempInter) + TempIndex (j); TempMa (j): = MidIndex/TempInter; end loop; if TempVal <6 then -- if the calculation is minute-to-day ma technical curve begin cursor2: = dbms_ SQL .open_cursor; TempSql: = 'insert into' | totab | 'values (: r_no,: I _interval,: I _time,: I _inde X) '; dbms_ SQL .parse (cursor2, TempSql, dbms_ SQL .native); for j in I + TempInter .. 999 loop handler (cursor2, 'r _ no', pid); dbms_ SQL .bind_variable (cursor2, 'I _ interval', pinterval); dbms_ SQL .bind_variable (cursor2, 'I _ time ', tempDate (j); dbms_ SQL .bind_variable (cursor2, 'I _ Index', TempMa (j); rows_processed: = dbms_ SQL .execute (cursor2); -- insert data end loop; end; end if; commit; dbms_ SQL .close_c Ursor (cursor2); -- insufficient data volume jumps out <JumpLess> null; -- exception Processing, irrelevant to this topic end;/www.2cto.com (4) in my opinion, in the process of using the dbms_ SQL system package, the method is simple and flexible, but you still need to pay attention to some problems: 1. During the whole program design process, the cursor operation cannot be omitted. Once a step is omitted, the compilation process of the program will both fail. If the cursor is not closed at the end of the program, an error occurs during the next call. 2. In addition to normal select, insert, update, delete, and other static SQL statements, dbms_ SQL can also perform DDL operations such as create, however, when performing such operations, you should first explicitly grant the execution user the corresponding system permissions, such as create table. you only need to open cursor ---> prase ---> close cursor to complete this operation. above I have a little opinion on dbms_ SQL at work. If not, please correct me. if you want to learn more about dbms_ SQL, read dbmssql. SQL file. www.2cto.com is attached with an Oracle built-in process description (powerful): -- The flow of procedure callwill typically look like this: -- ----------- -- | open_cursor | -- ----------- -- | -- v -- ----- -- ------------> | parse | -- | ----- -- | --------- -- | v | -- | -------------- | -- | --------> | bind_variable | -- | ^ ------------- | -- | | -- | ----------- | -- | <-------- -- | V www.2cto.com -- | query? ---------- Yes --------- -- | no | -- | v -- | ------- ------------- -- | -----------> | execute |-> | define_column | -- | ------- | ------------- -- | ------------ | -- | ---------- | -- | v -- | -------------- | ------- -- |-> | variable_value | ------> | execute | -- | -------------- | | ------- -- | ---------- | -- | v -- | ---------- -- | <----------- | -----> | fetch_rows | -- | ---------- -- | v -- | ---------------------- -- | column_value | -- | variable_value | -- | ------------------- -- | | -- | <-------------------------- -- | -- ----------------- | www.2cto.com -- | -- v -- ------------ -- | close_cursor | -------------------------------