Advanced Product Service 10g Develop PLP 02

7.10 Returining Clause

CREATE or replace PROCEDURE update_salary(emp_id NUMBER) IS

name emp.ename%TYPE;

new_sal number(10);

BEGIN

UPDATE emp

SET sal = sal * 1.1

WHERE empno = emp_id

RETURNING ename, sal INTO name, new_sal;

dbms_output.put_line ('ename: ' || name ||' sal: ' || new_sal);

END update_salary;

/

7.16 Bulk Binding (forall)

à For 문과 forall 문의 속도차이를 경험하자!! (set timing on 설정)

CREATE TABLE TEST_FORALL

(

PROD NUMBER (6),

PROD_DESC VARCHAR2 (30)

);

ALTER TABLE TEST_FORALL ADD PRIMARY KEY (PROD);

1. FOR LOOP 이용

BEGIN

FOR v_Count IN 1..5000 loop

INSERT INTO test_forall VALUES

(v_Count, 'Product Description #' || v_Count);

END LOOP;

END;

/

2. FORALL 이용

DECLARE

TYPE t_Prods IS TABLE OF test_forall.PROD%TYPE;

TYPE t_Descs IS TABLE OF test_forall.PROD_DESC%TYPE;

v_Prods t_Prods := t_Prods(1);

v_Descs t_Descs := t_Descs(1);

BEGIN

v_Prods.EXTEND(500000);

v_Descs.EXTEND(500000);

FOR v_Count IN 1..5000 LOOP

v_Prods(v_Count) := v_Count;

v_Descs(v_Count) := 'Product Description #' || v_Count;

END LOOP;

FORALL v_Count IN 1..5000

INSERT INTO test_forall VALUES

(v_Prods(v_Count), v_Descs(v_Count));

END;

/

7.18 Bulk collect into

CREATE or replace PROCEDURE get_departments(loc number ) is

TYPE dept_tabtype IS

TABLE OF hr.departments%ROWTYPE;

depts dept_tabtype;

BEGIN

SELECT * BULK COLLECT INTO depts

FROM hr.departments

WHERE location_id = loc;

FOR I IN 1 .. depts.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(depts(i).department_id ||' '||depts(i).department_name);

END LOOP;

END;

/

SQL> exec get_departments(1700);

7.18 Fetch using Bulk collect into

CREATE or replace PROCEDURE get_departments(loc NUMBER) IS

CURSOR dept_csr IS SELECT * FROM hr.departments

WHERE location_id = loc;

TYPE dept_tabtype IS TABLE OF dept_csr%ROWTYPE;

depts dept_tabtype;

BEGIN

OPEN dept_csr;

FETCH dept_csr BULK COLLECT INTO depts;

CLOSE dept_csr;

FOR I IN 1 .. depts.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(depts(i).department_id ||' '||depts(i).department_name);

END LOOP;

END;

/

SQL> exec get_departments(1700);

문제) 7-19 쪽 아래 limit 옵션을 보고 출력되는 row 를 3개로 제한해서 출력해보시요!

7.18 Fetch using Bulk collect into

declare

type Numlist is table of emp.empno%type;

type Bonlist is table of emp.sal%type;

id Numlist;

bl Numlist;

begin

select empno BULK COLLECT INTO id

from emp

where sal < 2000;

forall i in id.first..id.last

update emp set sal = sal * 1.1

where empno = id (i)

RETURNING sal BULK COLLECT INTO bl;

end;

/

7.21 Nocopy Hint

SQL> create or replace procedure delete_all_rows

(p_tab_name in varchar2, p_rows_del out number)

is

cursor_name integer;

begin

cursor_name := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_name,'DELETE FROM ' || p_tab_name, DBMS_SQL.NATIVE);

p_rows_del := DBMS_SQL.EXECUTE(cursor_name);

DBMS_SQL.CLOSE_CURSOR(cursor_name);

end;

/

프로시저가 생성되었습니다.

경 과: 00:00:00.32

SQL> variable deleted number

SQL> execute delete_all_rows('EMP', :deleted)

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:00.09

SQL> print deleted

DELETED

----------

14

SQL>

SQL>

SQL> create or replace procedure delete_all_rows

(p_tab_name in varchar2, p_rows_del out nocopy number)

is

cursor_name integer;

begin

cursor_name := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_name,'DELETE FROM ' || p_tab_name, DBMS_SQL.NATIVE);

p_rows_del := DBMS_SQL.EXECUTE(cursor_name);

DBMS_SQL.CLOSE_CURSOR(cursor_name);

end;

/

프로시저가 생성되었습니다.

경 과: 00:00:00.25

SQL>

SQL>

SQL>

SQL> variable deleted number

SQL> execute delete_all_rows('EMP', :deleted)

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:00.03

SQL>

SQL> rollback;

DECLARE

TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;

emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize

t1 NUMBER;

t2 NUMBER;

t3 NUMBER;

PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS

BEGIN

NULL;

END;

PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS

BEGIN

NULL;

END;

BEGIN

SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;

emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000

t1 := DBMS_UTILITY.GET_TIME;

do_nothing1(emp_tab); -- pass IN OUT parameter

t2 := DBMS_UTILITY.GET_TIME;

do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter

t3 := DBMS_UTILITY.GET_TIME;

dbms_output.put_line('Call Duration (secs/100)');

dbms_output.put_line('--------------------');

dbms_output.put_line('Just IN OUT: ' || TO_CHAR(t2 - t1));

dbms_output.put_line('With NOCOPY: ' || TO_CHAR(t3 - t2));

end;

/

DECLARE

TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;

emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize

t1 NUMBER;

t2 NUMBER;

t3 NUMBER;

PROCEDURE get_time (t OUT NUMBER) IS

BEGIN t := dbms_utility.get_time; END;

PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS

BEGIN NULL; END;

PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS

BEGIN NULL; END;

BEGIN

SELECT * INTO emp_tab(1) FROM employees WHERE employee_id = 100;

emp_tab.EXTEND(49999, 1); -- copy element 1 into 2..50000

get_time(t1);

do_nothing1(emp_tab); -- pass IN OUT parameter

get_time(t2);

do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter

get_time(t3);

dbms_output.put_line('Call Duration (secs)');

dbms_output.put_line('--------------------');

dbms_output.put_line('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));

dbms_output.put_line('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);

END;

/

8.9 Displaying Dependencies

SQL> @?\rdbms\admin\utldtree.sql

SQL> execute deptree_fill('TABLE','SCOTT','EMPLOYEES');

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:00.17

SQL>

SQL> select nested_level, type, name

from deptree

order by seq#;

8.13 Remote Dependencies

SQL> create view emp05

as

select empno, ename, sal, deptno

from emp;

SQL> CREATE OR REPLACE PROCEDURE pro1

( p_empno IN emp.empno%TYPE )

IS

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

BEGIN

SELECT empno, ename, sal

INTO v_empno, v_ename, v_sal

FROM emp

WHERE empno = p_empno ;

DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_empno );

DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_ename );

DBMS_OUTPUT.PUT_LINE( '사원급여 : ' || v_sal );

END;

/

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1');

---------- ------------------- -------

EMP05 VIEW VALID

PRO1 PROCEDURE VALID

SQL> drop table emp;

테이블이 삭제되었습니다.

SQL> select object_name,object_type, status

2 from user_objects

3 where object_name in ('EMP05','PRO1');

OBJECT_NAM OBJECT_TYPE STATUS

---------- ------------------- -------

EMP05 VIEW INVALID

PRO1 PROCEDURE INVALID

SQL> flashback table emp to before drop;

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1'); ß 확인해보시요 !!!

SQL> select count(*) from emp05;

COUNT(*)

----------

14

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1'); ß 확인해보시요 !!!

SQL> exec pro1(7788);

사원번호 : 7788

사원이름 : SCOTT

사원급여 : 3000

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1'); ß 확인해보시요 !!!

8.18 Remote Dependencies 두번째

SQL> create or replace procedure pro2

is

begin

pro1(7788);

end;

/

SQL> alter procedure pro1 compile;

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1','PRO2');

OBJECT_NAM OBJECT_TYPE STATUS

---------- ------------------- -------

EMP05 VIEW VALID

PRO1 PROCEDURE VALID

PRO2 PROCEDURE INVALID

SQL> alter procedure pro2 compile;

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1','PRO2');

OBJECT_NAM OBJECT_TYPE STATUS

---------- ------------------- -------

EMP05 VIEW VALID

PRO1 PROCEDURE VALID

PRO2 PROCEDURE VALID

SQL> alter procedure pro2 compile;

SQL> select object_name,object_type, status

from user_objects

where object_name in ('EMP05','PRO1','PRO2');

OBJECT_NAM OBJECT_TYPE STATUS

---------- ------------------- -------

EMP05 VIEW VALID

PRO1 PROCEDURE VALID

PRO2 PROCEDURE VALID

ß 문제) 만약 pro2 가 원격 서버에있었다면 pro1 을 컴파일했을 때 혹은 pro1의

참조 객체인 emp05 view 를 drop 했을때 status 가 어떻게 나오겠는가 ?

 9.13 Managing Bfile

-- bfile 컬럼을 추가한다.

SQL> alter table emp add emp_video bfile;

-- directory 를 생성한다.

SQL> create or replace directory log_file as 'c:\log_files';

-- directory 를 사용할 권한을 부여한다.

SQL> grant read on directory log_file to public;

-- bfilename 함수를 사용하여 테이블에 행을 삽입한다.

SQL> update emp
set emp_video=bfilename('LOG_FILE','KING.AVI')
where ename='KING';

SQL> create or replace procedure load_emp_bfile
( p_file_loc in varchar2 )
is
v_file bfile;
v_filename varchar2(16);
cursor emp_cursor is
select ename from emp
where deptno =10 for update ;
begin
for emp_record in emp_cursor loop
v_filename := emp_record.ename || '.bmp';
v_file := BFILENAME(p_file_loc, v_filename);
DBMS_LOB.FILEOPEN(v_file);
update emp
set emp_video=v_file
where current of emp_cursor;
DBMS_OUTPUT.PUT_LINE('LOAD FILE: '|| v_filename || 'size: '|| -
DBMS_LOB.GETLENGTH(v_file));
DBMS_LOB.FILECLOSE(v_file);
end loop;
end load_emp_bfile;
/

SQL> SET SERVEROUTPUT ON

SQL> execute load_emp_bfile('LOG_FILE');
LOAD FILE: KING.bmpsize: -71189
LOAD FILE: CLARK.bmpsize: -71189
LOAD FILE: MILLER.bmpsize: -71189

PL/SQL 처리가 정상적으로 완료되었습니다.

9.24 Add LOB columns

SQL> alter table emp add
(resume clob,
picture blob);

SQL> insert into emp(empno,ename, resume,picture)
values(1111,'aaa',empty_clob(), null);

SQL> update emp
set resume='date of birth: 8 February 1951',
picture=EMPTY_BLOB()
where empno=1111;

SQL> update emp
set resume='Date of Birth : 1 june 1956'
where empno=1111;

SQL> select empno, ename, resume
from emp
where empno =1111;

SQL> select dbms_lob.substr(resume,5,18)
from emp;

SQL> declare
text varchar2(4001);
begin
select resume into text
from emp
where empno=1111;
dbms_output.put_line('text is: '|| text);
end;
/
text is: Date of Birth : 1 june 1956

PL/SQL 처리가 정상적으로 완료되었습니다.

10.5 DML Trigger
SQL> create or replace trigger secure_emp
before insert on emp
begin
if (to_char(sysdate,'DY') in ('토','일')) OR
(to_char(sysdate,'HH24:MI')
not between '08:00' and '18:00')
then raise_application_error(-20500,'You may insert into emp table -
onlye during business hours.');
end if;
end;

/

SQL> insert into emp(empno ,ename, sal)
2 values(9391,'scott',3000);
insert into emp(empno ,ename, sal)
*
1행에 오류:
ORA-20500: You may insert into emp table -
onlye during business hours.
ORA-06512: "SCOTT.SECURE_EMP", 줄 5에서
ORA-04088: 트리거 'SCOTT.SECURE_EMP'의 수행시 오류

문제) 위의 트리거를 수정하여라 월요일 부터 금요일까지 저녘 6시에서 아침 9시
사이에는 데이타를 입력 못하겠금하는 트리거를 작성하여라

10.13 DML Trigger

Create or replace trigger secure_emp BEFORE

INSERT OR UPDATE OR DELETE ON emp

BEGIN

IF (TO_CHAR(SYSDATE,'DY') IN ('토','일')) OR (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18') THEN

IF DELETING THEN RAISE_APPLICATION_ERROR(-20502,'You may delete from EMPLOYEES table'|| 'only during business hours.');

ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(-20500,'You may insert into EMPLOYEES table'|| 'only during business hours.');

ELSIF UPDATING('SAL') THEN RAISE_APPLICATION_ERROR(-20503, 'You may '|| 'update SALARY only during business hours.');

ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||' update EMPLOYEES table only during'|| ' normal hours.');

END IF;

END IF;

END;

/

문제) 현재 시점으로 시간을 고쳐서 입력해보시요 !!

10.14 DML Row Trigger

SQL> create or replace trigger restrict_salary
before insert or update of sal on emp
for each row
begin
if not (:new.job in ('SALESMAN','ANALYST'))
and :new.sal > 15000
then
raise_application_error(-20202,'Employee cannot earn this amount');
end if;
end;
/

문제) 다음 부서번호가 10번 20번인 사원들만 월급을 5000 이상으로 수정할수있게

하고 나머지 부서번호는 수정못하게하라

10.15 After Trigger

SQL> create table audit_emp_values
( user_name varchar2(15),
timestamp date,
id number(4),
old_last_name varchar2(10),
new_last_name varchar2(10),
old_title varchar2(10),
new_title varchar2(10),
old_salary number(7,2),
new_salary number(7,2) );

SQL> create or replace trigger audit_emp_values
after delete or insert or update on emp
for each row
begin
insert into audit_emp_values
values(user,sysdate,:old.empno, :old.ename, :new.ename,
:old.job, :new.job, :old.sal, :new.sal );
end;
/

문제) 10-17 을 보시고 직업이 SALESMAN 인 사원의 월급을 갱신했을때만

audit_emp_values 에 값이 들어가겠금 위의 코드를 수정하시요 !!

10.15 After Trigger

SQL> alter table dept

add constraint dept_deptno_pk primary key(deptno);

테이블이 변경되었습니다.

SQL> alter table emp

add constraint emp_deptno_fk foreign key(deptno)

references dept(deptno);

테이블이 변경되었습니다.

SQL> update emp

set deptno=90

where empno=7788; ß 확인해보시요 !!

SQL> CREATE OR REPLACE TRIGGER emp_dept_fk_trg

AFTER UPDATE OF deptno

ON emp FOR EACH ROW

BEGIN

INSERT INTO dept VALUES(:new.deptno, NULL, NULL);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

NULL;

END;

/

SQL> update emp

set deptno=90

where empno=7788;

문제) delete from dept where deptno=10; ß 이 문장이 에러가
나지않고 수행될수 있도록 트리거를 작성하시요 !!

답)

CREATE OR REPLACE TRIGGER Dept_del_cascade

AFTER DELETE ON Dept

FOR EACH ROW

BEGIN

DELETE FROM Emp

WHERE Deptno = :old.Deptno;

END;

/

10.21 Instead of Trigger

SQL> create view emp_dept
as select empno, ename, sal, emp.deptno, dname
from emp, dept where emp.deptno=dept.deptno;

SQL>create or replace trigger emp_dept_insert
instead of insert on emp_dept
referencing new as n
for each row
declare
dept_cnt number;
begin
if :n.empno is not null then
insert into emp(empno, ename, sal)
values (:n.empno, :n.ename, :n.sal);
end if;

if :n.deptno is not null then
select count(*) into dept_cnt
from dept where deptno = :n.deptno;

if dept_cnt > 0 and (:n.dname is not null) then
update dept set dname = :n.dname where deptno = :n.deptno;
else insert into dept(deptno, dname) values(:n.deptno, :n.dname);
end if;
end if;
end;
/

insert into emp_dept values (5000, 'EYKIM', 100, 10, 'SALES');
insert into emp_dept(empno, ename, deptno) values (6000, 'YOUNKIM', 20);
insert into emp_dept (empno, deptno, dname) values (7000, 50, 'NEW_DEPT');

11.4 DDL Trigger

create table trigger_log

(action varchar2(90),

action_date date );

CREATE OR REPLACE TRIGGER audit_object_create AFTER CREATE ON SCHEMA -- 현재 내 SCHEMA 안에서만 추적하라 ( 전체라면 DATABASE로 )

BEGIN

INSERT INTO trigger_log

VALUES ('CREATE ' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || ' by ' || sys.login_terminal, sysdate);

END;

/

CREATE OR REPLACE TRIGGER audit_object_alter AFTER ALTER ON SCHEMA

BEGIN

INSERT INTO trigger_log

VALUES ('ALTER ' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || ' by ' || sys.login_terminal, sysdate);

END;

/

CREATE OR REPLACE TRIGGER audit_object_drop AFTER DROP ON SCHEMA

BEGIN

INSERT INTO trigger_log

VALUES ('DROP ' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || ' by ' ||sys.login_terminal, sysdate);

END;

/

문제) 아예 drop 이나 truncate 를 수행하지 못하게 트리거를 생성하여라!!

create or replace trigger no_ddl

before drop or truncate on scott.schema

begin

raise_application_error(-20001,'You cannot drop or truncate');

end;

/

11.6 Logon Trigger

SQL> create table log_trig_table
(user_id varchar2(10),
log_date date,
action varchar2(20) );

SQL> create or replace trigger logon_trig
after logon on database
begin
insert into log_trig_table(user_id,log_date,action)
values(user,sysdate,'logging on');
end;
/

문제) 특정 유져로 접속을 시도해보고 테이블에 잘 입력이 되는지 확인하라!!

11.6 Logon Trigger 사용예

CREATE OR REPLACE TRIGGER LOGON_TRACE

AFTER LOGON ON DATABASE

DECLARE

v_sid v$session.sid%TYPE;

v_serial v$session.serial#%TYPE;

v_username v$session.username%TYPE;

v_terminal v$session.terminal%TYPE;

BEGIN

SELECT sid, serial#, username, terminal

         into v_sid, v_serial, v_username, v_terminal

FROM v$session

WHERE AUDSID = USERENV('SESSIONID')

         AND ROWNUM <= 1 ;

IF v_username = 'SPC' AND v_terminal = 'LJH' THEN

execute immediate 'alter session set cursor_sharing = force';

SYS.DBMS_SESSION.SET_SQL_TRACE(TRUE);

END IF;

END ;

/