Use of Dbms_sql in Oracle

First, using Dbms_sql to execute the query

Use Dbms_sql to execute SELECT statements in the order of open cursor-->parse-->define column-->execute-->fetch rows-->close cursor;

1. Create a team table structure, as shown in:

Proteamid: Primary Key ID, Proteamname: Team name, Jctype: locomotive type, Workflag: Job identification

2, write the stored procedure, use Dbms_sql to query the data information from the Dict_proteam, and output the result:

Create or Replace procedure Pro_dict_proteam is/** uses Dbms_sql to execute SELECT statements in the order of open Cursor-->parse-->define column- ->execute-->fetch rows-->close cursor;**/--Define variable v_cursor number;--cursor ID v_sql varchar2 (500); -For storing SQL statements v_proteam_id number;--team ID v_proteam_name varchar2 (50);--The team name V_count number;--there is no actual meaning, just save       Drop function return value--v_jctype varchar: = ', ss3b, '; V_workflag number:=1;--query condition field begin--:v_workflag is the placeholder--select the first column in the statement is Proteamid, and the second column is Proteamname v_sql:= ' s       Elect Proteamid,proteamname from Dict_proteam where Workflag=:v_workflag '; v_cursor:=dbms_sql.open_cursor;--Open Cursor Dbms_sql.parse (v_cursor,v_sql,dbms_sql.native);--Parse dynamic SQL statement--bind input parameter, V_WO       The value of Rkflag is passed to: V_workflag dbms_sql.bind_variable (V_cursor, ': V_workflag ', v_workflag);       --Define the column, v_proteam_id corresponds to the first column in the SELECT statement Dbms_sql.define_column (V_CURSOR,1,V_PROTEAM_ID); --Define the column, V_proteam_name corresponds to the second column in the SELECT statement, the length is Dbms_sql.define_columN (v_cursor,2,v_proteam_name,50);       --Execute Dynamic SQL statement v_count:=dbms_sql.execute (v_cursor); Dbms_output.put_line (' v_count= ' | |       V_count); Loop--fetch_rows moves the cursor in the result set, returns 1 if the end is not reached-retrieves the data from the cursor into the buffer, and the value of the buffer can only be--column by the function              _value () Read exit when Dbms_sql.fetch_rows (V_cursor) <=0; --Reads the value of the column of the buffer into the corresponding variable--writes the query result of the current row to the column defined above--the value of the first column is read into v_proteam_id dbms_sql.column_val              UE (V_CURSOR,1,V_PROTEAM_ID);              --The value of the second column is read into V_proteam_name dbms_sql.column_value (v_cursor,2,v_proteam_name); --Print the value of the variable dbms_output.put_line (v_proteam_id| | ') '||       V_proteam_name);       End Loop; Dbms_sql.close_cursor (v_cursor);--Close the cursor end;

3. Execute Stored Procedure

Begin  --Call the procedure  pro_dict_proteam;end;

4. Test output results

V_count=0
1 Electric EMU
2 Motor EMU
3 cars in the car set
4 pantograph Group
5 Brake Set
6-line Security device Group
8 Instrument Group
9 Charging Unit
10 Flaw Detection Group
Ii. using Dbms_sql to execute DML statements

Insert, update its order is: Open cursor-->parse-->bind variable-->execute-->close cursor;

The delete is in the order: open cursor-->parse-->execute-->close cursor;

1. Create a test table structure:

CREATE TABLE Tb_test2 (  ID number not   null,  NAME VARCHAR2 (+),  SEX  CHAR (5))

2. Create a stored procedure and insert data into TB_TEST2 using Dbms_sql

Create or Replace procedure pro_tb_test2/** use Dbms_sql to execute DML statements INSERT, update their order to open Cursor-->parse-->bind Variab  Le-->execute-->close cursor;       Delete whose order is open cursor-->parse-->execute-->close cursor;**/is v_cursor number;       V_ID number;       V_name varchar2 (100);       V_sex char (5);       V_sql varchar2 (100);       V_count Number;begin v_id:=1;       v_name:= ' Tom ';       V_sex:= ' man ';       v_sql:= ' INSERT into Tb_test2 (Id,name,sex) VALUES (: V_id,:v_name,:v_sex) ';       V_cursor:=dbms_sql.open_cursor;       Dbms_sql.parse (v_cursor,v_sql,dbms_sql.native);       Dbms_sql.bind_variable (V_cursor, ': v_id ', v_id);       Dbms_sql.bind_variable (V_cursor, ': V_name ', v_name);       Dbms_sql.bind_variable (V_cursor, ': V_sex ', v_sex);       V_count:=dbms_sql.execute (V_cursor);       Dbms_sql.close_cursor (V_cursor); Dbms_output.put_line (' Data inserted successfully! ' | |       V_count); Commit Exception when others then Dbms_output.put_line (' The exception message appears! '); end;

3. Test the stored procedure

Begin  --Call the procedure  pro_tb_test2;end;

4. Result output: Data inserted successfully! 1

5, Query the TB_TEST2 table data information:

ID Name Sex

1 Tom Man

Summarize:

The Dbms_sql package provides an interface for executing dynamic SQL (including DDL and DML).
Dbms_sql defines an entity called the cursor ID, which is a PL/SQL integer, and the cursor can be manipulated by the cursor ID.
Dbms_sql package and local dynamic SQL have many overlapping functions, but some functions can only be implemented by local dynamic SQL, while some functions can only be implemented by Dbms_sql.

Use of Dbms_sql in Oracle