sql>오라클 DB수업 6일차 + PL/SQL START

-- delete 문 : 행을 삭제 // **** WHERE 절을 명시하지 않으면 모든 행이 삭제됨 ****

DELETE FROM emp WHERE empno='8000';

-- 다른 테이블의 정보를 기반으로 행 삭제

DELETE FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE empno='7839'); -- //셀렉트문으로 먼저 조회후 삭제하는 습관을 들여야함. 선조회 후삭제.

-- merge문  : 데이터베이스 테이블에서 조건에 따라 데이터를 갱신하거나 삽입하는 기능을 제공. 행당 행이 존재하는 경우 UPDATE를 수행하고

--               새로운 행일경우 INSERT를 수행

-- 행 병합 : copy_emp 테이블에서 행을 삽입 또는 갱신하여 emp 테이블과 일치시킴.

CREATE TABLE copy_emp ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), 

HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2), CONSTRAINT copy_pk_empno PRIMARY KEY (EMPNO),

CONSTRAINT copy_dept_fk1 FOREIGN KEY (deptno) REFERENCES dept(deptno)); --카피 생성

MERGE INTO copy_emp c USING emp e ON(c.empno = e.empno) WHEN MATCHED THEN UPDATE SET c.ename = e.ename ,

c.job =e.job , c.mgr = e.mgr , c.hiredate = e.hiredate , c.sal = e.sal , c.comm = e.comm , c.deptno = e.deptno WHEN NOT MATCHED THEN

INSERT VALUES (e.empno,e.ename,e.job,e.mgr,e.hiredate, e.sal, e.comm, e.deptno); --백업

-- 두개의 테이블을 병합하는 경우가 아니고 데이터를 삽입하려할 때 기존에 존재하는 데이터가 있으면 업데이트하도록 응용할 수 있음.

MERGE INTO emp c USING (SELECT 8000 empno , 'DENNIS2' ename , 'SALESMAN' job, '7698' mgr, '99/06/01' hiredate,

4000 sal, 200 comm, 30 deptno FROM dual )e /*컬럼에 저장될 데이터를 컬럼과 매칭하여 dual를 이용해 명시함*/

ON(c.empno = e.empno) WHEN MATCHED THEN UPDATE SET c.ename = e.ename ,

c.job =e.job , c.mgr = e.mgr , c.hiredate = e.hiredate , c.sal = e.sal , c.comm = e.comm , c.deptno = e.deptno WHEN NOT MATCHED THEN

INSERT VALUES (e.empno,e.ename,e.job,e.mgr,e.hiredate, e.sal, e.comm, e.deptno); --백업-

-- VIEW 질의문이 길고 복잡해질 때 간결하게 질의문을 작성해서 사용할 수 있도록 VIEW 생성할 수 있음

-- 보안적인 측면에서 특정 컬럼을 제외해서 표시하고 싶을 때 VIEW 사용 

          -- 복잡한 질의를 쉽게 작성하기 위해

          -- 데이터 독립성을 제공하기 위해

          -- 동일한 데이터로부터 다양한 결과를 얻기 위해

-- VIEW 작성

CREATE OR REPLACE VIEW emp10_view as SELECT empno id_number, ename name, sal*12 ann_salary FROM emp WHERE deptno=10;

-- VIEW 구조확인

DESC emp10_view;

-- VIEW의 데이터 호출

SELECT *FROM emp10_view;

CREATE OR REPLACE VIEW emp_info_view AS SELECT e.empno,e.ename,d.deptno,d.loc,d.dname FROM emp e ,dept d WHERE e.deptno=d.deptno;

DROP VIEW emp10_view; -- 뷰삭제

-- sequence : 유일한 값을 생성해주는 오라클객체 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할 수 있음

-- 보통 primary key 값을 생성하기 위해 사용

-- 시작 값이 1이고 1씩 증가하고, 최대값이 100000이 되는 시퀀스 생성

CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 MAXVALUE 100000;

--currval : 현재값을 반환

--nextval : 현재 시퀀스 값의 다음 값 반환

SELECT test_seq.nextval FROM dual;

SELECT test_seq.currval FROM dual;

-- 시퀀스의 수정

-- [주의 start with는 수정할 수 없음] 

--alter sequence sequence_name increment by n maxvalue n | nomaxvalueminvalue n | nominvaluecycle | nocycle ;

-- 시퀀스 삭제

--DROP SEQUENCE SEQUENCE_NAME;

--인덱스(INDEX) : 인덱스는 원하는 정보의 위치를 빠르고, 정확하고, 지능적으로 알아낼 수 있는 방법을 제공한다. 

--테이블의 컬럼에 대한 제약 조건을 설정할 때 Primary Key나 Unique로 설정하면 Oracle은 자동으로 이 컬럼에 대해 Unique Index를 설정한다. 

--데이터 값이 중복됨이 없이 유일한 값을 가지는 컬럼에 인덱스를 설정했을 때 이를 Unique Index라 한다.

--유일한 값을 가지는 컬럼에 인덱스 설정 : Unique Index

CREATE UNIQUE INDEX dname_idx ON dept (dname);

-- 유일한 값을 가지지 않는 컬럼에 인덱스 설정 : Non Unique Index

create index emp_ename_idx on emp(ename);

-- 동의어 : 동의어(객체의 다른 이름)를 생성하여 객체 액세스를 단순화한다.

-- - 다른 사용자가 소유한 테이블을 쉽게 참조

-- - 긴 객체 이름을 짧게 만든다.

--생성 

CREATE SYNONYM emp10 FOR emp10_view;

SELECT *FROM emp10;

--권한

--관리자 계정과 사용자 계정

--관리자 계정의 역할 :데이터베이스의 생성과 관리를 담당하는 슈퍼유저(Super User)계정이며

--데이터베이스 객체의 생성, 변경, 삭제 등의 작업이 가능하다. 

--sys계정 : 데이터베이스에서 발생하는 모든 문제를 처리할 수 있는 권한

--SYSTEM계정 : 오라클 데이터베이스를 유지,보수할 때 사용하는 계정, SYS와 달리 데이터베이스를 생성할 권을 가지지 않는다.

--사용자 계정의 역할 : 사용자 계정은 데이터베이스에 접근하여 데이터를 조작(삽입, 삭제, 수정, 검색)하고 관리하는 일을 수행할 수 있는 계정이다. 

-- 일반 계정은 업무에 필요한 최소한의 권한만 가지는 것을 원칙으로함.

권한을 받을 수 있도록

RUN SQL Command Lin 작성

--PL/SQL

--PL/SQL이란 ? 

--PL/SQL은 ‘Procedural language extension to Structured Query Language(SQL)’의 약자

--SQL을 확장한 순차적 처리 언어

--데이터베이스 질의어인 SQL과 일반 프로그래밍 언어의 특성을 결합한 언어. 즉 PL/SQL을 사용하면 조건문이나 반복문,

--변수나 상수를 선언해서 사용할 수 있을 뿐만 아니라 SQL도 사용할 수 있음

--PL/SQL의 기본 구조

--PL/SQL의 기본 단위는 블록(block)

--1) 선언부(declarative part) : 사용할 변수나 상수를 선언(선언부에만 변수와 상수 선언 가능)

--2) 실행부(executable part) : 실제 처리할 로직을 담당하는 부분 

--3) 예외처리부(exception-building part) : 실행부에서 로직을 처리하던 중 발생할 수 있는 각종 오류들에 대해 처리

--기본 구조

begin

     dbms_output.put_line('Hello World');

end;

--익명 블록

declare

  --변수를 선언할 수 있는 선언부

  message VARCHAR2(100);

begin

  --실행부에 사용할 변수는 선언부에서 미리 선언되어야함

  message:='Hello World!!!!'; 

  dbms_output.put_line(message);

end;

declare 

 --변수 선언 및 초기화

 message VARCHAR2(100) := 'HELLO WORLD';

begin

 dbms_output.put_line(message);

end;

declare 

     counter integer;

begin

     --변수 선언후 초기화 하지 않으면 널로 인식 

     counter:=counter+1;

     if counter is null then

     dbms_output.put_line('Result : counter is null');

end if;

end;

declare 

     counter integer;

     i integer;

begin

      for i in 1..9 loop

  counter := (2*i);

  dbms_output.put_line(' 2 * ' || i || ' = ' || counter);

  end loop;

end;

--예외처리부 : 

--Exception when 예외1 then 예외처리1

--      when 예외2 then 예외처리2

--      …

--      when others then 예외처리

declare

  counter integer;

begin

  counter := 10;

  counter := counter /0;

  dbms_output.put_line(counter);

exception when others then

  dbms_output.put_line('error occured');

end;

-- 콜렉션

-- varray

-- variable array의 약자로 고정 길이를 가진 배열

declare 

type varray_test is varray(3) of integer;

varray1 varray_test;

begin

-- 선언한 varray에 데이터를 할당

varray1:=varray_test(10,20,30);

-- varray에 저장된 데이터를 호출해서 출력

dbms_output.put_line(varray1(1));

end;