创建:
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'
...全文
337打赏收藏
请教我写最简单的过程!谢谢!
帮我把这个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'
用这句话不好,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;
返回結果集合在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;
/
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;
/