[SQL응용] PL/SQL

예지성준·2024년 5월 17일

DB

목록 보기
16/19
post-thumbnail

PL/SQL 기초

  • Oracle Procedual Language extension to SQL

PL/SQL 구조

1. 블록이란?

데이터베이스 관련 특정 작업을 수행하는 명령어와 실행에 필요한 여러 요소를 정의하는 명령어 등으로 구성되며, 이러한 명령어를 모아 둔 PL/SQL 프로그램의 기본 단위

2. 기본 형식

DECLARE
    [실행에 필요한 여러 요소 선언];
BEGIN
    [작업을 위해 실제 실행하는 명령어];
EXCEPTION
    [PL/SQL 수행 도중 발생하는 오류 처리];
END;
  • 선언부와 예외 처리부는 생략 가능하지만 실행부는 반드시 존재해야 한다. 필요에 따라 PL/SQL 블록 안에 다른 블록을 포함할 수도 있습니다. 이를 중첩 블록(nested block)이라고 한다.

3. Hello, PL/SQL 출력하기

DECLARE
	--GREETING VARCHAR2();
	GREETING VARCHAR2(30); --변수 역할
BEGIN
	GREETING := 'HEllo, PL/SQL!'; -- := 이게 대입연산자임
	
	DBMS_OUTPUT.PUT_LINE(GREETING); --출력에 사용하는 메서드
END;

/*
--여러줄 주석
*/

4. PL/SQL 주석

변수와 상수

  1. 변수 선언과 사용
    1) 변수이름 자료형 := 값 또는 값이 도출되는 표현식
    2) 변수 선언 및 변수 값 출력하기

📚자료형

  • 스칼라: 단일값- 숫자, 문자열, 날짜, 논리형
    • NUMBER: 숫자
    • CHAR
    • VARCHAR2
    • DATE: 날짜
    • BOOLEAN: 논리형, true, fale, NULL

      LOB: Large OBject - CLOB, BLOB
  • 참조형
    • %TYPE: 특정 테이블의 컬럼(1)의 자료형을 참조
    • %ROWTYPE: 특정 테이블의 모든 컬럼들을 참조
  • 복합형
    • RECORD: 여러 변수와 자료형을 한꺼번에 선언하는 방식 / 레코드
    • TABLE: 키 - 값 형태
  1. 상수 정의하기
    1) 변수이름 CONSTANT 자료형 := 값 또는 값이 도출되는 표현식
    2) 상수에 값을 대입한 후 출력하기
DECLARE
	V_NUM CONSTANT NUMBER := 1000; --상수
BEGIN
	--V_NUM := 2000; 불가능
	DBMS_OUTPUT.PUT_LINE(V_NUM); --상수가 되어서 값을 바꿀 수 없다.
END;
  1. 변수의 기본값 지정하기
    1) 변수이름 자료형 DEFAULT := 값 또는 값이 도출되는 표현식
    2) 변수에 기본값을 설정한 후 출력하기

  1. 변수에 NULL 값 저장 막기
    1) 변수이름 자료형 NOT NULL := 값 또는 값이 도출되는 표현식
    2) 변수에 NOT NULL을 설정하고 값을 대입한 수 출력하기

  2. 변수 이름 정하기

DECLARE
	--EMPNO NUMBER(4) NOT NULL := 1000; -- NOT NULL일경우 초기화를 해줘야함
	EMPNO NUMBER(4) NOT NULL DEFAULT 1000; -- 기본값 지정하는 또 다른 방식
	ENAME VARCHAR2(10);
BEGIN
	EMPNO := 1000;
	ENAME := '내이름';

	DBMS_OUTPUT.PUT_LINE(EMPNO || ' : ' || ENAME);
END;

📒%TYPE: 특정 테이블의 컬럼의 자료형을 참조

DECLARE
	-- 타입을 통해서 다른 테이블의 컬럼참조하기
	EMPNO EMP.EMPNO%TYPE	
	ENAME EMP.ENAME%TYPE;
BEGIN
	EMPNO := 1000;
	ENAME := '내이름';

	DBMS_OUTPUT.PUT_LINE(EMPNO || ' : ' || ENAME);
END;

📒테이블의 모든 컬럼 참조하기

--컬럼명을 통해서 접근
DECLARE
	EMP_ROW EMP%ROWTYPE;
BEGIN
	EMP_ROW.EMPNO := 1000;
	EMP_ROW.ENAME := '이이름';
	EMP_ROW.JOB := 'CLERK';
	EMP_ROW.DEPTNO := 40;

	DBMS_OUTPUT.PUT_LINE('EMPNO : ' || EMP_ROW.EMPNO);
	DBMS_OUTPUT.PUT_LINE('ENAME : ' || EMP_ROW.ENAME);
	DBMS_OUTPUT.PUT_LINE('JOB : ' || EMP_ROW.JOB);
	DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || EMP_ROW.DEPTNO);
END;
/*
EMPNO : 1000
ENAME : 이이름
JOB : CLERK
DEPTNO : 40
*/
DECLARE
	EMP_ROW EMP%ROWTYPE;
BEGIN
	--sql구문과 더 자주 쓰인다.
    --주로 SELECT INTO구문과 많이 쓰임
	SELECT * INTO EMP_ROW
	FROM EMP WHERE ENAME='SCOTT';

	DBMS_OUTPUT.PUT_LINE('EMPNO : ' || EMP_ROW.EMPNO);
	DBMS_OUTPUT.PUT_LINE('ENAME : ' || EMP_ROW.ENAME);
	DBMS_OUTPUT.PUT_LINE('JOB : ' || EMP_ROW.JOB);
	DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || EMP_ROW.DEPTNO);
END;
/*
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
DEPTNO : 20
*/

변수의 자료형

  1. 스칼라
    1) 숫자 NUMBER
    2) 문자열 CHAAR, VARCHAR2
    3) 날짜 DATE
    4) 논리 데이터 BOOLEAN

  2. 참조형
    1) %TYPE : 열을 참조
    2) %ROWTYPE : 행을 참조
    3) 변수 이름 테이블이름.열이름%TYPE
    4) 참조형(열)의 변수에 값을 대입한 후 출력하기

5) 특정 테이블에서 하나의 열이 아닌 행 구조 전체를 참조할 때 %ROWTYPE을 사용
6) 변수이름 테이블이름%ROWTYPE
7) 참조형(행)의 변수에 값을 대입한 후 출력하기

  1. 복합형, LOB형
    1) 컬렉션, TABLE 자료형 : 한 가지 자료형의 데이터를 여러 개 저장(테이블의 열과 유사)
    2) 레코드, RECORD 자료형 : 여러 종류 자료형의 데이터를 저장(테이블의 행과 유사)

조건 제어문

  1. 조건문
    1) IF-THEN
  • 변수에 입력한 값이 홀수인지 알아보기(입력 값이 홀수일 때)
IF 조건식 THEN
 조건식이 참일때 실행
END IF;
DECLARE
	V_NUMBER NUMBER := 22;
BEGIN
	IF MOD(V_NUMBER, 2) = 1 THEN
		DBMS_OUTPUT.PUT_LINE('홀수 입니다.');
	END IF;
END;

2) IF-THEN-ELSE

  • 변수에 입력된 값이 홀수인지 짝수인지 알아보기(입력 값이 짝수일 때)
DECLARE
	V_NUMBER NUMBER := 22;
BEGIN
	IF MOD(V_NUMBER, 2) = 1 THEN
		DBMS_OUTPUT.PUT_LINE('홀수 입니다.');
	ELSE
		DBMS_OUTPUT.PUT_LINE('짝수 입니다.');
	END IF;
END;

3) IF-THEN-ELSIF

  • 입력한 점수가 어느 학점인지 출력하기
DECLARE
	V_SCORE NUMBER := 83; --B학점
BEGIN
	IF V_SCORE >= 90 THEN
		DBMS_OUTPUT.PUT_LINE('A학점');
	ELSIF V_SCORE >= 80 THEN
		DBMS_OUTPUT.PUT_LINE('B학점');
	ELSIF V_SCORE >= 70 THEN
		DBMS_OUTPUT.PUT_LINE('C학점');
	ELSIF V_SCORE >= 60 THEN
		DBMS_OUTPUT.PUT_LINE('D학점');
	ELSE 
		DBMS_OUTPUT.PUT_LINE('F학점');
	END IF;
	
END;
  1. CASE 조건문
CASE
	WHEN 조건, 값 TEHN ...

1) 단순 CASE: 값의 일치여부

  • CASE쪽에 변수가 온다. 1 -> 금메달, 2 -> 은메달, 3 -> 동메달
DECLARE
	V_RANK NUMBER := 3;
BEGIN
	CASE V_RANK
		WHEN 1 THEN
		DBMS_OUTPUT.PUT_LINE('금메달');
		WHEN 2 THEN
		DBMS_OUTPUT.PUT_LINE('은메달');
		WHEN 3 THEN
		DBMS_OUTPUT.PUT_LINE('동메달');
        ELSE
        DBMS_OUTPUT.PUT_LINE('해당하지 않습니다.');
	END CASE;
END;
  • 입력 점수에 따른 학점 출력하기(단순 CASE 사용)

2) 검색 CASE

  • CASE 쪽에는 변수X -> WHEN THEN 조건이 참일때 실행되는 부분 정의
  • 입력 점수에 따른 학점 출력하기(검색 CASE 사용)
DECLARE
	V_SCORE NUMBER := 75;
BEGIN
	CASE
		WHEN V_SCORE >= 90 
			THEN DBMS_OUTPUT.PUT_LINE('A학점'); 
		WHEN V_SCORE >= 80 
			THEN DBMS_OUTPUT.PUT_LINE('B학점');
		WHEN V_SCORE >= 70 
			THEN DBMS_OUTPUT.PUT_LINE('C학점');
		WHEN V_SCORE >= 60 
			THEN DBMS_OUTPUT.PUT_LINE('D학점');
		ELSE
			DBMS_OUTPUT.PUT_LINE('F학점');
	END CASE;
	
END;

반복 제어문

  1. 반복문 종류

1) 기본 LOOP: 반드시 반복 중단 조건이 필수 - 무한반복
EXIT, EXIT-WHEN 조건식(조건식이 참일때 중단)

LOOP
END LOOP;
#EXIT
DECLARE
	V_NUM NUMBER := 1;
BEGIN
	LOOP
		DBMS_OUTPUT.PUT_LINE('VNUM : ' || V_NUM);
		V_NUM := V_NUM +1;
	
		IF V_NUM > 4 THEN EXIT;
		END IF;
	END LOOP;
END;
/*
VNUM : 1
VNUM : 2
VNUM : 3
VNUM : 4
*/
#EXIT-WHEN
DECLARE
	V_NUM NUMBER := 1;
BEGIN
	LOOP
		DBMS_OUTPUT.PUT_LINE('VNUM : ' || V_NUM);
		V_NUM := V_NUM +1;
	
		--IF V_NUM > 4 THEN EXIT;
		--END IF;
		EXIT WHEN V_NUM > 4;
	END LOOP;
END;

2) WHILE LOOP

WHILE 조건식 LOOP
	조건이 참일때 반복되는 구간
END LOOP;
DECLARE
	V_NUM NUMBER := 1;
BEGIN
	WHILE V_NUM <= 4 LOOP
		DBMS_OUTPUT.PUT_LINE('V_NUM: '|| V_NUM);
		V_NUM := V_NUM +1;
	END LOOP;	
END;

3) FOR LOOP

FOR i IN 0..4 LOOP  --i는 0~4 반복

END LOOP;
FOR i IN REVERSE 0..4 LOOP  --i는 4~0 역순 반복

END LOOP;
BEGIN
	FOR i IN 1..4 LOOP
		DBMS_OUTPUT.PUT_LINE('i: '|| i);
	END LOOP;	
END;
/*
i: 1
i: 2
i: 3
i: 4
*/

4) Cursor For LOOP

  1. 반복 중단 명령어 종류

1) EXIT
2) EXIT-WHEN
3) CONTINUE: 반복 건너뛰기
4) CONTINUE-WHEN 조건식;

#1부터 100까지 숫자 중에서 홀수만 덧셈
DECLARE
	V_TOTAL NUMBER := 0;
BEGIN
	FOR i IN 1..100 LOOP
		CONTINUE WHEN MOD(i,2) = 0; -- 2로 나눈 나머지가 0일때 건너뛰기
		V_TOTAL := V_TOTAL + i;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('합계: '|| V_TOTAL);
END;
-- 합계 : 2500
profile
예지성준
꽁꽁 얼어붙은 한강 위로 😺
이전 포스트

[DB_SQL활용] 제약조건

다음 포스트

[SQL응용] 레코드와 컬렉션

0개의 댓글