Plsql NOTE----Dynamic SQL

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.

For a general select operation, the following steps are required if you are using dynamic SQL statements:
Open cursor---> Parse---> Define column---> Excute---> Fetch rows---> Close cursor;
For DML operations (Insert,update), the following steps are required:
Open cursor---> Parse---> Bind variable---> Execute---> Close cursor;
There are only a few steps required for the delete operation:
Open cursor---> Parse---> Execute---> Close cursor;
Execute DDL statements with Dbms_sql:

CREATE OR REPLACE PROCEDURE CreateTable2 (tablename VARCHAR2) issql_string VARCHAR2 (1000);--Store SQL statements V_cur integer;-- Define shaping variables to hold cursor beginsql_string: = ' CREATE TABLE ' | | TableName | | ' (Name VARCHAR (20)) '; V_cur: = dbms_sql.open_cursor;--Open cursor dbms_sql.parse (v_cur,sql_string,dbms_sql. NATIVE);--parse and Execute SQL statement dbms_sql.close_cursor (v_cur);--close cursor end;

Execute the SELECT statement with Dbms_sql:
Open cursor---> Parse---> Define column---> Excute---> Fetch rows---> Close cursor;

DECLARE v_cursor number;--Cursor ID sqlstring VARCHAR2 (200);--For storing SQL statements V_phone_name VARCHAR2 (20);--Mobile phone name V_producer VARCHAR2 (20);--Handset manufacturer V_price Number: = 500;--phone price v_count int;--is meaningless here, just store function return value begin--:p is the 1th column in the placeholder--select statement is Phone_n Ame, the 2nd column is producer, and the 3rd column is price sqlstring: = ' SELECT phone_name,producer,price from phone_infor WHERE price >:p '; V_cursor: = dbms_sql.open_cursor;--open cursor, dbms_sql.parse (v_cursor, SqlString, dbms_sql.native);--parse dynamic SQL statement;--Bind input parameters,  The value of V_price is passed to:p dbms_sql.bind_variable (v_cursor, ':p ', v_price); --Define columns, v_phone_name corresponding to the 1th column in the SELECT statement Dbms_sql.define_column (v_cursor,1,v_phone_name,20); --Define columns, v_producer corresponding to the 2nd column in the SELECT statement Dbms_sql.define_column (v_cursor,2,v_producer,20);   --Define columns, v_price corresponding to the 3rd column in the SELECT statement Dbms_sql.define_column (V_cursor,3,v_price); V_count: = Dbms_sql. EXECUTE (V_cursor);   --Execute dynamic SQL statements.  LOOP-Retrieves the data from the cursor into the buffer, the value of which can only be read by the function coulumn_value () EXIT when Dbms_sql.fetch_rows (v_cursor) <=0;  --The function column_value () reads the value of the column of the buffer into the corresponding variable. --The value of the 1th column is read into V_phone_name DBms_sql.column_value (V_cursor,1,v_phone_name); --The value of the 2nd column is read into V_producer dbms_sql.column_value (v_cursor,2,v_producer); --The value of the 2nd column is read into V_price dbms_sql.column_value (v_cursor,3,v_price); --Print the value of the variable dbms_output.put_line (v_phone_name | | "| | v_producer| | "| | V_price); END LOOP; Dbms_sql.close_cursor (v_cursor);--Close the cursor END;

  executing DML statements with Dbms_sql:
open   cursor---> Parse---> bind   variable---> Execute---> close   cursor;

DECLARE v_cursor number;--Cursor ID sqlstring VARCHAR2 (200);--For storing SQL statements v_phone_name  VARCHAR2 (20);--Mobile phone name V_producer  VARCHAR2 (20);--Mobile phone manufacturer V_price  Number: = 500;--mobile Price v_count  int;--rows affected by DML statements BEGIN  sqlstring: = ' INSERT into phone_infor values (: a,:b,:c) ';-- : A,:b,:c is the placeholder  v_phone_name  : = ' S123 '; v_producer  : = ' sony AA '; V_price   : = 999;  V_cursor: = dbms_sql.open_cursor;--open cursor, dbms_sql.parse (v_cursor, SqlString, dbms_sql.native);--parse dynamic SQL statement;-  - Binding input parameters, the value of V_price to:p dbms_sql.bind_variable (V_cursor, ': A ', v_phone_name); Dbms_sql.bind_variable (V_cursor, ': B ', v_producer); Dbms_sql.bind_variable (V_cursor, ': C ', v_price);   V_count: = Dbms_sql. EXECUTE (V_cursor); --Execute dynamic SQL statements.   dbms_sql.close_cursor (v_cursor);---Close cursor  dbms_output.put_line (' INSERT ' | | | to_char (v_count) | | ' Row ');-- Print how many lines are inserted into  COMMIT; END;  

Plsql NOTE----Dynamic SQL