오라클 데이터베이스
SQL 9일차(0720)
피아마수
2023. 7. 20. 11:37
커서 실행 후 반환 값
| 속성 | 뜻 |
| SQL%FOUND | sql문 실행후 반환된 행수가 1개 이상이다 (TRUE) |
| SQL%NOTFOUND | sql문 실행후 반환된 행수가 없다 (TRUE) |
| SQL%ISOPEN | 커서가 열려잇으면 TRUE - > 묵시적을 닫아서 항상 FALSE |
| SQL%ROWCOUNT | sql문 실행후 반영된 행의 수 (정수로 반환) |
자바에서 JDBC를 통해서 sql문을 실행하면 무조건 commit이 되는 데 이걸 막기위해서 java코드로 commit(false)해줘야함
conn.setAutoCommit(false);작업이 다끝나고 원하는 시점에 commit 하는 함수
conn.commit();자바에서 procedure에서 out 파라미터로 설정한거는 뭘 전달하는지 모르기때문에 getObject를 통해서 받아와서 형변환을 해주어야한다.
cstmt.registerOutParameter(4, Types.VARCHAR);Types.~ : 다른 데이터베이스의 파라미터도 받아올수 있는 범용 데이터타입
OracleTypes.~ : 오라클 데이터베이스에서만 사용할수 있는 오라클 전용 테이터 타입
DB에서 테스트하기
--developer 에서 테스트
var out_cursor REFCURSOR
exec usp_getEmpList('1980-01-01','1983-12-30',:out_cursor)
print out_cursor;
프로시저 장점
1. 보안상 좋다( 개발자가 sql문을 갖고 있지 않기때문)
2. 영속적인 데이터를갖고 있으면서 CRUD가 가능하다
프로시저는 단독 존재
function은 select 문법에 select ,where등등에 사용
function
create or replace function f_max_sal
(s_deptno emp.deptno%TYPE)--파라미터
return number -- public int f_max_sal(int deptno) { return 10}
--리턴타입 정의
is
max_sal emp.sal%TYPE;
BEGIN
select max(sal)
into max_sal
from emp
where deptno = s_deptno;
return max_sal;--실제 리턴되는 값
END;
트리거
어떤 사건(insert,delete, update)이 발생했을때 자동으로 실행되는 트랜잭션
하나의 테이블에 트리거 3개씩 등록가능하다
insert,delete, update 3개
내부적으로 트랜잭션을 가지고 있다
입고 재고 출고 가 있는게 출고 실패하면 재고도 입고도 롤백됌
"트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는
[저장 프로시저]라고 할 수 있다."
{}무조건쓰기
create or replace trigger tri_01
after insert on tri_emp --tri_emp 테이블에 insert 가 되고 나서
BEGIN -- 자동 동작할 내용
DBMS_OUTPUT.PUT_LINE('신입사원 입사');
END;트랜잭션 때문에 lock을 걸어서 트리거를 실행 중에는 다른 사람이 접근하지 못한다.
트리거 타입, 이벤트, 대상테이블, 활성화상태 를 확인하자
대보 1기 주제
데이터베이스 들어갈 랜덤 데이터를 원하는 갯수만큼 만들어주는 더미데이터 사이트
깃허브 연동해서 이벤트를 처리하는 프로젝트;;;
테이블에 DML할때마다 테이블에 기록 , 문장도 기록가능(해보기)
create or replace trigger emp_audit_tr
after insert or update or delete on emp2
for each row -- 5행 추가 되면 5번실행하도록함 ,원래는 한번만함 몇번수행이든
begin
if inserting then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'inserting', sysdate);
elsif updating then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'updating', sysdate);
elsif deleting then --어떤 이벤트인지 구분
insert into emp_audit
values(emp_audit_tr.nextval, user, 'deleting', sysdate);
end if;
end;
--update
--기존의 데이터를 삭제하고 새 데이터를 insert하는 작업이다.
이런걸 하려고 insert가상테이블과 delete가상테이블이 존재한다
들어가는 데이터를 기록하는 트리거
create or replace trigger emp_audit_tr
after insert or update or delete on emp2
for each row
begin
if inserting then
insert into emp_audit --insert는 new 만 사용하면된다.
values(emp_audit_tr.nextval, user, 'inserting', sysdate, :old.deptno, :new.deptno);
--old 가상테이블(기존데이터)--new 가상테이블(새로운 데이터)
elsif updating then--update는 old, new를 둘다사용
insert into emp_audit
values(emp_audit_tr.nextval, user, 'updating', sysdate, :old.deptno, :new.deptno);
elsif deleting then -- old 만 사용한다.
insert into emp_audit
values(emp_audit_tr.nextval, user, 'deleting', sysdate, :old.deptno, :new.deptno);
end if;
end;
select* from emp2;
--insert
insert into emp2(empno,ename,deptno) values (9999,'홍길동',100);
--deptno만 삽입했으니 이것만 들어감
select * from emp_audit;
--update
update emp2 set deptno=200
where empno=9999;
select * from emp_audit;
--delete
delete from emp2 where empno=9999;
select * from emp_audit;
insert , update, delete 트리거는 before와 update는 인지하기 못하기 때문에 상관없이 3개임
데이터를 넣을때 유효성 검사
예외를 강제 발생
아래는 시간 대에 삽입을 불가능하게 한다
create or replace trigger trigger_order
before insert on tri_order
BEGIN
IF(to_char(sysdate,'HH24:MM') not between '12:00' and '18:00') THEN
RAISE_APPLICATION_ERROR(-20002, '허용시간 오류 쉬세요');
END IF;
END;다른 테이블에 같은 데이터를 입력하고 싶다
--입고 데이터 들어오면 같은 데이터를 재고 입력
create or replace trigger insert_t_01
after insert on t_01
for each row
BEGIN
insert into t_02(no, pname)
values(:NEW.no ,:NEW.pname);
END;
--입고
insert into t_01 values(1,'notebook');
ps-sql 0720 공부한거
--emp 테이블
--CURD 프로시져 만들기
CREATE OR REPLACE PROCEDURE pc_update_deptno
(
e_empno IN number,
e_deptno IN number
)
IS
BEGIN
UPDATE c_emp
SET deptno = e_deptno
WHERE empno = e_empno;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
END;
select * from c_emp;
select * from c_dept;
select * from user_constraints where table_name='C_EMP';
select * from user_constraints where table_name='C_DEPT';
exec pc_update_deptno(1,500);
--exec pc_update_deptno('','')
--여러건의 데이터 조회시 JDBC 작업....
create or replace procedure usp_getEmpList
(
s_hiredate in varchar2,
e_hiredate in varchar2,
list_cursor out SYS_REFCURSOR --약속된 ...
)
is
begin
open list_cursor FOR
select empno,ename,job,sal , hiredate
from emp
where hiredate between to_date(s_hiredate,'YYYY-MM-DD') and to_date(e_hiredate,'YYYY-MM-DD')
--자바에서 문자로 받아온 날짜를 dB에서 날짜로 변환해서 사용한다.
order by hiredate desc;
exception
when others then
DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
end;
--developer 에서 테스트
var out_cursor REFCURSOR
exec usp_getEmpList('1980-01-01','1983-12-30',:out_cursor)
print out_cursor;
/*
String proc_call = "{? = call emptest(?)}";
// create callable statement
cstmt = conn.prepareCall(proc_call);
// key here is to register the output parameter
// of type cursor, execute, then cast it as a ResultSet.
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setInt(2, 10); //10번 부서의 데이터만 얻기 위해
cstmt.executeQuery();
rs = (ResultSet)cstmt.getObject(1);
while(rs.next()) {
out.println(rs.getString("ename") + "<br>");
}
*/
---------------------기본 procedure END-----------------------------------------
--[사용자 정의 함수]
--to_char() , sum() 오라클에서 제공
--사용자가 직접 필요한 함수를 만들어 사용가능
--사용방법은 다른 함수사용법과 동일
--사용자 정의 함수 paramter 정의 , return 값
create or replace function f_max_sal
(s_deptno emp.deptno%TYPE)--파라미터
return number -- public int f_max_sal(int deptno) { return 10}
--리턴타입 정의
is
max_sal emp.sal%TYPE;
BEGIN
select max(sal)
into max_sal
from emp
where deptno = s_deptno;
return max_sal;--실제 리턴되는 값
END;
---
select * from emp where sal = f_max_sal(10);
select max(sal) , f_max_sal(30) from emp;
--
create or replace function f_callname
(vempno emp.empno%TYPE)
return varchar2 -- public String f_callname() { String v_name; return "홍길동"}
is
v_name emp.ename%TYPE;
BEGIN
select ename || '님'
into v_name
from emp
where empno=vempno;
return v_name;
END;
select f_callname(7788) from dual;
select empno, ename , f_callname(7788) , sal
from emp
where empno=7788;
select empno, ename , f_callname(empno) , sal
from emp
where empno=7788;
select * from emp;
--함수
--parmater 사번을 입력받아서 사번에 해당되는 부서이름을 리턴하는 함수
create or replace function f_get_dname
(vempno emp.empno%TYPE)
return varchar2
is
v_dname dept.dname%TYPE;
BEGIN
select dname
into v_dname
from dept
where deptno = (select deptno from emp where empno=vempno);
return v_dname;
END;
select empno , ename ,deptno, f_get_dname(empno)
from emp
where empno=7788;
--------------------------function END------------------------------------------
--[트리거 : Trigger]
--트리거(trigger)의 사전적인 의미는 방아쇠나 (방아쇠를) 쏘다, 발사하다,
--(사건을) 유발시키다라는 의미가 있다.
--[입고] [재고] [출고]
--입고 INSERT (내부적으로 [트랜잭션]이 동작)
--재고 INSERT
--위험부담 : lock
--PL/SQL에서의 트리거 역시 방아쇠가 당겨지면 자동으로 총알이 발사되듯이
--어떠한 이벤트가 발생하면 그에 따라 다른 작업이 자동으로 처리되는 것을 의미한다.
/*
트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는
[저장 프로시저]라고 할 수 있다.
앞서 배운 저장 프로시저는 필요할 때마다 사용자가 직접
EXECUTE 명령어로 호출해야 했다.
하지만 트리거는 이와 달리 테이블의
데이터가 INSERT, UPDATE, DELETE 문에 의해 변경되어질 때
[ 자동으로 수행되므로 이 기능을 이용하며 여러 가지 작업 ] 을 할 수 있다.
이런 이유로 트리거를 사용자가 직접 실행시킬 수는 없다.
--자동(insert, update ,delete)이벤트가 발생하면 자동으로 실행되는 procedure ...
--BEFORE : 테이블에서 DML 실행되기 전에 트리거가 동작
--AFTER : 테이블에서 DML 실행후에 트리거 동작
Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} triggering_event [OF column1, . . .] ON table_name
[FOR EACH ROW [WHEN trigger_condition]
trigger_body;
trigger_name TRIGGER 의 식별자
BEFORE | AFTER DML 문장이 실행되기 전에 TRIGGER 를 실행할 것인지 실행된
후에 TRIGGER 를 실행할 것인지를 정의
triggering_event
TRIGGER 를 실행하는 DML(INSERT,UPDATE,DELETE)문을 기술한다.
OF column TRIGGER 가 실행되는 테이블에서 COLUMN 명을 기술한다.
table_name TRIGGER 가 실행되는 테이블 이름
FOR EACH ROW 이 옵션을 사용하면
행 레벨 트리거가 되어 triggering 문장
에 의해 영향받은 행에 대해 각각 한번씩 실행하고 사용하지
않으면 문장 레벨 트리거가 되어 DML 문장 당 한번만 실행된다.
TRIGGER 에서 OLD 와 NEW
행 레벨 TRIGGER 에서만 사용할 수 있는 예약어로 트리거 내에서 현재 처리되고 있는 행
을 액세스할 수 있다. 즉 두개의 의사 레코드를 통하여 이 작업을 수행할 수 있다. :OLD
는 INSERT 문에 의해 정의되지 않고 :NEW 는 DELETE 에 대해 정의되지 않는다. 그러나
UPDATE 는 :OLD 와 :NEW 를 모두 정의한다. 아래의 표는 OLD 와 NEW 값을 정의한 표이다.
문장 :OLD :NEW
INSERT 모든 필드는 NULL 로 정의 문장이 완전할 때 삽입된 새로운 값
UPDATE 갱신하기 전의 원래 값 문장이 완전할 때 갱신된 새로운 값
DELETE 행이 삭제되기 전의 원래 값 모든 필드는 NULL 이다.
DROP TRIGGER 명령어로 트리거를 삭제할 수 있고 TRIGGER 를 잠시 disable 할 수 있다.
DROP TRIGGER trigger_name;
ALTER TRIGGER trigger_name {DISABLE | ENABLE};
TRIGGER 와 DATA DICTIONARY
TRIGGER 가 생성될 때 소스 코드는 데이터 사전 VIEW 인 user_triggers 에 저장된다. 이
VIEW 는 TRIGGER_BODY, WHERE 절, 트리거링 테이블, TRIGGER 타입을 포함 한다.
*/
create table tri_emp
as
select empno , ename from emp where 1=2;
select * from tri_emp;
create or replace trigger tri_01
after insert on tri_emp --tri_emp 테이블에 insert 가 되고 나서
BEGIN -- 자동 동작할 내용
DBMS_OUTPUT.PUT_LINE('신입사원 입사');
END;
insert into tri_emp(empno,ename) values(100,'홍길동');
select * from tri_emp;
create or replace trigger tri_02
after update on tri_emp
BEGIN
DBMS_OUTPUT.PUT_LINE('신입사원 수정');
END;
select * from user_jobs;
--테이블에 trigger 정보
select * from user_triggers where table_name='TRI_EMP';
insert into tri_emp values(100,'김유신');
update tri_emp
set ename='아하'
where empno=100;
--delete 트리거 : tri_emp
--사원테이블 삭제 (화면 출력)
create or replace trigger tri_03
after delete on tri_emp
BEGIN
DBMS_OUTPUT.PUT_LINE('신입사원 삭제');
END;
select * from user_triggers where table_name='TRI_EMP';
insert into tri_emp values(200,'홍길동');
update tri_emp set ename='변경' where empno= 200;
delete from tri_emp where empno=200;
-------------------------------------------
----------------------------------
--예제1) 테이블에 INSERT, UPDATE, DELETE 를 할 때 user, 구분(I,U,D), sysdate 를 기록하는
--테이블(emp_audit)에 내용을 저장한다.
--FOR EACH ROW 이 옵션을 사용하면
--행 레벨 트리거가 되어 triggering 문장
--에 의해 영향받은 행에 대해 각각 한번씩 실행하고 사용하지
--않으면 문장 레벨 트리거가 되어 DML 문장 당 한번만 실행된다.
drop sequence emp_audit_tr;
drop table emp_audit;
create sequence emp_audit_tr
increment by 1
start with 1
maxvalue 999999
minvalue 1
nocycle
nocache;
create table emp_audit(
e_id number(6) constraint emp_audit_pk primary key,
e_name varchar2(30),
e_gubun varchar2(10),
e_date date);
drop table emp2;
create table emp2
as
select * from emp;
desc emp2;
select * from emp2;
create or replace trigger emp_audit_tr
after insert or update or delete on emp2
for each row -- 5행 추가 되면 5번실행하도록함 ,원래는 한번만함 몇번수행이든
begin
if inserting then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'inserting', sysdate);
elsif updating then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'updating', sysdate);
elsif deleting then --어떤 이벤트인지 구분
insert into emp_audit
values(emp_audit_tr.nextval, user, 'deleting', sysdate);
end if;
end;
-- for each row 선언 안했을 때 (명령어 한 번에 대하여 한 건으로 기록된다.)
select * from emp2;
rollback;
update emp2
set deptno = 200
where deptno = 10;
select * from emp_audit;
delete from emp2 where deptno = 20;
select * from emp_audit;
rollback;
-- for each row 생략하고 선언 했을 때(명령어 한 번에 변경된 행만큼 기록된다.)
create or replace trigger emp_audit_tr
after insert or update or delete on emp2
begin
if inserting then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'inserting', sysdate);
elsif updating then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'updating', sysdate);
elsif deleting then
insert into emp_audit
values(emp_audit_tr.nextval, user, 'deleting', sysdate);
end if;
end;
select * from emp2;
update emp2 set deptno = 20 where deptno = 10;
select * from emp_audit;
rollback;
--------------------------------------------------------------------
--INSERT, UPDATE, DELETE로 변경되는 내용에 대하여 전/후 데이터를 기록한다.
drop table emp_audit;
create table emp_audit (
id number(6) constraint emp_audit_pk primary key,
name varchar2(30),
gubun varchar2(10),
wdate date,
etc1 varchar2(20), -- old
etc2 varchar2(20) -- new
);
--update
--기존의 데이터를 삭제하고 새 데이터를 insert하는 작업이다.
--이런걸 하려고 insert가상테이블과 delete가상테이블이 존재한다
create or replace trigger emp_audit_tr
after insert or update or delete on emp2
for each row
begin
if inserting then
insert into emp_audit --insert는 new 만 사용하면된다.
values(emp_audit_tr.nextval, user, 'inserting', sysdate, :old.deptno, :new.deptno);
--old 가상테이블(기존데이터)--new 가상테이블(새로운 데이터)
elsif updating then--update는 old, new를 둘다사용
insert into emp_audit
values(emp_audit_tr.nextval, user, 'updating', sysdate, :old.deptno, :new.deptno);
elsif deleting then -- old 만 사용한다.
insert into emp_audit
values(emp_audit_tr.nextval, user, 'deleting', sysdate, :old.deptno, :new.deptno);
end if;
end;
select* from emp2;
--insert
insert into emp2(empno,ename,deptno) values (9999,'홍길동',100);
--deptno만 삽입했으니 이것만 들어감
select * from emp_audit;
--update
update emp2 set deptno=200
where empno=9999;
select * from emp_audit;
--delete
delete from emp2 where empno=9999;
select * from emp_audit;
--------------------------------------------------------------------------------
--트리거의 활용
create table tri_order
(
no number,
ord_code varchar2(10),
ord_date date
);
--before 트리거의 동작시점이 실제 tri_order 테이블 insert 되기 전에
--트리거 먼저 동작 그 이후 insert 작업
create or replace trigger trigger_order
before insert on tri_order
BEGIN
IF(to_char(sysdate,'HH24:MM') not between '12:00' and '18:00') THEN
RAISE_APPLICATION_ERROR(-20002, '허용시간 오류 쉬세요');
END IF;
END;
insert into tri_order values(1,'notebook',sysdate);
select * from tri_order;
commit;
rollback;
--트리거 삭제
drop trigger trigger_order;
--POINT
--PL_SQL 두개의 가상데이터(테이블) 제공
--:OLD > 트리거가 처리한 레코드의 원래 값을 저장 (ms-sql (deleted)
--:NEW > 새값을 포함 (ms-sql (inserted)
create or replace trigger tri_order2
before insert on tri_order
for each row
BEGIN
IF(:NEW.ord_code) not in('desktop') THEN
RAISE_APPLICATION_ERROR(-20002, '제품코드 오류');
END IF;
END;
select * from tri_order;
--오류 (desktop)
insert into tri_order values(200,'notebook',sysdate);
insert into tri_order values(200,'desktop',sysdate);
select * from tri_order;
commit;
--------------------------------------------------------------------------------
--입고 , 재고
create table t_01 --입고
(
no number,
pname varchar2(20)
);
create table t_02 --재고
(
no number,
pname varchar2(20)
);
--입고 데이터 들어오면 같은 데이터를 재고 입력
create or replace trigger insert_t_01
after insert on t_01
for each row
BEGIN
insert into t_02(no, pname)
values(:NEW.no ,:NEW.pname);
END;
--입고
insert into t_01 values(1,'notebook');
select * from t_01;
select * from t_02;
-- 입고 제품이 변경 (재고 변경)
create or replace trigger update_t_01
after update on t_01
for each row
BEGIN
update t_02
set pname = :NEW.pname
where no = :OLD.no;
END;
update t_01
set pname = 'notebook2'
where no = 1;
select * from t_01;
select * from t_02;
--delete 트리거 만들어 보세요
--입고 데이터 delete from t_01 where no =1 삭제 되면 재고 삭제
create or replace trigger delete_tri_01
after delete on t_01
for each row
BEGIN
delete from t_02
where no=:OLD.no;
END;
delete from t_01 where no=1;
select* from t_01;
select* from t_02;
commit;
--------------------------------------------------------------------------------
-- 고생했다 토닥 토닥 ---------------------------------------------------------------------