< 함수(Function) >

* 값을 계산하고 결과값을 반환하기 위해서 사용

* 저장 프로시저와의 차이점 

- 입력 매개변수만 사용할 수 있고 리턴 타입을 반드시 지정해야 함

CREATE OR REPLACE FUNCTION 함수이름

[(argument...)]

RETURN datatype

-- Datatype 은 반환되는 값의 datatype 입니다.

IS

   변수 선언

BEGIN

   문장
   return

END;

 

 

-- 함수

create or replace function fn_update_sal(v_empno number)
return number
is
 v_sal number;
begin
  update emp set  sal=sal*1.1 where empno=v_empno;
  select sal into v_sal from emp where empno=v_empno;
  return v_sal;
end;
/

 

 

-- sqlplus 에서만 실행됨
-- A := B A에 B를 대임

-- :변수 => 매개변수

var salary number;
execute :salary  := fn_update_sal(7369);
print salary;

>>>출력

 

SQL> var salary number;
SQL> execute :salary  := fn_update_sal(7369);

PL/SQL procedure successfully completed.

SQL> print salary;

    SALARY
----------
       220

SQL>

-- 질의 안에 DML 작업을 수행할 수 없습니다.

-- select 절에서 사용할 경우 dml 문장을 제거해야 함

select fn_update_sal(7369) from dual;

 

if


if 조건 then

  statements

elsif 조건 then

  statements

else

  statements

end;

 

 

-- select 컬럼 into 변수
-- = equal :=assign

create or replace procedure dept_search(p_empno number)
is
v_deptno number;
begin
    select deptno into v_deptno from emp
    where empno=p_empno;
    dbms_output.put_line('부서코드:' || v_deptno);
      if v_deptno=10 then
        dbms_output.put_line('경리팀');
    elsif v_deptno=20 then
        dbms_output.put_line('연구팀');
    elsif v_deptno=30 then
        dbms_output.put_line('총무팀');
    else
        dbms_output.put_line('기타 부서');
    end if;
end;
/

 

execute dept_search(7499);

< FOR LOOP 문 >

* index : 자동 선언되는 binary_integer 형 변수. 1씩 증가

* reverse 옵션이 사용될 경우 1씩 감소

* IN 다음에는 coursor 나 select 문이 올 수 있음.

FOR index IN [REVERSE] 시작값... END 값 LOOp

    statement 1
    statement 2

    ...

END LOOP:

 

 

< Loop 문 while 문 >

* EXIT : 무조건 LOOP 문 종료

* EXIT WHEN : LOOP 를 빠져 나가는 조건을 제어

LOOP
 PL/SQL statement...
 다른 LOOP를 포함하여 중첩으로 사용 가능
 EXIT [WHEN condition]
END LOOP:

 

 

< WHILE LOOP 문 >

*  FOR 문과 비슷하며 조건이 TRUE 일 경우만 반복되는 LOOP

* 예제

 

while cnt < 10 LOOP

 INSERT INTO emp(empno, ename, hiredate ) values(emp_seq.nextval, 'test', sysdate);
 cnt := cnt+1;

END LOOP;

 

declare

    type ename_table is table of emp.ename%type
        index by binary_integer;
    type sal_table is table of emp.sal%type
        index by binary_integer;
    ename_tab ename_table;
    sal_tab sal_table;
    i binary_integer :=0;
begin
    for emp_list in (select ename, sal from emp ) loop
        i :=i+1;
        ename_tab(i) := emp_list.ename;
        sal_tab(i) :=emp_list.sal;

    end loop;
    for cnt in 1..i loop
        dbms_output.put_line('이름:'||ename_tab(cnt));
        dbms_output.put_line('급여:'||sal_tab(cnt));
     end loop;

end;
/

    

< loop 실습  >

 

declare -- 선언부

    v_cnt number :=9000;
begin
  loop 
    v_cnt :=v_cnt +1;
    insert into emp(empno, ename, hiredate) values
        (v_cnt, 'test'||v_cnt, sysdate);
        exit when v_cnt >= 9100; -- 탈출조건
   end loop;
   dbms_output.put_line(v_cnt-9000|'개의 레코드가 입력되었습니다.');
end;
/