Use of PL/SQL DBMS_ SQL package (1) (Study Notes), pldbms_ SQL

Use of PL/SQL DBMS_ SQL package (1) (Study Notes), pldbms_ SQL

Dbms_ SQL package
The dbms_ SQL package is another method provided by the system to use dynamic SQL:
Follow these steps to use the DBMS_ SQL package to implement dynamic SQL:
1. Place the SQL statement or statement to be executed in a string variable.
2. Analyze the string using the parse process of the DBMS_ SQL package
3. bind_varable using the DBMS_ SQL package to bind variables
4. Use the execute function and execution statement of the DMBS_ SQL package:

Example 1:

-- Use the DBMS_ SQL package to execute DDL statements -- requirement: Use the DBMS_ SQL package to create the DECLARE TABLE_NAME VARCHAR2 (20) Table based on the user input table name, field name, and field type ); -- table name FIELD1 VARCHAR2 (20); -- field name DATATYPE1 VARCHAR2 (20); -- field type FIELD2 VARCHAR2 (20); -- field name DATATYPE2 VARCHAR2 (20); -- field type V_CURSOR NUMBER; -- defines the cursor V_STRING VARCHAR2 (200); -- defines the string variable V_ROW NUMBER; -- the NUMBER of rows BEGIN TABLE_NAME: = 't2'; FIELD1: = 'id'; DATATYPE1: = 'number '; FIELD2: = 'name'; DATATYPE2: = 'varchar2 (20) '; V_CURSOR: = DBMS_ SQL .OPEN_CURSOR; -- open the cursor V_STRING for processing: = 'create table' | TABLE_NAME | '(' | FIELD1 | ''| DATATYPE1 | ', '| FIELD2 | ''| DATATYPE2 |') '; DBMS_ SQL .PARSE (V_CURSOR, V_STRING, DBMS_ SQL .NATIVE); -- Analysis Statement V_ROW: = DBMS_ SQL .EXECUTE (V_CURSOR ); -- execute the statement DBMS_ SQL .CLOSE_CURSOR (V_CURSOR); -- close the cursor DBMS_OUTPUT.PUT_LINE (V_ROW); exception when others then DBMS_ SQL .CLOSE_CURSOR (V_CURSOR); -- close the cursor END;

Example 2:

-- Use the DBMS_ SQL package to execute the DML statement insert DECLARE ID NUMBER: = & ID; NAME VARCHAR2 (20): = '& name'; v_cursor NUMBER; -- Define the cursor v_string VARCHAR2 (200 ); -- Define the string variable v_row NUMBER; -- the NUMBER of rows variable BEGIN v_cursor: = dbms_ SQL .open_cursor; -- open the cursor v_string: = 'insert into t2 values (: id,: name) '; dbms_ SQL .parse (v_cursor, v_string, dbms_ SQL .native); -- Analysis Statement dbms_ SQL .bind_variable (v_cursor, 'id', id); -- Bind Variable dbms_ SQL .bind_variable (v_cursor, 'name', name); -- Bind Variable v_row: export dbms_ SQL .exe cute (v_cursor); -- execute dynamic SQL commit; dbms_ SQL .close_cursor (v_cursor); -- close cursor exception when others then dbms_ SQL .close_cursor (v_cursor); -- close cursor END;

Example 3:

-Run the DML statement using the DBMS_ SQL package -- requirement: Use the DBMS_ SQL package to change the NAME of id = 1 in Table t2 to MarryDECLARE ID NUMBER: = & ID; NAME VARCHAR2 (20 ): = '& name'; V_CURSOR NUMBER; -- Define the cursor V_STRING VARCHAR2 (200); -- Define the string variable V_ROW NUMBER; -- the NUMBER of rows variable BEGIN V_CURSOR: = DBMS_ SQL .OPEN_CURSOR; -- open the cursor V_STRING: = 'Update t2 set name =: name where id =: id'; DBMS_ SQL .PARSE (V_CURSOR, V_STRING, DBMS_ SQL .NATIVE); -- Analysis Statement DBMS_ SQL .BIND_VARIABLE (V_CURSOR, 'name', name ); -- Bind Variable DBMS_ SQL .BIND_VARIABLE (V_CURSOR, 'id', id); -- Bind Variable V_ROW: = DBMS_ SQL .EXECUTE (V_CURSOR); -- execute dynamic SQL commit; DBMS_ SQL .CLOSE_CURSOR (V_CURSOR ); -- disable the cursor exception when others then DBMS_ SQL .CLOSE_CURSOR (V_CURSOR); -- disable the cursor RAISE; END;

Example 4:

-- Use the DBMS_ SQL package to execute the DML statement deleteDECLARE ID NUMBER: = & ID; -- Define the id V_CURSOR NUMBER; -- Define the cursor V_ROW NUMBER; -- define the NUMBER of rows V_STRING VARCHAR2 (200 ); -- Define the string variable BEGIN V_CURSOR: = DBMS_ SQL .OPEN_CURSOR; -- open the cursor V_STRING: = 'delete from t2 where id =: id'; DBMS_ SQL .PARSE (V_CURSOR, V_STRING, DBMS_ SQL .NATIVE ); -- Analysis Statement DBMS_ SQL .BIND_VARIABLE (V_CURSOR, 'id', id); -- bind field ID V_ROW: = DBMS_ SQL .EXECUTE (V_CURSOR); -- execute dynamic SQL commit; DBMS_ SQL .CLOSE_CURSOR (V_CURSOR ); -- disable the cursor exception when others then DBMS_ SQL .CLOSE_CURSOR (V_CURSOR); -- disable the cursor RAISE; END;

 

Query

-- Example 5:

Run the DML statement selectDECLARE V_ID emp using the DBMS_ SQL package. deptno % TYPE: = & ID; -- defines the variable V_STRING VARCHAR2 (200); -- defines the string variable V_EMPNO NUMBER; V_NAME VARCHAR2 (20); V_CURSOR NUMBER; -- defines the cursor V_ROW NUMBER; -- Define the row BEGINv_cursor: = cursor; -- open the cursor V_STRING: = 'select empno, ename from emp where deptno =: deptno'; DBMS_ SQL .PARSE (V_CURSOR, V_STRING, DBMS_ SQL .NATIVE ); -- Analysis Statement DBMS_ SQL .BIND_VARIABLE (V_CURSOR, 'deptno', V_ID); -- bind the field DBMS _ SQL. DEFINE_COLUMN (V_CURSOR, 1, V_EMPNO); DBMS_ SQL .DEFINE_COLUMN (V_CURSOR, 2, V_NAME, 20); -- if there is a length, specify the length of V_ROW: = DBMS_ SQL .EXECUTE (V_CURSOR ); -- execute loopexit when DBMS_ SQL .FETCH_ROWS (V_CURSOR) <= 0; -- resolve the cursor, DBMS_ SQL .COLUMN_VALUE (V_CURSOR, 1, V_EMPNO); -- write the data of the current row to the corresponding column above. DBMS_ SQL .COLUMN_VALUE (V_CURSOR, 2, V_NAME); DBMS_OUTPUT.PUT_LINE ('No: '| V_EMPNO | 'enmae:' | V_NAME); -- output content END LOOP; cursor (V_CURSOR ); -- close the cursor END;