Day061

PL/SQL

PROCEDURE

FUNCTION

EXCEPTION

EXCEPTION
        WHEN NO_DATA_FOUND THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('데이터 없다.');
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('OHTERS 걸림');

CURSOR

---

--PL/SQL
SELECT * FROM emp;

--화면 출력기능 활성화
SET SERVEROUTPUT ON;

CREATE TABLE pl_test
(
no number(10),
name varchar2(30)
);

CREATE SEQUENCE pl_test_seq
START WITH 1
INCREMENT BY 1;
select pl_test_seq.nextval from dual;
BEGIN
--PL/SQL 구문
DBMS_OUTPUT.PUT_LINE('내용 출력 부분');

--SELECT문은 INTO절이 필요하다.
--SELECT * FROM pl_test;

INSERT INTO pl_test VALUES (pl_test_seq.nextval, 'Name');

UPDATE pl_test
SET NAME = '변경'
WHERE no = 2;

DELETE FROM pl_test
WHERE no = 1;

COMMIT;

END;
/

SELECT * FROM pl_test;

--PL/SQL 문법 구조
DECLARE
v_no number(6);
v_name varchar2(30);
v_no_type pl_test.no%TYPE;
v_name_type pl_test.name%TYPE;
BEGIN
SELECT no, name
INTO v_no_type, v_name_type
FROM pl_test
WHERE no=7;

/*
IF '조건' THEN
ELSIF
ELSE
END IF;
*/
IF v_no_type = 6 THEN
    DBMS_OUTPUT.PUT_LINE('조건 6에 걸림');
ELSIF v_no_type = 7 THEN
    DBMS_OUTPUT.PUT_LINE('조건 7에 걸림');
ELSE
    DBMS_OUTPUT.PUT_LINE('조건 ELSE에 걸림');
END IF;

DBMS_OUTPUT.PUT_LINE('저장한 v_no:' || v_no || ' 저장한 v_name:' || v_name);

END;
/

--PROCEDURE --> 수행
--FUNCTION --> 수행결과

CREATE OR REPLACE PROCEDURE test_proc_1
--파라미터 부분
IS
--변수선언
v_no number(6);
v_name varchar2(30);
v_no_type pl_test.no%TYPE;
v_name_type pl_test.name%TYPE;
BEGIN
--실행
IF v_no_type = 6 THEN
DBMS_OUTPUT.PUT_LINE('조건 6에 걸림');
ELSIF v_no_type = 7 THEN
DBMS_OUTPUT.PUT_LINE('조건 7에 걸림');
ELSE
DBMS_OUTPUT.PUT_LINE('조건 ELSE에 걸림');
END IF;
END;
/

EXECUTE test_proc_1;
--매개변수
EXECUTE test_proc_1();

CREATE OR REPLACE PROCEDURE test_proc_2
(p_name IN varchar2)
--파라미터 부분
IS
BEGIN
INSERT INTO pl_test VALUES (pl_test_seq.nextval, p_name);

COMMIT;

END;
/

EXECUTE test_proc_2('인수이름1');

SELECT * FROM pl_test;

CREATE TABLE T_SCORE_A
(
stuno number(6),
score number(3)
);

CREATE TABLE T_SCORE_B
(
stuno number(6),
score number(3)
);

CREATE TABLE T_SCORE_TOTAL
(
stuno number(6),
score number(3)
);

INSERT INTO T_SCORE_A VALUES (1, 100);
INSERT INTO T_SCORE_B VALUES (1, 80);
INSERT INTO T_SCORE_TOTAL VALUES (1, 180);

CREATE OR REPLACE PROCEDURE PROC_INSERT_SCORE
(p_stuno IN T_SCORE_TOTAL.stuno%TYPE,
p_score_a IN T_SCORE_A.score%TYPE,
p_score_b IN T_SCORE_B.score%TYPE)

IS
v_total_score T_SCORE_TOTAL.score%TYPE;
BEGIN
INSERT INTO T_SCORE_A VALUES (p_stuno, p_score_a);
INSERT INTO T_SCORE_B VALUES (p_stuno, p_score_b);

v_total_score := p_score_a + p_score_b;
INSERT INTO T_SCORE_TOTAL VALUES (p_stuno, v_total_score);

COMMIT;

END;
/

EXECUTE PROC_INSERT_SCORE(1, 100, 80);

SELECT FROM T_SCORE_A;
SELECT FROM T_SCORE_B;
SELECT * FROM T_SCORE_TOTAL;

--SAMPLE PROCEDURE

CREATE TABLE BASE_MEMBER
(
id NUMBER(4),
name VARCHAR2(24)
);

CREATE TABLE BASE_MEMBER_HOBBY
(
--id를 PK로 지정하면 여러 취미를 저장할 수 없다.
id NUMBER(4),
no NUMBER(4),
hobby VARCHAR2(24),
CONSTRAINT BASE_MEMBER_HOBBY_PK PRIMARY KEY(id, no)
);

INSERT INTO BASE_MEMBER VALUES (1, '가사람');
INSERT INTO BASE_MEMBER VALUES (2, '나사람');
INSERT INTO BASE_MEMBER VALUES (3, '다사람');

SELECT * FROM BASE_MEMBER;

CREATE OR REPLACE PROCEDURE PROC_INSERT_MEMBER_HOBBY
(p_name IN BASE_MEMBER.name%TYPE,
p_hobby IN BASE_MEMBER_HOBBY.hobby%TYPE,
o_result OUT VARCHAR2)
IS
v_tgt_id base_member.id%TYPE;
v_next_no base_member_hobby.no%TYPE;
BEGIN

SELECT id
INTO v_tgt_id
FROM base_member
WHERE name = p_name;

DBMS_OUTPUT.PUT_LINE(v_tgt_id || '-' || p_name);

SELECT NVL(MAX(no), 0)+1
INTO v_next_no
FROM base_member_hobby
WHERE id = v_tgt_id;

DBMS_OUTPUT.PUT_LINE(v_tgt_id || '-' || v_next_no);

INSERT INTO base_member_hobby 
VALUES (v_tgt_id, v_next_no, p_hobby);

o_result := v_tgt_id || ' ' || v_next_no || ' ' || p_hobby;

COMMIT;

END;
/

SELECT *
FROM base_member_hobby;

DECLARE
out_result VARCHAR2(256);
BEGIN
PROC_INSERT_MEMBER_HOBBY('다사람', '운동', out_result);
DBMS_OUTPUT.PUT_LINE('결과' || out_result);
END;
/
--EXECUTE PROC_INSERT_MEMBER_HOBBY('가사람', '낮잠', '결과');

--FUNCTION

--급여 10% 인상 함수
CREATE OR REPLACE FUNCTION FUNC_UPDATE_SAL
(p_empno IN NUMBER)
RETURN NUMBER
IS
v_sal emp.sal%TYPE;
BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE empno = p_empno;

COMMIT;

SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_empno;

RETURN v_sal;

END;
/

SELECT * FROM emp;
--empno = 7844
SELECT FUNC_UPDATE_SAL(7844) FROM dual;

--함수 만들기 샘플

SELECT id, FUNC_FIND_MEMBER_NAME(id), no, hobby
FROM base_member_hobby;

--없는 아이디를 넘겨 줄 경우 null이 리턴된다.
SELECT FUNC_FIND_MEMBER_NAME(123) FROM dual;

CREATE OR REPLACE FUNCTION FUNC_FIND_MEMBER_NAME
(p_member_id IN NUMBER)
RETURN VARCHAR2
IS
v_member_name base_member.name%TYPE;
BEGIN
SELECT name
INTO v_member_name
FROM base_member
WHERE id = p_member_id;

RETURN v_member_name;

END;
/

--반복문
DECLARE
num NUMBER(4);
BEGIN
FOR cnt IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(cnt);
END LOOP;

num := 1;
LOOP
    DBMS_OUTPUT.PUT_LINE(num);
    num := num + 1;
    EXIT WHEN num > 5;
END LOOP;

WHILE(num < 10)
LOOP
    num := num + 1;
END LOOP;

END;
/

--반복문 중요!
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;

CURSOR tgt_emp_list IS
    SELECT empno, ename, dname
    FROM emp e, dept d
    WHERE e.deptno = d.deptno;

BEGIN
FOR emp_temp IN (SELECT empno, ename FROM emp)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_temp.empno || '-' || emp_temp.ename);
END LOOP;

DBMS_OUTPUT.PUT_LINE('-----커서 부분-----');
FOR emp_temp IN tgt_emp_list
LOOP
    DBMS_OUTPUT.PUT_LINE(emp_temp.empno || '-' || emp_temp.ename || '-' || emp_temp.dname);
END LOOP;

--커서 오픈
OPEN tgt_emp_list;
--커서 패치
LOOP
    FETCH tgt_emp_list INTO v_empno, v_ename, v_dname;
    DBMS_OUTPUT.PUT_LINE(v_empno || v_ename || v_dname);
    
    EXIT WHEN tgt_emp_list%NOTFOUND;    
END LOOP;
--커서 닫기
CLOSE tgt_emp_list;

END;
/

SELECT empno, ename, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT * FROM emp;

--커서 활용해보기
CREATE TABLE EMP_10
(
empno number(4),
ename varchar2(10)
);

CREATE TABLE EMP_20
(
empno number(4),
ename varchar2(10)
);

CREATE TABLE EMP_30
(
empno number(4),
ename varchar2(10)
);

CREATE OR REPLACE PROCEDURE CURSOR_TEST
IS
CURSOR emp_cursor IS
SELECT empno, ename, deptno
FROM emp;
BEGIN

FOR emp_data IN emp_cursor
LOOP
    IF emp_data.deptno = 10 THEN
        INSERT INTO emp_10 VALUES (emp_data.empno, emp_data.ename);
    ELSIF emp_data.deptno = 20 THEN
        INSERT INTO emp_20 VALUES (emp_data.empno, emp_data.ename);
    ELSE 
        INSERT INTO emp_30 VALUES (emp_data.empno, emp_data.ename);
    END IF;
END LOOP;

COMMIT;

END;
/

EXECUTE CURSOR_TEST;

SELECT empno, ename, (SELECT deptno FROM emp
WHERE empno = e10.empno)
FROM EMP_10 e10;
SELECT FROM EMP_20;
SELECT FROM EMP_30;

SELECT FROM BASE_MEMBER;
SELECT FROM BASE_MEMBER_HOBBY;