将DB2存储过程迁移到Oracle

Question

我有一个Db2存储过程，它有多个游标，我将根据某些条件打开任意一个游标，

CREATE PROCEDURE SAMPLEPROCEDURE (
    IN "IN_PARM1"   CHARACTER(3),
    IN "IN_PARM2"   CHARACTER(3),
    IN "IN_PARM3"   CHARACTER(3),
 )
  SPECIFIC "SAMPLEPROCEDURE"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  NO EXTERNAL ACTION
  MODIFIES SQL DATA
  OLD SAVEPOINT LEVEL
P1: BEGIN
        DECLARE CURSOR1 CURSOR WITH RETURN FOR      
         --My Select query goes here
        DECLARE CURSOR2 CURSOR WITH RETURN FOR      
        --My Select query goes here
        DECLARE CURSOR3 CURSOR WITH RETURN FOR          
        --My Select query goes here  
  IF IN_PARM1 <> '' OR IN_PARM1 IS NOT NULL THEN    
    IF IN_PARM2 <> '444' THEN    
            OPEN CURSOR1;
        ELSE
            OPEN CURSOR2;
    END IF;
  ELSE
        OPEN CURSOR3;
  END IF;
  END P1

如何在Oracle中创建类似的过程?

Answer 1

在Oracle的PL/SQL中看起来没有太大的不同:

CREATE PROCEDURE SAMPLEPROCEDURE (
   IN_PARM1 VARCHAR2,
   IN_PARM2 VARCHAR2,
   IN_PARM3 VARCHAR2) 
AS
  CURSOR CURSOR1 IS SELECT ...;
  CURSOR CURSOR2 IS SELECT ...;
  CURSOR CURSOR3 IS SELECT ...;
BEGIN
  IF IN_PARM1 IS NOT NULL THEN
    IF IN_PARM2 <> '444' THEN
      OPEN CURSOR1;
    ELSE 
      OPEN CURSOR2;
    END IF;
  ELSE 
    OPEN CURSOR3;
  END IF;
END SAMPLEPROCEDURE;
/