CREATE OR REPLACE PROCEDURE printcode (
p_view_name VARCHAR2,
p_table_name VARCHAR2)
IS
v_begin_str varchar2(4000);
v_column_name varchar2(30);
v_nullable varchar2(1);
v_insert1 varchar2(4000);
v_insert2 varchar2(4000);
v_vstr varchar2(4000);
v_newstr varchar2(4000);
v_variables varchar2(4000);
v_select varchar2(4000);
CURSOR column_cur IS
select column_name,NULLABLE
from user_tab_columns
where table_name=upper(p_table_name);
BEGIN
v_begin_str:='CREATE OR REPLACE TRIGGER TRG_DML_ON_'||upper(p_view_name)||chr(10)||' INSTEAD OF INSERT OR UPDATE OR DELETE ON '||upper(p_view_name)||chr(10)
||'DECLARE'||chr(10);
v_select:='SELECT SEQ_'||p_table_name||'.NEXTVAL INTO v_ID FROM DUAL;'||chr(10);
v_insert1:='INSERT INTO '||p_table_name||'(';
v_insert2:='';
v_vstr:='';
v_newstr:='';
v_variables:='';
DBMS_OUTPUT.ENABLE(1000000);
OPEN column_cur;
LOOP
FETCH column_cur INTO v_column_name, v_nullable;
EXIT WHEN column_cur%NOTFOUND;
IF v_nullable = 'N' and v_column_name<> 'ID'
THEN
v_insert1:=v_insert1||v_column_name||',';
v_vstr:=v_vstr||'v_'||v_column_name||',';
v_begin_str:=v_begin_str||'v_'||v_column_name||' NUMBER(10);'||chr(10);
v_variables:=v_variables||'v_'||v_column_name||':='||chr(10);
ELSE
v_insert2:=v_insert2||v_column_name||',';
if v_column_name<>'ID'
then
v_newstr:=v_newstr||':new.'||v_column_name||',';
else
v_newstr:=v_newstr||'v_'||v_column_name||',';
v_begin_str:=v_begin_str||'v_'||v_column_name||' NUMBER(10);'||chr(10);
end if;
END IF;
END LOOP;
v_insert2:=substr(v_insert2,0,length(v_insert2)-1);
v_insert2:=v_insert2||')'||chr(10)||'values(';
v_newstr:=substr(v_newstr,0,length(v_newstr)-1);
v_begin_str:=v_begin_str||'BEGIN'||chr(10)||' IF INSERTING THEN '||chr(10);
CLOSE column_cur;
dbms_output.put_line(v_begin_str||v_select||v_variables||v_insert1||v_insert2||v_vstr||v_newstr||');');
END;CREATE OR REPLACE TRIGGER TRG_S_DTFW_QPPZ
BEFORE INSERT ON S_DTFW_QPPZ
FOR EACH ROW
when (NEW.ID IS NULL)
BEGIN
SELECT SEQ_S_DTFW_QPPZ_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
PL/SQL自动生成代码
最新推荐文章于 2024-01-15 19:54:00 发布