하위^^

스칼라 데이터 타입은 일반 단일 데이터타입 변수와 %TYPE 데이터형 변수가 있다.

일반 단일 데이터 타입은 타입을 직접 지정하고, 알고 있어야 하고, :=으로 초기값을 부여함

%TYPE 데이터형은 %TYPE만 사용하면 되고 데이터 타입을 몰라도 된다.

특정 테이블의 컬럼에 변수를 담는다.

SET SERVEROUTPUT ON;

-- 일반 단일 데이터 타입
-- 변수명 [CONSTANT] [DataType] [NOT NULL] := [초기값];

DECLARE
    var_emp_no NUMBER(3, 0);
    var_emp_name VARCHAR2(40);
    var_emp_grade CHAR(1) := 'A';
    BEGIN
        var_emp_no := 123;
        var_emp_name := 'kim';
        DBMS_OUTPUT.PUT_LINE('사원 번호 : '||var_emp_no);
        DBMS_OUTPUT.PUT_LINE('사원 이름 : '||var_emp_name);
        DBMS_OUTPUT.PUT_LINE('사원 등급 : '||var_emp_grade);
    END;
/
-- 초기 값을 생성 시 넣어도 되고 BEGIN, 시작하고 나서 넣어도 별 차이는 없다.

-- 사원번호 115에 대한 이름, 급여
DESC employees;
DECLARE
    var_emp_no NUMBER(3);
    var_emp_fn NVARCHAR2(8);
    var_emp_ln NVARCHAR2(2);
    var_emp_sal NUMBER;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = 115;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/






-- %TYPE 변수
-- [변수명] 테이블명.컬럼명%TYPE
-- 변수에 테이블, 컬럼을 입력해서 타입을 가져옴
DECLARE
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = 115;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/


-- %TYPE을 이용한 프로시저
CREATE OR REPLACE PROCEDURE emp_in(ref_emp_no IN employees.emp_no%TYPE)
    IS
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = ref_emp_no;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/

EXEC emp_in(120);

PL/SQL은 Procedual Language SQL의 약자로

프로시저라고 부른다.

PL은 SQL에서 변수 정의, 조건문, 제어문 등 프로그래밍 언어에서 사용하던 비슷한 기능, 함수들을 사용할 수 있다.

장점은 SQL문을 한번에 DB에 보내 처리하여 속도가 향상된다.

단점으로는 실행 주기 설정이 어렵고 SQL과 PL/SQL 형태만 실행 가능, DB 이외의 프로그램을 실행 할 수 없다.

실행할 작업과 이름을 지정한 부분을 PL/SQL BLOCK이라고 한다.

함수와 똑같이 매개변수를 받고 재사용 할 수 있는 BLOCK이다.

대개 반복 실행, 구현이 복잡한 트랜잭션을 실행하기 위해 생성한다.

CREATE OR REPLACE PROCEDURE [임의의 이름]

    IS

 -- 초기 변수 선언

        [변수 이름] [변수 타입] := [초기값];

    BEGIN

        [실행 명령어];

    END;

/

/, 슬래시로 닫아 마무리하여 PL/SQL BLOCK을 만든다.

가끔 프로시저는 주석문을 위 아래 같은줄 어디든 영향받을 때가 있는거 같은데 왜 그런지는 정확히 모르겠다.

가장 큰 특징으로는 Return 값이 따로 없기 때문에 UPDATE같은 구문으로 수정을 바로 해야함

그 외의 실행이라던지 접속 연결이라던지는 다음 코드에서 설명한다.

-- 1) 매개변수가 없는 프로시저
CREATE OR REPLACE PROCEDURE hello
    IS
        msg VARCHAR2(20) := 'Hello, world';
 -- 초기 변수 선언
    BEGIN
        DBMS_OUTPUT.PUT_LINE(msg);
    END;
/

-- 프로시저 종료
-- DBMS_OUTPUT 출력을 위한 구문(WEB_USER 연결시 딱 한 번만 설정 해주면 접속 종료할 때 까지 유지됨)
SET SERVEROUTPUT ON;

-- 프로시저 실행
-- EXEC [프로시저 이름]
EXEC hello;


-- 2) IN 매개변수가 있는 경우
-- IN 매개변수 : 인자값을 받는 변수, 변수형에는 크기를 따로 입력하지 않음.
-- 사원번호를 받아 특정 사원의 급여를 10% 올려주는 프로시저 생성
SELECT * FROM employees;
DESC employees;

SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE upSalary(eno IN NUMBER)
    IS
    BEGIN
        UPDATE employees SET salary = salary * 1.1 WHERE emp_no = eno;
        COMMIT;
    END;
/

EXEC upSalary(118);



-- 3) OUT 매개변수
-- 프로시저에는 반환(Return 값)이 없기 때문에 OUT 매개변수를 활용한다.
-- 사원 번호를 넣으면 사원명과 급여를 반환
-- 115번의 이름과 급여 출력
CREATE OR REPLACE PROCEDURE find_emp(v_eno IN NUMBER, v_fname OUT NVARCHAR2, v_sal OUT NUMBER)
    IS
    BEGIN
        SELECT first_name, salary
        INTO v_fname, v_sal
        FROM employees 
        WHERE emp_no = v_eno;
    END;
/
-- SELECT 인자들에 맞춰 INTO에 빼낼 값들을 타입과 자리를 맞춰서 기입
-- 실행은 기존과 다름
-- OUT 매개변수는 실행 전 받아 줄 변수(BIND, 바인드 변수)를 선언해야 한다.
-- 바인드 변수 선언 시 실제 컬럼 속성 보다 조금 더 여유있게 선언하는게 좋다.
VARIABLE v_fname NVARCHAR2(10);
VARIABLE v_sal NUMBER;
-- 실행
EXEC find_emp(115, :v_fname, :v_sal);

PRINT v_fname;
PRINT v_sal;



-- IN/OUT 매개변수
-- 매개변수로 시작하지만 종료시 OUT 변수 기능을 함
-- 일반적으로 프로시저가 다른 프로시저 호출 후 반환값을 사용 할 경우 활용
-- 사원번호를 넣으면 해당 사원의 급여 출력
CREATE OR REPLACE PROCEDURE find_sal(v_eno IN OUT NUMBER)
    IS
    BEGIN
        SELECT salary INTO v_eno FROM employees WHERE emp_no = v_eno;
    END;
/
-- 익명 프로시저, 일회용 프로시저, 저장이 안되고 한번 쓰고 사라짐
-- 위에 선언한 find_sal을 다시 이용
DECLARE 
    v_eno NUMBER := 115;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('eno = '||v_eno);
        find_sal(v_eno);
        DBMS_OUTPUT.PUT_LINE('salary = '||v_eno);
    END;
/

-- 프로시저 조회
SELECT object_name,object_type FROM USER_PROCEDURES;

-- 프로시저 삭제
DROP PROCEDURE hello;

Sequence는 자동으로 증가하는 Object이다.

데이터에 번호를 붙일 때 사용하며 키값으로 자주 사용한다.

-- 1) 시퀀스 생성

-- CREATE SEQUENCE [sequence name]

-- [START WITH n] [INCREMENT BY n]

-- [MAXVALUE n | NOMAXVALUE]

-- [MINVALUE n | NOMINVALUE]

-- [CYCLE | NOCYCLE]

-- [CACHE | NOCACHE]

-- START WITH n : 시퀀스의 시작값 n 설정(default : 1)

-- INCREMENT BY n : 시퀀스 증가값 n 설정(default:1)

-- MAXVALUE n : 최대값 n 설정(지정하지 않으면 9999....,n을 넘으면 에러)

-- MINVALUE n : 최소값 n 설정(default:1)

-- CYCLE:최대값 도달시 순환 여부(default:nocycle)

-- cycle 선언시 cache 필요

-- CACHE : 원하는 숫자만큼 미리 만들어 저장

-- 1-1) 일반적인 시퀀스 생성

CREATE SEQUENCE seq_empno;

-- 1-2) 8000 부터 시작하고 2만큼 증가하며 20개씩 저장하는 시퀀스

CREATE SEQUENCE seq_empno 

START WITH 8000 INCREMENT BY 2 CACHE 20;

-- 시퀀스 증가

SELECT seq_empno.NEXTVAL FROM DUAL;

-- 현재 시퀀스 값 확인

SELECT seq_empno.CURRVAL FROM DUAL;

-- 2) 기타 옵션들

CREATE SEQUENCE seq_test_max MAXVALUE 10;

-- MAX VALUE가 10이므로 11번째 에러가 나타난다.

SELECT seq_test_max.NEXTVAL FROM DUAL;

-- CYCLE 옵션 + CACHE가 필요

CREATE SEQUENCE seq_test_cycle START WITH 5 MAXVALUE 10 CYCLE CACHE 5;

SELECT seq_test_cycle.NEXTVAL FROM DUAL;

-- CYCLE 옵션 + CACHE + MINVALUE

CREATE SEQUENCE seq_test_min START WITH 5 MINVALUE 5 MAXVALUE 10 CYCLE CACHE 5;

SELECT seq_test_min.NEXTVAL FROM DUAL;

-- 3) 시퀀스의 실제 활용 예시

SELECT * FROM emp;

-- emp 테이블을 활용에 적절하게 변경

ALTER TABLE emp ADD(emp_no NUMBER(5));

INSERT INTO emp (emp_no, ename, job, deptno, hiredate) VALUES (seq_empno.NEXTVAL, 'oh', 'manager', 3, SYSDATE);

SELECT * FROM emp;

-- 4) 시퀀스 수정(START WITH n을 제외한 나머지 옵션 수정 가능)

ALTER SEQUENCE seq_empno INCREMENT BY 10 CACHE 40;

SELECT seq_empno.NEXTVAL FROM DUAL;

-- 5) 시퀸스 검색

SELECT * FROM USER_SEQUENCES;

-- 6) 시퀸스 삭제

DROP SEQUENCE seq_text_max;