I need to return ID’s of a table from a function based on input.

Create or replace function f(v_input nvarchar) 
Return v_table
Is 
  Select id bulk collect into v_table from table1 where user=v_input ;
  Return v_table
End; 

And this function will be called in sql queries like

Select * from table2 where id in (f(abc)); 

This is the concept.

How can we do this programmatically in oracle? How to return multiple record id’s from a function?

CodePudding user response：

You need:

• a BEGIN keyword between IS and the start of the main block.
• a declaration for a variable to BULK COLLECT INTO between IS and BEGIN.
• the return type to be a type not the name of a variable.
• a semi-colon to terminate the reutrn statement.
• NVARCHAR should be NVARCHAR2.
• USER is a reserved word and you should not use this for a column name; if you do it must be as a quoted identifier.

Like this:

Create or replace function f(v_input nvarchar2) 
Return t_table
Is
  v_table t_table;
BEGIN
  Select id bulk collect into v_table from table1 where "USER"=v_input ;
  Return v_table;
End; 
/

Then, if you have created these:

CREATE TYPE t_table IS TABLE OF INTEGER;

CREATE TABLE table1 (id INTEGER, "USER" NVARCHAR2(20));

INSERT INTO table1 (id, "USER")
SELECT 1, N'user1' FROM DUAL UNION ALL
SELECT 2, N'user1' FROM DUAL UNION ALL
SELECT 2, N'user2' FROM DUAL UNION ALL
SELECT 3, N'user3' FROM DUAL;

CREATE TABLE table2 (id) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

Then:

Select * from table2 where id MEMBER OF f('user1');

or

Select * from table2 where id IN (SELECT COLUMN_VALUE FROM TABLE(f('user1'))); 

Outputs:

ID
1
2

db<>fiddle here

CodePudding user response：

In Oracle 19c and above you may use SQL_MACRO concept for this purpose, which is a parameterized query.

As you may see, it is merged within the main query.

create function f_get_ids(p_user in varchar2)
return varchar2
sql_macro(table)
as
begin
  return q'[select id from table1 where user_name = p_user]';
end;
/

select *
from table2
where id in (select id from f_get_ids('User 1'))

ID | DATA_
-: | :----
 1 | 001  

select *
from dbms_xplan.display_cursor(format => 'TYPICAL')

| PLAN_TABLE_OUTPUT                                                             |
| :---------------------------------------------------------------------------- |
| SQL_ID  9ns9x68zjtvhy, child number 0                                         |
| -------------------------------------                                         |
| select * from table2 where id in (select id from f_get_ids('User 1'))         |
|                                                                               |
| Plan hash value: 2946371633                                                   |
|                                                                               |
| ----------------------------------------------------------------------------- |
| | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     | |
| ----------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT   |        |       |       |     6 (100)|          | |
| |*  1 |  HASH JOIN SEMI    |        |     1 |    17 |     6   (0)| 00:00:01 | |
| |   2 |   TABLE ACCESS FULL| TABLE2 |     4 |    28 |     3   (0)| 00:00:01 | |
| |*  3 |   TABLE ACCESS FULL| TABLE1 |     1 |    10 |     3   (0)| 00:00:01 | |
| ----------------------------------------------------------------------------- |
|                                                                               |
| Predicate Information (identified by operation id):                           |
| ---------------------------------------------------                           |
|                                                                               |
|    1 - access("ID"="ID")                                                      |
|    3 - filter("USER_NAME"='User 1')                                           |
|                                                                               |

db<>fiddle here