创建成功,运行时提示错误,大家帮我看看怎么回事,谢谢SQL> create or replace procedure up_update_code_curno is
2
3 v_tableid varchar2(30);
4 v_columnsid varchar2(30);
5 v_strsql varchar2(1000);
6 v_strsql2 varchar2(1000);
7 v_SQLString varchar2(1000);
8 v_ch_value varchar2(10);
9 v_ch_flag varchar2(20);
10 v_count number(5);
11 v_receipsid varchar2(5);
12 v_stationid varchar2(8);
13 v_month varchar2(8);
14 type curtyp is ref cursor;
15 cursor_a curtyp;
16
17 cursor cursor_table IS
18 select TABLE_ID, COLUMNS_ID
19 from db_receipts_class
20 where (COLUMNS_ID is not null)
21 and (table_id is not null);
22
23 begin
24 open cursor_table;
25 loop
26 fetch cursor_table
27 into v_tableid, v_columnsid;
28 EXIT WHEN cursor_table % NOTFOUND;
29 v_strsql2 := 'select substr(ee.' || v_columnsid || ',1,2), substr(ee.' ||
30 v_columnsid || ',3,5),substr(ee.' || v_columnsid ||
31 ',8,6) from db_code_build dd ,' || v_tableid ||
32 ' ee where dd.cur_month< substr(ee.' || v_columnsid ||
33 ',8,6) and dd.receipts_id=substr(ee.' || v_columnsid ||
34 ',1,2) and dd.station_id=substr(ee.' || v_columnsid ||
35 ',3,5)';
36
37 open cursor_a for v_strsql2;
38 loop
39 FETCH cursor_a
40 INTO v_receipsid, v_stationid, v_month;
41 EXIT WHEN cursor_a % NOTFOUND;
42
43 select count(rownum)
44 into v_count
45 from db_code_build ff
46 where ff.cur_month < v_month
47 and ff.station_id = v_stationid
48 and ff.receipts_id = v_receipsid;
49 if v_count = 1 then
50
51 v_SQLString := 'INSERT INTO db_code_build VALUES (' || v_stationid || ', ' ||
52 v_receipsid || ',1,' || v_month || ')';
53 EXECUTE IMMEDIATE v_SQLString;
54
55
56
57 commit;
58 end if;
59 end loop;
60 v_strsql := '(select max(dj_no) as ch_value, ch_flag from (select RECEIPTS_ID || station_id ||cur_month as ch_flag from DB_CODE_BUILD) u,(select substr(' ||
61 v_columnsid || ', 1, 13) as DJ_ID,substr(' || v_columnsid ||
62 ', 14, 5) as dj_no from ' || v_tableid ||
63 ') T where u.ch_flag = T.dj_id group by ch_flag)';
64
65 open cursor_a FOR v_strsql;
66
67 LOOP
68 FETCH cursor_a
69 INTO v_ch_value, v_ch_flag;
70 EXIT WHEN cursor_a%NOTFOUND;
71
72 SELECT count(rownum)
73 INTO v_count
74 FROM db_code_build aa
75 WHERE v_ch_value > aa.cur_no
76 and aa.receipts_id || aa.station_id || aa.cur_month = v_ch_flag;
77
78 if v_count = 1 then
79
80 update db_code_build
81 set cur_no = v_ch_value
82 where receipts_id || station_id || cur_month = v_ch_flag;
83 end if;
84
85 END LOOP;
86 END LOOP;
87
88 CLOSE cursor_table;
89
90 COMMIT;
91
92 end up_update_code_curno;
93 /Procedure createdSQL> exec up_update_code_curnobegin up_update_code_curno; end;ORA-00984: 列在此处不允许
ORA-06512: 在"KMIS.UP_UPDATE_CODE_CURNO", line 53
2
3 v_tableid varchar2(30);
4 v_columnsid varchar2(30);
5 v_strsql varchar2(1000);
6 v_strsql2 varchar2(1000);
7 v_SQLString varchar2(1000);
8 v_ch_value varchar2(10);
9 v_ch_flag varchar2(20);
10 v_count number(5);
11 v_receipsid varchar2(5);
12 v_stationid varchar2(8);
13 v_month varchar2(8);
14 type curtyp is ref cursor;
15 cursor_a curtyp;
16
17 cursor cursor_table IS
18 select TABLE_ID, COLUMNS_ID
19 from db_receipts_class
20 where (COLUMNS_ID is not null)
21 and (table_id is not null);
22
23 begin
24 open cursor_table;
25 loop
26 fetch cursor_table
27 into v_tableid, v_columnsid;
28 EXIT WHEN cursor_table % NOTFOUND;
29 v_strsql2 := 'select substr(ee.' || v_columnsid || ',1,2), substr(ee.' ||
30 v_columnsid || ',3,5),substr(ee.' || v_columnsid ||
31 ',8,6) from db_code_build dd ,' || v_tableid ||
32 ' ee where dd.cur_month< substr(ee.' || v_columnsid ||
33 ',8,6) and dd.receipts_id=substr(ee.' || v_columnsid ||
34 ',1,2) and dd.station_id=substr(ee.' || v_columnsid ||
35 ',3,5)';
36
37 open cursor_a for v_strsql2;
38 loop
39 FETCH cursor_a
40 INTO v_receipsid, v_stationid, v_month;
41 EXIT WHEN cursor_a % NOTFOUND;
42
43 select count(rownum)
44 into v_count
45 from db_code_build ff
46 where ff.cur_month < v_month
47 and ff.station_id = v_stationid
48 and ff.receipts_id = v_receipsid;
49 if v_count = 1 then
50
51 v_SQLString := 'INSERT INTO db_code_build VALUES (' || v_stationid || ', ' ||
52 v_receipsid || ',1,' || v_month || ')';
53 EXECUTE IMMEDIATE v_SQLString;
54
55
56
57 commit;
58 end if;
59 end loop;
60 v_strsql := '(select max(dj_no) as ch_value, ch_flag from (select RECEIPTS_ID || station_id ||cur_month as ch_flag from DB_CODE_BUILD) u,(select substr(' ||
61 v_columnsid || ', 1, 13) as DJ_ID,substr(' || v_columnsid ||
62 ', 14, 5) as dj_no from ' || v_tableid ||
63 ') T where u.ch_flag = T.dj_id group by ch_flag)';
64
65 open cursor_a FOR v_strsql;
66
67 LOOP
68 FETCH cursor_a
69 INTO v_ch_value, v_ch_flag;
70 EXIT WHEN cursor_a%NOTFOUND;
71
72 SELECT count(rownum)
73 INTO v_count
74 FROM db_code_build aa
75 WHERE v_ch_value > aa.cur_no
76 and aa.receipts_id || aa.station_id || aa.cur_month = v_ch_flag;
77
78 if v_count = 1 then
79
80 update db_code_build
81 set cur_no = v_ch_value
82 where receipts_id || station_id || cur_month = v_ch_flag;
83 end if;
84
85 END LOOP;
86 END LOOP;
87
88 CLOSE cursor_table;
89
90 COMMIT;
91
92 end up_update_code_curno;
93 /Procedure createdSQL> exec up_update_code_curnobegin up_update_code_curno; end;ORA-00984: 列在此处不允许
ORA-06512: 在"KMIS.UP_UPDATE_CODE_CURNO", line 53
提示违反唯一约束条件53 INSERT INTO db_code_build
54 (STATION_ID, RECEIPTS_ID, CUR_NO,
55 CUR_MONTH
56 )
57 VALUES (v_stationid, v_receipsid, 1,
58 v_month
59 );
STATION_ID, CUR_MONTH,char型 RECEIPTS_ID,varchar2型
CUR_NO,数字型
v_SQLString := 'INSERT INTO db_code_build VALUES (:V1,:V2,1,:V3)';
EXECUTE IMMEDIATE v_SQLString USING v_stationid,v_receipsid,v_month ;
还是不对啊,违反唯一约束条件
是因为有两个键是主键,不可以插入了,update就可以了