oracle預存程序代碼日誌記錄

標籤：color   cal   ace   upper   _id   cut   varchar2   creat   span   

prompt 建立函數 f_getprocname
CREATEORREPLACEFUNCTION f_getprocname RETURNVARCHAR2
--擷取函數或者預存程序自身的名稱及調用者
IS
l_owner VARCHAR2(30);
l_name VARCHAR2(30);
l_lineno NUMBER;
l_type VARCHAR2(30);
BEGIN
OWA_UTIL.who_called_me(l_owner, l_name, l_lineno, l_type);
RETURN l_owner ||‘.‘|| l_name;
END;
/

prompt 正在執行建立表 T_PROC_LOGS
declare
????v_count number:=0;
????v_tb_name varchar2(1000):=‘T_PROC_LOGS‘;
begin
????SELECTcount(*)INTO v_count FROM USER_OBJECTS WHEREOBJECT_NAME=UPPER(v_tb_name);
????if v_count >0then
????????executeimmediate‘drop table ‘||UPPER(v_tb_name);
????endif;
end;
/
createtable T_PROC_LOGS
(
LOG_ID NUMBER(10)default0notnull,
PROC_MC VARCHAR2(100)default‘ ‘notnull,
TITLE VARCHAR2(4000)default‘ ‘notnull,
CONTENT VARCHAR2(4000)default‘ ‘notnull,
LOG_TYPE VARCHAR2(10)default‘ ‘notnull,
LOG_TIME DATE
);

prompt 正在執行建立序列 seq_log_id
declare
????v_count number;
????v_sequence_name varchar2(1000):=‘seq_log_id‘;
begin
selectcount(*)into v_count from user_sequences a where a.sequence_name =upper(v_sequence_name);
if(v_count =0)then
executeimmediate‘create sequence ‘|| v_sequence_name ||
‘ increment by 1 start with 1‘||
‘ maxvalue 2100000000 minvalue 1‘||
‘ cycle nocache noorder‘;
endif;
end;
/

?

?

?

?

?

?

?

oracle預存程序代碼日誌記錄