关于function结果集以及参与连接运算的问题

[Ykang]

这个问题比较宽泛，但下面的问题比较明确：
Create Table Test_Clob
(
id varchar2(32),
Info CLOB,
primary key(id)
);
Create Table Test_Clob1
(
ID varchar2(32),
C1 varchar2(32),
C2 varchar2(32),
C3 varchar2(32)
primary key(id)
);
--Test_Clob和TestClob1以ID关联
Insert into Test_Clob values('1','11,12,13');
Insert into Test_Clob values('2','21,22,23');
Insert into Test_Clob values('3','31,32,33');

现在有两个问题：
1、能否用一个Select(当然包含子Select,Select里可以使用function)以及使用返回结果集的返回如下结果：
1 11 12 13
2 21 22 23
3 31 32 33
2、能否用一个Update更改Test_Clob1的c1,c2,c3,假定Test_Clob1只有ID有数值:
Test_Clob1 ID c1 c2 c3
1
2
3
则更新后，结果如下：
1 11 12 13
2 21 22 23
3 31 32 33

谢谢大家

[oracledbalgtu]

SELECT ID,
       SUBSTR(INFO, 1, INSTR(INFO, ',', 1) - 1) INFO1,
       SUBSTR(INFO,
              INSTR(INFO, ',', 1, 1) + 1,
              INSTR(INFO, ',', 1, 2) - INSTR(INFO, ',', 1, 1) - 1) INFO2,
       SUBSTR(INFO, INSTR(INFO, ',', 1, 2) + 1) INFO3
  FROM (SELECT ID, DBMS_LOB.SUBSTR(INFO, DBMS_LOB.GETLENGTH(INFO), 1) INFO
          FROM TEST_CLOB);

[Quote=引用楼主 Ykang 的帖子:]
这个问题比较宽泛，但下面的问题比较明确：
Create Table Test_Clob
(
id varchar2(32),
Info CLOB,
primary key(id)
);
Create Table Test_Clob1
(
ID varchar2(32),
C1 varchar2(32),
C2 varchar2(32),
C3 varchar2(32)
primary key(id)
);
--Test_Clob和TestClob1以ID关联
Insert into Test_Clob values('1','11,12,13');
Insert into Test_Clob …
[/Quote]

[oracledbalgtu]

如果TEST_CLOB1表id列有值的情况下update的方法：
UPDATE TEST_CLOB1
   SET (C1, C2, C3) = (SELECT  SUBSTR(INFO, 1, INSTR(INFO, ',', 1) - 1) INFO1,
                              SUBSTR(INFO,
                                     INSTR(INFO, ',', 1, 1) + 1,
                                     INSTR(INFO, ',', 1, 2) -
                                     INSTR(INFO, ',', 1, 1) - 1) INFO2,
                              SUBSTR(INFO, INSTR(INFO, ',', 1, 2) + 1) INFO3
                         FROM (SELECT ID,
                                      DBMS_LOB.SUBSTR(INFO,
                                                      DBMS_LOB.GETLENGTH(INFO),
                                                      1) INFO
                                 FROM TEST_CLOB)
                        WHERE ID = TEST_CLOB1.ID)
 WHERE EXISTS (SELECT NULL FROM TEST_CLOB WHERE ID = TEST_CLOB1.ID);
如果TEST_CLOB1表没有值的方法:
INSERT INTO TEST_CLOB1
  SELECT ID,
         SUBSTR(INFO, 1, INSTR(INFO, ',', 1) - 1) INFO1,
         SUBSTR(INFO,
                INSTR(INFO, ',', 1, 1) + 1,
                INSTR(INFO, ',', 1, 2) - INSTR(INFO, ',', 1, 1) - 1) INFO2,
         SUBSTR(INFO, INSTR(INFO, ',', 1, 2) + 1) INFO3
    FROM (SELECT ID, DBMS_LOB.SUBSTR(INFO, DBMS_LOB.GETLENGTH(INFO), 1) INFO
            FROM TEST_CLOB);

[Quote=引用 1 楼 oracledbalgtu 的回复:]
SQL code
SELECT ID,
SUBSTR(INFO, 1, INSTR(INFO, ',', 1) - 1) INFO1,
SUBSTR(INFO,
INSTR(INFO, ',', 1, 1) + 1,
INSTR(INFO, ',', 1, 2) - INSTR(INFO, ',', 1, 1) - 1) INFO2,
SUBSTR(INFO, INSTR(INFO, ',', 1, 2) + 1) INFO3
FROM (SELECT ID, DBMS_LOB.SUBSTR(INFO, DBMS_LOB.GETLENGTH(INFO), 1) INFO
FROM TEST_CLOB);



引用楼主 Ykang 的…
[/Quote]

[Ykang]

to oracledbalgtu:
我的本意是要自己写个返回结果集的function配合Select和Update，因为在实际项目中，不可能单纯用到Substr以及instr

[oracledbalgtu]

这还不简单，如下：
CREATE OR REPLACE FUNCTION GET_SUBSTR(P_STR VARCHAR2, P_POSITION INT)
  RETURN VARCHAR2 IS
  L_R VARCHAR2(1000);
BEGIN
  L_R := TRIM(BOTH ',' FROM P_STR);
  L_R := ',' || L_R || ',';
  L_R := SUBSTR(L_R,
                INSTR(L_R, ',', 1, P_POSITION) + 1,
                INSTR(L_R, ',', 1, P_POSITION + 1) -
                INSTR(L_R, ',', 1, P_POSITION) - 1);
  RETURN(L_R);
END GET_SUBSTR;
/

SELECT ID,
       GET_SUBSTR(INFO, 1) S1,
       GET_SUBSTR(INFO, 2) S2,
       GET_SUBSTR(INFO, 3) S3
  FROM (SELECT ID, DBMS_LOB.SUBSTR(INFO, DBMS_LOB.GETLENGTH(INFO), 1) INFO
          FROM TEST_CLOB);
输出:
ID	S1	S2	S3
1	11	12	13
2	21	22	23
3	31	32	33

至于update，参照楼上。

[Quote=引用 3 楼 Ykang 的回复:]
to oracledbalgtu:
我的本意是要自己写个返回结果集的function配合Select和Update，因为在实际项目中，不可能单纯用到Substr以及instr
[/Quote]

[fxianxian]

不错
学习中!