使用DBMS_SQL包的实例

ORACLE是利用DBMS_SQL包来实现动态SQL的;

parse语法
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER);

language_flag 参数的含义：
Determines how Oracle handles the SQL statement. The following options are recognized:
V6 (or 0) specifies version 6 behavior.
NATIVE (or 1) specifies normal behavior for the dataｂａｓｅ to
which the program is connected.
V7 (or 2) specifies Oracle7 behavior.

//使用实例

抄自 http://blog.youkuaiyun.com/ggjjzhzz/archive/2005/10/17/507880.aspx

DECLARE
v_cursor NUMBER;
v_stat NUMBER;
v_row NUMBER;
v_id NUMBER;
v_no VARCHAR(100);
v_date DATE;
v_sql VARCHAR(200);
s_id NUMBER;
s_date DATE;
BEGIN
s_id := 3000;
s_date := SYSDATE;
v_sql := 'SELECT id,qan_no,sample_date FROM "tblno" WHERE id > :sid and sample_date < :sdate';
v_cursor := dbms_sql.open_cursor; --打开游标；
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句；
dbms_sql.bind_variable(v_cursor, ':sid', s_id); --绑定输入参数；
dbms_sql.bind_variable(v_cursor, ':sdate', s_date);

dbms_sql.define_column(v_cursor, 1, v_id); --定义列 dbms_sql.define_column(v_cursor, 2, v_no, 100);
dbms_sql.define_column(v_cursor, 3, v_date);
v_stat := dbms_sql.execute(v_cursor); --执行动态SQL语句。 LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。 dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。 dbms_sql.column_value(v_cursor, 2, v_no);
dbms_sql.column_value(v_cursor, 3, v_date);
dbms_output.put_line(v_id || ';' || v_no || ';' || v_date);
END LOOP;
dbms_sql.close_cursor(v_cursor); --关闭游标。END;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9274353/viewspace-915970/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9274353/viewspace-915970/