create procedure Test is num number :=0; page varchar2(100):=''; begin select Count(*) into num from apply_leave; page := ' insert into temp(n)values('||to_char(s)||') '; execute immediate page; commit; Exception when others then rollback; raise; end Test;
一个例子: create or replace procedure SP_LoadData IS v_xmmc t_sjzd.xmmc%TYPE; CURSOR c_LoadTable IS SELECT DISTINCT infor FROM "CODE_L52" where trim(infor) is not null; CURSOR c_exist IS SELECT * FROM t_sjzd WHERE xmmc=''||v_xmmc||''; v_nr t_sjzd.xmnr%TYPE; v_sql STRING(200); v_count NUMBER; v_i NUMBER:=1; BEGIN v_xmmc:='ÏÖ´ÓÊÂרҵ'; SELECT MAX(xmbh) INTO v_count FROM t_sjzd; v_count:=v_count+1; OPEN c_exist; IF c_exist%FOUND THEN v_sql:='delete from t_sjzd where xmmc='''||v_xmmc||''''; EXECUTE IMMEDIATE v_sql; END IF; CLOSE c_exist;
OPEN c_LoadTable; FETCH c_LoadTable INTO v_nr; WHILE c_LoadTable%FOUND LOOP v_sql:='insert into t_sjzd(xmbh,xmmc,xmnr,sfkj,yxj)'|| 'values'|| '('||v_count||','''||v_xmmc||''','''||v_nr||''',''Y'','||v_i||')'; EXECUTE IMMEDIATE v_sql; v_count:=v_count+1; v_i:=v_i+1; FETCH c_LoadTable INTO v_nr; END LOOP; CLOSE c_LoadTable; COMMIT; end SP_LoadData; /
is
num number :=0;
page varchar2(100):='';
begin
select Count(*) into num from apply_leave;
page := ' insert into temp(n)values('||to_char(s)||') ';
execute immediate page;
commit;
Exception
when others then
rollback;
raise;
end Test;
create or replace procedure SP_LoadData IS
v_xmmc t_sjzd.xmmc%TYPE;
CURSOR c_LoadTable IS SELECT DISTINCT infor FROM "CODE_L52" where trim(infor) is not null;
CURSOR c_exist IS SELECT * FROM t_sjzd WHERE xmmc=''||v_xmmc||'';
v_nr t_sjzd.xmnr%TYPE;
v_sql STRING(200);
v_count NUMBER;
v_i NUMBER:=1;
BEGIN
v_xmmc:='ÏÖ´ÓÊÂרҵ';
SELECT MAX(xmbh) INTO v_count FROM t_sjzd;
v_count:=v_count+1;
OPEN c_exist;
IF c_exist%FOUND THEN
v_sql:='delete from t_sjzd where xmmc='''||v_xmmc||'''';
EXECUTE IMMEDIATE v_sql;
END IF;
CLOSE c_exist;
OPEN c_LoadTable;
FETCH c_LoadTable INTO v_nr;
WHILE c_LoadTable%FOUND LOOP
v_sql:='insert into t_sjzd(xmbh,xmmc,xmnr,sfkj,yxj)'||
'values'||
'('||v_count||','''||v_xmmc||''','''||v_nr||''',''Y'','||v_i||')';
EXECUTE IMMEDIATE v_sql;
v_count:=v_count+1;
v_i:=v_i+1;
FETCH c_LoadTable INTO v_nr;
END LOOP;
CLOSE c_LoadTable;
COMMIT;
end SP_LoadData;
/