오라클 pl/sql 커서, exception, pragma, exception raise, procedure, function

-- 커서 사용, declare, open, fetch, close

set serverout on;

declare

    vempid employees.employee_id%type;

    vfname employees.first_name%type;

    cursor cl is  -- 커서 정의, 여러 레코드를 가질 수 있다

        select employee_id, first_name

        from employees

        where department_id= 30;

begin

    open cl; -- 커서 열기

    loop

        fetch cl into vempid, vfname;  -- 커서 cl의 값을 vempid, vfname에 각각 담기

        exit when cl%notfound;  -- 커서 cl에 더이상 레코드가 없으면 loop종료

        dbms_output.put_line(vempid||'=='||vfname);

    end loop;

    close cl;  -- 커서 닫기

end ;

/

-- 커서와  for문

declare

    cursor emp_cur is  -- 커서 선언

        select employee_id, first_name

        from employees

        where department_id = 100;

begin

    for emp_rec in emp_cur  -- 커서에 레코드가 있는동안 반복

        loop

            dbms_output.put_line(emp_rec.employee_id||'  '||emp_rec.first_name);

        end loop;

end;

-- 커서와  for문, for문 안에서 커서 바로 사용

declare

    begin

        for emp_rec in (select employee_id, first_name

                        from employees

                        where department_id=100)

        loop

            dbms_output.put_line(emp_rec.employee_id||' '||emp_rec.first_name);

        end loop;

    end;

-- exception 

declare

    v_fname employees.first_name%type;

begin

    select first_name into v_fname

    from employees

    where first_name like 'B%';

    dbms_output.put_line('사원명은 '||v_fname||'입니다.');

exception  -- 위 begin에서 v_fname은 한개의 레코드가 와야되는데, 레코드가 없거나 두개이상이면 exception이 실행된다

    when no_data_found then

        dbms_output.put_line('해당 사원이 없습니다.');

    when too_many_rows then

        dbms_output.put_line('사원이 두 명 이상입니다');

end;        

-- pragma

create table t_pragma

(no number,

name varchar2(10),

primary KEY (no));

insert into t_pragma

values (1, 'aaa');

insert into t_pragma

values(1, 'bbb');

declare

    new_msg exception;   -- exception인 new_msg 변수 선언

    pragma exception_init(new_msg, -1);

begin

    insert into t_pragma values (1, 'ccc');

exception

    when new_msg then

    dbms_output.put_line('존재하는 번호입니다');

end;

/

-- exception raise

create table employees11

as

    select employee_id, first_name

    from employees;

declare

    no_empid exception;  -- 예외 선언

begin

    delete from employees11

    where employee_id = &empid;

        if sql%notfound then  -- 데이터가 없다면

            raise no_empid;  -- no_empid 예외를 호출

        end if;

exception

    when no_empid then

        dbms_output.put_line('입력하신 번호는 없는 사원번호 입니다');

end;

-- procedure 생성

create or replace procedure up_sal

(vempid employees.employee_id%type)

is

    begin

        update employees set salary = 5000

        where employee_id=vempid;

    end;

/    

 -- procedure 실행

exec up_sal(206);

select employee_id, salary from employees

where employee_id=206;

desc user_source;

select text 

from user_source 

where name = 'up_sal';

-- function 생성

create or replace function max_sal

    (s_deptno employees.department_id%type)

    return number  -- 함수는 return이 꼭 있어야한다

is  

    max_sal employees.salary%type;

begin

    select max(salary) into max_sal

    from employees

    where department_id = s_deptno;

    return max_sal;

end;

/

-- function 사용

select max_sal(10) from dual;