220915 - PL/SQL - 커서, 함수, 프로시저

커서

•  

declare
    v_dept department%rowtype;
    cursor cl is select * from department; --
begin
    dbms_output.put_line('부서번호   부서명   지역명');
    dbms_output.put_line('-------------------------');
    open cl; --
    loop
        fetch cl into v_dept.dno, v_dept.dname, v_dept.loc; --
        exit when cl%notfound; --
        dbms_output.put_line(v_dept.dno|| v_dept.dname|| v_dept.loc);
    end loop;
    close cl; --
end;
/

=>

부서번호   부서명   지역명
-------------------------
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

 

 

 

 

• for문

FOR 인덱스(로우 전체) IN 커서명 (매개변수1,매개변수2,..)
LOOP
  처리문
END LOOP;

 

 

=> OPEN, CLOSE 생략

 

 

 

•  

    FOR rec IN cur_schNo('1') --schNo가 1인 rec
    LOOP
        dbms_output.put_line(rec.name); --레코드명.컬럼명
    END LOOP;

 

 

 

•  

declare
    v_dept department%rowtype;
    cursor cl is select * from department;
begin
    dbms_output.put_line('부서번호   부서명   지역명');
    dbms_output.put_line('-------------------------');
    for v_dept in cl loop
        exit when cl%notfound;
        dbms_output.put_line(v_dept.dno||' '|| v_dept.dname||' '|| v_dept.loc);
    end loop;
end;
/

=>

부서번호   부서명   지역명
-------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

 

 

 

 

https://hoon93.tistory.com/38

저장 모듈

: PL/SQL문장을 데이터베이스 서버에 저장해 사용자-애플리케이션 공유하도록 만든 프로그램 / 독립적 실행 가능

 

종류 : 프로시저, 사용자정의 함수, 트리거

 

 

• PL/SQL 블록 구조

 

 

 

 

• 함수 - 프로시저 - 트리거 비교

 

 

   

 

사용자 정의 함수

: 프로시저의 각 프로세스를 수행하기 위해 필요한 기능

 

쿼리 또는 EXECUTE 문으로 호출

 

 

 

• 함수 생성

CREATE [OR REPLACE] FUNCTION 함수명 (매개변수1, 매개변수2…)
RETURN 데이터타입
IS[AS] 변수, 상수 등 선언

BEGIN 
    명령문
    RETURN 반환값;

(EXCEPTION 예외처리부)

END;

 

 

 

•  

CREATE OR REPLACE FUNCTION my_mod (num1 number, num2 number)
RETURN number
IS 
v_remain number := 0; --나머지
v_quot number := 0; --몫

BEGIN
    v_quot := floor(num1/num2);
    v_remain := num1 - (num2*v_quot);
RETURN v_remain;

END;
/

=>호출

select my_mod(14,3) 나머지 FROM DUAL;

 

 

 

•  

CREATE OR REPLACE FUNCTION salary_ename (v_ename in employee.ename%type)
RETURN number
IS 
v_salary number(7,2);
BEGIN
  SELECT salary INTO v_salary FROM employee WHERE ename = v_ename;
  RETURN v_salary;
END;
/

=>실행

variable v_sal number;
execute : v_sal := salary_ename('SCOTT');
print v_sal;

 

 

 

•  

create or replace function ename_eno (v_ename in employee.ename%type)
return varchar2
is
v_dname department.dname%type;
begin
    select d.dname into v_dname from department d natural join employee e where ename=v_ename;
    return v_dname;
end;
/

=>실행

variable v_dname varchar2;
exec :v_dname:=ename_eno('SCOTT');
print v_dname;

=> 출력

V_DNAME
-------------------
RESEARCH

 

 

 

https://bluemumin.github.io/sql/2022/01/13/SQL-oracle-sql-%EB%B0%94%EC%9D%B8%EB%93%9C-%EB%B3%80%EC%88%98,-%ED%9E%8C%ED%8A%B8-%EA%B5%AC%EB%AC%B8-%EC%A0%95%EB%A6%AC/
https://thebook.io/006696/part03/ch13/02/01/02/
https://blog.sting.pe.kr/112

※ 바인드 변수 ( : )

여러번 재사용 가능하게 함 (sql 최초 실행 이후 => 다시 hard parse를 하지 않고 이미 parse된 쿼리로 사용하도록 함)
PL/SQL 외부에서도 사용 가능하게 함

 

 

 

 

 

https://mjn5027.tistory.com/47

프로시저

: 일련 작업들을 하나로 묶어 저장, 이후 호출해 실행함
=> 로직만 처리하고 결과값 반환 X (대체로)

복잡한 sql문 단순화
여러번 반복 호출, 사용 가능

 

 

• 함수 - 프로시저 차이

 

 

 

 

• 생성 및 실행

CREATE OR REPLACE PROCEDURE 프로시저명 (매개변수..)
IS 변수, 상수 등 선언
BEGIN 명령어;
END;

 

=>실행

 

EXEC 프로시저명;

 

 

 

• 매개변수 X

CREATE OR REPLACE PROCEDURE p_salary
IS
v_salary employee.salary%type;
BEGIN
    select salary into v_salary from employee where ename='SCOTT';
    dbms_output.put_line('SCOTT 사원 급여 : '||v_salary);
END;

=>

EXEC p_salary;

 

 

 

• 매개변수 O

create or replace NONEDITIONABLE PROCEDURE p_salary (v_ename in employee.ename%type)
is
v_salary employee.salary%type;
begin
    select salary into v_salary from employee where ename=v_ename;
    dbms_output.put_line(v_ename||' 사원 급여 : '||v_salary);
end;

=>

EXEC p_salary('KING');

 

 

 

• 리턴값 O

create procedure p_salary2 
(v_ename in employee.ename%type, v_salary out employee.salary%type) --out: 돌려주는 매개변수
is
begin
    select salary into v_salary from employee where ename=v_ename;
end;

=>

variable v_salary varchar2(14);
exec p_salary2('SCOTT',:v_salary);
print v_salary;

 

 

 

•  

create or replace NONEDITIONABLE procedure p_commission
is
cursor cur is select * from employee where commission is not null order by ename;
begin
    dbms_output.put_line('사원번호   이름   급여');
    dbms_output.put_line('---------------------');
    for rec in cur loop
        exit when cur%notfound;
        dbms_output.put_line(rec.eno||'   '||rec.ename||'   '||rec.salary);
    end loop;
end;

=>

exec p_commission;

=>

사원번호   이름   급여
---------------------
7499   ALLEN   1600
7654   MARTIN   1250
7844   TURNER   1500
7521   WARD   1250

 

 

 

 

문제

○ basic loop문, 1~100합

declare
    num number:=1;
    tot number:=0;
begin
    loop
        tot:=tot+num;
        num:=num+1;
        if num>100 then 
            exit;
        end if;    
    end loop;
    dbms_output.put_line(tot);
end;
/

=>

5050



○ for문, 1~100합 (5의 배수 제외)

declare
    tot number:=0;
begin
    for i in 1..100
    loop
        continue when mod(i,5)=0;
        tot:=tot+i;
    end loop;
    dbms_output.put_line(tot);
end;
/

=> 4000



○ while문, 1~100 합이 3000 넘을 때 반복횟수 값 출력

declare
    tot number:=0;
    num number:=1;
begin
    while num<=100
    loop
        tot:=tot+num;
        if tot>3000 then 
            goto exit;
        end if;
        num:=num+1;
    end loop;
    <<exit>>
    dbms_output.put_line(num||'까지의 합은 '||(tot)||'입니다');
    dbms_output.put_line((num-1)||'까지의 합은 '||(tot-num)||'입니다');
end;
/

=>

77까지의 합은 3003입니다
76까지의 합은 2926입니다



○ 커서, employee 테이블에서 commision이 null 아닌 사원의 사원번호, 이름, 급여, 커미션을 급여 기준 내림차순 정렬

내답변:

declare
    cursor cur is select eno, ename, salary, commission from employee where commission is not null order by salary desc;
begin
    dbms_output.put_line('사원번호   이름   연봉   커미션');
    for rec in cur loop
        exit when cur%notfound;
        dbms_output.put_line(rec.eno||'    '||rec.ename||'    '||rec.salary||'    '||rec.commission);
    end loop;
end;
/

예시답변:

declare
    rec employee%rowtype;
    cursor cur is select eno, ename, salary, commission from employee where commission is not null order by salary desc;
begin
    dbms_output.put_line('사원번호   이름   급여   커미션');
    for rec in cur loop
        exit when cur%notfound;
        dbms_output.put_line(rec.eno||'    '||rec.ename||'    '||rec.salary||'    '||rec.commission);
    end loop;
end;
/