Select into statement for null or more than one value

Question

I am stuck in plsql , as I have making function in which I have to update a table if only values comes in select into ..

and if not come then not and if multiple comes then have to update and delete for all that values .

In below function if in first select into null value comes then should not goto exception handling should update only CUSTOMER table and only delete from table 3 ,, if one or many values comes then do all update and delete for each value

 create or replace FUNCTION  FUNCTION_NAME (
   from_PARTICIPANT_KEY1   IN NUMBER
)
  RETURN 
IS
   to_participant_key1   NUMBER (11);
BEGIN
   SELECT   to_participant_key
     INTO   to_participant_key1
     FROM   TABLE2
    WHERE   FROM_PARTICIPANT_KEY = from_PARTICIPANT_KEY1;




  UPDATE   CUSTOMERS C
      SET   C.CUSTOMER_STATUS_CD =
               NVL (
                  (SELECT   old_status_cd
                     FROM   TABLE1
                    WHERE   PARTICIPANT_UID = from_PARTICIPANT_KEY1
                            AND participant_cd = 'CUSTOMER'),
                  C.CUSTOMER_STATUS_CD
               )
    WHERE   C.CUSTOMER_UID = from_PARTICIPANT_KEY1;



   UPDATE   subscribers C
      SET   C.STATUS_CD =
               NVL (
                  (SELECT   old_status_cd
                     FROM   TABLE1
                    WHERE   PARTICIPANT_UID = to_participant_key1
                            AND participant_cd = 'SUBSCRIBER'),
                  C.STATUS_CD
               )
    WHERE   C.account_no = to_participant_key1;

   DBMS_OUTPUT.PUT_LINE ('Delete TABLE1 rows');

   DELETE FROM   TABLE3
         WHERE   PARTICIPANT_UID = from_PARTICIPANT_KEY1 AND participant_cd = 'CUSTOMER';

   DELETE FROM   TABLE1
         WHERE   PARTICIPANT_UID = to_PARTICIPANT_KEY1 AND participant_cd = 'SUBSCRIBER';



   COMMIT;



EXCEPTION  -- exception handlers begin
   WHEN NO_DATA_FOUND  THEN  -- handles 'division by zero' error
      dbms_output.put_line('Customer not found ' || from_PARTICIPANT_KEY1);

   WHEN OTHERS THEN  -- handles all other errors
      dbms_output.put_line('Some other kind of error occurred.');


END;

The `return` type declaration is missing its datatype, and the function never returns anything. Perhaps you meant to create a procedure and not a function. — William Robertson, May 07 '18 at 14:02
You could improve error handling by removing the `when others` exception handler. The default behaviour (with no handler) is to print the error stack and fail, which is probably what you want here. — William Robertson, May 07 '18 at 14:03

mkuligowski · Accepted Answer · 2018-05-07T07:40:27.547

0

You can use BULK COLLECT INTO and iterate over collection.

First of all, you have to declare (or use some existing) collection type and create the variable of this type:

  TYPE participant_keys is table of number (11); 
  l_participant_keys participant_keys;

Then, your query will change to:

  SELECT to_participant_key
  BULK COLLECT INTO to_participant_key1
  FROM TABLE2
  WHERE FROM_PARTICIPANT_KEY = from_PARTICIPANT_KEY1;

If the query will not return any record then you can check it with COUNT:

if l_participant_keys.COUNT = 0 then
 -- update only CUSTOMER table and only delete from table 3
else
  FOR I IN l_participant_keys.FIRST .. l_participant_keys.LAST LOOP
    --use l_participant_keys(i) do all update and delete for each value
  END LOOP;
end if;

edited May 07 '18 at 07:40

answered May 07 '18 at 05:36

mkuligowski

1,544
1
17
27

but if select into not return any value then should not goto exception handling and only update only CUSTOMER table and only delete from table 3 – Prakhar Agrawal May 07 '18 at 05:43
Thanks .. one more doubt if SELECT to_participant_key BULK COLLECT INTO to_participant_key1 FROM TABLE2 WHERE FROM_PARTICIPANT_KEY = from_PARTICIPANT_KEY1; will return nothing then it will go to exception handling right ? How I can exclude any statement from exception handling .. – Prakhar Agrawal May 07 '18 at 05:58
If it won't return any record then _l_participant_keys.COUNT_ will be equal to _0_; It won't go to the _EXCEPTION WHEN_ block – mkuligowski May 07 '18 at 06:00

Select into statement for null or more than one value

1 Answers1