有这样一道PL/SQL编程的题目：

设表tab_ch4的定义如下：

字段名    数据类型            属性     注释
numlist  Varchar2(300)      非空    用逗号相隔的数字组成的字符串，如23,56,90,543,754
num      Varchar2(5)          非空    数字形式的字符串，如90
position Number(4)                      Num在numlist中是第几个数字，如3

其中position字段的值为空。

现要求找到每行记录中num字段的数字在numlist字段中第一次出现时是第几个数字，写到position字段中。如果找不到，则写-1。例如， 当numlist=’23,56,90,543,90’，num=90时，position=3；当numlist=’23,56,90,543,90’，num=54时，position=-1。

注意：尽管543中有54这个数字，但并不认为54出现过。

解决办法：

1、首先给numlist和num两个待比较的列内容前后都加上逗号，这样就可以拿“,90,”类似这样的字符串去比较，容易操作；

2、通过instr函数定位子串的位置，如果返回结果为1则position＝1，为0则position＝-1，否则看第3步；

3、取第2步得到的位置之前的子串，判断其中逗号出现的次数，逗号的次数加1即为想要的position结果，那么如何判断一个字符串中某个字符或字符串出现的次数呢？

4、通过translate函数把不是逗号的字符都替换为空，结果只保留了逗号，然后直接用length函数取其长度即可。

SQL语句如下所示：

update tab_ch4 set position=
case when instr(','||numlist||',',','||num||',')=1 then 1
when instr(','||numlist||',',','||num||',')=0 then -1
else
length(
translate(substr(numlist,0,instr(','||numlist||',',','||num||',')-1),
','||substr(numlist,0,instr(','||numlist||',',','||num||',')-1),
',')
)+1
end

具体操作过程如下：

[oracle@erpdevdb ~]$ export ORACLE_SID=devdb
[oracle@erpdevdb ~]$ sqlplus "banping/***"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 11 10:37:06 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create table tab_ch4 (numlist varchar2(300) not null,num varchar2(5) not null,position number(4))
  2  /
Table created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,90','90')
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,90','54')
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,90','231')
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,90','23')
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,90','569')
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,76','76') 
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,76','762')
  2  /
1 row created.
SQL> insert into tab_ch4 (numlist,num) values ('23,56,90,543,7','72')
  2  /
1 row created.
SQL> commit;
Commit complete.
SQL> column numlist format A20
SQL> select * from tab_ch4
  2  /
NUMLIST              NUM     POSITION
-------------------- ----- ----------
23,56,90,543,90      90
23,56,90,543,90      54
23,56,90,543,90      231
23,56,90,543,90      23
23,56,90,543,90      569
23,56,90,543,76      76
23,56,90,543,76      762
23,56,90,543,7       72

SQL> update tab_ch4 set position=
  2  case when instr(','||numlist||',',','||num||',')=1 then 1
  3  when instr(','||numlist||',',','||num||',')=0 then -1
  4  else
  5  length(translate(substr(numlist,0,instr(','||numlist||',',','||num||',')-1),
  6  ','||substr(numlist,0,instr(','||numlist||',',','||num||',')-1),
  7  ','))+1
  8  end
  9  /
8 rows updated.
 
SQL> select * from tab_ch4
  2  /
NUMLIST              NUM     POSITION
-------------------- ----- ----------
23,56,90,543,90      90             3
23,56,90,543,90      54             -1
23,56,90,543,90      231            -1
23,56,90,543,90      23             1
23,56,90,543,90      569            -1
23,56,90,543,76      76             5
23,56,90,543,76      762            -1
23,56,90,543,7       72             -1
8 rows selected.

可以看到结果是完全正确的。

2009/05/11 12:05 - Oracle

Reader's Comments

1. bluelinguo | 2009/05/13 20:30

   多谢了，好好学习。