现有一段存储过程片段如下:
line 1 create or replace procedure ENTERPRISE_SUM(p_unitid varchar2,p_bbrq varchar2) is
type rtablelist IS varray(14) of varchar2(6);......line 64 execute immediate 'select count(*)+:v_gjj from '|| rtables(i)||' where bbrq = p_bbrq and dwdh = p_unitid and jb=''国家级''' into v_gjj using v_gjj;--其中j_gjj是前面定义的整型变量,rtables是字符数组,p_unitid 和 p_bbrq是传进来的参数
存储过程编译通过,在测试时出错:
*
ERROR 位于第 1 行:
ORA-00904: "P_UNITID": 无效的标识符
ORA-06512: 在"NYT.ENTERPRISE_SUM", line 64
ORA-06512: 在line 1
试着改了一下
execute immediate 'select count(*)+:v_gjj from '|| rtables(i)||' where bbrq = '||p_bbrq||' and dwdh = '||p_unitid||' and jb=''国家级'''into v_gjj using v_gjj;
仍然出现上述问题。
我对plsql存储过程的语法不大了解,查过一些资料,但是很少有类似的例子,哪位能帮忙找一下错误吗?谢了
line 1 create or replace procedure ENTERPRISE_SUM(p_unitid varchar2,p_bbrq varchar2) is
type rtablelist IS varray(14) of varchar2(6);......line 64 execute immediate 'select count(*)+:v_gjj from '|| rtables(i)||' where bbrq = p_bbrq and dwdh = p_unitid and jb=''国家级''' into v_gjj using v_gjj;--其中j_gjj是前面定义的整型变量,rtables是字符数组,p_unitid 和 p_bbrq是传进来的参数
存储过程编译通过,在测试时出错:
*
ERROR 位于第 1 行:
ORA-00904: "P_UNITID": 无效的标识符
ORA-06512: 在"NYT.ENTERPRISE_SUM", line 64
ORA-06512: 在line 1
试着改了一下
execute immediate 'select count(*)+:v_gjj from '|| rtables(i)||' where bbrq = '||p_bbrq||' and dwdh = '||p_unitid||' and jb=''国家级'''into v_gjj using v_gjj;
仍然出现上述问题。
我对plsql存储过程的语法不大了解,查过一些资料,但是很少有类似的例子,哪位能帮忙找一下错误吗?谢了
varchar2类型的缺少前后的单引号,这样试事:
execute immediate 'select count(*)+:v_gjj from '|| rtables(i)||' where bbrq = '''||p_bbrq||''' and dwdh = '''||p_unitid||''' and jb=''国家级'''into v_gjj using v_gjj;
...bbrq = '''||p_bbrq||''' and dwdh = '''||p_unitid||'''...当然推荐用using
execute immediate 'select count(*)+'||:v_gjj from rtables(i)||' where bbrq = '''||p_bbrq||''' and dwdh = '''||p_unitid||''' and jb=''国家级'''into v_gjj using v_gjj;