使用Oracle的Instr()与decode()函数进行多条件组合查询
系统中遇到了要处理多条件组合查询的情况，使用instr()和decode()函数的实现方法。下面先说明一下instr()的功能和语法：(函数的语法是从处得到的，相当清晰明了：)In Oracle/PLSQL, the [b]instr[/b] function returns the location of a substring in a string.
The syntax for the [b]instr[/b] function is:
instr ([i]string1[/i], [i]string2[/i], [[i]start_position[/i]], [[i]nth_appearance[/i]])
[i][/i]
[i]string1[/i] is the string to search. [i]string2[/i] is the substring to search for in string1[/i].
[i]start_position[/i] is the position in string1[/i] where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the [i]start_position[/i] is negative, the function counts back [i]start_position[/i] number of characters from the end of [i]string1[/i] and then searches towards the beginning of [i]string1[/i].
[i]nth_appearance[/i] is the nth appearance of [i]string2[/i]. This is optional. If omiited, it defaults to 1.
再说明一下decode()的功能和语法：
In Oracle/PLSQL, the [b]decode[/b] function has the functionality of an IF-THEN-ELSE statement.
The syntax for the [b]decode[/b] function is:
decode ( [i]expression[/i] , search , result [, search , result]... [, default] )
[i]expression[/i] is the value to compare.
[i]search[/i] is the value that is compared against [i]expression[/i].
[i]result[/i] is the value returned, if [i]expression[/i] is equal to [i]search[/i].
[i]default[/i] is optional. If no matches are found, the decode will return [i]default[/i]. If [i]default[/i] is omitted, then the decode statement will return null (if no matches are found).
综合使用得到的SQL语句如下：
select e.到达日期,
e.角色名,
d.单据标题,
d.单据编号,
e.节点编号,
e.处理动作,
e.处理日期,
b.流程实例编号
from gzl_流程类型a,
gzl_流程实例b,
gzl_流程定义c,
dj_单据 d,
gzl_流程流转状态 e
where a.流程类型编号 = c.流程类型编号 and e.处理标记 = '是'
and e.用户id = 'tetdmis' and b.流程定义编号 = c.流程定义编号
and b.活动编号 = d.单据编号 and c.流程定义编号 = b.流程定义编号
and e.流程实例编号 = b.流程实例编号
and instr(decode(:流程类型条件, '-1', a.流程类型编号, :流程类型条件), a.流程类型编号) > 0
and (to_char(e.处理日期, 'yyyy-mm-dd') between :开始日期 and :结束日期)
and instr(decode(:节点名称条件, '-1', e.处理动作, :节点名称条件),
e.处理动作) > 0 instr()函数
instr (string1, string2, [start_position], [nth_appearance])
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
string1 被搜索的字符串
string2 希望搜索的字符串
start_position 搜索的开始位置,默认为1
nth_appearance 出现的位置,默认为1 decode ( expression , search , result [, search , result]... [, default] )
decode ( 条件 ,值1 ,翻译值1 [, search , result]... [, 缺省值] )
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
expression
页:
[1]