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

Source: Internet
Author: User

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;

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.