求助 DB2迁移到Oracle 存储过程怎么更改？

[yangyuanjie1123]

单位最近把DB2数据库迁移到了Oracle上，但是存储过程报错，导致项目跑不起来。对于数据库了解不够，求助大神改怎么改动以前的DB2的存储过程?
这是原DB2的存储过程代码：

CREATE OR REPLACE PROCEDURE "JCCS"."JCCS_FIND_FUNCTION_LIST"
  (IN USERID VARCHAR(200))
  SPECIFIC JCCS.SQL081126155452500
  DYNAMIC RESULT SETS 1
  MODIFIES SQL DATA
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  LANGUAGE SQL
  BEGIN
	DECLARE SQLCODE INTEGER DEFAULT 0;--
	DECLARE myCursor CURSOR WITH RETURN TO CALLER
		FOR 
select * from(select r.application_role_id,
                     r.role_name,
                     r.application_parent_role_id,
                     r.node_code,
                     f.application_function_id,
                     f.function_name,
                     f.function_code,
                     c.category_name,
                     c.function_category_id
                from JCCS_ROLE_USER_TYPE       ut,
                     JCCS_APPLICATION_ROLE     r,
                     JCCS_ROLER_FUNCTION       rf,
                     JCCS_APPLICATION_FUNCTION f,
                     JCCS_FUNCTION_CATEGORY    c
               where (ut.del_flag = '0' and ut.role_assign_type = '3' and
                     ut.role_assign_type_value = userId and
                     ut.role_id = r.application_role_id and
                     r.del_flag = '0' and
                     r.application_role_id = rf.role_id and
                     rf.del_flag = '0' and
                     rf.function_id = f.application_function_id and
                     f.del_flag = '0' and
                     f.function_category_id = c.function_category_id)
              union (select r.application_role_id,
                           r.role_name,
                           r.application_parent_role_id,
                           r.node_code,
                           f.application_function_id,
                           f.function_name,
                           f.function_code,
                           c.category_name,
                           c.function_category_id

                      from JCCS_SELFR_USER_TYPE      su,
                           JCCS_SELF_ASSIGN_ROLE     sr,
                           JCCS_SELFR_FUNCTION       srf,
                           jccs_selff_function       sf,
                           JCCS_APPLICATION_ROLE     r,
                           JCCS_APPLICATION_FUNCTION f,
                           JCCS_FUNCTION_CATEGORY    c
                     where su.del_flag = '0'
                       and su.role_assign_type = '3'
                       and su.role_assign_type_value = userId
                       and su.self_role_id = srf.self_role_id
                       and srf.self_function_id = sf.self_function_id
                       and sf.application_role_id = r.application_role_id
                       and srf.function_id = f.application_function_id
                       and f.function_category_id = c.function_category_id))AS temp;--
OPEN myCursor;--
END

求问如何更改？谢谢~

[yangyuanjie1123]

引用 3 楼 wmxcn2000 的回复:

CREATE OR REPLACE PROCEDURE "JCCS"."JCCS_FIND_FUNCTION_LIST"(temp out sys_refcursor)
AS
  USERID VARCHAR(200);
  BEGIN
      USERID := '100';
      OPEN temp FOR select * from t where col_name = USERID; 
END

非常感谢耐心解答，根据你的代码 我也查询了其他资料之后修改为:

CREATE OR REPLACE PROCEDURE "JCCS"."JCCS_FIND_FUNCTION_LIST"(temp out sys_refcursor,userid IN varchar2)
AS
BEGIN
        OPEN temp FOR
                select * from (select ... from ... where col_name = userid and ...);
END;

修改后能够成功运行，也能查询出数据 但是在跑JAVA项目时，运行到此调用还是报错： PLS-00306: 调用 'JCCS_FIND_FUNCTION_LIST' 时参数个数或类型错误 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored 请问这是什么原因造成的呢？

[卖水果的net]

引用 4 楼 u013699131 的回复:

修改后能够成功运行，也能查询出数据 但是在跑JAVA项目时，运行到此调用还是报错： PLS-00306: 调用 'JCCS_FIND_FUNCTION_LIST' 时参数个数或类型错误 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored 请问这是什么原因造成的呢？

我也不懂 java ,你到 java 区问问吧，他们更专业；

[卖水果的net]

create or replace procedure sp(ret out sys_refcursor)
as
    v_sql varchar(200);
begin
    v_sql := 'select * from user_objects';
    open ret for v_sql;
end;

[yangyuanjie1123]

引用 1 楼 wmxcn2000 的回复:

create or replace procedure sp(ret out sys_refcursor)
as
    v_sql varchar(200);
begin
    v_sql := 'select * from user_objects';
    open ret for v_sql;
end;

多谢大神解答，因为本人技术实在较差，按照例句修改为：

CREATE OR REPLACE PROCEDURE "JCCS"."JCCS_FIND_FUNCTION_LIST"(temp out myCursor)
AS
  USERID VARCHAR(200);
  BEGIN
select * from (...)AS temp;

	OPEN temp FOR USERID;
END

控制台出现报错： 11:01:21 [CREATE - 0 row(s), 0.000 secs] {53:77} PL/SQL: ORA-00933: SQL 命令未正确结束 11:01:21 [CREATE - 0 row(s), 0.000 secs] {5:1} PL/SQL: SQL Statement ignored 11:01:21 [CREATE - 0 row(s), 0.000 secs] {57:0} PLS-00103: 出现符号 "end-of-file"在需要下列之一时： ; <an identifier> <a double-quoted delimited-identifier> current delete exists prior <a single-quoted SQL string> 符号 ";" 被替换为 "end-of-file" 后继续。 ... 3 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 3 errors] 请问哪里还出现了问题

[卖水果的net]

CREATE OR REPLACE PROCEDURE "JCCS"."JCCS_FIND_FUNCTION_LIST"(temp out sys_refcursor)
AS
  USERID VARCHAR(200);
  BEGIN
      USERID := '100';
      OPEN temp FOR select * from t where col_name = USERID; 
END