PL/SQL自动生成代码

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;