I am trying to write a pl/sql function that return a number. I must use the number in SQL where clause. The function is in the following:

CREATE OR REPLACE FUNCTION func_id(str_id IN STRING,num_group IN NUMBER)
RETURN NUMBER
IS
result NUMBER;
declare
  temp STRING;
BEGIN
  temp := substr(str_id, -least(length(str_id), 2));
  result := TO_NUMBER(temp) % num_group;
RETURN result;
END;

select * from table where func_id("id",2)=1

2 and 1 are just an example. I want to call the function in my Scala Program that variables are replaced in place of 2 and 1.

When I run the code in SQLDEVELOPER I receive this errors:

 Function FUNC_ID compiled

 LINE/COL  ERROR
 --------- -------------------------------------------------------------
 5/1       PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior The symbol "begin" was substituted for "DECLARE" to continue. 
 13/54     PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     . , @ ; for <an identifier>    <a double-quoted delimited-identifier> group having intersect    minus order partition start subpartition union where connect    sample 
 Errors: check compiler log

Would you please guide me how to write a PL/SQL function and call it in another query or any where else?

Any help is really appreciated.

CodePudding user response：

• DECLARE is syntactically invalid in that position;
• STRING is not a valid data type, you want VARCHAR2;
• % is not a valid operator, you want the MOD function; and
• The intermediate variables are not required.

CREATE OR REPLACE FUNCTION func_id(
  str_id    IN VARCHAR2,
  num_group IN NUMBER
) RETURN NUMBER
IS
BEGIN
  RETURN MOD(TO_NUMBER(substr(str_id, -LEAST(2, LENGTH(str_id)))), num_group);
END;
/

Then, if you have the table:

CREATE TABLE table_name ( id ) AS
SELECT '1' FROM DUAL UNION ALL
SELECT '24' FROM DUAL UNION ALL
SELECT '10-05' FROM DUAL;

You can call the function using:

select id,
       func_id(id, 23)
from   table_name

Which outputs:

ID	FUNC_ID(ID,23)
1	1
24	1
10-05	5

db<>fiddle here