Use of dbms_ SQL in oracle, oracledbms_ SQL

Use of dbms_ SQL in oracle, oracledbms_ SQL

1. Run the query using dbms_ SQL

Use dbms_ SQL to execute the select statement 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: work ID

2. Write a stored procedure, use dbms_ SQL to query data information from dict_proteam, and output the result:

Create or replace procedure pro_dict_proteam is/** execute the select statement using dbms_ SQL in the order of open cursor --> parse --> define column --> execute --> fetch rows --> close cursor; **/-- Define the variable v_cursor number; -- cursor ID v_ SQL varchar2 (500); -- used to store the SQL statement v_proteam_id number; -- team ID v_proteam_name varchar2 (50 ); -- team name v_count number; -- there is no actual meaning here, just store the function return value -- v_jctype varchar (20): = ', SS3B,'; v_workflag number: = 1; -- the query condition field begin --: v_workflag is the placeholder -- the first column in the select statement is proteamid, and the second column is proteamname v_ SQL: = 'select proteamid, proteamname from dict_proteam where workflag =: v_workflag '; v_cursor: = cursor; -- open the cursor dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .native); -- parse the dynamic SQL statement -- bind the input parameter and pass the v_workflag value to: v_workflag cursor (v_cursor ,': v_workflag ', v_workflag); -- defines the column. v_proteam_id corresponds to the first column dbms_ SQL .define_column (v_cursor, 1, v_proteam_id) in the select statement. -- defines the column and v_proteam_name corresponds to the second column in the select statement, length: 50 dbms_ SQL .define_column (v_cursor, 2, v_proteam_name, 50); -- execute dynamic SQL statement v_count: mongodbms_ SQL .exe cute (v_cursor); dbms_output.put_line ('v _ count = '| v_count ); loop -- fetch_rows moves the cursor in the result set. If the cursor does not reach the end, return 1 -- retrieves data from the cursor to the buffer, the buffer value can only be read by the function -- column_value (). exit when dbms_ SQL .fetch_rows (v_cursor) <= 0; -- read the value of the buffer column into the corresponding variable -- write the query result of the current row to the column defined above -- the value of the first column is read into dbms_ SQL .column_value (v_cursor, 1, v_proteam_id); -- the value of the second column is read into dbms_ SQL .column_value (v_cursor, 2, v_proteam_name); -- print the variable value dbms_output.put_line (v_proteam_id | ''| v_proteam_name ); end loop; dbms_ SQL .close_cursor (v_cursor); -- close the cursor end;

3. Execute the Stored Procedure

begin  -- Call the procedure  pro_dict_proteam;end;

4. Test output results

V_count = 0
1 Electric Appliance rack
2 Motor upstream
3 car mounts
4. Pantograph
5. Brake Group
Line 6 security device group
8 instrument group
9 charging group
10 inspection teams
2. Use dbms_ SQL to execute DML statements

The order of insert and update is: open cursor --> parse --> bind variable --> execute --> close cursor;

Delete: open cursor --> parse --> execute --> close cursor;

1. Create a test table structure:

create table TB_TEST2(  ID   NUMBER not null,  NAME VARCHAR2(100),  SEX  CHAR(5))

2. Create a stored procedure and use dbms_ SQL to insert data to tb_test2.

Create or replace procedure pro_tb_test2/** execute DML statement insert and update with dbms_ SQL in the order of open cursor --> parse --> bind variable --> execute --> close cursor; delete in the order of 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: = 'mal'; v_ SQL: = 'insert into tb_test2 (id, name, sex) values (: v _ Id,: v_name,: v_sex) '; v_cursor: = cursor; dbms_ SQL .parse (v_cursor, v_ SQL, dbms_ SQL .native); cursor (v_cursor,': v_id', v_id); cursor (v_cursor, ': v_name', v_name); dbms_ SQL .bind_variable (v_cursor, ': v_sex', v_sex); v_count: mongodbms_ SQL .exe cute (v_cursor); values ('data inserted successfully! '| V_count); commit; Exception when others then dbms_output.put_line ('exception message! '); End;

3. Test the Stored Procedure

begin  -- Call the procedure  pro_tb_test2;end;

4. result output: data is inserted successfully! 1

5. query data in table tb_test2:

Id name sex

1 tom male

Summary:

The DBMS_ SQL package provides an interface for executing dynamic SQL statements (including DDL and DML ).
DBMS_ SQL defines an object called the cursor ID. The cursor ID is a PL/SQL integer. You can operate the cursor through the cursor ID.
The DBMS_ SQL package and local dynamic SQL have many overlapping functions, but some functions can only be implemented through local dynamic SQL, and some functions can only be implemented through DBMS_ SQL.