我写了一个oracle的存储过程,第一次写,有错,自己不会改,哪位高手帮我看看。把几张表里面的数据存到一张表里面CREATE OR REPLACE PROCEDURE procedure_system1 IS
table_name IN VARCHAR2;
table_system IN VARCHAR2;
max_num INTEGER;
count_num INTEGER;
--sbdc_adjtype待遇调整原因表
TYPE Code_Tab_adjtype IS TABLE OF sbdc_adjtype.adjcode%TYPE;
TYPE Name_Tab_adjtype IS TABLE OF sbdc_adjtype.adjname%TYPE;
Codes_adjtype Code_Tab_adjitem;
Names_adjtype Name_Tab_adjitem;
CURSOR cursor_adjitem IS
SELECT pyitem,adjcode FROM sbdc_adjitem WHERE vflag = Flags_adjitem;
--sbdc_admlevel行政职务(级别)表
TYPE Code_Tab_admlevel IS TABLE OF sbdc_admlevel.adcode%TYPE;
TYPE Name_Tab_admlevel IS TABLE OF sbdc_admlevel.adname%TYPE;
Codes_admlevel Code_Tab_admlevel;
Names_admlevel Name_Tab_admlevel;
Flags_admlevel sbdc_admlevel.cdflag%TYPE;
CURSOR cursor_admlevel IS
SELECT adcode,adname FROM sbdc_admlevel WHERE cdflag = Flags_admlevel;
BEGIN
CASE tablename
WHEN 'sbdc_admlevel'
THEN SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_admlevel := '0';
IF count_num := 0 THEN
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
INSERT INTO EA10 VALUES (i,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'0');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES (max_num,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'0');
END LOOP;
END IF;
SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_admlevel := '1';
IF count_num := 0 THEN
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
INSERT INTO EA10 VALUES (i,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'1');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES (max_num,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'1');
END LOOP;
END IF;
WHEN 'sbdc_adjitem'
THEN SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_adjitem := '0';
IF count_num := 0 THEN
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
INSERT INTO EA10 VALUES(i,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'0');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES(max_num,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'0');
END LOOP;
END IF;
SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_adjitem := '1';
IF count_num := 0 THEN
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes.COUNT LOOP
INSERT INTO EA10 VALUES(i,'adjitem', '特殊工种编码',Codes_adjitem(j),Names_adjitem(j),'1');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES(max_num,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'1');
END LOOP;
END IF;
END CASE;
END;
table_name IN VARCHAR2;
table_system IN VARCHAR2;
max_num INTEGER;
count_num INTEGER;
--sbdc_adjtype待遇调整原因表
TYPE Code_Tab_adjtype IS TABLE OF sbdc_adjtype.adjcode%TYPE;
TYPE Name_Tab_adjtype IS TABLE OF sbdc_adjtype.adjname%TYPE;
Codes_adjtype Code_Tab_adjitem;
Names_adjtype Name_Tab_adjitem;
CURSOR cursor_adjitem IS
SELECT pyitem,adjcode FROM sbdc_adjitem WHERE vflag = Flags_adjitem;
--sbdc_admlevel行政职务(级别)表
TYPE Code_Tab_admlevel IS TABLE OF sbdc_admlevel.adcode%TYPE;
TYPE Name_Tab_admlevel IS TABLE OF sbdc_admlevel.adname%TYPE;
Codes_admlevel Code_Tab_admlevel;
Names_admlevel Name_Tab_admlevel;
Flags_admlevel sbdc_admlevel.cdflag%TYPE;
CURSOR cursor_admlevel IS
SELECT adcode,adname FROM sbdc_admlevel WHERE cdflag = Flags_admlevel;
BEGIN
CASE tablename
WHEN 'sbdc_admlevel'
THEN SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_admlevel := '0';
IF count_num := 0 THEN
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
INSERT INTO EA10 VALUES (i,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'0');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES (max_num,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'0');
END LOOP;
END IF;
SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_admlevel := '1';
IF count_num := 0 THEN
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
INSERT INTO EA10 VALUES (i,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'1');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES (max_num,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'1');
END LOOP;
END IF;
WHEN 'sbdc_adjitem'
THEN SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_adjitem := '0';
IF count_num := 0 THEN
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
INSERT INTO EA10 VALUES(i,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'0');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES(max_num,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'0');
END LOOP;
END IF;
SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_adjitem := '1';
IF count_num := 0 THEN
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes.COUNT LOOP
INSERT INTO EA10 VALUES(i,'adjitem', '特殊工种编码',Codes_adjitem(j),Names_adjitem(j),'1');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES(max_num,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'1');
END LOOP;
END IF;
END CASE;
END;
table_name IN VARCHAR2;
table_system IN VARCHAR2;
max_num INTEGER;
count_num INTEGER;
--sbdc_adjitem退休人员待遇调整项目归类表
TYPE Code_Tab_adjitem IS TABLE OF sbdc_adjitem.adjcode%TYPE;
TYPE Name_Tab_adjitem IS TABLE OF sbdc_adjitem.pyitem%TYPE;
Codes_adjitem Code_Tab_adjitem;
Names_adjitem Name_Tab_adjitem;
Flags_adjitem sbdc_adjitem.vflag%TYPE;
CURSOR cursor_adjitem IS
SELECT pyitem,adjcode FROM sbdc_adjitem WHERE vflag = Flags_adjitem;
--sbdc_adjtype待遇调整原因表
TYPE Code_Tab_adjtype IS TABLE OF sbdc_adjtype.adjcode%TYPE;
TYPE Name_Tab_adjtype IS TABLE OF sbdc_adjtype.adjname%TYPE;
Codes_adjtype Code_Tab_adjitem;
Names_adjtype Name_Tab_adjitem;
CURSOR cursor_adjitem IS
SELECT pyitem,adjcode FROM sbdc_adjitem WHERE vflag = Flags_adjitem;
--sbdc_admlevel行政职务(级别)表
TYPE Code_Tab_admlevel IS TABLE OF sbdc_admlevel.adcode%TYPE;
TYPE Name_Tab_admlevel IS TABLE OF sbdc_admlevel.adname%TYPE;
Codes_admlevel Code_Tab_admlevel;
Names_admlevel Name_Tab_admlevel;
Flags_admlevel sbdc_admlevel.cdflag%TYPE;
CURSOR cursor_admlevel IS
SELECT adcode,adname FROM sbdc_admlevel WHERE cdflag = Flags_admlevel;
BEGIN
CASE tablename
WHEN 'sbdc_admlevel'
THEN SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_admlevel := '0';
IF count_num := 0 THEN
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
INSERT INTO EA10 VALUES (i,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'0');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES (max_num,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'0');
END LOOP;
END IF;
SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_admlevel := '1';
IF count_num := 0 THEN
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
INSERT INTO EA10 VALUES (i,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'1');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_admlevel;
FETCH cursor_admlevel BULK COLLECT INTO Codes_admlevel,Names_admlevel;
CLOSE cursor_admlevel;
FOR i in 1..Codes_admlevel.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES (max_num,'admlevel','行政职务',Codes_admlevel(i),Names_admlevel(i),'1');
END LOOP;
END IF;
WHEN 'sbdc_adjitem'
THEN SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_adjitem := '0';
IF count_num := 0 THEN
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
INSERT INTO EA10 VALUES(i,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'0');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES(max_num,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'0');
END LOOP;
END IF;
SELECT COUNT(EKE028) INTO count_num FROM EA10;
--select max(EKE028) into max_num from EA10;
Flags_adjitem := '1';
IF count_num := 0 THEN
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes.COUNT LOOP
INSERT INTO EA10 VALUES(i,'adjitem', '特殊工种编码',Codes_adjitem(j),Names_adjitem(j),'1');
END LOOP;
ELSE
SELECT MAX(EKE028) INTO max_num FROM EA10;
OPEN cursor_adjitem;
FETCH cursor_adjitem BULK COLLECT INTO Codes_adjitem,Names_adjitem;
CLOSE cursor_adjitem;
FOR i IN 1..Codes_adjitem.COUNT LOOP
max_num := max_num + 1;
INSERT INTO EA10 VALUES(max_num,'adjitem', '特殊工种编码',Codes_adjitem(i),Names_adjitem(i),'1');
END LOOP;
END IF;
END CASE;
END;