Dynamic where 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 ) ) );