2023. 4. 11. 20:03ㆍoracle
orcle
1. 트랜잭션 (일련의 작업)
- 일련의 작업 단위를 처리하기 위한 거를 트랜잭션
- 일련의 작업에서 하나의 작업이라도 실패한다면 모든 작업을 취소시킨다
(ROLLBACK 한다고 한다)
- 일련의 작업이 정상적으로 끝나면 COMMIT 한다
- 오라클의 트랜잭션 제어 명령어
COMMIT:모든 미결정 데이타를 영구적으로 변경함으로써
현재 TRANSATION을 종료
ROLLBACK [TO SAVEPOINT name]:모든 미결정 데이터 변경을 원래대로 돌림으로써
현재의 TRANSATION종료
SAVEPOINT :TRANSATION내의 SAVEPOINT표시/ANSI 표준 SQL이 아님.
- 자동 COMMIT일 얼어나는 경우
1.DDL/DCL문장 완료시(DDL=>create,alter,drop,truncate,rename),DCL=>grant,revoke)
2.SQL*PLUS 정상 종료 시
- 자동 ROLLBACK이 일어나는 경우
1.SQL*PLUS 비정상 종료시 혹은 시스템 실패 시
※cmd에서 했지만 sql developer에서 안 보이는 이유 임시테이블에 저장되어 있어서 보이게 하려면 commit을 하면 됨.
- SAVEPOINT 예]
SQL> UPDATE emp SET depno=30 WHERE empno=7788;
SQL> SAVEPOINT point1
SQL> UPDATE emp SET job='MANAGER';
SQL>UPDATE emp SET SAL=500;
SQL> ROLLBACK TO SAVEPOINT point1;
SQL> EXIT(정상종료라 부름 이걸 하면 자동 커밋)
2. 주요 내장 함수
2-1). 문자열 관련
-NVL(칼럼명, NULL인 경우 대체할 값)
-NVL2(칼럼명, "대체값 1", "대체값 2")
2-2). LOWER('문자열'):영문자를 소문자로 변환
SELECT LOWER('ORACLE') FROM DUAL +> oracle
2-3). UPPER('문자열'):영문자를 대문자로 변환
SELECT UPPER('oracle') FROM DUAL +> ORACLE
2-4). INITCAP('문자열'): 첫 영문자를 대문자로 변환
SELECT INITCAP('oracle') FROM DUAL +> Oracle
2-5). CONCAT('문자열', '문자열'):문자열 연결 또는 ||
SELECT CONCAT('ORACLE', 'JAVA') FROM DUAL +> ORACLEJAVA
2-6). LENGTH():문자열 길이
SELECT LENGTH('오라클') FROM DUAL; +>3
2-7). lengthb():문자열 길이를 바이트로(한글이 3바이트 차지)
SELECT LENGTHB('오라클') FROM DUAL; +>9
2-8). LPAD('문자열', 전체 자릿수, '채울 문자열'):좌측을 지정한 값으로 채운다
SELECT LPAD('HELLO',10, 'X') FROM DUAL+> XXXXXHELLO
혹은
SELECT LPAD('9',2, '0') FROM DUAL +>09
2-9). RPAD('문자열', 전체 자릿수, '채울 문자열'):우측을 지정한 값으로 채운다
SELECT LPAD('HELLO',10, 'X') FROM DUAL+> HELLOXXXXX
혹은
SELECT LPAD('9',2, '0') FROM DUAL +>90
2-10). INSTR('문자열', '찾을 문자열'): 찾은 문자열의 인덱스 반환.
인덱스는 1부터 시작
SELECT INSTR('ABCDEFG', 'DE') FROM DUAL +>4
(1) SELECT * FROM DEPT WHERE instr(LOWER(DNAME), 'e')!=0
는
(2) SELECT * FROM DEPT WHERE LOWER(DNAME) like '%e%'와 결과가
같으나 쿼리속도면에서 (1)이 훨씬 성능이 우수하다.
2-11). SUBSTR('문자열', 시작인덱스, 길이):문자열에서 시작인덱스부터 길이만큼 가져옴, 인덱스는 1부터 시작
SELECT SUBSTR('123456789',3,3) FROM DUAL;+>345
2-12). REPLACE('문자열', '바꿀 문자열', '바뀔 문자열'): 특정 문자열을 다른 문자열로 대체
SELECT REPLACE('HELLO WORLD', 'HELLO', 'JAVA') FROM DUAL +> JAVA WORLD
2-13). TO_CHAR():
TO_CHAR(숫자 혹은 날짜)
TO_CHAR(숫자 혹은 날짜, 숫자형식 포맷 문자열 혹은 날짜형식 포맷 문자열)
-숫자형을 문자형으로 변환
SELECT TO_CHAR(123) || TO_CHAR(456) FROM DUAL +>123456
혹은
SELECT 123 || 456 FROM DUAL
SELECT TO_CHAR(123, '0999') FROM DUAL +>0123--0은 숫자가 아니면 0
SELECT TO_CHAR(123, '9999') FROM DUAL +> 123--9는 숫자가 아니면 빈공백
SELECT TO_CHAR(1234, '$999,999.99') FROM DUAL +>$1,234.00
SELECT TO_CHAR(1234, '$999,999.00') FROM DUAL +>$1,234.00
SELECT TO_CHAR(1234, 'L999,999') FROM DUAL +> \1,234
SELECT TO_CHAR(1234, 'l999,999') FROM DUAL +> \1,234
※TO_CHAR(숫자, '숫자포맷문자열')
9는 값이 있으면 표시, 없으면 표시 안 함
0은 값이 있으면 표시, 없으면 0으로 표시
단, 소수점은 9든 0이든 값이 없으면 모두 0으로 표시됨
또한 소수점은 실제값의 자릿수가 많으면 나머지는 짤림.
단, 정수인 경우는 실제값의 자리수가 많으면 값이 #으로 표시됨
2-14). 날짜형을 문자 형으로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL
※'yyyy"월"mm"년"dd"일" :한글로 연월일 표시 시 포맷형식에 ""로 한글을 감싼다.
예] SELECT TO_CHAR(SYSDATE, 'YYYY"년"MM"월"DD"일" HH"시"MI"분"SS"초"') FROM DUAL
2-15). 지정한 날짜의 특정 부분을 나타내는 문자열을 반환
SELECT TO_CHAR(TO_DATE('2012/10/1'), 'YYYY') FROM DUAL+>2012
SELECT TO_CHAR(TO_DATE('2012/10/1'), 'MM') FROM DUAL+>10
SELECT TO_CHAR(TO_DATE('2012/10/1'), 'HH') FROM DUAL+>12
SELECT TO_CHAR(SYSDATE, 'HH') FROM DUAL+>1~12시 형태의 시간 반환
18 시인경우 06
오전 6시인 경우 06
SELECT TO_CHAR(SYSDATE, 'HH AM') FROM DUAL+> 06 오전 혹은 오후
SELECT TO_CHAR(SYSDATE,'HH PM') FROM DUAL+> 06 오전 혹은 오후
SELECT TO_CHAR(SYSDATE, 'AM HH') FROM DUAL+> 오전 혹은 오후 06
SELECT TO_CHAR(SYSDATE, 'PM HH') FROM DUAL+> 오전 혹은 오후 06
SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL+>0~23시 형태의 시간 반환
SELECT TO_CHAR(SYSDATE, 'day') FROM DUAL+> 목요일
SELECT TO_CHAR(SYSDATE, 'dy') FROM DUAL+> 목
SELECT TO_CHAR(TO_DATE('2012/10/1'), 'DD') FROM DUAL+>01
SELECT TO_CHAR(TO_DATE('2012/10/1'), 'D') FROM DUAL+>2(요일 반환)
1:일요일
2:월
3:화
4:수
5:목
6:금
7:토
SELECT TO_CHAR(TO_DATE('2013/1/1'), 'DDD') FROM DUAL+>001
해당 연도 1월 1일부터
몇일째인지 반환
※ '2012-10-1'도 날짜형식의 문자열로 인식하지만
'2012년 10월 1일'은 오라클이 날짜형식의 문자열로 인식 못함.
2-16). TO_NUMBER():문자형을 숫자형으로 변환
SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL +>579
혹은
SELECT '123' + '456' FROM DUAL
2-17). TRIM():양쪽 공백 제거
2-18). 날짜 관련
*ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH:MI:SS'
*REGEDIT->HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY~에
NLS_DATE_FORMAT ='YYYY-MM-DD'추가
*UNIX인 경우. bashrc 혹은. bash_profile에 export=NLS_DATE_FORMAT ='YYYY-MM-DD'추가
-SYSDATE:현재 날짜 가져오기
SELECT SYSDATE FROM DUAL +> YY/MM/DD
- + 혹은 - :날짜 더하기 빼기
SELECT SYSDATE + 1 FROM DUAL +>1일 더한 날 출력
SELECT SYSDATE -1 FROM DUAL +>1일 뺀날 출력
2-19). TO_DATE():문자형을 날짜형으로 변환
yy:년도 2자리만
yyyy:년도 4자리
mm:01~12 형태의 월
d: 요일 반환(일요일은 1, 월요일 2)
dd:1~31 형태의 일 표시
ddd:해당 연도의 1월 1일부터 현재까지의 일수
HH:1~12시 형태로 표시
HH12:1~12시 형태로 표시
HH24:0~23시
MI:0~59분
SS:0~59초
SELECT TO_DATE('2000/10/20') FROM DUAL;+>00/10/20
※문자열타입으로 저장된 날짜형식을 DATE타입으로 변환 후
VARCHAR2 2017-12-19
TO_CHAR(DATE타입, '날짜포맷형식')을 이용해
원하는 날짜 형태로 출력하고자 할 때
단, 문자열타입의 날짜형식은 연월일형태여야 한다
(시분초 포함 시 에러 즉 날짜형식으로 인식 안됨)
만약 VARCHAR2(20)의 타입으로 2013-03-14 17:05;56로 저장되었다면
select to_char(to_date(substr(postdate,1,10)), 'yyyy-mm-dd') postdate from board
2-20). MONTHS_BETWEEN(DATE타입, DATE타입):개월 수 구하기
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2012/01/01')) FROM DUAL+>2.48.....
-ADD_MONTHS(DATE타입, 숫자):개월 수 더하기
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL+>
2-21). 수학 관련
-ROUND() : 반올림
-FLOOR(): 소수자리 버림
-CEIL(): 올림
-MOD: 나머지
SELECT MOD(5,2) FROM DUAL+>1
-POWER(2,3):지수곱
-SQRT(10):제곱근
2-22). -DECODE() 함수는 첫 번째 매개변수의 값에 따라 결과를 표시하는 함수
(switch와 같다)
DECODE(표현식, 값 1, 결괏값 1, 값 2, 결괏값 2,.. 값 N, 결괏값 N, 기본값)
표현식이 값 1일 때 결괏값 1이 반환됨 값 2일 때는 결괏값 2
표현식이 아무 값도 해당되지 않을 때 기본값 반환
예]
SELECT DECODE( 10/2 ,1, '값이 1',2, '값이 2',3, '값이 3', '그 외 값') FROM DUAL;
select ename,
decode(job, 'MANAGER', '매니저', 'SALESMAN', '영업맨', '기타')
FROM EMP
DECODE()는 값 1 = 결괏값 1 즉 = 연산만 가능
2-23). ※CASE WHEN 절은 다양한 조건식 가능
1] 다양한 조건을 줄 때(if ~else if ~else if else와 같다)
CASE WHEN 조건 1 THEN 결괏값 1 <=칼럼명이 없을 때 실수형 데이터일 때 사용
WHEN 조건 2 THEN 결괏값 2
..................
WHEN 조건 N THEN 결괏값 N
ELSE 그 외 값
END [칼럼별칭]
예]
select ename,
CASE JOB WHEN 'MANAGER' THEN '매니저'<=case 칼럼명 when은 decode와 같음
WHEN 'SALESMAN' THEN '영업맨'
ELSE '기타'
END AS JOB, DEPTNO,
-- 범위를 조건으로 할 때는 CASE와 WHEN사이에
표현식 생략.(자바의 IF문)
CASE WHEN SAL >= 3000 THEN '고액 연봉자'
WHEN SAL >= 2000 THEN '중간 연봉자'
ELSE '하위 연봉자'
END SAL
FROM EMP
2] DECODE() 함수처럼 쓰려면(switch와 같다)
--범위가 아닌 특정 값으로 분기할 때는 (자바의 SWITCH문)
CASE와 WHEN 사이에 표현식 생략 불가
CASE 표현식 WHEN 값 1 THEN 결괏값 1
WHEN 값 2 THEN 결괏값 2
..................
WHEN 값 N THEN 결괏값 N
ELSE 그 외 값
END [칼럼별칭]
예]
select ename,
CASE JOB WHEN 'MANAGER' THEN '매니저'
WHEN 'SALESMAN' THEN '영업맨'
ELSE '기타'
END AS JOB
FROM
SELECT SAL,
CASE FLOOR(SAL/1000) WHEN 3 THEN '고액연봉'
WHEN 4 THEN '고액연봉'
WHEN 5 THEN '고액연봉'
WHEN 2 THEN '중간연봉'
ELSE '저액연봉'
END AS 직무,
JOB
FROM emp ORDER BY sal;
SELECT ENAME,
CASE
WHEN MGR IS NULL
THEN 'CEO'
WHEN EMPNO IN (SELECT E1.EMPNO FROM EMP E1 JOIN EMP E2 ON E1.EMPNO=E2.MGR )
THEN 'MANAGER'
WHEN EMPNO NOT IN (SELECT E1.EMPNO FROM EMP E1 JOIN EMP E2 ON E1.EMPNO=E2.MGR )
THEN 'WORKER'
END
유형
FROM EMP
문제) hr 계정
3. CURSOR
- SEELCT 문장에 의해 여러 행이 RETURN 되는 경우 각 행에 접근하기 위한 것
CURSOR 선언]
CURSOR 커서명 IS
SELECT문장 ------------------DECLARE부에서 한다
(INTO절이 없는 SELECT문)
OPEN CURSOR]
-질의를 수행하라는 의미 즉
OPEN시에 CURSOR 선언 시의 SELECT문장이 실행되어
결과 셋을 얻게 된다 CURSOR는 그 결과세의 첫 번째 행에
바로 위에 위치하게 된다.
OPEN 커서명'
FETCH ~ INTO ~]
-결과 셋에서 하나의 행을 읽어 들이는 작업,
결과 셋에서 인출(FETCH) 후에
CURSOR는 다음 행으로 이동
FETCH 커서명 INTO {varaiable1 [, variable2,....]};
CURSOR 닫기]
-결과 셋의 자원을 반납. SELECT 문장이 다 처리된 후 CURSOR를 CLOSE
CLOSE 커서명;
CURSOR의 속성]
커서명%FOUND :가장 최근의 인출(FETCH)이 행을 RETURN 하면 TRUE 아니면 FALSE;
커서명%ROWCOUNT :지금까지 RETURN 된 행의 수
소스 예]
-----부서 번호를 입력받아 출력하는 예제
SET SERVEROUT ON
ACCEPT p_deptno PROMPT '부사번호를 입력하세요 : '
DECLARE
v_deptno emp.deptno%TYPE:=&p_deptno;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
--커서 선언
CURSOR emp_cursor IS
SELECT ename, sal FROM emp WHERE deptno=v_deptno;
BEGIN
--커서 오픈
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE(' 이름 연봉 ');
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
LOOP
--FETCH 하기
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%FOUND;
DBMS_OUTPUT.PUT_LINE(RPAD(v_ename,12) || LPAD(TO_CHAR(v_sal, '$99,999,990.00'),16));
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
END LOOP;
--커서 닫기
CLOSE emp_cursor;
END;
/
SET SERVEROUT OFF
4. SUBPROGRAM
- PL/SQL에는 (스토어드) 프로시저 와 함수라는 두 가지 유형의 SUBPROGRAM이 있다
- SELECT구문뿐만 아니라 기타 DML문 등을 이용하거나
혹은 프로그래밍적인 요소등을 사용하여
처리하기 복잡한 여러 가지 데이터베이스 작업등을 처리할 수 있도록 만들어진
데이터베이스 객체이다
4-1) Function
- 사용자가 PL/SQL구문을 사용하여 직접 오라클에서 제공하는 내장함수와
같은 기능을 정의한 것
- 함수는 IN 파라미터만 사용할 수 있으며,
반드시 반환될 값의 데이터 타입을
RETURN문에 선언해야 하며 단일값만 반환된다.
-함수 구문
CREATE [OR REPLACE] FUNCTION 함수명
[(
매개변수1 IN 자료형, --IN 생략 가능
매개변수2 IN 자료형 ---자료형 정의 시 자릿수 지정 안함 <=지정하면 에러
)]
RETURN 자료형-- 자리수 지정 안 함 <=지정하면 에러
IS
[변수 선언]
BEGIN--<=함수 시작
함수 내용
RETURN(값);
END;
함수 예]
- FUNCTION정의
CREATE OR REPLACE FUNCTION GETSUM(A NUMBER, B IN NUMBER)
RETURN NUMBER
IS
HAP NUMBER; --<=hap number(10);자릿수 여긴 설정가능
BEGIN
HAP:=0;
FOR i IN A .. B LOOP
HAP:=HAP+i;
END LOOP;
RETURN HAP;
END;
/
-FUNCTION 호출
방법 1)
SQL> SELECT GETSUM(1,100) FROM DUAL
방법 2)
SQL> VAR HAP CHAR(2);
SQL> EXCUTE :HAP := GETSUM(1,100);--<=함수를 실행하라 그리고 그걸 hap에 넣어라
//excute는 실행권한을 가짐(함수나 프로시저를 실행할 때 사용)
SQL> PRINT HAP;
함수 예]
CREATE OR REPLACE FUNCTION GETGENDER(JUMIN VARCHAR2)
RETURN NCHAR
IS ----함수란(;는 붙이면 안 됨)
RTVAL VARCHAR2(4);
BEGIN
IF TO_NUMBER(SUBSTR(TRIM(JUMIN),7,1)) = 1 THEN
RTVAL:='남자';
ELSE
RTVAL:='여자';
END IF;
RETURN RTVAL;
END;
/
문제)
hrr계정에서 scott계정에서 만든 함수 to_asterisk사용하기
4-2). 저장 프로시저([Stored] Procedure)
- 프로시저는 RETURN문이 없다 OUT 매개변수로 값을 RETURN 한다.
- 저장 프러시저(STORED PROCEDURE)의 장점
(1) 매우 좋은 성능
(2) 보안성을 높일 수 있음.
(3) 다양한 처리가 가능
(4) 네트워크의 부하를 줄일 수 있음.
-프로시저 구문
:RETURN 문이 없다.
CREATE [OR REPLACE] PROCEDURE 프로시저명
[(
매개변수 IN 자료형,//크기 지정 안 함
매개변수 OUT 자료형,//출력용
매개변수 IN OUT 자료형//입 출력용 EXEC시 숫자 전달 불가능, 역시 변수 전달
)]
IS
[변수선언]
BEGIN
........
END;
-실행
EXECUTE 프로시저명[(인수 1, 인수 2,..)]
※ 매개변수의 자료형 지정 시 해당 테이블의 칼럼과 같은
자료형으로 지정하고 싶을 때 테이블명. 컬러명% TYPE
이라고 지정한다
예] 매개변수 IN 테이블명.컬럼명%TYPE
소스 예]
- 사원번호 와 연봉을 입력받아 연봉을 수정하고 수정된 행의 개수를 받아 오기
SET SERVEROUT ON
CREATE OR REPLACE PROCEDURE
emp_sal_procedure(
p_empno [IN] emp.empno%TYPE,
p_sal IN emp.sal%TYPE,
count OUT NUMBER)
IS
BEGIN
UPDATE emp
SET sal =p_sal
WHERE empno =p_empno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_empno) || '는 없는 사원번호입니다');
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || '명의 자료가 수정되었습니다.');
count:=SQL%ROWCOUNT;
END IF;
COMMIT; -- 커밋해야 실제 반영됨.
END;
/
SET SERVEROUT OFF
-프로시저 실행
SQL> VAR count NUMBER
SQL> EXECUTE emp_sal_procedure(10,1000,:count);
SQL> PRINT count
----회원일치여부 프로시저
CREATE OR REPLACE PROCEDURE SP_CHECK_MEMBER(IEMPNO IN EMP.EMPNO%TYPE,IENAME IN EMP.ENAME%TYPE,FLAG OUT NUMBER)
IS
CNT NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO CNT
FROM EMP
WHERE EMPNO = IEMPNO;
IF CNT = 0 THEN
FLAG:=0; -- 아이디가 일치하지 않는 경우
ELSE
BEGIN
CNT:=0;
SELECT COUNT(*) INTO CNT
FROM EMP
WHERE EMPNO=IEMPNO AND ENAME=UPPER(IENAME);
IF CNT = 1 THEN
FLAG:=2; --아이디와 비밀번호가 일치하는 경우
ELSE
FLAG:=1; --아이디는 일치하나 비밀번호가 일치하지 않은 경우
END IF;
END;
END IF;
END;
/
4-3). 트리거
- 자동으로 실행되는 프로시저의 한 종류. 직접(exec) 실행불가
- 하나의 테이블에 최대 3개까지 트리거 적용가능
단, 트리거 많을수록 성능저하 초래 가능성 있다.
- 트리거 몸체(PL/SQL블락) 안에는 COMMIT;ROLLBACK불가
- :NEW(변경 후) ,:OLD(변경 전) 임시테이블은 행단 위 트리거에서만 사용가능
트리거 몸체에서 :NEW 및 :OLD의 의미
타깃 테이블에
INSERT 실행 시 :OLD는 NULL이고 :NEW은 새로운 입력값이다
UPDATE 실행시 :OLD는 업데이트 전의 값이고 :NEW은 새로운 변경 값이다
DELETE 실행시 :OLD는 삭제 전의 값이고 :NEW은 NULL이다
즉 :OLD는 DELETE 혹은 UPDATE 하기 전의 데이터(변경 전)이고 :NEW는 INSERT 혹은 UPDATE 하려는 데이터(변경 후)이다
- 트리거 종류
1. 트리거몸체(DECLARE~ BEGIN END;절)의 실행 횟수에 따른 분류
문장단위 트리거- 한 번만 실행
행단 위 트리거 - 행단 위로 변경된 횟수만큼 실행됨
2. 타이밍에 따른 분류
BEFORE트리거- 이벤트(I/D/U)발생전 트리거몸체 실행됨
예] 판매와 상품(재고) 관계, 보안
AFTRE트리거 - 이벤트(I/D/U)발생후 트리거몸체 실행됨
예] 입고 와 상품(재고) 관계
-트리거 생성
CREATE TRIGGER 트리거명
타이밍 [BEFORE|AFTER] 이벤트 [INSERT [OR] | UPDATE [OR] DELETE]
ON 트리거를 걸 테이블명
[FOR EACH ROW] --생략 시 문장단위 트리거
[WHEN 트리거 조건]
DECALRE
변수 선언
BEGIN
END;
/
-트리거 삭제
DROP TRIGGER 트리거명
-특정 트리거 활성화/비활성화
ALTER TRIGGER 트리거명 [ENABLE/DISABLE]
- 테이블에 건 모든 트리거 활성화 비활성화
ALTER TABLE 테이블명 ENABLE/DISABLE ALL TRIGGERS
※INSERTING /DELETING/UPDATING 키워드
PL/SQL블락 안에서 사용하는 상수
예] INSERT이벤트가 발생하면 INSERTING 이 TRUE
※BEFORE트리거 걸 때 주로 사용하는 함수
RAISE_APPLICATION_ERROR(에러번호, '에러메시지');
에러번호는 -20000 ~-20999 사이의 임의의 숫자 지정
--트리거를 걸 테이블
CREATE TABLE TRIGER_TBL(
NO NUMBER PRIMARY KEY,
MESSAGE NVARCHAR2(10)
);
--위 테이블에 행이 입력/수정/삭제되면 자동으로 아래 테이블에 어떤 이벤트(I/D/U)가 발생했는지 입력하자
CREATE TABLE TARGET_TBL(
EVENT NVARCHAR2(10),
POSTDATE DATE DEFAULT SYSDATE
);
create trigger mytrigger
after insert or update or delete
on TRIGER_TBL
for each row
declare
begin
if inserting then
insert into target_tbl values('insert',sysdate);
elsif deleting then
insert into target_tbl values('delete',sysdate);
elsif updating then
insert into target_tbl values('update',sysdate);
end if;
end;
/
select * from triger_tbl;
select * from TARGET_TBL;
insert into triger_tbl values(1,'입력이밴트1');
insert into triger_tbl values(2,'입력이밴트2');
insert into triger_tbl values(3,'입력이밴트3');
update triger_tbl set message='inputevent' where no=1;
delete triger_tbl;
drop trigger mytrigger2;
create trigger mytrigger2
before insert
on triger_tbl
for each row
declare
begin
if to_char(sysdate,'dy')='수' or to_char(sysdate,'HH24')>=19 then
raise_application_before(-20001,'화요일 혹은 19시 이후에는 입력불가');
else
insert into target_tbl values('입력성공',sysdate);
end if;
end;
/
insert into triger_tbl values(4,'입력이밴트4');--[x]화요일 혹은 19시 이후에는 입력불가
--상품
CREATE TABLE PRODUCT(
P_CODE CHAR(4) PRIMARY KEY,
P_NAME NVARCHAR2(10) NOT NULL,
P_PRICE NUMBER,
P_QTY NUMBER DEFAULT 0);--재고수량
--입고
CREATE TABLE INP(
I_NO NUMBER PRIMARY KEY,
P_CODE CHAR(4) REFERENCES PRODUCT(P_CODE),
I_DATE DATE DEFAULT SYSDATE,
I_QTY NUMBER,
I_PRICE NUMBER
);
--판매
CREATE TABLE SALES(
S_NO NUMBER PRIMARY KEY,
P_CODE CHAR(4) REFERENCES PRODUCT(P_CODE),--판매일련번호
S_DATE DATE DEFAULT SYSDATE,
S_QTY NUMBER,--판매수량
S_PRICE NUMBER
);
SELECT * FROM PRODUCT;
UPDATE PRODUCT SET P_QTY=10 WHERE P_CODE='B001';
SELECT * FROM INP;
SELECT * FROM SALES;
INSERT INTO PRODUCT(P_CODE,P_NAME,P_PRICE) VALUES('B001','자바',2500);
INSERT INTO PRODUCT(P_CODE,P_NAME,P_PRICE) VALUES('B002','스프링',3000);
--입고테이블에 insert 트리거 걸자
--즉 입고되면 삼품테이블의 재고수량 자동증가
drop trigger ipgo_trigger;
create trigger ipgo_trigger
after insert
on inp
for each row
declare
begin
update product set p_qty=p_qty +:new.i_qty
where P_CODE=:new.P_CODE;
end;
/
-- 입고 테이블에 입력하자
INSERT INTO inp(i_no,P_CODE,i_qty,i_price) values(1,'B001',10,2000);
INSERT INTO inp(i_no,P_CODE,i_qty,i_price) values(2,'B001',15,1800);
--입고테이블에 update트리거를 걸자
--즉 입고량이 수정되면 상품 테이블의 재고수량 자동으로 수정
--입고 테이블 입고 수량 수정
update inp set i_qty = 1 where i_no=1;
CREATE TRIGGER IPGO_TRIGGER3
BEFORE INSERT
ON SALES
FOR EACH ROW
DECLARE
qty NUMBER;--상품의 재고수량 저장
BEGIN
--재고수량 파악
SELECT P_QTY INTO QTY FROM PRODUCT WHERE P_CODE = :NEW.P_CODE;
--재고수량과 판매수량비교
IF qty < :NEW.s_qty THEN
RAISE_APPLICATION_ERROR(-20002,'재고가 없어요. 수량이 ' || QTY || '밖에 남자 않았어요');
ELSE
--PRODUCT테이블의 재고 수량 수정을 판매수량만큼 뺀다
update product SET P_QTY = P_QTY - :NEW.S_QTY WHERE P_CODE =:NEW.P_CODE;
END IF;
END;
/
INSERT INTO SALES(S_NO,P_CODE,S_QTY,S_PRICE) VALUES(1,'B001',9,10000);
5. 백업과 복원
:백업 및 복원 실행 파일 위치 : [오라클설치된 디렉토리]\product\10.2.0\db_1\BIN
백업:EXP.EXE
복원:IMP.EXE
-백업(Export) :데이타와 구조를 바이너리 파일로 저장
dos>exp userid=아이디/비밀번호@전역데이타베이스명 file=저장경로
예]
:system계정으로 전체 백업
dos>exp userid=system/비밀번호@전역데이타베이스명 full=y file=c:\dump.dmp
:system 계정으로 scott 계정에 있는 DB백업
dos>exp userid=system/비밀번호@전역데이타베이스명 owner=scott file=c:\dump.dmp
:scott계정으로 자신의 모든 데이타 백업
dos>exp userid=scott/비밀번호@전역데이타베이스명 file=c:\dump.dmp
:scott계정으로 emp테이블만 백업
dos>exp userid=scott/비밀번호@전역데이타베이스명 file=c:\dump.dmp tables=emp
*여러개 테이블을 동시에 받으려면 tables=(테이블1,테이블2,...)
*백업 파일의 확장자는 보통 .dmp 혹은 .dat .bak으로 한다 한다.
-복원(Import)
imp 아이디/비밀번호@전역데이타베이스명 file=백업경로
예]
:system계정으로 전체 복원
dos>imp system/비밀번호@전역데이타베이스명 file=c:\dump.dmp
:system 계정으로 scott 계정에 있는 DB복원
dos>imp system/비밀번호@전역데이타베이스명 fromuser=scott touser=scott file=c:\dump.dmp
:scott계정으로 자신의 모든 데이타 복원
dos>imp scott/비밀번호@전역데이타베이스명 file=c:\dump.dmp
:복원하고자하는 DB에 같은 이름의 Object가 있을때,오류를 무시하고 건너 띄고 싶을때 ignore 옵션사용
dos>imp 아이디/비밀번호@전역데이타베이스명 file=c:\dump.dmp ignore=y
:system계정으로 들어가 scott에서 Export한 데이터를 scott2에게 Import
dos>imp system/비밀번호@전역데이타베이스명 fromuser=scott touser=scott2 file=c:\dump.dmp
'oracle' 카테고리의 다른 글
| 24일차 2023-04-10 (0) | 2023.04.10 |
|---|---|
| 23일차 2023-04-07 (0) | 2023.04.07 |
| 22일차 2023-04-06 (0) | 2023.04.06 |
| 21일차 2023-04-05 (0) | 2023.04.05 |