请教我写最简单的过程！谢谢！

[lynx800602]

帮我把这个SQL SERVER的存储过程改成ORACLE的：

创建：
CREATE PROCEDURE [DBO].[PRO_SLE]
@INPUT1 INT,
@INPUT2 VARCHAR(200)
AS
SELECT * FROM TEST WHERE AA=@INPUT1 AND BB LIKE '%'+@INPUT2+'%'
GO

执行：EXEC PRO_SLE 8,'EE'

[beckhambobo]

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/

CREATE PROCEDURE PRO_SLE(INPUT1 in integer,INPUT2 in VARCHAR2,p_rc out pkg_test.myrctype)
AS
begin
open p_rc for 'SELECT * FROM TEST WHERE AA='||INPUT1||' AND instr(BB,'||INPUT2||')>0';
end;
/

[jiezhi]

返回結果集合在oracle里面比較麻煩，例子：
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/

[lynx800602]

谢谢！
研究中.....

[lynx800602]

SORRY!
本人不懂ORACLE!
在SQL SERVER 中这种存储过程很常见，很简单
请问类似的存储过程在ORACLE 中怎么处理的？
向楼上[jiezhi(浪子) ]那样处理？

给个模版我套吧！或者把我的那个改成ORACLE的，我就会套了！

[tiangou]

用这句话不好，v_sql := 'select a1,a2 from test';生成动态sql语句
不妨用function试试：
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test;
end pkg_test;
/
create or replace package body pkg_test is
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test
is
cur_return cur_test;
begin
open cur_return for
SELECT *
FROM TEST
WHERE AA=INPUT1
AND BB LIKE '%'||INPUT2||'%';
return cur_return;
exception
when others then
if cur_return%isopen
then
close cur_return;
end if;

end getResult;

end pkg_test;

[lynx800602]

谢谢beckham!

[beckhambobo]

declare
v_col1 TEST.col1%type;
v_col1 test.col1%type;
....
v_rc pkg_test.myrctype;
begin
PRO_SLE(1,'1',v_rc);
loop
fetch v_rc into v_col1,v_col2,...;
exit when v_rc%notfound;
dbms_output.put_line(v_col1||v_col2||...);
end loop;
end;
/