Dynamic Where Clause in Oracle 11g

[kavehYn]

کسانیکه در برنامه نویسی سعی میکنن عملیات مربوط به بانک اطلاعاتی را در Stored Procedure انجام بدهند بعضی مواقع به یک مشکل کوچک برخورد میکنند و آن زمانی است که بخواهند عملیات جستجو را هم به همین روش بنویسند. تهیه Where Clause به شکل داینامیک کمی مشکل است و البته در Oracle مشکل تر از SQL
کد زیر ، یک مثال کوچک و راحت و آزمایش شده است که چطور این امر را در Oracle انجام بدهیم.

مثال ذیل که در اوراکل 11g نوشته شده است نشان میدهد که چگونه میتوان یک Dynamic Query ایجاد کرد که براساس پارامترهای وارد شده از طرف کاربر خروجی مورد نیاز را تهیه نماید.جهت درک بهتر کد پایین به نکات ذیل توجه نمایید :
●اوراکل به حروف بزرگ و کوچک حساس نیست و این روش کدنویسی فقط برای خوانایی بیشتر میباشد.
●این spc به این صورت در نظر گرفته شده است که یک مقدار عددی و یک مقدار رشته ای به آن ارسال میشود.اگر مقدار عددی بزرگتر از صفر باشد در شرط جستجو لحاظ میشود و اگر مقدار رشته هم بصورت یک رشته خالی ارسال نشده باشد در شرط جستجو لحاظ میگردد.
●خروجی بصورت یک sys_refcursor به روال فراخواننده برگشت داده میشود.
منبع
(http://www.yazdinezhad.com/)


--************************************************** *********************
-- Date : 89/10/26
--Author : Kaveh Yazdi Nezahd
--Parameters : Para_01_IN : Numeric parameter(s) that are passed in stored Proceure. 0 means all values have to be returnd
-- : Para_02_IN : String parameter(s) that are passed in stored Proceure. '' means all values have to be returnd
--************************************************** *****************************************
create or replace PROCEDURE SP_MyProc
( Para_01_IN IN NUMBER, Para_02_IN In Nvarchar2 ,RESULT_OUT OUT sys_refcursor )
IS
Begin
declare
SelectClause clob; --this will save the dynamic sql statement of search
whereClause clob;
begin
SelectClause:='';
whereClause:='';
SelectClause:=' select * from MyTable';
whereClause := ' where {FixedConditionsHere} '
Generating dynamic conditions--
if Para_01_IN != 0 then
whereClause:=whereClause || ' and Para_01= '|| to_char(Para_01_IN);
end if;
if LENGTH(Para_02 ) > 0 then
whereClause:=whereClause||' and Para_02 ='||Para_02_IN ;
end if;
-- This will open the cursor dynamically based on generated SQL commands
open RESULT_OUT for SelectClause||whereClause ;--using whereClause;
commit;
end;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,'Error code ' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1 , 200));
End;