1026_PL/SQL(변수, 조건문, 반복문)

PL/SQL

 

<기본 형식>

declare 
-변수를 정의
begin 
-SQL구문 작성
-출력구문 작성  --> 쿼리문의 수행결과를 반드시 출력함수를 통해서 확인해야 한다.
exception
-예외 처리 구문

end; 

 

<변수 및 상수 선언>

 

- decleare 선언부 블럭내에서만 가능

- 상수 선언시 초기값 필수 할당

- 변수 미 할당시 데이터타입 상관없이 초기값 null 로 지정

 

- 변수 :

변수명 데이터타입 := 초기값;

ex_num numnber := 1;
ex_str varchar(10) := 'str1';

- 상수 :

상수명 constrant 데이터타입 := 상수값;

ex_num constant number := 1;

- 출력 예 :

DECLARE
EX_NUM CONSTANT NUMBER := 10; --상수 선언
EX_STR VARCHAR2(10); --변수 선언

BEGIN
EX_STR := 'EXAMPLE'; --변수 초기값 설정
DBMS_OUTPUT.PUT_LINE(EX_NUM); --상수 출력
DBMS_OUTPUT.PUT_LINE(EX_STR); --변수 출력
END;

10
EXAMPLE

 

 

<데이터 조회>

- 초기세팅

set serveroutput on;

- 출력 명령어

DBMS_OUTPUT.PUT_LINE(변수,상수 명)

- 출력 예시

begin
          dbms_output.put_line('Hello World'); --출력함수 (자바의 sysout구문과 같음.)
end;

 

 

<PL/SQL 구문 변수 활용과 선언 및 초기화 >

declare
--            vempno number(4);  --변수의 선언
--            vename varchar2(10);

                vempno constant number(4) := 7777;      --변수의 선언과 동시에 초기화
                vename varchar2(10) not null  := 'SCOTT';  --not null을 붙이면 null값을 변수의 값으로 사용할 수 없다.
begin
--            vempno := 7777;       --변수의 초기화 / := 콜론는 은 같이 붙여서 사용해야 한다.
--            vename := 'SCOTT';
            
            dbms_output.put_line('사원 / 이름');
            dbms_output.put_line(vempno || ' ' || vename);
end;

<PL/SQL 구문 변수 활용과 선언 및 초기화 >

DECRARE

 

1. 단순변수 

 

1) 스칼라 방식 : 단일 값을 가지는 변수의 데이터형을 직접 지정해주는 변수 

Identifier [constant] datatype [not null] [:=default expr];

- identifier : 변수명

- constant : 상수 선언 (읽기전용 변수), 초기값 반드시 지정, 값이 없을 경우 생략

- datatype : %type, %rowtype 등

- not null : 초기값 반드시 지정, 생략가능

- := 또는 default : 변수에 기본값 할당

- expr : 변수에 부여할 기본값

 

-주요 스칼라 변수의 데이터 타입

CHAR(최대길이)	고정길이의 문자 (최소값 : 1)
VARCHAR2(최대길이)	가변 길이의 문자 (기본값 없음)
NUMBER(전체자리수, 소수점이하 자리수)	전체자리수와 소수점 이하의 자리수를 가진 숫자  전체 범위 : 1 ~ 38 소수점 이하 자리수 범위 : -84 ~ 127
BINARY_INTEGER	-2,147,483,647 ~ 2,147,483,647 사이의 정수
PLS_INTEGER	-2,147,483,647 ~ 2,147,483,647 사이의 부호있는 정수 NUMBER 보다 저장공간이 적게 필요하고 연산속도가 빠름

 

예시)

Vno number(5);
Vname varchar2(10);
Vday date;

 

 

 

2) 레퍼런스 방식 (참조 변수) : 변수의 데이터 형을 다른 컬럼에서 참조 후 지정하는 방식

저장되어야 할 정확한 데이터 형태를 모를경우 해당 데이터가 들어있는 컬럼의 정보를 참조

 

Vno emp.empno%TYPE   - emp테이블의 empno와 동일한 데이터형으로 선언
Vname emp.ename%TYPE - emp테이블의 ename과 동일한 데이터형으로 선언

Vrow emp%ROWTYPE   -emp테이블의 여러컬럼을 한번에 저장할 변수로 선언.

• %TYPE : 기존 테이블의 컬럼의 타입을 참조한다.
• %ROWTYPE : 예로 tno number, tname varchar2(10), tday date로 이루어진 test 테이블이 있는 경우 test%ROWTYPE 으로 선언시 하나의 변수에 세가지 (number, varchar2(10), date) 를 모두 저장 할 수 있는 변수로 선언된다.

-예시

declare  
            --vempno number(4); --스칼라 방식
            vempno emp.empno%type := 7777; --레퍼런스 방식
 begin 
            --vempno := 7777;
            dbms_output.put_line(vempno);
 end;

 

<레퍼런스 방식 활용>

-제약이 크나 PL/SQL 구문이 주는 장점이 있음.

 

-%TYPE

declare 
            vempno emp.empno%type;  
            vename emp.ename%type;
begin            --try 영역   

           select empno,ename 
           into vempno,vename  --empno,ename 을 vempno,vename에 넣어준다.
           from emp;
           --where empno = 7788;
           --select구문을 PL/SQL 구문에 쓸때는 into 절과 where절이 필수로 반드시 들어와야 한다.
           
           dbms_output.put_line('사번 / 이름');
           dbms_output.put_line(vempno || ' ' || vename);
           
exception      --catch 영역       --where절이 없을 경우,  / when-then 구문을 쓴다.
           
           when TOO_MANY_ROWS then dbms_output.put_line('행의 수가 여러 개 입니다.');
           when OTHERS then dbms_output.put_line('모든 예외에 대한 처리');
end;

-%ROWTYPE

declare

v_row emp2%ROWTYPE

Begin

Select * into v_row
From emp3
Where melon = 7300;

 

2.  복합변수 

 

1) Table Type

- 사용자 정의 변수 타입

- 배열의 형식과 같음

 

<형식>

TYPE type_name IS TABLE OF
columntype [not null] 
[index by binary_interger];

- type_name : 동일 유형의 데이터 또는 데이터 구조를 하나의 연속적인 메모리 공간에 확보

- index by : 배열 내의 요소에 접근하기 위한 위치값으로 사용

 

 

 

-사용 예시 1)

사원번호가 7999인 사원의 이름을 조회해 해당 변수에 저장한 후 출력 (Table_type명 : tbl_emp_name)

set serveroutput on;

declare
t_name varchar2(20);

TYPE tbl_emp_name IS TABLE OF --table type 변수 생성
emp.ename%TYPE
INDEX BY BINARY_INTEGER;

v_name tbl_emp_name; --table type 선언

begin
select ename
into t_name
from emp
where empno=7999;

v_name(0) := t_name; --t_name에 있던 사원 ename을 v_name의 1번째 칸에 할당.
dbms_output.put_line(v_name(0);

end;

 

-사용 예시2)

- for 반복문 사용

- emp 테이블에 있는 사원의 이름과 직업 추출하기

declare
            --vename varchar2(10)
            
            TYPE empno_table_type IS TABLE OF emp.empno%type
            INDEX BY BINARY_INTEGER;
            
            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;
            
            TYPE mgr_table_type IS TABLE OF emp.mgr%type
            INDEX BY BINARY_INTEGER;
            
            TYPE hiredate_table_type IS TABLE OF emp.hiredate%type
            INDEX BY BINARY_INTEGER;
            
            TYPE sal_table_type IS TABLE OF emp.sal%type
            INDEX BY BINARY_INTEGER;
            
            TYPE comm_table_type IS TABLE OF emp.comm%type
            INDEX BY BINARY_INTEGER;
            
            TYPE deptno_table_type IS TABLE OF emp.deptno%type
            INDEX BY BINARY_INTEGER;
            
            empnoArr empno_table_type;
            enameArr ename_table_type;  --배열 형식의 변수 선언
            jobArr job_table_type;              --배열 형식의 변수 선언
            mgrArr mgr_table_type;
            hiredateArr hiredate_table_type;
            salArr sal_table_type;
            commArr comm_table_type;
            deptnoArr deptno_table_type;
            
            
            i BINARY_INTEGER := 0;  --변수 i 선언

begin  --반복문 사용
            for k in (select * from emp) loop
                        i := i + 1;  --index번호는 자바 0번 시작과 달리 1번부터 사용된다.
                        empnoArr(i) := k.empno;
                        enameArr(i) := k.ename;  --select구문에서 조회된 k.ename , k.job
                        jobArr(i) := k.job;
                        mgrArr(i) := k.mgr;
                        hiredateArr(i) := k.hiredate;
                        salArr(i) := k.sal;
                        commArr(i) := k.comm;
                        deptnoArr(i) := k.deptno;
                        
            end loop;
            
            for j in 1..i loop 
                        dbms_output.put_line(empnoArr(j) || ' / ' || enameArr(j) || ' / ' || jobArr(j) || ' / ' || mgrArr(j) || ' / ' || hiredateArr(j) || ' / ' || salArr(j) || ' / ' || commArr(j) || ' / ' || deptnoArr(j));
            end loop;

end;
/

 

 

2) Record Type

- 사용자 정의 변수 타입

- 배열의 형식과 같음

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
            --레코드 type(여러개의 변수를 묶어서 사용한다) => 사용자 정의 변수 타입
            --클래스랑 유사하다.
            select empno,ename,job,deptno
            into emp_record
            from emp
            where empno = 7788;
            
            dbms_output.put_line(emp_record.v_empno || ' ' || emp_record.v_ename || ' ' || emp_record.v_job || ' ' || emp_record.v_deptno);
            
end;

 

<데이터 생성 및 삽입 >

 

create table dept_record
as
select * from dept;

declare
            TYPE rec_dept IS RECORD(
                    v_deptno dept_record.deptno%type,
                    v_dname dept_record.dname%type,
                    v_loc dept_record.loc%type
            );
            
            dept_rec rec_dept;  --변수와 테이블명이 같으면 오류남.
begin
            dept_rec.v_deptno := 50;
            dept_rec.v_dname := 'DEV';
            dept_rec.v_loc := 'BUSAN';
            
            insert into dept_record
            values dept_rec;  --values에 (괄호)는 레코드문에서는 안써야 한다.
end;


select * from dept_record;

<UPDATE>

 

declare
            TYPE rec_dept IS RECORD(
                    v_deptno dept_record.deptno%type not null  := 99,
                    v_dname dept_record.dname%type,
                    v_loc dept_record.loc%type
            );
            
            dept_rec rec_dept;
         
begin
            dept_rec.v_deptno := 50;
            dept_rec.v_dname := 'INSA';
            dept_rec.v_loc := 'SEOUL';
            
            update dept_record
             
            set dname = dept_rec.v_dname , loc = dept_rec.v_loc
            where deptno = dept_rec.v_deptno;
            
end;

select * from dept_record;

<DELETE>

 

declare
           v_deptno dept_record.deptno%type := 50;
begin
          delete from dept_record
          where deptno = v_deptno;     
end;

<조건문>

1) IF문

-if then end if

-if then else end if
-if then elsif then end if

declare 
            vempno number(4);
            vename varchar2(10);
            vdeptno varchar2(10);
            vdname varchar2(10) := null ;

begin
            select empno,ename,deptno
            into vempno,vename,vdeptno
            from emp
            where empno = 7788;
            --oracle에서의 if문 특성
--            if( 조건식 ) then     
--                        실행문
--            end if ;
             if( vdeptno = 10 ) then    
                         vdname := 'AAA';
            end if ;
            
            if( vdeptno = 20 ) then     
                         vdname := 'BBB';
            end if ;
            
            if( vdeptno = 30 ) then     
                         vdname := 'CCC';
            end if ;
            
            if( vdeptno= 40 ) then    
                         vdname := 'DDD';
            end if ;
            
            dbms_output.put_line('부서명 : ' || vdname);

end;

 

-문제 1-1 if-then / end if 문

declare
            vemp emp%rowtype;
            annsal number(7,2);

begin 
            dbms_output.put_line('사번 / 이름 / 연봉');
            dbms_output.put_line('------------- ');
            
            select *
            into vemp 
            from emp
            where empno = 7788;
            
            --Q1.
            --해당 사원의 연봉을 출력하라. 단, 커미션이 null인 경우 0으로 출력하라.
            --계산된 연봉을 변수 annsal에 넣어서 출력하라.
            
             if( vemp.comm is null ) then     
                         vemp.comm := 0;
            end if ;
            
            annsal := vemp.sal*12 + vemp.comm;
          
            dbms_output.put_line('사번 : ' || vemp.empno ||' '|| '이름 : ' ||vemp.ename ||' '||'연봉 : ' || annsal ||'만원');     
end;

-문제 1-2  if-then / else if 문  

declare
            vemp emp%rowtype;
            annsal number(7,2);

begin 
            dbms_output.put_line('사번 / 이름 / 연봉');
            dbms_output.put_line('------------- ');
            
            select *
            into vemp 
            from emp
            where empno = 7788;
            
             if( vemp.comm is null ) then     
                        annsal := vemp.sal*12;
            else
                        annsal := vemp.sal * 12 + vemp.comm;
            end if ;
          
            dbms_output.put_line('사번 : ' || vemp.empno ||' '|| '이름 : ' ||vemp.ename ||' '||'연봉 : ' || annsal ||'만원');     
end;

-다중 if문

declare
            vemp emp %rowtype;
            vdname varchar2(10);
begin
            select *
            into vemp
            from emp
            where empno = 7788;
            
            if(vemp.deptno = 10) then
                    vdname := 'AAA';
            elsif (vemp.deptno = 20) then  --elsif =else if 의 오라클 특성
                    vdname := 'BBB';
            elsif (vemp.deptno = 30) then  
                    vdname := 'CCC';
            elsif (vemp.deptno = 40) then  
                    vdname := 'DDD';
            end if;
            
            dbms_output.put_line(vdname);        
end;

 

<반복문>

-loop end loop;
-for in loop end loop;  
-for in reverse loop end loop; 
-while loop end loop;

 

 

 

1) LOOP문

 

- 형식

loop
     실행문( 무한 반복문 )
     무한 반복문의 제어
     1. EXIT WHEN 조건식;
     2. IF THEN END IF;
end loop;

- 예시)

declare
            n number := 1;
begin
            loop 
                    dbms_output.put_line(n);
                    n := n +1;                     
                    exit when n > 10;  --반복문을 멈추기 위한 조건
            end loop;
end;

 

2) FOR문
 

-형식

for 증감변수 in 초기값..최종값
loop
처리문;
end loop;

- 예시)

declare
            --변수 선언 필요 없음
begin
            --in 구문 뒤에 작성되는 값이 반복의 횟수를 결정한다.
            --in : 증가
            
            for n in 1..10 
            loop
            dbms_output.put_line(n);
            end loop;
end;

  -in 뒤에있는 값은 시작값..끝값 1씩 증가  / 1~10까지 10회 반복하는 것.

 

 

2) FOR문-reverse (for문이 거꾸로 시작)

 

begin
            --in reverse : 감소
            for n in reverse 1..10 loop  --in 뒤에있는 값은 시작값..끝값 1씩 감소
            dbms_output.put_line(n);
            end loop;
end;

declare
            vdept dept %rowtype;
begin
            for n in 1..4 loop
                    select *
                    into vdept
                    from dept
                    where deptno = 10 * n ;  --where절이 없으면 오류로 판단한다.
            dbms_output.put_line(vdept.deptno || '/ ' || vdept.dname || '/ ' || vdept.loc);
            
            end loop;
end;

3) WHILE문

 

-형식

declare
            n number := 1;
begin
            while (조건식) 
            loop            
            end loop;
end;

- 예시

declare
            n number := 1;
begin
            while (n <= 10) loop
            dbms_output.put_line(n);
            n := n + 1;
            end loop;
end;

 

 

https://rosebud90.tistory.com/entry/15-PLSQL-%EB%B3%80%EC%88%98 

 

--정리
--스칼라 방식
--레퍼런스 방식
--    1. emp.empno%type
--    2. emp%rowtype

--사용자 정의 변수 타입
--    1. 테이블 type
--                   -TYPE empno_table_type IS TABLE OF emp.empno%type
--                   -INDEX BY BINARY_INTEGER;
--    2. 레코드 type
--                   -TYPE rec_dept IS RECORD(
--                   -v_deptno dept_record.deptno%type not null  := 99,
--                   -v_dname dept_record.dname%type,
--                   -v_loc dept_record.loc%type
--                   - );

 

%ROWTYPE

declare
            --%ROWTYPE : 테이블의 모든 컬럼의 이름과 변수를 참조하겠다.
            --컬럼명이 변수명으로 사용되고 컬럼의 타입을 변수의 타입으로 사용한다.
            
            vemp emp%rowtype;         
            
begin
            select *
            into vemp  --%rowtype으로 정의된 타입
            from emp
            where empno = 7788;
            
            dbms_output.put_line(vemp.empno);  --empno : 7788을 가진 사번 정보 조회
            dbms_output.put_line(vemp.ename); 
            dbms_output.put_line(vemp.job);
            dbms_output.put_line(vemp.mgr);
            dbms_output.put_line(vemp.hiredate);
            dbms_output.put_line(vemp.sal);
            dbms_output.put_line(vemp.comm);  
            dbms_output.put_line(vemp.deptno);
            
end;