求一个存储过程的写法 在线等！！谢谢了

[登峰造Geek]

例子
表1
row_id item_no
11 1
22 2
33 3
44 4

表2
item_no date
1,2 2011-06-21
3 2011-06-22
4 2011-06-23


用什么方式写出来 效果是

11 1 2011-06-21
22 2 2011-06-21
33 3 2011-06-22
44 4 2011-06-23

[登峰造Geek]

自己顶

[hudingchen]

sql的：

SQL> WITH t1 AS (
  2    SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
  3    SELECT '22' ,'2'  FROM DUAL UNION ALL
  4    SELECT '33' ,'3'  FROM DUAL UNION ALL
  5    SELECT '44' ,'4'  FROM DUAL
  6  ),t2 AS (
  7    SELECT '1,2' item_no,'2011-06-21' idate FROM DUAL UNION ALL
  8    SELECT '3' ,'2011-06-22'  FROM DUAL UNION ALL
  9    SELECT '4' ,'2011-06-23'  FROM DUAL
 10  )
 11  SELECT t1.*,
 12         t2.idate
 13    FROM t1,
 14         t2
 15   WHERE instr(',' || t2.item_no || ',', ',' || t1.item_no || ',') > 0;

ROW_ IT IDATE
---- -- --------------------
11   1  2011-06-21
22   2  2011-06-21
33   3  2011-06-22
44   4  2011-06-23

[bonoobar]

学习了[Quote=引用 2 楼 hudingchen 的回复:]

sql的：
SQL code
SQL> WITH t1 AS (
2 SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
3 SELECT '22' ,'2' FROM DUAL UNION ALL
4 SELECT '33' ,'3' FROM DUAL UNION ALL
5 SELECT '4……
[/Quote]

[aaa19891215]

table1代表你的表1 table2代表你的表2

create procedure Pro_T is
begin
select t1.row_id,t1.item_no,t2.date from table1 t1 left join table2 t2 where t1.item_no = t2.item_no;
end;

[aaa19891215]

是我理解错了还是怎么,楼上为什么那样展示来写?一条查询语句不就搞定的事?求点拨...

[bonoobar]

表2中有1,2这种数据，你怎么left join[Quote=引用 5 楼 aaa19891215 的回复:]

是我理解错了还是怎么,楼上为什么那样展示来写?一条查询语句不就搞定的事?求点拨...
[/Quote]

[304的的哥]

select t1.row_id,t1.item_no,t2.idate
from t1,t2
where instr(t2.item_no,t1.item_no)>0
/

[登峰造Geek]

[Quote=引用 7 楼 bobo12082119 的回复:]
SQL code


select t1.row_id,t1.item_no,t2.idate
from t1,t2
where instr(t2.item_no,t1.item_no)>0
/
[/Quote]




这个方式不行 ，instr 是单行查询，

[304的的哥]

SQL> 
SQL> WITH t1 AS (
  2       SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
  3       SELECT '22' ,'2'  FROM DUAL UNION ALL
  4       SELECT '33' ,'3'  FROM DUAL UNION ALL
  5       SELECT '44' ,'4'  FROM DUAL)
  6  , t2 AS (
  7      SELECT '1,2' item_no,'2011-06-21' idate FROM DUAL UNION ALL
  8      SELECT '3' ,'2011-06-22'  FROM DUAL UNION ALL
  9      SELECT '4' ,'2011-06-23'  FROM DUAL)
 10  select t1.row_id,t1.item_no,t2.idate
 11  from t1,t2
 12  where instr(t2.item_no,t1.item_no)>0
 13  /
 
ROW_ID ITEM_NO IDATE
------ ------- ----------
11     1       2011-06-21
22     2       2011-06-21
33     3       2011-06-22
44     4       2011-06-23

[hudingchen]

[Quote=引用 9 楼 bobo12082119 的回复:]

SQL code

SQL>
SQL> WITH t1 AS (
2 SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
3 SELECT '22' ,'2' FROM DUAL UNION ALL
4 SELECT '33' ,'3' FROM DUAL UNION ALL
5 ……
[/Quote]
你写这个不对哦，如果t2表中有'1,22'这个值，你的instr(t2.item_no,t1.item_no)>0
条件也满足了

[hudingchen]

instr('1,22','2') > 0 明白没？

[「已注销」]

[Quote=引用 9 楼 bobo12082119 的回复:]

SQL code

SQL>
SQL> WITH t1 AS (
2 SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
3 SELECT '22' ,'2' FROM DUAL UNION ALL
4 SELECT '33' ,'3' FROM DUAL UNION ALL
5 ……
[/Quote]
超过10你这个就出问题了
e.g.:
SELECT INSTR('11,12,13',',3,') FROM dual d

[fangxiaoke]

CREATE OR REPLACE PROCEDURE P_TEST_SUM(

i_BeginTime IN VARCHAR2,
i_EndTime IN VARCHAR2
)

AS
v_BeginTime VARCHAR2(20); -- 结算记录起始时间
v_EndTime VARCHAR2(20); -- 结算记录结束时间

BEGIN


EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END P_TEST_SUM;


楼主结构就这样了，中间的逻辑可以随便加

[304的的哥]

SQL> 
SQL> WITH t1 AS (
  2       SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
  3       SELECT '22' ,'2'  FROM DUAL UNION ALL
  4       SELECT '33' ,'3'  FROM DUAL UNION ALL
  5       SELECT '44' ,'4'  FROM DUAL UNION ALL
  6       SELECT '55' ,'5'  FROM DUAL)
  7  , t2 AS (
  8       SELECT '1,2,50' item_no,'2011-06-21' idate FROM DUAL UNION ALL
  9       SELECT '3' ,'2011-06-22'  FROM DUAL UNION ALL
 10       SELECT '4' ,'2011-06-23'  FROM DUAL)
 11  select t1.row_id,t3.idate
 12  from t1,(
 13       select distinct regexp_substr(item_no,'[^,]+',1,level) item_no,idate
 14       from t2
 15       connect by level<=length(item_no)-length(replace(item_no,',',''))+1) t3
 16  where t1.item_no=t3.item_no
 17  /
 
ROW_ID IDATE
------ ----------
11     2011-06-21
22     2011-06-21
33     2011-06-22
44     2011-06-23
 
SQL>