下面是我以前寫的過程里面的一段: str1:=''; str2:=''; OPEN c_cur FOR SELECT tname FROM t_sjbgl; LOOP FETCH c_cur INTO v_tname; EXIT WHEN c_cur%NOTFOUND; IF v_tname='T_DYJCJL_SAME' THEN str1:='delete from '||v_tname||' where dybh='|| '(select dybh from T_DYJBXXB_SAME where ygid='''||v_ygid||''')'; ELSIF v_tname='T_DNZWLL_SAME' THEN str1:='delete from '||v_tname||' where dybh='|| '(select dybh from T_DYJBXXB_SAME where ygid='''||v_ygid||''')'; ELSIF v_tname='T_HTFJ_SAME' THEN str1:='delete from '||v_tname||' where htbh='|| '(select htbh from T_YGHT_SAME where ygid='''||v_ygid||''')'; ELSIF v_tname='T_GZJSXGZXM_SAME' THEN str1:='delete from '||v_tname||' where bh='|| '(select bh from T_GZJSXB_SAME where ygid='''||v_ygid||''')'; ELSE str1:='delete from '||v_tname||' where ygid='''||v_ygid||''''; END IF; EXECUTE IMMEDIATE str1; END LOOP;
create or replace procedure aa as talename varchar2(100); tempid number; str varchar2(50); begin tablename :='table1'; str:='select id from '||tablename; execute immediate str into tempid; insert into table2(vv) values(tempid); exception when others then dbms_output.put_line('ddd'); commit; end; /
str1:='';
str2:='';
OPEN c_cur FOR SELECT tname FROM t_sjbgl;
LOOP
FETCH c_cur INTO v_tname;
EXIT WHEN c_cur%NOTFOUND;
IF v_tname='T_DYJCJL_SAME' THEN
str1:='delete from '||v_tname||' where dybh='||
'(select dybh from T_DYJBXXB_SAME where ygid='''||v_ygid||''')';
ELSIF v_tname='T_DNZWLL_SAME' THEN
str1:='delete from '||v_tname||' where dybh='||
'(select dybh from T_DYJBXXB_SAME where ygid='''||v_ygid||''')';
ELSIF v_tname='T_HTFJ_SAME' THEN
str1:='delete from '||v_tname||' where htbh='||
'(select htbh from T_YGHT_SAME where ygid='''||v_ygid||''')';
ELSIF v_tname='T_GZJSXGZXM_SAME' THEN
str1:='delete from '||v_tname||' where bh='||
'(select bh from T_GZJSXB_SAME where ygid='''||v_ygid||''')';
ELSE
str1:='delete from '||v_tname||' where ygid='''||v_ygid||'''';
END IF;
EXECUTE IMMEDIATE str1;
END LOOP;
table_name := 'T' || LTRIM(TO_CHAR(my_bbh, v_format ));
sqlcmd := 'SELECT COUNT(*) FROM tabs WHERE TABLE_NAME=:TBNAME';
DBMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ) ;
DBMS_SQL.BIND_VARIABLE( nSQL, ':TBNAME', table_name ) ;
DBMS_SQL.DEFINE_COLUMN( nSQL, 1, my_count ) ;
ret := DBMS_SQL.EXECUTE( nSQL )
as
talename varchar2(100);
tempid number;
str varchar2(50);
begin
tablename :='table1';
str:='select id from '||tablename;
execute immediate str into tempid;
insert into table2(vv) values(tempid);
exception
when others then
dbms_output.put_line('ddd');
commit;
end;
/