我的目的是从db_receipts_class取出表名和字段名,根据表名和字段名查询这个表中的单据类别,单位,年月日相同的单据号最大的,查找DB_CODE_BUILD(单据生成器表)表中cur_no值,如果小于单据最大值,用最大值更新之!报错,是warning 报什么procedure编辑错误!
各位老大,指点一下迷津啊,不胜感激!create or replace procedure up_update_code_curno is v_tableid db_receipts_class.TABLE_ID%TYPE;
v_columnsid db_receipts_class.COLUMNS_ID%TYPE;
v_strsql varchar2(1000);
v_ch_value varchar2(10);
v_ch_flag varchar2(20);
v_count number(5)
type curtyp is ref cursor;
cursor_a curtyp; cursor cursor_table IS
select TABLE_ID, COLUMNS_ID
from db_receipts_class
where (COLUMNS_ID is not null)
and (table_id is not null);begin
open cursor_table;
loop
fetch cursor_table
into v_tableid, v_columnsid;
EXIT WHEN cursor_table % NOTFOUND;
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(' ||
v_columnsid || ', 1, 13) as DJ_ID,substr(' || v_columnsid ||
', 14, 5) as dj_no from ' || v_tableid ||
') T where u.ch_flag = T.dj_id group by ch_flag)';
open cursor_a FOR v_strsql;
LOOP
FETCH cursor_a
INTO v_ch_value, v_ch_flag;
EXIT WHEN cursor_a%NOTFOUND;
SELECT count(rownum)
INTO v_count
FROM db_code_build aa
WHERE v_chvalue>aa.cur_no and aa.receipts_id||aa.station_id||aa.cur_month = v_ch_flag; if v_count=1 then
updatable db_code_build set cur_no=v_chvalue where receipts_id||station_id||cur_month = v_ch_flag;
end if;
END LOOP;
END LOOP; CLOSE cursor_table; COMMIT;end up_update_code_curno;
v_count number(5)
type curtyp is ref cursor;====>v_count number(5);
type curtyp is ref cursor;还有,把错误信息贴出来。
updatable---->update
"show error命令看错误信息。"怎么用啊?各位老大
执行完你上面的create procedure的代码,接着执行show error;就行了。