[Oracle] PL/SQL(1) 정리

 

PL/SQL(Procedural Language Extension to Structured Query Language)

 

프로그래밍 언어 기능(변수, 제어문, 함수...)을 절차적으로 확장한 언어이다.

컴파일이 필요 없어 스크립트 생성, 변경 후 바로 실행이 가능하다.

 

장점

1. 블록 내에서 논리적으로 관련된 문장들의 그룹화 가능 (프로그램 개발의 모듈화)
2. 절차적 언어 구조로 된 프로그램 작성 가능( IF문, LOOP문, Cursor)
3. Exception 처리가 가능하다.

---

PL/SQL 구조

--선언부
DECLARE
     --변수선언
        v_no NUMBER := 10;

    --상수선언
       c_message CONSTANT VARCHAR2(50) := '안녕하세요. PL/SQL';

--실행부
BEGIN
    --DBMS_OUTPUT패키지 안에 PUT_LINE 프로시저를 이용하여 결과 출력
       DBMS_OUTPUT.PUT_LINE('오늘부터 PL/SQL 시작');
       DBMS_OUTPUT.PUT_LINE(c_message);


END;

 

특정 테이블의 로우를 검색하여 변수에 할당한 수 출력

DECLARE
    v_name VARCHAR2(20);
    v_salary NUMBER;
    v_hiredate VARCHAR2(30);

BEGIN
    SELECT first_name, salary, TO_CHAR(hire_date, 'yyyy-mm-dd')
                INTO v_name, v_salary, v_hiredate
                FROM employees
                WHERE first_name = 'Ellen';

DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
DBMS_OUTPUT.PUT_LINE(v_name ||'  '||v_salary||'  '||v_hiredate);

END;

 

사원번호 100번에 해당하는 사원의 이름과 부서명을 출력


DECLARE
    v_id NUMBER;
    v_name VARCHAR2(20);
    v_dept VARCHAR2(20);

BEGIN
    SELECT e.employee_id, e.last_name, d.department_name
                INTO v_id, v_name, v_dept
                FROM employees e, departments d
                WHERE e.department_id = d.department_id
                AND employee_id = 100;

DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
DBMS_OUTPUT.PUT_LINE(v_id ||'  '||v_name||'  '||v_dept);

END;

---

데이터유형(기본형, 레퍼런스형)

DECLARE
    --기본형 데이터
    v_search VARCHAR2(30) := 'Lisa';
   

 --레퍼런스형

v_name employees.last_name%TYPE;

v_salary employees.salary%TYPE;

BEGIN
    SELECT last_name, salary
        INTO v_name, v_salary
        FROM employees
        WHERE first_name = v_search;

DBMS_OUTPUT.PUT_LINE(v_name||'  '||v_salary);

END;

 

---

ROWTYPE -> 1개의 로우타입을 갖는다.

DECLARE
    employee_record employees%ROWTYPE;
    v_department_name departments.department_name%TYPE;

BEGIN 
    SELECT * INTO employee_record
    FROM employees
    WHERE first_name = 'Lisa';

SELECT department_name INTO v_department_name
            FROM departments
            WHERE department_id = employee_record.department_id;
    

DBMS_OUTPUT.PUT_LINE(employee_record.employee_id||'  '||employee_record.first_name||'  '||v_department_name);

END;

 

40번 부서의 부서정보를 ROWTYPE을 이용하여 출력

DECLARE
dept_record dept%ROWTYPE;

BEGIN
    SELECT * INTO dept_record
    FROM dept
    WHERE deptno = 40;

DBMS_OUTPUT.PUT_LINE(dept_record.deptno||'  '||dept_record.dname||'  '||dept_record.loc);

END;