본문 바로가기
Computer Science/Database

[DB] SQLD 요약

by AI_Wooah 2022. 3. 7.

PL SQL 헬로월드 콘솔 활성화 하기위한 명령어

SET SERVEROUTPUT ON;

문제해결 시나리오 : PL/SQL문

-- 선언부, 실행부, 예외처리부로 구성
-- 선언부 : DECLARE로 시작, 변수나 상수를 선언하는 부분
-- 실행부 : BEGIN으로 시작, 제어문, 반복문, 함수의 정의 등 로직 작성 -> 반드시 작성해야 함
-- 예외처리부 : EXCEPTION으로 시작, 예외처리 내용 작성

레퍼런스 변수타입

DECLARE
  EMP_ID EMPLOYEE.EMP_ID%TYPE;
  EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
  SELECT EMP_ID, EMP_NAME  -- 조회할 컬럼
    INTO EMP_ID, EMP_NAME  -- 대입할 변수
    FROM EMPLOYEE
   WHERE EMP_ID = '&EMP_ID';  -- & : 입력프롬프트 창 띄워라
   
   DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
   DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/

TOP-N 분석 ROWNUM / RANK / DENSE_RANK

--RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 다음 순위 계산
SELECT
       *
  FROM (SELECT
               EMP_NAME
             , SALARY
             , RANK() OVER(ORDER BY SALARY DESC) 순위
          FROM EMPLOYEE)
 WHERE 순위 <= 5;

--DENSE_RANK() 함수 : 중복되는 순위 이후의 등수를 이후 등수로 처리
SELECT
       EMP_NAME
     , SALARY
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

```java
-- 인라인뷰를 활용한 TOP-N 분석
-- ROWNUM은 행 번호를 의미함
-- WHERE절을 수행 할 때 붙여진다.
-- 실제 존재하지 않는 가상 컬럼
SELECT ROWNUM, EMP_NAME, SALARY 
  FROM EMPLOYEE
 ORDER BY SALARY;

SELECT ROWNUM, EMP_NAME, SALARY 
  FROM (SELECT *
          FROM EMPLOYEE
         ORDER BY SALARY DESC)
 WHERE ROWNUM <= 5;

-- 급여 평균 3위 안에 드는 부서의
-- 부서코드와 부서명, 평균 급여를 조회하세요
SELECT 
       ROWNUM
       ,부서코드
       ,부서명
       ,"평균 급여"
FROM (SELECT 
       DEPT_CODE 부서코드
       ,DEPT_TITLE 부서명
       ,TRUNC(AVG(SALARY),-5) "평균 급여"
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON( D.DEPT_ID = E.DEPT_CODE)
GROUP BY DEPT_CODE,DEPT_TITLE
ORDER BY AVG(SALARY) DESC)
WHERE ROWNUM <= 3;

-- 직원 정보에서 급여를 가장 많이 받는 순으로 이름, 급여, 순위 조회
-- RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 다음 순위 계산
-- DENSC_RANK() 함수 : 중복되는 순위 이후의 등수를 이후 등수로 처리

SELECT EMP_NAME
     , SALARY
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;
  

SELECT EMP_NAME
     , SALARY
     , RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

SELECT 
       *
  FROM (SELECT EMP_NAME
             , SALARY
             , RANK() OVER(ORDER BY SALARY DESC) 순위
          FROM EMPLOYEE)
 WHERE 순위 <= 5;
```

에러를 보여지게하는 SHOW ERRORS

PL/SQL은 오류를 안보여주기때문에 아래 명령어로 확인

SHOW ERRORS;

INSERT INTO 테이블명

INSERT INTO USER_NOCONS
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

PL SQL 구조 익명 블록

-- PL/SQL 블록(anonymous block) : 이름 없는 블록이라고 불리며 간단한 block 수행 시 사용됨
-- 프로시져(procedure) : 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형으로,
--                     단독으로 실행되거나 다른 프로시져나 다른 툴 등에 호출되어 실행됨
-- 함수(function) : procedure와 수행 결과가 유사하나 값 반환 여부에 따라 차이가 있음

---------------------------------------------------------------------------------
--             Anonymous Block          procedure                Function
--                (익명블록)              (프로시져)                 (함수)
-- ---------------------------------------------------------------------------------
-- 객체로 저장          x                     o                        o
-- 값 반환             x                     x                        o
-- 이름 지정 여부       x                     o                        o
-- 파라미터 사용        x                     o                         
-- 타 응용프로그래밍  
-- 호출 여부           x                     o                         o
-- ---------------------------------------------------------------------------------

FUNCTION (함수) RETURN 있음

FUNCTION (함수) : 프로시져와 사용 용도가 거의 비슷하다. 실행 결과를 되돌려받을 수 있다.(RETURN)

CREATE OR REPLACE FUNCTION BONUS_CALC(V_EMP EMPLOYEE.EMP_ID%TYPE) RETURN NUMBER
IS
  V_SAL EMPLOYEE.SALARY%TYPE;
  V_BONUS EMPLOYEE.BONUS%TYPE;
  CALC_SAL NUMBER;
BEGIN
  SELECT SALARY, NVL(BONUS, 0)
  INTO V_SAL, V_BONUS
  FROM EMPLOYEE
  WHERE EMP_ID = V_EMP;
  
  CALC_SAL := (V_SAL + (V_SAL + V_BONUS)) * 12;
  
  RETURN CALC_SAL;
END;
/

VARIABLE VAR_CALC NUMBER;

EXEC : VAR_CALC := BONUS_CALC('&EMP_ID');

SELECT EMP_ID, EMP_NAME, BONUS_CALC(EMP_ID)
FROM EMPLOYEE
WHERE BONUS_CALC(EMP_ID) > 30000000;

프로시져 객체(RETURN 없음)

프로시져(PROCEDURE) : PL/SQL문과 DML 구문을 저장하는 객체이다. 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 호출을 통해서 간단히 실행시키기 위한 목적으로 사용된다.

CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_DUP;

--프로시져 생성
CREATE OR REPLACE PROCEDURE DBL_ALL_EMP
IS
BEGIN
  DELETE FROM EMP_DUP;
  COMMIT;
END;
/

EXECUTE DBL_ALL_EMP;
EXEC DBL_ALL_EMP;

SELECT * FROM EMP_DUP;

-- 매개변수 있는 프로시져
CREATE OR REPLACE PROCEDURE DEL_EMP_ID(V_EMP_ID EMPLOYEE.EMP_ID%TYPE)
IS
BEGIN
  DELETE FROM EMPLOYEE
  WHERE EMP_ID = V_EMP_ID;
END;
/

EXEC DEL_EMP_ID('&EMP_ID');

SELECT * FROM EMPLOYEE;

ROLLBACK;

-- IN/OUT 매개변수 있는 프로시져
CREATE OR REPLACE PROCEDURE SELECT_EMP_ID(
  V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE,
  V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,
  V_SALARY OUT EMPLOYEE.SALARY%TYPE,
  V_BONUS OUT EMPLOYEE.BONUS%TYPE
)
IS
BEGIN
  SELECT EMP_NAME, SALARY, NVL(BONUS, 0)
  INTO V_EMP_NAME, V_SALARY, V_BONUS
  FROM EMPLOYEE
  WHERE EMP_ID = V_EMP_ID;
END;
/

VARIABLE VAR_EMP_NAME VARCHAR2(30);
VARIABLE VAR_SALARY NUMBER;
VARIABLE VAR_BONUS NUMBER;

EXEC SELECT_EMP_ID(200, :VAR_EMP_NAME, :VAR_SALARY, :VAR_BONUS);

PRINT VAR_EMP_NAME;
PRINT VAR_SALARY;
PRINT VAR_BONUS;

SET AUTOPRINT ON;

트리거

테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체

  • 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며,
  • 그 데이터의 행의 실제 값 제어 가능
  • 문장 트리거 : 트리거 사건에 의해 단 한번만 실행, 컬럼의 각 데이터 행 제어 불가능

CREATE OR REPLACE TRIGGER 트리거이름 AFTER INSERT ON 테이블명 BEGIN DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.'); END; /

  • ㅇ
  • -- 어떤 일의 시작점. 오라클에서의 트리거는 테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 -- 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체를 의미 -- 특정 DML 구문이 실행될 때 자동으로 동작하게 하는 구문 CREATE OR REPLACE TRIGGER TRG_01 AFTER INSERT ON EMPLOYEE BEGIN DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.'); -- EMPLOYEE 테이블에 INSERT됐을 때 실행 할 내용 END; / INSERT INTO EMPLOYEE VALUES(400, '길성춘', '650512-1151432', 'gil_sc@kh.or.kr', '01012345678', 'D5', 'J3', 'S5', 3000000, 0.1, 200, SYSDATE, NULL, DEFAULT); -- 유도속성(다른 연산에 의해 계산될 수 있는 중복될 수 있는 속성. EX)회원포인트)이 있는 구문에 많이 사용 -- 복잡하게 DML을 작성 할 경우 오류 발생 지점을 찾기 어려운 단점이 있다.(프로시져와 같은 단점) CREATE TABLE PRODUCT( PCODE NUMBER PRIMARY KEY, -- 제품코드, 인위적식별자 PNAME VARCHAR2(30), -- 제품명 BRANC VARCHAR2(30), -- 제조사 PRICE NUMBER, -- 가격 STOCK NUMBER DEFAULT 0 -- 재고량 ); CREATE TABLE PRO_DETAIL( DCODE NUMBER PRIMARY KEY, -- 이력발생번호, 인위적식별자 PCODE NUMBER, -- 제품코드 PDATE DATE, -- 이력발생일자 AMOUNT NUMBER, -- 이력발생수량 STATUS VARCHAR2(10) CHECK(STATUS IN ('입고', '출고')), -- 이력발생구분 FOREIGN KEY(PCODE) REFERENCES PRODUCT(PCODE) ); -- 카디널리티 : 관계대응수 -- 시퀀스 생성 CREATE SEQUENCE SEQ_PCODE; CREATE SEQUENCE SEQ_DCODE; INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시노트8', '샘숭', 900000, DEFAULT); INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '아이펀8', '애펄', 900000, DEFAULT); INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '대륙폰', '샤우미', 300000, DEFAULT); SELECT * FROM PRODUCT; SELECT * FROM PRO_DETAIL; -- 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며, 그 데이터의 행의 실제 값을 제어할 수 있다. -- 문장 트리거 : 트리거 사건에 의해 단 한번만 실행되며, 컬럼의 각 데이터 행을 제어할 수 없다. CREATE OR REPLACE TRIGGER TRG_02 AFTER INSERT ON PRO_DETAIL FOR EACH ROW -- 행 트리거로 만드는 옵션 BEGIN IF :NEW.STATUS = '입고' -- 새롭게 행이 INSERT될 때의 상태값(기존 행은 OLD.STATUS) THEN -- 행 트리거인 경우만 이렇게 사용 가능 UPDATE PRODUCT SET STOCK = STOCK + :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; IF :NEW.STATUS = '출고' THEN UPDATE PRODUCT SET STOCK = STOCK - :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; END; / INSERT INTO PRO_DETAIL VALUES (SEQ_DCODE.NEXTVAL, 1, SYSDATE, 5, '입고'); SELECT * FROM PRO_DETAIL; SELECT * FROM PRODUCT; INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 2, SYSDATE, 11, '입고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 3, SYSDATE, 50, '입고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 3, SYSDATE, 11, '출고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 1, SYSDATE, 5, '출고');

이미 만들어져있는 테이블에 제약조건을 추가할때 ALTER TABLE 테이블명 ADD

  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 PRIMARY KEY (컬럼명);
  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 UNIQUE (컬럼명);
  • ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건이름 NOT NULL;
-- 컬럼에 제약조건 추가
ALTER TABLE DEPT_COPY2
ADD (CONSTRAINT PK_DEPT_ID2 PRIMARY KEY (DEPT_ID));
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT UN_DEPT_TITLE2 UNIQUE(DEPT_TITLE);
ALTER TABLE DEPT_COPY2
MODIFY LOCATION_ID CONSTRAINT NN_LID NOT NULL;

SET OPERATOR -> UNION, UNION ALL, MINUS, INTERSECT 의미

  • UNION
  • 중복되는 영역 제외하고 여러 개 쿼리 결과를 하나로 합치는 연산자
  • UNION ALL
  • 중복되는 영역을 포함시켜서 여러 개 쿼리 결과를 하나로 합치는 연산자
  • INTERSECT
  • 여러개의 결과에서 공통된 부분만 결과로 추출
  • MINUS
  • 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출

DB 오브젝트 정리

  • 테이블 (TABLE)
  • 데이터의 저장을 위한 객체로 DB의 기본적인 저장구조
  • 뷰 (VIEW)한개 이상의 테이블이나 다른 뷰를 기초로하여복잡한 access를 단순화하여 보안유지등을 위해 사용
  • 사용자가 원하는 형태의 데이터를 볼 수 있게 하고
  • 기본테이블을 기초로 한 논리적테이블(가상테이블)
  • 시퀀스 (SEQUENCE)시퀀스는 유일하고 연속적인 번호를 만드는 객체
  • 주로 pk를 생성하기 위해 사용
  • 순차적으로 primary key값을 자동으로 생성
  • 인덱스 (INDEX)인덱스는 DB를 보다 효율적이고 신속하게 검색할 수 있도록 도와주는 객체로서
  • 검색속도의 향상 및 데이터의 중복을 방지하기 위해 사용
  • 검색의 성능향상을 위해 생성
  • 패키지 (PACKAGE)패키지는 컴파일 과정을 거쳐 데이터베이스에 저장되며
  • 다른 프로그램에서 패키지의 항목을 참조하고 실행 가능
  • 수, 상수, 서브프로그램등의 항목을 묶어놓은 객체
  • 트리거 (TRIGGER)
    • 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며,
    • 그 데이터의 행의 실제 값 제어 가능
    • 문장 트리거 : 트리거 사건에 의해 단 한번만 실행, 컬럼의 각 데이터 행 제어 불가능
  • 테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체
  • 동의어 (SYNONYM)실질적으로 그 자체가 객체가 아니라 객체에 대한 직접적인 참조
  • 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말하며
  • 프로시져 (PROCEDURE)
  • PL/SQL 문과 DML 구문을 저장하는 객체 필요할 때 마다 복잡한 구문을 다시 입력할 필요 없이 호출을 통해서 간단히 실행시키기 위한 목적으로 사용
  • 함수 (FUNCTION) 파라미터로 이용해서 값 전달받음 정해진 작업을 수행한 후 결과 RETURN
  • 프로시져와 사용 용도가거의 비슷하다 실행 결과를 되돌려받을 수 있다 (RETURN값 O)
  • 사용자 (USER)기본적으로 사용자가 소유한 객체에 대한 모든 권한은 자동적으로 획득
  • 객체 소유자는 다른 사용자에게 특정 PRIVILEGES 부여 가능
  • 클러스터 (CLUSTER)물리적 디스크 I/O 효율성 증대, 조인 시 접근속도 향상
  • 공통 컬럼에 대한 동일 데이터 블록을 공유하는 테이블집합
  • 각 뭘 의미하는지 서술디비에서 우리가 배운 객체들이 뭔지 설명트리거사용자
  • -- 오라클에서의 객체 -- : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), -- 패키지(PACKAGE), 트리거(TRIGGER), 동의어(SYNONYM), -- 프로시져(PROCEDURE), 함수(FUNCTION), 사용자(USER)
  • 시노님=동의어

테이블의 정의 및 서브쿼리 종류

  1. 단일행 서브쿼리 : 서브쿼리의 조회 결과 값의 개수가 1개 일 때
  2. 다중행 서브쿼리 : 서브쿼리의 조회 결과 값의 행이 여러 개 일 때
  3. 다중열 서브쿼리: 서브쿼리의 조회 결과 칼럼의 갯수가 여러 개 일 때
  4. 다중행 다중열 서브쿼리 : 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 떄
  5. 상호연관 서브쿼리 : 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산 할 떄, 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀐다
  6. 스칼라 서브쿼리 : 상관쿼리 이면서 결과값이 한 개인 서브쿼리

관리자계정이란? 사용자계정이란?

사용자 관리

<관리자계정>
 : 사용자 관리 권한이 있음. 사용자의 계정과 암호 설정 및 권한 부여 가능.
   WITH ADMIN OPTION 권한을 설정하면 사용자계정에도 원하는 권한 부여 가능.
   ROLE을 사용해 간편하게 권한을 부여할 수 있다.

<사용자계정>
 : 사용자 계정은 권한 부여 불가. 관리자계정에서 권한을 설정해야
   해당 객체에 접근하거나 해당 테이블에 SQL문을 사용할 수 있는 권한이 생긴다.
-- SAMPLE 계정은 SYSTEM계정이 아니라 권한 부여 불가
-- SYSTEM 계정에서 WITH ADMIN OPTION 권한 설정 후에는 가능

GRANT CREATE SYNONYM TO EMPLOYEE;

SELECT * FROM EMP;

CREATE PUBLIC SYNONYM DEPT FOR EMPLOYEE.DEPARTMENT;

SELECT * FROM DEPT;

-- <사용자 관리>
-- : 사용자의 계정과 암호 설정, 권한 부여

-- 오라클 데이터베이스를 설치하며 기본적으로 제공되는 계정
--1. SYS
--2. SYSTEM
--3. SCOTT(교육용 샘플 계정) : 11G부터 별도로 생성해야 함
--4. HR (샘플 계정) : 처음에는 잠겨져 있고, 11G에서는 없음

/* 보안을 위한 데이터베이스 관리자
   : 사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대해
   특정 권한을 가질 수 있게 하는 권한이 있음
   다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안 설정
   데이터베이스에 접근하는 사용자마다 서로 다른 권한과 롤(role)을 부여함
   
    권한 
    : 사용자가 특정 테이블에 접근할 수 있도록 하거나
    해당 테이블에 SQL(SELECT/INSERT/UPDATE/DELETE)문을
    사용할 수 있도록 제한을 두는 것
    
    시스템 권한
    : 데이터베이스 관리자가 가지고 있는 권한.
    CREATE USER(사용자 계정 만들기)
    DROP USER(사용자 계정 삭제)
    DROP ANY TABLE(임의의 테이블 삭제)
    QUERY REWRITE(함수 기반 인덱스 생성 권한)
    
    시스템 관리자가 사용자에게 부여하는 권한
    CREATE SESSION(데이터베이스에 접속)
    CREATE TABLE(테이블 생성)
    CREATE VIEW(뷰 생성)
    CREATE SEQUENCE(시퀀스 생성)
    CREATE PROCEDURE(프로시져 생성 권한)

*/
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;

GRANT CREATE SESSION TO SAMPLE;
GRANT CREATE TABLE TO SAMPLE;

PL SQL 헬로월드 콘솔 활성화 하기위한 명령어

SET SERVEROUTPUT ON;

문제해결 시나리오 : PL/SQL문

-- 선언부, 실행부, 예외처리부로 구성
-- 선언부 : DECLARE로 시작, 변수나 상수를 선언하는 부분
-- 실행부 : BEGIN으로 시작, 제어문, 반복문, 함수의 정의 등 로직 작성 -> 반드시 작성해야 함
-- 예외처리부 : EXCEPTION으로 시작, 예외처리 내용 작성

레퍼런스 변수타입

DECLARE
  EMP_ID EMPLOYEE.EMP_ID%TYPE;
  EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
  SELECT EMP_ID, EMP_NAME  -- 조회할 컬럼
    INTO EMP_ID, EMP_NAME  -- 대입할 변수
    FROM EMPLOYEE
   WHERE EMP_ID = '&EMP_ID';  -- & : 입력프롬프트 창 띄워라
   
   DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
   DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/

TOP-N 분석 ROWNUM / RANK / DENSE_RANK

--RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 다음 순위 계산
SELECT
       *
  FROM (SELECT
               EMP_NAME
             , SALARY
             , RANK() OVER(ORDER BY SALARY DESC) 순위
          FROM EMPLOYEE)
 WHERE 순위 <= 5;

--DENSE_RANK() 함수 : 중복되는 순위 이후의 등수를 이후 등수로 처리
SELECT
       EMP_NAME
     , SALARY
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

```java
-- 인라인뷰를 활용한 TOP-N 분석
-- ROWNUM은 행 번호를 의미함
-- WHERE절을 수행 할 때 붙여진다.
-- 실제 존재하지 않는 가상 컬럼
SELECT ROWNUM, EMP_NAME, SALARY 
  FROM EMPLOYEE
 ORDER BY SALARY;

SELECT ROWNUM, EMP_NAME, SALARY 
  FROM (SELECT *
          FROM EMPLOYEE
         ORDER BY SALARY DESC)
 WHERE ROWNUM <= 5;

-- 급여 평균 3위 안에 드는 부서의
-- 부서코드와 부서명, 평균 급여를 조회하세요
SELECT 
       ROWNUM
       ,부서코드
       ,부서명
       ,"평균 급여"
FROM (SELECT 
       DEPT_CODE 부서코드
       ,DEPT_TITLE 부서명
       ,TRUNC(AVG(SALARY),-5) "평균 급여"
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON( D.DEPT_ID = E.DEPT_CODE)
GROUP BY DEPT_CODE,DEPT_TITLE
ORDER BY AVG(SALARY) DESC)
WHERE ROWNUM <= 3;

-- 직원 정보에서 급여를 가장 많이 받는 순으로 이름, 급여, 순위 조회
-- RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 다음 순위 계산
-- DENSC_RANK() 함수 : 중복되는 순위 이후의 등수를 이후 등수로 처리

SELECT EMP_NAME
     , SALARY
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;
  

SELECT EMP_NAME
     , SALARY
     , RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

SELECT 
       *
  FROM (SELECT EMP_NAME
             , SALARY
             , RANK() OVER(ORDER BY SALARY DESC) 순위
          FROM EMPLOYEE)
 WHERE 순위 <= 5;
```

에러를 보여지게하는 SHOW ERRORS

PL/SQL은 오류를 안보여주기때문에 아래 명령어로 확인

SHOW ERRORS;

INSERT INTO 테이블명

INSERT INTO USER_NOCONS
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

PL SQL 구조 익명 블록

-- PL/SQL 블록(anonymous block) : 이름 없는 블록이라고 불리며 간단한 block 수행 시 사용됨
-- 프로시져(procedure) : 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형으로,
--                     단독으로 실행되거나 다른 프로시져나 다른 툴 등에 호출되어 실행됨
-- 함수(function) : procedure와 수행 결과가 유사하나 값 반환 여부에 따라 차이가 있음

---------------------------------------------------------------------------------
--             Anonymous Block          procedure                Function
--                (익명블록)              (프로시져)                 (함수)
-- ---------------------------------------------------------------------------------
-- 객체로 저장          x                     o                        o
-- 값 반환             x                     x                        o
-- 이름 지정 여부       x                     o                        o
-- 파라미터 사용        x                     o                         
-- 타 응용프로그래밍  
-- 호출 여부           x                     o                         o
-- ---------------------------------------------------------------------------------

FUNCTION (함수) RETURN 있음

FUNCTION (함수) : 프로시져와 사용 용도가 거의 비슷하다. 실행 결과를 되돌려받을 수 있다.(RETURN)

CREATE OR REPLACE FUNCTION BONUS_CALC(V_EMP EMPLOYEE.EMP_ID%TYPE) RETURN NUMBER
IS
  V_SAL EMPLOYEE.SALARY%TYPE;
  V_BONUS EMPLOYEE.BONUS%TYPE;
  CALC_SAL NUMBER;
BEGIN
  SELECT SALARY, NVL(BONUS, 0)
  INTO V_SAL, V_BONUS
  FROM EMPLOYEE
  WHERE EMP_ID = V_EMP;
  
  CALC_SAL := (V_SAL + (V_SAL + V_BONUS)) * 12;
  
  RETURN CALC_SAL;
END;
/

VARIABLE VAR_CALC NUMBER;

EXEC : VAR_CALC := BONUS_CALC('&EMP_ID');

SELECT EMP_ID, EMP_NAME, BONUS_CALC(EMP_ID)
FROM EMPLOYEE
WHERE BONUS_CALC(EMP_ID) > 30000000;

프로시져 객체(RETURN 없음)

프로시져(PROCEDURE) : PL/SQL문과 DML 구문을 저장하는 객체이다. 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 호출을 통해서 간단히 실행시키기 위한 목적으로 사용된다.

CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_DUP;

--프로시져 생성
CREATE OR REPLACE PROCEDURE DBL_ALL_EMP
IS
BEGIN
  DELETE FROM EMP_DUP;
  COMMIT;
END;
/

EXECUTE DBL_ALL_EMP;
EXEC DBL_ALL_EMP;

SELECT * FROM EMP_DUP;

-- 매개변수 있는 프로시져
CREATE OR REPLACE PROCEDURE DEL_EMP_ID(V_EMP_ID EMPLOYEE.EMP_ID%TYPE)
IS
BEGIN
  DELETE FROM EMPLOYEE
  WHERE EMP_ID = V_EMP_ID;
END;
/

EXEC DEL_EMP_ID('&EMP_ID');

SELECT * FROM EMPLOYEE;

ROLLBACK;

-- IN/OUT 매개변수 있는 프로시져
CREATE OR REPLACE PROCEDURE SELECT_EMP_ID(
  V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE,
  V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,
  V_SALARY OUT EMPLOYEE.SALARY%TYPE,
  V_BONUS OUT EMPLOYEE.BONUS%TYPE
)
IS
BEGIN
  SELECT EMP_NAME, SALARY, NVL(BONUS, 0)
  INTO V_EMP_NAME, V_SALARY, V_BONUS
  FROM EMPLOYEE
  WHERE EMP_ID = V_EMP_ID;
END;
/

VARIABLE VAR_EMP_NAME VARCHAR2(30);
VARIABLE VAR_SALARY NUMBER;
VARIABLE VAR_BONUS NUMBER;

EXEC SELECT_EMP_ID(200, :VAR_EMP_NAME, :VAR_SALARY, :VAR_BONUS);

PRINT VAR_EMP_NAME;
PRINT VAR_SALARY;
PRINT VAR_BONUS;

SET AUTOPRINT ON;

트리거

테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체

  • 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며,
  • 그 데이터의 행의 실제 값 제어 가능
  • 문장 트리거 : 트리거 사건에 의해 단 한번만 실행, 컬럼의 각 데이터 행 제어 불가능

CREATE OR REPLACE TRIGGER 트리거이름 AFTER INSERT ON 테이블명 BEGIN DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.'); END; /

  • ㅇ
  • -- 어떤 일의 시작점. 오라클에서의 트리거는 테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 -- 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체를 의미 -- 특정 DML 구문이 실행될 때 자동으로 동작하게 하는 구문 CREATE OR REPLACE TRIGGER TRG_01 AFTER INSERT ON EMPLOYEE BEGIN DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.'); -- EMPLOYEE 테이블에 INSERT됐을 때 실행 할 내용 END; / INSERT INTO EMPLOYEE VALUES(400, '길성춘', '650512-1151432', 'gil_sc@kh.or.kr', '01012345678', 'D5', 'J3', 'S5', 3000000, 0.1, 200, SYSDATE, NULL, DEFAULT); -- 유도속성(다른 연산에 의해 계산될 수 있는 중복될 수 있는 속성. EX)회원포인트)이 있는 구문에 많이 사용 -- 복잡하게 DML을 작성 할 경우 오류 발생 지점을 찾기 어려운 단점이 있다.(프로시져와 같은 단점) CREATE TABLE PRODUCT( PCODE NUMBER PRIMARY KEY, -- 제품코드, 인위적식별자 PNAME VARCHAR2(30), -- 제품명 BRANC VARCHAR2(30), -- 제조사 PRICE NUMBER, -- 가격 STOCK NUMBER DEFAULT 0 -- 재고량 ); CREATE TABLE PRO_DETAIL( DCODE NUMBER PRIMARY KEY, -- 이력발생번호, 인위적식별자 PCODE NUMBER, -- 제품코드 PDATE DATE, -- 이력발생일자 AMOUNT NUMBER, -- 이력발생수량 STATUS VARCHAR2(10) CHECK(STATUS IN ('입고', '출고')), -- 이력발생구분 FOREIGN KEY(PCODE) REFERENCES PRODUCT(PCODE) ); -- 카디널리티 : 관계대응수 -- 시퀀스 생성 CREATE SEQUENCE SEQ_PCODE; CREATE SEQUENCE SEQ_DCODE; INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시노트8', '샘숭', 900000, DEFAULT); INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '아이펀8', '애펄', 900000, DEFAULT); INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '대륙폰', '샤우미', 300000, DEFAULT); SELECT * FROM PRODUCT; SELECT * FROM PRO_DETAIL; -- 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며, 그 데이터의 행의 실제 값을 제어할 수 있다. -- 문장 트리거 : 트리거 사건에 의해 단 한번만 실행되며, 컬럼의 각 데이터 행을 제어할 수 없다. CREATE OR REPLACE TRIGGER TRG_02 AFTER INSERT ON PRO_DETAIL FOR EACH ROW -- 행 트리거로 만드는 옵션 BEGIN IF :NEW.STATUS = '입고' -- 새롭게 행이 INSERT될 때의 상태값(기존 행은 OLD.STATUS) THEN -- 행 트리거인 경우만 이렇게 사용 가능 UPDATE PRODUCT SET STOCK = STOCK + :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; IF :NEW.STATUS = '출고' THEN UPDATE PRODUCT SET STOCK = STOCK - :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; END; / INSERT INTO PRO_DETAIL VALUES (SEQ_DCODE.NEXTVAL, 1, SYSDATE, 5, '입고'); SELECT * FROM PRO_DETAIL; SELECT * FROM PRODUCT; INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 2, SYSDATE, 11, '입고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 3, SYSDATE, 50, '입고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 3, SYSDATE, 11, '출고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 1, SYSDATE, 5, '출고');

이미 만들어져있는 테이블에 제약조건을 추가할때 ALTER TABLE 테이블명 ADD

  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 PRIMARY KEY (컬럼명);
  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 UNIQUE (컬럼명);
  • ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건이름 NOT NULL;
-- 컬럼에 제약조건 추가
ALTER TABLE DEPT_COPY2
ADD (CONSTRAINT PK_DEPT_ID2 PRIMARY KEY (DEPT_ID));
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT UN_DEPT_TITLE2 UNIQUE(DEPT_TITLE);
ALTER TABLE DEPT_COPY2
MODIFY LOCATION_ID CONSTRAINT NN_LID NOT NULL;

SET OPERATOR -> UNION, UNION ALL, MINUS, INTERSECT 의미

  • UNION
  • 중복되는 영역 제외하고 여러 개 쿼리 결과를 하나로 합치는 연산자
  • UNION ALL
  • 중복되는 영역을 포함시켜서 여러 개 쿼리 결과를 하나로 합치는 연산자
  • INTERSECT
  • 여러개의 결과에서 공통된 부분만 결과로 추출
  • MINUS
  • 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출

DB 오브젝트 정리

  • 테이블 (TABLE)
  • 데이터의 저장을 위한 객체로 DB의 기본적인 저장구조
  • 뷰 (VIEW)한개 이상의 테이블이나 다른 뷰를 기초로하여복잡한 access를 단순화하여 보안유지등을 위해 사용
  • 사용자가 원하는 형태의 데이터를 볼 수 있게 하고
  • 기본테이블을 기초로 한 논리적테이블(가상테이블)
  • 시퀀스 (SEQUENCE)시퀀스는 유일하고 연속적인 번호를 만드는 객체
  • 주로 pk를 생성하기 위해 사용
  • 순차적으로 primary key값을 자동으로 생성
  • 인덱스 (INDEX)인덱스는 DB를 보다 효율적이고 신속하게 검색할 수 있도록 도와주는 객체로서
  • 검색속도의 향상 및 데이터의 중복을 방지하기 위해 사용
  • 검색의 성능향상을 위해 생성
  • 패키지 (PACKAGE)패키지는 컴파일 과정을 거쳐 데이터베이스에 저장되며
  • 다른 프로그램에서 패키지의 항목을 참조하고 실행 가능
  • 수, 상수, 서브프로그램등의 항목을 묶어놓은 객체
  • 트리거 (TRIGGER)
    • 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며,
    • 그 데이터의 행의 실제 값 제어 가능
    • 문장 트리거 : 트리거 사건에 의해 단 한번만 실행, 컬럼의 각 데이터 행 제어 불가능
  • 테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체
  • 동의어 (SYNONYM)실질적으로 그 자체가 객체가 아니라 객체에 대한 직접적인 참조
  • 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말하며
  • 프로시져 (PROCEDURE)
  • PL/SQL 문과 DML 구문을 저장하는 객체 필요할 때 마다 복잡한 구문을 다시 입력할 필요 없이 호출을 통해서 간단히 실행시키기 위한 목적으로 사용
  • 함수 (FUNCTION) 파라미터로 이용해서 값 전달받음 정해진 작업을 수행한 후 결과 RETURN
  • 프로시져와 사용 용도가거의 비슷하다 실행 결과를 되돌려받을 수 있다 (RETURN값 O)
  • 사용자 (USER)기본적으로 사용자가 소유한 객체에 대한 모든 권한은 자동적으로 획득
  • 객체 소유자는 다른 사용자에게 특정 PRIVILEGES 부여 가능
  • 클러스터 (CLUSTER)물리적 디스크 I/O 효율성 증대, 조인 시 접근속도 향상
  • 공통 컬럼에 대한 동일 데이터 블록을 공유하는 테이블집합
  • 각 뭘 의미하는지 서술디비에서 우리가 배운 객체들이 뭔지 설명트리거사용자
  • -- 오라클에서의 객체 -- : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), -- 패키지(PACKAGE), 트리거(TRIGGER), 동의어(SYNONYM), -- 프로시져(PROCEDURE), 함수(FUNCTION), 사용자(USER)
  • 시노님=동의어

테이블의 정의 및 서브쿼리 종류

  1. 단일행 서브쿼리 : 서브쿼리의 조회 결과 값의 개수가 1개 일 때
  2. 다중행 서브쿼리 : 서브쿼리의 조회 결과 값의 행이 여러 개 일 때
  3. 다중열 서브쿼리: 서브쿼리의 조회 결과 칼럼의 갯수가 여러 개 일 때
  4. 다중행 다중열 서브쿼리 : 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 떄
  5. 상호연관 서브쿼리 : 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산 할 떄, 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀐다
  6. 스칼라 서브쿼리 : 상관쿼리 이면서 결과값이 한 개인 서브쿼리

관리자계정이란? 사용자계정이란?

사용자 관리

<관리자계정>
 : 사용자 관리 권한이 있음. 사용자의 계정과 암호 설정 및 권한 부여 가능.
   WITH ADMIN OPTION 권한을 설정하면 사용자계정에도 원하는 권한 부여 가능.
   ROLE을 사용해 간편하게 권한을 부여할 수 있다.

<사용자계정>
 : 사용자 계정은 권한 부여 불가. 관리자계정에서 권한을 설정해야
   해당 객체에 접근하거나 해당 테이블에 SQL문을 사용할 수 있는 권한이 생긴다.
-- SAMPLE 계정은 SYSTEM계정이 아니라 권한 부여 불가
-- SYSTEM 계정에서 WITH ADMIN OPTION 권한 설정 후에는 가능

GRANT CREATE SYNONYM TO EMPLOYEE;

SELECT * FROM EMP;

CREATE PUBLIC SYNONYM DEPT FOR EMPLOYEE.DEPARTMENT;

SELECT * FROM DEPT;

-- <사용자 관리>
-- : 사용자의 계정과 암호 설정, 권한 부여

-- 오라클 데이터베이스를 설치하며 기본적으로 제공되는 계정
--1. SYS
--2. SYSTEM
--3. SCOTT(교육용 샘플 계정) : 11G부터 별도로 생성해야 함
--4. HR (샘플 계정) : 처음에는 잠겨져 있고, 11G에서는 없음

/* 보안을 위한 데이터베이스 관리자
   : 사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대해
   특정 권한을 가질 수 있게 하는 권한이 있음
   다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안 설정
   데이터베이스에 접근하는 사용자마다 서로 다른 권한과 롤(role)을 부여함
   
    권한 
    : 사용자가 특정 테이블에 접근할 수 있도록 하거나
    해당 테이블에 SQL(SELECT/INSERT/UPDATE/DELETE)문을
    사용할 수 있도록 제한을 두는 것
    
    시스템 권한
    : 데이터베이스 관리자가 가지고 있는 권한.
    CREATE USER(사용자 계정 만들기)
    DROP USER(사용자 계정 삭제)
    DROP ANY TABLE(임의의 테이블 삭제)
    QUERY REWRITE(함수 기반 인덱스 생성 권한)
    
    시스템 관리자가 사용자에게 부여하는 권한
    CREATE SESSION(데이터베이스에 접속)
    CREATE TABLE(테이블 생성)
    CREATE VIEW(뷰 생성)
    CREATE SEQUENCE(시퀀스 생성)
    CREATE PROCEDURE(프로시져 생성 권한)

*/
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;

GRANT CREATE SESSION TO SAMPLE;
GRANT CREATE TABLE TO SAMPLE;

PL SQL 헬로월드 콘솔 활성화 하기위한 명령어

SET SERVEROUTPUT ON;

문제해결 시나리오 : PL/SQL문

-- 선언부, 실행부, 예외처리부로 구성
-- 선언부 : DECLARE로 시작, 변수나 상수를 선언하는 부분
-- 실행부 : BEGIN으로 시작, 제어문, 반복문, 함수의 정의 등 로직 작성 -> 반드시 작성해야 함
-- 예외처리부 : EXCEPTION으로 시작, 예외처리 내용 작성

레퍼런스 변수타입

DECLARE
  EMP_ID EMPLOYEE.EMP_ID%TYPE;
  EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
  SELECT EMP_ID, EMP_NAME  -- 조회할 컬럼
    INTO EMP_ID, EMP_NAME  -- 대입할 변수
    FROM EMPLOYEE
   WHERE EMP_ID = '&EMP_ID';  -- & : 입력프롬프트 창 띄워라
   
   DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
   DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/

TOP-N 분석 ROWNUM / RANK / DENSE_RANK

--RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 다음 순위 계산
SELECT
       *
  FROM (SELECT
               EMP_NAME
             , SALARY
             , RANK() OVER(ORDER BY SALARY DESC) 순위
          FROM EMPLOYEE)
 WHERE 순위 <= 5;

--DENSE_RANK() 함수 : 중복되는 순위 이후의 등수를 이후 등수로 처리
SELECT
       EMP_NAME
     , SALARY
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

```java
-- 인라인뷰를 활용한 TOP-N 분석
-- ROWNUM은 행 번호를 의미함
-- WHERE절을 수행 할 때 붙여진다.
-- 실제 존재하지 않는 가상 컬럼
SELECT ROWNUM, EMP_NAME, SALARY 
  FROM EMPLOYEE
 ORDER BY SALARY;

SELECT ROWNUM, EMP_NAME, SALARY 
  FROM (SELECT *
          FROM EMPLOYEE
         ORDER BY SALARY DESC)
 WHERE ROWNUM <= 5;

-- 급여 평균 3위 안에 드는 부서의
-- 부서코드와 부서명, 평균 급여를 조회하세요
SELECT 
       ROWNUM
       ,부서코드
       ,부서명
       ,"평균 급여"
FROM (SELECT 
       DEPT_CODE 부서코드
       ,DEPT_TITLE 부서명
       ,TRUNC(AVG(SALARY),-5) "평균 급여"
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON( D.DEPT_ID = E.DEPT_CODE)
GROUP BY DEPT_CODE,DEPT_TITLE
ORDER BY AVG(SALARY) DESC)
WHERE ROWNUM <= 3;

-- 직원 정보에서 급여를 가장 많이 받는 순으로 이름, 급여, 순위 조회
-- RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 다음 순위 계산
-- DENSC_RANK() 함수 : 중복되는 순위 이후의 등수를 이후 등수로 처리

SELECT EMP_NAME
     , SALARY
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;
  

SELECT EMP_NAME
     , SALARY
     , RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

SELECT 
       *
  FROM (SELECT EMP_NAME
             , SALARY
             , RANK() OVER(ORDER BY SALARY DESC) 순위
          FROM EMPLOYEE)
 WHERE 순위 <= 5;
```

에러를 보여지게하는 SHOW ERRORS

PL/SQL은 오류를 안보여주기때문에 아래 명령어로 확인

SHOW ERRORS;

INSERT INTO 테이블명

INSERT INTO USER_NOCONS
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

PL SQL 구조 익명 블록

-- PL/SQL 블록(anonymous block) : 이름 없는 블록이라고 불리며 간단한 block 수행 시 사용됨
-- 프로시져(procedure) : 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형으로,
--                     단독으로 실행되거나 다른 프로시져나 다른 툴 등에 호출되어 실행됨
-- 함수(function) : procedure와 수행 결과가 유사하나 값 반환 여부에 따라 차이가 있음

---------------------------------------------------------------------------------
--             Anonymous Block          procedure                Function
--                (익명블록)              (프로시져)                 (함수)
-- ---------------------------------------------------------------------------------
-- 객체로 저장          x                     o                        o
-- 값 반환             x                     x                        o
-- 이름 지정 여부       x                     o                        o
-- 파라미터 사용        x                     o                         
-- 타 응용프로그래밍  
-- 호출 여부           x                     o                         o
-- ---------------------------------------------------------------------------------

FUNCTION (함수) RETURN 있음

FUNCTION (함수) : 프로시져와 사용 용도가 거의 비슷하다. 실행 결과를 되돌려받을 수 있다.(RETURN)

CREATE OR REPLACE FUNCTION BONUS_CALC(V_EMP EMPLOYEE.EMP_ID%TYPE) RETURN NUMBER
IS
  V_SAL EMPLOYEE.SALARY%TYPE;
  V_BONUS EMPLOYEE.BONUS%TYPE;
  CALC_SAL NUMBER;
BEGIN
  SELECT SALARY, NVL(BONUS, 0)
  INTO V_SAL, V_BONUS
  FROM EMPLOYEE
  WHERE EMP_ID = V_EMP;
  
  CALC_SAL := (V_SAL + (V_SAL + V_BONUS)) * 12;
  
  RETURN CALC_SAL;
END;
/

VARIABLE VAR_CALC NUMBER;

EXEC : VAR_CALC := BONUS_CALC('&EMP_ID');

SELECT EMP_ID, EMP_NAME, BONUS_CALC(EMP_ID)
FROM EMPLOYEE
WHERE BONUS_CALC(EMP_ID) > 30000000;

프로시져 객체(RETURN 없음)

프로시져(PROCEDURE) : PL/SQL문과 DML 구문을 저장하는 객체이다. 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 호출을 통해서 간단히 실행시키기 위한 목적으로 사용된다.

CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_DUP;

--프로시져 생성
CREATE OR REPLACE PROCEDURE DBL_ALL_EMP
IS
BEGIN
  DELETE FROM EMP_DUP;
  COMMIT;
END;
/

EXECUTE DBL_ALL_EMP;
EXEC DBL_ALL_EMP;

SELECT * FROM EMP_DUP;

-- 매개변수 있는 프로시져
CREATE OR REPLACE PROCEDURE DEL_EMP_ID(V_EMP_ID EMPLOYEE.EMP_ID%TYPE)
IS
BEGIN
  DELETE FROM EMPLOYEE
  WHERE EMP_ID = V_EMP_ID;
END;
/

EXEC DEL_EMP_ID('&EMP_ID');

SELECT * FROM EMPLOYEE;

ROLLBACK;

-- IN/OUT 매개변수 있는 프로시져
CREATE OR REPLACE PROCEDURE SELECT_EMP_ID(
  V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE,
  V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,
  V_SALARY OUT EMPLOYEE.SALARY%TYPE,
  V_BONUS OUT EMPLOYEE.BONUS%TYPE
)
IS
BEGIN
  SELECT EMP_NAME, SALARY, NVL(BONUS, 0)
  INTO V_EMP_NAME, V_SALARY, V_BONUS
  FROM EMPLOYEE
  WHERE EMP_ID = V_EMP_ID;
END;
/

VARIABLE VAR_EMP_NAME VARCHAR2(30);
VARIABLE VAR_SALARY NUMBER;
VARIABLE VAR_BONUS NUMBER;

EXEC SELECT_EMP_ID(200, :VAR_EMP_NAME, :VAR_SALARY, :VAR_BONUS);

PRINT VAR_EMP_NAME;
PRINT VAR_SALARY;
PRINT VAR_BONUS;

SET AUTOPRINT ON;

트리거

테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체

  • 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며,
  • 그 데이터의 행의 실제 값 제어 가능
  • 문장 트리거 : 트리거 사건에 의해 단 한번만 실행, 컬럼의 각 데이터 행 제어 불가능

CREATE OR REPLACE TRIGGER 트리거이름 AFTER INSERT ON 테이블명 BEGIN DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.'); END; /

  • ㅇ
  • -- 어떤 일의 시작점. 오라클에서의 트리거는 테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 -- 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체를 의미 -- 특정 DML 구문이 실행될 때 자동으로 동작하게 하는 구문 CREATE OR REPLACE TRIGGER TRG_01 AFTER INSERT ON EMPLOYEE BEGIN DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.'); -- EMPLOYEE 테이블에 INSERT됐을 때 실행 할 내용 END; / INSERT INTO EMPLOYEE VALUES(400, '길성춘', '650512-1151432', 'gil_sc@kh.or.kr', '01012345678', 'D5', 'J3', 'S5', 3000000, 0.1, 200, SYSDATE, NULL, DEFAULT); -- 유도속성(다른 연산에 의해 계산될 수 있는 중복될 수 있는 속성. EX)회원포인트)이 있는 구문에 많이 사용 -- 복잡하게 DML을 작성 할 경우 오류 발생 지점을 찾기 어려운 단점이 있다.(프로시져와 같은 단점) CREATE TABLE PRODUCT( PCODE NUMBER PRIMARY KEY, -- 제품코드, 인위적식별자 PNAME VARCHAR2(30), -- 제품명 BRANC VARCHAR2(30), -- 제조사 PRICE NUMBER, -- 가격 STOCK NUMBER DEFAULT 0 -- 재고량 ); CREATE TABLE PRO_DETAIL( DCODE NUMBER PRIMARY KEY, -- 이력발생번호, 인위적식별자 PCODE NUMBER, -- 제품코드 PDATE DATE, -- 이력발생일자 AMOUNT NUMBER, -- 이력발생수량 STATUS VARCHAR2(10) CHECK(STATUS IN ('입고', '출고')), -- 이력발생구분 FOREIGN KEY(PCODE) REFERENCES PRODUCT(PCODE) ); -- 카디널리티 : 관계대응수 -- 시퀀스 생성 CREATE SEQUENCE SEQ_PCODE; CREATE SEQUENCE SEQ_DCODE; INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시노트8', '샘숭', 900000, DEFAULT); INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '아이펀8', '애펄', 900000, DEFAULT); INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '대륙폰', '샤우미', 300000, DEFAULT); SELECT * FROM PRODUCT; SELECT * FROM PRO_DETAIL; -- 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며, 그 데이터의 행의 실제 값을 제어할 수 있다. -- 문장 트리거 : 트리거 사건에 의해 단 한번만 실행되며, 컬럼의 각 데이터 행을 제어할 수 없다. CREATE OR REPLACE TRIGGER TRG_02 AFTER INSERT ON PRO_DETAIL FOR EACH ROW -- 행 트리거로 만드는 옵션 BEGIN IF :NEW.STATUS = '입고' -- 새롭게 행이 INSERT될 때의 상태값(기존 행은 OLD.STATUS) THEN -- 행 트리거인 경우만 이렇게 사용 가능 UPDATE PRODUCT SET STOCK = STOCK + :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; IF :NEW.STATUS = '출고' THEN UPDATE PRODUCT SET STOCK = STOCK - :NEW.AMOUNT WHERE PCODE = :NEW.PCODE; END IF; END; / INSERT INTO PRO_DETAIL VALUES (SEQ_DCODE.NEXTVAL, 1, SYSDATE, 5, '입고'); SELECT * FROM PRO_DETAIL; SELECT * FROM PRODUCT; INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 2, SYSDATE, 11, '입고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 3, SYSDATE, 50, '입고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 3, SYSDATE, 11, '출고'); INSERT INTO PRO_DETAIL VALUES(SEQ_DCODE.NEXTVAL, 1, SYSDATE, 5, '출고');

이미 만들어져있는 테이블에 제약조건을 추가할때 ALTER TABLE 테이블명 ADD

  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 PRIMARY KEY (컬럼명);
  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 UNIQUE (컬럼명);
  • ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건이름 NOT NULL;
-- 컬럼에 제약조건 추가
ALTER TABLE DEPT_COPY2
ADD (CONSTRAINT PK_DEPT_ID2 PRIMARY KEY (DEPT_ID));
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT UN_DEPT_TITLE2 UNIQUE(DEPT_TITLE);
ALTER TABLE DEPT_COPY2
MODIFY LOCATION_ID CONSTRAINT NN_LID NOT NULL;

SET OPERATOR -> UNION, UNION ALL, MINUS, INTERSECT 의미

  • UNION
  • 중복되는 영역 제외하고 여러 개 쿼리 결과를 하나로 합치는 연산자
  • UNION ALL
  • 중복되는 영역을 포함시켜서 여러 개 쿼리 결과를 하나로 합치는 연산자
  • INTERSECT
  • 여러개의 결과에서 공통된 부분만 결과로 추출
  • MINUS
  • 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출

DB 오브젝트 정리

  • 테이블 (TABLE)
  • 데이터의 저장을 위한 객체로 DB의 기본적인 저장구조
  • 뷰 (VIEW)한개 이상의 테이블이나 다른 뷰를 기초로하여복잡한 access를 단순화하여 보안유지등을 위해 사용
  • 사용자가 원하는 형태의 데이터를 볼 수 있게 하고
  • 기본테이블을 기초로 한 논리적테이블(가상테이블)
  • 시퀀스 (SEQUENCE)시퀀스는 유일하고 연속적인 번호를 만드는 객체
  • 주로 pk를 생성하기 위해 사용
  • 순차적으로 primary key값을 자동으로 생성
  • 인덱스 (INDEX)인덱스는 DB를 보다 효율적이고 신속하게 검색할 수 있도록 도와주는 객체로서
  • 검색속도의 향상 및 데이터의 중복을 방지하기 위해 사용
  • 검색의 성능향상을 위해 생성
  • 패키지 (PACKAGE)패키지는 컴파일 과정을 거쳐 데이터베이스에 저장되며
  • 다른 프로그램에서 패키지의 항목을 참조하고 실행 가능
  • 수, 상수, 서브프로그램등의 항목을 묶어놓은 객체
  • 트리거 (TRIGGER)
    • 행 트리거 : 컬럼의 각각의 행의 데이터에 행 변화가 생길 때 마다 실행되며,
    • 그 데이터의 행의 실제 값 제어 가능
    • 문장 트리거 : 트리거 사건에 의해 단 한번만 실행, 컬럼의 각 데이터 행 제어 불가능
  • 테이블에 INSERT, UPDATE, DELETE 등의 DML에 의해 변경될 때 자동으로 수행할 내용을 정의하여 저장한 객체
  • 동의어 (SYNONYM)실질적으로 그 자체가 객체가 아니라 객체에 대한 직접적인 참조
  • 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말하며
  • 프로시져 (PROCEDURE)
  • PL/SQL 문과 DML 구문을 저장하는 객체 필요할 때 마다 복잡한 구문을 다시 입력할 필요 없이 호출을 통해서 간단히 실행시키기 위한 목적으로 사용
  • 함수 (FUNCTION) 파라미터로 이용해서 값 전달받음 정해진 작업을 수행한 후 결과 RETURN
  • 프로시져와 사용 용도가거의 비슷하다 실행 결과를 되돌려받을 수 있다 (RETURN값 O)
  • 사용자 (USER)기본적으로 사용자가 소유한 객체에 대한 모든 권한은 자동적으로 획득
  • 객체 소유자는 다른 사용자에게 특정 PRIVILEGES 부여 가능
  • 클러스터 (CLUSTER)물리적 디스크 I/O 효율성 증대, 조인 시 접근속도 향상
  • 공통 컬럼에 대한 동일 데이터 블록을 공유하는 테이블집합
  • 각 뭘 의미하는지 서술디비에서 우리가 배운 객체들이 뭔지 설명트리거사용자
  • -- 오라클에서의 객체 -- : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), -- 패키지(PACKAGE), 트리거(TRIGGER), 동의어(SYNONYM), -- 프로시져(PROCEDURE), 함수(FUNCTION), 사용자(USER)
  • 시노님=동의어

테이블의 정의 및 서브쿼리 종류

  1. 단일행 서브쿼리 : 서브쿼리의 조회 결과 값의 개수가 1개 일 때
  2. 다중행 서브쿼리 : 서브쿼리의 조회 결과 값의 행이 여러 개 일 때
  3. 다중열 서브쿼리: 서브쿼리의 조회 결과 칼럼의 갯수가 여러 개 일 때
  4. 다중행 다중열 서브쿼리 : 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 떄
  5. 상호연관 서브쿼리 : 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산 할 떄, 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀐다
  6. 스칼라 서브쿼리 : 상관쿼리 이면서 결과값이 한 개인 서브쿼리

관리자계정이란? 사용자계정이란?

사용자 관리

<관리자계정>
 : 사용자 관리 권한이 있음. 사용자의 계정과 암호 설정 및 권한 부여 가능.
   WITH ADMIN OPTION 권한을 설정하면 사용자계정에도 원하는 권한 부여 가능.
   ROLE을 사용해 간편하게 권한을 부여할 수 있다.

<사용자계정>
 : 사용자 계정은 권한 부여 불가. 관리자계정에서 권한을 설정해야
   해당 객체에 접근하거나 해당 테이블에 SQL문을 사용할 수 있는 권한이 생긴다.
-- SAMPLE 계정은 SYSTEM계정이 아니라 권한 부여 불가
-- SYSTEM 계정에서 WITH ADMIN OPTION 권한 설정 후에는 가능

GRANT CREATE SYNONYM TO EMPLOYEE;

SELECT * FROM EMP;

CREATE PUBLIC SYNONYM DEPT FOR EMPLOYEE.DEPARTMENT;

SELECT * FROM DEPT;

-- <사용자 관리>
-- : 사용자의 계정과 암호 설정, 권한 부여

-- 오라클 데이터베이스를 설치하며 기본적으로 제공되는 계정
--1. SYS
--2. SYSTEM
--3. SCOTT(교육용 샘플 계정) : 11G부터 별도로 생성해야 함
--4. HR (샘플 계정) : 처음에는 잠겨져 있고, 11G에서는 없음

/* 보안을 위한 데이터베이스 관리자
   : 사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대해
   특정 권한을 가질 수 있게 하는 권한이 있음
   다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안 설정
   데이터베이스에 접근하는 사용자마다 서로 다른 권한과 롤(role)을 부여함
   
    권한 
    : 사용자가 특정 테이블에 접근할 수 있도록 하거나
    해당 테이블에 SQL(SELECT/INSERT/UPDATE/DELETE)문을
    사용할 수 있도록 제한을 두는 것
    
    시스템 권한
    : 데이터베이스 관리자가 가지고 있는 권한.
    CREATE USER(사용자 계정 만들기)
    DROP USER(사용자 계정 삭제)
    DROP ANY TABLE(임의의 테이블 삭제)
    QUERY REWRITE(함수 기반 인덱스 생성 권한)
    
    시스템 관리자가 사용자에게 부여하는 권한
    CREATE SESSION(데이터베이스에 접속)
    CREATE TABLE(테이블 생성)
    CREATE VIEW(뷰 생성)
    CREATE SEQUENCE(시퀀스 생성)
    CREATE PROCEDURE(프로시져 생성 권한)

*/
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;

GRANT CREATE SESSION TO SAMPLE;
GRANT CREATE TABLE TO SAMPLE;

 

반응형
저작자표시 비영리 동일조건

'Computer Science > Database' 카테고리의 다른 글

[DB] 관계형 모델  (0) 2022.03.07
[DB] 데이터베이스 모델링  (0) 2022.03.07
[DB] 데이터베이스의 특징  (0) 2022.03.07

관련글

댓글

티스토리툴바