국비 교육 49일차 - PL/SQL

정처기(필기)가 끝났다는 해방감+어제의 사건으로 꽤 정신없는 오전을 보냈다... 

 

계속 css와 오라클을 왔다갔다해서 pl/sql이 조금 헷갈린다. 지금 제대로 정리를 해둬야 할 것 같다. 

그냥 처음부터 차근차근 정리를 해보려고 한다. 

 

-- 2021-02-24 PL/SQL => 함수를 만들어서 재사용이 가능하게 만든다 
/*
    DML (SELECT,INSERT,UPDATE,DELETE) : 주로 사용하는 명령어 
      = 연산자,내장함수
      = JOIN
      = SUBQuery
    DDL (CREATE , DROP , ALTER , TRUNCATE) : DBA
      = Table
      = View => 인라인 뷰 
      = Sequence
      = Index 
    DCL (GRANT , REVOKE) : DBA
    TCL (COMMIT,ROLLBACK)
    
    PL / SQL 
    ==
    프로시저 언어 , 제어는 SQL
    = 리턴형을 가지고 있는 함수 : FUNCTION => MAX,MIN,SUBSTR (사용자 정의=오라클에서 지원하지 않는 함수)
                 => AUTO_INCREMENT 
    = 리턴형이 없는 함수 : PROCEDURE (중심 => 포인터(C언어 형식)) * , OUT
    = 조건에 맞으면 자동으로 실행하는 프로그램 (TRIGGER)
    
    1) 형식 (오라클은 {}가 없다)
       자바=> if(){} ,for(){}
             IF() ~ END IF;  FOR LOOP ~~ END LOOP;
             
       DECLARE  => CREATE FUNCTION func_name() , CREATE PROCEDURE pro_name() , CREATE TRIGGER tri_name
         선언 => 변수선언 (데이터를 조작)
       BEGIN
         SQL을 이용해서 데이터 조작 
       END;
       /  ======> 함수 종료 시작 
       
       1. 변수선언 방법 
          = 스칼라 변수 
               name VARCHAR2(34)
               no NUMBER
          = 참조변수 (컬럼한개)
               ename emp.ename%TYPE => %TYPE
          = 참조변수 (테이블에 있는 전체 컬럼)
               emp emp%ROWTYPE  => %ROWTYPE
          = 사용자 정의 변수 
               RECORD 
          = 전체데이터를 모아서 처리
               CURSOR =============> ResultSet  : Spring (댓글 처리=>PL/SQL,모든 데이터의 페이지)
       2. 연산자 : SQL문장은 연산자 사용 
       3. 제어문 : 조건문 (단일 조건문,선택 조건문,다중 조건문)
                    IF(조건)
                      처리
                    END IF;
                  =============  
                    IF(조건)
                      처리
                    ELSE
                      처리
                    END IF;
                  =============
                     IF(조건)
                       처리
                     ELSIF(조건)
                       처리
                     ELSE
                       처리
                     END IF;
                  반복문 : 일반 LOOP , WHILE , FOR
                  
    PL / SQL의 구조 
       선언부 : 모든 변수나 상수를 선어하는 부분
       실행부 : 제어문,SQL,연산자를 이용해서 처리 
       예외처리부 : 오류방지 
*/
-- 반드시 처리 (화면에 출력)
SET SERVEROUTPUT ON;
-- 변수 선언 방법 (스칼라변수)
DECLARE
  vempno NUMBER(4); -- 스칼라변수
  vename VARCHAR2(20);
BEGIN
  SELECT empno,ename INTO vempno,vename -- 변수값을 받는 경우 INTO
  FROM emp
  WHERE empno=7788;
  -- 변수 출력 
  DBMS_OUTPUT.PUT_LINE('======== 실행 결과 ========'); -- System.out.println()
  DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
  DBMS_OUTPUT.PUT_LINE('이름:'||vename);
END;
/
DESC emp;
-- 변수 선언 방법 (컬럼 한개를  참조변수)  => %TYPE
DECLARE
  vempno emp.empno%TYPE; -- emp.empno%TYPE => NUMBER(4)
  vename emp.ename%TYPE; -- emp.ename%TYPE => VARCHAR2(10)
  vjob emp.job%TYPE; 
  vhiredate emp.hiredate%TYPE;
  vsal emp.sal%TYPE;
BEGIN
  SELECT empno,ename,job,hiredate,sal INTO vempno,vename,vjob,vhiredate,vsal
  FROM emp
  WHERE ename='KING';
  
  DBMS_OUTPUT.PUT_LINE('======== 실행 결과 ========'); -- System.out.println()
  DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
  DBMS_OUTPUT.PUT_LINE('이름:'||vename);
  DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
  DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
  DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
END;
/

-- 변수 선언 방법 (테이블에 있는 모든 컬럼의 변수를 참조) => %ROWTYPE => ~VO
-- 함수를 만들면 : 컬럼명,테이블명 => 확인이 안된다 (보안)
DECLARE
  -- 변수 선언
  vemp emp%ROWTYPE;
  /*
      vemp (
         empno emp.empno%TYPE,
         ename emp.ename%TYPE,
         --
         --
         --
         --
      )
  */
  -- 임의로 데이터값 설정 
BEGIN
  -- 기능 처리 => INSERT,UPDATE,DELETE,SELECT , 자동증가번호
  SELECT * INTO vemp
  FROM emp
  WHERE ename='SCOTT';
  -- 결과값을 출력 => UPDATE,DELETE...
  -- 공지사항 : 금요일 (오전 : 오라클 (PL/SQL) , 오후 : 2시~3시 : HTML , 3시~4시(오라클 시험) , 4시~5시(오라클 시험) , 5시~6시(아이템 결정))
  -- PL/SQL => ERP (관리프로그램=금융권,공기업...,대기업)
  -- 12c , 18c ... (보안 강조 => Spring 5) => 자바,코틀린 
  DBMS_OUTPUT.PUT_LINE('======== 실행 결과 ========'); -- System.out.println()
  DBMS_OUTPUT.PUT_LINE('사번:'||vemp.empno);
  DBMS_OUTPUT.PUT_LINE('이름:'||vemp.ename);
  DBMS_OUTPUT.PUT_LINE('직위:'||vemp.job);
  DBMS_OUTPUT.PUT_LINE('입사일:'||vemp.hiredate);
  DBMS_OUTPUT.PUT_LINE('급여:'||vemp.sal);
END;
/
-- 사용자 정의 변수 (RECORD) => JOIN,SUBQUERY => 테이블 두개이상에서 데이터를 가지고 온다 
-- JOIN 이용 => 스칼라 변수 이용 
SELECT empno,ename,job,hiredate,sal,dname,loc,grade
FROM emp,dept,salgrade
WHERE emp.deptno=dept.deptno
AND sal BETWEEN losal AND hisal
AND ename='JONES';

-- %ROWTYPE => 한개의 테이블만 사용이 가능  => 사용자가 만들어서 사용 => RECORD => 1ROW 
DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vjob emp.job%TYPE;
  vhiredate emp.hiredate%TYPE;
  vsal emp.sal%TYPE;
  vdname dept.dname%TYPE;
  vloc dept.loc%TYPE;
  vgrade salgrade.grade%TYPE;
BEGIN
  -- 값을 읽어서 변수에 값대입 => SELECT ~ INTO 변수 설정 => SELECT 컬럼(변수가 아니라 출력용)
  -- 함수(메소드) => 변수를 이용해서 요청처리 
  SELECT empno,ename,job,hiredate,sal,dname,loc,grade 
  INTO vempno,vename,vjob,vhiredate,vsal,vdname,vloc,vgrade
  FROM emp,dept,salgrade
  WHERE ename='SCOTT'
  AND emp.deptno=dept.deptno
  AND sal BETWEEN losal AND hisal;
  DBMS_OUTPUT.PUT_LINE('====== 결과값 ======');
  DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
  DBMS_OUTPUT.PUT_LINE('이름:'||vename);
  DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
  DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
  DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
  DBMS_OUTPUT.PUT_LINE('부서명:'||vdname);
  DBMS_OUTPUT.PUT_LINE('근무지:'||vloc);
  DBMS_OUTPUT.PUT_LINE('호봉:'||vgrade);
END;
/
-- RECORD 이용하는 방식 
/*  
    DECLARE
    => CREATE FUNCTION func_name(매개변수..):RETURN VARCHAR2
       IS
       BEGIN 
       END;
       /
     => CREATE PROCEDURE pro_name(매개변수)
        IS
        BEGIN
        END;
     => CREATE TRIGGER tri_name
        BEGIN
        END;
        /
*/
DECLARE
  TYPE emp_dept_salgrade IS RECORD
  (
      empno emp.empno%TYPE,
      ename emp.ename%TYPE,
      job emp.job%TYPE,
      hiredate emp.hiredate%TYPE,
      sal emp.sal%TYPE,
      dname dept.dname%TYPE,
      loc dept.loc%TYPE,
      grade salgrade.grade%TYPE
  );
  -- Record변수 
  eds emp_dept_salgrade;
BEGIN
  SELECT empno,ename,job,hiredate,sal,dname,loc,grade INTO eds
  FROM emp,dept,salgrade
  WHERE emp.deptno=dept.deptno
  AND sal BETWEEN losal AND hisal
  AND ename='ADAMS';
  
  DBMS_OUTPUT.PUT_LINE('====== 결과값 ======');
  DBMS_OUTPUT.PUT_LINE('사번:'||eds.empno);
  DBMS_OUTPUT.PUT_LINE('이름:'||eds.ename);
  DBMS_OUTPUT.PUT_LINE('직위:'||eds.job);
  DBMS_OUTPUT.PUT_LINE('입사일:'||eds.hiredate);
  DBMS_OUTPUT.PUT_LINE('급여:'||eds.sal);
  DBMS_OUTPUT.PUT_LINE('부서명:'||eds.dname);
  DBMS_OUTPUT.PUT_LINE('근무지:'||eds.loc);
  DBMS_OUTPUT.PUT_LINE('호봉:'||eds.grade);
END;
/
-- PL/SQL => SELECT , INSERT , UPDATE , DELETE
DESC member;
CREATE TABLE member_test(
   id VARCHAR2(10),
   name VARCHAR2(20),
   addr VARCHAR2(100)
);

-- INSERT
DECLARE
  vid member_test.id%TYPE:='&id';
  vname member_test.name%TYPE:='&name';
  vaddr member_test.addr%TYPE:='&addr';
BEGIN
  INSERT INTO member_test VALUES(vid,vname,vaddr);
  COMMIT;
END;
/

SELECT * FROM member_test;

-- UPDATE
DECLARE
  -- 함수 => 매개변수 (JOIN,SUBQUERY사용하지 않는다)
  vid member_test.id%TYPE:='&id';
  vname member_test.name%TYPE:='&name';
  vaddr member_test.addr%TYPE:='&addr';
BEGIN
  UPDATE member_test SET
  name=vname,addr=vaddr
  WHERE id=vid;
  COMMIT;
END;
/

SELECT * FROM member_test;
-- DELETE
DECLARE
  vid member_test.id%TYPE:='&id'; -- 값을 받는 경우 :=
BEGIN
  DELETE FROM member_test
  WHERE id=vid;
  COMMIT;
END;
/

 오늘은 PROCEDEURE, FUNCTION을 배웠는데 두개의 가장 큰 차이점은 FUNCTION은 리턴형이 있다는것이고, 프로시져는 없다는 점이다. 방금 보면서 다시 깨우쳤다. 

 

그리고 %TYPE은 참조변수, %ROWTYPE은 테이블 전체를 가져오는 변수 

 

-- PROCEDURE : 함수 
/*
    함수형 2개 
      = FUNCTION (리턴형이 있다) => MAX,MIN,SUBSTR...
                                  지원하지 않는 함수 (사용자 정의 함수)
      = PROCEDURE (리턴형이 없다) => 매개변수로 값을 받아 온다 = Call BY Reference
    매개변수 
      = IN : 데이터를 넣는 변수 (INSERT , UPDATE , DELETE)
      = OUT : 포인터 변수 (데이터를 읽어 올때) : SELECT
      = INOUT : IN, OUT 동시에 사용 
      
         
    형식) CREATE [OR REPLACE] PROCEDURE pro_name(
             매개변수 
             매개변수...
         )
         IS
           변수 선언
         BEGIN
            처리 (구현부)
         END;
         /
         
         CREATE [OR REPLACE] FUNCTION func_name(
            매개변수 
         ):RETURN 데이터형
         IS
           변수 선언
         BEGIN 
            처리 
            RETURN 값
         END;
         /
         
         삭제)  DROP PROCEDURE pro_name;
               DROP FUNCTION func_name;
*/
CREATE TABLE pro_student(
   hakbun NUMBER,
   name VARCHAR2(20) CONSTRAINT ps_name_nn NOT NULL,
   kor NUMBER(3),
   eng NUMBER(3),
   math NUMBER(3),
   CONSTRAINT ps_hakbun_pk PRIMARY KEY(hakbun)
);
-- INSERT 하는 프로시저 제작
-- 생략하면 IN변수 (일반변수)
CREATE OR REPLACE PROCEDURE psInsert(
   pname pro_student.name%TYPE,
   pkor pro_student.kor%TYPE,
   peng pro_student.eng%TYPE,
   pmath pro_student.math%TYPE
)
IS 
BEGIN
  INSERT INTO pro_student VALUES(
    (SELECT NVL(MAX(hakbun)+1,1) FROM pro_student),
    pname,pkor,peng,pmath
  );
  COMMIT;
END;
/

EXECUTE psInsert('홍길동',90,80,80);
EXECUTE psInsert('심청이',85,75,97);
SELECT * FROM pro_student;
-- UPDATE 
CREATE OR REPLACE PROCEDURE psUpdate(
   pname pro_student.name%TYPE,
   pkor pro_student.kor%TYPE,
   peng pro_student.eng%TYPE,
   pmath pro_student.math%TYPE,
   phakbun pro_student.hakbun%TYPE
)
IS 
BEGIN
  UPDATE pro_student SET
  name=pname,
  kor=pkor,
  eng=peng,
  math=pmath
  WHERE hakbun=phakbun;
  COMMIT;
END;
/

EXECUTE psUpdate('홍길수',90,90,90,1);
SELECT * FROM pro_student;
-- DELETE
CREATE OR REPLACE PROCEDURE psDelete(
    phakbun NUMBER
)
IS
BEGIN
  DELETE FROM pro_student
  WHERE hakbun=phakbun;
  COMMIT;
END;
/
EXECUTE psDelete(1);
SELECT * FROM pro_student;
-- SELECT 
CREATE OR REPLACE PROCEDURE psSelect(
   pname OUT pro_student.name%TYPE,
   pkor OUT pro_student.kor%TYPE,
   peng OUT pro_student.eng%TYPE,
   pmath OUT  pro_student.math%TYPE,
   phakbun pro_student.hakbun%TYPE
)
IS
BEGIN
  SELECT name,kor,eng,math INTO pname,pkor,peng,pmath
  FROM pro_student
  WHERE hakbun=phakbun;
END;
/

VARIABLE vName VARCHAR2(20);
VARIABLE vKor NUMBER;
VARIABLE vEng NUMBER;
VARIABLE vMath NUMBER;
EXECUTE psSelect(:vName,:vKor,:vEng,:vMath,2);
PRINT vName;
PRINT vKor;
PRINT vEng;
PRINT vMath;
/*
   IN : 프로시저롤 값만 전송하는 변수
   OUT : 프로시저로부터 값을 받을 변수 (포인터) => Call By Reference
         Call By Reference : 자바(배열,클래스만 사용이 가능) 
         C => 일반변수도 가능
         void display(int* p)
         {
            *p=100;
         }
         int a=10;
         int* p=&a;
         display(p);  ==> a값은 100
         
         ==> OUT 
*/
CREATE OR REPLACE FUNCTION func_sum(
   phakbun pro_student.hakbun%TYPE
) RETURN NUMBER 
IS
  psum NUMBER;
BEGIN
  SELECT kor+eng+math INTO psum
  FROM pro_student
  WHERE hakbun=phakbun;
  
  RETURN psum;
END;
/
SELECT hakbun,name,kor,eng,math,func_sum(hakbun)
FROM pro_student;
-- 평균 출력 함수 : func_avg
CREATE OR REPLACE FUNCTION func_avg(
   phakbun pro_student.hakbun%TYPE
) RETURN NUMBER 
IS
  pavg NUMBER;
BEGIN
  SELECT ROUND((kor+eng+math)/3,2) INTO pavg
  FROM pro_student
  WHERE hakbun=phakbun;
  
  RETURN pavg;
END;
/
SELECT hakbun,name,kor,eng,math,func_sum(hakbun),func_avg(hakbun)
FROM pro_student;

-- JOIN
SELECT empno,ename,job,hiredate,dname,loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;
-- 리턴형은 1개다 
CREATE OR REPLACE FUNCTION func_dname(
    pDeptno emp.deptno%TYPE
) RETURN VARCHAR2
IS
   vdname dept.dname%TYPE;
BEGIN
   SELECT dname  INTO vdname
   FROM dept
   WHERE deptno=pDeptno;
   return vdname;
END;
/

CREATE OR REPLACE FUNCTION func_dloc(
    pDeptno emp.deptno%TYPE
) RETURN VARCHAR2
IS
   vloc dept.loc%TYPE;
BEGIN
   SELECT loc  INTO vloc
   FROM dept
   WHERE deptno=pDeptno;
   return vloc;
END;
/
SELECT emp.*,func_dname(deptno),func_dloc(deptno)
FROM emp;

CREATE OR REPLACE FUNCTION func_rank(
   vempno emp.empno%TYPE
) RETURN NUMBER
IS
  pRank NUMBER; -- 스칼라변수
BEGIN
  SELECT RANK() OVER(ORDER BY sal DESC) INTO pRank
  FROM emp
  WHERE empno=vempno;
  RETURN pRank;
END;
/
SELECT empno,ename,func_rank(empno)
FROM emp;

 INSERT, UPDATE, DELETE로 받아오는 값은 IN변수이고, SELECT를 이용해서 자바로 내보내주는 값은 OUT변수이다. 

그래서 변수를 쓸때 IN변수인지 OUT변수인지를 잘 판단해서 써줘야한다.

 

값 처리는 BEGIN 안에서 해준다. 그래서 결국 SQL문장이 직접적으로 쓰이는 부분은 BEGIN 아래 부분이 된다. 

값을 처리해준 다음 꼭 COMMIT을 써줘야 완성이 된다.