SE la condizione else sql stored procedure

Sto provando a compilare la stored procedure:

create 
procedure checkFroud2(code IN varchar2, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_recordset FOR

if code='C' then 
select * from emp
//dbms_output.putline('this is if block')
else if code='F' then
dbms_output.putline('this is else block')
else
dbms_output.putline('last else')
end if;
end checkFroud2;

ma questo sta mostrando errori di compilazione. Qualcuno può suggerire dove è il problema?

  • Perché voti verso il basso? hai qualche problema con la domanda o risposta?

 

6 Replies
  1. 3
    CREATE
    PROCEDURE checkFroud2(
              code IN VARCHAR2,
              p_recordset OUT SYS_REFCURSOR)
    AS
    BEGIN
         IF code='C' THEN
         dbms_output.put_line('this is if block');
         OPEN p_recordset FOR        
         SELECT * FROM emp;
         ELSIF code='F' THEN
    
         --you can open p_recordset with dummy as
         /*open p_recordset for select * from dual where 1 = 0; */
              dbms_output.put_line('this is else block');
    
         ELSE
         /*open p_recordset for select * from dual where 1 = 0; */
              dbms_output.put_line('last else');
         END IF;
    END checkFroud2;
    /
    
    var o refcursor;
    BEGIN
    CHECKfroud2
    ('C',:o);
    
    END;
    /
    
    PRINT O;
  2. 3
    The correct code is as follows:
    
    create procedure checkFroud2(code IN varchar2, p_recordset OUT SYS_REFCURSOR)
    AS
    BEGIN
    OPEN p_recordset FOR
    
    if code='C' then 
    select * from emp
    //dbms_output.putline('this is if block');
    elsif code='F' then
    dbms_output.putline('this is else block');
    else
    dbms_output.putline('last else');
    end if;
    end checkFroud2;
    • Ottimo Tentativo,
    • prima esecuzione nel sistema . è lavoro?
  3. 1

    Questo è un esempio in oracle 11g

    CREATE OR REPLACE PROCEDURE PROC_EMP_CHECK 
    (
      EMPNO1 IN NUMBER 
    , EMPNO2 IN NUMBER 
    ) 
    AS 
    EMPONE_NOT_EXISTS EXCEPTION;
    EMPTWO_NOT_EXISTS EXCEPTION;
    BOTHEMP_NOT_EXISTS EXCEPTION;
    EMPCOUNT1 NUMBER;
    EMPCOUNT2 NUMBER;
    BEGIN
    
      SELECT COUNT(1) INTO EMPCOUNT1 FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPNO1;
      SELECT COUNT(1) INTO EMPCOUNT2 FROM EMPLOYEES WHERE EMPLOYEE_ID=EMPNO2;
      BEGIN
      IF( EMPCOUNT1=0 AND EMPCOUNT2=0)THEN 
        RAISE BOTHEMP_NOT_EXISTS;
      ELSIF ( EMPCOUNT1=0) THEN 
        RAISE EMPONE_NOT_EXISTS;
       ELSIF ( EMPCOUNT2=0) THEN 
        RAISE BOTHEMP_NOT_EXISTS;
        dbms_output.put_line('ELSE BLOCK');
       END IF; 
      END;
    
      EXCEPTION
      WHEN EMPONE_NOT_EXISTS THEN 
      dbms_output.put_line('EMP One not exit');
      WHEN EMPTWO_NOT_EXISTS THEN 
      dbms_output.put_line('EMP two not exit');
      WHEN BOTHEMP_NOT_EXISTS THEN 
      dbms_output.put_line('both not exit');
      WHEN OTHERS THEN 
      dbms_output.put_line(SQLERRM);
    END PROC_EMP_CHECK;
  4. 0

    Utilizzare ELSIF invece di ELSE IF. Utilizzando ELSE IF si sta aprendo un nuovo nidificato IF-block, che si sta per chiudere.

  5. 0

    Paio di errori:
    1. Aprire il set di record per ??? Per cosa?????
    2. ELSIF e nessun ALTRO SE
    3. dove si trova ;

    CREATE PROCEDURE CHECKFROUD2 ( CODE        IN   VARCHAR2,
                             P_RECORDSET         OUT SYS_REFCURSOR )
    AS
    BEGIN
        OPEN P_RECORDSET FOR SELECT * FROM DUAL;
    
        IF CODE = 'C'
        THEN
            SELECT * FROM EMP;
        ELSIF CODE = 'F'
        THEN
            DBMS_OUTPUT.PUTLINE ( 'this is else block' );
        ELSE
            DBMS_OUTPUT.PUTLINE ( 'last else' );
        END IF;
    END CHECKFROUD2;

Lascia un commento