1. 실습 준비
1-1. PL/SQL 결과 출력
- PUT_LINE 프로시저를 이용
- 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트값이 OFF이므로) ON으로 변경
SET serveroutput ON
BEGIN
dbms_output.put_line('ynjch');
END;
1-2. 테스트용 테이블 준비
CREATE TABLE USER_INFO
(
USER_NUM NUMBER(10) NOT NULL
, USER_NM VARCHAR2(20)
, USER_BIRTH VARCHAR2(8)
);
SELECT * FROM USER_INFO;
DROP TABLE USER_INFO;
CREATE SEQUENCE USER_NUM_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 10000000 CYCLE NOCACHE;
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'USER_NUM_SEQ';
ALTER SEQUENCE USER_NUM_SEQ INCREMENT BY -1000;
SELECT USER_NUM_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE USER_NUM_SEQ INCREMENT BY 1;
CREATE TABLE USER_SCORE
(
USER_NUM NUMBER(10) NOT NULL
, USER_GRADE VARCHAR(5)
, CONSTRAINT user_score_pk PRIMARY KEY(USER_NUM)
);
SELECT * FROM USER_SCORE;
DROP TABLE USER_SCORE;
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '차은우', '19971026');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '서강준', '19971001');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '강태오', '19911022');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '지창욱', '20010107');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '이준혁', '19710606');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '임시완', '19970714');
INSERT INTO USER_SCORE values(1, 'A');
INSERT INTO USER_SCORE values(2, 'C');
INSERT INTO USER_SCORE values(3, 'A');
INSERT INTO USER_SCORE values(4, 'D');
INSERT INTO USER_SCORE values(5, 'B');
INSERT INTO USER_SCORE values(6, 'C');
CREATE TABLE STORE_INFO
(
STORE_NUM NUMBER(10) NOT NULL
, STORE_NM VARCHAR2(30)
, STORE_TEL VARCHAR2(20)
, STORE_ADDR VARCHAR2(30)
, OWNER_NUM NUMBER(10)
, CONSTRAINT STORE_info_pk PRIMARY KEY(STORE_NUM)
, CONSTRAINT STORE_info_fk FOREIGN KEY(OWNER_NUM) REFERENCES USER_INFO (USER_NUM)
);
SELECT * FROM STORE_INFO;
DROP TABLE STORE_INFO;
ALTER TABLE STORE_INFO MODIFY STORE_NM VARCHAR2(50);
ALTER TABLE STORE_INFO MODIFY STORE_ADDR VARCHAR2(50);
CREATE SEQUENCE STORE_NUM_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 10000000 CYCLE NOCACHE;
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'STORE_NUM_SEQ';
ALTER SEQUENCE STORE_NUM_SEQ INCREMENT BY -1000;
SELECT STORE_NUM_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE STORE_NUM_SEQ INCREMENT BY 1;
CREATE FUNCTION FUNC_STORE_NUM_SEQ RETURN NUMBER IS
BEGIN
RETURN STORE_NUM_SEQ.NEXTVAL;
END;
/
SELECT * FROM USER_SOURCE WHERE TYPE = 'FUNCTION' AND NAME = 'FUNC_STORE_NUM_SEQ';
DROP FUNCTION FUNC_STORE_NUM_SEQ;
INSERT INTO STORE_INFO (STORE_NUM, STORE_NM, STORE_TEL, STORE_ADDR, OWNER_NUM)
SELECT FUNC_STORE_NUM_SEQ, '카페노티드 제주애월', '070-4415-9377', '제주특별자치도 제주시', 1 FROM DUAL
UNION ALL SELECT FUNC_STORE_NUM_SEQ, '런던베이글뮤지엄 도산점', '02-111-4453', '서울 강남구', 2 FROM DUAL
UNION ALL SELECT FUNC_STORE_NUM_SEQ, '카페 레이어드 연남점', '02-2341-4277', '서울 마포구', 3 FROM DUAL
UNION ALL SELECT FUNC_STORE_NUM_SEQ, '카페 쿠운', '02-3245-8775', '서울 서초구', 5 FROM DUAL
UNION ALL SELECT FUNC_STORE_NUM_SEQ, '태시트 TACIT', '02-2341-4277', '강원 고성군', 5 FROM DUAL
UNION ALL SELECT FUNC_STORE_NUM_SEQ, '귤꽃다락', '02-2341-4277', '제주특별자치도 서귀포시', 5 FROM DUAL;
COMMIT;
2. 변수
- 선언부(DECLARE) 변수명, 데이터 타입을 기술
- identifier : 변수명(식별자)
- CONSTANT : 상수로 지정 (초기치를 반드시 할당해야 함)
- datatype : 자료형을 기술
- NOT NULL : 값을 반드시 포함
- expression : Literal, 다른 변수, 연산자나 함수를 포함하는 표현식
identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expression];
2-1. 변수 선언
DECLARE NAME VARCHAR2(10);
DECLARE NAME VARCHAR2(10) := 'ynjch';
DECLARE NAME VARCHAR2(10) DEFAULT 'ynjch';
DECLARE
NAME VARCHAR2(20);
AGE NUMBER(2);
GENDER VARCHAR(5) DEFAULT '여';
2-2. Type으로 변수 선언
2-2-1. %ROWTYPE
- 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태
DECLARE
userNum NUMBER(5) := 1;
rowData USER_INFO%ROWTYPE;
BEGIN
SELECT * INTO rowData
FROM USER_INFO
WHERE USER_NUM = userNum;
dbms_output.put_line(rowData.USER_NUM||'. '||rowData.USER_NM);
END;
2-2-2. %TYPE
- 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태
DECLARE
userNum NUMBER(5) := 1;
userNm USER_INFO.USER_NM%TYPE;
userBirth USER_INFO.USER_BIRTH%TYPE;
BEGIN
SELECT
USER_NM, USER_BIRTH INTO userNm, userBirth
FROM USER_INFO
WHERE USER_NUM = userNum;
dbms_output.put_line(userNm||'('||userBirth||')');
END;
- SELECT 문을 이용하여 값 대입
- INTO 절에 조회 결과 값을 저장할 변수를 기술
- SELECT 문은 INTO 절에 의해 하나의 행만을 저장 가능
- SELECT 이후 작성한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야 함 > 개수와 데이터 타입, 길이를 일치시켜야 함
3. 반복문
3-1. FOR LOOP문
- index는 자동 선언되는 binary_integer형 변수이며 1씩 증가
- REVERSE 옵션이 사용 될 경우 index는 upper_bound에서 lower_bound로 1씩 감소
- IN 다음에는 coursor나 SELECT 문이 올 수 있음
FOR index in [REVERSE] 시작값 .. END값 LOOP
STATEMENT 1
STATEMENT 2
...
END LOOP;
3-1-1. 예제
BEGIN
FOR i IN 1..5 LOOP
IF MOD(i,2) = 0 THEN
dbms_output.put_line(i||'는 짝수');
ELSE
dbms_output.put_line(i||'는 홀수');
END IF;
END LOOP;
END;
- USER_NUM 1~3 까지의 데이터 출력하기
DECLARE
userNum NUMBER(5) := 1;
rowData USER_INFO%ROWTYPE;
BEGIN
FOR i IN 1..3 LOOP
userNum := i;
SELECT * INTO rowData
FROM USER_INFO
WHERE USER_NUM = userNum;
dbms_output.put_line(rowData.USER_NM||'('||rowData.USER_NUM||')');
END LOOP;
END;
- SELECT 문을 이용하여 별도의 변수 선언 없이 사용 가능
BEGIN
FOR userList IN (
SELECT * FROM USER_INFO
) LOOP
dbms_output.put_line(userList.USER_NM||'('||userList.USER_NUM||')');
END LOOP;
END;
DECLARE
userNum NUMBER(5) := 1;
rowData USER_INFO%ROWTYPE;
BEGIN
SELECT * INTO rowData
FROM USER_INFO
WHERE USER_NUM = userNum;
FOR userList IN (
SELECT * FROM USER_INFO WHERE USER_NUM <> userNum
) LOOP
IF ( SUBSTR(rowData.USER_BIRTH,1,4) = SUBSTR(userList.USER_BIRTH,1,4) ) THEN
dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'의 친구');
ELSIF ( SUBSTR(rowData.USER_BIRTH,1,4) > SUBSTR(userList.USER_BIRTH,1,4) ) THEN
dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'보다 나이가 많다');
ELSE
dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'보다 나이가 적다');
END IF;
END LOOP;
END;
3-1-2. 예제
- 테이블 JOIN하여 유저들의 점수 조회하기 1
- USER_INFO, USER_SCORE 테이블의 컬럼을 담은 트랜잭션 GTT 생성(TX_USER_INFO_SCORE)
- WITH문, FOR LOOP문 사용
CREATE GLOBAL TEMPORARY TABLE TX_USER_INFO_SCORE (
USER_NUM NUMBER(10) NOT NULL
, USER_NM VARCHAR2(20)
, USER_BIRTH VARCHAR2(8)
, USER_GRADE VARCHAR(5)
)
ON COMMIT DELETE ROWS;
DECLARE
userCnt NUMBER;
rowData TX_USER_INFO_SCORE%ROWTYPE;
BEGIN
SELECT COUNT(1) INTO userCnt FROM USER_INFO;
dbms_output.put_line('userCnt : '||userCnt);
FOR i IN 1..userCnt LOOP
WITH TEMP_USER_INFO_SCORE AS
(
SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
FROM USER_INFO I
LEFT JOIN USER_SCORE S
ON I.USER_NUM = S.USER_NUM
)
SELECT * INTO rowData FROM TEMP_USER_INFO_SCORE WHERE USER_NUM = i;
dbms_output.put_line(rowData.USER_NM ||'('|| rowData.USER_NUM ||')''s score : ' || rowData.USER_GRADE);
END LOOP;
END;
- 테이블 JOIN하여 유저들의 점수 조회하기 2
FOR [변수명] IN [쿼리] LOOP
사용
- WITH문, FOR LOOP문 사용
DECLARE
BEGIN
FOR userList IN (
WITH TEMP_USER_INFO_SCORE AS
(
SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
FROM USER_INFO I
LEFT JOIN USER_SCORE S
ON I.USER_NUM = S.USER_NUM
)
SELECT * FROM TEMP_USER_INFO_SCORE ORDER BY USER_NUM
) LOOP
dbms_output.put_line(userList.USER_NM ||'('|| userList.USER_NUM ||')''s score : ' || userList.USER_GRADE);
END LOOP;
END;
3-2. LOOP문
- EXIT : 무조건 LOOP 문을 빠져나감
- EXIT WHEN : WHEN 절에서 LOOP를 빠져나가는 조건을 제어
LOOP
STATEMENT 1
다른 LOOP를 포함하여 중첩으로 사용 가능
EXIT [WHEN CONDITION]
END LOOP;
3-2-1. 예제
DECLARE
startNum NUMBER := 1;
endNum NUMBER := 5;
loopCnt NUMBER := 0;
BEGIN
LOOP
dbms_output.put_line('현재 숫자 : '||startNum);
loopCnt := loopCnt + 1;
startNum := startNum + 1;
EXIT WHEN loopCnt >= endNum;
END LOOP;
dbms_output.put_line('반복 횟수 : '||loopCnt);
END;
3-2-2. WHILE LOOP문
- 3-2-1. 의 예제를 WHILE LOOP문으로 표현
DECLARE
startNum NUMBER := 1;
endNum NUMBER := 5;
loopCnt NUMBER := 0;
BEGIN
WHILE loopCnt < endNum LOOP
dbms_output.put_line('현재 숫자 : '||startNum);
loopCnt := loopCnt + 1;
startNum := startNum + 1;
END LOOP;
dbms_output.put_line('반복 횟수 : '||loopCnt);
END;
4. 제어문
- 일반적인 프로그래밍에서 사용되는 IF문, CASE문 등의 제어문(조건절) 사용 가능
4-1. IF문
IF 조건1 THEN
처리문1
ELSIF 조건2 THEN
처리문2
ELSE
처리문
END IF;
4-1-1. 예제
DECLARE
userNum NUMBER := 1;
userBirthYear VARCHAR(4);
rowData USER_INFO%ROWTYPE;
BEGIN
SELECT * INTO rowData
FROM USER_INFO
WHERE USER_NUM = userNum;
userBirthYear := SUBSTR(rowData.USER_BIRTH, 1, 4);
IF (2022 - userBirthYear + 1) > 49 THEN
DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 50세 이상입니다.');
ELSIF (2022 - userBirthYear + 1) > 29 THEN
DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 30세 이상입니다.');
ELSIF (2022 - userBirthYear + 1) > 19 THEN
DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 20세 이상입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 20세 미만입니다.');
END IF;
END;
4-2. CASE문
- 조건에 따른 값을 대입하거나, PL/SQL 명령문 실행 시 사용
4-2-1. 예제
DECLARE
userNum NUMBER := 1;
userGrade USER_SCORE.USER_GRADE%TYPE;
resultStr VARCHAR(30);
BEGIN
SELECT USER_GRADE INTO userGrade
FROM USER_SCORE
WHERE USER_NUM = userNum;
resultStr :=
CASE userGrade
WHEN 'A' THEN 'Perfect'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'Hmm...'
END;
DBMS_OUTPUT.PUT_LINE(userGrade||' is '||resultStr);
END;
게시물 공유 감사합니다 :)