我的一个存储过程:
CREATE OR REPLACE procedure PRO_YSJKKM
(
istartdate in varchar2,
ienddate in varchar2)
is
CURSOR c_ysjkkm is
select
jbdate,
sum(p_seat+p_bed+p_cost+p_air+p_package+p_paper) tkprice1,
sum(p_paper) paper,
sum(p_seat+p_bed+p_cost+p_air+p_package) tkprice2
from sale
where jbdate>=istartdate and jbdate<=ienddate and safe<>'作废'
group by jbdate;
begin
open c_ysjkkm;
for cur_ysjkkm in c_ysjkkm
loop
insert into temp_table(a1,a2,b2,c2)
values(cur_ysjkkm.jbdate,cur_ysjkkm.tkprice1,cur_ysjkkm.paper,cur_ysjkkm.tkprice2);
end loop;
EXCEPTION
WHEN OTHERS THEN
NULL;
close c_ysjkkm;
end pro_ysjkkm;
但是我在执行后,老是提示“自变量数量或类型错误”!
我在delphi中调用是用storedporc控件。
CREATE OR REPLACE procedure PRO_YSJKKM
(
istartdate in varchar2,
ienddate in varchar2)
is
CURSOR c_ysjkkm is
select
jbdate,
sum(p_seat+p_bed+p_cost+p_air+p_package+p_paper) tkprice1,
sum(p_paper) paper,
sum(p_seat+p_bed+p_cost+p_air+p_package) tkprice2
from sale
where jbdate>=istartdate and jbdate<=ienddate and safe<>'作废'
group by jbdate;
begin
open c_ysjkkm;
for cur_ysjkkm in c_ysjkkm
loop
insert into temp_table(a1,a2,b2,c2)
values(cur_ysjkkm.jbdate,cur_ysjkkm.tkprice1,cur_ysjkkm.paper,cur_ysjkkm.tkprice2);
end loop;
EXCEPTION
WHEN OTHERS THEN
NULL;
close c_ysjkkm;
end pro_ysjkkm;
但是我在执行后,老是提示“自变量数量或类型错误”!
我在delphi中调用是用storedporc控件。
create or replace procedure SP_CLEAR(V_TABLE IN STRING) IS
/*---------------------------------------------------------------
--Function:delete all records of tables in v_table //
--Author:Liaozq //
--Date:2002/10/8 //
--Explain:(1)v_table must like this:table1,table2,table3,... //
(2)Before delete records from tables,recorder thess //
tables.
---------------------------------------------------------------*/
TYPE cur_type IS REF CURSOR;
c_tab cur_type;
v_str STRING(2000);
v_tab t_clear.wlbmc%TYPE;
v_sql STRING(3000);
begin
--Get table from v_table
--Reorder these tables
v_str:=upper(v_table);
v_str:=REPLACE(v_str,',',''',''');
v_str:=''''||v_str||'''';
v_sql:='SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN ('||v_str||') ORDER BY BZ DESC';
OPEN c_tab FOR v_sql;
LOOP
FETCH c_tab INTO v_tab;
EXIT WHEN c_tab%NOTFOUND;
v_sql:='delete from '||v_tab||'';
EXECUTE IMMEDIATE v_sql;
END LOOP;
COMMIT;
--deal with exception
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end SP_CLEAR;
/