[DB] 계정, PL(Procedure Language)/SQL

* 계정 

- User : 사용자를 의미한다. 사용자는 데이터를 Oracle Server에 접속해서 가져오든(select), 변경하든(DML), 데이터에 어떤 영향을 줄 수 있다.

- Schema : 특정 사용자가 만들어 놓은 모든 Object의 집합을 말한다. table, index, view, constraint, trigger, dblink, synonym,sequence등을 다 통들어서 User Schema라고 한다.

[참고] 원래 Schema와 username은 다른의미지만 일반적으로는 서로 같이 혼용해서 많이 사용한다.

- 관리자 계성만이 생성할 수 있으며, 생성된 계정은 아무런 권한도 없다.

- 문법

계정 생성

create user 계정명 identified by password;

권한 설정

grant 권한 to 계정명[모든 사용자에게 권한을 할당시 public];

권한 : 접속 : connect / DB사용 : resource / DBA권한 : DBA

권한 회수

revoke 권한,,, from 계정명[모든 사용자에게 권한을 회수시 public];

계정 삭제

drop user 계정명 cascade ...

------------------------------------------

-- 계정

------------------------------------------

-- user01.dbf

 

-- *계정 생성   

-- 현재 계정 : scott

create user jinki identified by rabbit;

-- └error msg : 권한이 불충분합니다

/*

현재 관리자 계정이 아니므로 계정 생성 불가

sysdba로 접속해서 작업해야한다.

*/

 

 

 

사용자를 생성하긴 했지만 접속권한은 주지 않았으므로 로그인이 거부된다.

 

 

 

사용자에게 권한부여 후 접속이 된다.

그러나 DB사용권한은 없으므로 쿼리는 사용하지 못한다.

 

 

 

DB사용 권한 부여 후 create, insert...등의 쿼리문이 수행된다.

 

 

 

데이터가 들어있는 상태에서 사용자 삭제를 하려하니 거부된다.

cascade 옵션을 설정하고 삭제

 

 

 

사용자 삭제 후 접속하면 이와같은 에러메시지를 확인할 수 있다.

 

 

* PL(Procedure Language)/SQL

- 오라클에서 제공하는 프로그래밍 언어이다.

- 기존의 쿼리(sql)는 저장성이 없다. 따라서 같은 쿼리를 일일히 새로 작성해주어야 한다. 그러나 PL/SQL을 사용하면 재작성하지 않아도 된다.(편의성 제공)

- PL/SQL은 제어구조를 가지고 있기 때문에 언어의 기능을 제공한다.

- Database에서 언어의 기능(연산, 제어구조)을 사용하여 쿼리문을 수행할 때 사용한다.

- 컴파일(@경로|파일명.sql) 즉시 수행된다.

- 쿼리문을 사용할 수 있다.(selete 사용 주의)

- 블록구조를 가지고 있다. 기본적인 구성은 선언부(declare), 실행부(begin, 반드시 존재해야한다), 예외처리부(exception, 생략가능)로 구성되어 있다. 블록안에 블록을 포함할 수 있는데 포함된 블록을 Nested Block(중첩블록)이라 부르기도 한다. 블록의 유형에는 크게 Anonymous PL/SQL Block(익명 블록)과 Stored PL/SQL Block(저장된 블록)이 있다. 익명 블록은 주로 일회성으로 사용할 경우 많이 사용되고, 저장된 블록은 서버에 파싱해서 놓고 주기적으로 반복해서 사용할 경우에 많이 만들게 된다. Stored PL/SQL Block은 다른 용어로 서브 프로그램 또는 프로그램 단위라고도 하며, 스키마를 구성하는 오브젝트로서 파싱된 후 오라클 서버 내부에 저장되거나 오라클 툴 안에 라이브러리 형태로 저장되어 있다.

- 문법

|sqlplus문

declare

변수 선언, cursor 선언

begin

코드 작성

ex)

dbms_output.put_line(메시지);

└ 실행전 set serveroutput on을 설정해주어야 한다. 이 문장은 sqlplus문장이므로 컴파일 전에 설정해주어야 한다. 또한 일회성의 성격을 가진다.

end;

/

- declare, begin, exception과 같은 예약어들은 ';'으로 끝나지 않지만 나머지 명령어들은 SQL문장처럼 ';'으로 끝난다.

- 예약어는 식별자명으로 사용될 수 없으나, Alias로는 사용될 수 있다. 즉, 이중부호("")를 함께 사용할 수는 있다라는 뜻이다.(ex. "TABLE")

- 리터럴(문자, 날짜)은 단일 인용부호('')로 표시해야 하며 널 값은 NULL상수로 기술해야한다.

- PL/SQL 블록 내의 명령(수식)에서는 오라클 함수를 사용할 수 있으나 그룹 함수와 DECODE 함수는 SQL문장에 포함되어야만 사용될 수 있다. 만약 다른 경우에 그룹 함수와 DECODE함수를 사용할 경우 에러가 발생하면 PL/SQL에서는 에러(EXCEPTION)처리와 관련된 함수에는 SQLCODE함수와 SQLERRM함수가 별도로 존재한다.

 

* 변수(variable)

- 선언

- declare ~ begin 사이에서 값을 임시 저장하기 위한 목적으로 선언한다.

- 문법

1. 기본 생성

변수명(영어소문자) 데이터형 (크기);

ex)

name varchar2(10 or 10byte);

 

2. 실제 테이블명 컬럼 데이터형, 크기를 참조하여 생성(테이블에 접근해야 하기 때문에 속도가 느리고 alter등의 명령어들로 테이블과 함께 변경된다. 그러나 그 변경된 사실을 알 수 없다. 또한 데이터타입 대신 테이블명.컬럼명이 들어가므로 직관적으로 알기 어렵다. 관리자가 모든 사실을 알고 있어야 하는 단점이 있다.)

테이블명.컬럼명%type

ex)

ename emp.ename%type;

 

3. 테이블의 모든 컬럼을 참조하여 생성(변수명 하나로 여러 값을 저장할 수 있다.)]

테이블명%rowtype;

ex)

temp dept%rowtype;

사용 변수명.컬럼명

ex) temp.deptno

 

4.  cursor를 사용할 때

- 값 할당

- begin ~ end 사이에서 사용한다.

- 문법

변수명 := 값;

- 값 사용

- 출력, 재할당, 연산

 

* DataType

number(총 자릿수, 소수 자릿수) : 정수, 실수(signed한 값)

binary_integer : 정수만 저장(-2147483647 ~ +2147483647)

long : 2Gbyte 숫자

char : 고정길이(최대 2000byte)

varchar2 : 가변길이(최대 4000byte)

clob : 4Gbyte 문자열

blob : 파일 저장(4Gbyte)

 

* 연산자

- 연산시 사용하는 예약된 부호들

산술 : +, -, *, /, mod

관계 : >, <, >=, <=, =, !=(<>)

논리 : and, or, not

문자열 : like, in

null : is null, is not null

 

* 외부값 받기

- declare 위에서 선언

accept 변수명 prompt '메시지'

...

ex)

num number(크기) := &변수명; (구 -> 입력값 변환 -> 신)

사용

&변수명(사용자가 입력한 값으로 변환)

검증옵션 끄기

set verify off

구 => 신으로 변경되는 결과를 출력하지 않는다. 정수는 그냥 사용하며 문자열은 '로 묶어서 사용한다.

 

------------------------------------------

-- PL/SQL

------------------------------------------

PL1002_1.sql

 

set serveroutput on <-(sqlplus문. sqlplus에서 직접 사용할 수도 있고 이 곳에 적어줄 수도 있다.)

-- set serveroutput on : 화면 출력 기능을 활성화시킨다.(PL/SQL은 처리된 문장의 결과를 화면에 출력하지 않는다.)

 

declare

 

begin

dbms_output.put_line('안녕 o(^▽^O)o(^^O;;)');

end;

/

 

 

 

-------------------------------------------------------------------------------------

 

 

PL1002_2.sql

 

-- sqlplus문장이기 때문에 문장 끝에 ';'넣지 않는다.

set serveroutput on

-- declare을 anonymous block이라고 한다.

-- 선언부 시작

declare

-- 1. 변수선언(데이터베이스에서 수행된 결과 값을 저장할 변수 선언)   

-- 1-1. 데이터형을 직접사용

name varchar2(15 byte);    

-- 1-2. 컬럼 데이터형을 참조하여 사용

addr student.address%type;

age number(3);

-- 실행부 시작

begin

-- 2. 값 할당

name := '우주미남';

addr := '광명';

age := 26;

 

-- 3. 값 사용(이 예제에서는 출력을 통해 값 사용)

dbms_output.put_line('이름 : '||name||', 주소 : '||addr||', 나이 : '||age||', 태어난 해 : '||(to_char(sysdate,'yyyy')-age+1));

end;

/

-- '/' : 작성된 PL/SQL 블록을 실행시킨다.

 

-------------------------------------------------------------------------------------

 

 

-------------------------------------------- 외부값 받기와 연산자의 사용------------------------------------------

PL1002_3.sql [소스 결과 최종 화면은 그림 3을 참고]

 

set serveroutput on -- *검증 옵션 끄기(검증 옵션인 구신의 출력을 끄겠다라는 명령어. 그림 0 참고)set verify offaccept name prompt '이름 : 'accept year prompt '태어난 해 : 'declare -- 그림1. 변수명만 지정하면 프롬프트창에서 사용자가 ''를 붙여서 입력해야 한다. -- '' 없이 입력하면 에러메시지를 출력한다.  -- 사용자에게 값을 입력 받아서 변수에 할당 할 때는 &(앰퍼센트)기호를 사용한다.-- name varchar2(30) := &name;  -- 그림2. 선언시 ''를 붙여서 선언할 수 있다. 이 경우 프롬프트 창에서 사용자가 ''를 따로 명시하지 않아도 된다.                    name varchar2(30) := '&name'; -- year number(4) := &year;begin                      dbms_output.put_line('입력 이름 : '||name||', 태어난 해 : '||year); -- 입력된 년도를 가지고 나이 계산 dbms_output.put_line('나이 : '||(to_char(sysdate,'yyyy')-year+1)); -- '나이 : '||to_char(sysdate,'yyyy')가 먼저 계산 되므로 '나이 : 입력값'-year+1의 상태가 된다. 따라서 산술연산되지 않는다. -- 그러므로 괄호()를 통해 우선순위를 정해주어야 한다.    dbms_output.put_line(mod(year,12)); -- 11 양띠, 10 말띠, 9 뱀띠, 8 용띠, 7 토끼, 6 호랑이띠, 5 소띠, 4 쥐띠, 3 돼지띠, 2 개띠, 1 닭띠, 0 원숭이띠end;/

 

 

[그림 2] 검증 옵션의 여부 차이

 

[그림 1] 문자열은 ''으로 감싸주어야 한다.

 

 

[그림 2] 선언시 ''를 붙여주면 입력시 ''을 붙여주지 않아도 된다.

 

 

[그림 3] 선언에 ''를 붙여준 상태. 나이계산과 띠계산을 해준 예제이다.

 

기본문법(데이터형, 연산자, 제어문), 쿼리문 수행(insert, update, delete, select), exception, cursor, , 외부변수, procedure, package, tirgger, 백업, 복구