[SQLD] 정리 3 (DCL / 절차형 SQL)

DCL

유저를 생성하고 권한을 제어할 수 있는 명령어

* Oracle

아이디와 비밀번호 방식으로 데이터 베이스에 접속.

유저마다 스키마에 오브젝트 생성 등의 권한을 부여 받음.

* SQL Server

방식 1 ) Microsoft Windows 인증방식으로 Windows에 대한 로그인 정보를 가지고 접속.

Windows에서 사용자 ID를 확인.

방식 2) Windows 인증방식, Oracle 인증 방식 둘다 가능.

Oracle 은 유저마다 스키마에 오브젝트 생성 권한을 가진다.

SQL Server 유저는 단지 스키마에 대한 권한만을 가진다.

스키마가 테이블과 같은 오브젝트를 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지는 것이다.

* 공통

PROCEDURE 는 EXECUTE 권한만 있음

테이블에만 INDEX 가능 ( VIEW, SEQUESNCE, PROCEDURE는 인덱스 불가능)

VIEW는 ALTER 불가능

ROLE

관리해야 할 유저가 점점 늘어 나고 자주 변경되는 상황, 많은 유저들과 권한들 사이에서 중개 역할

데이터베이스 관리자는 ROLE을 생성하고， ROLE에 각종 권한들을 부여한 후

ROLE을 다른 ROLE이나 유저에게 부여할 수 있다

빠르고 정확하게 필요한 권한을 부여할 수 있게 된다.

CONNECT는 CREATE SESSION과 같은 로그인 권한이 포함

RESOURCE는 CREATE TABLE과 같은 오브젝트의 생성 권한이 포함

유저를 생성할 때 기본 권한 CONNECT와 RESOURCE ROLE

DROP USER는 유저를 삭제하는 명령어

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

SQL Server에서는 Oracle과 같이 Role을 자주 사용하지 않음

인스턴스 수준을 요구하는 로그인에는 서버 수준 역할부여

데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여한다

PL/SQL

* PL/SQL은 Block 구조로 되어 있어 각 기능별로 모듈화가 가능하다

* 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다

* IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다

* DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다

* PL/SQL은 Oracle에 내정되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다

* PL/SQL은 응용 프로그램의 성능을 향상시킨다

* PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일수 있다

* PL/SQL Block 프로그램을 입력받으면 SQL 문장과 프로그램 문장을 구분하여 처리.

* 프로그램 문장은 PL/SQL 엔진이 처리.

SQL 문장은 Oracle 서버의 SQL Statement Executor가 실행하도록 작업을 분리하여 처리.

PL : EXCEPTION

T-SQL : ERROR

T-SQL

* 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @ 이라는 지역변수가 있다.

* 지역변수는 시용자가 자신의 연결 시간 동안만 사용히기 위해 만들어지는 변수

* 전역변수는 이미 SQL서버에 내정된 값

* 데이터 유형 (Data Type) 을 제공한다.

  즉 int, float, varchar 등의 자료형을 의미한다.

* 연산자(Operator)

  산술연산자( +, -, *, /)와 비교연산자(=， <. > , <>) 논리연신자(and, or, not) 사용이 가능

* 흐름 제어 기능

  IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.

* 주석 기능

  한줄 주석 : -- 뒤의 내용은 주석

  범위 주석 : /* 내용 */ 형태를 사용히며, 여러 줄도 가능

WITH 부분에 지정할 수 있는 옵션은 3가지가 있다.

1) RECOMPILE

데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 된다

데이터베이스 엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대힌 계획을 삭제하려 할 때 RECOMPILE

쿼리 힌트를 사용한다.

2) ENCRYPTION

CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다. 변조된 출력은 SQL

Server의 카탈로그 뷰 어디에서도 직접 표시되지 않는다. 원본을 볼 수 있는 방법이 없기 때문에 반드시

원본은 백업을 해두어 야 한다.

3) EXECUTE AS

해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다

<주의사항>

* PL/SQL에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 히며， 그 결과 역시 반드시

하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러를 발생시킨다. T-SQL에서는 결과 값이 없어도

에러가 발생하지 않는다

* T-SQL을 비롯하여 일반적으로 대입 연산자는 =을 시용하지만 PL/SQL에서는 :=를 사용한다

* Function 이 Procedure와 다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것이다

Trigger

절차형 SQL을 이용해서 PROCEDURE, TRIGGER, USER DEFINED FUNCTION을 만들 수 있다