오늘 정리
커서란? SQL문을 처리한 결과 집합을 가리키는 포인터이다.SELECT 문의 결과 집합을 처리하는데 사용된다.
트리거는? 특정한 조건에 작동하도록 설정하여 호출할 필요 없이 자동으로 실행된다.데이터 제한, 감시 가능(보안), 데이터 무결성, 테이블 복제(동기화), 연속 작업 수행한다.
강의 내용
[PL/SQL문]
CREATE OR REPLACE [PROCEDURE, FUNCTION, TRIGGER] 이름()
IS
--선언부
BEGIN
--프로그램, dml, commit, rollback, 반복문
--여러건 한 번에 처리
--자동으로 채점하기
EXCEPTION
END;- 구조체
- 프로시저(재사용, 프로시저 호출, 커밋, 롤백 적용 가능)
- 함수(반환값 있음)
- 트리거
[프로시저]
- 화면 정의서 : 어떤 컬럼, 정보가 출력하면 되는지
- 변수저장
- select … into → 한 번에 한 건 씩 가능
- fecth … into → n건에 대한 처리 가능, 한 행 씩 접근(반복문과 결합)
[커서]
- 커서란? SQL문을 처리한 결과 집합을 가리키는 포인터이다.
- 질의의 결과로 얻어진 여러 행이 저장된 메모리상의 위치.
- 커서는 SELECT 문의 결과 집합을 처리하는데 사용된다.
- 테이블을 수정한다 → update & commit
- LOOP문 & EXIT(무한루프 방지)
- 기본문법
--기본 문법
DECLARE
CURSUR [커서이름] IS [SELECT 문];
BEGIN
OPEN [커서이름];
FETCH [커서이름] INTO [변수];
CLOSE [커서이름];
END;
출처: https://hoonmaro.tistory.com/50 [훈마로의 보물창고:티스토리][커서 예시 with emp, 사원 테이블에 속한 모든 사원의 급여 인상)]
CREATE OR REPLACE PROCEDURE proc_emp_update3(p_deptno IN number)
IS
--평균 급여 담기
avg_sal number(7,2) := 0;
--커서에서 꺼내온 사원번호 담기
v_empno number(5) :=0;
--커서에서 꺼내온 급여 담기
v_sal number(7,2) :=0;
--커서에서 꺼내온 이름 담기
v_ename varchar2(20) :='';
--급여 인상분 담기
rate number(3,1) :=0;
--커서 선언하기
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_deptno;
BEGIN
--급여 평균을 구해보자.
SELECT avg(sal) INTO avg_sal
FROM emp
WHERE deptno = p_deptno;
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno, v_ename, v_sal;
EXIT WHEN emp_cur%NOTFOUND;
IF v_sal > avg_sal THEN
rate :=1.1;
dbms_output.put_line(v_empno||'의 인상 전 금여 : '||v_sal||' / 인상분 급여 : '||v_sal*rate);
ELSIF v_sal <= avg_sal THEN
rate:=1.2;
dbms_output.put_line(v_empno||'의 인상 전 금여 : '||v_sal||' / 인상분 급여 : '||v_sal*rate);
END IF;
UPDATE emp
SET sal = sal * rate
WHERE empno = v_empno;
END LOOP;
COMMIT;
CLOSE emp_cur;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;[커서 및 프로시저 실습 with sw_design]
- SW_DESIGN 코드&파일
sw_design_backup.txtCREATE TABLE SCOTT.SW_DESIGN ( D_NO NUMBER(4), SUB_CD NUMBER(4) NOT NULL, QUESTION VARCHAR2(500 BYTE) NOT NULL, ANSWER1 VARCHAR2(1000 BYTE) NOT NULL, ANSWER2 VARCHAR2(1000 BYTE) NOT NULL, ANSWER3 VARCHAR2(1000 BYTE) NOT NULL, ANSWER4 VARCHAR2(1000 BYTE) NOT NULL, D_POINT NUMBER(3) DEFAULT 0, DAP NUMBER(1) DEFAULT 0 ) TABLESPACE USERS PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE MONITORING; CREATE UNIQUE INDEX SCOTT.DESIGN_NO_PK ON SCOTT.SW_DESIGN (D_NO) LOGGING TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ); ALTER TABLE SCOTT.SW_DESIGN ADD ( CONSTRAINT DESIGN_NO_PK PRIMARY KEY (D_NO) USING INDEX SCOTT.DESIGN_NO_PK ENABLE VALIDATE);
- EXAM_PAPER 코드
CREATE TABLE HR.EXAM_PAPER ( EXAM_NO VARCHAR2(10 BYTE), DAP1 NUMBER(5) DEFAULT 0, DAP2 NUMBER(5) DEFAULT 0, DAP3 NUMBER(5) DEFAULT 0, DAP4 NUMBER(5) DEFAULT 0, RIGHT_ANSWER NUMBER(5) DEFAULT 0, WRONG_ANSWER NUMBER(5) DEFAULT 0 ) TABLESPACE USERS PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE MONITORING; CREATE UNIQUE INDEX HR.PAPER_NO_PK ON HR.EXAM_PAPER (EXAM_NO) LOGGING TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ); ALTER TABLE HR.EXAM_PAPER ADD ( CONSTRAINT PAPER_NO_PK PRIMARY KEY (EXAM_NO) USING INDEX HR.PAPER_NO_PK ENABLE VALIDATE); - TABLE IMPORT 과정
- 학생의 제출 답안에 대한 계산 프로시저
- 테이블에 대한 이해
SQL> variable msg varchar2(300);
SQL> exec proc_account1('2023112811',:msg);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print msg;
MSG
--------------------------------------------------------------------------------
정답 : 3, 오답 : 1
SQL> exec proc_account1('2023112811',:msg);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print msg;
MSG
--------------------------------------------------------------------------------
정답 : 3, 오답 : 1[프로시저로 하는 CRUD like jdbc API 오라클 연동하기]
- CRUD 처리
-----------------------------------
commit, rollback 대상 o : 1->2->3
🔸insert - 회원가입, 주문, 글등록
🔸update - 좋아요, 수정
🔸delete - 구독취소
------------------------------------
commit, rollback 대상 x : 1->2->3->4
🔸select - - 순서
- DriverClass 로딩(벤더 회사정보 취득) - 제품 -the others - 커넥션 맺기
- 쿼리문 전송하기 - DML 위해 preparestatement 로딩(인스턴스화 - 객체 생성 - 메모리)
- 처리 요청 - 오라클 서버가 일을 진행한 뒤 처리결과를 반환 - 일괄처리(효율성)
- select문 해당 - cursor 조작 - resultset
⇒ 프로시저는 커넥션을 한 번 맺으면 여러 쿼리문을 처리받으면서 사용할 수 있음.
SQL> exec proc_deptInsert(50, 'developer', 'Pusan');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from dept where deptno = 50;
DEPTNO DNAME LOC
---------- -------------- -------------
50 developer Pusan
CREATE OR REPLACE PROCEDURE proc_deptDelete(p_deptno IN number)
IS
BEGIN
DELETE FROM dept WHERE deptno = p_deptno;
COMMIT;
END;--생성, 삭제 프로시저 진행 후 진행이 잘되었는지 확인
--백엔드는 전처리, 후처리를 염두해두는 쿼리문 알고 있어야 함.
BEGIN
proc_deptInsert(50, 'developer', 'Pusan');
END;
BEGIN
proc_deptDelete(50);
END;
SELECT * FROM dept WHERE deptno = 50;
SELECT
nvl((SELECT deptno FROM dept <WHERE deptno = 50),-1) AS "null(-1)"
FROM dual;[트리거]
- 특정한 조건에 작동하도록 설정하여 호출할 필요 없이 자동으로 실행된다.
- 비활성화 또는 비활성화 할 수 있다.(disable, enable)
- 트리거 재컴파일 가능
- 응용 범위 : 보안(테이블 변경에 대해 제한 可)
Create Trigger 트리거명
Before (or After)
UPDATE OR DELETE OR INSERT ON 테이블명
[FOR EACH ROW] --히스토리 따질 때
DECLARE
변수선언부
BEGIN
프로그램 코딩부
END;--트리거 생성
CREATE OR REPLACE TRIGGER trg_dept
BEFORE
UPDATE OR DELETE OR INSERT ON dept
DECLARE
msg varchar2(200) := '';
BEGIN
IF UPDATING THEN
dbms_output.put_line('=>UPDATE');
END IF;
IF deleting THEN
dbms_output.put_line('=>Delete');
END IF;
IF INSERTING THEN
dbms_output.put_line('=>Insert');
END IF;
END;
--트리거 비활성화
ALTER TRIGGER trg_dept DISABLE;
--트리거 활성화
ALTER TRIGGER trg_dept ENABLE;
--트리거 재컴파일
ALTER TRIGGER 트리거명 COMPILE;
--트리거 삭제
DROP TRIGGER 트리거명;
💡PL/SQL 뭐가 있지?
프로시저 함수 트리거 특정 작업을 수행 특정 계산을 수행 특정한 조건에 작동하도록 설정 리턴값 가질 수도, 안 가질 수도 있음 리턴값 반드시 가짐 수정전/수정후 분류 可(old.칼럼/new.칼럼) 리턴값 여러개 가짐 리턴값 하나만 가짐 사용하지 않을 때는 비활성화 단독으로 문장 구성 가능(멀티는 cursor사용 필요) 단독으로 문장 구성 불가 데이터 제한, 감시 가능(보안), 데이터 무결성, 테이블 복제(동기화), 연속 작업 수행 명령어로 실행 명령어로 실행 자동 실행 커밋, 롤백 실행 o 커밋, 롤백 실행 o 커밋, 롤백 실행 x
- 트리거 실습
- dept 테이블을 생성, 수정, 삭제하면 dept_copy 테이블에도 동기화되어 실행되는 것을 확인할 수 있다.
--트리거 동기화, 복제, 보안 등 실습
CREATE TABLE dept_copy AS
SELECT * FROM dept;
CREATE OR REPLACE TRIGGER trg_deptcopy
AFTER
UPDATE OR DELETE OR INSERT ON dept
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO dept_copy(deptno, dname, loc)
VALUES(:NEW.deptno, :NEW.dname, :NEW.loc);
ELSIF UPDATING THEN
UPDATE dept_copy
set dname=:NEW.dname, loc=:NEW.loc
WHERE deptno = :OLD.deptno; --기존에 있는 것을 바꾸는 것이라서 old 사용
ELSIF deleting THEN
DELETE FROM dept_copy
WHERE deptno = :OLD.deptno;
END IF;
END;
--테스트 시나리오
--데이터 복제 트리거 적용여부 확인
INSERT INTO dept(deptno, dname, loc) VALUES(80, '전산과','서울');
UPDATE dept set loc = '포항' WHERE deptno = 80;
DELETE FROM dept WHERE deptno = 80;
