오라클 강의자료 2011.09.26 (쌍용교육센터)

--2011.09.26

   view (뷰)
   1) 특징
     1. 가상 테이블
        테이블 : 실제로 데이터를 저장하고 있다.
        뷰 : 실제로 데이터를 저장하고 있지 않다.
     2. 수정, 삭제, 추가가 제한적으로 되어 있다.
     3. 검색을 쉽게, 쿼리 문장을 단순화 시키는 역할
   2) 사용처
     1. 복잡하고 긴 쿼리문을 뷰로 이용하면 단순해 진다.
     2. 보안에 유리하다.(검색용으로 주로 이용한다.)
   3) 단순뷰, 복잡뷰
     단순뷰 : 하나의 테이블을 이용한다.
     복잡뷰 : 한개 이상의 테이블을 이용
   4) 뷰생성
     CREATE VIEW view명 AS 서브쿼리
    
--테이블 복사
     CREATE table emp_copy AS SELECT * FROM emp;
     CREATE table dept_copy AS SELECT * FROM dept;
     SET linesize 150;
     SELECT * FROM emp_copy;
     SELECT * FROM dept_copy;
     COMMIT;

--뷰 생성

   뷰를 생성할 수 있게 권한 부여
   1. system계정으로 접속
      conn system/a12345678
   2. system 계정에서 scott에 권한을 부여한다.
      뷰를 생성할 수 있는 권한
      GRANT CREATE VIEW TO SCOTT;
      ==1) CREATE VIEW
 2) CREATE TABLE(*)
 3) CREATE SEQUENCE(*)
 4) CREATE INDEX
 5) CREATE PROCEDURE(*)
   3.  user 계정으로 다시 접속한다.
       conn user명/비밀번호
   4. 권한을 얻은 뷰를 생성
      CREATE VIEW view명 AS 서브쿼리
   5. 뷰 제거
      DROP VIEW view명;
   6. 뷰의 수정
      CREATE OR REPLACE VIEW
   7. 인라인뷰(뷰 중에 가장 많이 사용하는 뷰)
      SELECT FROM view명 AS 서브쿼리가 수행
   8. JDBC에서는 그냥 일반 테이블 사용법과 동일

CREATE VIEW emp_view AS SELECT empno, ename, hiredate, job, sal FROM emp_copy;

CREATE VIEW dept_view AS SELECT* FROM dept_copy;
SELECT* FROM dept_view;
DROP VIEW emp_view;
DROP VIEW dept_view;
CREATE VIEW emp_view AS SELECT empno, ename, job, sal FROM emp_copy;
SELECT * FROM emp_view;
INSERT INTO emp_view VALUES(9100,'이산','사원',1500);
COMMIT;
SELECT * FROM emp_view;

--복잡뷰 : 테이블이 한개 이상

   emp_copy : empno, ename, job, mgr, hiredate, sal, comm, deptno
   dept_copt : deptno, dname, loc

CREATE OR REPLACE VIEW emp_view_join AS
SELECT empno, ename, job, hiredate, sal, comm, e.deptno, dname, loc FROM --emp_copy e, dept_copy d
WHERE e.deptno=d.deptno;

SELECT * FROM emp_view_join;

테이블의 값
SELECT * FROM dept_copy;
인라인뷰게 값을 설정해서 출력
SELECT deptno, dname, loc FROM (SELECT * FROM dept_copy);

CREATE VIEW emp_mgr_view(empno,ename,mgr,mname,hiredate) AS SELECT e1.empno,e1.ename,e1.mgr,e2.ename,e1.hiredate FROM emp e1, emp e2 where e1.mgr = e2.empno;
SELECT * FROM emp_mgr_view;

 -- 2011.9.26 월요일

 프로시저 만들기
 프로시져 : 1) 재사용
     2) 캐쉬메모리에 저장이 되기때문에
        호출시에 속도가 빠르다.
     3) 여러개의 쿼리문장을 동시에 수행 할수 있다
 1. 생성부분
  CREATE PROCEDURE  프로시저명(매개변수)
  IS
  BEGIN
   쿼리문장
  END;
  /
 2. CREATE OR REPLACE PROCEDURE 명칭(매개변수)
    IS
    BEGIN
      쿼리문장
    END
    /

    delete from emp where empno = 7788
 3. 매개변수
   IN : 값을 입력 (update,delete,insert)
   OUT : 값을 출력 (select)
   INOUT : 입력, 출력
   SELECT empno, ename from emp
   where empno = 7788;

   void int print(int *a)
   {
  return *a;
   }


-- table 생성
DROP TABLE sawon;
CREATE TABLE sawon(sabun NUMBER PRIMARY KEY,
 name VARCHAR2(128)NOT NULL,
 dept VARCHAR2(20)NOT NULL,
 loc VARCHAR2(20)NOT NULL,
 pay NUMBER);

CREATE SEQUENCE sawon_sabun_seq START WITH 1 INCREMENT BY 1 NOCACHE;

INSERT INTO sawon VALUES(sawon_sabun_seq.nextval, '홍길동','영업부','서울',3000);
INSERT INTO sawon VALUES(sawon_sabun_seq.nextval, '심청이','자재부','서울',3000);
INSERT INTO sawon VALUES(sawon_sabun_seq.nextval, '박문수','기획부','서울',3000);

SELECT * FROM sawon;

CREATE OR REPLACE PROCEDURE del_sawon(vsabun IN NUMBER)
IS
BEGIN
  DELETE FROM sawon WHERE sabun = vsabun;
  COMMIT;
END;
/

-- 프로시져 수행
EXECUTE del_sawon(4);

SELECT * FROM sawon;

CREATE OR REPLACE PROCEDURE insert_sawon(vsabun IN sawon.sabun%TYPE,vname IN sawon.name%TYPE, vdept IN sawon.dept%TYPE,vloc IN sawon.loc%TYPE,vpay IN sawon.pay%TYPE)
IS
BEGIN
 INSERT INTO sawon VALUES(sawon_sabun_seq.nextval,vname,vdept,vloc,vpay);
 COMMIT;
END;
/

--실행
EXECUTE insert_sawon('박문수','영업부','경기',2400);
SELECT * FROM sawon;

 

CREATE OR REPLACE PROCEDURE update_sawon(vname IN sawon.name%TYPE, vdept IN sawon.dept%TYPE, vloc IN sawon.loc%TYPE, vpay IN sawon.pay%TYPE, vsabun IN sawon.sabun%TYPE)
IS
BEGIN
  update sawon set name=vname,dept=vdept,loc=vloc,pay=vpay where sabun=vsabun;
  COMMIT;
END;
/


--EXECUTE update_sawon('김두한','개발부','제주',4000,5);
SELECT * FROM sawon;

SELECT

CREATE OR REPLACE PROCEDURE select_sawon(
 vsabun IN sawon.sabun%TYPE,
 vname OUT sawon.name%TYPE,
 vdept OUT sawon.dept%TYPE,
 vloc OUT sawon.loc%TYPE,
 vpay OUT sawon.pay%TYPE)
IS
BEGIN
  SELECT name,dept,loc,pay INTO vname,vdept,vloc,vpay
  FROM sawon
  WHERE sabun=vsabun;
END;
/

/// 오류
--변수
VARIABLE var_name VARCHAR2(128);
VARIABLE var_dept VARCHAR2(20);
VARIABLE var_loc VARCHAR2(20);
VARIABLE var_pay NUMBER;
--프로시져 실행
EXECUTE select_sawon(7788,:var_name,:var_dept,:var_loc,:var_pay);
--실행결과 출력
PRINT var_name;
PRINT var_dept;
PRINT var_loc;
PRINT var_pay;

///오류