Oracle_dbms_ SQL Application

Today, I have nothing to do. I have studied dbms_ SQL. below is the information I just collected online. Write it down.

 

Main functions in dbms_ SQL encapsulation:
1. open_cursor: returns the ID of the new game subject.
2. parse: parse the statement to be executed
3. bind_variable: connect the specified quantity to a specific variable.
4. define_coloumn: defines a field variable. Its value corresponds to the value of an element at a certain position in the specified cursor (used only for select statements)
5. Execute: Execute the specified cursor.
6. execute_and_fetch: Execute the specified cursor and retrieve records.
7. fetch_rows: retrieve records from the specified cursor
8. column_value: return the elements at the specified position in the cursor.
9. is_open: returns the true value when the specified cursor status is open.
10. close_cursor: closes the specified cursor and releases the memory.
11. last_error_position: return the byte offset of an error SQL statement.
12. last_row_id: returns the rowid of the last record.
13. last_ SQL _function_code: return the SQL function code of the statement.

Example 1:

-- This is an example of creating a table. This process has two parameters: Table Name and field and its type list. <Br/> Create or replace procedure ddlproc (tablename varchar2, cols varchar2) as <br/> cursor1 integer; <br/> begin <br/> cursor1: = dbms_ SQL .open_cursor; <br/> dbms_ SQL .parse (cursor1, 'create table 'tablename' ('cols') ', dbms_ SQL .v7); <br/> dbms_ SQL .close_cursor (cursor1); <br/> end;

 

Example 2:

-- Use the dbms_ SQL package and cursor to calculate the number of all table rows under the user <br/> declare <br/> t_c1_tname user_tables.table_name % type; <br/> t_command varchar2 (200 ); <br/> t_cid integer; <br/> t_total_records number (10); <br/> stat integer; <br/> row_count integer; <br/> t_limit INTEGER: = 0; -- only tables with records greater than 0 can be retrieved. </P> <p> cursor C1 is select table_name from user_tables order by table_name; -- find the names of all tables <br/> begin <br/> t_limit: = 0; <br/> open C1; </P> <p> loop <br/> fetch C1 into t_c1_tname; -- retrieve a table name <br/> exit when C1 % notfound; -- if the cursor record is obtained, exit the loop <br/> t_command: = 'select count (0) from' | t_c1_tname; -- Define the SQL command <br/> t_cid: = dbms_ SQL .open_cursor; -- create a cursor <br/> dbms_ SQL .parse (t_cid, t_command, dbms_ SQL .native); -- issue a statement to the server and check its syntax and syntax errors <br/> dbms_ SQL .define_column (t_cid, 1, t_total_records); -- defines the data type and size of the variable that will receive data from the fetchrows () function <br/> stat: = dbms_ SQL .execute (t_cid); -- execute this statement, because the query is executed, you must follow the fetch_rows function and retrieve data for a single row <br/> row_count: = dbms_ SQL .fetch_rows (t_cid ); -- retrieves a row of data and places it in a local buffer <br/> dbms_ SQL .column_value (t_cid, 1, t_total_records); -- returns the value of a Column Retrieved by calling fetchrows, the value of this column is stored in t_total_records </P> <p> If t_total_records> t_limit then <br/> dbms_output.put_line (rpad (t_c1_tname, 55 ,'') | <br/> to_char (t_total_records, '000000') | 'record (s) '); <br/> end if; </P> <p> dbms_ SQL .close_cursor (t_cid); <br/> end loop; </P> <p> close C1; <br/> end;