[PL/SQL] DAY1 - 프로시저 [PROCEDURE]

윤수인·2023년 12월 21일

📒국비학원 [DB]

목록 보기

10/14

📌 외워두자!

열=칼럼=가로

행=로우=세로

PL/SQL은 오라클에만 있음

why? SQL명령문만으로 처리하기 힘든 복잡한 자료의 저장 / 보안 강화

PROCEDURE 프로시저

: 자주 사용되는 SQL문을 DB로 생성해서 저장한 후 프로시저명을 만들어서 호출해서 사용함

(IN/OUT/INOUT - 매개변수)를 사용하므로 보안확보

DB에는 저장 X / 그 순간에 실행될때 캐시에 저장

처리속도 빠름 / 비지니스 로직 처리

CREATE OR REPLACE PROCEDURE 프로시저명
(매개변수1 [IN/OUT/INOUT 중 1] 데이터타입.
매개변수2 [IN/OUT/INOUT 중 1] 데이터타입..)
[IS/AS중 1]
변수,상수 등 선언문장
BEGIN
실행문장
END; --이 사이에 EXCEPTION예외 필수아님                                                                                              

EXEC(UTE) 프로시저명 (값,값,,,) --실행 명령문

⭐ 1. PL/SQL 실행

✏️ NOTE

DBMS_OUTPUT.PUT_LINE() = (java의 System.out.println() : 콘솔에 띄워달라는 뜻)
DBMS_OUTPUT.PUT_LINE : 언어와 연동이 안돼서 눈으로 보려고 쓰는 명령어
드래그+CTRL+ENTER : 드래그한거 전부 실행
PL/SQL은 맘대로 주석달면 안됨 맨 위 가운데 끝에만 달아줘야함
BINARY_INTEGER; : 인티져 중에 가장 큰값
원래는 한글로 적지 않음 -> 영어로

PL/SQL에서 데이터 가져올때 3가지

하나의 타입 - %TYPE
하나의 열 - %ROWTYPE
하나의 컬럼 - 변수나 레코드의 멤버 사용

- 1) 하나의 타입 : %TYPE

PL/SQL편집기

DECLARE
TYPE FIRSTTYPE IS RECORD  -- 레코드를 담을 변수 FIRSTTYPE
(A 사원.사원명%TYPE, B 사원.직급%TYPE, C 사원.급여%TYPE); 


CUS FIRSTTYPE; -- CUS가 해당 타입을 가지게 됨

BEGIN
SELECT 사원명,직급,급여 INTO CUS FROM 사원 WHERE 사원번호 = 2001;  - FIRSTTYPE은 3개의 저장공간을 갖는 데이터 타입임
-- 여기에 실행되는 하나의 데이터를 CUS에 넣을 거임 (INTO CUS)

DBMS_OUTPUT.PUT_LINE('사원명   직급   급여');   
DBMS_OUTPUT.PUT_LINE('--------------------'); 
DBMS_OUTPUT.PUT_LINE(CUS.A||'   '||CUS.B||'   '||TO_CHAR(CUS.C)); 
DBMS_OUTPUT.PUT_LINE('질의자 계정명 : '|| USER); 
DBMS_OUTPUT.PUT_LINE('질의 시간:'||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')); 
END; --- BEGIN문을 닫아줌

쿼리편집기

COMMIT;

![](https://velog.velcdn.com/images/_ddu2ni/post/8285d1fe-5470-4564-96d3-8d67c9f4ce14/image.png)

2) 하나의 열 : %ROWTYPE

PL/SQL편집기

DECLARE
SAWON_RECORD 사원%ROWTYPE; -- 사원테이블에 가서 하나의 레코드 타입을 모두 읽어오는 명령어

BEGIN
SELECT * INTO SAWON_RECORD FROM 사원 WHERE 사원번호 = 2001; --- 해당조건에 모든 컬럼을 SAWON_RECORD 에 넣겠음

DBMS_OUTPUT.PUT_LINE('사원번호: '||SAWON_RECORD.사원번호); 
DBMS_OUTPUT.PUT_LINE('사원명: '||SAWON_RECORD.사원명); 
DBMS_OUTPUT.PUT_LINE('직급: '||SAWON_RECORD.직급); 
DBMS_OUTPUT.PUT_LINE('주소: '||SAWON_RECORD.주소); 
END;

2.PL/SQL [cmd] 실행

하나의 타입 : %TYPE

✏️ NOTE

외부에서 CHANGE_PAY 호출할때
(SANO IN NUMBER, NEWPAY IN NUMBER)
=> IS의 윗부분은 매개변수 느낌
=> IS의 아랫부분은 내부에서 사용할 변수임

BEGIN
UPDATE 사원 SET 급여 = NEW_PAY WHERE 사원번호=SANO;
COMMIT;

=> 급여랑 사원번호 받아서 UPDATE하겠음 => (트렌지셕이 시작되니까 커밋)

ED - 편집수정
EXECUTE - 실행시키는 명령어
EXECUTE CHANGE_PAY(2001,5000);
->매개 변수 두개 넣기 (( = SANO IN NUMBER, NEWPAY IN NUMBER)

함수호출

프로시져는 원래 한번 실행해주면 끝나는데, 잘 사용하진 않지만, 외부로 보낼 수 있는 방법도 있음

LEE@XE> CREATE OR REPLACE PROCEDURE P_OUTEX 매개변수 값 하나 설정해주면
3개의 값을 출력해줌 -> 근데 3개의 변수도 필요함

예제 해보기

고객번호를 입력받아 해당고객이 주문한 총 횟수 구하기

다른 예제 해보기

직책을 입력받아 그 직책의 급여의 총액, 평균월급, 인원수를 찾으시오

  1  CREATE OR REPLACE PROCEDURE SEARCHJIK
  2  (JIK IN VARCHAR2)
  3  IS
  4  A NUMBER := 0;
  5  B NUMBER(12,2) := 0;
  6  C NUMBER := 0;
  7  BEGIN
  8  SELECT SUM(PAY), AVG(PAY),COUNT(*) INTO A,B,C FROM COMPANY
  9  WHERE JIK = POSIT;
 10  DBMS_OUTPUT.PUT_LINE(JIK||'의 급여의 총액: ' ||A|| '원, 평균월급: ' ||B|| '원, 인원수: '||C||'명 입니다.');
 11* END;
 12  /

Procedure created.

LEE@XE> EXEC SEARCHJIK('과장');
과장의 급여의 총액: 125984000원, 평균월급: 1749777.78원, 인원수: 72명 입니다.

PL/SQL procedure successfully completed.

-> 이후에 'A.B.C'를 언어쪽으로 보내 '프로시져 SERACHJIK'을 호출하게 됨

데이터를 입력하는 프로시져 만들기

- 테이블 만들고 
LEE@XE> CREATE TABLE TEST
  2  (A NUMBER(10),
  3  B NUMBER(10),
  4  C DATE DEFAULT SYSDATE);

Table created.

- 프로시져 만들기
LEE@XE> CREATE OR REPLACE PROCEDURE P_EX
  2  (A IN NUMBER, B IN NUMBER)
  3  IS
  4  BEGIN
  5  INSERT INTO TEST(A,B) VALUES (A,B);
  6  COMMIT;
  7  END;
  8  /

Procedure created.

- 프로시져에 값 넣어주기 
LEE@XE> EXEC P_EX(20,30)

PL/SQL procedure successfully completed.

- 테이블 호출
LEE@XE> SELECT * FROM TEST;

         A          B C
---------- ---------- --------
        20         30 23/12/21