Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

oracle - Function or Procedure for an IN clause

I want to write a funcion or procedure that can be used in the IN clause of another procedure. The function or procedure would return ID numbers.

The main procedure would say something like

SELECT *
FROM EMPLOYEES
WHERE OFFICE_ID IN (GET_OFFICE_IDS);  -- GET_OFFICE_IDS requires no parameters

GET_OFFICE_IDS returns a VARCHAR2 with the ID separated by commas. When I run the main procedure, I get a "ORA-01722: invalid number" error which makes sense but I don't know where I need to go from here.

Do I need GET_OFFICE_IDS to create a temp table that the main procedure uses? If so, will there be a performance penalty?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Here is a working example of the nested table solution, using the EMP table:

create type t_ids is table of integer
/

create or replace function get_office_ids return t_ids
is
   l_ids t_ids := t_ids();
   l_idx integer := 0;
begin
   for r in (select empno from emp where deptno=10)
   loop
      l_ids.extend;
      l_idx := l_idx+1;
      l_ids(l_idx) := r.empno;
   end loop;
   return l_ids;
end;
/

select ename from emp where empno in (select * from table(get_office_ids));


ENAME
----------
CLARK
KING
TEST
MILLER
BINNSY
FARMER

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...