뷰 소유자(생성자) GRANT권한명(예: SELECT) ON 뷰이름 TO 권한받을ID - SELECT 권한을 '권한받을ID' 에게 준 것
권한 받을 사람 SELECT * FROM 뷰소유자ID.뷰이름
VIEW 권한 제거
REVOKE권한명(예: SELECT)ON 뷰이름 FROM 권한받은ID;
VIEW 수정
동일한 이름의 VIEW가 존재하면 수정해주고 없다면 새로 생성하는 명령
CREATEORREPLACEVIEW 뷰이름 AS SELECT ....
VIEW 삭제
DROP VIEW VIEW명
시스템 뷰
오라클이 제공하는 뷰 (데이터사전)
user_tables ==> 사용자가 만든 모든 테이블의 정보를 갖고 있음 user_objects ==> 사용자가 만든 모든 객체의 정보를 갖고 있음 (테이블, 뷰, 제약 등 모든 객체 정보) user_constraints ==> 사용자가 만든 모든 제약의 정보를 갖고 있음
user_tables
사용자가 만든 모든 테이블의 정보를 갖고 있음
USER_TABLE;
user_objects
사용자가 만든 모든 객체의 정보를 갖고 있음 (테이블, 뷰, 제약 등 모든 객체 정보)
USER_OBJECTS
user_constraints
사용자가 만든 모든 제약의 정보를 갖고 있음
CONSTRAINT_NAME : 제약명 CONSTRAINT_TYPE : 제약의 종류 TABLE_NAME : 테이블이름 STATUS : 제약의 상태 (때로는 제약을 활성화, 비활성화 시킬 수 있음
제약 정보 검색 시 자주 사용되는 속성들과 쿼리문 SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints
제약의 활성 / 비활성화
ALTER TABLE 테이블명 DISABLE/ENABLE CONSTRAINT 제약명
예시 ALTER TABLE emp DISABLE/ENABLE CONSTRAINT SYS_C008327;
인덱스
특정 컬럼에 인덱스를 만들어 검색 속도를 향상시킬 수 있음
레코드의 수가 적을 때는 굳이 인덱스를 만들 필요가 없음. 당연히 레코드의 수가 많을 때 인덱스를 사용
인덱스를 만들었는데 레코드의 값이 변경이 된다면 인덱스 재구성 필요
PK 로 설정한 컬럼은 자동으로 인덱스가 생성
인덱스 생성
생성법 CREATEINDEX 인덱스명 ON테이블명(컬럼명1, [컬럼명2])
사용법 SELECT * FROM BOOK WHERE 컬럼명1 = 'xx' [AND 컬럼명2 = 'yy'];
-> '컬럼명'을 검색(SELECT) 할 때에 검색속도 향상
인덱스 재구성 방법
ALTER INDEX 인덱스명 REBUILD;
인덱스 삭제
DROP INDEX 인덱스명;
인덱스 목록 보기
시스템에서 자동으로 생성한 인덱스는 SYS로 시작함
SELECT * FROM user_indexes;
데이터베이스 프로그래밍
DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정
오라클에서는 PL/SQL 언어를 사용하여, SQL Server는 T-SQL 이라는 언어를 사용
호스트 언어가 JSP, ASP, PHP 등의 웹 스크립트 언어인 경우 사용
PL/SQL의 종류
프로시저
트리거
사용자 정의 함수로 구성됨
프로시저(Procedure)
메소드처럼 빈번하게 사용하는 SQL 명령어를프로시저로 만들어서 사용 가능
프로시저를 정의하려면 'CREATE PROCEDURE' 문을 사용함 선언부와 실행부(BEGIN - END)로 구성
1. 프로시저 생성
CREATE OR REPLACE PROCEDURE 프로시저명( 매개변수 IN 자료형 -> 입력 용도 매개변수 IN 자료형 -> 입력 용도 매개변수 IN 자료형 -> 입력 용도 ...... [매개변수 OUT 자료형] -> 출력 용도 ( OUT 을 쓸 경우 DECLARE로 출력 해야함 ) ...... ) AS 지역변수 BEGIN 동작 (INSERT .. ~) INSERT : 일반INSERT 문 처럼 사용 DELETE : 일반 DELETE 문 처럼 사용 SELECT: 해당 결과 값을 지역변수를 선언하고 INTO로 저장해야함 ex) SELECT 속성 INTO 지역변수 END; /
CREATE OR REPLACE PROCEDURE insertCustomer(
p_custid IN NUMBER,
p_name IN VARCHAR2,
p_address IN VARCHAR2,
p_phone IN VARCHAR2
)
AS
BEGIN
INSERT INTO CUSTOMER VALUES(p_custid,p_name,p_address,p_phone);
END;
/
2. dbms_output.put_line (AS문에서 설정한 출력할 값이 저장된 변수명)
3. set serveroutput on; 입력 하여 설정해줄 것
동작 개념 SELETE 의 결과가 dloc에 담겨있으니 해당 값을 AS문에서 선언한 변수인 p_dloc로 저장 후 출력하는 개념
4. 오류 확인 명령어
SHOW ERRORS
5. DECLARE
( 매개변수 OUT NUMBER ) .... ..... .....
DECLARE 변수선언 begin 명령어 end; /
- 매개변수에 OUT 을 써줬기 때문에 변수와 출력문을 DECLARE 에서 따로 선언
고객 번호를 입력 받아 총 구매 가격을 출력
DECLARE 부분에서 result 변수의 역할
= custOrder 함수의 실행 결과는 담을 변수
CREATE OR REPLACE PROCEDURE custOrder(
p_custid IN NUMBER,
p_printprice OUT NUMBER
)
AS
BEGIN
SELECT SUM(saleprice) INTO p_printprice
FROM CUSTOMER c, ORDERs o
WHERE o.custid = c.CUSTID
AND c.custid = 1;
END;
/
DECLARE
result NUMBER;
BEGIN
custOrder(1, result);
dbms_output.put_line(result);
END;
6. CURSOR
프로시저 안에서 select 한 행의 수가 여러 건(복수)이라면 cursor를 사용 해야 함. (하나의 행씩 가져와서 동작하도록 함.)
AS문에 'CURSOR' 선언
AS문에 데이터를 저장할 '변수'를 선언
'CURSOR'를 순회(LOOP) 하면서 한 행씩 뽑아(FETCH)하여 변수에 '저장'
EXIT WHEN으로 LOOP 문 탈출 가능
AS문에서는 SELECT를 할때 INTO 써 줄 필요 없음.(BEGIN에서 FETCH 할 때 INTO 사용 해야함)
AS 변수명 자료형; CURSOR커서이름 IS Select 문 BEGIN OPEN 커서이름; --> 커서 동작 LOOP --> 반복하면서 복수의 자료들을 뽑기위한 루프문 FETCH 커서이름 INTO 변수명; --> 커서에 있는 자료들을 가져와 변수에 저장 EXIT WHEN 커서이름%NOTFOUND ; --> 커서에 더이상 자료가 없으면 EXIT dbms_output.put_line(변수명) ; --> EXIT 하지 않았을 경우 '변수명' 출력 END LOOP; CLOSE 커서이름;
관리자의 이름을 전달받아 부하 직원을 출력 (CURSOR 사용)
- 부하직원이 여러 명 반환되므로 cursor를 써야함
/* 쿼리문 */
SELECT e.ename
FROM emp e, emp m
WHERE e.MGR = m.eno
AND m.ename = '관리자명';
/* Procedure */
CREATE OR REPLACE PROCEDURE printEmpMtoC(
p_mname varchar2
)
AS
p_cname VARCHAR2(20);
CURSOR cn IS SELECT e.ename FROM emp e,
emp m WHERE e.MGR = m.eno AND m.ename = p_mname;
BEGIN
OPEN cn;
LOOP
FETCH cn INTO p_cname;
EXIT WHEN cn%NOTFOUND;
dbms_output.put_line(p_cname);
END LOOP;
CLOSE cn;
END;
/
트리거(Trigger)
DML 이벤트(insert, update, delete)가 발생할 때 자동으로 동작하는 프로시저
연쇄하여다른 테이블에도 적용할 필요가 존재할 때 사용
프로시저는 직접 호출해야 하는데 트리거는 해당 시점에 자동으로 동작
CREATE OR REPLACE TRIGGER 트리거이름 시점 : BEFORE / AFTER / INSTREA OF (얜 View 용) 명령어 : INSERT UPDATE [OF] [컬럼명] - 만약 [OF] [컬림명]이 붙으면 해당 컬럼의 데이터에 변경이 올 경우 동작 DELETE ON 테이블명 FOR EACH ROW WHEN 조건 DECLARE 변수선언 BEGIN 트리거가 해야할 명령어(들)
[IF문도 사용가능] if UPDATING THEN 속성명 :='변경 값'; elsif DELETING THEN 속성명 :='변경 값2'; END IF;
END; /
시스템 함수 UPDATING = 수정 되었다면 1반환
시스템 함수 DELETING = 삭제 되었다면 1반환
:new 와 :old
이해하려고 하지말고 외울부분이 있는데,
- UPDATE 또는 DELETE 실행 되기 전에 데이터가 잠깐? :OLD 테이블 로 넘어감
- INSERT 실행 되기 전에는 데이터가 :NEW 테이블로 넘어감
한 레코드(행)을 기준으로 생각하면 이해하기 쉽다 만약 한 행을 DELETE 하면 현재 기준(=NEW 기준)에는 한 행이 DELETE 되었기 때문에 당연히 NEW(현재 행)가 존재할 수가 없다. 출처 : https://www.youtube.com/watch?v=0KxIvQRPIIs
구분
:NEW
:OLD
INSERT
추가한 레코드
없음
UPDATE
수정 후 레코드
수정 전 레코드
DELETE
없음
삭제 전 레코드 이미 지워진 레코드
:new 명령어가 실행된 새로운 레코드(행)를 의미 (INSERT, UPDATE) 만약 INSERT 가 되었다면 INSERT 되고난 후의 레코드(행) 만약 UPDATE 되었다면 UPDATE 되고난 후의 레코드(행) 만약 DELETE 되었다면 이미 삭제되었으니 없음
:old 명령어가 실행되기 전의 레코드 (UDPATE, DELETE) 만약 UPDATE 되었다면 UPDATE 전의 레코드(행) 만약 DELETE 되었다면 DELETE 전의 레코드(행) 만약 INSERT 되었다면 이전 데이터는 당연히 없으니 없음
아래 예시 설명
DEPT 테이블에 INSERT가 일어한 후(AFTER) 해당 행을 가져오고, BEGIN 구문을 실행해라
CREATE OR REPLACE TRIGGER trg_dept_insert
AFTER INSERT ON DEPT FOR EACH ROW
DECLARE
BEGIN
INSERT INTO DEPT_BACK VALUES(:NEW.DNO, :NEW.DNAME, :NEW.DLOC );
END;
/
트리거 조회하기
SELECT * FROM ALL_TRIGGERS;
/*쉬운 예시*/
SELECT * FROM all_triggers WHERE OWNER LIKE '%##%'
