存储过程,紧急求助

Question

紧急求助, --存储了系统信息
CREATE TABLE CZSYSTEMM1
   ( "ID" VARCHAR2(60),  
"SHTEL_CZSYSTEM" VARCHAR2(60)
   ) 
--存储了产品信息
CREATE TABLE CZPRODUCTM1
   ( "ID" VARCHAR2(60), 
"SHTEL_CZPRODUCT" VARCHAR2(60)
   ) 
--存储了问题信息
CREATE TABLE CZPROBLEMTYPEM1
   ( "ID" VARCHAR2(60), 
"SHTEL_CZPROBLEMTYPE" VARCHAR2(60)
   )-- 存储事件信息
CREATE TABLE PROBSUMMARYM1 
(  
"CZPROBLEMTYPE" VARCHAR2(60), 
"CZPRODUCT" VARCHAR2(60), 
"CZSYSTEM" VARCHAR2(60), 
"CHANNEL" VARCHAR2(50)
)其中关联关系如下
   PROBSUMMARYM1.CZPRODUCT=CZPRODUCTM1.SHTEL_CZPRODUCT
   PROBSUMMARYM1.CZPROBLEMTYPE=CZPROBLEMTYPEM1.SHTEL_CZPROBLEMTYPE
   PROBSUMMARYM1.CZSYSTEM=CZSYSTEMM1.SHTEL_CZSYSTEM
-- 聚合的表
CREATE TABLE PROBAGGREGATION (
        "ID" VARCHAR2(60),
        "CZPRODUCT" VARCHAR2(60), 
"CZPROBLEMTYPE" VARCHAR2(60), 
"CZSYSTEM" VARCHAR2(60),
        "TOTAL" NUMBER
)问题如下:   我想嵌套遍历前3个表,并查询对应的记录在第四个表中,是否存在,不存在, 在0,存在+1, 所有嵌套查询的结果插入表PROBAGGREGATION 因为确实没怎么用过存储过程,紧急求助各位,帮帮忙,谢谢.

Answer 1

将3个表的连接语句用1条sql写出来
对其游标遍历
结果插入第四个表。
主要是3个表的连接问题
剩下的只要你会用游标
很简单了

Answer 2

create or replace pro
is
   
   v_flag number := 0;
   v_cnt  number := 0;begin
   for i in (select c1,c2 from t1,t2,t3 where t1.c3 = t2.c3 and t3.c3 = t1.c3)
   loop
      select count(1) into v_flag from t4 where exists (t4.c1 = i.c1);
      if v_flag > 0 then 
         v_cnt := v_cnt + 1;
         insert into t4 values(i.c1,i.c2);
         v_flag := 0;
      end if;
   end loop;
exception 
   when others then
      rollback;
end pro;
/

Answer 3

想问的问题是:   统计PROBSUMMARYM1 中的事件,但要将前三个表中的所有组合都列出来插入PROBAGGREGATION 

Answer 4

CREATE OR REPLACE PROCEDURE Pro
IS 
  num number; 
BEGIN 
    num:=0; 
    delete from PROBAGGREGATION; 
    commit; 
    declare cursor c1 is 
        select SHTEL_CZPRODUCT from SHTELEPRODUCTM1; 
        c1R c1.SHTEL_CZPRODUCT%TYPE; 
  begin 
    for c1R in c1 loop 
        
        declare cursor c2 is 
        select SHTEL_CZPROBLEMTYPE from SHTELEPROBLEMTYPEM1; 
        c2R c2.SHTEL_CZPROBLEMTYPE%TYPE; 
        begin 
          for c2R in c2 loop 
              
                declare cursor c3 is 
                select SHTEL_CZSYSTEM from SHTELESYSTEMM1; 
                c3R c3.SHTEL_CZSYSTEM%TYPE; 
                begin 
                    for c3R in c3 loop 
                    SELECT COUNT(*) T FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1
                     WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;
                     INSERT INTO PROBAGGREGATION(CZPRODUCT,CZPROBLEMTYPE,CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,T);
                    end loop; 
                end;  
            end loop; 
          end; 
    end loop; 
    commit; 
  end; 
END Pro; 
报错:Compilation errors for PROCEDURE SHSMUSER.PROError: PLS-00225: subprogram or cursor 'C1' reference is out of scope
Line: 10
Text: c1R c1.SHTEL_CZPRODUCT%TYPE;Error: PL/SQL: Item ignored
Line: 10
Text: c1R c1.SHTEL_CZPRODUCT%TYPE;Error: PLS-00225: subprogram or cursor 'C2' reference is out of scope
Line: 16
Text: c2R c2.SHTEL_CZPROBLEMTYPE%TYPE;Error: PL/SQL: Item ignored
Line: 16
Text: c2R c2.SHTEL_CZPROBLEMTYPE%TYPE;Error: PLS-00225: subprogram or cursor 'C3' reference is out of scope
Line: 22
Text: c3R c3.SHTEL_CZSYSTEM%TYPE;Error: PL/SQL: Item ignored
Line: 22
Text: c3R c3.SHTEL_CZSYSTEM%TYPE;Error: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
       ORA-06553: PLS-488: invalid variable declaration:  object 'NUMBER' must be a type or subtype
Line: 25
Text: SELECT COUNT(*) T FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1Error: PL/SQL: SQL Statement ignored
Line: 25
Text: SELECT COUNT(*) T FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1Error: PL/SQL: ORA-00984: column not allowed here
Line: 27
Text: INSERT INTO PROBAGGREGATION(CZPRODUCT,CZPROBLEMTYPE,CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,T);Error: PL/SQL: SQL Statement ignored
Line: 27
Text: INSERT INTO PROBAGGREGATION(CZPRODUCT,CZPROBLEMTYPE,CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,T);Error: Hint: Value assigned to 'num' never used in 'Pro'
Line: 5
Text: num:=0;

Answer 5

应该是c1R c1%ROWTYPE; 下同这三个cursor为什么不写成一个sql呢

Answer 6

to shiyiwan : 我是想弄成三重嵌套现在的错:Compilation errors for PROCEDURE SHSMUSER.PROError: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
       ORA-06553: PLS-488: invalid variable declaration:  object 'NUMBER' must be a type or subtype
Line: 25
Text: SELECT COUNT(*) FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1 WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;Error: PL/SQL: SQL Statement ignored
Line: 25
Text: SELECT COUNT(*) FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1 WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;Error: PL/SQL: ORA-00984: column not allowed here
Line: 26
Text: INSERT INTO PROBAGGREGATION(CZPRODUCT,CZPROBLEMTYPE,CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,T);Error: PL/SQL: SQL Statement ignored
Line: 26
Text: INSERT INTO PROBAGGREGATION(CZPRODUCT,CZPROBLEMTYPE,CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,T);Error: Hint: Value assigned to 'num' never used in 'Pro'
Line: 5
Text: num:=0;

Answer 7

num number; 后面加一行
vt number;SELECT COUNT(*) T FROM PROBSUMMARYM1...改成select count(*) into vt from ...INSERT INTO ...VALUES(c1R,c2R,c3R,vt); 

Answer 8

噢，你可以用num直接代替，不必多声明一个vt

Answer 9

to shiyiwan:SELECT COUNT(*) INTO num FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1 WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;还是错啊Compilation errors for PROCEDURE SHSMUSER.PROError: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
       ORA-06553: PLS-488: invalid variable declaration:  object 'NUMBER' must be a type or subtype
Line: 25
Text: SELECT COUNT(*) INTO num FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1 WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;Error: PL/SQL: SQL Statement ignored
Line: 25
Text: SELECT COUNT(*) INTO num FROM PROBSUMMARYM1,SHTELEPRODUCTM1,SHTELEPROBLEMTYPEM1,SHTELESYSTEMM1 WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;Error: PLS-00382: expression is of wrong type
Line: 26
Text: INSERT INTO PROBAGGREGATION(SHTEL_CZPRODUCT,SHTEL_CZPROBLEMTYPE,SHTEL_CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,num);Error: PLS-00382: expression is of wrong type
Line: 26
Text: INSERT INTO PROBAGGREGATION(SHTEL_CZPRODUCT,SHTEL_CZPROBLEMTYPE,SHTEL_CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,num);Error: PLS-00382: expression is of wrong type
Line: 26
Text: INSERT INTO PROBAGGREGATION(SHTEL_CZPRODUCT,SHTEL_CZPROBLEMTYPE,SHTEL_CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,num);

Answer 10

num可能是关键字你换成别的变量名试试吧还有insert的数据类型对应

Answer 11

还有你的sql写的有问题
后面那些表根本一个都没用到为什么还要加在where后面呢
没有连接关系会报错的

Answer 12

WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R.SHTEL_CZPRODUCT AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R.SHTEL_CZPROBLEMTYPE 
还有这里，c1r,c2r代表一条记录，不是一个字段。
下同

Answer 13

CREATE OR REPLACE PROCEDURE Pro
IS 
  vt long;
BEGIN 
    vt:=0; 
    delete from PROBAGGREGATION; 
    commit; 
    declare cursor c1 is 
        select SHTEL_CZPRODUCT from SHTELEPRODUCTM1; 
        c1R SHTELEPRODUCTM1.SHTEL_CZPRODUCT%TYPE; 
  begin
    open c1;
    loop
        fetch c1 into c1R;
        exit when c1%notfound;
        
        declare cursor c2 is 
        select SHTEL_CZPROBLEMTYPE from SHTELEPROBLEMTYPEM1; 
        c2R SHTELEPROBLEMTYPEM1.SHTEL_CZPROBLEMTYPE%TYPE; 
        begin
          open c2; 
          loop
              fetch c2 into c2R; 
              exit when c2%notfound;
              declare cursor c3 is 
              select SHTEL_CZSYSTEM from SHTELESYSTEMM1; 
              c3R SHTELESYSTEMM1.SHTEL_CZSYSTEM%TYPE; 
              begin
                  open c3; 
                  loop
                     fetch c3 into c3R;
                     exit when c3%notfound; 
                     SELECT COUNT(1) into vt 
                     FROM PROBSUMMARYM1;
                     WHERE PROBSUMMARYM1.SHTEL_CZPRODUCT = c1R AND PROBSUMMARYM1.SHTEL_CZPROBLEMTYPE=c2R AND PROBSUMMARYM1.SHTEL_CZSYSTEM=c3R;
                     INSERT INTO PROBAGGREGATION(SHTEL_CZPRODUCT,SHTEL_CZPROBLEMTYPE,SHTEL_CZSYSTEM,TOTAL) VALUES(c1R,c2R,c3R,vt);
                  end loop;
                  close c3;
                end;  
            end loop;
            close c2; 
          end; 
    end loop;
    close c1; 
    commit; 
  end; 
END Pro; Compilation errors for PROCEDURE SHSMUSER.PROError: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
       ORA-06553: PLS-488: invalid variable declaration:  object 'NUMBER' must be a type or subtype
Line: 34
Text: FROM PROBSUMMARYM1;Error: PL/SQL: SQL Statement ignored
Line: 33
Text: SELECT COUNT(1) into vt我查了一下,可能是因为PROBSUMMARYM1有个字段名为NUMBER,但这个字段又不能改,求助啊!