今天使用到动态的sql:
用的方法是:
用if else 拼出sql文,然后用
“open cursor for sql”来执行。
-- 大概代码如下
create or replace....
is
var_sql varchar2(4000);
TYPE typCur IS REF CURSOR;
cur typCur;
rec aType.aRecord%type; -- aRecord是一个record
begin
var_sql := '';
if...then
var_sql :=var_sql|| 'select a, b, c'..||..||..;
elsif... then
var_sql :=var_sql||..||..||..;
end if;
if...then
var_sql := var_sql||'..';
end if;
open cur for var_sql
loop fetch cur into rec
exit when cur%notfound;
...
end loop;
close cur;
exception
when others then
close cur;
end;
/*
* 1. 由于拼的东西sql文太长,当我将拼好的var_sql塞给var_sql时,会报06502的错,如果定义时将var_sql定义为varchar2(32000)
* 【oracle db里面varchar2最长为4000bytes,而plsql在处理时,能处理的最长的是32000bytes】
*2. 1的问题完了之后,程序能执行到“fetch cur into rec”,rec在定义的时候定义的type与select检索到的不一致,比如是b,a,c
*/
ORA-06502 PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
用的方法是:
用if else 拼出sql文,然后用
“open cursor for sql”来执行。
-- 大概代码如下
create or replace....
is
var_sql varchar2(4000);
TYPE typCur IS REF CURSOR;
cur typCur;
rec aType.aRecord%type; -- aRecord是一个record
begin
var_sql := '';
if...then
var_sql :=var_sql|| 'select a, b, c'..||..||..;
elsif... then
var_sql :=var_sql||..||..||..;
end if;
if...then
var_sql := var_sql||'..';
end if;
open cur for var_sql
loop fetch cur into rec
exit when cur%notfound;
...
end loop;
close cur;
exception
when others then
close cur;
end;
/*
* 1. 由于拼的东西sql文太长,当我将拼好的var_sql塞给var_sql时,会报06502的错,如果定义时将var_sql定义为varchar2(32000)
* 【oracle db里面varchar2最长为4000bytes,而plsql在处理时,能处理的最长的是32000bytes】
*2. 1的问题完了之后,程序能执行到“fetch cur into rec”,rec在定义的时候定义的type与select检索到的不一致,比如是b,a,c
*/
ORA-06502 PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.