[Day14] Oracle 14 - PL/SQL(procedure, cursor, type, stored function, if/while)

[Day14] Oracle14 [10/17]

[복습문제]
------------------------------------------------------------------------------------
T_MEMBER 회원
T_POLL 설문
T_POLLSUB 설문항목
T_VOTER 투표

1) 설문테이블 생성

CREATE TABLE SCOTT.T_POLL (
   PollSeq NUMBER(4) NOT NULL,
   Question Varchar(256) NOT NULL,
   SDate DATE NOT NULL,
   EDAte DATE NOT NULL,
   ItemCount Number(1) DEFAULT 1 NOT NULL,
   PollTotal NUMBER(4),
   RegDate Date DEFAULT sysdate NOT NULL,
   MemberSEQ NUMBER(4)
)

COMMENT ON TABLE SCOTT.T_POLL IS '설문조사'
COMMENT ON COLUMN SCOTT.T_POLL.PollSeq IS '설문SEQ'
COMMENT ON COLUMN SCOTT.T_POLL.Question IS '질문'
COMMENT ON COLUMN SCOTT.T_POLL.SDate IS '시작일'
COMMENT ON COLUMN SCOTT.T_POLL.EDAte IS '종료일'
COMMENT ON COLUMN SCOTT.T_POLL.ItemCount IS '답변항목수'
COMMENT ON COLUMN SCOTT.T_POLL.PollTotal IS '총참여수'
COMMENT ON COLUMN SCOTT.T_POLL.RegDate IS '작성일'
COMMENT ON COLUMN SCOTT.T_POLL.MemberSEQ IS '작성자(회원SEQ)'

CREATE UNIQUE INDEX SCOTT.PK_T_POLL
   ON SCOTT.T_POLL (
      PollSeq ASC
   )

ALTER TABLE SCOTT.T_POLL
   ADD
      CONSTRAINT PK_T_POLL
      PRIMARY KEY (
         PollSeq
      )

2) 설문항목 테이블 생성

CREATE TABLE SCOTT.T_PollSub (
   PollSubSeq NUMBER(38) NOT NULL,
   Answer Varchar2(100) NOT NULL,
   ACount Number(4),
   PollSeq NUMBER(4) NOT NULL
)

COMMENT ON TABLE SCOTT.T_PollSub IS '설문항목'
COMMENT ON COLUMN SCOTT.T_PollSub.PollSubSeq IS '답변항목SEQ'
COMMENT ON COLUMN SCOTT.T_PollSub.Answer IS '답변항목'
COMMENT ON COLUMN SCOTT.T_PollSub.ACount IS '답변항목선택수'
COMMENT ON COLUMN SCOTT.T_PollSub.PollSeq IS '설문SEQ'

CREATE UNIQUE INDEX SCOTT.PK_T_PollSub
   ON SCOTT.T_PollSub (
      PollSubSeq ASC
   )

ALTER TABLE SCOTT.T_PollSub
   ADD
      CONSTRAINT PK_T_PollSub
      PRIMARY KEY (
         PollSubSeq
      )

3) 투표 테이블 생성

CREATE TABLE SCOTT.T_Voter (
   VectorSeq NUMBER NOT NULL,
   UserName VARCHAR2(20),
   RegDate DATE,
   PollSeq NUMBER(4),
   PollSubSeq NUMBER(38),
   MemberSeq NUMBER(4)
)

COMMENT ON TABLE SCOTT.T_Voter IS '투표자'
COMMENT ON COLUMN SCOTT.T_Voter.VectorSeq IS '투표SEQ'
COMMENT ON COLUMN SCOTT.T_Voter.UserName IS '사용자이름'
COMMENT ON COLUMN SCOTT.T_Voter.RegDate IS '투표일'
COMMENT ON COLUMN SCOTT.T_Voter.PollSeq IS '설문SEQ'
COMMENT ON COLUMN SCOTT.T_Voter.PollSubSeq IS '답변항목SEQ'
COMMENT ON COLUMN SCOTT.T_Voter.MemberSeq IS '회원SEQ'

CREATE UNIQUE INDEX SCOTT.PK_T_Voter
   ON SCOTT.T_Voter (
      VectorSeq ASC
   )

ALTER TABLE SCOTT.T_Voter
   ADD
      CONSTRAINT PK_T_Voter
      PRIMARY KEY (
         VectorSeq
      )

4) 회원 테이블 생성

CREATE TABLE SCOTT.T_Member (
   MemberSeq NUMBER(4) NOT NULL,
   MemberID varchar2(20) NOT NULL,
   MemberPasswd varchar2(20),
   MemberName varchar2(20),
   MemberPhone varchar2(20),
   MemberAddress varchar2(100)
)

COMMENT ON TABLE SCOTT.T_Member IS '회원'
COMMENT ON COLUMN SCOTT.T_Member.MemberSeq IS '회원SEQ'
COMMENT ON COLUMN SCOTT.T_Member.MemberID IS '회원아이디'
COMMENT ON COLUMN SCOTT.T_Member.MemberPasswd IS '비밀번호'
COMMENT ON COLUMN SCOTT.T_Member.MemberName IS '회원명'
COMMENT ON COLUMN SCOTT.T_Member.MemberPhone IS '휴대폰'
COMMENT ON COLUMN SCOTT.T_Member.MemberAddress IS '주소'

CREATE UNIQUE INDEX SCOTT.PK_T_Member
   ON SCOTT.T_Member (
      MemberSeq ASC
   )

ALTER TABLE SCOTT.T_Member
   ADD
      CONSTRAINT PK_T_Member
      PRIMARY KEY (
         MemberSeq
      )

ALTER TABLE SCOTT.T_POLL
   ADD
      CONSTRAINT FK_T_Member_TO_T_POLL
      FOREIGN KEY (
         MemberSEQ
      )
      REFERENCES SCOTT.T_Member (
         MemberSeq
      )

ALTER TABLE SCOTT.T_PollSub
   ADD
      CONSTRAINT FK_T_POLL_TO_T_PollSub
      FOREIGN KEY (
         PollSeq
      )
      REFERENCES SCOTT.T_POLL (
         PollSeq
      )

ALTER TABLE SCOTT.T_Voter
   ADD
      CONSTRAINT FK_T_POLL_TO_T_Voter
      FOREIGN KEY (
         PollSeq
      )
      REFERENCES SCOTT.T_POLL (
         PollSeq
      )

ALTER TABLE SCOTT.T_Voter
   ADD
      CONSTRAINT FK_T_PollSub_TO_T_Voter
      FOREIGN KEY (
         PollSubSeq
      )
      REFERENCES SCOTT.T_PollSub (
         PollSubSeq
      )

ALTER TABLE SCOTT.T_Voter
   ADD
      CONSTRAINT FK_T_Member_TO_T_Voter
      FOREIGN KEY (
         MemberSeq
      )
      REFERENCES SCOTT.T_Member (
         MemberSeq
      )

------------------------------------------------------------------------------------
회원

1) 회원 가입/탈퇴/수정
CREATE SEQUENCE seq_member
INCREMENT BY 1
START WITH 1
MAXVALUE   9999
NOCACHE;

INSERT INTO T_MEMBER ( MemberSeq, MemberID, MemberPasswd, MemberName,MemberPhone, MemberAddress  )
VALUES ( seq_member.nextval,'admin', '1234', '관리자','010-1111-1111', '서울 강남구'  );
INSERT INTO T_MEMBER ( MemberSeq, MemberID, MemberPasswd, MemberName,MemberPhone, MemberAddress  )
VALUES ( seq_member.nextval,'hong', '1234', '홍길동','010-2222-2222', '경기도 남양주'  );
INSERT INTO T_MEMBER ( MemberSeq, MemberID, MemberPasswd, MemberName,MemberPhone, MemberAddress  )
VALUES ( seq_member.nextval,'kim', '1234', '김기수','010-3333-3333', '서울 양천구'  );
COMMIT;

2) 설문 등록(작성) / 수정 / 탈퇴

CREATE SEQUENCE seq_poll;
-- Sequence SEQ_POLL이(가) 생성되었습니다.
INSERT INTO T_POLL (PollSeq,Question,SDate, EDAte , ItemCount,PollTotal, RegDate, MemberSEQ )
VALUES             ( seq_poll.nextval,'좋아하는 여배우?'
                          , TO_DATE( '2022-03-01 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , TO_DATE( '2022-03-15 18:00:00'   ,'YYYY-MM-DD HH24:MI:SS') 
                          , 5
                          , 0
                          , TO_DATE( '2022-02-01 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , 1
                    );

INSERT INTO T_POLL (PollSeq,Question,SDate, EDAte , ItemCount,PollTotal, RegDate, MemberSEQ )
VALUES             ( seq_poll.nextval,'좋아하는 과목?'
                          , TO_DATE( '2022-04-20 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , TO_DATE( '2022-05-01 18:00:00'   ,'YYYY-MM-DD HH24:MI:SS') 
                          , 4
                          , 0
                          , TO_DATE( '2022-04-15 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , 1
                    );

INSERT INTO T_POLL (PollSeq,Question,SDate, EDAte , ItemCount,PollTotal, RegDate, MemberSEQ )
VALUES             ( seq_poll.nextval,'5월 5일 휴강 찬반?'
                          , TO_DATE( '2022-05-01 00:00:00'   ,'YYYY-MM-DD HH24:MI:SS')
                          , TO_DATE( '2022-05-04 18:00:00'   ,'YYYY-MM-DD HH24:MI:SS') 
                          , 2
                          , 0
                          , SYSDATE
                          , 1
                    );
COMMIT;

-- 세부 항목 추가 쿼리 작성

CREATE SEQUENCE seq_pollsub;
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'배슬기', 0, 1 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'김옥빈', 0, 1 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'아이유', 0, 1 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'김선아', 0, 1 );

INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'수학', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'국어', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'영어', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'사회', 0, 2 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'과학', 0, 2 );


INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'예', 0, 3 );
INSERT INTO T_PollSub (PollSubSeq          , Answer , ACount , PollSeq  ) 
VALUES                (seq_pollsub.nextval ,'아니요', 0, 3 );

COMMIT;

-- 설문 수정/삭제

3) 설문 목록 페이지 = 쿼리
-- 번호/질문/작성자/시작일/종료일/항목수/참여수/상태

SELECT pollseq, question, membername , sDate, edate
       , itemcount, polltotal
       , CASE 
            WHEN SYSDATE > edate THEN  '종료'
            WHEN SYSDATE BETWEEN sdate AND edate THEN  '진행중'
            ELSE   '시작전'
         END  state
FROM t_poll  p JOIN t_member m ON p.memberseq = m.memberseq ;

4) [설문 투표] / 수정 / 삭제
-- [설문 투표] 1) + 2) + 3) PL/SQL

-- 1)
INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
VALUES             ( seq_vecter.nextval, '홍길동', SYSDATE,  1,     3             , 2 );
-- 2)
UPDATE t_poll
SET polltotal = polltotal +1
WHERE pollseq = 1;
--3)
UPDATE t_pollsub
SET acount = acount + 1
WHERE pollsubseq = 3;

-- [설문 투표] 1) + 2) + 3) PL/SQL
-- 1)

INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
VALUES             ( seq_vecter.nextval, '김기수', SYSDATE,  1,     2             , 3 );
-- 2)
UPDATE t_poll
SET polltotal = polltotal +1
WHERE pollseq = 1;
--3)
UPDATE t_pollsub
SET acount = acount + 1
WHERE pollsubseq = 3;

-- [설문 투표] 1) + 2) + 3) PL/SQL 트리거( Trigger)

-- 1)
INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
VALUES             ( seq_vecter.nextval, '관리자', SYSDATE,  1,     2             , 1 );
-- 2)
UPDATE t_poll
SET polltotal = polltotal +1
WHERE pollseq = 1;
--3)
UPDATE t_pollsub
SET acount = acount + 1
WHERE pollsubseq = 2;

5) 1번 설문에 대한 투표 결과 보기

-- 1) 
SELECT COUNT(*)
FROM t_voter
WHERE pollseq = 1;
-- 1-2)  polltotal 참여자수
SELECT polltotal
FROM t_poll
WHERE pollseq = 1;

-- 1) 
SELECT COUNT(*)
FROM t_voter
WHERE pollseq = 1;
-- 1-2)  polltotal 참여자수
SELECT polltotal
FROM t_poll
WHERE pollseq = 1;

좋아하는 여배우?   배슬기    0(0%)
좋아하는 여배우?   김옥빈    #################################1(33%)
좋아하는 여배우?   아이유    ###################################################################2(67%)
좋아하는 여배우?   김선아    0(0%)

PL/SQL = Procedural Language extensions to SQL
절차적 언어 확장된 SQL
- IF
- 변수선언 ( 상수, 변수 사용 )
- FOR 문 ( 흐름 제어 )
- 예외처리 ( 오류 처리 )
- procedure / function 정의

1) PL/SQL은 블럭 구조의 언어
- [서브 블럭]을 가질 수 있는 논리적 블럭
- 선언된 블럭 내에서만 유효

2) PL/SQL은 3가지의 블럭 구조
[선언 블럭] - 변수, 상수 선언
[실행 블럭] - DML, DDL, TCL SQL 실행
[예외 처리 블럭] - 생략 가능

형식)

 DECLARE 
   -- 선언블럭
 BEGIN
   -- 실행 블럭 
   -- 1)
    INSERT INTO t_voter (VectorSeq         , UserName,RegDate, PollSeq,PollSubSeq, MemberSeq) 
    VALUES             ( seq_vecter.nextval, '관리자', SYSDATE,  1,     2             , 1 );
    -- 2)
    UPDATE t_poll
    SET polltotal = polltotal +1
    WHERE pollseq = 1;
    --3)
    UPDATE t_pollsub
    SET acount = acount + 1
    WHERE pollsubseq = 2;
   
   COMMIT;
 EXCEPTION 
     -- 예외 처리 블럭
     ROLLBACK;
 END;

PL/SQL : 6가지 종류

1) anonymous procedure  ( 익명 프로시저 )
   DECLARE ...로 시작되며
   저장 X

2) stored procedure  ( 저장 프로시저 ) *****
   CREATE PROCEDURE name ...~~
   저장 O

3)stored function ( 저장 함수 )
[처리 결과]를 사용자에게 [반환]함.

4)package ( 패키지 ) dbms_ranodm 패키지   .value() 함수
  자주 사용되는 + 관련있는 여러 procedure, function들을
  하나의 package묶어서 관리에 편리토록 함

5)trigger ( 트리거 )
  어떤 작업전, 또는 작업 후 trigger에 정의한 로직을 실행시키는 PL/SQL 블럭임

6) X

1) anonymous procedure ( 익명 프로시저 )

-- SELECT 쿼리 실행해서 사원명,sal를 변수에 저장하고 출력

DECLARE
  -- 선언블럭( 변수 선언 )
  -- v변수명 자료형(크기);
  -- ENAME             VARCHAR2(10) 
  -- SAL               NUMBER(7,2) 
  vename varchar2(10);
  vsal number(7,2);
BEGIN
  -- 실행블럭
  SELECT ename, sal INTO vename, vsal
  FROM emp  
  WHERE empno = 7369;

DBMS_OUTPUT.PUT_LINE(vename);
DBMS_OUTPUT.PUT_LINE(vsal);
--EXCEPTION
END;

DBMS_OUTPUT 패키지
1) PL/SQL 내에서 처리된 어떤 결과를 사용자의 화면에 출력할 때 사용한다
2) PUT 또는 PUT_LINE : 정의된 문자값을 화면에 출력하는 프로세서
NEW_LINE : GET_LINE에 의해 읽힌 행의 다음 라인을 읽을 때 사용
GET_LINE 또는 GET_LINES : 현재 라인의 문자값을 읽는 프로세서

ENABLE : 화면에 문자값을 출력하는 모드로 설정하며 문자값을 지정할 수 있는 버퍼크기를 정의함
DISABLE : 화면에 문자값을 출력하는 모드를 해제함

*결과 확인하는 법 : 보기 -> DBMS 출력

PL/SQL 문법
-- 예) 이름, 나이 변수 선언 + 값을 할당 -> 출력

DECLARE
  vname varchar2(10);
  vage  number(3);
BEGIN
   -- = 자바 대입연산자   ==
   -- := 오라클 대입연산자
   -- vname := '홍길동님'; -- varchar2(10 byte)  한글한문자=3바이트
   vname := '홍길동';
   vage := 20;
   
   DBMS_OUTPUT.PUT_LINE( vname || ', ' || vage );
--EXCEPTION
END;

* 대입      :=   INTO 구문 사용한다.

-- 30번 부서의 지역명*(loc) 가져와서   10번 부서의 loc 로 수정

DESC dept;
LOC             VARCHAR2(13)

DECLARE 
   -- 자바 변수 선언 할 때 초기화  int age = 10;
   -- vloc VARCHAR2(13);
   vloc dept.loc%TYPE := 'SEOUL' ; -- %TYPE    타입형 변수
BEGIN
   SELECT loc  INTO vloc
   FROM dept
   WHERE deptno = 30;
   
   UPDATE dept
   SET loc = vloc
   WHERE deptno = 10;
   
--   COMMIT;
-- EXCEPTION
--   ROLLBACK;
END;

-- 문제) 10번 부서원 중에 급여를 가장 많이 받는 사원의 정보를 출력하는 익명 프로시저 작성.

DECLARE
    vmax_pay NUMBER;
    
    vEMPNO      NUMBER(4) ;   
    vENAME      VARCHAR2(10) ;
    vJOB        VARCHAR2(9);       
    vHIREDATE   DATE ;        
    vpay        NUMBER; 
    vdeptno     NUMBER(2);
BEGIN
  SELECT MAX( sal + NVL( comm, 0 )) max_pay INTO vmax_pay
  FROM emp
  WHERE deptno = 10;
  
  SELECT empno, ename, deptno, job, hiredate, sal+NVL(comm, 0) pay
      INTO vempno, vename,  vdeptno, vjob, vhiredate , vpay
  FROM emp
  WHERE deptno = 10 AND sal+NVL(comm, 0) = vmax_pay;
  
  DBMS_OUTPUT.PUT_LINE( vempno || ', ' || vename || ', ' || vdeptno || ', ' || vjob || ', ' || vhiredate || ', ' ||  vpay   );
  
--EXCEPTION
END;

-- TYPE

DECLARE
    vmax_pay NUMBER;
    -- %TYPE 타입형 변수로 선언
    vEMPNO      emp.empno%TYPE ;   
    vENAME      emp.ename%TYPE ;
    vJOB        emp.job%TYPE;       
    vHIREDATE   emp.hiredate%TYPE ;        
    vpay        emp.sal%TYPE; 
    vdeptno     emp.deptno%TYPE;
BEGIN
  SELECT MAX( sal + NVL( comm, 0 )) max_pay INTO vmax_pay
  FROM emp
  WHERE deptno = 10;
  
  SELECT empno, ename, deptno, job, hiredate, sal+NVL(comm, 0) pay
      INTO vempno, vename,  vdeptno, vjob, vhiredate , vpay
  FROM emp
  WHERE deptno = 10 AND sal+NVL(comm, 0) = vmax_pay;
  
  DBMS_OUTPUT.PUT_LINE( vempno || ', ' || vename || ', ' || vdeptno || ', ' || vjob || ', ' || vhiredate || ', ' ||  vpay   );
  
--EXCEPTION
END;

-- ROWTYPE

DECLARE
    vmax_pay NUMBER;
    --emp 테이블의 한 행(row, record ) 변수 선언
    vemprow emp%ROWTYPE; -- %행타입 변수    
    vpay NUMBER;
BEGIN
  SELECT MAX( sal + NVL( comm, 0 )) max_pay INTO vmax_pay
  FROM emp
  WHERE deptno = 10;
  
  SELECT empno, ename, deptno, job, hiredate, sal+NVL(comm, 0) pay
      INTO vemprow.empno, vemprow.ename,  vemprow.deptno, vemprow.job, vemprow.hiredate 
      --, vemprow.sa
      , vpay
  FROM emp
  WHERE deptno = 10 AND sal+NVL(comm, 0) = vmax_pay;
  
  DBMS_OUTPUT.PUT_LINE( vemprow.empno || ', ' || vemprow.ename || ', ' || vemprow.deptno 
  || ', ' || vemprow.job || ', ' || vemprow.hiredate  || ', ' ||  vpay   );
  
--EXCEPTION
END;

PL/SQL 의 SELECT 처리 결과가 여러 개의 행을 반환할 경우에는, 반드시 [커서(cursor)]를 사용해야 된다. ( 기억)

DECLARE  
  vempno emp.empno%type;
  vename emp.ename%type;
BEGIN
  -- emp 테이블의 모든 사원의 empno, ename 조회해서 출력...
   SELECT empno, ename  INTO vempno, vename
   FROM emp;
   --WHERE empno = 7369;
   
   DBMS_OUTPUT.PUT_LINE( vempno  || ', ' || vename );
-- EXCEPTION
END;

PL/SQL 흐름 제어( 제어문 )
-- IF 문자

오라클 PL/SQL

IF ( 조건식 ) THEN
   --
ELSIF ( 조건식 ) THEN
   --
ELSIF ( 조건식 ) THEN
   --
ELSIF ( 조건식 ) THEN
    --
ELSE
   --
END IF;

--- 문제) 변수를 하나 선언해서 정수를 입력받아서 짝수/홀수 출력. (입력받기 :binNumber)

DECLARE
   -- int n ;
   vn NUMBER := 0;
   vresult varchar2(10) := '짝수';
BEGIN
    -- vn := scanner.nextInt();
    vn :=   :bindNumber;
    
   -- IF ( vn%2 != 0 ) THEN 
   IF (   MOD( vn, 2) != 0  ) THEN 
      vresult := '홀수';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(  vresult  );
-- EXCEPTION
END;

-- 문제) 국어점수를 입력받아서 수,우,미,양,가 출력하는 익명프로시저를 작성.
-- ( IF문 사용 )

DECLARE
  vkor NUMBER(3) := 0;
  vgrade VARCHAR2(3);
BEGIN
   vkor :=    :bindKor;
   
   IF vkor >= 90 THEN
      vgrade := '수';
   ELSIF vkor >= 80 THEN
      vgrade := '우';
   ELSIF vkor >= 70 THEN
      vgrade := '미';
   ELSIF vkor >= 60 THEN
      vgrade := '양';
   ELSE
     vgrade := '가';
   END IF;
   
   DBMS_OUTPUT.PUT_LINE( vgrade );
--EXCEPTION
END;

DECLARE
  vkor NUMBER(3) := 0;
  vgrade VARCHAR2(3);
BEGIN
   vkor :=    :bindKor;
   
   CASE   TRUNC(   vkor / 10  )
      WHEN  10 THEN vgrade := '수';
      WHEN  9 THEN vgrade := '수';
      WHEN  8 THEN vgrade := '우';
      WHEN  7 THEN vgrade := '미';
      WHEN  6 THEN vgrade := '양';
      ELSE vgrade := '가';
   END CASE; 
   
--   CASE 
--      WHEN TRUNC(   vkor / 10  ) IN ( 10, 9 ) THEN vgrade := '수';
--   END CASE ;
   
   DBMS_OUTPUT.PUT_LINE( vgrade );
--EXCEPTION
END;

PL/SQL FOR/WHILE 문

FOR  카운트변수  IN [REVERSE]  시작값.. 끝값
LOOP
   -- 반복적으로 처리할 코딩.
END LOOP;

WHILE  (조건식)
LOOP
   -- 반복적으로 처리할 코딩.
END LOOP;

LOOP
    -- 반복적으로 처리할 코딩.
   EXIT WHEN 조건 -- 반복문을 빠져나갈 조건
END LOOP;

예) 1~10 합 출력; ( 1+2+3+4+5+6+7+8+9+10=55 )

DECLARE
   vi  NUMBER ;
   vsum NUMBER := 0;
BEGIN
   --FOR  vi  IN REVERSE 1.. 10
   FOR  vi  IN  1.. 10
   LOOP
      -- sum += i;
      -- vsum += vi;    += 복합대입연산자 X
      IF vi = 10 THEN
        DBMS_OUTPUT.PUT( vi  );
      ELSE
         DBMS_OUTPUT.PUT( vi ||  '+' );
      END IF;      
      vsum :=  vsum + vi;
   END LOOP;
   
   DBMS_OUTPUT.PUT_LINE('=' || vsum );

--EXCEPTION
END;

예) WHILE 문 사용 1~10 합 출력; ( 1+2+3+4+5+6+7+8+9+10=55 )

DECLARE
   vi  NUMBER := 1;
   vsum NUMBER := 0;
BEGIN
   vi := 1;
   WHILE ( vi <= 10 )
   LOOP
      IF vi = 10 THEN
        DBMS_OUTPUT.PUT( vi  );
      ELSE
         DBMS_OUTPUT.PUT( vi ||  '+' );
      END IF;      
      vsum :=  vsum + vi;
      -- vi++;   ++ 증감연산자 없어요.
      vi := vi + 1 ;
   END LOOP;  
   DBMS_OUTPUT.PUT_LINE('=' || vsum );

--EXCEPTION
END;

예) do~ WHILE 문 사용 1~10 합 출력; ( 1+2+3+4+5+6+7+8+9+10=55 )

DECLARE
   vi  NUMBER := 1;
   vsum NUMBER := 0;
BEGIN
   vi := 1;
    
   LOOP
      IF vi = 10 THEN
        DBMS_OUTPUT.PUT( vi  );
      ELSE
         DBMS_OUTPUT.PUT( vi ||  '+' );
      END IF;      
      vsum :=  vsum + vi;
      -- vi++;   ++ 증감연산자 없어요.
      EXIT WHEN vi = 10;
      vi := vi + 1 ;
   END LOOP;  
   DBMS_OUTPUT.PUT_LINE('=' || vsum );

--EXCEPTION
END;

-- 문제) 구구단 2~9단
--      1) for 문사용  ( 2개 )
--      2) while 문사용 ( 2개 )

DECLARE
  vdan NUMBER(2):=2 ;
  vi NUMBER(2) := 1 ;
BEGIN
   WHILE (vdan <= 9)
   LOOP
      vi  := 1;  -- *****
      WHILE( vi <= 9)
      LOOP
          DBMS_OUTPUT.PUT( vdan || '*' || vi || '=' || RPAD( vdan*vi, 4, ' ' ) );
          vi := vi + 1; -- 10
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('');
      
      vdan := vdan + 1;
   END LOOP; 
--EXCEPTION
END;

DECLARE
  -- vdan NUMBER(1) ;
  -- vi NUMBER(1) ;
BEGIN

   FOR vdan IN 2.. 9
   LOOP 
      FOR vi IN 1.. 9
      LOOP
        -- System.out.printf("format")
        DBMS_OUTPUT.PUT( vdan || '*' || vi || '=' || RPAD( vdan*vi, 4, ' ' ) );
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('');
   END LOOP;

--EXCEPTION
END;

-- PL/SQL
-- 3가지 블럭 구조
-- 6가지 1) 익명프로시저. + 문법
:=
INTO 절
-- 커서( cursor )

[ RECORD형 변수] : 1) TYPE, 2) ROWTYPE, 3) 사용자 정의 TYPE 구조체
deptno, dname, empno, ename, pay 를 저장할 수 있는 사용자 정의 구조체 (자료형) 선언

DECLARE
   -- 타입( 자료형 ) 선언
   TYPE EmpDeptTYPE IS RECORD(
     deptno  dept.deptno%type,
     dname dept.dname%type,
     empno emp.empno%type,
     ename emp.ename%type,
     pay number
   );
   
   vedrow EmpDeptTYPE;  -- 레코드형 변수 선언
BEGIN
    SELECT d.deptno, dname, empno, ename, sal + NVL(comm, 0) pay
         INTo vedrow.deptno, vedrow.dname, vedrow.empno, vedrow.ename  , vedrow.pay
    FROM dept d JOIN emp e  ON d.deptno = e.deptno
    WHERE empno = 7369;
    
    DBMS_OUTPUT.PUT_LINE( vedrow.deptno || ', ' || vedrow.dname  
    || ', ' ||  vedrow.empno  || ', ' || vedrow.ename  || ', ' ||  vedrow.pay );
--EXCEPTION
END;

커서( CURSOR )
1) 커서 ? PL/SQL 블럭 내에서 실행되는 [SELECT 문]을 의미한다.
2) SELECT 한 결과가 1개 X
여러 개의 행(레코드) -> 데이터를 저장 -> 커서
3) 커서 2가지 종류
<1> 묵시적 커서 ( implicit ) == 자동 커서

--DECLARE
  -- FOR 문 사용되는 반복변수( 카운트변수) 는 선언하지 않고 사용해도 된다. 
  -- vrow dept%ROWTYPE;
BEGIN
-- EXCEPTION
   FOR  vrow IN (  SELECT  *    FROM dept  ) --  커서 생성
   LOOP
     DBMS_OUTPUT.PUT_LINE( vrow.deptno || ', ' || vrow.dname || ', ' || vrow.loc );
   END LOOP; 
END;

<2> 명시적 커서 ( explicit )
ㄱ. 명시적 커서를 사용하는 순서(단계)

         (1) 커서 선언
         (2) OPEN -  SELECT문을 실제 실행 -> 여러 개의 레코드(행)을 커서 저장
         (3) FETCH
                    LOOP

                       EXIT WHEN ???;
                    END LOOP;
          (4) CLOSE

ㄴ. 커서 속성

        (1) %ROWCOUNT : 실행된 커서문장에서 읽힌 행의 수
        (2) %FOUND : 실행된 커서문장에서 검색된 행이 발견되었는지 알 수 있는 속성
        (3) %NOTFOUND : 실행된 커서문장에서 검색된 행이 발견되지 않았음을 알 수 있는 속성
        (4) %ISOPEN : 선언된 커서가 현재 OPEN되어 있는지를 반환

ㄷ.명시적 커서를 사용하는 예제

DECLARE
   -- vrow emp%ROWTYPE;  컬럼수가 10개
   vempno emp.empno%type;
   vename emp.ename%type;
   vhiredate emp.hiredate%type;
   
   --(1) 커서 선언
   -- CURSOR 커서명 IS ( 서브쿼리 );
   CURSOR empcursor IS (
                        SELECT empno, ename, hiredate
                       FROM emp
                      );
BEGIN
   -- (2) 커서 OPEN
   --  OPEN 커서명;
   OPEN empcursor;
   
   -- (3) FETCH == 가져오다
   LOOP
       -- PLS-00394: wrong number of values in the INTO list of a FETCH statement
       --   FETCH 문의  INTO  목록에 잘못된 수의 값있다.
       -- FETCH empcursor INTO vrow;
       FETCH empcursor INTO vempno, vename, vhiredate;
       DBMS_OUTPUT.PUT_LINE( vempno || ', ' || vename || ', ' || vhiredate ); 
      EXIT WHEN empcursor%NOTFOUND;
   END LOOP;
   
   CLOSE empcursor;
-- EXCEPTION
END;

* 마지막에 커서 닫아야함

2) STORED PROCEDURE( 저장 프로시저 ) -- 가장 대표적인 PL/SQL

- PL/SQL 언어 중에서 가장 대표적인 구조
- 개발자가 자주 실행해야 하는 업무 흐름을 이 문법에 의해 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행할 수 있다.
- 실행 성능 빨라진다.
- 선언 형식

CREATE [OR REPLACE] PROCEDURE 프로시저명
(
   p파라미터명 MODE(입력/출력/입출력용 IN ,OUT , IN OUT)  자료형(크기 X),
   p파라미터명 IN 자료형(크기 X),
   p파라미터명 자료형(크기 X)
)
IS
  -- v변수명 자료형(크기)[:=초기값];
  -- v변수 선언;
BEGIN
   -- 실행 블럭
EXCEPTION
   -- 예외 처리
END;

예) 삭제할 부서번호를 입력받아서 삭제하는 저장 프로시저 선언 + 실행

--  up_== user procedure
CREATE OR REPLACE PROCEDURE up_delDept
(
   pdeptno IN NUMBER
)
IS
  -- 선언 X
BEGIN
   DELETE FROM dept
   WHERE deptno = pdeptno;
   --COMMIT;
-- EXCEPTION
END up_delDept;

저장 프로시저를 실행하는 3가지 방법
1) execute 문

  EXECUTE UP_DELDEPT(60);
  COMMIT;
  ROLLBACK;
  SELECT * FROM dept;

2) 또 다른 저장 프로시저 안에서 실행..

CREATE OR REPLACE PROCEDURE up_exectest
IS
BEGIN
   UP_DELDEPT( 50 );
-- EXCEPTION
END;

EXEC up_exectest;

3) 익명 프로시저 안에서 실행

  DECLARE
  BEGIN
    UP_DELDEPT(50);
  --EXCEPTION
  END;

1) DEPT 테이블에 새로운 부서를 추가하는 저장 프로시저

 CREATE SEQUENCE seq_dept
 INCREMENT BY 10
 START WITH 50
 MAXVALUE 90
 NOCACHE;

[DESC dept;]

DEPTNO NOT NULL NUMBER(2)
DNAME           VARCHAR2(14)   널허용
LOC             VARCHAR2(13)   널허용

 CREATE OR REPLACE PROCEDURE up_insDept
 ( 
     pdname  dept.dname%type := null
     , ploc  dept.loc%type  DEFAULT null
 )
 IS
 BEGIN
    INSERT INTO dept ( deptno, dname, loc ) 
    VALUES ( seq_dept.nextval, pdname, ploc);
    COMMIT;
 --EXCEPTION
   -- ROLLBACK;
 END;

 EXEC UP_INSDEPT;
 EXEC UP_INSDEPT( pdname=>'QC');
 EXEC UP_INSDEPT( ploc =>'SEOUL');
 EXEC UP_INSDEPT( ploc =>'SEOUL' , pdname = 'QC');
 EXEC UP_INSDEPT(   'QC', 'SEOUL'  );

2) 부서정보를 수정하는 저장 프로시저

CREATE OR REPLACE PROCEDURE up_udpDept
 ( 
       pdeptno  dept.deptno%type
     , pdname  dept.dname%type := null
     , ploc  dept.loc%type  DEFAULT null
 )
 IS
    vdname dept.dname%type;
    vloc dept.loc%type;
 BEGIN    
    
    IF  pdname  IS NULL OR ploc IS NULL THEN       
        --원래 수정 전의 부서명, 지역명 저장
        SELECT dname, loc INTO  vdname, vloc
        FROM dept
        WHERE deptno =  pdeptno;
    END IF;

    UPDATE dept
    SET dname = CASE 
                   WHEN pdname IS NULL THEN vdname
                   ELSE pdname
               END
        , loc =     NVL( ploc, vloc )  
    WHERE deptno = pdeptno;
    COMMIT;
 --EXCEPTION
   -- ROLLBACK;
 END;
-- Procedure UP_UDPDEPT이(가) 컴파일되었습니다.

EXEC up_udpDept( 60, 'QC','SEOUL');   -- 부서명, 지역명 수정
EXEC up_udpDept( 60,  pdname => 'X'); -- 부서명 수정
EXEC up_udpDept( 60,  ploc => 'Y'); -- 지역명 수정

3) 부서를 삭제 저장프로시저 : UP_DELDEPT( 60 ) X
4) 부서 정보를 조회하는 저장 프로시저

CREATE OR REPLACE PROCEDURE up_selDept
--()
IS
   CURSOR vcur IS ( SELECT * FROM dept );
   vrow dept%ROWTYPE;
BEGIN  
  OPEN vcur;  
  LOOP
    FETCH vcur INTO vrow;
    EXIT WHEN vcur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( vrow.deptno || ', ' || vrow.dname || ', ' || vrow.loc );    
  END LOOP;  
  CLOSE vcur;
--EXCEPTIOn
END;
-- Procedure UP_SELDEPT이(가) 컴파일되었습니다.

-- 묵시적 커서 사용

CREATE OR REPLACE PROCEDURE up_selDept 
IS
BEGIN  
  FOR vrow  IN ( SELECT * FROM dept)
  LOOP    
    DBMS_OUTPUT.PUT_LINE( vrow.deptno || ', ' || vrow.dname || ', ' || vrow.loc );    
  END LOOP;  
   
--EXCEPTIOn
END;
-- Procedure UP_SELDEPT이(가) 컴파일되었습니다

EXEC UP_SELDEPT;

-- (  pdeptno IN 입력용 파라미터 )
-- (  OUT  출력용 파라미터를 저장 프로시저에서 사용하는 예제 1개 )

예)  주민등록번호 입력용 매개변수 -> 주민등록번호 앞자리 6개를 출력용 매개변수 처리
하는 저장 프로시저.

CREATE OR REPLACE PROCEDURE up_rr6
(
    pnum IN insa.num%type
    , prrn6 OUT VARCHAR2 -- 크기 X
)
IS
   vrrn insa.ssn%type;
BEGIN
   SELECT ssn INTO vrrn
   FROM insa
   WHERE num = pnum;
   
   prrn6 := SUBSTR( vrrn , 0, 6 );
-- EXCEPTION
END;

DECLARE
   vrrn6 VARCHAR2(6);
BEGIN
   UP_RR6( 1001, vrrn6);
   
   DBMS_OUTPUT.PUT_LINE( vrrn6 );
--EXCEPTION
END;

PL/SQL 저장 함수 ( Stored Function )

저장 프로시저 : 리턴값이 없다

저장 함수 : 리턴값이 있다

CREATE OR REPLACE FUNCTION uf_함수명
()
RETURN 리턴자료형
IS
BEGIN
  RETURN (리턴값);
  RETURN 리턴값;
EXCEPTION
END;

예) 저장 함수 : 주민등록 번호->> 성별 반환하는 함수 uf_gender

CREATE OR REPLACE FUNCTION uf_gender
(
   prrn VARCHAR2
)
RETURN VARCHAR2 -- 남자, 여자
IS
   vgender VARCHAR2(6);
BEGIN   
   IF  MOD( SUBSTR( prrn, -7, 1), 2) = 0  THEN  -- 여자
      vgender := '여자';
   ELSE  -- 남자
     vgender := '남자';
   END IF;   
   RETURN  vgender;
--EXCEPTION
END;

SELECT num, LENGTH(  name ), ssn, UF_GENDER( ssn ) gender
FROM insa;

-- 예) 1~ n 합을 반환하는 함수

CREATE OR REPLACE FUNCTION uf_sum
(
   pn NUMBER
)
RETURN NUMBER
IS
   vsum  NUMBER := 0;
BEGIN   
   
   FOR  i IN 1..pn
   LOOP
      vsum := vsum + i;
   END LOOP;
     
   RETURN  vsum;
--EXCEPTION
END;

SELECT uf_sum(10), uf_sum( 100)
FROM dual;

-- 복습 문제 --

문제 1)tbl_score 테이블에 새로운 학생의 성적 정보를 저장하는 프로시저 : up_insScore
    p :   num, name,  kor, eng, mat   입력하지 않으면 0 처리
    총점, 평균, 등급, 등수 까지는 처리가 되도록 ..
문제 2) tbl_score 테이블에 새로운 학생의 성적 정보를 수정하는 프로시저 : up_updScore
   p  : num,   kor, eng, mat  입력하지 않으면  수정 전의 점수로 처리
     총점, 평균, 등급 까지는 처리가 되도록 ..
문제 3)      tbl_score 테이블에  삭제하는 프로시저 : up_delScore
  p  : num
문제 4)   tbl_score 테이블에  모든 학생 정보를 조회하는 프로시저 : up_selScore
문제 5) 등수를 처리하는 프로시저  : up_rankScore

저작자표시

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

[Day15] Oracle 15 - trigger, package, cursor+paramete, 에러처리, 동적쿼리 (0)	2022.10.19
[Day13] Oracle 13 - 정규화, 이상현상, 뷰, 시퀀스 (0)	2022.10.19
[Day12] Oracle 12 - CROSS JOIN, DB Modeling (0)	2022.10.13
[Day11] Oracle 11 - PURGE, 제약조건2, JOIN (0)	2022.10.12
[Day10] Oracle 10 - MODIFY, DROP COLUMN, Table copy, sub query(insert, update, delete), MERGE, Constraint (0)	2022.10.12

[Day14] Oracle 14 - PL/SQL(procedure, cursor, type, stored function, if/while)

[Day14] Oracle14 [10/17]

1) anonymous procedure ( 익명 프로시저 )

2) STORED PROCEDURE( 저장 프로시저 ) -- 가장 대표적인 PL/SQL

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day14] Oracle14 [10/17]

1) anonymous procedure ( 익명 프로시저 )

2) STORED PROCEDURE( 저장 프로시저 ) -- 가장 대표적인 PL/SQL

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바