CREATE OR REPLACE PROCEDURE GDSC
IS
TYPE t_type IS RECORD
(
YPBH AJHD01.YPBH%type
);
TYPE t_type_array IS TABLE OF t_type INDEX BY BINARY_INTEGER;
i number:=1;
P_NUM1 INTEGER;
P_NUM2 INTEGER;
P_NUM3 INTEGER;
P_NUM4 INTEGER;
P_NUM5 INTEGER;
P_NUM6 INTEGER;
P_NUM7 INTEGER;
P_NUM8 INTEGER;
t_rec t_type;/*定义记录变量*/
t_rec_array t_type_array;/*定义集合变量*/
P_YPPH TEMP_AJHF03.YPPH%type;
P_JCXM TEMP_AJHF03.JCXM%type;
P_ROW AJHF04%ROWTYPE;
P_BM VARCHAR2(20);
/*P_YJDDB AJHF04.YJDDB%TYPE;
P_EJDDB AJHF04.EJDDB%TYPE;*/
P_ZB AJHF04.ZB%TYPE;
P_ZIB AJHF04.ZIB%TYPE;
P_ZB1 AJHF04.ZB%TYPE;
P_ZIB1 AJHF04.ZIB%TYPE;
/*P_WBWJB AJHF04.WBWJB%TYPE;*/
--p_str varchar2(500);
P_PK SYSHY_SJK.SYS_DIC03.PK%TYPE;
CURSOR C1 IS
SELECT DISTINCT YPPH,JCXM FROM TEMP_AJHF03 ORDER BY YPPH,JCXM ASC;
CURSOR C2 IS
SELECT DISTINCT PYDM FROM SYSHY_SJK.SYS_DIC03
WHERE SJJMC='||P_BM||' AND PK='是' ORDER BY XH;
BEGIN
OPEN C1;
FETCH C1 INTO P_YPPH,P_JCXM;/*提取资料员要求上传的批号,检测项目*/
WHILE C1%FOUND LOOP
SELECT COUNT(*) INTO P_NUM1 FROM AJHF03 WHERE SCCG='1' AND YPPH=P_YPPH;
BEGIN
FOR A IN(SELECT DISTINCT YPBH FROM AJHD01 WHERE YPPH=P_YPPH ORDER BY YPBH ASC)
loop
t_rec.YPBH:=A.YPBH; /*给记录变量中的成员赋值*/
t_rec_array(i):=t_rec; /*相当于给一条记录赋值,也就是相当于给数二维数组中的行赋值*/
i := i + 1;
end loop;
FOR B IN(SELECT DISTINCT YPBH FROM AJHW01 /*提取AJHW01中该批号下的所有编号*/
WHERE YPBH IN (SELECT DISTINCT YPBH FROM AJHD01 WHERE YPPH=P_YPPH) ORDER BY YPBH ASC)
loop
t_rec.YPBH:=B.YPBH; /*给记录变量中的成员赋值*/
t_rec_array(i):=t_rec; /*相当于给一条记录赋值,也就是相当于给数二维数组中的行赋值*/
i := i + 1;
end loop;
FOR C IN(SELECT DISTINCT YPBH FROM AJHS01 /*提取AJHS01中该批号下的所有编号*/
WHERE YPBH IN (SELECT DISTINCT YPBH FROM AJHD01 WHERE YPPH=P_YPPH) ORDER BY YPBH ASC)
loop
t_rec.YPBH:=C.YPBH;
t_rec_array(i):=t_rec;
i := i + 1;
end loop;
FOR D IN(SELECT DISTINCT YPBH FROM AJHG01 /*提取AJHG01中该批号下的所有编号*/
WHERE YPBH IN (SELECT DISTINCT YPBH FROM AJHD01 WHERE YPPH=P_YPPH) ORDER BY YPBH ASC)
loop
t_rec.YPBH:=D.YPBH;
t_rec_array(i):=t_rec;
i := i + 1;
end loop;
FOR E IN(SELECT DISTINCT YPBH FROM AJHL30 /*提取AJHL30中该批号下的所有编号*/
WHERE YPBH IN (SELECT DISTINCT YPBH FROM AJHD01 WHERE YPPH=P_YPPH) ORDER BY YPBH ASC)
loop
t_rec.YPBH:=E.YPBH;
t_rec_array(i):=t_rec;
i := i + 1;
end loop;
END;
IF P_NUM1=0 THEN
BEGIN
/*将一级调度表新增标志插入到SYS_INFO01*/
SELECT NVL(MAX(XH),0)+1 INTO P_NUM2 FROM SYS_INFO01;/*提取SYS_INFO01中最大的序号*/
INSERT INTO SYS_INFO01(XH,BMC,GLTJ,CZFS12)
VALUES (P_NUM2,'AJHD01','DW='||''''||'新疆'||''''||' AND YPBH='||''''||t_rec.YPBH||'''','I');
COMMIT;
SELECT NVL(MAX(XH),0)+1 INTO P_NUM3 FROM SYS_INFO01;
INSERT INTO SYS_INFO01(XH,BMC,GLTJ,CZFS12)
VALUES (P_NUM3,'AJHW01','DW='||''''||'新疆'||''''||' AND YPBH='||''''||t_rec.YPBH||'''','I');
COMMIT;
SELECT NVL(MAX(XH),0)+1 INTO P_NUM4 FROM SYS_INFO01;
INSERT INTO SYS_INFO01(XH,BMC,GLTJ,CZFS12)
VALUES (P_NUM4,'AJHS01','DW='||''''||'新疆'||''''||' AND YPBH='||''''||t_rec.YPBH||'''','I');
COMMIT;
SELECT NVL(MAX(XH),0)+1 INTO P_NUM5 FROM SYS_INFO01;
INSERT INTO SYS_INFO01(XH,BMC,GLTJ,CZFS12)
VALUES (P_NUM5,'AJHG01','DW='||''''||'新疆'||''''||' AND YPBH='||''''||t_rec.YPBH||'''','I');
COMMIT;
SELECT NVL(MAX(XH),0)+1 INTO P_NUM6 FROM SYS_INFO01;
INSERT INTO SYS_INFO01(XH,BMC,GLTJ,CZFS12)
VALUES (P_NUM6,'AJHL30','DW='||''''||'新疆'||''''||' AND YPBH='||''''||t_rec.YPBH||'''','I');
COMMIT;
END;
END IF;
/*3、根据临时表TEMP_AJHF03的检测项目从AJHF04中查询一级调度表、二级调度表、主表、子表、外部文件表的名称*/
SELECT DISTINCT * INTO P_ROW FROM AJHF04 WHERE JCXM=P_JCXM AND ROWNUM = 1;
--P_BM := P_ROW.YJDDB||','||P_ROW.EJDDB||','||P_ROW.ZB||','||P_ROW.ZIB||','||P_ROW.WBWJB;
/*P_YJDDB := P_ROW.YJDDB;
P_EJDDB := P_ROW.EJDDB;*/
P_ZB := P_ROW.ZB;
P_ZIB := P_ROW.ZIB;
/*P_WBWJB := P_ROW.WBWJB;*/
SELECT MOD(LENGTH('P_ZB'),6) INTO P_NUM7 FROM DUAL;
IF P_NUM7=0 THEN
INSERT INTO TEMP_AJHF04(JCXM,BM)
VALUES(P_JCXM,P_ZB);
COMMIT;
ELSE
FOR K IN 0..P_NUM7 LOOP
P_ZB1:= SUBSTR(P_ZB,1+7*K,6+7*K);
INSERT INTO TEMP_AJHF04(JCXM,BM)
VALUES(P_JCXM,P_ZB1);
COMMIT;
END LOOP;
END IF;
SELECT MOD(LENGTH('P_ZIB'),8) INTO P_NUM7 FROM DUAL;
IF P_NUM8=0 THEN
INSERT INTO TEMP_AJHF04(JCXM,BM)
VALUES(P_JCXM,P_ZIB);
COMMIT;
ELSE
FOR N IN 0..P_NUM8 LOOP
P_ZIB1:= SUBSTR(P_ZIB,1+9*N,8+9*N);
INSERT INTO TEMP_AJHF04(JCXM,BM)
VALUES(P_JCXM,P_ZIB1);
COMMIT;
END LOOP;
END IF;
SELECT DISTINCT BM INTO P_BM FROM TEMP_AJHF04 WHERE JCXM=P_JCXM ORDER BY ROWID;
OPEN C2;
FETCH C2 INTO P_PK;
WHILE C2%FOUND LOOP
/*P_STR :=SELECT DISTINCT P_PK FROM P_BM WHERE YPBH IN (SELECT DISTINCT YPBH FROM P_EJDDB where ypbh in(select distinct ypbh from ajhd01));*/
????
上面注释的是我需要写的语句,该语句实现从变量P_BM(一个具体表名)中提取他的主键,主键是变量,可能是多个,同时该编号存在变量P_EJDDB(一个具体表名)中,这样查询出来的结果可能是多个值,还需要将这多个值存放到一个索引表或者表中,请问该如何实现?我最终构造出来的SQL语句就是这样的,如下:
CURSOR C3 IS
SELECT DISTINCT DW,YPBH,SYWD FROM AJHW19 WHERE YPBH IN(SELECT DISTINCT YPBH FROM AJHW01 WHERE YPBH IN(SELECT DISTINCT YPBH FROM AJHD01)) ORDER BY YPBH,SYWD;
FETCH C2 INTO P_PK;
END LOOP;
CLOSE C2;
FETCH C1 INTO P_YPPH,P_JCXM;
END LOOP;
CLOSE C1;
END;