SQL
2022_11_25 PLSQL
0304호
2022. 11. 25. 18:43
--2022_11_25
--PLSQL(프로그래밍SQL)
/*
오라클에서 제공되는 프로그래밍 기능이다.
일반적인 프로그래밍과는 차이가 있지만, 오라클 내부에서 아주 적절한 방식을 적용해준다
쿼리문의 집합으로 어떠한 동작을 일괄처리하기 위한 용도로 사용됨
절차형 SQL(PLSQL)은 코드부분만 선택하여 F5로 컴파일하야 실행함
*/
--출력을 허용함
SET SERVEROUTPUT ON;
-----------------------------------------------
DECLARE
vi_num NUMBER; -- 변수선언
BEGIN
vi_num := 100; --대입
DBMS_OUTPUT.PUT_LINE(vi_num); --출력
END;
---------------------------------------------
/*
연산자
일반 sql문의 모든 연산자의 사용이 가능하고
특별하게 **는 제곱을 의미합니다.
*/
DECLARE
a NUMBER := 2**2*3**2; --4*9
BEGIN
DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;
---------------------------------------------
/*
DML문
DML문은 사용이 불가능하고, 일반적인 SQL문의 SELECT절을 사용하는데
특이한점은 SELECT절 아래에 INTO절을 사용해 변수에 할당한다.
*/
DECLARE
-- v_emp_name VARCHAR2(50); --사원병(문자열 변수는 길이 제약이 필수)
-- v_dep_name VARCHAR2(50); --부서명
--해당 테이블과 같은 타입의 컬럼변수를 선언하려면 테이블명.컬럼명%TYPE을 사용하면됨
v_emp_name employees.first_name%TYPE;
v_dep_name departments.department_name%TYPE;
BEGIN
SELECT a.first_name, b.department_name
INTO v_emp_name, v_dep_name --대입을 의미
FROM employees a
LEFT OUTER JOIN departments b
ON a.department_id=b.department_id
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_emp_name || '-' || v_dep_name);
END;
---------------------------------------------------------------
--SELECT 문과 INSERT문 DML문을 같이 사용할 수 있다
CREATE TABLE EMP_SAL(
EMP_YEARS VARCHAR2(50),
EMP_SALARY NUMBER(10)
);
--년도별 사원의 급여합을 구해서 새로운 테이블 INSERT
DECLARE
EMP_SUM EMPLOYEES.SALARY%TYPE;
EMP_YEARS EMP_SAL.EMP_YEARS%TYPE :=2008;
BEGIN
--SELECT
SELECT SUM(SALARY)
INTO EMP_SUM
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY') = EMP_YEARS;
--INSERT
INSERT INTO EMP_SAL VALUES(EMP_YEARS,EMP_SUM);
--COMMIT
COMMIT;
DBMS_OUTPUT.PUT_LINE(EMP_SUM);
END;
SELECT * FROM EMP_SAL;
------------------------------------------------------------1. 구구단중 3단을 출력하는 익명 블록을 만들어 보자.
DECLARE
X NUMBER := 3;
Y1 NUMBER := 1;
Y2 NUMBER := 2;
Y3 NUMBER := 3;
Y4 NUMBER := 4;
Y5 NUMBER := 5;
Y6 NUMBER := 6;
Y7 NUMBER := 7;
Y8 NUMBER := 8;
Y9 NUMBER := 9;
ANS1 NUMBER := X*Y1;
ANS2 NUMBER := X*Y2;
ANS3 NUMBER := X*Y3;
ANS4 NUMBER := X*Y4;
ANS5 NUMBER := X*Y5;
ANS6 NUMBER := X*Y6;
ANS7 NUMBER := X*Y7;
ANS8 NUMBER := X*Y8;
ANS9 NUMBER := X*Y9;
BEGIN
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y1 || '=' || ANS1);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y2 || '=' || ANS2);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y3 || '=' || ANS3);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y4 || '=' || ANS4);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y5 || '=' || ANS5);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y6 || '=' || ANS6);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y7 || '=' || ANS7);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y8 || '=' || ANS8);
DBMS_OUTPUT.PUT_LINE(X || 'x' || Y9 || '=' || ANS9);
END;
--2. 사원테이블에서 201번 사원과 이름과 이메일 주소를 출력하는 익명 블록을 만들어 보자
--FIRST_NAME / EMAIL
DECLARE
EMP_NAME EMPLOYEES.FIRST_NAME%TYPE;
EMP_EMAIL EMPLOYEES.EMAIL%TYPE;
BEGIN
SELECT FIRST_NAME, EMAIL
INTO emp_name, emp_email
FROM EMPLOYEES
where employee_id = 201;
DBMS_OUTPUT.PUT_LINE(emp_name||' - ' || emp_email);
END;
--3. 사원 테이블에서 사원번호가 제일 큰 사원을 찾아낸 뒤,
--이 번호 +1번으로 아래의 사원을 EMPS에 INSERT하는 문장
--EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_dATE, JOB_ID를 신규 입력하는 익명 블록을 만들어 보자
--HINT :SELECT절 이후에 INSERT문의 사용이 가능합니다.
<사원명 > : STEVEN
<이메일> : STEVENJOBS
<입사일자> : 오늘날짜
<JOB_ID> : CEO
DECLARE
EMP_max_empno EMPLOYEES.employee_id%TYPE; --최대값 저장
BEGIN
SELECT max(employee_id)
into EMP_max_empno
FROM employees;
INSERT INTO emps( employee_id, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID )
VALUES(EMP_max_empno+1, 'steven', 'stevenjobs',sysdate,'ceo');
commit;
END;
SELECT SEQ_INFO.NEXTVAL FROM DUAL;
select*from INFO;
DELETE FROM INFO WHERE ID<=10;
CREATE TABLE EMPS AS (SELECT * FROM EMPLOYEES WHERE 1=2);--PLSQL제어문
-----------------------------------------------------
--랜덤수 출력
DECLARE
V_NUM NUMBER := ROUND(DBMS_RANDOM.value(0,10));
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NUM);
END;
------------------------------------------------------
--IF문장
--IF문 (IF (조건) THEN (구문) ELSE END IF);
DECLARE
NUM1 NUMBER := 5;
NUM2 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1,11)); --RANDOM수 저장
BEGIN
DBMS_OUTPUT.PUT_LINE('랜덤수 : ' || NUM2);
IF ( NUM1 >= NUM2 ) THEN
DBMS_OUTPUT.PUT_LINE ( NUM1 || '이 큰 수 입니다'); --단독 IF문
ELSE
DBMS_OUTPUT.PUT_LINE ( NUM2 || '이 큰 수 입니다'); --ELSE문
END IF;
END;
--------------------------------------------------------------
--ELSEIF문
DECLARE
RAN_NUM NUMBER:=TRUNC(DBMS_RANDOM.VALUE(1,101));
BEGIN
IF RAN_NUM>=90 THEN
DBMS_OUTPUT.PUT_LINE('A학점입니다');
ELSIF RAN_NUM>=80 THEN
DBMS_OUTPUT.PUT_LINE('B학점입니다');
ELSIF RAN_NUM>=70 THEN
DBMS_OUTPUT.PUT_LINE('C학점입니다');
ELSE
DBMS_OUTPUT.PUT_LINE('D학점입니다');
END IF;
END;
--실습
/*첫번째 값은 ROWNUM을 이용하면 됩니다.
10~120 사이의 10단위 랜덤한 번호를 이용해서 랜덤DEPARTMENT_ID 의 첫번째 행만 SELECT합니다.
뽑은 사람의 SALARYRK 9000이상이면 높음, 5000이상이면 중간, 나머지는 낮음으로 출력
*/
DECLARE
SCORE EMPLOYEES.SALARY%TYPE;
RAN NUMBER :=ROUND(DBMS_RANDOM.VALUE(10,120),-1);
BEGIN
SELECT SALARY
INTO SCORE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = ROUND(DBMS_RANDOM.VALUE(10,120),-1) AND ROWNUM=1;
DBMS_OUTPUT.PUT_LINE(SCORE);
IF SCORE>=9000 THEN
DBMS_OUTPUT.PUT_LINE('높음');
ELSIF SCORE>=5000 THEN
DBMS_OUTPUT.PUT_LINE('중간');
ELSE
DBMS_OUTPUT.PUT_LINE('낮음');
END IF;
END;
---------------------------------------------------------------------------------
DECLARE
SCORE EMPLOYEES.SALARY%TYPE;
RAN NUMBER :=ROUND(DBMS_RANDOM.VALUE(10,120),-1);
BEGIN
SELECT SALARY
INTO SCORE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = ROUND(DBMS_RANDOM.VALUE(10,120),-1) AND ROWNUM=1;
DBMS_OUTPUT.PUT_LINE(SCORE);
CASE WHEN SCORE>=9000 THEN
DBMS_OUTPUT.PUT_LINE('높음');
WHEN SCORE>=5000 THEN
DBMS_OUTPUT.PUT_LINE('중간');
ELSE
DBMS_OUTPUT.PUT_LINE('낮음');
END CASE;
END;
---------------------------------------------------------------------------
--반복문 WHILE
DECLARE
a NUMBER := 3;
b NUMBER := 1;
BEGIN
WHILE b <= 9
LOOP
DBMS_OUTPUT.PUT_LINE(a || ' x ' || b || ' = ' || a*b);
b := b + 1;
END LOOP;
END;
---------------------------------------------------------------------
--탈출문 EXIT WHEN 조건
DECLARE
a NUMBER := 3;
b NUMBER := 1;
BEGIN
WHILE b <= 9
LOOP
DBMS_OUTPUT.PUT_LINE(a || ' x ' || b || ' = ' || a*b);
EXIT WHEN b=5;
b := b + 1;
END LOOP;
END;
---------------------------------------------------------------------
--CONTINUE WHEN 조건
DECLARE
a NUMBER := 3;
b NUMBER := 1;
BEGIN
WHILE b <= 9
LOOP
DBMS_OUTPUT.PUT_LINE(a || ' x ' || b || ' = ' || a*b);
b := b + 1;
CONTINUE WHEN b=5;
END LOOP;
END;
----------------------------------
DECLARE
A NUMBER :=3;
BEGIN
FOR I IN 1..9
LOOP
CONTINUE WHEN I=5;
DBMS_OUTPUT.PUT_LINE(A || ' x ' || I || ' = ' || A*I);
END LOOP;
END;
--------------------------------------------------------------
--실습
--1. 모든 구구단을 출력하는 익명 블록
DECLARE
A NUMBER:=2;
BEGIN
FOR I IN 2..9
LOOP
FOR J IN 1..9
LOOP
DBMS_OUTPUT.PUT_LINE(I || ' x ' || J || ' = ' || I*J);
END LOOP;
END LOOP;
END;
--2. INFO테이블(시퀀스사용)에 INSERT를 300번 실행하는 익명블록을 처리하세요
CREATE SEQUENCE INFO_SEQ NOCACHE;
ALTER
DECLARE
BEGIN
FOR I IN 1..300
LOOP
INSERT INTO INFO( ID, TITLE, CONTENT, REGDATE, AUTH_ID )
VALUES(SEQ_INFO.NEXTVAL, 'TITLE','CONTENT',SYSDATE,I);
END LOOP;
END;
SELECT * FROM INFO;
/*
저장 프로시저 - 하나의 함수처럼 실행하기 위한 쿼리의 집합
만드는 과정과, 실행하는 구문을 나누어 작성합니다
*/
--프로시저 생성
/*
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC --매개변수
IS --변수의 선언 영역
BEGIN --실행영역
DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');
END;
*/
--------------------------------------------------
--프로시져 실행
EXECUTE NEW_JOB_PROC;
--프로시저 매개변수
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
(
P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SAL IN JOBS.MIN_SALARY%TYPE,
P_MAX_SAL IN JOBS.MAX_SALARY%TYPE
)
IS
BEGIN
INSERT INTO JOBS
VALUES (P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);
END;
------실행
EXECUTE NEW_JOB_PROC('SM_MAN1','SAMPLE TEST',1000,5000);
------조회
SELECT * FROM JOBS;
-------------------------------------------------
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
(
P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SAL IN JOBS.MIN_SALARY%TYPE,
P_MAX_SAL IN JOBS.MAX_SALARY%TYPE
)
IS
V_COUNT NUMBER := 0; --지역변수
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID; --V_COUNT라는 지역변수에 데이터 개수 할당
IF V_COUNT=0 THEN
INSERT INTO JOBS
VALUES (P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);
ELSE
UPDATE JOBS
SET JOB_TITLE = P_JOB_TITLE,
MIN_SALARY = P_MIN_SAL,
MAX_SALARY = P_MAX_SAL
WHERE JOB_ID = P_JOB_ID;
END IF;
END;
------실행
EXECUTE NEW_JOB_PROC('SM_MAN1','SAMPLE TEST22',1000,5000);
------조회
SELECT * FROM JOBS;
----------------------------------------------------------------------
--프로시저의 디폴트 매개변수
EXECUTE NEW_JOB_PROC('SM_MAN1','SAMPLE TEST2222'); --매개변수가 일치하지 않기 때문에 에러
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC
(
P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SAL IN JOBS.MIN_SALARY%TYPE :=0,
P_MAX_SAL IN JOBS.MAX_SALARY%TYPE :=1000
)
IS
V_COUNT NUMBER := 0; --지역변수
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID; --V_COUNT라는 지역변수에 데이터 개수 할당
IF V_COUNT=0 THEN
INSERT INTO JOBS
VALUES (P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);
ELSE
UPDATE JOBS
SET JOB_TITLE = P_JOB_TITLE,
MIN_SALARY = P_MIN_SAL,
MAX_SALARY = P_MAX_SAL
WHERE JOB_ID = P_JOB_ID;
END IF;
END;
COMMIT;
--디폴트 매개변수를 추가하면
EXECUTE NEW_JOB_PROC('SM_MAN1','SAMPLE TEST2222'); --디폴트 매개변수를 가지고있기 때문에 문제없음
-------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE NEW_JOB_PROC --매개변수
( P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SALARY IN JOBS.MIN_SALARY%TYPE := 0,
P_MAX_SALARY IN JOBS.MAX_SALARY%TYPE := 1000,
P_RESULT OUT VARCHAR2 --OUT
)
IS --변수의 선언영역
V_COUNT NUMBER := 0;
BEGIN --실행영역
SELECT COUNT(*)
INTO V_COUNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID;--V_COUNT의 데이터개수 할당
IF V_COUNT = 0 THEN
INSERT INTO JOBS
VALUES (P_JOB_ID, P_JOB_TITLE, P_MIN_SALARY, P_MAX_SALARY);
--OUT변수에 '성공'할당
P_RESULT := '성공';
ELSE
UPDATE JOBS
SET JOB_TITLE = P_JOB_TITLE,
MIN_SALARY = P_MIN_SALARY,
MAX_SALARY = P_MAX_SALARY
WHERE JOB_ID = P_JOB_ID;
--OUT변수에 '업데이트' 할당
P_RESULT := '업데이트';
END IF;
--예외 처리
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다');
END;
--OUT변수를 활용하려면 익명블록에서 실행
DECLARE
STR VARCHAR2(20); --OUT변수를 돌려받을 변수
BEGIN
NEW_JOB_PROC('SM_MAN4','SAMPLE',0,1000,STR);
DBMS_OUTPUT.PUT_LINE(STR);
END;
--------------------------------------------------------------------------------
--실습
--EMPLOYEE_ID 를 받아서 EMPLOYEES 에 존재하면, 근속년수를 출력
--없다면 없습니다를 출력하는 프로시저.
CREATE OR REPLACE PROCEDURE IF_EMPLOY
(
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS
P_COUNT NUMBER :=0;
P_HIRE_DATE EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
SELECT HIRE_DATE
INTO P_HIRE_DATE
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
DBMS_OUTPUT.PUT_LINE(P_HIRE_DATE);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('없습니다');
END;
--실행
EXECUTE IF_EMPLOY(100);