将sql server存储过程转换为oracle存储过程时出现的问题

My Sql Server Stored Procedure Is

我的Sql Server存储过程是

ALTER PROCEDURE myproc 

@iii NVARCHAR(255)
AS
BEGIN
select * from  dbo.users where (first_name = @iii  OR @iii = '' );
END;

While

而

By Running it

通过运行它

exec myproc  ''

Get All Records

获取所有记录

But in oracle

但在甲骨文

CREATE OR REPLACE PROCEDURE myproc (fname IN STRING, name1 OUT STRING) AS
BEGIN
SELECT "first_name" INTO name1 FROM "users" WHERE ( "first_name" = fname OR fname = '');
END;

But by executing it with '' Parameter Not Found Data.

但是通过''参数未找到数据执行它。

How do I get all the data?

我如何获得所有数据？

1 个解决方案

#1

You have to declare the output variable,

你必须声明输出变量，

declare 
  --declare the output variable
  v_output varchar2(100);
begin
  -- call it
  myproc('King', v_output);
  -- print it
  dbms_output.put_line(v_output);
end;

You can as well declare an exception, cause if the output variable end without value then it will return an error too

你也可以声明一个异常，如果输出变量没有值结束那么它也会返回一个错误

Exception:

例外：

CREATE OR REPLACE PROCEDURE myproc ( fname IN STRING
                                   , name1 OUT STRING) AS

  BEGIN

    select First_name 
      into name1 
      from hr.employees x 
     where x.last_name = fname 
       and rownum = 1;

  EXCEPTION
  when others then
    name1 := null;
  END;

#1