Dynamic Cursor in Plsql (1)----from cyber

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:

12345678910

CREATE OR REPLACE PROCEDURE CreateTable2 (tablename VARCHAR2)ISSQL_string VARCHAR2(1000);--存放SQL语句V_cur integer;--定义整形变量，用于存放游标BEGINSQL_string := ‘CREATE TABLE ‘ || tablename || ‘(name VARCHAR(20))‘;V_cur := dbms_sql.open_cursor;--打开游标dbms_sql.parse(V_cur,SQL_string,DBMS_SQL.NATIVE);--解析并执行SQL语句dbms_sql.close_cursor(V_cur);--关闭游标END;

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

1234567891011121314151617181920212223242526272829303132333435363738394041

DECLARE v_cursor NUMBER;--游标ID sqlstring VARCHAR2(200);--用于存放SQL语句 v_phone_name  VARCHAR2(20);--手机名字 v_producer  VARCHAR2(20);--手机生产商 v_price  NUMBER := 500;--手机价钱 v_count  INT;--在这里无意义，只是存放函数返回值BEGIN --:p是占位符 --SELECT 语句中的第1列是phone_name,第2列是producer ,第3列是price sqlstring :=‘SELECT phone_name,producer,price FROM phone_infor WHERE price > :p‘; v_cursor := dbms_sql.open_cursor;--打开游标； dbms_sql.parse(v_cursor ,sqlstring ,dbms_sql.native);--解析动态SQL语句；   --绑定输入参数，v_price的值传给 :p dbms_sql.bind_variable(v_cursor ,‘:p‘,v_price);  --定义列，v_phone_name对应SELECT 语句中的第1列 dbms_sql.define_column(v_cursor,1,v_phone_name,20); --定义列，v_producer对应SELECT语句中的第2列 dbms_sql.define_column(v_cursor,2,v_producer,20); --定义列，v_price对应SELECT语句中的第3列 dbms_sql.define_column(v_cursor,3,v_price);   v_count := dbms_sql.EXECUTE(v_cursor); --执行动态SQL语句。   LOOP  --从游标中把数据检索到缓存区（BUFFER）中，缓冲区 的值只能被函数COULUMN_VALUE()所读取 　　EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0;  --函数column_value()把缓冲区的列的值读入相应变量中。  --第1列的值被读入v_phone_name中　　 dbms_sql.column_value(v_cursor,1,v_phone_name);  --第2列的值被读入v_producer中 　　dbms_sql.column_value(v_cursor,2,v_producer); --第2列的值被读入v_price中 　　dbms_sql.column_value(v_cursor,3,v_price); --打印变量的值 　　dbms_output.put_line(v_phone_name || ‘ ‘|| v_producer|| ‘ ‘||v_price); END LOOP; dbms_sql.close_cursor(v_cursor);--关闭游标 END;

To execute DML statements with Dbms_sql:
Open cursor---> Parse---> Bind variable---> Execute---> Close cursor;

1234567891011121314151617181920212223242526272829

DECLARE v_cursor NUMBER;--游标ID sqlstring VARCHAR2(200);--用于存放SQL语句 v_phone_name  VARCHAR2(20);--手机名字 v_producer  VARCHAR2(20);--手机生产商 v_price  NUMBER := 500;--手机价钱 v_count  INT;--被DML语句影响的行数 BEGIN  sqlstring :=‘INSERT INTO phone_infor values (:a,:b,:c)‘;-- :a,:b,:c 是占位符  v_phone_name  := ‘S123‘; v_producer  := ‘索尼AA‘; v_price   := 999;  v_cursor := dbms_sql.open_cursor;--打开游标； dbms_sql.parse(v_cursor ,sqlstring ,dbms_sql.native);--解析动态SQL语句；  --绑定输入参数，v_price的值传给 :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); --执行动态SQL语句。   dbms_sql.close_cursor(v_cursor);--关闭游标  dbms_output.put_line(‘ INSERT ‘ || to_char( v_count) ||‘ row ‘);--打印有多少行被插入  COMMIT; END;

Dynamic Cursor in Plsql (1)----from cyber