Procedure(프로시져)

● hello world 출력하기 

set serveroutput on

begin

dbms_output.put_line('hello world');

end;

/

● 변수 사용하기 (대소문자 구분함)

set serveroutput on

declare

vempno number(4);

vename varchar(10);

begin

vempno := 7788;

vename := 'SCOTT';

DBMS_OUTPUT.PUT_LINE('사번/이름');

DBMS_OUTPUT.PUT_LINE('-------------');

DBMS_OUTPUT.PUT_LINE(vempno||'/'||vename);

end;

/

찾아 보기 - vemp emp%rowtype; 

//exam02

set serveroutput on

declare

-- %type 속성으로 컬럼 단위 레퍼런스 변수 선언

VEMPNO emp.empno%type;

VENAME emp.ename%type;

begin

DBMS_OUTPUT.PUT_LINE('사번/이름');

DBMS_OUTPUT.PUT_LINE('--------------------');

SELECT EMPNO, ENAME INTO VEMPNO, VENAME

FROM EMP

WHERE ENAME='SCOTT';

--레퍼런스 변수에 저장된 값을 출력한다.

DBMS_OUTPUT.PUT_LINE(VEMPNO || '/' || VENAME);

end;

/

//exam03

declare

vdeptno dept.deptno%type;

vloc dept.loc%type;

begin

select deptno, loc into vdeptno, vloc

from dept

where deptno=10;

DBMS_OUTPUT.PUT_LINE('부서번호/지역');

DBMS_OUTPUT.PUT_LINE('--------------------');

DBMS_OUTPUT.PUT_LINE(vdeptno||'/'||vloc);

end;

/

● table 변수 사용하기

set serveroutput on

declare

-- 테이블 타입 정의

type ename_table_type is table of emp.ename%type

index by binary_integer;

type job_table_type is table of emp.job%type

index by binary_integer;

-- 테이블 타입으로 변수 선언

ename_table ename_table_type;

job_table job_table_type;

i binary_integer :=0;

begin

-- emp 테이블에서 사원이름과 직급을 얻어옴

for k in (select ename, job from emp)

loop

i := I+1;

ename_table(i) := k.ename;

job_table(i) := k.job;

end loop;

-- 테이블 변수에 저장된 내용을 출력 

for j in 1..i

loop

dbms_output.put_line(rpad(ename_table(j),8) || '/' || rpad(job_table(j),9));

end loop;

end;

/

● 레코드 타입(RECORD TYPE) 사용하기

set serveroutput on

declare

-- 레코드 타입을 정의

type emp_record_type is record(

v_empno    emp.empno%type,

v_ename    emp.ename%type,

v_job        emp.job%type,

v_deptno    emp.deptno%type

);

-- 레코드로 변수 선언 

emp_record emp_record_type;

begin

-- scott 사원의 정보를 레코드 변수에 저장

select empno, ename, job, deptno 

into emp_record

from emp

where ename = upper('scott');

-- 레코드 변수에 저장된 사원 정보를 출력

DBMS_OUTPUT.PUT_LINE('사원번호:' || TO_CHAR(emp_record.v_empno));

DBMS_OUTPUT.PUT_LINE('이    름:' || emp_record.v_ename);

DBMS_OUTPUT.PUT_LINE('담담업무:' || emp_record.v_job);

DBMS_OUTPUT.PUT_LINE('부서번호:' || emp_record.v_deptno);

end;

/

● IF문 이용하기 

set serveroutput on

declare

vempno number(4);

vename varchar2(20);

vdeptno emp.deptno%type;

vdname varchar2(20) := null;

begin

select empno, ename, deptno 

into vempno, vename, vdeptno

from emp

where empno=7788;

if(vdeptno =10) then vdname := 'ACCOUNTING';

end if;

if(vdeptno =20) then vdname := 'RESEARCH';

end if;

if(vdeptno =30) then vdname := 'SALES';

end if;

if(vdeptno =40) then vdname := 'OPERATIONS';

end if;

DBMS_OUTPUT.PUT_LINE('사번 이름 부서명');

DBMS_OUTPUT.PUT_LINE(vempno||' '||vename||' '||vdname);

end;

/

탄탄 다쥐기 

declare

vemp emp%rowtype;

annsal number(7,2);

begin

dbms_output.put_line('사번/이름/연봉');

dbms_output.put_line('----------------------------------');

select * into vemp from emp where ename='SCOTT';

if(vemp.comm is null) then annsal := vemp.sal*12;

end if;

if(vemp.comm is not null) then annsal := vemp.sal*12*vemp.comm;

end if;

dbms_output.put_line(vemp.empno||'/'||vemp.ename||'/'||annsal);

end;

/

●예외 처리 하기 

set serveroutput on

declare

vemp emp%rowtype;

vempno emp.empno%type :=7000;

begin

select * into vemp from emp where empno=vempno;

DBMS_OUTPUT.PUT_LINE('사원 번호 : '|| vemp.empno);

DBMS_OUTPUT.PUT_LINE('사원 이름 : '|| vemp.ename);

DBMS_OUTPUT.PUT_LINE('사원 번호 : '|| vemp.sal);

EXCEPTION

when NO_DATA_FOUND THEN 

DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러 발생');

when OTHERS THEN

DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

end;

/

● 데이터 입력 받기 

set serveroutput on

set verify on

accept p_empno prompt '사원번호 입력하세요 :'; 

declare

vemp emp%rowtype;

vempno emp.empno%type := &p_empno;

begin

select * into vemp from emp where empno=vempno;

DBMS_OUTPUT.PUT_LINE('사원 번호 : '|| vemp.empno);

DBMS_OUTPUT.PUT_LINE('사원 이름 : '|| vemp.ename);

DBMS_OUTPUT.PUT_LINE('사원 번호 : '|| vemp.sal);

EXCEPTION

when NO_DATA_FOUND THEN 

DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러 발생');

when OTHERS THEN

DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

end;

/

● LOOP SQL 반복문

--IF 문으로 빠져 나오기

set serveroutput on

declare

n NUMBER := 1;

BEGIN

loop

DBMS_OUTPUT.PUT_LINE(n);

N := N + 1;

IF N > 500 THEN

EXIT;

END IF;

END loop;

end;

/

---------------------------------------------------------------

-- EXIT WHEN 문

set serveroutput on

declare

n NUMBER := 1;

BEGIN

loop

DBMS_OUTPUT.PUT_LINE(n);

N := N + 1;

EXIT when N>5;

END loop;

end;

/

---------------------------------------------------------------

-- 5단 출력하기 

set serveroutput on

declare

dan number := 5;

i number := 1;

begin

loop

DBMS_OUTPUT.PUT_LINE(dan || 'x' || i ||'='||(dan*i));

i := i + 1;

exit when i > 9;

end loop;

end;

/

-----------------------------------------------------------------------------------

탄탄다지기 

set serveroutput on

declare

dan number := 5;

i number := 1;

begin

loop

DBMS_OUTPUT.PUT_LINE(dan || 'x' || i ||'='||(dan*i));

i := i + 1;

if(i > 9) then exit;

end if;

end loop;

end;

/

-----------------------------------------------------------------------------------

입력 받아 구구단 출력하기 

set serveroutput on

accept dan prompt '단을 입력하세요 :'; 

declare

dan number := &dan;

i number := 0;

begin

loop

DBMS_OUTPUT.PUT_LINE(dan || 'x' || i ||'='||(dan*i));

i := i + 1;

if(i > 9) then exit;

end if;

end loop;

end;

/

-----------------------------------------------------------------------------------

탄탄다지기

set serveroutput on;

declare

 vdept dept%rowtype;

 cnt number := 1;

begin

DBMS_OUTPUT.PUT_LINE('부서번호/부서명/지역명');

DBMS_OUTPUT.PUT_LINE('------------------------------------------');

-- 변수 cnt는 1부터 1씩 증가하다 4에 도달하면 끝

for cnt in 1..4 loop

select * into vdept

from dept

where deptno=10*cnt;

DBMS_OUTPUT.PUT_LINE(vdept.deptno||'/'||vdept.dname||'/'||vdept.loc);

end loop;

end;

/

● 테이블 타입 변수 사용하여 출력

SET SERVEROUTPUT ON

DECLARE

  -- 테이블 타입을 정의 

  TYPE  TABLE_TYPE IS TABLE OF DEPT%ROWTYPE

    INDEX BY BINARY_INTEGER;

  -- 테이블 타입으로 변수 선언

  DEPT_TABLE   TABLE_TYPE;

  

  I BINARY_INTEGER := 0;

  J BINARY_INTEGER := 0;

BEGIN  

  -- EMP 테이블에서 사원이름과 직급을 얻어옴

  DBMS_OUTPUT.PUT_LINE('부서번호/직업이름/지역');

  FOR  K IN (SELECT * FROM DEPT) 

   LOOP

    I := I + 1;                

    DEPT_TABLE(I) := K; 

   END LOOP;

  FOR J IN 1..I 

   LOOP          

    DBMS_OUTPUT.PUT_LINE(DEPT_TABLE(J).DEPTNO||'/'||DEPT_TABLE(J).DNAME||'/'||DEPT_TABLE(J).LOC);

   END LOOP;

  

END;

/

● while 문 샘플 

- 기본형 

set setveroutput on

declare

n number :=1;

begin

while n <=5

loop

DBMS_output.put_line(n);

n := N+1;

end loop;

end;

/

- 탄탄다지기 별로 피라미드 

set serveroutput on

declare 

v_cnt number :=1;

v_str varchar(10) := null;

begin

while v_cnt <= 5 loop

v_str := LPAD(' ',v_cnt,'*');

-- v_str := v_str || '*';

DBMS_OUTPUT.PUT_LINE(V_STR);

v_cnt := v_cnt + 1;

end loop;

end;

/

- 탄탄 다지기 부서별 값 출력 

-- v1

declare

   v_deptno number := &deptno;

   v_allsal number := 0;

begin

   for k in (select * from emp where deptno = v_deptno)

   loop

           v_allsal := v_allsal + k.sal;

   end loop;

   if v_allsal = 0

   then

   DBMS_OUTPUT.PUT_LINE('해당 부서의 사원은 없습니다.');

   else

   DBMS_OUTPUT.PUT_LINE('부서' ||v_deptno||'의 월급총합'|| v_allsal );

   end if;

end;

-- v2 

set serveroutput on

set verify off

accept deptno prompt '부서번호를 입력하세요:'; 

declare 

v_deptno number := &deptno;

v_allsal number := 0;

begin

select sum(sal) into v_allsal 

from emp 

where deptno = v_deptno group by deptno;

DBMS_OUTPUT.PUT_LINE('부서'||v_deptno||'의 월급 총합:'||v_allsal);

EXCEPTION

when NO_DATA_FOUND THEN 

DBMS_OUTPUT.PUT_LINE('해당 부서의 사원이 없습니다.');

when OTHERS THEN

DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

end;

/

-- v3 

set serveroutput on

set verify off

accept deptno prompt '부서번호를 입력하세요:'; 

declare 

v_deptno number := &deptno;

v_allsal number := 0;

begin

select sum(sal) into v_allsal 

from emp 

where deptno = v_deptno;

if v_allsal is null then 

DBMS_OUTPUT.PUT_LINE('해당 부서의 사원이 없습니다.');

else

DBMS_OUTPUT.PUT_LINE('부서'||v_deptno||'의 월급 총합:'||v_allsal);

end if;

EXCEPTION

when NO_DATA_FOUND THEN 

DBMS_OUTPUT.PUT_LINE('해당 부서의 사원이 없습니다.');

when OTHERS THEN

DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

end;

/

●참고 : SELECT를 사용할경우  NO_DATA_FOUND를 반환 하나, 함수를 사용 할 경우 null값을 반환한다. 

https://stackoverflow.com/questions/30797126/exception-is-not-coming-with-sql-max-function