دینامیک بودن عبارت IN در پرس و جوها

دینامیک بودن عبارت IN در پرس و جوها

Dynamic IN clause in oracle

یکی از دغدغه هایی که برنامه نویسان با آن  مواجه هستند استفاده از متغییر در عبارت IN در پرس و جوها هست.

نمونه های زیر را ببینید. در عبارت IN می خواهیم بصورت

select * from employees
where job_id in (‘SH_CLERK’);

select * from employees
where job_id in (‘SH_CLERK’,’MK_REP’);

select * from employees
where job_id in (‘SH_CLERK’,’MK_REP’,’ST_CLERK’);

select * from employees
where department_id in (10);

select * from employees
where department_id in (10,20);

select * from employees
where department_id in (10,20);

بجای نمونه های بالا می خواهیم از  متغییر و یا Bind Variable  استفاده کنیم.

select * from employees
where department_id in (:p_dept_id);

برای اینکار ابتدا باید دو تا تابع (Function) و یا یک بسته (package) بشکل زیر تعریف کنیم.

create or replace package dynamic_where is
FUNCTION Dynamic_In ( v_list IN VARCHAR2 ) RETURN sys.dbms_debug_vc2coll;
end;
/

create or replace package body  dynamic_where is
FUNCTION Split (
PC$Chaine IN VARCHAR2,         — input string
PN$Pos IN PLS_INTEGER,         — token number
PC$Sep IN VARCHAR2 DEFAULT ‘,’ — separator character
)
RETURN VARCHAR2
IS
LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;
LI$I      PLS_INTEGER ;
LI$I2     PLS_INTEGER ;
BEGIN
LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;
IF LI$I > 0 THEN
LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;
IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ;   END IF ;
RETURN( SUBSTR( LC$Chaine, LI$I+1, LI$I2 – LI$I-1 ) ) ;
ELSE
RETURN NULL ;
END IF ;
END;

   FUNCTION Dynamic_In ( v_list IN VARCHAR2 )
RETURN sys.dbms_debug_vc2coll
IS
ttab sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll() ;
v_Token  VARCHAR2(100) ;
i         PLS_INTEGER := 1 ;
BEGIN
— Populate the collection —
LOOP
v_Token := Split( v_list, i , ‘,’) ;
EXIT WHEN v_Token IS NULL ;
ttab.extend ;
ttab(ttab.COUNT) := v_Token ;
i := i + 1 ;
END LOOP ;
RETURN ttab ;
END ;

end;
/

بعد از ساختن بسته بالا نحوه استفاده بشکل زیر است :

select sum(salary) from employees
where job_id in ( select * from table (CAST (dynamic_where.Dynamic_In(:p_job_list) AS sys.dbms_debug_vc2coll ) ) );

select sum(salary) from employees
where
department_id in ( select *  from table (CAST (dynamic_where.Dynamic_In(:p_dept_list) AS sys.dbms_debug_vc2coll ) ) );