Is the code I wrote correct or are there more optimized approaches to solve the problem?-CodePudding

I have a srting p_balances like:

balances|100095|5800870-989095-3|Mb|10240.0|10240|0.0|3|2021-11-22T10:12:31.000 06:00^balances|989011|5800870-989011-3|Min|16666666666666666|0|-16666666666666666|3|2021-11-22T10:12:31.000 06:00

I want a return in my function, if 1 is true, 0 for false. My table service_id_map contains column npararam1 (where there is a data like 989011, 100095, 100096 etc.)

The function I wrote works, but are there more optimized ways to get the result?

CREATE OR REPLACE FUNCTION find_option_func(p_balances IN VARCHAR2)
  RETURN NUMBER IS
  v_result NUMBER;
  ex EXCEPTION;
BEGIN
  FOR i IN (SELECT t.npararam1 FROM service_id_map t) LOOP
    BEGIN
      IF instr(p_balances, '|' || i.npararam1 || '|') > 0 THEN
        v_result := 1;
        EXIT;
      ELSE
        RAISE ex;
      END IF;
    EXCEPTION
      WHEN ex THEN
        v_result := 0;
    END;
  END LOOP;
  RETURN v_result;
END find_option_func;

CodePudding user response：

You could do this:

CREATE OR REPLACE FUNCTION find_option_func(p_balances IN VARCHAR2)
  RETURN NUMBER IS
  v_result NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_result
    FROM service_id_map t
   WHERE instr(p_balances, '|' || t.npararam1 || '|') > 0
     AND ROWNUM = 1;

  RETURN v_result;
END find_option_func;