/*
Írjunk meg egy függvényt, amelyik visszaadja a paraméterként szereplő '+'-szal 
elválasztott számok és kifejezések összegét. Ha valamelyik kifejezés nem szám,
akkor azt az összeadásnál hagyja figyelmen kívül, vagyis 0-nak tekintse.
*/
CREATE OR REPLACE FUNCTION is_integer(p_str VARCHAR2) RETURN BOOLEAN IS
  v_regex VARCHAR2(20);
BEGIN
  v_regex := '^[-+]?[0-9]+$';  -- Match optional sign, digits, optional end-of-string
  RETURN REGEXP_INSTR(p_str, v_regex) > 0;
END;
/

CREATE OR REPLACE FUNCTION summ(p_char VARCHAR2) RETURN NUMBER IS
  input VARCHAR2(100) := p_char;
  separator VARCHAR2(1) := '+'; -- Separator character
  type string_array is table of VARCHAR2(100) index by pls_integer;
  output_array string_array;
  idx pls_integer := 1;
  num INTEGER := 0;
BEGIN
  -- Loop to extract each substring and store it in the array
  WHILE (REGEXP_SUBSTR(input, '[^'||separator||']+', 1, idx) IS NOT NULL) LOOP
    output_array(idx) := REGEXP_SUBSTR(input, '[^'||separator||']+', 1, idx);
    idx := idx + 1;
  END LOOP;
  
  -- Print the elements of the array
  FOR i IN 1..output_array.count LOOP
    DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || output_array(i));
    IF is_integer(output_array(i)) THEN
      num := num + TO_NUMBER(output_array(i));
    END IF;
  END LOOP;
  
  RETURN num;
END;
/

DECLARE
  asd INTEGER;
BEGIN
  asd := summ('1+50+kaki');
  DBMS_OUTPUT.PUT_LINE('The sum is ' || asd);
END;

PL/SQL Online Compiler

Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.

About PL/SQL

PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.

Syntax help

Following is the syntax structure for the PL/SQL code blocks

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Example

DECLARE 
   message  varchar2(100):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Named procedures

CREATE OR REPLACE FUNCTION 
hello_user
   (user_name IN VARCHAR2) 
    RETURN VARCHAR2
IS
BEGIN
   RETURN 'Hello ' || user_name;
END hello_user;
/

BEGIN
   dbms_output.put_line(hello_user('Peter'));
END;
/

Exception handling

BEGIN
  DBMS_OUTPUT.put_line (1/0);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;