Declare 모든 변수, 상수 선언
Begin 제어문, 반복문, 함수정의 등의 로직을 기술함
Exception 실행 도붕 에러 발생시 해결하는 문장들을 기술함.
화면에 출력하려면 아래와 같은 사전 작업이 필요함
SET SERVEROUTPUT ON;
예
DECLARE
vno NUMBER(4);
vname VARCHAR2(10);
BEGIN
SELECT employee_id, first_name INTO vno, vname
FROM employees
WHERE employee_id = 200;
DBMS_OUTPUT.PUT_LINE(vno||' '||vname);
END;
익명 블록이 완료되었습니다.
예
DECLARE
v_empid employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT employee_id, first_name INTO v_empid , v_salary
FROM employees
WHERE employee_id = 197;
DBMS_OUTPUT.PUT_LINE(v_empid ||' '||v_salary );
END;
%type
employees테이블의 참고할 컬럼들의 타입으로 지정했음.
%rowtype
SET serveroutput ON
DELCLARE
v_row pl_employees3%ROWTYPE;
BEGIN
SELECT * INTO v_row
FROMK pl_employees3
WHERE emmployee_id =180;
DBMS_OUTPUT.PUT_LISE(v_row.employee_id || '===' || v_row.first_name || '==' || v_row.salary);
END;
INSERT
테이블 생성하고
시퀀스주고
PL/SQL INSERT하기
SQL 테이블에 값들어갔는지 보기
COMMIT하기
예
CREATE TABLE pl_test
(no NUMBER,
name varchar2(10));
CREATE SEQUENCE pl_seq;
BEGIN
INSERT INTO pl_test
VALUES(pl_seq.NEXTVAL, 'AAA');
END;
SELECT * FROM pl_test;
COMMIT;
입력값 받아서 INSERT해보자. 입력받으려면 %을 컬럼명 앞에 붙여야 함.
CREATE TABLE pl_test2
(no NUMBER,
name VARCHAR2(10),
addr VARCHAR2(10) );
DECLARE
v_no NUMBER := '&no';
v_name VARCHAR2(10) :='&name' ;
v_addr VARCHAR2(10) := '&addr' ;
BEGIN
INSERT INTO pl_test2
VALUES(v_no, v_name, v_addr);
END;
10 ENTER AAA ENTER 서울 ENTER
SELECT * FROM pl_tesr2;
UPDATE
BEGIN
UPDATE pl_test
SET name = 'BBB'
WHERE no = 2;
END;
SELECT * FROM pl_test;
COMMIT;
DELETE
BRGIN
DELETE FROM pl_TEST
WHERE no_1;
END;
SELECT * FROM pl_test;
COMMIT
MERGE
CREATE TABLE pl_merge1
(no NUMBER,
name VARCHAR2(10) );
CREATE TABLE pl_merge2
AS SELECT * FROM pl_merge1;
INSERT INTO pl_merge1 VALUES(1,'AAA');
INSERT INTO pl_merge1 VALUES(2,'BBB');
INSERT INTO pl_merge2 VALUES(1,'CCC');
INSERT INTO pl_merge2 VALUES(3,'DDD');
SELECT * FROM PL_MERGE1;
SELECT * FROM PL_MERGE2;
BEGIN
MERGE INTO pl_merge2 m2
USING pl_merge1 m1
ON(m1.no = m2.no)
WHEN MATCHED THEN
UPDATE SET
m2.name = m1.name
WHEN NOT MATCHED THEN
INSERT VALUES(m1.no, m1.name);
END;
SELECT * FROM pl_merge1;
SELECT * FROM pl_merge2;
중첩된 PL/SQL 블록
변수가 적용되는 범위는 아주 중요하다.
DECLARE
v_first VARCHAR2(5) := 'FIRST';
BEGIN
DECLARE
v_second VARCHAR2(5) := 'SECOND';
BEGIN
DBMS_OUT.PUT_LINE(v_first);
DBMS_OUT.PUT_LINE(v_second);
END;
DBMS_OUPUT.PUT_LINE(v_first):
END;
레코드 타입 변수
레코드 타입 변수를 선언하는 부분입니다.
SET SERVEROUTPUT ON;
DECLARE
TYPE emp_record_type IS RECORD
( emp_id employees.employee_id%TYPE,
f_name employees.first_name%TYPE,
e_sal employees.salary%TYPE) ;
v_rec1 emp_record_type;
BEGIN
SELECT employee_id, first_name, slary INTO v_rec1
FROM employees
WHERE employee_id = 180;
DBMS_OUTPUT.PUT_LINE(v_rec1.emp_id || '--' || v_rec1.f.name || '--' || v_rec1.e_sal);
END;
테이블 타입 변수
인덱스 만들어 줌
SET SERVEROUTPUT ON
DECLARE
t_name VARCHAR2(20);
TYPE tb1_emp_name IS TABLE OF
employees.first_name%TYPE
INDEX BY BINARY_INTEGER;
v_name tb1_emp_name;
BEGIN
SELECT first_name INTO t_name
FROM employees
WHERE employee_id = 180;
v_name(0) := t_name;
DBMS_OUTPUT.PUT_LINE(v_name(0) );
END;
601쪽~
Cursor
오라클 서버에서는 SQL문을 실행할 때 마다 처리(Parse, Execution)를 위한 메모리공간 (이 공간을 이후부터 SQL 커서라고 부르겠습니다.)을 사용합니다. 즉 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 커서로 복사ㅏ 해 온 후 커서에서 원하는 데이터를 추출하여(Fetch) 후속 작업을 하게 된다는 뜻입니다. 이 메모리 공간을 Private SQL Area 라고도 부르며, 오라클의 서버 프로세스 구성이 Dedicated Server 환경이냐 또는 MTS(Multi-Threaded Server)환경이냐에 따라 서버 내에 위치되는 곳이 다릅니다.
SQL커서는 크게 묵시적 커서(Implicate Cursor)와 명시적 커서(Explict Cursor)로 나눌 수 있습니다.
묵시적 커서에 저장되는 데이턴믄 1행만 가능합니다.
묵시적 커서 속성
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
명시적커서는 사용자가 선언하여 생성 후 사용하는 SQL 커서로 주로 여러 개의 행을 처리하고자 할 경우 사용합니다.
명시적 커서 속성
커서이름%ROWCOUNT
커서이름%FOUND
커서이름%NOTFOUND
커서이름%ISOPEN
DECLARE
vempid employees.employee_id%TYPE;
vfname employee.first_name%TYPE;
CURSOR c1 IS
SELECT employee_id, first_name
FROM employees
WHERE department_id = 30;
BEGIN
OPEN c!;
LOOP
FETCH c1 INTO vempid, vfnameL
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT_LINE(vempid || '==' || vfname);
END LOOP;
CLOSE c1;
END;
607P예시
Cursor FOR Loop문 활용하기 609예시 암묵적 알아서 OPEN FETCH CLOSE/ 610쪽 암묵적 커서 만들면서 바로 사용
DECLARE
611
파라미터 명시적 커서
612
잠금
613
EXCEPTION
런타임 에러 를 예외라고 한다.
frunction과 procedure 차이
return이 있고 없고 차이
실행할 때
PROCEDURE 은 EXEC(명령어)로 실행
FUNCTION 은 문장안에서 메소드처럼 사용.
procedure
CREATE OR REPLACE PROCEDURE up_sal
( vempid employees.employee_id%TYPE )
IS
BEGIN
UPDATE employees SET salary = 500
WHERE employee_ID = vempid;
END;
EXEC ip_sal(206);
SELECT employee_id, salary FROM employees
WHERE employee_id = 206;
frunction
CREATE OR REPLACE FUNCTION max_sal
(s_deptno employees.department_id%TYPE)
return NUMBER
IS
max_sal employees.salary%TYPE;
BEGIN
SELECT maq(salary) INTO max_sal
FROM employees
WHERE department_ID = s_deptno;
RETURN max_sal; --이 부분 데이터 형이 위 3번 줄의 형과 같아야 합니다.
END;
SELECT max_sal(10) FROM dual;
procedure 이라도 out할 수 있다. 참조값을 통해서
PACKAGE
연관성이 높은 함수나 프로시저를 하나의 그룹으로 묶어두는 개념.
패키지 선언부(Spec)와 패키지 몸체부(body)로 구성.
패키지 선언부의 역할은 해당 패키지에 사용될 함수나 프로시저, 변수 등에 대한 정의를 선언하는 부분
패키지 몸체부에서는 선언부에서 선언된 함수나 프로시저 등이 실제 구현되는 부분임.
패키지 선언부에서 선언되지 않더라도 패키지 몸체부에서 사용될 수는 있지만 별로 권장사항은 아니니 가급적 선언부에서 선언하신 후 몸체부에서 사용해야 함.
PACKAGE 구조 : 선언부와 몸체부로 구성됨
패키지 선언부 생성
CREATE [OR REPLACE] PACKAGE package_name
IS | AS
Public type and item declarations
Subprogram specifications
END packagename;
패키지 몸체부(Package Body) 생성
CREATE [OR REPLACE] PACKAGE BODY package_name
IS | AS
Private type and item declarations
Subprogram bodies
END package_name;
DROP PACKAGE package_name;
DROP PACKAGE BODY package_name;
예
패키비 선언부
CREATE OR REPLACE PACKAGE emp_total
AS
PROCEDURE emp_sum;
PROCEDURE emp_avg;
AND emp_total;
패키지 몸체부
CREATE OR REPLACE PACKAGE BODY emp_total AS
PROCEDURE emp_sum
IS
CURSOR emp_total_sum IS
SELECT COUNT(*), SUM(NVL(salary,0))
FROM employees;
total_num NUMBER;
total_sum NUMBER;
BRGIN
OPEN emp_total_sum;
FETCH emp_total_sum INTO total_num, total_sum;
DBMS_OUTPUT.PUT_LINE('총인원수 : ' || total_num ||' , 급여합계 : ' || total_sum);
CLOSE emp_total_sum;
END emp_sum; --emp_avg 프로시저 끝
PROCEDURE emp_avg -- emp_avg 프로시저 시작
IS
CURSOR emp_total_avg IS
SELECT COUNT(*), AVG(NVL(SALARY,0))
FROM employees;
total_num NUMBER;
total_avg NUMBER;
BEGIN
OPEN emp_total_avg;
FETCH emp_total_avg INTO total_num, total_avg;
DBMS_OUTPUT.PUT_LINE('총인원수: ' || total || ' , 급여평균: '|| total_avg);
CLOSE emp_total_avg;
END emp_avg; --프로시저 끝
END emp_total; --패키지 끝
패키지바디생성되었고
실행 하기
SET SERVEROUTPUT ON
EXEC emp_total.emp_sum; --패키지 이름, 프로시저 이름으로 실행 합니다.
위 패키지 샐행 결과가 맞는지 SQL을 직접 수행해서 검증해봅니다.
SELECT count(*) , sum(salary)
FROM employees;
급여합계 똑같음.
실행 하기
EXEC emp_total.emp_avg;
위 패키지 샐행 결과가 맞는지 SQL을 직접 수행해서 검증해봅니다.
SELECT count(*), avg(salary)
FROM employees;
급여평균 똑같음.
TRIGGER(트리거)
'DATA BASE' 카테고리의 다른 글
| JSP와 Java로 JSON 만들기 (0) | 2020.05.14 |
|---|---|
| Predefined ORACLE Exception (미리 정의되어 있는 오라클 예외들) (0) | 2020.05.13 |
| 20200507 / DELETE TRUNCATE DROP 차이점 비교 (0) | 2020.05.07 |
| 자바 - DB 연동 (0) | 2020.05.07 |
| 값초기화(NULL하고 다른 거 차이), NVL, VIEW, 인덱스 (0) | 2020.05.06 |
