当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - preparing update statement based on input parameters dynamically

问题描述:

I have to prepare update statement dynamically in stored procedure based on 4 input variables.

suppose take test, test1, test2 and test3 are input parameters.

then I have to prepare query like ,please help me to build this:

update emp

set empid='1'

where test = test

and test1 = test1

and test2 = test2

and test3 = test3

Suppose if the test and test1 values are null and test2 and test3 values are not null then I can prepare update statement like below

update emp

set empid='1'

where test is null

and test1 is null

and test2 = test2

and test3 = test3

网友答案:

If there is a change that one or more parameters that you are passing in a procedure are going to be NULL you could write your UPDATE statement as follows. There is really no need of using dynamic SQL. In this example procedure's parameters are prefixed with p_:

update emp 
   set empid='1' 
 where (test  = p_test  or (test  is null and p_test  is null)) 
   and (test1 = p_test1 or (test1 is null and p_test1 is null))
   and (test2 = p_test2 or (test2 is null and p_test2 is null)) 
   and (test3 = p_test3 or (test3 is null and p_test3 is null))
网友答案:

Create a SQL statement based on your input parameters
like

 'SELECT '||'Colum_nmae from table' || where var1=var2 .....

then use execute immediate to execute this query

网友答案:

You can't give your variables the same name as your columns, that would be confusing to PL/SQL. So let's name the variables v_test, v_test1, ...

You could write:

update emp 
       set empid='1'
where  (test  = v_test  OR v_test  is null)
and    (test1 = v_test1 OR v_test1 is null)
and ... 

Or more simply using NVL:

update emp 
       set empid='1'
where  test  = nvl(v_test, test) 
and    test1 = nvl(v_test1, test1) 
and ...

NVL(A, B) is A unless A is null in which case it is B.

分享给朋友:
您可能感兴趣的文章:
随机阅读: