Oracle 的 varchar2 类型最大长度为 4000, PLSQL 的 varchar2 最大长度为 32767 就是 32K, 这也是本地动态 sql 语句能够处理的最大长度 . 如果动态 sql 语句过长，就没有办法在一个 varchar2 变量中存储整个语句，导致 execute immediate 本地动态 sql 方式无法使用。但现实中，有些时候确实会碰到超长的动态 sql 语句，怎么办呢？解决办法就是，使用 dbms_sql 包，用 dbms_sql.varchar2s 或者 dbms_sql.varchar2a 数组来保存拼接的 sql 语句，然后执行。
在dbms_sql程序包中, 上述两个数组的定义如下.
type varchar2a is table of varchar2(32767) index by binary_integer;
type varchar2s is table of varchar2(256) index by binary_integer;
实例1, 执行一个超过32K长度的DDL语句.
declare
l_sql dbms_sql.varchar2s;
l_cursor number;
l_rows number;
begin
l_cursor := dbms_sql.open_cursor;
l_sql(1) :='create table cols(';
for i in 2..1000 loop
l_sql(i) := 'a'||to_char(i) || ' int,';
end loop;
l_sql(1000) := 'last int)';
dbms_sql.parse(c => l_cursor,
statement => l_sql,
lb => l_sql.first,
ub => l_sql.last,
lfflg => TRUE,
language_flag => dbms_sql.native);
l_rows := dbms_sql.execute(l_cursor);
dbms_sql.close_cursor( l_cursor );
end;
/
实例2
/********************************************************************************************************/
A few days ago, My developer encounted a problem when he develop a procedure. He met a dynamic select sql statement more than 32K.
I try to use dbms_sql package to solve this problem.
However, this brings new problems.
Our procedure used to develop as the following ways.
procedure DRP_RPT_SALES_QTY_ALL_MULTI
(
o_cursor_rst out ref cursor
) is
begin
open o_cursor_rst for
'select * from dual';
end;
Now, I do not know how to achieve the same results when I use dbms_sql package instead execute immediate sql statement.
Plz help me.
Thanks.
/********************************************************************************************************/
procedure DRP_RPT_SALES_QTY_ALL_MULTI
(
o_cursor_rst out pkg_rpt_jax_int_multi.type_ref_cur,
) is
--拼接sql变量
v_sql_tmp3 varchar2(30000);
v_sql_tmp4 varchar2(2000);
v_sql_tmp5 varchar2(2000);
v_sql_tmp6 varchar2(2000);
v_sql_body varchar2(32000);
v_sql_body2 varchar2(32000);
v_sql_body3 varchar2(32000);
v_sql_body4 varchar2(32000);
v_sql_body5 varchar2(32000);
v_sql_body6 varchar2(32000);
v_sql_body7 varchar2(32000);
v_sql_body8 varchar2(32000);
v_sql_body9 varchar2(32000);
v_sql_body10 varchar2(32000);
v_sql_body11 varchar2(32000);
v_sql_body12 varchar2(32000);
v_sql_body13 varchar2(32000);
jax_sql dbms_sql.varchar2a; -- table of varchar2(256)
jax_cursor number;
jax_temptablename varchar2(100);
Begin
select 'jax_'||userenv('sessionid') into jax_temptablename from dual; --创建的表名称
jax_cursor := dbms_sql.open_cursor;
jax_sql(1) := 'create table '||jax_temptablename||' as select cube_style,function_name,spec_name, ';
jax_sql(2) := v_sql_tmp3;
jax_sql(3) := v_sql_tmp5;
jax_sql(4) := v_sql_body;
jax_sql(5) := v_sql_body2;
jax_sql(6) := v_sql_body3;
jax_sql(7) := v_sql_body4;
jax_sql(8) := v_sql_body5;
jax_sql(9) := v_sql_body6;
jax_sql(10) := v_sql_body7;
jax_sql(11) := v_sql_body8;
jax_sql(12) := v_sql_body9;
jax_sql(13) := v_sql_body10;
jax_sql(14) := v_sql_body11;
jax_sql(15) := v_sql_body12;
jax_sql(16) := v_sql_body13;
jax_sql(17) := v_sql_tmp6||')';
dbms_sql.parse(c=>jax_cursor,statement => jax_sql,lb=>jax_sql.first,ub=>jax_sql.last,
lfflg=>true,language_flag => dbms_sql.native);
o_rst_rows := dbms_sql.execute(jax_cursor);
dbms_sql.close_cursor(jax_cursor);
--打开游标
open o_cursor_rst for
'select * from '||jax_temptablename;
-- 删除临时表
execute immediate 'drop table '||jax_temptablename;
end;
关于dbms_sql的使用
PL/SQL中使用动态SQL编程
在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
(一)介绍
DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:
function open_cursor:打开一个动态游标,并返回一个整型;
procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;
procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);
procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);
function execute(c in integer):执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);
function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;
procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;
procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;
以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql
(二)一般过程
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;
(三)具体案例
下面就本人所开发系统中某一程序做分析
该过程为一股票技术曲线计算程序,将数据从即时数据表中取出,并按照计算曲线的公式,对这些数据进行计算,并将结果保存到技术曲线表中.
--**********************************
--procedure name:R_Ma_Main
--入口参数：PID股票代码,PEND时间,pinterval时间间隔,totab目标数据表
--调用函数：R_GetSql1,R_GetSql2
--功能：具体计算单支股票ma技术曲线
--时间：2001-06-20
--**********************************
create or replace procedure R_Ma_Main
(
pid varchar2,
pend varchar2,
pinterval varchar2,
totab varchar2
) is
--定义数组
type Date_type is table of varchar2(12) index by binary_integer;
type Index_type is table of number index by binary_integer;
TempDate Date_Type;--时间数组
TempIndex Index_Type;--股票收盘价数组
TempMa Index_Type;--ma技术曲线数据
cursor1 integer;--游标
cursor2 integer;--游标
rows_processed integer;--执行游标返回
TempInter integer;--参与计算数值个数
TempVal integer;--计算时间类型
TempSql varchar2(500);--动态sql语句
MyTime varchar2(12);--时间
MyIndex number;--数值
MidIndex number;--中间变量
i integer := 999;
j integer;
begin
TempInter := to_number(substr(pinterval,1,4));
TempVal := to_number(substr(pinterval,5,2));
TempSql := R_GetSql1(pid, pend, TempVal);--得到选择数据的sql语句
--得到当天的即时数据,并依次保存到数组中
cursor1 := dbms_sql.open_cursor; --创建游标
dbms_sql.parse(cursor1, TempSql, dbms_sql.native); --解析动态sql语句,取两个字段,时间及价格,其中时间以14位的varchar2表示
dbms_sql.define_column(cursor1, 1, MyTime, 12); --分别定义sql语句中各字段所对应变量
dbms_sql.define_column(cursor1, 2, MyIndex);
rows_processed := dbms_sql.execute(cursor1);
loop
if dbms_sql.fetch_rows(cursor1) > 0 then
begin
dbms_sql.column_value(cursor1, 1, MyTime);
dbms_sql.column_value(cursor1, 2, MyIndex);
TempDate(i) := MyTime;
TempIndex(i) := MyIndex;
i := i - 1;--按倒序的方法填入数组
end;
else
exit;
end if;
end loop;
dbms_sql.close_cursor(cursor1);
--如果取得的数据量不够计算个数,则跳出程序
if i > 999-TempInter then
goto JumpLess;
end if;
--初始化中间变量
MidIndex := 0;
TempIndex(i) := 0;
for j in i..i+TempInter-1 loop
MidIndex := MidIndex + TempIndex(j);
end loop;
--依次对当天数据计算ma值,并保存到ma数组中
for j in i+TempInter..999 loop
MidIndex := MidIndex - TempIndex(j-TempInter) + TempIndex(j);
TempMa(j) := MidIndex/TempInter;
end loop;
if TempVal < 6 then--如果计算的是分钟跟天的ma技术曲线
begin
cursor2 := dbms_sql.open_cursor;
TempSql := 'insert into ' || totab || ' values(:r_no, :i_interval, :i_time, :i_index)';
dbms_sql.parse(cursor2, TempSql, dbms_sql.native);
for j in i+TempInter..999 loop
dbms_sql.bind_variable(cursor2, 'r_no', pid);
dbms_sql.bind_variable(cursor2, 'i_interval', pinterval);
dbms_sql.bind_variable(cursor2, 'i_time', TempDate(j));
dbms_sql.bind_variable(cursor2, 'i_index', TempMa(j));
rows_processed := dbms_sql.execute(cursor2);--插入数据
end loop;
end;
end if;
commit;
dbms_sql.close_cursor(cursor2);
--数据量不足跳出
<<JumpLess>>
null;
--exception处理,无关本话题
end;
/
(四)个人观点
在使用dbms_sql系统包的过程中,其方法简单而又不失灵活,但还是需要注意一些问题:
1,在整个程序的设计过程中,对游标的操作切不可有省略的部分,一旦省略其中某一步骤,则会程序编译过程既告失败,如在程序结尾处未对改游标进行关闭操作,则在再次调用过程时会出现错误.
2,dbms_sql除了可以做一般的select,insert,update,delete等静态的sql做能在过程中所做工作外,还能执行create等DDL操作,不过在执行该类操作时应首先显式赋予执行用户相应的系统权限,比如create table等.该类操作只需open cursor--->prase--->close cursor即能完成.
以上为本人在工作中对dbms_sql的一点点看法,不到之处,请予指正.
对于想更深了解dbms_sql的朋友,请阅读dbmssql.sql文件
在某些场合下，存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。本文通过一个简单的例子来展示如何利用DBMS_SQL包执行动态SQL语句：
| DECLARE |
结果：
| 3095;S051013XW00010;15-10月-05 |
| General |
| ||||||||||||
| Note: DMBS_SQL is the traditional form of dynamic SQL in Oracle. |
| ||||||||||||
| Purpose |
| ||||||||||||
| Source | {ORACLE_HOME}/rdbms/admin/dbmssql.sql | ||||||||||||
| Constants |
| ||||||||||||
| Defined Data Types | General Types | ||||||||||||
| Dependencies | SELECT name | ||||||||||||
| Exceptions |
| ||||||||||||
| BIND_ARRAY |
| ||||||||||||
| Binds a given value to a given collection | dbms_sql.BIND_ARRAY( | ||||||||||||
| DECLARE | |||||||||||||
| BIND_VARIABLE |
| ||||||||||||
| Binds a given value to a given variable | dbms_sql.bind_variable ( | ||||||||||||
| CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS | |||||||||||||
| BIND_VARIABLE_CHAR |
| ||||||||||||
| Binds a given value to a given variable | dbms_sql.bind_variabl_char ( | ||||||||||||
| See bind_variable demo | |||||||||||||
| BIND_VARIABLE_RAW |
| ||||||||||||
| Binds a given value to a given variable | dbms_sql.bind_variable_raw ( | ||||||||||||
| See bind_variable demo | |||||||||||||
| BIND_VARIABLE_ROWID |
| ||||||||||||
| Binds a given value to a given variable | dbms_sql.bind_variable_rowid ( | ||||||||||||
| See bind_variable demo | |||||||||||||
| CLOSE_CURSOR |
| ||||||||||||
| Closes cursor and free memory | dbms_sql.close_cursor(c IN OUT INTEGER); | ||||||||||||
| See is_open demo | |||||||||||||
| COLUMN_VALUE |
| ||||||||||||
| Returns value of the cursor element for a given position in a cursor | dbms_sql.column_value ( | ||||||||||||
| See final demo | |||||||||||||
| COLUMN_VALUE_CHAR |
| ||||||||||||
| Returns value of the cursor element for a given position in a cursor | dbms_sql.column_value_char ( | ||||||||||||
| See column_value in final demo | |||||||||||||
| COLUMN_VALUE_LONG |
| ||||||||||||
| Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG | dbms_sql.column_value_long ( | ||||||||||||
| See column_value in final demo | |||||||||||||
| COLUMN_VALUE_RAW |
| ||||||||||||
| Returns value of the cursor element for a given position in a cursor | dbms_sql.column_value_raw ( | ||||||||||||
| See column_value in final demo | |||||||||||||
| COLUMN_VALUE_ROWID |
| ||||||||||||
| Undoc | dbms_sql.column_value_rowid ( | ||||||||||||
| See column_value in final demo | |||||||||||||
| DEFINE_ARRAY |
| ||||||||||||
| Defines a collection to be selected from the given cursor, used only with SELECT statements | dbms_sql.define_array ( | ||||||||||||
| DECLARE | |||||||||||||
| DEFINE_COLUMN |
| ||||||||||||
| Defines a column to be selected from the given cursor, used only with SELECT statements | dbms_sql.define_column ( | ||||||||||||
| See final demo | |||||||||||||
| DEFINE_COLUMN_CHAR |
| ||||||||||||
| Undoc | dbms_sql.define_column_char ( | ||||||||||||
| See define_column in final demo | |||||||||||||
| DEFINE_COLUMN_LONG |
| ||||||||||||
| Defines a LONG column to be selected from the given cursor, used only with SELECT statements | dbms_sql.define_column_long ( | ||||||||||||
| See define_column in final demo | |||||||||||||
| DEFINE_COLUMN_RAW |
| ||||||||||||
| Undoc | dbms_sql.define_column_raw ( | ||||||||||||
| See define_column in final demo | |||||||||||||
| DEFINE_COLUMN_ROWID |
| ||||||||||||
| Undoc | dbms_sql.define_column_rowid ( | ||||||||||||
| See define_column in final demo | |||||||||||||
| DESCRIBE_COLUMNS |
| ||||||||||||
| Describes the columns for a cursor opened and parsed through DBMS_SQL | dbms_sql.describe_columns ( | ||||||||||||
| DECLARE | |||||||||||||
|
|
| ||||||||||||
| DESCRIBE_COLUMNS2 |
| ||||||||||||
| Describes the specified column, an alternative method | dbms_sql.describe_columns2 ( | ||||||||||||
| Why? Research | |||||||||||||
| EXECUTE |
| ||||||||||||
| Execute dynamic SQL cursor | dbms_sql.execute(c IN INTEGER) RETURN INTEGER; | ||||||||||||
| DECLARE | |||||||||||||
| EXECUTE_AND_FETCH |
| ||||||||||||
| Executes a given cursor and fetch rows | dbms_sql.execute_and_fetch(c IN INTEGER, | ||||||||||||
| Combine demo w/ last_row_count and last_row_id demos | |||||||||||||
| FETCH_ROWS |
| ||||||||||||
| Fetches a row from a given cursor | dbms_sql.fetch_rows(c IN INTEGER) RETURN INTEGER; | ||||||||||||
| See final demo | |||||||||||||
| IS_OPEN |
| ||||||||||||
| Determine whether a cursor is open | dbms_sql.is_open(c IN INTEGER) RETURN BOOLEAN; | ||||||||||||
| set serveroutput on | |||||||||||||
| LAST_ERROR_POSITION |
| ||||||||||||
| Returns byte offset in the SQL statement text where the error occurred | dbms_sql.last_error_position RETURN INTEGER; | ||||||||||||
|
| |||||||||||||
| LAST_ROW_COUNT |
| ||||||||||||
| Returns cumulative count of the number of rows fetched | dbms_sql.last_row_count RETURN INTEGER; | ||||||||||||
|
| |||||||||||||
| LAST_ROW_ID |
| ||||||||||||
| Returns ROWID of last row processed | dbms_sql.last_row_id RETURN ROWID; | ||||||||||||
|
| |||||||||||||
| LAST_SQL_FUNCTION_CODE |
| ||||||||||||
| Returns SQL function code for statement | dbms_sql.last_sql_function_code RETURN INTEGER; | ||||||||||||
|
| |||||||||||||
| OPEN_CURSOR |
| ||||||||||||
| Open dynamic SQL cursor and return cursor ID number of new cursor | dbms_sql.open_cursor RETURN INTEGER; | ||||||||||||
| See is_open demo | |||||||||||||
|
|
| ||||||||||||
| PARSE |
| ||||||||||||
| Parse statement | dbms_sql.parse(<cursor_variable>, <sql_string>, | ||||||||||||
| CREATE SYNONYM test_syn FOR dual; | |||||||||||||
| -- with returning clause | |||||||||||||
| Parse statement | dbms_sql.parse( | ||||||||||||
|
| |||||||||||||
| Parse statement | dbms_sql.parse( | ||||||||||||
|
| |||||||||||||
|
|
| ||||||||||||
| VARIABLE_VALUE |
| ||||||||||||
| Returns value of named variable for given cursor | dbms_sql.variable_value( | ||||||||||||
|
| |||||||||||||
| Returns value of named variable for given cursor | dbms_sql.variable_value( | ||||||||||||
|
| |||||||||||||
|
|
| ||||||||||||
| VARIABLE_VALUE_CHAR |
| ||||||||||||
| Undoc | dbms_sql.variable_value_char( | ||||||||||||
|
| |||||||||||||
|
|
| ||||||||||||
| VARIABLE_VALUE_RAW |
| ||||||||||||
| Undoc | dbms_sql.variable_value_raw( | ||||||||||||
|
| |||||||||||||
|
|
| ||||||||||||
| VARIABLE_VALUE_ROWID |
| ||||||||||||
| Undoc | dbms_sql.variable_value_rowid( | ||||||||||||
|
| |||||||||||||
|
|
| ||||||||||||
| Demos |
| ||||||||||||
|
| SELECT synonym_name | ||||||||||||
| Executing CLOBS Demo Tables | CREATE TABLE workstations ( | ||||||||||||
|
| CREATE OR REPLACE PROCEDURE execute_plsql_block( | ||||||||||||
|
| DECLARE | ||||||||||||
|
| CREATE OR REPLACE PROCEDURE copy( | ||||||||||||
| Bulk Insert Demo | DECLARE |
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
