Show errors
CREATE OR REPLACE PROCEDURE User_Exception
(v_deptno IN emp.deptno%type )
IS
-- 예외의 이름을 선언
user_define_error EXCEPTION; -- STEP 1
cnt NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE;
SELECT COUNT(empno)
INTO cnta
FROM emp
WHERE deptno = v_deptno;
IF cnt < 5 THEN
-- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
RAISE user_define_error; -- STEP 2
END IF;
EXCEPTION
-- 예외가 발생할 경우 해당 예외를 참조한다.
WHEN user_define_error THEN -- STEP 3
RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');
END;
/
경고: 컴파일 오류와 함께 프로시저가 생성되었습니다.
SQL> show err
1.9 in Parameter
create or replace procedure raise_salary
( id in emp.empno%type,
percent in number )
is
begin
update emp
set sal = sal *( 1 + percent/100)
where empno = id;
end raise_salary;
/
SQL> exec raise_salary(7788,10);
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) 부서번호를 넣고 해당 부서번호의 사원들의 월급을 인상시켜주는 프로시져를
작성하시요 !!
1.10 Out Parameter
create or replace procedure query_emp
( v_empno in emp.empno%type,
v_ename out emp.ename%type,
v_sal out emp.sal%type ) is
begin
select ename, sal into v_ename, v_sal
from emp
where empno = v_empno;
end query_emp;
/
SQL> declare
emp_name emp.ename%type;
emp_sal emp.sal%type;
begin
query_emp(7788,emp_name, emp_sal);
dbms_output.put_line(emp_name || emp_sal);
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) 1-11 을 보고 위에서 수행한 결과를 출력하시요 !!
1.13 IN OUT Parameter
SQL> create or replace procedure format_phone
( p_phone_no in out varchar2 )
is
begin
p_phone_no := '(' ||substr(p_phone_no,1,3) ||
')' ||substr(p_phone_no,4,3) ||
'-' ||substr(p_phone_no, 7);
end format_phone;
/
프로시저가 생성되었습니다.
SQL> variable g_phone_no varchar2(15)
SQL> begin
:g_phone_no := '8006330575';
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> print g_phone_no
G_PHONE_NO
--------------------------------
8006330575
SQL> execute format_phone(:g_phone_no)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print g_phone_no
G_PHONE_NO
--------------------------------
(800)633-0575
1.15 Parameter Passing
create sequence dept_seq;
create or replace procedure add_dept
( v_dname in dept.dname%type,
v_loc in dept.loc%type ) is
begin
insert into dept(deptno, dname, loc)
values(dept_seq.nextval, v_dname, v_loc);
end add_dept;
/
SQL> exec add_dept('ddd','aaa');
SQL> exec add_dept( v_dname =>’ccc’);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from dept;
1.16 Default Option
SQL> create sequence dept_seq1
start with 50
increment by 10;
시퀀스가 생성되었습니다.
SQL> create or replace procedure add_dept
( p_empno in emp.empno%type,
p_ename in emp.ename%type,
p_sal in emp.sal%type default 3000,
p_comm in emp.comm%type default 20 )
is
begin
insert into emp(empno,ename, sal, comm)
values(p_empno,p_ename,p_sal, p_comm);
end add_dept;
/
프로시저가 생성되었습니다.
SQL> exec add_dept;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 unkown unknown
SQL> exec add_dept('INSA','SEQUL');
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) 사원테이블에 사원번호와 이름과 월급과 커미션을 입력하는 프로시져를 생성하는데
월급과 커미션을 넣지 않으면 각각 3000 , 20 이 입력되게하시요 !!
1.23 Exception handle
SQL>
create or replace procedure p2_ins_dept
(p_deptno number )
is
v_did number(4);
begin
dbms_output.put_line('Procedure p2_ins_dept started');
insert into dept values(p_deptno,'sales','Busan');
select deptno into v_did
from emp
where empno=999;
end;
/
SQL>
create or replace procedure p1_ins_loc
(p_deptno number, p_ename varchar2)
is
v_ename varchar2(30); v_deptno number;
begin
dbms_output.put_line('Main Procedure p1_ins_loc');
insert into emp(empno,ename,deptno)
values(1111, p_ename, p_deptno) ;
dbms_output.put_line('Insert into Dept');
dbms_output.put_line('Main Procedure p2_ins_dept');
p2_ins_dept(p_deptno);
exception
when no_data_found then
dbms_output.put_line('No such dept/loc for any employee');
end;
/
SQL> exec p1_ins_loc(70,'JJJ');
Main Procedure p1_ins_loc
Insert into Dept
Main Procedure p2_ins_dept
Procedure p2_ins_dept started
No such dept/loc for any employee
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) 9-23 쪽을 보고 위의 스크립트를 처리되지 않은 예외사항으로 변경하시요 !!
SQL>
create or replace procedure p2_ins_dept
(p_deptno number )
is
v_did number(4);
begin
dbms_output.put_line('Procedure p2_ins_dept started');
insert into dept values(p_deptno,'sales','Busan');
select deptno into v_did
from emp
where empno=999;
end;
/
SQL>
create or replace procedure p1_ins_loc
(p_deptno number, p_ename varchar2)
is
v_ename varchar2(30); v_deptno number;
begin
dbms_output.put_line('Main Procedure p1_ins_loc');
insert into emp(empno,ename,deptno)
values(1111, p_ename, p_deptno) ;
dbms_output.put_line('Insert into Dept');
dbms_output.put_line('Main Procedure p2_ins_dept');
p2_ins_dept(p_deptno);
/* exception
when no_data_found then
dbms_output.put_line('No such dept/loc for any employee'); */
end;
/
SQL> exec p1_ins_loc(70,'JJJ');
Main Procedure p1_ins_loc
Insert into Dept
Main Procedure p2_ins_dept
Procedure p2_ins_dept started
BEGIN p1_ins_loc(70,'JJJ'); END;
*
1행에 오류:
ORA-01403: 데이터를 찾을 수 없습니다.
ORA-06512: "SYS.P2_INS_DEPT", 줄 8에서
ORA-06512: "SYS.P1_INS_LOC", 줄 11에서
ORA-06512: 줄 1에서
롤백되었는지 꼭 확인하시요 !!!
2.6 Stored Function
create or replace function get_sal
( p_ename in emp.ename%type )
return number
is
v_sal emp.sal%type ;
begin
select sal into v_sal
from emp
where ename=p_ename;
return v_sal;
end get_sal;
/
SQL> variable g_deptno number
SQL> execute :g_deptno := get_depnto(‘SCOTT’)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print g_deptno
G_SALARY
----------
3000
문제) 사원이름을 넣으면 부서번호가 출력되게 하는 함수를 작성하시요 !!
2.10 Function location
SQL> update emp
set sal= get_sal(7788)
where ename='SMITH';
SQL> create table copy_emp
as
select * from emp;
테이블이 생성되었습니다.
SQL> update copy_emp
set sal=get_sal(7788)
where ename='SMITH';
1 행이 갱신되었습니다.
Drop sequence seq2;
Create sequence seq2;
SQL> create or replace function dml_call_sql
(v_sal number)
return number is
begin
insert into emp(empno,ename, sal, deptno)
values(seq2.nextval, 'No name',v_sal,10);
return (v_sal + 100) ;
end;
/
SQL> update emp
set sal = dml_call_sql(2000)
where empno=7788;
2.15 View Function source
문제) 2.15 를 보고 get_sal function 의 소스를 확인하여라 !!
3.8 Package Specification
create or replace package comm_package
is
g_comm number := 0.10;
procedure reset_comm
( p_comm in number );
end comm_package;
/
3.8 Package Body
create or replace package body comm_package
is
function validate_comm ( p_comm in number )
return boolean
is
v_max_comm number;
begin
select max(comm) into v_max_comm
from emp;
if p_comm > v_max_comm then return(false);
else return(true);
end if;
end validate_comm;
procedure reset_comm (p_comm in number)
is
begin
if validate_comm(p_comm)
then g_comm := p_comm;
else
raise_application_error(-20210,'Invalid commission');
end if;
dbms_output.put_line(g_comm);
end reset_comm;
end comm_package;
/
execute comm_package.reset_comm(300);
문제) 3-11 쪽을 보고 위의 패키지를 수행하시요 ! 근데 에러가 나겠금도 수행해
보시요 !!
3.12 Bodiless Packages
SQL> create or replace package global_consts is
mile_2_kilo constant number := 1.6093;
kilo_2_mile constant number := 0.6214;
yard_2_meter constant number := 0.9166;
meter_2_yard constant number := 1.0936;
end global_consts;
/
SQL> set serveroutput on
SQL> execute dbms_output.put_line('20 miles = '|| 20 * - global_consts.mile_2_kilo || ' km')
50 yard 가 몇미터인지 ?
SQL> create or replace procedure meter_to_yard
( p_meter in number, p_yard out number )
is
begin
p_yard := p_meter * global_consts.meter_2_yard;
end meter_to_yard;
/
SQL> variable yard number
SQL> execute meter_to_yard(1,:yard)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print yard
YARD
----------
1.0936
문제) 위에선 만든 패키지를 이용하여 20 meter 은 몇 yard
가 나오는지 출력해보시요
3.15 When Packages Recompile
SQL> col object_name for a20
SQL> select object_name, status, object_type
from user_objects
where object_name='COMM_PACKAGE' ;
OBJECT_NAME STATUS OBJECT_TYPE
-------------------- ------- -------------------
COMM_PACKAGE VALID PACKAGE
COMM_PACKAGE VALID PACKAGE BODY
문제) 패키지 명세의 public 변수를 수정하고 실행한다음에 다시 status 를 확인하라!
SQL> alter package comm_package compile ;
SQL> create or replace package comm_package
is
g_comm number := 0.10;
procedure reset_comm
( p_comm in number );
end comm_package;
/
패키지가 생성되었습니다.
SQL> select object_name, status, object_type
from user_objects
where object_name='COMM_PACKAGE' ;
OBJECT_NAME STATUS OBJECT_TYPE
-------------------- ------- -------------------
COMM_PACKAGE VALID PACKAGE
COMM_PACKAGE INVALID PACKAGE BODY
SQL> alter package comm_package compile ;
패키지가 변경되었습니다.
SQL> select object_name, status, object_type
from user_objects
where object_name='COMM_PACKAGE' ;
3.16 Hidden Package Body coding
SQL> ed comm_package.sql
SQL> ed comm_package_body.sql
SQL> exit
\oracleyu>wrap iname=comm_package_body.sql oname=comm_package.plb
SQL> connect scott/tiger
연결되었습니다.
SQL> @comm_package.sql
패키지가 생성되었습니다.
SQL> @comm_package.plb
패키지 본문이 생성되었습니다.
4.5 Overloading
Drop sequence dept_seq1;
SQL> create sequence dept_seq1
start with 50
increment by 10;
시퀀스가 생성되었습니다.
SQL> create or replace package over_pack
is
procedure add_dept
( p_deptno in dept.deptno%type,
p_dname in dept.dname%type default 'unknown',
p_loc in dept.loc%type default 0 );
procedure add_dept
( p_dname in dept.dname%type default 'unknown',
p_loc in dept.loc%type default 0 );
end over_pack;
/
SQL> create or replace package body over_pack is
procedure add_dept
( p_deptno in dept.deptno%type,
p_dname in dept.dname%type default 'unknown',
p_loc in dept.loc%type default 0 )
is
begin
insert into dept(deptno,dname,loc)
values(p_deptno, p_dname, p_loc);
end add_dept;
procedure add_dept
( p_dname in dept.dname%type default 'unknown',
p_loc in dept.loc%type default 0 )
is
begin
insert into dept(deptno, dname, loc)
values(dept_seq1.nextval, p_dname, p_loc);
end add_dept;
end over_pack;
/
SQL> execute over_pack.add_dept(42,'aaa','bbb');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> execute over_pack.add_dept('ttt','ddd');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
42 aaa bbb
50 ttt ddd
문제) 위에서 만든 패키지를 사용하여 부서테이블에 seoul, insa 라는 데이타를
입력하여라 부서번호는 시퀀스값으로 임의로 설정되게하여라 !
4.11 Restriction using Package function
SQL> create or replace package taxes_pack2
is
function tax2 (p_value in number ) return number;
v_sal number;
end taxes_pack2;
/
패키지가 생성되었습니다.
SQL> create or replace package body taxes_pack2
is
function tax2 (p_value in number) return number
is
begin
select sal*0.2 into v_sal
from emp
where empno=P_VALUE;
return v_sal;
end tax2;
end taxes_pack2;
/
패키지 본문이 생성되었습니다.
SQL> select empno, taxes_pack2.tax2(empno)
from emp;
EMPNO TAXES_PACK2.TAX2(EMPNO)
---------- -----------------------
7839 1000
14 개의 행이 선택되었습니다.
SQL> update emp
set sal = taxes_pack2.tax2(7902)
where ename='SCOTT';
ß 수행되는가 확인하시요 !!!
4.12 Package Function Example
SQL> create or replace package taxes_pack
is
function tax (p_value in number ) return number;
end taxes_pack;
/
SQL> create or replace package body taxes_pack
Is
function tax (p_value in number) return number
is
v_rate number := 0.08;
begin
return (p_value * v_rate);
end tax;
end taxes_pack;
/
SQL>select taxes_pack.tax(sal), sal, ename
from emp;
문제) taxes_pack 패키지의 tax 함수를 사용하여 사원번호 7788 번인
사원의 월급을 갱신하면 되겠는가 ?
SQL> update emp
set sal=taxes_pack.tax(30000)
where empno=7788;
1 행이 갱신되었습니다.
SQL> select ename, sal
2 from emp
3 where empno=7788;
ENAME SAL
---------- ----------
SCOTT 2400
4.12 Package Function Example
SQL> create or replace package pack_cur is
PRAGMA SERIALLY_REUSABLE;
cursor c1 is select empno
from emp
order by empno desc;
procedure proc1_3rows;
procedure proc4_6rows;
end pack_cur;
/
SQL> create or replace package body pack_cur is
PRAGMA SERIALLY_REUSABLE;
v_empno number;
procedure proc1_3rows is
begin
open c1;
loop
fetch c1 into v_empno;
dbms_output.put_line('id:' || (v_empno));
exit when c1%rowcount >= 3;
end loop;
end proc1_3rows;
procedure proc4_6rows is
begin
loop
fetch c1 into v_empno;
dbms_output.put_line('id:' || (v_empno) );
exit when c1%rowcount >= 6;
end loop;
close c1;
end proc4_6rows;
end pack_cur;
/
SQL> execute pack_cur.proc1_3rows
id:7934
id:7902
id:7900
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> execute pack_cur.proc4_6rows
id:7876
id:7844
id:7839
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) PRAGMA SERIALLY_REUSABLE 를 사용하여 다시 실행해보시요!!
4.17 Records in Packages
SQL> create or replace package emp_package is
type emp_table_type is table of emp%rowtype
index by binary_integer;
procedure read_emp_table
(p_emp_table out emp_table_type);
end emp_package;
/
SQL> create or replace package body emp_package is
procedure read_emp_table
(p_emp_table out emp_table_type) is
i binary_integer := 0;
begin
for emp_record in (select * from emp)
loop
p_emp_table(i) := emp_record;
i := i +1 ;
end loop;
end read_emp_table;
end emp_package;
/
SQL> set serveroutput on
SQL>
SQL> declare
v_emp_table emp_package.emp_table_type;
begin
emp_package.read_emp_table(v_emp_table);
dbms_output.put_line('An example: '|| v_emp_table(4).ename);
end;
/
문제) dept 테이블의 3번째 loc 가 무엇인지 나타내는 패키지를 작성하여라 !!
SQL> create or replace package body over_pack is
procedure add_dept
( p_deptno in dept.deptno%type,
p_dname in dept.dname%type default 'unknown',
p_loc in dept.loc%type default 0 )
is
begin
insert into dept(deptno,dname,loc)
values(p_deptno, p_dname, p_loc);
end add_dept;
procedure add_dept
( p_dname in dept.dname%type default 'unknown',
p_loc in dept.loc%type default 0 )
is
begin
insert into dept(deptno, dname, loc)
values(dept_seq1.nextval, p_dname, p_loc);
end add_dept;
end over_pack;
/
SQL> execute over_pack.add_dept(42,'aaa','bbb');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> execute over_pack.add_dept('ttt','ddd');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
42 aaa bbb
50 ttt ddd
문제) 위에서 만든 패키지를 사용하여 부서테이블에 seoul, insa 라는 데이타를
입력하여라 부서번호는 시퀀스값으로 임의로 설정되게하여라 !
4.11 Restriction using Package function
SQL> create or replace package taxes_pack2
is
function tax2 (p_value in number ) return number;
v_sal number;
end taxes_pack2;
/
패키지가 생성되었습니다.
SQL> create or replace package body taxes_pack2
is
function tax2 (p_value in number) return number
is
begin
select sal*0.2 into v_sal
from emp
where empno=P_VALUE;
return v_sal;
end tax2;
end taxes_pack2;
/
패키지 본문이 생성되었습니다.
SQL> select empno, taxes_pack2.tax2(empno)
from emp;
EMPNO TAXES_PACK2.TAX2(EMPNO)
---------- -----------------------
7839 1000
14 개의 행이 선택되었습니다.
SQL> update emp
set sal = taxes_pack2.tax2(7902)
where ename='SCOTT';
ß 수행되는가 확인하시요 !!!
4.12 Package Function Example
SQL> create or replace package taxes_pack
is
function tax (p_value in number ) return number;
end taxes_pack;
/
SQL> create or replace package body taxes_pack
Is
function tax (p_value in number) return number
is
v_rate number := 0.08;
begin
return (p_value * v_rate);
end tax;
end taxes_pack;
/
SQL>select taxes_pack.tax(sal), sal, ename
from emp;
문제) taxes_pack 패키지의 tax 함수를 사용하여 사원번호 7788 번인
사원의 월급을 갱신하면 되겠는가 ?
SQL> update emp
set sal=taxes_pack.tax(30000)
where empno=7788;
1 행이 갱신되었습니다.
SQL> select ename, sal
2 from emp
3 where empno=7788;
ENAME SAL
---------- ----------
SCOTT 2400
4.12 Package Function Example
SQL> create or replace package pack_cur is
PRAGMA SERIALLY_REUSABLE;
cursor c1 is select empno
from emp
order by empno desc;
procedure proc1_3rows;
procedure proc4_6rows;
end pack_cur;
/
SQL> create or replace package body pack_cur is
PRAGMA SERIALLY_REUSABLE;
v_empno number;
procedure proc1_3rows is
begin
open c1;
loop
fetch c1 into v_empno;
dbms_output.put_line('id:' || (v_empno));
exit when c1%rowcount >= 3;
end loop;
end proc1_3rows;
procedure proc4_6rows is
begin
loop
fetch c1 into v_empno;
dbms_output.put_line('id:' || (v_empno) );
exit when c1%rowcount >= 6;
end loop;
close c1;
end proc4_6rows;
end pack_cur;
/
SQL> execute pack_cur.proc1_3rows
id:7934
id:7902
id:7900
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> execute pack_cur.proc4_6rows
id:7876
id:7844
id:7839
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) PRAGMA SERIALLY_REUSABLE 를 사용하여 다시 실행해보시요!!
4.17 Records in Packages
SQL> create or replace package emp_package is
type emp_table_type is table of emp%rowtype
index by binary_integer;
procedure read_emp_table
(p_emp_table out emp_table_type);
end emp_package;
/
SQL> create or replace package body emp_package is
procedure read_emp_table
(p_emp_table out emp_table_type) is
i binary_integer := 0;
begin
for emp_record in (select * from emp)
loop
p_emp_table(i) := emp_record;
i := i +1 ;
end loop;
end read_emp_table;
end emp_package;
/
SQL> set serveroutput on
SQL>
SQL> declare
v_emp_table emp_package.emp_table_type;
begin
emp_package.read_emp_table(v_emp_table);
dbms_output.put_line('An example: '|| v_emp_table(4).ename);
end;
/
문제) dept 테이블의 3번째 loc 가 무엇인지 나타내는 패키지를 작성하여라 !!
5.5 Debugging using DBMS_OUTPUT
CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
CURSOR emp_cursor IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11, 2) := 0;
counter NUMBER(10) := 1;
BEGIN
FOR emp_record IN emp_cursor LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal
+ emp_record.comm;
DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
'; Wages = '|| TO_CHAR(total_wages)); /* Debug line */
counter := counter + 1; /* Increment debug counter */
END LOOP;
/* Debug line */
DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
TO_CHAR(total_wages));
RETURN total_wages;
END dept_salary;
SQL> select empno,sal, comm, deptno from emp where deptno=20;
EMPNO SAL COMM DEPT
----- ------- -------- -------
1002 1500 500 20
1203 1000 30
1289 1000 10
1347 1000 250 20
SQL>
SET SERVEROUTPUT ON
VARIABLE salary NUMBER;
EXECUTE :salary := job_salary(‘SALESMAN’);
Loop number = 1; Wages = 2000
Loop number = 2; Wages = 3250
Total wages = 3250
PL/SQL procedure successfully executed.
문제) 아래의 function 생성 스크립트를 디버깅하시요!!
CREATE OR replace FUNCTION job_salary (v_job varchar2) RETURN NUMBER IS
CURSOR emp_cursor IS
SELECT sal, comm FROM emp WHERE job = v_job;
total_wages NUMBER(11, 2) := 0;
counter NUMBER(10) := 1;
BEGIN
FOR emp_record IN emp_cursor LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal
+ emp_record.comm;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
TO_CHAR(total_wages));
RETURN total_wages;
END job_salary;
/
5.10 UTL_FILE example
SQL> create directory MY_DIR as 'c:\';
SQL>
create or replace procedure sal_status
( dir in varchar2, filename in varchar2 )
is file utl_file.file_type;
cursor empc is
select ename, sal, deptno
from emp
order by deptno;
newdeptno emp.deptno%type;
olddeptno emp.deptno%type := 0;
begin
file := utl_file.fopen (dir, filename,'w');
utl_file.put_line(file,'REPORT: GENERATED ON ' || sysdate );
utl_file.new_line(file);
for emp_rec in empc loop
if emp_rec.deptno <> olddeptno then
utl_file.put_line (file, 'DEPARTMENT: ' || emp_rec.deptno);
utl_file.new_line(file);
end if;
utl_file.put_line(file,' Employee: ' || emp_rec.ename ||' earns: ' || emp_rec.sal);
olddeptno := emp_rec.deptno;
utl_file.new_line(file);
end loop;
utl_file.put_line(file,'*** end of report ***');
utl_file.fclose(file);
exception
when utl_file.invalid_filehandle then
raise_application_error(-20001,'Invalide File.');
when utl_file.write_error then
raise_application_error(-20002,'Unable to write to file');
end sal_status;
/
SQL> exec sal_status('MY_DIR','ttt.txt');
PL/SQL 처리가 정상적으로 완료되었습니다.
5.12 HTP Package
SQL> CREATE OR REPLACE PROCEDURE hello AS
BEGIN
HTP.HTMLOPEN; -- generates <HTML>
HTP.HEADOPEN; -- generates <HEAD>
HTP.TITLE('Hello'); -- generates <TITLE>Hello</TITLE>
HTP.HEADCLOSE; -- generates </HEAD>
HTP.BODYOPEN; -- generates <BODY>
HTP.HEADER(1, 'Hello'); -- generates <H1>Hello</H1>
HTP.BODYCLOSE; -- generates </BODY>
HTP.HTMLCLOSE; -- generates </HTML>
END;
/
프로시저가 생성되었습니다.
SQL> set serveroutput on
SQL> exec hello;
SQL> exec owa_util.showpage ß 결과를 확인해보시요!!
http://localhost:5560/isqlplus/ ß isqlplus 에서도 확인해본다.
5.14 UTL_Mail example
SQL> alter system set smtp_out_server='smtp.server.com' scope=spfile;
시스템이 변경되었습니다.
SQL> startup force
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 83886760 bytes
Database Buffers 201326592 bytes
Redo Buffers 2945024 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
SQL>
SQL> @?\rdbms\admin\utlmail.sql
패키지가 생성되었습니다.
동의어가 생성되었습니다.
SQL> @?\rdbms\admin\prvtmail.plb
패키지 본문이 생성되었습니다.
오류가 없음.
SQL>
SQL>
SQL> begin
2 utl_mail.send('oracleyu23@google.com','oracleyu@empas.com',
3 message=>'i am a yu test test',
4 subject => 'My letter');
5 end;
6 /
문제) DB 가 Shutdown 될때마다 shutdown 되었다는 메시지가 메일로 전송되게하라 !!
CREATE OR REPLACE TRIGGER SCOTT.db_shutdown
before shutdown on database
begin
UTL_MAIL.SEND('oracleyu23@google.com','oracleyu@empas.com',
null,null,'DB Shutting Down','DATABASE SHUTTING DOWN !!');
end;
/
5.24 Dbms_scheduler example
-- JOB 생성
SQL> exec dbms_scheduler.create_job( -
job_name =>'STAT_SCOTT_15', -
job_type =>'PLSQL_BLOCK', -
job_action =>'sys.dbms_stats.gather_schema_stats("SCOTT",-
estimate_percent =>10);', -
start_date => trunc(sysdate) + 25/24, -
repeat_interval => 'FREQUENCY=HOURLY;INTERVAL=1', -
enabled => true, -
comments => 'Gathering the stats of scott 1 time per day!');
-- JOB 확인
SQL> select job_name, state from user_scheduler_jobs;
-- procedure 생성
SQL> create or replace procedure gather_me is
begin
dbms_stats.gather_schema_stats('SCOTT',estimate_percent=>80);
end;
/
-- program 생성
SQL> exec dbms_scheduler.create_program(-
program_name =>'SCOTT_FULL2', -
program_action => 'gather_me', -
program_type => 'STORED_PROCEDURE', -
enabled => true );
-- JOB 생성
SQL> exec dbms_scheduler.create_job (job_name=>'Stat_scott3', -
program_name =>'SCOTT_FULL2', -
start_Date => systimestamp, -
repeat_interval => 'FREQ=DAILY',-
enabled => true );
5.26 Arguments example
CREATE OR REPLACE PROCEDURE sp_gather_stats (
schema IN VARCHAR2
,cascade IN INTEGER
)
IS
b_cascade BOOLEAN := SYS.DIUTIL.INT_TO_BOOL(cascade);
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => schema, cascade => b_cascade);
END;
5.27 Using Schedule
SQL> begin
dbms_scheduler.create_schedule('SCHED_NAME',
start_date => systimestamp,
repeat_interval => 'FREQ=DAILY',
end_date => systimestamp + 15 );
end;
/
SQL> begin
dbms_scheduler.create_job('job_name',
schedule_name =>'SCHED_NAME',
job_type => 'PLSQL_BLOCK',
job_action => 'dbms_stats.gather_schema_stats("SCOTT");',
enabled => true );
end;
/
문제) 위의 job 을 매주 금요일 자정에만 수행되겠금 수정하시요 !!
SQL>
declare
ltd_start timestamp;
ltd_next timestamp;
ltd_return timestamp;
begin
ltd_start := trunc(systimestamp);
ltd_return := ltd_start;
for cnt in 1..12 loop
dbms_scheduler.evaluate_calendar_string (
'FREQ=MONTHLY; BYDAY=-1SAT; BYHOUR=1',
ltd_start, ltd_return, ltd_next);
dbms_output.put_line('next date: '||
to_char(ltd_next,'YYYYMMDD HH24:MI:SS'));
ltd_return := ltd_next;
end loop;
end;
/
next date: 20080329 01:00:00
next date: 20080426 01:00:00
next date: 20080531 01:00:00
next date: 20080628 01:00:00
next date: 20080726 01:00:00
next date: 20080830 01:00:00
next date: 20080927 01:00:00
next date: 20081025 01:00:00
next date: 20081129 01:00:00
next date: 20081227 01:00:00
Examples of Using Calendaring Expressions
The following examples illustrate simple tasks.
Execute every Friday.
FREQ=WEEKLY; BYDAY=FRI;
Execute every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;
Execute on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY-1;
Execute on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY-2;
Execute on March 10th.
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
Execute every 10 days.
FREQ=DAILY; INTERVAL=10;
Execute daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;
Execute on the 15th day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;
Execute on the 29th day of every month.
FREQ=MONTHLY; BYMONTHDAY=29;
Execute on the second Wednesday of each month.
FREQ=MONTHLY; BYDAY=2WED;
Execute on the last Friday of the year.
FREQ=YEARLY; BYDAY=-1FRI;
Execute every 50 hours.
FREQ=HOURLY; INTERVAL=50;
6.7 Dynamic SQL with DDL
SQL> create or replace procedure drop_table
( table_name varchar2)
is
begin
execute immediate 'drop table ' || table_name ;
end;
/
프로시저가 생성되었습니다.
SQL> begin
create_table('EMP_NAME','id number(4) primary key, name varchar2(40) ');
end;
/
SQL> desc emp_name
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME VARCHAR2(40)
문제) Table 이름을 넣으면 drop 되는 프로시져를 생성하시요 !!
6.8 Dynamic SQL with DML
SQL> create function del_rows
(table_name varchar2 )
return number is
begin
execute immediate 'delete from ' || table_name;
return SQL%ROWCOUNT;
end;
/
함수가 생성되었습니다.
SQL> set serveroutput on
SQL> begin
dbms_output.put_line( del_rows('EMP') || ' rows deleted ');
end;
/
15 rows deleted
SQL> create procedure add_row
(table_name varchar2, id number, name varchar2 )
is
begin
execute immediate 'insert into ' || table_name ||
' values(:1, :2) ' using id, name;
end;
/
SQL> create table emp_name
2 (empno number(10),
3 ename char(10));
SQL> begin
2 add_row('EMP_NAME',1,'aa');
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
문제) 테이블명을 넣었을 때 해당 테이블의 사원의 월급을 2배로 갱신하는 프로시져
를 만드시요!!
6.8 Dynamic SQL with SELECT
SQL> create or replace function get_emp(emp_name varchar2)
return emp%rowtype is
stmt varchar2(200);
emprec emp%rowtype;
begin
stmt := 'select * from emp '||
'where ename = :name' ;
execute immediate stmt into emprec using emp_name;
return emprec;
end;
/
SQL> declare
emprec emp%rowtype := get_emp(‘SCOTT;);
begin
dbms_output.put_line('EMP: ' ||emprec.sal);
end;
/
EMP: SCOTT
문제) 사원이름을 넣으면 월급이 출력되겠금 프로시져를 작성하시요 !!
6.10 Dynamic SQL with Multirow Query
SQL> create procedure list_emp(deptno number) is
type emp_refcsr is ref cursor;
emp_cv emp_refcsr;
emprec emp%rowtype;
stmt varchar2(200) := 'select * from emp';
begin
if deptno is null then open emp_cv for stmt;
else
stmt := stmt || ' where deptno = :id';
open emp_cv for stmt using deptno;
end if;
loop
fetch emp_cv into emprec;
exit when emp_cv%notfound;
dbms_output.put_line(emprec.deptno || ' ' || emprec.ename);
end loop;
close emp_cv;
end;
/
SQL> exec list_emp(10);
문제) 직업을 넣으면 해당 직업과 사원이름이 출력되겠금하는 프로시져를 작성하시요!
6.11 Ref cursor
EMP 테이블의 데이터 중 인자로 넘기는 부서에 해당 하는 데이터만 리턴 합니다…
SQL> CREATE OR REPLACE procedure emptest(v_deptno in number)
is
type emp_refcsr is ref cursor;
test_cursor emp_refcsr;
sql_string Varchar2(500);
emprec emp%rowtype;
BEGIN
sql_string := 'Select * from scott.Emp where deptno = :deptno' ;
Open test_cursor FOR sql_string USING v_deptno;
loop
fetch test_cursor into emprec;
exit when test_cursor%notfound;
dbms_output.put_line(emprec.deptno ||' ' || emprec.ename );
end loop;
CLOSE test_Cursor;
END;
/
SQL> exec emptest(10);
10 KING
10 CLARK
10 MILLER
위 함수를 다음과 같이 USING문을 이용하지 않고도 가능 합니다. (skip)
SQL> CREATE OR REPLACE procedure emptest(v_deptno in number)
is
type emp_refcsr is ref cursor;
test_cursor emp_refcsr;
sql_string Varchar2(500);
emprec emp%rowtype;
BEGIN
sql_string := 'Select * from scott.Emp where deptno = ' || v_deptno ;
Open test_cursor FOR sql_string ;
loop
fetch test_cursor into emprec;
exit when test_cursor%notfound;
dbms_output.put_line(emprec.deptno ||' ' || emprec.ename );
end loop;
CLOSE test_Cursor;
END;
/
또는 아래처럼 OPEN과 동시에 커서를 만들어 버릴 수 도 있습니다..
CREATE OR REPLACE procedure emptest(v_deptno in number)
is
type emp_refcsr is ref cursor;
test_cursor emp_refcsr;
sql_string Varchar2(500);
emprec emp%rowtype;
BEGIN
Open test_cursor FOR
select * from scott.emp
where deptno = v_deptno;
loop
fetch test_cursor into emprec;
exit when test_cursor%notfound;
dbms_output.put_line(emprec.deptno ||' ' || emprec.ename );
end loop;
CLOSE test_Cursor;
END;
/
자 그럼 이젠 JSP 프로그램을 간단히 만들어 TEST 해 봅시다…
//test.jsp
<%@ page language="java" contentType="text/html; charset=euc-kr" %>
<%@ page import="java.sql.*,oracle.jdbc.driver.* " %>
<%
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;
String driver_name = "oracle.jdbc.driver.OracleDriver"; //오라클 드라이버
String url = "jdbc:oracle:thin:@localhost:1521:wink"; //호스트
String user = "scott"; //계정
String pwd = "tiger"; //비밀번호
String query = "";
try {
Class.forName(driver_name); //jdbc 드라이버연결
conn = DriverManager.getConnection(url,user,pwd); //Connection인수 입력
String proc_call = "{? = call emptest(?)}";
// create callable statement
cstmt = conn.prepareCall(proc_call);
// key here is to register the output parameter
// of type cursor, execute, then cast it as a ResultSet.
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setInt(2, 10); //10번 부서의 데이터만 얻기 위해
cstmt.executeQuery();
rs = (ResultSet)cstmt.getObject(1);
while(rs.next()) {
out.println(rs.getString("ename") + "<br>");
}
}
catch(Throwable e)
{
out.println(e);
}
finally {
try {
rs.close();
cstmt.close();
conn.close();
}
catch(Exception e) {}
}
%>
DocumentRoot에 두고 http://localhost/test.jsp 라고 실행을 했습니다.
[결과]
CLARK
KING
MILLER
6.12 Dynamically Executing PL/SQL block (skip)
SQL> create function annual_sal
(emp_id number)
return number is
plsql varchar2(200) :=
'declare ' ||
'emprec emp%rowtype; ' ||
'begin ' ||
'emprec := get_emp(:empid) ; ' ||
' :res := emprec.sal * 12; ' ||
'end; ' ;
result number;
begin
execute immediate plsql
using in emp_id, out result;
return result;
end;
/
SQL> execute dbms_output.put_line(annual_sal(7788));
1112859
6.13 Compile PL/SQL Code
SQL> create procedure compile_plsql
(name varchar2, plsql_type varchar2, options varchar2 := null)
is
stmt varchar2(200) := 'alter ' || plsql_type ||
' ' || name || ' compile' ;
begin
if options is not null then
stmt := stmt || ' ' || options;
end if;
execute immediate stmt;
end;
/
SQL> drop table emp;
SQL> select object_name, status
from user_objects
where object_type='PROCEDURE'
and status ='INVALID';
OBJECT_NAME STATUS
------------------------- -------
LIST_EMP INVALID
EMPTEST INVALID
LOG_EXECUTIN INVALID
SQL> flashback table emp to before drop;
SQL> select object_name, status
2 from user_objects
3 where object_type='PROCEDURE
4 and status ='INVALID';
OBJECT_NAME STATUS
------------------------- -------
LIST_EMP INVALID
EMPTEST INVALID
LOG_EXECUTIN INVALID
CROSS_AVGSAL INVALID
LOAD_EMP_BFILE INVALID
SQL> exec compile_plsql('LIST_EMP','PROCEDURE')
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select object_name, status
2 from user_objects
3 where object_type='PROCEDURE'
4 and status ='INVALID';
OBJECT_NAME STATUS
------------------------- -------
EMPTEST INVALID
LOG_EXECUTIN INVALID
CROSS_AVGSAL INVALID
LOAD_EMP_BFILE INVALID
문제) Stutus 가 invalid 된 프로시져를 모두 컴파일하는 PL/SQL 구문을 작성하시요
Café.daum.net/oracleoracle
6.15 DBMS_SQL
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;
/
프로시저가 생성되었습니다.
SQL> variable deleted number
SQL> execute delete_all_rows('EMP', :deleted)
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print deleted
DELETED
----------
14
6.15 DBMS_METADATA
SQL> set long 50000000
SQL> SET PAGES 40
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;
SQL> select dbms_metadata.get_xml('TABLE','EMP') from dual;
문제) SCOTT 이 가지고 소유하고 있는 TABLE 생성 스크립트를 모두 뜰려면 ?
SET LONG 2000000
SET PAGESIZE 0
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_ALL_TABLES u
WHERE u.nested='NO'
AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_
TRANSFORM,'DEFAULT');
6.20 Subprogram in DBMS_METADATA
문제) SCOTT 에게 부여했던 권한 리스트를 보여달라!
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
6.27 Example
SQL> create function get_table_md return clob is
h number;
th number;
doc clob;
begin
h := DBMS_METADATA.OPEN('TABLE');
dbms_metadata.set_filter(h,'SCHEMA','HR');
dbms_metadata.set_filter(h,'NAME','EMPLOYEES');
th := dbms_metadata.add_transform(h,'DDL');
doc := dbms_metadata.fetch_clob(h);
dbms_metadata.close(h);
return doc;
end;
/
SQL> set pages 0
SQL> set long 1000000
SQL> select get_table_md from dual;
문제) employees 테이블에 걸린 index인 EMP_EMAIL_UK 의 스크립트를 생성하여라 !!
7.4 Standard Exception
CREATE OR REPLACE PROCEDURE User_Exception
(v_deptno IN emp.deptno%type )
IS
-- 예외의 이름을 선언
user_define_error EXCEPTION; -- STEP 1
cnt NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE;
SELECT COUNT(empno)
INTO cnt
FROM emp
WHERE deptno = v_deptno;
IF cnt < 5 THEN
-- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
RAISE user_define_error; -- STEP 2
END IF;
EXCEPTION
-- 예외가 발생할 경우 해당 예외를 참조한다.
WHEN user_define_error THEN -- STEP 3
RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');
END;
/
문제) 이름을 넣었을 때 82년 이전에 입사한 사원이면 정년 퇴임
대상입니다라는 메시지가 출력되게하시요 !!
7.6 Standard Constant
SQL> create or replace package constant_pkg is
c_order_recevied constant varchar(2) := 'OR';
c_order_shipped constant varchar(2) := 'OS';
c_min_sal constant number(3) := 900;
end constant_pkg;
/
SQL> begin
update emp
set sal = sal + 200
where sal <= constant_pkg.c_min_sal;
end;
/
7.7 Local Subprogram
Create or replace procedure emp_sal (id number) is
emprow emp%rowtype;
function tax(sal varchar2) return number is
begin
return sal * 0.825;
end tax;
begin
select * into emprow
from emp where empno = id;
dbms_output.put_line('Tax: ' || tax(emprow.sal) );
end;
/
SQL> exec emp_sal(7788);
문제) 사원번호를 입력하면 해당 사원의 커미션을 월급의 10%로 갱신하고 그 커미션을 출력하는 프로시져를 작성하시요 !!
7.9 Authid Current_User
SQL> connect /as sysdba
연결되었습니다.
SQL> create user jack
2 identified by tiger;
사용자가 생성되었습니다.
SQL> grant connect, resource to jack;
권한이 부여되었습니다.
SQL> connect scott/tiger
create or replace procedure proc_grant
( v_empno in emp.empno%type)
is
v_name emp.ename%type;
begin
select ename into v_name
from scott.emp
where empno=7788;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
SQL> exec proc_grant(7788);
SCOTT
SQL> grant execute on proc_grant to jack;
SQL> connect jack/tiger
SQL> set serveroutput on
SQL> exec scott.proc_grant(7788);
SCOTT
SQL> connect scott/tiger
create or replace procedure proc_grant
( v_empno in emp.empno%type)
authid current_user
is
v_name emp.ename%type;
begin
select ename into v_name
from scott.emp
where empno=7788;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
SQL> connect jack/tiger
연결되었습니다.
SQL> exec scott.proc_grant(7788);
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
ORA-06512: "SCOTT.PROC_GRANT", 줄 8에서
ORA-06512: 줄 1에서
문제) scott 으로 접속해서 emp 테이블을 select 할 수 있는 권한을 jack 에게 주고 다시 실행해보시요 !!
7.10 Autonomous_transaction
SQL> create table log_table
( username varchar2(30),
date_time timestamp,
message varchar2(4000) );
SQL> create table temp_table
( n number);
SQL> create or replace procedure log_message
(p_message varchar2 )
as
pragma autonomous_transaction;
begin
insert into log_table(username,date_time, message)
values(user, current_date, p_message);
commit;
end log_message;
/
SQL> select * from temp_table;
선택된 레코드가 없습니다.
SQL> select * from log_table;
선택된 레코드가 없습니다.
SQL> begin
log_message('About to insert into temp_table');
insert into temp_table(n) values(12345);
log_message('rolling back insert into temp_table');
rollback;
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from temp_table;
선택된 레코드가 없습니다.
SQL> select * from log_table; ß 확인해보시요 !!
문제)
BEGIN
exec....PROC1
....
exec....PROC2
....
exec....PROC3
....
exec....PROC2
IF~~~~THEN
commit;
ELSE
rollback;
END IF;
END;
크게 문제가 없어보이긴하다 허나 만약 PROC1 프로시저에서 PRAGMA
autonomous_transaction; 옵션을 적용 하고 COMMIT을 안때리면 어떨까~!?
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;
/
'oracle10R2' 카테고리의 다른 글
| SQL Loader (0) | 2011.07.10 |
|---|---|
| Advanced Product Service 10g Develop PLP 02 (0) | 2011.07.02 |
| ASM (0) | 2011.06.22 |
| Oracle DICTIONARY (0) | 2011.06.20 |
| shared pool 사이즈 예상방법 (0) | 2011.06.20 |
