Oracle迭代和存储过程练习2

一、创建多维表snlink_dim
create table SNLINK_DIM
(
  ROOTID  NUMBER(10),
  SN      VARCHAR2(10),
  CID     NUMBER(10),
  LEVELID NUMBER(2),
  DIM     VARCHAR2(255)
)
;


二、创建ADDSNLINK触发器
CREATE OR REPLACE TRIGGER tg_addsnlink
AFTER INSERT ON snlink
FOR EACH ROW
DECLARE
  pragma AUTONOMOUS_TRANSACTION;
  rootid  NUMBER;
  cid     NUMBER;
  fsn     VARCHAR2(100);
  flevid  NUMBER;
  fdim    VARCHAR2(100);
  clevid  NUMBER;
  cdim    VARCHAR2(100);
  cursql  VARCHAR2(1000);
  mdim    VARCHAR2(100);
  mdim2   VARCHAR2(100);
  i       NUMBER;
  TYPE myrecord IS RECORD(levelid NUMBER, dim VARCHAR2(100));
  myrec myrecord;
  CURSOR myfid(thesn VARCHAR2) IS SELECT levelid, dim FROM snlink_dim WHERE sn=thesn;
  CURSOR myfid2(rid NUMBER, lev NUMBER, fdim VARCHAR2, flen NUMBER)
   IS SELECT MAX(DIM) FROM snlink_dim
   WHERE rootid = rid
   AND levelid = lev
   AND SUBSTR(dim, 1, flen+1) = fdim || ',';
BEGIN
  cid := :new.id;
  rootid := getrootid(:new.fid);
  IF :new.fid<0 THEN -- add new root
    rootid := :new.id;
    clevid := 1;
    cdim :='1';
    cursql := 'INSERT INTO snlink_dim VALUES(' || rootid || ',''' || :new.sn || ''',' || rootid;
    cursql := cursql || ',' || 1 || ',''1'')';
    EXECUTE immediate cursql;
    COMMIT;       
  ELSE  -- to get levelid and dim
    cid := :new.id;
    fsn := getsnbyid(:new.fid);
    OPEN myfid(fsn);
    FETCH myfid INTO myrec;
    flevid := myrec.levelid;
    fdim := myrec.dim;
    clevid := flevid + 1; -- get levelid
    OPEN myfid2(rootid, clevid, fdim, length(fdim));
    FETCH myfid2 INTO mdim;
    IF (mdim is null) or (mdim = '') THEN -- first child
      cdim := fdim || ',1';   
    ELSE --
      i := 1;
      WHILE i>0 LOOP
        i := INSTR(mdim,',');
        if i>0 THEN
         mdim := SUBSTR(mdim,i+1,length(mdim)-i);        
        END IF;
      END LOOP;
      mdim2 := TO_CHAR(TO_NUMBER(mdim)+1);
      cdim := fdim || ',' || mdim2;      
    END IF;   
    cursql := 'INSERT INTO snlink_dim VALUES(' || rootid || ',''' || :new.sn || ''',' || cid;
    cursql := cursql || ',' || clevid || ',''' || cdim || ''')';    
    EXECUTE immediate cursql;
    COMMIT;          
  END IF;  
END;


三、建SNINFO表
CREATE TABLE sninfos
(
 sn    varchar2(10),
 pn    varchar2(10)
);


四、定义产品结构
+pn1
 -pn2
 +pn3
  +pn4
   -pn5


五、创建批量导入的存储过程
CREATE OR REPLACE PROCEDURE batch_add_snlink(beginid NUMBER, qty NUMBER)
AS
  i NUMBER;
  cursql VARCHAR2(1000);
  sn1 VARCHAR2(10);
  sn2 VARCHAR2(10);
  sn3 VARCHAR2(10);
  sn4 VARCHAR2(10);
  sn5 VARCHAR2(10);
BEGIN
  i := beginid;
  WHILE i< (beginid + qty) LOOP
    sn1 := 'sn1-' || i;
    sn2 := 'sn2-' || i;
    sn3 := 'sn3-' || i;
    sn4 := 'sn4-' || i;
    sn5 := 'sn5-' || i;
    cursql := 'INSERT INTO sninfos values('''||sn1||''', ''pn1'')';
    EXECUTE immediate cursql;
    COMMIT;
    cursql := 'INSERT INTO sninfos values('''||sn2||''', ''pn2'')';
    EXECUTE immediate cursql;
    COMMIT;
    cursql := 'INSERT INTO sninfos values('''||sn3||''', ''pn3'')';
    EXECUTE immediate cursql;
    COMMIT;  
    cursql := 'INSERT INTO sninfos values('''||sn4||''', ''pn4'')';
    EXECUTE immediate cursql;
    COMMIT;  
    cursql := 'INSERT INTO sninfos values('''||sn5||''', ''pn5'')';           
    EXECUTE immediate cursql;
    COMMIT;
    addsnlink(sn1, sn2);
    addsnlink(sn1, sn3);
    addsnlink(sn3, sn4);
    addsnlink(sn4, sn5);
    i := i + 1;
  END LOOP;
END;


六、调用batch_add_snlink(1001,1000)插入100条记录，并查看sninfos, snlink, snlink_dim的数据情况。


七、从snlink表查询数据生成交叉表
SELECT
 i1.sn as pn1,
 i2.sn as pn2,
 i3.sn as pn3,
 i4.sn as pn4,
 i5.sn as pn5
FROM
 sninfos i1,
 sninfos i2,
 sninfos i3,
 sninfos i4,
 sninfos i5,
 snlink s1,
 snlink s2,
 snlink s3,
 snlink s4,
 snlink s5
WHERE i1.sn = s1.sn
AND i2.sn = s2.sn
AND i3.sn = s3.sn
AND i4.sn = s4.sn
AND i5.sn = s5.sn
AND s1.id = s2.fid
AND i2.pn = 'pn2'
AND s1.id = s3.fid
AND i3.pn = 'pn3'
AND s3.id = s4.fid
AND s4.id = s5.fid
ORDER BY i1.sn

1120 rows selected in 0.889 seconds.
10000 rows selected in 6.707 seconds.
24092 rows selected in 25.335 seconds.

八、从snlink_dim表查询数据生成交叉表
SELECT
 s1.sn as pn1,
 s2.sn as pn2,
 s3.sn as pn3,
 s4.sn as pn4,
 s5.sn as pn5
FROM
 snlink_dim s1,
 snlink_dim s2,
 snlink_dim s3,
 snlink_dim s4,
 snlink_dim s5
WHERE s1.dim = '1'
AND s2.dim = '1,1'
AND s3.dim = '1,2'
AND s4.dim = '1,2,1'
AND s5.dim = '1,2,1,1'
AND s1.rootid = s2.rootid
AND s1.rootid = s3.rootid
AND s1.rootid = s4.rootid
AND s1.rootid = s5.rootid
ORDER BY s1.sn

1130 rows selected in 0.546 seconds.
10000 rows selected in 4.93 seconds.
24092 rows selected in 11.654 seconds.


九、创建BOM表1
CREATE TABLE bom1
(
 pn    VARCHAR2(10),
 cpn   VARCHAR2(10),
 qty   NUMBER(3)
);

输入以下数据：
+pn1
 +pn2,1
  -pn3,2
  -pn6,2
 +pn4,2
  -pn5,1
 -pn3,1


十、创建pn表
CREATE TABLE pn
(
 pn    VARCHAR2(10),
 des  VARCHAR2(1024)
);


十一、关联迭代查询
SELECT LEVEL, bom1.pn, bom1.cpn, pn.des, bom1.qty
FROM bom1, pn
WHERE bom1.cpn=pn.pn
START WITH bom1.pn='pn1'
CONNECT BY PRIOR cpn=bom1.pn