4. PL/SQL 프로그램의 SQL문
(오라클 데이터베이스 서버와 상호작용)
A. PL/SQL의 SQL문
*** END 키워드는 TX의 끝이 아닌, PL/SQL 블록의 끝!
TX도 다중 블록을 확장할 수 있다.
*** PL/SQL은 DDL, DCL을 ‘직접’ 지원하지 않는다.
=> ‘동적 SQL’을 사용, DDL, DCL 문 실행 가능
B. PL/SQL의 SELECT 문
-> NO_DATA_FOUND, TOO_MANY_ROWS로 예외 처리
ex)
DECLARE
v_emp_hiredate employees.hire_date%TYPE;
v_emp_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_emp_hiredate, v_emp_salary
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE (‘Hire date is : ’ ||
v_emp_hiredate);
DBMS_OUTPUT.PUT_LINE (‘Salary is : ’ ||
v_emp_salary);
END;
/
C. 이름 지정 규칙
[이름의 혼동 가능성은 WHERE절에만 있음]
D. PL/SQL을 사용한 데이터 조작
COMMIT, ROLLBACK을 넣어 해제
E. 데이터 삽입
ex)
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES (employees_seq.NEXTVAL, ‘Ruth’, ‘Cores’,
‘RCORES’, CURRENT_DATE, ‘AD_ASST’, 4000);
END;
/
F. 데이터 갱신
하지만! 할당 연산자의 오른쪽의 식별자는 DB의 컬럼일 수도,
PL/SQL의 변수일 수도 있음!
(UPDATE의 SET에서의 양쪽의 SALARY)
PL/SQL의 SELECT와 달리 오류 발생하지 않음
*** PL/SQL 변수 할당은 :=
SQL 컬럼의 할당은 =
ex)
DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = ‘ST_CLERK’;
END;
/
G. 데이터 삭제
DECLARE
deptno departments.department_id%TYPE := 120;
BEGIN
DELETE FROM departments
WHERE department_id = deptno;
END;
/
H. 행 병합
CREATE TABLE copy_dept
AS select * from departments
WHERE department_id IN(10,20);
UPDATE copy_dept
SET department_name = ‘MKT’
WHERE department_id = 20;
BEGIN
MERGE INTO copy_dept c
USING departments d
ON (d.department_id = c.department_id)
WHEN MATCHED THEN
UPDATE SET
c.department_name = d.department_name,
c.manager_id = d.manager_id,
c.location_id = d.location_id
WHEN NOT MATCHED THEN
INSERT VALUES (d.department_id,
d.department_name, d.manager_id, d.location_id);
END;
/
I. SQL 커서
내부적으로 생성/관리
지정하여 한 번에 한 행씩 처리해야 하는 경우
‘전용 메모리 영역’
J. IMPLICIT CURSOR 속성
테스트할 수 있다.
SELECT 문이 아무 행도 검색하지 않으면 PL/SQL은 오류 반환
=> 커서 이름 대신 ‘SQL’을 붙여서 사용
->TRUE , 부울 속성
->TRUE, 부울속성
-> 정수 값
ex)
DECLARE
v_rows_deleted VARCHAR2(30);
v_deptno copy_dept.department_id%type := 20;
BEGIN
DELETE FROM copy_dept
WHERE department_id = v_deptno;
v_rows_deleted := (SQL%ROWCOUNT ||
‘ row deleted. ‘);
DBMS_OUTPUT.PUT_LINE (v_rows_deleted);
END;
/