Executing stored procedure based on table column value-CodePudding

I am trying to return result from table based on value returned from another table column.

I am getting error when no data is found in user_config table. I want else part to execute instead.

CREATE OR REPLACE PROCEDURE valuefinders (
    in_soeid   IN VARCHAR2,
    out_cursor OUT SYS_REFCURSOR
) AS
    group1 VARCHAR2(128 BYTE) := 'n';
    
BEGIN
    SELECT
        admin
    INTO group1
    FROM
        user_config
    WHERE
        soeid = in_soeid;

    IF ( group1 = 'y' ) THEN
        OPEN out_cursor FOR SELECT
                                *
                            FROM
                                user_profile;

    ELSE
        OPEN out_cursor FOR SELECT
                                *
                            FROM
                                user_request;

    END IF;

END valuefinders;```

CodePudding user response：

You can also nest exceptions in your code itself and take advantage of them.

This example shows that when the query on user_config raises a NO_DATA_FOUND, the variable group1 is set to 'n' and the else block will be executed:

CREATE OR REPLACE PROCEDURE valuefinders (
    in_soeid   IN VARCHAR2,
    out_cursor OUT SYS_REFCURSOR
) AS
    group1 VARCHAR2(128 BYTE) := 'n';
    
BEGIN
    BEGIN
      SELECT
        admin
      INTO group1
        FROM
          user_config
      WHERE
          soeid = in_soeid;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      group1 := 'n';
    END;

    IF ( group1 = 'y' ) THEN
        OPEN out_cursor FOR SELECT
                                *
                            FROM
                                user_profile;
    ELSE
        OPEN out_cursor FOR SELECT
                                *
                            FROM
                                user_request;
    END IF;
END valuefinders;

CodePudding user response：

Handle the exception, somehow. The simplest option is to do nothing:

CREATE OR REPLACE PROCEDURE valuefinders (in_soeid    IN     VARCHAR2,
                                          out_cursor     OUT SYS_REFCURSOR)
AS
   group1  VARCHAR2 (128 BYTE) := 'n';
BEGIN
   SELECT admin
     INTO group1
     FROM user_config
    WHERE soeid = in_soeid;

   IF (group1 = 'y')
   THEN
      OPEN out_cursor FOR SELECT * FROM user_profile;
   ELSE
      OPEN out_cursor FOR SELECT * FROM user_request;
   END IF;

EXCEPTION                     --> here
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END valuefinders;

If you want to do something else, then just do it:

EXCEPTION                                                             
   WHEN NO_DATA_FOUND
   THEN
      OPEN out_cursor FOR SELECT * FROM user_request;