190603_DAY38 <복습>, <조건문>,<반복문>, <Stored Procedure>, <Stored Function>

용진용진 2019. 6. 3. 19:26

2019. 6. 3. 19:26

190603_DAY38 <복습>, <조건문>,<반복문>, ,

SQL언어에 절차적인 프로그래밍 언어 요소(조건문,반복문)를 가미해 만든 것
여러개의 SQL문(DQL,DML) 사이에 연결관계를 표현

select 컬럼명 into 변수명

from 테이블명

if 변수명 < 10
then insert into~ end if;

declare -- 선언부

--   실행부에서 사용하는 변수에 대한 선언!!
    a number(4); -- 변수선언
    b emp.ename%type; -- 변수선언( 존재하는 테이블안의 컬럼이 갖는 자료형을 참조 )
    c number(2) := 30;

begin --실행부 시작
    a := 300;

    SQL문1 작성
    SQL문2 작성
    SQL문3( DQL, DML ) 작성--------1번 실행
           ---- 주의 select empno, ename, sal into a, b, c 
               --        from emp;
               dbms_output.put_line(empno); -- X ==> 조회된 컬럼명 empno 인식 불가
               dbms_output.put_line(a); -- O ==> 조회된 사원번호를 begin~end 사이에서 a변수로 사용!

            if(조건식)
                then SQL문4; -- 조건식 참일때 -----0,1번 실행
            end if; (if문 종결문, {} 못씀)

            for()
            {
                SQL문5; -- 0 ~ 무한대 실행
            }
end; -- 실행부 끝
/

<조건문> begin~end사이에 들어갈 수 있는

IF문

형식

IF 조건식
    THEN --시작
        조건식 결과가 참일 때 실행할 (SQL)문장;
END IF; -- 끝

IF ~ ELSE문

형식

IF 조건식
    THEN 조건식 결과가 참일 때 실행할 문장;
    ELSE 조건식 결과가 거짓일 때 실행할 문장;
END IF;

문제

문제) 특정사원이 커미션을 받는지 안 받는지 구분해서 출력하시오
    예1 : 사번 7788은 SCOTT사원이고 커미션을 받지 않습니다.

    예2 : 사번 7654는 MARTIN사원이고 커미션을 1400 받습니다.
    => IF_ELSE_TEST.SQL

declare 
    vEMPNO emp.empno%type;
    vENAME emp.ename%type;
    vCOMM emp.comm%type;

begin
    select empno, ename, comm into vEMPNO, vENAME, vCOMM -- v는 변수명
    from emp
    where empno = 7654;

    IF (vCOMM IS NOT NULL)
                THEN
                    dbms_output.put_line( vEMPNO ||'은(는) '||vENAME ||'사원이고 커미션을' || vCOMM ||'받습니다.');                    
                ELSE
                    dbms_output.put_line( '커미션 안받음' );
    END IF;
end;
/

문제) 특정사원 한 명의 사원번호, 사원명, 연봉을 출력하시오.
     (연봉계산은 급여*12+comm)

    ==> where empno=7788;   where empno=7654;
    ==> 예) 사번 7788은 SCOTT사원이고 연봉은 36000입니다.

declare 
    vempno emp.empno%type;
    vename emp.ename%type;
    vsal emp.sal%type;
    vcomm emp.comm%type;

    annsal number;
begin
    select empno, ename, sal, comm into vempno, vename, vsal, vcomm
    from emp
    where empno = 7788;

     IF(vcomm is null)
         then
             annsal := vsal * 12 + 0;
         else
             annsal := vsal * 12 + vcomm;
     END IF;
             dbms_output.put_line('사번' || vempno || '은(는) ' || vename || 
                                    '사원이고 ' || '연봉은' || annsal || ' 입니다.' );
 end;
 /
 ```

IF ~ ELSIF ~ ELSE 문

형식

IF (조건식1)
    THEN 조건식1 참일때 실행할 문장;
    ELSIF (조건식2)
        THEN 조건식1이 거짓이고 조건식2 참일때 실행할 문장
    ELSIF (조건식3)
        THEN 조건식1,2가 거짓이고 조건식3 참일때 실행할 문장
    ELSE 조건식1,2,3 이 거짓일 때 실행할 문장
END IF;

문제

문제) 특정사원의 사원번호, 사원명, 소속된 부서명을 출력하시오
예 : 사번 7788은 SCOTT 사원이고 RESEARCH 부서에 근무합니다.
if_elsif_test.sql

declare
    vempno emp.empno%type;
    vename emp.ename%type;
    vdeptno emp.deptno%type;
    vdname dept.dname%type;

begin
    vempno := 7788;

    select ename, deptno into vename, vdeptno
    from emp
    where empno = vempno; -- 여기서 empno 써서 select에 안써도 됨.

    if (vdeptno = 10)
        then vdname := 'ACCOUNTING';
    elsif (vdeptno = 20)
        then vdname := 'RESEARCH';
    elsif (vdeptno = 30)
        then vdname := 'SALES';
    else
        vdname := 'OPERATION';
    end if;    

    dbms_output.put_line('사번' || vempno || '은(는) ' ||
                                    vename || '사원이고 ' || vdname || '부서에 근무합니다.');

end;
/

<반복문>

LOOP (반복문)

형식

LOOP
    반복실행할 문장1;
    반복실행할 문장2;
    반복실행할 문장3;
    EXIT [WHEN 조건식]; -- 탈출조건
END LOOP;

문제

문제) 1부터 10까지 출력하세요
loop_test.sql
declare

    i number := 0;

begin
    loop
        i := i + 1;
        dbms_output.put_line( i || '번째  안녕~!' );
        exit when i = 10;
    end loop;
end;
/

문제2) 화면에 3, 6, 9를 출력하시오
declare

    j number := 0;

begin
    loop
        j := j + 3;
        dbms_output.put_line( j );
        exit when j = 9;
    end loop;
end;
/

FOR LOOP

형식

FOR 변수 IN 최소값.. 최대값
LOOP
    반복 실행할 문장;
END LOOP;

문제

문제) 화면에 '나는 SQL이 좋아요!! '를 10번 출력하시오.
for_test.sql

declare

begin
    FOR i in 1..10
    LOOP
    dbms_output.put_line('나는 딩꼬가 좋아요!!==> ' || i);
    END LOOP;

    dbms_output.put_line('++++++++++++++++++++++++');
    FOR i in 10..1
    LOOP
    dbms_output.put_line('10부터 1까지는 에러는 없지만 출력도 안되네');
    END LOOP;

    dbms_output.put_line('++++++++++++++++++++++++');
    FOR i in reverse 1..10
    LOOP
    dbms_output.put_line('in 뒤에 reverse 사용해서 10부터 1까지' || i);
    END LOOP;
end;
/

문제) 3단에서 5단까지 출력하시오.
for_loop 사용 
for_dan.sql

declare

begin
    FOR dan in 3..5
    LOOP
        dbms_output.put_line('< ' || dan || '단 >');
        FOR i in 1..9
        LOOP
         dbms_output.put_line( dan || ' X ' || i || ' = ' || dan*i);

    END LOOP;
    END LOOP;

end;
/

WHILE LOOP

형식

WHILE 조건식
LOOP
    반복실행문장;
END LOOP;

문제

문제) 1부터 10까지의 합을 출력하시오
declare
    i number := 1;
    total number := 0;
begin
    while i <= 10
    loop
        total := total+i;
        i := i+1;
    end loop;

    dbms_output.put_line('total = ' || total);
end;
/

<Stored Procedure'> ★저장 프로시저

자주사용되는 DQL, DML 을 모듈화 시켜서 DB에 저장하였다가 필요할 때 호출해서 사용!
JAVA의 메소드와 같은 역할

형식)

CREATE [OR REPLACE] PROCEDURE 프로시저명( 변수명1 [IN|OUT] 자료형, 변수명2 자료형)
IS
    변수선언;
BEGIN
    실행할 문장;
    실행할 문장;
    실행할 문장;
    --초기화, SQL문, 함수호출, 절차적요소 ( 조건문, 반복문 )
END;
/

실행방법)
```
EXECUTE 프로시저명();
```

문제)

문제) 부서테이블 (dept3)에서 40번 부서를 삭제하시오 ( 프로시저 사용 )
- pro_test.sql

drop table dept3;

create table dept3
as select * from dept;
---
create or replace procedure del_dept

is
    -- 실행부에서 사용 할 변수 선언!
begin

    delete from dept3
    where deptno = 40;

    dbms_output.put_line('#40번 부서를 삭제하였습니다.');

end;
/
---
--하고 끝나는것이 아니라 프로시저를 실행해야 한다
EXTCUTE del_dept();

--이제 20번 부서 지우고 싶은데
--그럼 또 재정의하고, 등록하고 삭제해야해?!...

drop table dept3;

create table dept3
as select * from dept;

--다시 해보자!
create or replace procedure del_dept(delNo number)
is
    -- 실행부에서 사용 할 변수 선언!
begin
    delete from dept3
    where deptno = delNo;
    dbms_output.put_line('#40번 부서를 삭제하였습니다.');
end;
/
--
--이제 프로시저 등록 후 
-- 실행할 때, 
EXECUTE del_dept(원하는 deptno); --하면 
--원하는 dept3 테이블의 deptno 컬럼이 지워진다.
--=====================================================================

-- 0603/pro_test.sql (40번 부서 삭제)
-- 0603/pro_test.sql (특정 부서 삭제)

--create or replace procedure del_dept
-- 호출?  exec del_dept  또는   exec del_dept()
--create or replace procedure del_dept(delNo number)
-- 호출?  exec del_dept(40)
create or replace procedure del_dept(delNo in number, tomorrow out varchar2)
-- 변수 선언  variable tom  varchar2(15);
-- 바인드 변수(전달 변수) 사용==>  :변수명
-- 호출?  exec del_dept(40, :tom)
-- 확인   print tom;
is
  -- 실행부에서 사용할 변수 선언!!

begin
    tomorrow := '토요일';
  /*    
   delete from dept3
   where deptno=40;
   dbms_output.put_line('#40번 부서를 삭제하였습니다!!');    
  */
   delete from dept3
   where deptno=delNo;
   dbms_output.put_line('#'||delNo||
                             '번 부서를 삭제하였습니다!!');    
   commit;

end;
/

show errors;

variable tom varchar2(15);
exec del_dept(40, :tom);

--============================================================
문제 ) select 문을 통해 tomorrow에 오늘의 요일의 아웃되도록

create or replace procedure del_dept(delNo in number, tomorrow out varchar2)

is

begin
   delete from dept3
   where deptno=delNo;
   dbms_output.put_line('#'||delNo||
                             '번 부서를 삭제하였습니다!!');    
   commit;

--   select to_char(sysdate, 'yy/mm/dd') into -- 년월일 
   select to_char(sysdate, 'day') into tomorrow -- 년월일 
   from dual;
end;
/

show errors;

1559542009180

문제) 사원테이블 (emp3)에서 특정 사원명을 (매개변수) 입력을 통해 삭제하는 프로시저를 작성하고 현재 사원수를 출력하시오

drop table emp3;
create table emp3
as select empno, ename, sal
from emp;

--> pro_test2.sql
--> 프로시저 명 : del_ename

create or replace procedure del_ename(deleName in varchar2)

is
    cnt number;
    total number;
begin
    --사원이름 조회 유1 무0
    select count(ename) into cnt 
    from emp3
    where ename = delename;

    if cnt>0 -- 존재한다면
        then
        delete from emp3
        where ename = deleName;

        commit;

        select count(*) into total
        from emp3;

        dbms_output.put_line('#'|| deleName ||' 사원이 삭제되었습니다. 총 사원은  '|| total ||'입니다.');

    else
        select count(*) into total
        from emp3;
        dbms_output.put_line('#'|| deleName ||' 사원이 존재하지 않고. 총 사원은  '|| total ||'입니다.');

  end if;

end;
/

show errors;

--============================================================
개선1) if then과 else select 문을 프로시저 내에 한번만 정의해서 출력
select count(*) into total
from emp3;

개선2)
del_ename('ADAMS')
del_enmae('adams') 상관없이 일치하는 사원 삭제

create or replace procedure del_ename(deleName in varchar2)
is
cnt number;
total number;
begin
--사원이름 조회 유1 무0
select count(ename) into cnt
from emp3
where upper(ename) = upper(delename);

  select count(*) into total
  from emp3;

  if cnt>0 -- 존재한다면
      then
      delete from emp3
      where lower(ename) = lower(deleName);

      commit;

      dbms_output.put_line('#'|| deleName ||' 사원이 삭제되었습니다. 총 사원은  '|| (total-1) ||'입니다.');

  else
      dbms_output.put_line('#'|| deleName ||' 사원이 존재하지 않고. 총 사원은  '|| total ||'입니다.');


  end if;

end;
/

show errors;




------

<Stored Function'> 저장함수

- 실행(함수 호출) 후 결과를 되돌려 받을 수 있는 return 데이터; 를 반드시 명시해야 함!

- 형식

  ```SQL
  CREATE OR REPLACE FUNCTION 함수명( 매개변수 선언 )
  RETURN 자료형
  IS
      변수선언
  BEGIN
      (순차적으로) 실행할 SQL문
      ...
      RETURN 데이터;
  END;
  /

사용법

1. CREATE FUNCTION~ (함수를 DB에 저장) --함수 생성
2. VARIABLE 바인드 변수명; --변수 선언 
3. EXEC[UTE] : 바인드변수명 := 함수명() -- 함수호출, 결과값 저장
4. PRINT 바인드변수명; --저장값 확인

참고

      void hello(String  name){
         System.out.println("안녕,"+name);
      }       
           hello();
      -----> Stored Procedure (리턴키워드 없고)

      String hi( ){
         return "좋은 하루~!!";
      }      
          String msg =   hi();
      -----> Stored Function (리턴키워드 있고)

문제

문제)
특정 사원에게 보너스를 지급하려고 하는데 급여의 200%에 해당하는 비용을 지급하려 한다. 이 보너스 금액을 FUNCTION을 통해 구하시오
--> fun_test.sql
--> 함수명 : call_bonus('SMITH');
CREATE OR REPLACE FUNCTION call_bonus( vename emp.ename%type )
return number
IS

    sal emp.sal%type;
    bonus number;
BEGIN
    SELECT sal INTO sal -- 컬렴명 sal, 변수명 vsal
    FROM emp
    WHERE ename = vename;
    --SELECT 문의 ';' 끝난 후 컬럼명은 사라지고 변수명만 남는다.
    --SELECT는 죽어서 변수명을 남긴다!

    --보너스 계산
    bonus := sal*2;

    return bonus; --프로시저와 다르게 펑션은 반드시 리턴값이 지정되어야 함.
END;
/

--=========================================================

문제) call_bonus2 Function을 정의하고 매개변수로 1.사원명과 보너스 비율을 넣어서 보너스 금액을 리턴하시오

exec :bonus := call_bonus('SMITH', 200)
print bonus

궁금상자

select into가 헷갈려
원본은 있고 대상 테이블 새롭게 생성하려고 할때, 그럼 테이블 create 로 하면 되는데 왜 selet into로 하는거지?...
부분적으로 원본테이블을 가져오려고 할때?
그럼 select into 와 insert into는?
select into는 타겟테이블이 실제로 존재하지 않아도 된다,
반면 insert into는 타겟테이블이 존재해야 하고,

'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글

190610_DAY41 <기존 인메모리 코드에 DB연결 실습 및 연습> (0)	2019.06.10
190504,5_DAY39,40 <복습> <Cursor>, <Package>, <Trigger>, <바인드변수>, <Table>, <Rollup & Cube>, <카티션 곱>, <GROUPING>, <집합연산자>, <JDBC> (0)	2019.06.04
190531_DAY <뷰>, <시퀀스>, <PL/SQL> (0)	2019.05.31
190530_DAY36 트랜잭션, 무결성제약조건, 데이터 딕셔너리 (0)	2019.05.30
190529_DAY35, <복습>, <DDL>, <DML> (0)	2019.05.29

Yong Study