2023.02.17

연을 2023. 2. 17. 17:28

728x90

예외처리

declare
begin
exception when 예외명1 then 예외처리1
when 예외명2 then 예외처리2
when others then 예외처리n

end;
/

=============================================

<DB테이블 설계(호텔객실관리)>

객실타입관리 roomtype	객실관리 roominfo	예약관리 book	고객관리 customer
typecode number 타입코드 (기본키pk) typename varhar2(10) 타입이름 comment created => 부수적인것 updated	roomcode(pk) typecode(fk) roomname price people commnet	book_no(pk) roomcode(fk) people(객실관리people과 다르다) totalprice checkin checkout cust_no => 고객관리가 있을 경우 name => 고객관리가 없을 경우 mobile	cust_no(pk) name mobile created level member (고객:1, 비고객:0)
pk 기준 null unique unchangeable 컬럼하나에 한가지 값(atomic)만 저장 : 1NF 1차 Normalization Form

정규화는 3,4까지가 좋다.

=============================================

복습

procedure(절차)

Pascal언어까지만 있었던 용어
반환값이 없는 함수의 원래 이름 : return value;
지정된 작업만 수행(반환X)
SQL Developer/SQL Plus에서 사용자가 직접 호출
코드블럭 안에서 호출

create or replace function f1() begin end; /	create or replace procedure P1() begin end; /
select f1(xxx) from ... insert into T values (f1(xxxx),....) update T set col1=f1(xxxx)	exec P1(xxxx);


create or replace procedure 프로시저명(매개변수1,...,매개변수n) is 변수선언 begin 실행문 ~~return X;~~ end; /

<예시코드>

매개 변수가 없는 코드 : 매개변수가 없을땐 ()도 쓰지 말아야 한다.

select distinct name from user_source;

create or replace procedure p1
is
    name varchar2(80);
    sal number;
begin
    select emp_name, salary
    into name, sal
    from employees
    where employee_id=121;
    dbms_output.put_line(name||', '||sal);
end;
/

<결과>

Adam Fripp, 8200

<예시코드>

매개변수가 있는 버전

create or replace procedure p2(eid number)
is
    name varchar2(80);
    sal number;
begin
    select emp_name, salary
    into name, sal
    from employees
    where employee_id=eid;
    dbms_output.put_line(eid||', '||name||', '||sal);
        
exception 
    when no_data_found then 
        dbms_output.put_line('사번에 해당하는 직원은 없습니다.');
    when others then
        dbms_output.put_line('알수없는 오류발생');
end;
/
exec p2(110);

<결과>

exec p2(110);	exec p2(90);
110, John Chen, 8200	사번에 해당하는 직원은 없습니다.

<예시코드>

create or replace procedure p3(x number, y number)
is
    cursor c1(a number, b number) is
        select emp_name, salary
        from employees
        where salary between a and b;
    name varchar2(80);
    sal number;
begin
    open c1(x,y);
    loop
        fetch c1 into name, sal;
        exit when c1%notfound;
        dbms_output.put_line(name||', '||sal);
    end loop;
    close c1;
end;
/
exec p3(2000,3000);

<결과>

exec p3(2000,3000);

Donald OConnell, 2600
Douglas Grant, 2600
Shelli Baida, 2900
Sigal Tobias, 2800
Guy Himuro, 2600
Karen Colmenares, 2500
Irene Mikkilineni, 2700
James Landry, 2400
Steven Markle, 2200
Mozhe Atkinson, 2800
James Marlow, 2500
TJ Olson, 2100
Michael Rogers, 2900
Ki Gee, 2400
Hazel Philtanker, 2200
John Seo, 2700
Joshua Patel, 2500
Randall Matos, 2600
Peter Vargas, 2500
Martha Sullivan, 2500
Girard Geoni, 2800
Anthony Cabrio, 3000
Timothy Gates, 2900
Randall Perkins, 2500
Vance Jones, 2800
Kevin Feeney, 3000

==============================================

c1%rowcount => fetch로 처리된 row의 갯수

<코드>

create or replace procedure p3(x number, y number)
is
    cursor c1(a number, b number) is
        select emp_name, salary
        from employees
        where salary between a and b;
    name varchar2(80);
    sal number;
    flag number :=0;
begin
    open c1(x,y);
    loop
        fetch c1 into name, sal;
        exit when c1%notfound;
        flag:=flag+1;
        dbms_output.put_line(name||', '||sal);
    end loop;
    if c1%rowcount=0 then
        dbms_output.put_line('해당하는 데이터가 없습니다');
    end if;
    close c1;
end;
/
exec p3(110000,120000);

<결과>

해당하는 데이터가 없습니다

<코드>

create or replace procedure p3(x number, y number, z  out varchar2)
is
    cursor c1(a number, b number) is
        select emp_name, salary
        from employees
        where salary between a and b;
    name varchar2(80);
    sal number;
    flag number :=0;
begin
    open c1(x,y);
    loop
        fetch c1 into name, sal;
        exit when c1%notfound;
        flag:=flag+1;
        dbms_output.put_line(name||', '||sal);
    end loop;
    if c1%rowcount=0 then
        dbms_output.put_line('해당하는 데이터가 없습니다');
    end if;
    z:=c1%rowcount||'개 행을 출력했습니다.' ;
    close c1;
end;
/
declare
    n varchar2(80);
begin
    p3(9000,10000,n);
    dbms_output.put_line(n);
end;
/

<결과>

Hermann Baer, 10000
Alexander Hunold, 9000
Daniel Faviet, 9000
Peter Tucker, 10000
David Bernstein, 9500
Peter Hall, 9000
Janette King, 10000
Patrick Sully, 9500
Allan McEwen, 9000
Danielle Greene, 9500
Harrison Bloom, 10000
Tayler Fox, 9600
12개 행을 출력했습니다.

==========================================

execute immediate (SQL문으로 구성된)문자열

into 변수명1,...,변수명n

using 변수명1,...,변수명n

<코드>

declare
    str varchar2(200);
    eid number;
    ename varchar2(80);
    jid varchar2(80);
begin
    str:='select employee_id,emp_name,job_id
    from employees where job_id=''AD_ASST'' ';
    execute immediate str into eid,ename,jid;
    
--    select employee_id, emp_name, job_id
--    into eid,enmae,jid
--    from employees where job_id='AD_ASST';
    dbms_output.put_line(eid||','||ename||','||jid);
end;
/

<결과>

200,Jennifer Whalen,AD_ASST

<코드>

사번이 121번인 직원의 사번, 이름,월급 출력

declare
    str varchar2(200);
    eid number;
    ename varchar2(80);
    sal number;
begin
    str:='select employee_id,emp_name,salary
    from employees where employee_id=''121'' ';
    execute immediate str into eid,ename,sal;
    
--    select employee_id, emp_name, job_id
--    into eid,enmae,jid
--    from employees where job_id='AD_ASST';
    dbms_output.put_line(eid||','||ename||','||sal);
end;
/

<결과>

121,Adam Fripp,8200

<코드>

declare
    str varchar2(200);
    eid number;
    ename varchar2(80);
    sal number;
    jid varchar2(80);
begin
    eid:=200;
    str:='select employee_id,emp_name,salary,job_id
    from employees where employee_id =:a';
    execute immediate str into eid,ename,sal,jid using eid;
    
    dbms_output.put_line(eid||','||ename||','||sal||','||jid);
end;
/

<결과>

eid:=121	eid:=200	eid:=124
121,Adam Fripp,8200,ST_MAN	200,Jennifer Whalen,4400,AD_ASST	124,Kevin Mourgos,5800,ST_MAN

<코드>

declare
    str varchar2(200);
    eid number;
    ename varchar2(80);
    sal number;
    jid varchar2(80);
    mid number;
begin
    jid:='SA_REP';
    sal:=7000;
    mid:=148;
    str:='select employee_id,emp_name,salary,job_id
    from employees where job_id=:a and salary <:b and manager_id=:c';
    execute immediate str into eid,ename,sal,jid using jid,sal,mid;
    
    dbms_output.put_line(eid||','||ename||','||sal||','||jid);
end;
/

<결과>

173,Sundita Kumar,6100,SA_REP

<코드>

declare
    isql varchar2(200);
begin
    isql:='insert into dep_sal values(:a,:b,:c,:d)';
    for rec in(select b.department_name,count(*) cnt,sum(a.salary) sumsal,c.emp_name manager_name
                from employees a, departments b, employees c
                where a.department_id=b.department_id
                and b.manager_id=c.employee_id
                group by b.department_name, c.emp_name
                order by b.department_name)
    loop
        execute immediate isql using rec.department_name, rec.cnt,rec.sumsal, rec.manager_name;
    end loop;
end;
/
create table dep_sal(
    dname varchar2(80),
    cnt number,
    total number,
    mname varchar2(80)
);
select * from dep_sal;

<결과>

<코드>

create or replace procedure p11(lowsal number, hisal number)
is
    c1 sys_refcursor;
    isql varchar2(200);
    eid number;
    ename varchar2(80);
    sal number;
begin    
    isql:='select employee_id,emp_name,salary
            from employees
            where salary between :a and :b';
    open c1 for isql using lowsal,hisal;
    loop
        fetch c1 into eid,ename,sal;
        exit when c1%notfound;
        
        dbms_output.put_line(eid||', '||ename||', '||sal);
    end loop;
end;
/

create or replace procedure p11(lowsal number, hisal number)
is
    c1 sys_refcursor;
    isql varchar2(200);
    eid number;
    ename varchar2(80);
    sal number;
begin
    for rec in(select employee_id,emp_name,salary
                from employees
                where salary between 5000 and 10000)
    loop
        dbms_output.put_line(rec.employee_id||', '||rec.emp_name||', '||rec.salary);
    end loop;
end;
/

<결과>

첫번째 결과물과 두번째 결과물은 같다
204, Hermann Baer, 10000 103, Alexander Hunold, 9000 109, Daniel Faviet, 9000 150, Peter Tucker, 10000 151, David Bernstein, 9500 152, Peter Hall, 9000 156, Janette King, 10000 157, Patrick Sully, 9500 158, Allan McEwen, 9000 163, Danielle Greene, 9500 169, Harrison Bloom, 10000 170, Tayler Fox, 9600

불금이다!!!!

728x90