데이터베이스
13. PL/SQL
개린이네
2024. 4. 3. 23:16
Procedural Language extension to SQL의 약자
- 오라클에 내장되어 있는 절차적 언어(oracle에서 제공하는 자체언어)
- SQL 단점 보완 => 변수의 정의, 조건 처리, 반복 처리 등 지원
| 구조 | 설명 |
| DECARE SECTION(선언부) | DECLARE로 시작 변수/상수 선언 |
| EXECUTABLE SECTION(실행부) | BEGIN으로 시작 제어문/반복문/함수 정의 |
| EXCEPTION SECTION | EXCEPTION으로 시작 예회사항 발생 시 해결 |
PL/SQL 기본 모양
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘HELLO WORLD’);
END;
/DBMS_OUTPUT.PUT_LINE : sysout
DEFAULT 값 OFF라서 ON 으로 변경하고 사용 => 출력하는 내용을 화면에 보여줌
CONSOLE 창 - 디버깅 용도로 사용
창 두개띄우기 가능
변수의 선언과 초기화, 변수 값 출력
- 변수명이 앞에 있음
- 대입연산자 => :=
- 연결자 => ||
DECLARE
V_EMPNO EMP.EMPNO%TYPE; --변수선언
--V_EMPNO 가 변수이름
--V_EMPNO NUMBER(4) EMP.EMPNO%TYPE; 이렇게 쓸 필요 X
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT EMPNO, ENAME
INTO V_EMPNO, V_ENAME
--ORA-06550: 줄 7, 열5:PLS-00428: 해당 SELECT 문에 INTO 절이 필요합니다.
--줄 7 = DECLARE부터 줄 시작 (DECLARE 부터 1,2,..) => DECLARE 부터 줄 7을 의미함
--PROCEDURE 은 기능 안에서 SELECT문을 해달라고 해서 안되는 것
--SELECT 에 쓴 개수랑 INTO 개수랑 같아야함(자료형 같아야함)
--순서도 같아야 함
FROM EMP
--WHERE EMPNO=7788;
WHERE EMPNO='&ABC';
--01403. 00000 - "no data found"
--검색한 데이터값이 없을 때 뜸
DBMS_OUTPUT.PUT_LINE(V_EMPNO || ':' || V_ENAME);
--이거 해야 DBMS에 출력되서 화면에 뜸
END;
/
--PROCEDURE 한개 당 파일 하나로 구성
--set define off: 대체 문자 동작 X(& 동작 X)
테이블과 상관이 없기 때문에 변수 선언해서 값 대입해도 테이블에 값이 대입되지는 X
레퍼런스 변수의 선언과 초기화, 변수 값 출력
- 단일행, 단일열
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
--변수명 테이블명.컬럼명%TYPE(자료형)
BEGIN
SELECT EMP_ID, EMP_NAME -- 테이블 컬럼명
INTO EMP_ID, EMP_NAME --변수ID
--컬럼을 변수에 대입해달라 => 행이 딱 하나여야 함
--단일행 나오는 조건: PK를 가지고 = 연산 & 조건 하나 더 찾아보기
FROM EMPLOYEE
WHERE EMP_ID = ‘&EMP_ID’; --컬럼명
-- ''안은 문자형, & 뒤에 문구가 있는데 & 하면 창이 하나 뜨는데 문구가 그 창 안에 들어감
END;
/
* set define off : 대채문자(&) 동작 안함
set define on : 다시 동작함
* set feedback off
- insert 한 행이 삽입 되었습니다 등 문구들이 계속 뜨면 속도가 느려져서 안뜨게 하는 것
=> 오류랑 prompt 다음에 있는 얘들은 뜸
한 행에 대한 ROWTYPE변수의 선언과 초기화, 값 출력
=> 행 전체를 긁어오는 것(다중열, 단일행인 경우 가능)
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT * INTO E
FROM EMPLOYEE
WHERE EMP_ID = ‘&EMP_ID’;
DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || E.EMP_ID);
DBMS_OUTPUT.PUT_LINE(‘EMP_NAME : ’ || E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE(‘EMP_NO : ’ || E.EMP_NO);
DBMS_OUTPUT.PUT_LINE(‘SALARY : ’ || E.SALARY);
END;
/
테이블 타입의 변수 선언과 초기화, 변수 값 출력
DECLARE
TYPE EMP_ID_TABLE_TYPE IS TABLE OF EMPLOYEE.EMP_ID%TYPE
INDEX BY BINARY_INTEGER;
--TABLE이 배열개념을 가지고 있다
TYPE EMP_NAME_TABLE_TYPE IS TABLE OF EMPLOYEE.EMP_NAME%TYPE
INDEX BY BINARY_INTEGER;
EMP_ID_TABLE EMP_ID_TABLE_TYPE;
EMP_NAME_TABLE EMP_NAME_TABLE_TYPE;
--EMP_ID_TABLE, EMP_NAME_TABLE 은 변수명
I BINARY_INTEGER := 0;
--INTEGER 형태가 아래 I 안에 들어갈거야
--자료형이 INTEGER 모양(선언과 동시에 값 지정)
BEGIN
FOR K IN (SELECT EMP_ID, EMP_NAME FROM EMPLOYEE) LOOP
-- K.EMP_ID 값을 꺼내서 EMP_ID_TABLE(I)에 대입
I := I + 1;
EMP_ID_TABLE(I) := K.EMP_ID;
EMP_NAME_TABLE(I) := K.EMP_NAME;
END LOOP;
--여기가 계속 돈다(테이블 행 개수만큼 반복되고 그만큼 I값 증가)
FOR J IN 1..I LOOP
--1부터 I까지 꺼내서 J에 담기
DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || EMP_ID_TABLE(J) || ‘, EMP_NAME : ’
|| EMP_NAME_TABLE(J));
--1부터 I까지 J에 대입해서 화면에 값 출력
END LOOP;
END;
/
DECLARE
TYPE EMPNO_TABLE_TYPE IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
V_ARR_NO EMPNO_TABLE_TYPE;
--TABLE 이 JAVA에서의 배열, ARRAYLIST 형태 가지게 될거다
TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
V_ARR_NAME ENAME_TABLE_TYPE;
V_IDX BINARY_INTEGER := 0;
BEGIN
FOR VO IN (SELECT * FROM EMP) LOOP
--여기가 여러번 반복되는 구간
--LIST<EMP> 모양
--EMP 테이블에서 한개의 값을 꺼내서 VO에 대입
--VO는 EMP모양으로 생김
--DBMS_OUTPUT.PUT_LINE(V_IDX);
--FOR문 안에 뿌려야 INDEX 나온다..
--DBMS_OUTPUT.PUT_LINE(VO.EMPNO || ':'||VO.SAL);
--여러행들 중 하나씩 나와서 행 끝날때까지 돌면서 값 뿌림
--이렇게만 뿌리면 반복 순서 번호 알 수 없어서 V_IDX BINARY_INTEGER 사용
V_IDX := V_IDX+1;
--증감식을 먼저 써야 1부터 시작
--증감식을 뒤로 빼면 0부터 시작
V_ARR_NO(V_IDX) := VO.EMPNO;
V_ARR_NAME(V_IDX) := VO.ENAME;
--DBMS_OUTPUT.PUT_LINE(V_IDX || ':' || V_ARR_NO(V_IDX) || ':' || V_ARR_NAME(V_IDX));
END LOOP;
-- 구구단
-- FOR I IN 2..9 LOOP
-- FOR J IN 1..9 LOOP
-- DBMS_OUTPUT.PUT_LINE(I||'*'||J||'='||I*J);
-- END LOOP;
FOR I IN 1..V_IDX LOOP
DBMS_OUTPUT.PUT_LINE(V_ARR_NO(I));
END LOOP;
END;
/
- TYPE EMP_ID_TABLE_TYPE :자료형 이름
EMPLOYEE.EMP_ID%TYPE : 배열 형태
=> DECLARE 안에 배열 형태로 선언
- TYPE 대신에 EMP%ROWTYPE 쓰는거 가능
- FOR IN END LOOP 가 한덩어리
FOR IN => 자바로 따지면 for each(한 행을 꺼내서 K에 대입)
레코드 타입의 변수 선언과 초기화, 변수 값 출력
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD (
EMP_ID EMPLOYEE.EMP_ID%TYPE,
EMP_NAME EMPLOYEE.EMP_NAME%TYPE,
DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE,
JOB_NAME JOB.JOB_NAME%TYPE
);
EMP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME INTO EMP_RECORD
FROM EMPLOYEE E, DEPARTMENT D, JOB J
WHERE E.DEPT_CODE = D.DEPT_ID
AND E.JOB_CODE = J.JOB_CODE
AND EMP_NAME = ‘&EMP_NAME’;
DBMS_OUTPUT.PUT_LINE(‘사번 : ’ || EMP_RECORD.EMP_ID);
DBMS_OUTPUT.PUT_LINE(‘이름 : ’ || EMP_RECORD.EMP_NAME);
DBMS_OUTPUT.PUT_LINE(‘부서 : ’ || EMP_RECORD.DEPT_TITLE);
DBMS_OUTPUT.PUT_LINE(‘직급 : ’ || EMP_RECORD.JOB_NAME)
END;
/
IF ~ THEN ~ END IF
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
INTO EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE EMP_ID = ‘&EMP_ID’;
DBMS_OUTPUT.PUT_LINE(‘사번 : ’ || EMP_ID);
DBMS_OUTPUT.PUT_LINE(‘이름 : ’ || EMP_NAME);
DBMS_OUTPUT.PUT_LINE(‘급여 : ’ || SALARY);
IF(BONUS = 0)
THEN DBMS_OUTPUT.PUT_LINE(‘보너스를 지급받지 않는 사원입니다.’);
END IF;
DBMS_OUTPUT.PUT_LINE(‘보너스율 : ‘ || BONUS * 100 || ‘%’);
--보너스가 없으면 보너스율: 0 이 뜸
--이거 뜨게 하기 싫어서 ELSE 넣고 이 문장 ELSE 안으로 넣어버리니까 안뜸
END;
/
IF ~ THEN ~ ELSE ~ END IF
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR2(20);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
FROM EMPLOYEE E, DEPARTMENT D, LOCATION L
WHERE E.DEPT_CODE = D. DEPT_ID
AND D.LOCATION_ID = L.LOCAL_CODE
AND EMP_ID = '&EMP_ID';
IF(NATIONAL_CODE = 'KO')
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);
DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/
IF ~ THEN ~ ELSIF ~ ELSE ~ END IF
DECLARE
SCORE INT;
GRADE VARCHAR2(2);
BEGIN
SCORE := ‘&SCORE’;
IF SCORE >= 90 THEN GRADE := ‘A’;
ELSIF SCORE >= 80 THEN GRADE := ‘B’;
ELSIF SCORE >= 70 THEN GRADE := ‘C’;
ELSIF SCORE >= 60 THEN GRADE := ‘D’;
ELSE GRADE := ‘F’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘당신의 점수는 ’ || SCORE || ‘점이고,
학점은 ‘ || GRADE || ‘학점입니다.’);
END;
/
BASIC LOOP
DECLARE
N NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N + 1;
IF N > 5 THEN EXIT;
END IF;
END LOOP;
END;
/
FOR LOOP
BEGIN
FOR N IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/
BEGIN
FOR N IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/
WHILE LOOP
DECLARE
N NUMBER := 1;
BEGIN
WHILE N <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N + 1;
END LOOP;
END;
/
예외처리: 미리 정의되지 않은 오라클 SERVER에 예외처리
CREATE OR REPLACE PROCEDURE PROC_TEST2
IS
AAA_UK EXCEPTION;
PRAGMA EXCEPTION_INIT(AAA_UK, -00001);
--PRAGMA: KEYWORD
BBB_OVERFLOW EXCEPTION;
PRAGMA EXCEPTION_INIT(BBB_OVERFLOW, -01438);
BEGIN
UPDATE EMP
SET EMPNO = 7369
WHERE EMPNO = 7788;
EXCEPTION
WHEN AAA_UK -- CATCH
THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
WHEN BBB_OVERFLOW -- CATCH
THEN DBMS_OUTPUT.PUT_LINE('4자리 숫자 입력해주세요. 자릿수보다 큰 값은 허용되지 않습니다.');
END;
/
UPDATE EMP
SET EMPNO = 78912311
WHERE EMPNO = 7788;
EXEC PROC_TEST1;
*JAVA배열
String[ ] strArr =
Integer[ ] inArr =
int i = 0;
for(StudentVo vo : StudentList) {
strArr[i] = vo.getName();
intArr[i] = vo.getNo();
i = i +1;
}