<< 프로시저 작성 문법
CREATE OR REPLACE PROCEDURE 이름
[(Parameter....)]
IS
pl/sql_block;
<<실행방법
SQL*Plus에서 명령프롬프트에서 argument값과 함께 프로시저 이름을
입력하면 됩니다.
SQL> EXECUTE raising_salary(20000);
<< 예 >>
CREATE OR REPLACE PROCEDURE GBN_INSERT
IS
V_LCODE ETC_AGENCY.LCODE%TYPE;
V_MCODE ETC_AGENCY.MCODE%TYPE;
V_SCODE ETC_AGENCY.SCODE%TYPE;
V_GBN ETC_AGENCY.GBN%TYPE;
data_count INTEGER;
CURSOR SELECT_GBN_INSERT IS
SELECT LCODE, MCODE, SCODE, GBN
FROM CD_SCODE;
BEGIN
OPEN SELECT_GBN_INSERT;
SELECT COUNT(*) INTO data_count FROM CD_SCODE;
FOR i IN 1..data_count LOOP
FETCH SELECT_GBN_INSERT INTO V_LCODE, V_MCODE, V_SCODE, V_GBN;
UPDATE ETC_AGENCY
SET GBN = V_GBN
WHERE LCODE = V_LCODE
AND MCODE = V_MCODE
AND SCODE = V_SCODE;
COMMIT;
END LOOP;
COMMIT;
CLOSE SELECT_GBN_INSERT;
END GBN_INSERT;
위의 예는 데이터를 여러행에 걸쳐 업데이트 하는데 update문에 조건절이 계속
바뀔 경우에 매번 빠꿔 주기 싫은 경우에 간단히 제작하여 쓰면 편합니다
<< 파라메터 명 선언 문법
Parameter명 [ IN | OUT | IN OUT ] data type
[ { := | DEFAULT } 식 ]
IN 실행환경에서 서브 프로그램으로 값을 전달.
OUT 프로시저에서 실행환경으로 값을 전달.
IN OUT 실행환경에서 프로시저로 값을 전달하고 프로시저에서 실행환경으로 변경된 값을 전달할 수 있다.
<< 주요 특징
PL/SQL블럭들로 구성된다.
어떠한 파라메터도 사용 가능합니다.
함수, 프로시져등을 호출할 수 있다.
함수에서는 리턴값을 필요로 하지만 프로시저에서는 가질 수 없습니다.
Query문에서 독립적으로 사용될 수 없다.
local 변수는 Declare section( IS 와 BEGIN 사이) 에 합니다.
SELECT, UPDATE, INSERT 등 프로시저 내의 PL/SQL블럭은 어떤 DML문이든 포함이 될 수 있으나 DDL문은 사용할 수 없다.
<< 프로시져(Procedure)의 구성
프로시져의 주요부는 선언, 실행, 예외 섹션을 가진 PL/SQL블럭이다.
CREATE OR REPLACE PROCEDURE procedurename AS
Declarative section
Executable section
EXCEPTION
Exception section
END [procedurename];
-----------------------------------------------------------
아래와 같이 change_salary procedure를 생성하고 실행해 보시오.
SQL> conn testxx/testxx
SQL> EDIT p1
CREATE OR REPLACE PROCEDURE change_salary
(p_id IN NUMBER, p_new_sal IN NUMBER)
IS
BEGIN
UPDATE s_emp
SET salary = p_new_sal
WHERE id = p_id;
COMMIT;
END change_salary ;
/
SQL> @p1
(* Error 발생 시 SHOW ERRORS 명령으로 확인하여 수정한 후 다시 생성)
SQL> SELECT id, salary FROM s_emp WHERE id = 1;
(* 현재의 salary 확인)
SQL> EXEC change_salary(1, 5000);
SQL> SELECT id, salary FROM s_emp WHERE id = 1;
(* 변경된 salary 확인)
FGA 예제
begin
DBMS_FGA.ADD_POLICY (
object_schema => 'scott',
object_name => 'emp',
policy_name => 'mypolicy1',
audit_condition => 'sal < 100',
audit_column => 'comm,sal',
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'INSERT, UPDATE',
audit_trail => DBMS_FGA.XML + DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
end;
/
<DROP_POLICY>
DBMS_FGA.DROP_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );
==================================================
** 상급admin이 delete 할때 마다 audit 남김 **
begin
DBMS_FGA.ADD_POLICY (
object_schema => 'wed',
object_name => 'customer',
policy_name => 'del_admin1',
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'DELETE',
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
end;
/
** 하급admin이 view select 할때 마다 audit 로
test 테이블에 scn 100 동안 10회 이상 조회할 경우 insert 해서 남김(빈번한 sql) **
begin
DBMS_FGA.ADD_POLICY (
object_schema => 'subadmin',
object_name => 'cust_view',
policy_name => 'sel_subadmin',
handler_schema => 'sys',
handler_module => 'test',
enable => TRUE,
statement_types => 'SELECT',
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
end;
/
==> DBMS_FGA.EXTENDED 는 FGA가 바인드 변수를 상시 캡쳐하도록 할수 있다
begin
DBMS_FGA.DROP_POLICY (
object_schema => 'wed',
object_name => 'customer',
policy_name => 'del_admin');
end;
/
begin
DBMS_FGA.DROP_POLICY (
object_schema => 'subadmin',
object_name => 'cust_view',
policy_name => 'sel_subadmin');
end;
/
=====검색하기
**fgalog.sql**
select sessionid,timestamp#,sqltext
from sys.fga_log$
/
**policy.sql**
select object_schema,object_name,policy_name
from dba_audit_policies
/
**fga_trail.sql**
select to_char(timestamp, 'RRRR-MM-DD HH24:MI:SS') as timestamp, db_user, policy_name, sql_bind,sql_text
from dba_fga_audit_trail;
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
** 빈번하게 발생하는 sql 확인 **
after insert on fga_log$
for each row
declare
begin
select sessionid,DBUID,count(*)
from sys.fga_log$
where scn between timestamp_to_scn(sysdate)-300 and timestamp_to_scn(sysdate)
and obj$name='CUSTOMER'
group by sessionid, DBUID
having count(*)>9;
** 프로시져 생성 **
create or replace procedure test
as
cursor c1 is
select sessionid,DBUID
from sys.fga_log$
where scn between timestamp_to_scn(sysdate)-10000 and timestamp_to_scn(sysdate)
and obj$name='CUST_VIEW'
group by sessionid, DBUID
having count(*)>9;
sql_stmt VARCHAR2(200);
begin
dbms_output.enable('10000000');
dbms_output.put_line('-----------------------------');
dbms_output.put_line('-------------성공------------');
dbms_output.put_line('-----------------------------');
for rec in c1 loop
dbms_output.put_line('----' || rec.sessionid || '---'|| rec.DBUID ||'--');
sql_stmt := 'insert into fre_trail values (:1,:2)';
execute immediate sql_stmt using rec.sessionid, rec.DBUID;
end loop;
dbms_output.put_line('-----------------------------');
end;
** 임시테이블 생성 **
create table fre_trail
(sessionid number,
DBUID varchar2(30));
'Oracle' 카테고리의 다른 글
[Oracle] 윈도우에서 오라클 한글깨짐 문제해결방법 (0) | 2013.03.27 |
---|---|
[Oracle] 캐릭터셋 변경 (0) | 2013.03.24 |
[Oracle] 이클립스 오라클 연동하기 (0) | 2013.03.22 |