将SQL Server存储过程转换为Oracle存储过程

Hi I am very new to Oracle, please help me to convert the below SQL Server stored procedure to an Oracle stored procedure.

您好我是Oracle新手,请帮我将以下SQL Server存储过程转换为Oracle存储过程。

Below is shown the SQL Server stored procedure that I am trying to convert to Oracle, but due to little time I am not able to find a solution, please help me.

下面显示了我尝试转换为Oracle的SQL Server存储过程,但由于时间不长,我无法找到解决方案,请帮助我。

Create PROCEDURE dbo.myprocedure
    @name          VarChar(50),
    @columnid  VarChar(50),
    @max   Int OUTPUT

AS
 DECLARE @period int,
         @myStatement  varChar(255)
 SET @period = 99999
 SET @max   = 0

 Create Table #newtable
 (
   valu INT
 )

 SET @myStatement  = 'Insert INTO #newtable(valu) SELECT max( ' + @columnid + ') FROM ' + @name + ' WHERE ' + 

@columnid+ ' <= ' + Convert(varChar(5), @period)
 EXEC ( @myStatement )
 SELECT @max = valu FROM  #newtable

 IF (@max = @period)
    SET @max =-1
 ELSE
    SET @max= @max+ 1   
 DROP Table #newtable

GO

this is how i changed into oracle stored procedure

这就是我如何改成oracle存储过程

Create Table newtable(   valu INT );
CREATE OR REPLACE
PROCEDURE PROCEDURE1
( name IN VARCHAR2
, columnid  IN VARCHAR2
, maxid IN OUT VARCHAR2
)
as
         period number;
         mystatement  varChar(255);   
   BEGIN
  period:= 99999; 
maxid:= 0;

  mystatement:= 'Insert INTO newtable(valu) SELECT max(columnid) FROM name WHERE columnid  <=  Convert(varChar(5), period)';  

 SELECT maxid = valu FROM  newtable;

   EXECUTE immediate mystatement;

 IF (maxid= period)
    return maxid:=-1;
 ELSE

    return maxid:=  maxid + 1;  

 DROP Table newtable;

END PROCEDURE1;

1 个解决方案

#1

Well, creating and dropping tables should be in execute immediate clauses too, procedures doesn't return values and so on. You really should consider reading at least something about PL/SQL and dynamic SQL in Oracle. This is working example of your procedure in Oracle, but I have no clue, why you would do such a simple task in such complex way:

好吧,创建和删除表也应该是执行立即子句,过程不返回值等等。你真的应该考虑在Oracle中至少阅读有关PL / SQL和动态SQL的内容。这是您在Oracle中的过程的示例,但我不知道为什么您会以这样复杂的方式执行这样一个简单的任务:

CREATE OR REPLACE PROCEDURE MyProcedure(p_name VarChar2,p_columnid  VarChar2,p_max OUT NUMBER)
AUTHID CURRENT_USER IS
 v_period NUMBER;
 v_max NUMBER;
BEGIN
 v_period := 99999;
 v_max := 0;

 EXECUTE IMMEDIATE 'Create Table newtable (valu NUMBER)';

 EXECUTE IMMEDIATE 'Insert INTO newtable (valu) ((SELECT max(' || p_columnid || ') FROM ' || p_name || ' WHERE '
         || p_columnid || ' <= ' || TO_CHAR(v_period) || '))';
 EXECUTE IMMEDIATE 'SELECT valu FROM newtable' INTO v_max;

 IF (v_max = v_period) THEN
    p_max := -1;
 ELSE
    p_max := v_max + 1;
 END IF;
 EXECUTE IMMEDIATE 'DROP Table newtable';
END MyProcedure;

---

An example of calling it:

调用它的一个例子:

DECLARE
 v_tmp NUMBER;
 v_table VARCHAR2(32);
 v_column VARCHAR2(32);
BEGIN
 v_table := 'SOME_TABLE';
 v_column := 'SOME_COLUMN';
 MyProcedure(v_table,v_column,v_tmp);
 DBMS_OUTPUT.PUT_LINE(v_tmp);
END;