22-11-25 PLSQL구문, 프로시저

--PLSQL(프로그래밍SQL)
/*
오라클에서 제공되는 프로그래밍 기능이다.
일반적인 프로그램과는 차이가 있지만, 오라클 내부에서 아주 적절한 방식을 적용해준다
쿼리문의 집합으로 어떠한 동작을 일괄처리하기 위한 용도로 사용됩니다.

절차형SQL (PLSQL)은 코드 부분만 선택하여 f5로 컴파일 하여 실행합니다.
*/
--출력을 허용함
set serveroutput on;

DECLARE
    vi_num NUMBER; --변수 선언
BEGIN
    vi_num := 100; --대입
    DBMS_OUTPUT.PUT_LINE(vi_num); --출력 =sysout과 같음
END;

/*
--연산자
일반SQL문의 모든 연산자의 사용이 가능하고
특별하게 **는 제곱을 의미합니다.
*/
DECLARE
    a number := 2**2*3**2; --4 * 9
BEGIN
    DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;

--DML문
--DDL문은 사용이 불가능 하고, 일반적인SQL문의 select절을 사용하는데
--특이한 점은 select절 아래에 into 절을 사용해 변수에 할당합니다.

DECLARE
    --v_emp_name VARCHAR2(50); --사원명 변수(문자열변수는 길이제약이 필요)
    --v_dep_name VARCHAR2(50); -- 부서명 변수
    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_YEARS EMP_SAL.EMP_YEARS%TYPE := 2008;
    EMP_SUM EMPLOYEES.SALARY%TYPE;
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;

#PLSQL 연습

set serveroutput on;

DECLARE
    a number := 2**2*3**2; --4 * 9
BEGIN
    DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;

--1. 구구단 중 3단을 출력하는 익명 블록을 만들어보자.
DECLARE
    a number := 3*1;
    b number := 3*2;
    c number := 3*3;
    d number := 3*4;
    e number := 3*5;
    f number := 3*6;
    g number := 3*7;
    h number := 3*8;
    i number := 3*9;
BEGIN 
    DBMS_OUTPUT.PUT_LINE('a=' || TO_CHAR(a));
    DBMS_OUTPUT.PUT_LINE('b=' || TO_CHAR(b));
    DBMS_OUTPUT.PUT_LINE('c=' || TO_CHAR(c));
    DBMS_OUTPUT.PUT_LINE('d=' || TO_CHAR(d));
    DBMS_OUTPUT.PUT_LINE('e=' || TO_CHAR(e));
    DBMS_OUTPUT.PUT_LINE('f=' || TO_CHAR(f));
    DBMS_OUTPUT.PUT_LINE('g=' || TO_CHAR(g));
    DBMS_OUTPUT.PUT_LINE('h=' || TO_CHAR(h));
    DBMS_OUTPUT.PUT_LINE('i=' || TO_CHAR(i));
END;

select * from Employees;

--2. 사원 테이블에서 201번 사원의 이름과 이메일주소를 출력하는 익명 블록을 만들어 보자.
DECLARE
    name employees.first_name%type;
    email employees.Email%type;
BEGIN
    SELECT concat(First_name ||' ',last_name), Email
    into name, email
    from employees
    where Employee_id = 201;
    
    DBMS_OUTPUT.PUT_LINE('name=' || name || ' email=' || email);
END;





--3. 사원 테이블에서 사원번호가 제일 큰 사원을 찾아낸 뒤,
--이 번호 +1번으로 아래의 사원을 emps에
--employee_id, last_name, email, hire_date, job_id를 신규 입력하는 익명 블록을 만들어 보자.
--hint: select절 이후에 insert문의 사용이 가능합니다.
--<사원명> : steven
--<이메일> : stevenjobs
--<입사일자> : 오늘날짜
--<JOB_ID> : CEO
--create table emps as (select * from employees where 1=2);

DECLARE
    사원ID employees.employee_id%type; --최대값 저장
    
BEGIN
    --select구문
    select max(employee_id)+1
    into 사원ID
    from employees;

    --intsert 구문
    insert into emps (employee_id, last_name, email, hire_date, job_id)
                        values(사원ID+1,'Steven','StevenJOBS', sysdate, 'CEO');
    commit;
end;    

select * From emps;

#PLSQL제어문

--PLSQL제어문
--랜덤수 출력
DECLARE
    v_num number := ROUND(DBMS_RANDOM.value(0,10)); --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)); --랜덤수 저장
BEGIN
    DBMS_OUTPUT.PUT_LINE('랜덤수' || NUM2);
    IF(NUM1 >= NUM2) THEN 
        DBMS_OUTPUT.PUT_LINE(NUM1 || '이 큰수 입니다');
    ELSE
        DBMS_OUTPUT.PUT_LINE(NUM2 || '이 큰수 입니다');
    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합니다.
뽑은 사람의 SALARY가 9000이상이면 높음, 5000이상이면 중간, 나머지는 낮음으로 출력.
*/
--ROUND(DBMS_RANDOM.VALUE(10,120), -1);

select ROWNUM, d.* from departments d;

select *
    from (select ROWNUM rn,d.* from departments d)
    where rn = 1;

DECLARE
    SAL EMPLOYEES.SALARY%TYPE;
    RAN_NUM NUMBER := ROUND(DBMS_RANDOM.VALUE(10,120), -1); 
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = RAN AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('급여:' || SAL);
    IF SAL >= 9000 THEN
        DBMS_OUTPUT.PUT_LINE('높음');
    ELSIF SAL >= 5000 THEN
        DBMS_OUTPUT.PUT_LINE('중간');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('낮음');
    END IF;
END;    
    
--CASE문
DECLARE
    SAL EMPLOYEES.SALARY%TYPE;
    RAN_NUM NUMBER := ROUND(DBMS_RANDOM.VALUE(10,120), -1); 
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = RAN AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('급여:' || SAL);
    
    CASE WHEN SAL >= 9000 THEN DBMS_OUTPUT.PUT_LINE('높음');
         WHEN SAL >= 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;   
--FOR구문 - FOR 변수명 IN 1..9
--CONTINUE WHEN 조건
DECLARE
    A NUMBER := 3;
    B NUMBER := 1;
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
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번 실행하는 익명블록을 처리하세요.
DECLARE
    I NUMBER := 1;
BEGIN    
    WHILE I <= 300
    LOOP
        INSERT INTO INFO (ID, TITLE, CONTENT, REGDATE) VALUES(SEQ_INFO.NEXTVAL,'TEST'||i, 'TEST'||I,SYSDATE);
        INSERT INTO INFO (ID, TITLE, CONTENT, REGDATE) VALUES(SEQ_INFO.NEXTVAL,'TEST'||i, 'TEST'||I,SYSDATE);
        I := I + 1;
    END LOOP;
    commit;
END;    
select * from info;
ALTER SEQUENCE SEQ_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;
*/

--프로시저 응용
/*
EXECUTE NEW_JOB_PROC('SM_MAN1', 'sample test222', 1000, 5000);

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 test222'); --매개변수가 일치하지 않기때문에 에러
/*
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_MAN2', 'sample test222'); 
EXECUTE NEW_JOB_PROC('SM_MAN2', 'sample test222', 1000, 5000);
*/
SELECT * FROM JOBS;
--------------------------------------------------------------------------------------------------------
--매개변수 out
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,
     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의 데이터개수 할당
    --V_COUNT가 없다면 INSERT 있다면 UPDATE
    IF V_COUNT = 0 THEN
        INSERT INTO JOBS
        VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, P_MAX_SAL);    
        
        --OUT변수에 '성공' 할당
        P_RESULT := '성공';
    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;
    
    --OUT변수에 '업데이트' 할당
    P_RESULT := '업데이트';
    
    END IF;
    
    --예외처리 OTHERS이라고 하면 모든 예외처리를 받아줄 수 있음.
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다');
    
END;

--OUT변수를 활용하려면 익명블록에서 실행
DECLARE
    STR VARCHAR2(20); -- OUT을 돌려받을 변수
BEGIN
    NEW_JOB_PROC('SA_MAN4', 'SAMPLE', 0, 1000, STR);
    DBMS_OUTPUT.PUT_LINE(STR); --결과
END;

--------------------------------------------------------------------------------------------
--실습
--EMPLOYEE_ID를 받아서 EMPLOYEES에 존재하면, 근속년수를 출력
--없다면, 없습니다를 출력하는 프로시저.
SELECT * FROM EMPLOYEES;

CREATE OR REPLACE PROCEDURE NEW_EMPLOYEES_PROC
    (P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE
    )
IS --프로시저에서 사용할 매개변수
    V_COUNT NUMBER := 0;
    HIRE_YEARS NUMBER := 0;
BEGIN
    SELECT COUNT(*)
    INTO V_COUNT
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID =P_EMPLOYEE_ID;
    
    --ID가 없을 때
    IF V_COUNT =0 THEN 
        DBMS_OUTPUT.PUT_LINE('없습니다');
    ELSE
        --SELECT TRUNC((SYSDATE - HIRE_DATE)/365) FROM EMPLOYEES;
        SELECT TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(HIRE_DATE, 'YYYY')
        INTO HIRE_YEARS
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID = p_EMPLOYEE_ID;
        DBMS_OUTPUT.PUT_LINE('근속년수: ' || HIRE_YEARS ||'년');
    
    END IF;
END;    



SELECT TO_CHAR(HIRE_DATE, 'YYYY-MM-DD')
FROM EMPLOYEES;

EXECUTE NEW_EMPLOYEES_PROC(100);

저작자표시

'데이터베이스 수업 기록' 카테고리의 다른 글

22-11-28 DB마지막 수업 - 트리거 (0)	2022.11.28
22-11-22 JDBC, 데이터베이스와 자바의 연동 (0)	2022.11.22
22-11-21 데이터베이스 모델링 - 정규화 (0)	2022.11.21
22-11-18 롤(ROLE), 데이터베이스 모델링 (0)	2022.11.18
22-11-17 INDEX, 권한 DCL문 (0)	2022.11.17

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/05 »
일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

글 보관함

웹 개발

티스토리 뷰

22-11-25 PLSQL구문, 프로시저

'데이터베이스 수업 기록' 카테고리의 다른 글

티스토리툴바