不知是否要用过程返回记录集呢?还有convert(char,@fiscal)是什意思? CREATE PROCEDURE sp_GET_AU_SAMPLE ( ACC_CODE varchar2, FISCAL number ) AS sqlstr varchar2(1000); choicestr varchar2(1000); begin sqlstr:='select A.VOU_NO,A.VOU_SEQ,A.DESCPT,A.DR_CR,A.STAD_AMT,A.ACC_CODE,A.EX_RATE from GL_VOU_DETAIL A,GL_VOU_HEAD B'; choicestr:= ' where A.VOU_NO = B.VOU_NO ';if acc_code is not null then choicestr:= choicestr || ' and a.acc_code='||acc_code; end if; if FISCAL is not null and fiscal<>0 choicestr := choicestr || ' and b.fiscal='||convert(char,@fiscal);sqlstr:=sqlstr ||choicestr; execute immediate sqlstr into 变量;--返回多行记录与没有返回记录都会出错; end; /
sorry,最后一个条件语句是这样 if FISCAL is not null and fiscal<>0 choicestr := choicestr || ' and b.fiscal='||convert(char,fiscal); end if;connvert函数作用?
to: beckhambobo(beckham) choicestr:= choicestr || ' and a.acc_code='||acc_code; 这句似乎应加上'' choicestr:= choicestr || ' and a.acc_code='||''''||acc_code||'''';connvert的作用相当于Oracle中的转换函数,convert(char,...),就相当于 oracle中的ro_char,所以 if FISCAL is not null and fiscal<>0 choicestr := choicestr || ' and b.fiscal='||convert(char,fiscal); end if; 应改为 if FISCAL is not null and fiscal<>0 then choicestr := choicestr || ' and b.fiscal='||to_char(fiscal); end if;PS:少了个 then , ^_^
根本在程程内部不用多加单引号''''||acc_code||'''',参数本来就是一个字符型,实参已包含了单引号. 调用时如下: begin sp_GET_AU_SAMPLE ('1',1); end; / 实参已有了引号,在过程中加单引号是多余.
呵呵,修改如下: choicestr:= choicestr || ' and a.acc_code='''||acc_code||'''';
to beckhambobo(beckham) :execute immediate sqlstr into 变量;--返回多行记录与没有返回记录都会出错;我想执行出结果集,上面这个语句会错吗?
多行一定会出错,那就要用到游标变量了。 若想返回记录集,还要多定义一个游标参数。 CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / CREATE PROCEDURE sp_GET_AU_SAMPLE ( ACC_CODE in varchar2, FISCAL in number, p_rc out pkg_test.myrctype ) as ... sqlstr:=sqlstr ||choicestr; open p_rc for sqlstr; end; /
CREATE PROCEDURE sp_GET_AU_SAMPLE (
ACC_CODE varchar2,
FISCAL number
)
AS
sqlstr varchar2(1000);
choicestr varchar2(1000);
begin
sqlstr:='select A.VOU_NO,A.VOU_SEQ,A.DESCPT,A.DR_CR,A.STAD_AMT,A.ACC_CODE,A.EX_RATE from GL_VOU_DETAIL A,GL_VOU_HEAD B';
choicestr:= ' where A.VOU_NO = B.VOU_NO ';if acc_code is not null then
choicestr:= choicestr || ' and a.acc_code='||acc_code;
end if;
if FISCAL is not null and fiscal<>0
choicestr := choicestr || ' and b.fiscal='||convert(char,@fiscal);sqlstr:=sqlstr ||choicestr;
execute immediate sqlstr into 变量;--返回多行记录与没有返回记录都会出错;
end;
/
if FISCAL is not null and fiscal<>0
choicestr := choicestr || ' and b.fiscal='||convert(char,fiscal);
end if;connvert函数作用?
这句似乎应加上''
choicestr:= choicestr || ' and a.acc_code='||''''||acc_code||'''';connvert的作用相当于Oracle中的转换函数,convert(char,...),就相当于
oracle中的ro_char,所以
if FISCAL is not null and fiscal<>0
choicestr := choicestr || ' and b.fiscal='||convert(char,fiscal);
end if;
应改为
if FISCAL is not null and fiscal<>0 then
choicestr := choicestr || ' and b.fiscal='||to_char(fiscal);
end if;PS:少了个 then , ^_^
调用时如下:
begin
sp_GET_AU_SAMPLE ('1',1);
end;
/
实参已有了引号,在过程中加单引号是多余.
你可以用DBMS_OUTPUT.PUT_LINE将拼的SQL显示出来,或者执行一下,会报错的。
choicestr:= choicestr || ' and a.acc_code='''||acc_code||'''';
若想返回记录集,还要多定义一个游标参数。
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
CREATE PROCEDURE sp_GET_AU_SAMPLE (
ACC_CODE in varchar2,
FISCAL in number,
p_rc out pkg_test.myrctype
)
as
...
sqlstr:=sqlstr ||choicestr;
open p_rc for sqlstr;
end;
/