CREATE OR REPLACE PROCEDURE PROC_QUERY_ELECTRICJOURNAL(trancodein in varchar2,
devdatein in varchar2,
tranacctin in varchar2,
clinicfeein in varchar2,
p_cur out sys_refcursor) as
str varchar2(2000);
normal varchar2(2000); trancode_code1 varchar2(10);
trancode_name1 varchar2(200);begin trancode_code1 := '0200000000';
trancode_name1 := '消费'; str := 'select case trancode when ' || trancode_code1 || ' then ' ||
trancode_name1 || 'end as trancode from electricjournal where trancode = '||trancodein; open p_cur for str;
end PROC_QUERY_ELECTRICJOURNAL;
调用的时候在 open p_cur for str;这行提示错误缺失关键字
devdatein in varchar2,
tranacctin in varchar2,
clinicfeein in varchar2,
p_cur out sys_refcursor) as
str varchar2(2000);
normal varchar2(2000); trancode_code1 varchar2(10);
trancode_name1 varchar2(200);begin trancode_code1 := '0200000000';
trancode_name1 := '消费'; str := 'select case trancode when ' || trancode_code1 || ' then ' ||
trancode_name1 || 'end as trancode from electricjournal where trancode = '||trancodein; open p_cur for str;
end PROC_QUERY_ELECTRICJOURNAL;
调用的时候在 open p_cur for str;这行提示错误缺失关键字
试试把动态SQL的拼接改成如下这样:
str := 'select case trancode when ''' || trancode_code1 || ''' then ''' ||
trancode_name1 || '''end as trancode from electricjournal where trancode = '''||trancodein||'''';
str VARCHAR2(2000);
/* normal VARCHAR2(2000);
*/ trancodein VARCHAR2(10);
trancode_code1 VARCHAR2(10);
trancode_name1 VARCHAR2(200);BEGIN trancode_code1 := '0200000000';
trancode_name1 := '消费';
trancodein:='测试用'; str := 'select case trancode when ' || trancode_code1 || ' then ' || trancode_name1 ||
'end as trancode from electricjournal where trancode = ' || trancodein; dbms_output.put_line(str);
/* OPEN p_cur FOR str;*/
END;
楼主你的动态sql打印出来的~,你自己调整下吧
trancode_name1 || ' end as trancode from electricjournal where trancode = '||trancodein;此END前缺少空格
str := 'select case trancode when ''' || trancode_code1 || ''' then ''' ||
trancode_name1 ||
''' end as trancode from electricjournal where trancode = ''' ||
trancodein || '''';