Oracle迭代和存储过程练习

用临时表生成包含层次信息的结构树。

一、创建测试帐号
DROP USER TEST CASCADE;
CREATE USER TEST IDENTIFIED BY tt;
GRANT CONNECT, RESOURCE TO TEST;


二、创建数据表SNLINK
CREATE TABLE SNLINK
(
 ID NUMBER(10)    NOT NULL,
 SN VARCHAR(10)    NOT NULL,
 FID NUMBER(10)
)
;


三、创建序列SEQ_SNLINK_ID
CREATE SEQUENCE SEQ_SNLINK_ID
MINVALUE 1
MAXVALUE 100000
START WITH 1
INCREMENT BY 1
;


四、递增函数getnewid
CREATE OR REPLACE FUNCTION getnewid RETURN NUMBER
AS
id NUMBER;
CURSOR myid IS SELECT seq_snlink_id.NEXTVAL FROM dual;
BEGIN
  OPEN myid;
  FETCH myid INTO id;
  CLOSE myid;
--  DBMS_OUTPUT.PUT_LINE('newid='||id);
  RETURN(id);
END getnewid;


五、查询SN对应的ID
CREATE OR REPLACE FUNCTION getidbysn(thesn IN VARCHAR2) RETURN NUMBER
AS
id NUMBER;
CURSOR myid(thesn VARCHAR2) IS SELECT id FROM snlink WHERE sn=thesn;
BEGIN
  OPEN myid(thesn);
  FETCH myid INTO id;
  IF myid%NOTFOUND THEN id:=0-getnewid;
  END IF;
  CLOSE myid;
  RETURN(id);
END getidbysn;


六、创建ADDSNLINK过程
CREATE OR REPLACE PROCEDURE addsnlink(fsn IN varchar2,csn IN varchar2) AS
  cid number;
  fid number;
  gid number;  -- grandfather id 
  cursql varchar2(1000);
BEGIN
-- get fid begin
  fid:=getidbysn(fsn);
  IF fid<0 THEN  -- get gid and insert fsn
    gid:=getnewid;
    cursql:='INSERT INTO snlink VALUES('||(0-fid)||', '||fsn||', '||(0-gid)||')';
--    DBMS_OUTPUT.put_line('sql1='||cursql); 
    EXECUTE immediate cursql;  
    fid:=0-fid;    
  END IF;
-- get cid begin
  cid:=getidbysn(csn);
  IF cid>0 THEN -- record found, raise error
--    DBMS_OUTPUT.PUT_LINE('RECORD FOUND, NOT ALLOWED!');
    ROLLBACK;
  ELSE
    cursql:='INSERT INTO snlink VALUES('||(0-cid)||', '||csn||', '||fid||')';
--    DBMS_OUTPUT.put_line('sql1='||cursql); 
    EXECUTE immediate cursql;       
    COMMIT;
  END IF;
END addsnlink;


七、一次性导入全部数据
CREATE OR REPLACE PROCEDURE inputalldata AS
  cursql varchar2(1000);
BEGIN
  cursql:='delete from snlink';
  EXECUTE immediate cursql;
  COMMIT;
  addsnlink('1111','1112');
  addsnlink('1111','1113');
  addsnlink('1113','1114');
  addsnlink('1113','1115');
  addsnlink('1111','1116');
  addsnlink('1116','1117');
  addsnlink('1117','1118');
  COMMIT;
END inputalldata;


八、以某SN为根进行层次查询
SELECT DISTINCT LEVEL, id, sn, fid
FROM SNLINK
START WITH SN='1111'
CONNECT BY PRIOR id=fid


九、查父节点
CREATE OR REPLACE FUNCTION getfatherid(cid IN NUMBER) RETURN NUMBER
AS
fid NUMBER;
CURSOR myid(cid NUMBER) IS SELECT fid FROM snlink WHERE id=cid;
BEGIN
  OPEN myid(cid);
  FETCH myid INTO fid;
  IF myid%NOTFOUND THEN fid:=0-getnewid;
  END IF;
  CLOSE myid;
  RETURN(fid);
END getfatherid;


十、查根节点
CREATE OR REPLACE FUNCTION getrootid(cid IN NUMBER) RETURN NUMBER
AS
cid2 NUMBER;
fid NUMBER;
rid NUMBER;
BEGIN
  cid2:=cid;
  fid:=0;
  rid:=0;
  WHILE fid>=0 LOOP
    fid:=getfatherid(cid2);
    cid2:=fid;
    IF fid>0 THEN rid:=fid;
    END IF;
  END LOOP;
  RETURN(rid);
END getrootid;


十一、创建查询结构临时表
CREATE GLOBAL TEMPORARY TABLE tmp_snlink_dim
(
 seqid        VARCHAR2(100),
 sn        VARCHAR2(10),
 cid        NUMBER(10),
 levelid    NUMBER(2),
 dim        VARCHAR2(255)
)
ON COMMIT PRESERVE ROWS;


十三、查家族
CREATE OR REPLACE PROCEDURE queryfamily(seqid2 VARCHAR2, pid2 IN NUMBER, levelid2 IN NUMBER, dim2 IN varchar2)
AS
 i NUMBER;
 pid3 NUMBER;
 levid NUMBER;
 levid2 NUMBER;
 dimid VARCHAR2(100);
 dimid2 VARCHAR2(100);
 cid2 NUMBER;
 cid3 NUMBER;
 csn2 VARCHAR2(10);
 cursql varchar2(1000);
 myrec snlink%ROWTYPE;
 CURSOR mycid IS SELECT * FROM snlink WHERE fid=pid2;
 CURSOR mycid2 IS SELECT * FROM snlink WHERE fid=pid3;
BEGIN
  i := 0;
  levid := levelid2 + 1;
--  DBMS_OUTPUT.put_line('i1:='||i);       
  OPEN mycid;
  FETCH mycid INTO myrec;
  WHILE mycid%FOUND LOOP
    i := i + 1;
--    DBMS_OUTPUT.put_line('i2:='||i);     
    cid2 := myrec.id;
    csn2 := myrec.sn;
    dimid := dim2 || ',' || i;
    cursql := 'INSERT INTO tmp_snlink_dim VALUES(''' || seqid2 || ''',''' || csn2 || ''',' || cid2;
    cursql := cursql || ',' || levid || ',''' || dimid || ''')';
--    DBMS_OUTPUT.put_line('sql='||cursql); 
    EXECUTE immediate cursql;
    cid3:=cid2;
    levid2:=levid;
    dimid2:=dimid;
    queryfamily(seqid2,cid3,levid2,dimid2);
--    cid2:=cid3;
--    levid:=levid2;
--    dimid:=dimid2;
    FETCH mycid INTO myrec; 
  END LOOP;
  CLOSE mycid;
  COMMIT;
END queryfamily;



十四、查询ID对应的SN
CREATE OR REPLACE FUNCTION getsnbyid(theid IN NUMBER) RETURN VARCHAR2
AS
sn NUMBER;
CURSOR myid(theid NUMBER) IS SELECT sn FROM snlink WHERE id=theid;
BEGIN
  OPEN myid(theid);
  FETCH myid INTO sn;
  IF myid%NOTFOUND THEN sn:='';
  END IF;
  CLOSE myid;
  RETURN(sn);
END getsnbyid;


十五、根据SN和线程字符串生成临时结果集
CREATE OR REPLACE PROCEDURE queryfamily2(csn IN VARCHAR2, seqid IN VARCHAR2)
AS
  cid NUMBER;
  rootid NUMBER;
  rootsn varchar2(10);
  cursql varchar2(1000); 
BEGIN
  cid:=getidbysn(csn); 
  if cid>0 then
   rootid:=getrootid(cid);
   rootsn:=getsnbyid(rootid);
   cursql := 'INSERT INTO tmp_snlink_dim VALUES(''' || seqid || ''',' || rootsn || ',' || rootid;
   cursql := cursql || ',' || 1 || ',''1'')';
--   DBMS_OUTPUT.put_line('sql='||cursql); 
   EXECUTE immediate cursql;     
   queryfamily(seqid,rootid,1,'1');
  END IF;
END queryfamily2;


十六、生成结果：
EXECUTE QUERYFAMILY2('1113','XYZ');
SQL> SELECT * FROM TMP_SNLINK_DIM ORDER BY DIM;

SEQID        SN                 CID LEVELID  DIM
------------ ---------- ----------- -------  -----------
XYZ          1111               189       1  1
XYZ          1116               195       2  1,1
XYZ          1117               196       3  1,1,1
XYZ          1118               197       4  1,1,1,1
XYZ          AC                 193       2  1,2
XYZ          1112               191       2  1,3
XYZ          1113               192       2  1,4
XYZ          1114               193       3  1,4,1
XYZ          1115               194       3  1,4,2