SQL 공부 일지 11일차 230830

## 공부정리

과목2 2장 SQL 활용 4편

7) DCL

8) 절차형 SQL

 

 

 

#7 DCL

1 DCL

- 유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요

- GRANT : 권한 부여 (SQL -> GRANT 권한 ON 오브젝트 TO 유저명;)

- REVOKE : 권한 제거 (SQL -> REVOKE 권한 ON 오프젝트 TO 유저명;)

 

 

2 권한

- SELECT, UPDATE, INSERT, DELETE, ALTER, ALL : DML 관련 권한

- REFFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한

- INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

 

 

3 Oracle 기본 유저 종류

유저	역할
SCOTT	Oracle 테스트용 샘플 유저 Default 패스워드 : TIGER
SYS	DBA ROLE을 부여받은 유저
SYSTEM	데이터베이스의 모든 시스템 권한을 부여받은 DBA 유저 Oracle 설치 완료 시에 패스워드 설정

 

 

4 ROLE을 이용한 권한 부여

• 많은 데이터베이스에서 유저들관 권한들 사이에서 중개 역할을 하는 ROLE을 제공
• 권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여
• 시스템 권한, 오브젝트 권한 모두 부여 가능
• ROLE은 유저에게 직접 부여하거나 다른 ROLE에 포함되어 유저에게 주여될 수 있다

Oracle>>
CONN SYSTEM/MANAGER
CREATE ROLE 롤이름;
GRANT 부여권한, 부여권한, ...TO 롤이름;
GRANT 롤이름 TO 유저명;

 

 

5 Oracle에서 제공하는 ROLE 종류

- CONNECT : CREATE SESSION 과 같은 로그인 권한

- RESOURCE : CREATE TABLE과 같은 오브젝트(리소스) 생성 권한

 

 

6 유저 삭제 명령어와 권한

- DROP USER 유저명 CASCADE;

- CASCADE 옵션은 해당 유저가 생성한 오브젝트를 먼저 삭제 후 유저를 삭제

 

 

7 SQL Server에서 서버 수준 역할명

- 서버 수준 역할명(SQL Server 사례)

이미지 출처 : https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=352

 

 

7 SQL Server에서 데이터베이스 수준 역할명

- 데이터베이스 수준 역할명(SQL Server 사례)

이미지 출처 : https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=352

 

 

 

#8 절차형 SQL

1 절차형 SQL

- 일반적인 개발 언어처럼 절차 지향적인 프로그램을 작성할 수 있도록 DBMS 벤더별로 절차별 SQL 제공

- SQL문의 연속적인 실행이나 조건에 다른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성

 

 

2 PL/SQL (Oracle)

- Oracle의 PL/SQL은 Block 구조로 되어 있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 철파형 언어(IF, LOOP)

 등을 사용할 수 있음

- 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어

 

이미지 출처 : https://hoon93.tistory.com/38

 

- PL/SQL 기본 문법

CREATE OR REPLACE Procedure 프로시저명 (argument1 mode data_type1, ...) IS AS ...
BEGIN .....
EXCEPTION ....
END; /

 

- 생성된 프로시저 삭제

DROP Procedure 프로시저명;

 

 

3 T-SQL (SQL Server)

- T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어

- MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완

 

① 기능

• 변수 선언 기능 : @@ - 전역변수(시스템함수), @ - 지역변수
• 데이터 타입을 제공 (INT,FLOAT,VARCHAR 등의 자료형)
• 산술연산자, 비교연산자, 논리연산자 사용이 가능
• 흐름 제어 기능 : IF-ELSE 와 WHILE, CASE-THEN 사용이 가능하다.
• 주석 기능 : 한줄 주석은 -- 뒤의 내용 주석, 범위 주석은 /* 내용 */ 형태를 사용하며 여러 줄도 가능

 

② T-SQL 기본 문법

CREATE Procedure 스키마명.프로시저명 @parameter1 data_type 1 mode, ...
WITH AS ...
BEGIN ...
ERROR 처리 ...
END;

DROP Procedure 스키마명.프로시저명

 

 

4 프로시저 (Procedure)

- 주로 DML을 사용해 주기적으로 진행해야 되는 작업을 저장

- 별도의 호출을 통해 실행

- CREATE OR REPLACE PROCEDURE 문으로 프로시저를 생성

 

* OR REPLACE는 기존에 같은 이름의 프로시저가 있으면 새로운 내용으로 덮어쓴다.

- 작업의 결과를 DB에 저장 (트랜잭션 처리)

- BEFIN ~ END문 사이에 작업 영역 생성(1~3은 섞어서 사용하고, 4번만 마지막에 작성)

-> 1) 조건/반복 영역

-> 2) SQL을 사용해 데이터 관리하는 영역

-> 3) 예외 처리 영역

-> 4) 트랜잭션 영역(작업 결과를 실제로 반영하거나 취소하는 영역)

 

 

5 사용자 정의 함수(User Defined Fuction)

- 절차형 SQL을 로직과 함께 DB내에 저장해 놓은 명령문 집합

- REUTRN을 통해 반드시 하나의 값 반환(↔프로시저는 DB에 저장)

 

 

6 트리거(Trigger)

• DML문이 수행되었을 때 자동으로 동작하는 프로그램(↔프로시저는 EXECUTE로 실행)
• DCL과 TCL 사용 불가(↔프로시저는 사용 가능)
• 데이터의 무결성과 일관성을 위해서 사용
• Trigger는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용

 

 

7 프로시저와 트리거의 차이

- 프로시저는 BEGIN~END절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용

- 데이터베이스 트리거는 BEGIN~END 절 내에 사용할 수 없다.

 

프로시저	트리거
CREATE Procedure 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨