오라클 주요 간단 정리

 

LANGUAGE의 종류
유형	명령문
DQL (Data Query Language)	SELECT (데이터 검색시 사용)
DML (Data Manipulation Language)	INSERT(데이터입력) UPDATE(수정) DELETE(삭제)
DDL (Data Definition Language)	CREATE(DB객체생성) ALTER(변경) DROP(삭제) RENAME(이름 변경) TRUNCATE(저장공간 삭제)
TCL (Transaction Control Language)	COMMIT(트랜잭션의 정상적인 종료처리) ROLLBACK(트랜잭션 취소) SAVEPOINT(트랜잭션내에 임시 저장점 설정)
DCL (Data Control Lanugage)	GRANT(DB에 대한 일련의 권한 부여) REVOKE(DB에 대한 일련의 권한 취소)

 

  

접속 및 시작

 

C:\sqlplus ID/PW

C:\sqlplus sys/PW as sysdba

Sql> startup

 

일반 사용자 계정이 SYSDBA권한으로 접속하는 것을 방지하기 위해서는 OS의 사용자 그룹 중 'ORACLE ~' 그룹에서 Administrator를 제거해주는 것이 좋다.

 

테이블의 데이터 구조 살펴보기 (컬럼이름, 데이터 TYPE, 길이와 NULL 허용 유무….)

DESC [RIBE] 테이블명

ALIAS(별칭)

 

Concatenation 연산자 (' || ')

Select 문 내부에 ' || ' 를 사용해서 표현,

DISTINCT Keyword

특정 키워드에 대해서 중복값을 제거하고, 한번만 출력

SET

시스템의 변수 설정을 위한 명령어 SET [시스템_변수명] [값]

  

SQL 주요 함수

숫자함수 ROUND() 반올림 함수 Round(A,B) A값을 B자리 출력 ex)ROUND(45.293,2)=>45.29 TRUNC() 반내림 함수 MOD() 나머지를 구하는 함수   문자 처리 함수 UPPER 대문자로 변환 LOWER 소문자로 변환 INITCAP 이니셜만 대문자로 변환 LENGTH 무낮열의 길이를 알려준다. INSTR 특정문자가 출현하는 위치를 알려준다. SUBSTR 문자의 일부분을 추출한다. LPAD 오른쪽 정렬 후 왼쪽에 생긴 빈 공백에 특정문자를 채운다. RPAD 왼쪽 정렬 후 오른쪽에 생긴 빈 공백에 특정문자를 채운다. LTRIM 왼쪽에서 특정 문자를 삭제한다. RTRIM 오른쪽에서 특정 문자를 삭제한다.   날짜 관련 함수 MONTHS_BETWEEN 날짜와 날짜 사이의 개월을 계산 숫자 ADD_MOINTHS 날짜에 개월을 더한 날짜 계산 날짜 NEXT_DAY 날짜 후의 첫 요일의 날짜를 계산 날짜 LAST_DAY 월의 마지막 날짜를 계산 날짜 ROUND 날짜를 반올림 날짜 TRUNC 날짜를 절삭 날짜 SYSDATE 시스템에 저장된 현재 날짜를 반환 날짜   형 변환 함수   ß-- TO_NUMBER ß--- TO_CHAR                                                                                        ----> TO_CHAR ----> TO_DATE     NULL 변환함수 NVL DECODE   CASE 함수 CASE [표현식] WHEN [조건1] THEN [결과 1] WHEN [조건2] THEN [결과 2] ELSE 결과 N   그룹함수 COUNT 행의 총 개수를 출력 SUM 해당 열의 총 행의 합계 AVG 해당 열의 총 행의 평균 MIN 해당 열의 총 행 중에 최소 값 MAX 해당 열의 총 행 중에 최대 값 **그룹함수는 테이블에 아무리 행이 많아도 단 한 개의 결과 값만을 산출한다. 이 때 어떤 컬럼을 기준으로 그룹함수를 적용해 줄 지[Group by]}절을 통해 기술해 주어야 한다. 또한, [HAVING (조건절)] 절을 사용하여 특정 행들만 출력하도록 할 수 있다.

 

JOIN Vs UNION

JOIN 두 개 이상의 테이블에서 Column과 Column 들을 연결 UNION 두 개 이상의 테이블에서 Low와 Low 들을 연결   JOIN EQUI JOIN 조인 대사잉 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성. Ex) From emp, dept Where emp.deptno = dept.deptno 컬럼명의 모호성을 위해 SELECT 절에서 컬럼명 앞에 테이블명을 명시해주는 것이 좋다. (ex. Select Emp.ename …) NON-EQUI JOIN 조인 조건에 특정 범위 내에 있는지를 조사하기 위해 where절에 조인 조건을 ' = ' 연산자 이외의 비교 연산자를 사용. Ex) From emp e, salgrade s Where e.sal >= s.losal and e.sal <=s.hisal; SELF-JOIN 하나의 테이블을 마치 두 개의 테이블처럼 인식시켜 스스로의 테이블에서 조인을 실행한다. 이때 별칭을 사용하여 쉽게 처리한다. (자신의 테이블만으로는 원하는 결과를 도출할 수 없으나, 자신과 같은 테이블이 두개 존재한다는 가정에서 질의가 가능한 경우 셀프조인을 사용) Ex) From emp A, emp B Where A.mgr=B.empno; OUTER-JOIN A 테이블에는 존재하지만, B 테이블에는 존재하지 않아 조인 조건이 만족하지 않아서 결과값에서 출력되지 않는 값을 출력하고자 할 때 사용하며 ' (+) ' 연산자를 사용하여 표기한다. Ex) From emp A, emp B Where A.mgr=B.empno(+) A=B의 조건을 만족하는 것 이외에 A테이블의 내용을 모두 출력.

 

SUB_QUERY (하나의 테이블에서 검색한 결과를 다른 테이블에 전달하여 새로운 결과를 검색하는 기능. )

단일 행 SUB_QUERY 하나의 행만 검색하여 그 결과를 메인 쿼리에 보내는 것. 다중 행 SUB_QUERY 서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리로 반드시 다중 행 연산자와 함께 사용하여야 한다.   IN 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 True ANY,SOME 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 True ALL 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 True EXIST 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 True

 

테이블의 생성 및 변경, 삭제 (DDL)

테이블의 종류 Data Dictionary Table DB를 생성할 때 기본적으로 만들어지는 관리 용도의 테이블 User Definition Table 사용자의 데이터를 저장하고 관리하기 위해 사용하는 테이블   테이블의 생성   CREATE TABLE [테이블명] (컬럼명 자료형)   자료형 사용자 정의     내장 스칼라 CHAR(N),NCHAR(N) VARCHAR2(N) NVARCHAR2(N) NUMBER(P,S) DATE RAW(N) BLOB,CLOB NCLOB,FILE LONG,LONG RAW ROWID 컬렉션 VARRAY TABLE 관계 ref   테이블의 구조만 복사하기 CREATE TABLE [테이블명A] AS SELECT * FROM [테이블명B] WHERE 1=0; 테이블의 모든 행 제거 TRUNCATE TABLE [테이블명] ;   테이블의 구조 변경 ALTER TABLE [테이블명] [ADD/DROP/MODIFY] [변경할 내용]   UNUSED 옵션 : 실제 해당 컬럼을 삭제하는 작업은 DB에 엄청난 Overhead를 가져온다. 이는 DB의 사용빈도가 낮은 시간에 수행하고, 우선적으로 UNMOUNT와 가은 개념으로 논리적으로 해당 컬럼의 사용을 제한하도록 하는 옵션

 

DATA DICTIONARY & DATA DICTIONARY VIEW

기본적으로 RDBMS는 데이터의 저장을 위해 'RELATION' 이라 불리는 테이블에 사용자의 모든 데이터를 저장한다. 뿐만 아니라 시스템 데이터(DB 자신의 이름, 생성시각, 접근할 수 있는 사용자 및 암호, 각 계정이 가지고 있는 테이블의 이름…)도 저장해야 하는데, 이들 정보를 바로 Data Dictionary 라 하고, 저장 장소는 SYSTEM TABLE이다.   DATA DICTIONARY는 사용자가 임의로 내용을 직접 수정 및 삭제가 불가능하며, 이 정보를 보기 위해서는 Data Dictionary View를 통해야 한다.   Data Dictionary View의 종류 DBA_xxxx DB Administror만이 접근 가능한 객체 등의 정보 조회 ALL_xxxx 자신 계정 소유 또는 권한을 부여 받은 객체 등에 관한 정보 조회 USER_xxxx 자신의 계정이 소유한 객체 등에 관한 정보 조회   Ex)   DBA_SEQUENCES DB에 있는 모든 시퀀스 정보 조회 DBA_INDEX DB에 있는 모든 인덱스 정보 조회 DBA_VIEWS DB에 있는 모든 뷰 정보 조회   DESC user_tables; 자신이 소유한 모든 테이블의 정보 조회 USER_SEQUENCES 계정이 소유한 시퀀스의 정보 조회 USER_INDEXES 계정이 소유한 인덱스 정보 조회 USER_VIEWS 계정이 소유한 뷰 정보 조회   ALL_SEQUENCES 현재 계정이 접근 가능한 시퀀스 정보 조회 ALL_INDEX 현재 계정이 접근 가능한 인덱스 정보 조회 ALL_VIEWS 현재 계정이 접근 가능한 뷰 정보 조회

 

 

테이블에 데이터 추가, 수정, 삭제하기 (DML)

INSERT INSERT INTO [테이블명] (컬럼명1,컬럼명2,컬럼명3,컬럼명n) VALUES(컬럼1의 내용,컬럼2의 내용, 컬럼3의 내용,컬럼명N의 내용) INSERT INTO [테이블명] VALUES(컬럼1의 내용,컬럼2의 내용, 컬럼3의 내용,컬럼명N의 내용) --컬럼명의 생략이 가능하다.   주요 연산자 ' & ' 테이블명이나 컬럼명앞에 &를 붙이게 되면 사용자에게 입력값을 묻는다. ' && ' 처음 입력받은 치환 값에 대해선 묻지만, 같은 값이 다시 입력될 시에는 다시묻지 않는다.(메모리에 처음 입력받았던 치환값을 기억)   // INSERT ALL 구문 추후 실습에서 사용했던 내용을 기반으로 채워넣기   UPDATE UPDATE [테이블명] SET [컬럼명=변경할 값] Where [조건절]   DELETE DELETE FROM [테이블명] Where [조건절]   MERGE(합병) 구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능. MERGE명령을 수행하기 위해서 수행하는 테이블에 기존에 존재하는 행이 있다면 새로운 값으로 갱신(update)되고, 존재하지 않으면 새로운 행으로 추가(insert)된다.   MERGE INTO [테이블 A] USING [테이블 B] On [조건식] 테이블 A와 테이블 B의 조건을 검사하여 일치하면 테이블 B의 내용을 테이블 A로 병합한다.

 

TRANSACTION

데이터 처리의 한 단위로서, 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 것. 하나의 트랜잭션은 'All – Or – Nothing' 방식으로 처리된다. (여러 개의 명령어의 집합이 정상적으로 처리되면 정상 종료하도록 하고 여러 개의 명령어 중에서 하나의 명령어라도 잘못되었다면 전체를 취소) 위에서 살펴보았듯 TCL(Transaction Control Language)명령어에는 Commit, Rollback, Savepoint 등이 있다.   ** DDL(Create, Alter, Drop, Rename, Truncate)문은 Auto -Commit이므로 Rollback이 이루어지지 않는다.

 

데이터 읽기 (일관성과 락(LOCK))

정의 어느 시점에서든지 Oracle User들은 일관적으로 동일한 데이터를 읽을 수 있어야 한다.   Ex) LOCK의 발생 User A User B   Select * from em1 Where empno=7788;     Delete from em1 Where empno=7788 Update em1 Set sal=200 Where empno=7788     Rollback; 종료   [1. 사용자 A가 사원번호 '7788'인 사원의 정보를 열람한다.] [2. 사용자 B가 사원번호 '7788'인 사원을 삭제한다.] [3. 사용자 A가 사원번호 '7788'인 사원의 sal 값을 200으로 변경하려 한다. 하지만 사용자 B가 '7788' 사원을 지웠기 때문에, Lock에 걸려 무한대기('Unlimited waiting')상태에 걸린다.] [4. 만일 사용자 B가 Rollback을 통해 '2'에서 실행한 명령을 취소하게 되면 '3'에서 사용자 A가 실행한 update문이 실행되게 된다.]   DEAD LOCK의 발생 USER A USER B Update em1 set sal=100 Where ename='SCOTT'     Update em1 set sal=20 Where ename='SMITH' Update em1 set sal=300 Where ename='SMITH'     Update em1 set sal=400 Where ename='SCOTT' [사용자 A가 SCOTT의 SAL을 100으로 변경한다.] [사용자 B가 SMITH의 SAL을 20으로 변경한다.] [사용자 A가 SMITH의 SAL을 300으로 변경하려 하지만, 사용자 B가 '2'에서 SMITH의 값을 변경한 후, Commit / Rollack을 하지 않아 LOCK이 걸려 있는 상태이므로 변경이 이루어지지 않고 무한대기 상태가 된다] [역시 마찬가지로 사용자 B가 SCOTT의 SAL을 400으로 변경하려 하지만 사용자 A가 '1'에서 SCOTT의 값을 변경한 후 Commit / Rollback을 하지 않아 Lcok이 걸려 있는 상태이므로 변경이 이루어지지 않고 무한대기 상태가 된다.]   이 때, Commit을 실행하게 되면, 정상 종료시 Lock이 걸려 있던 작업이 'Auto-Commit'된다.

 

무결성 제약조건(Data Integrity Constraint Rule)

정의 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러 가지 규칙. 종류   NOT NULL(C) 해당 컬럼값으로 NULL을 허용하지않음 UNIQUE(U) 테이블 내에서 해당 컬럼 값은 항상 유일무이한 값을 가질 것. PRIMARY KEY(P) 해당 컬럼 값은 반드시 존재해야하고 유일해야 한다. (=NOT NULL, UNIQUE) FOREIGN KEY(R) 해당 컬럼의 값이 다른 컬럼의 값을 참조해야함. (=참조되는 컬럼에 없는 값은 입력 불가함) CHECK(U) 해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정.   지정 방법 Column_Level   Column_name data_type CONSTRAINT constraint_name constraint_type   Table_Level   CONSTRAINT constraint_name constraint_type(column_name)   지정한 제약조건의 확인 DESC [table_name] Select constraint_name, constraint_type, table_name From user_constraints;   제약조건의 변경   ALTER TABLE [table_name] ADD CONSTRAINT [Constraint_name] constraint_type(column_name) CASCADE; CASCADE 는 옵션값으로 PK와 같이 다른 테이블의FK가 참조하고 있기 때문에 지워지지 않는 값들을 강제로 삭제할 때 사용한다.   제약조건의 삭제   ALTER TABLE [table_name] DROP CONSTRAINT [Constraint_name] ;

 

View

CREATE VIEW [view_name] AS [select …. From --- …..] 의 형식으로 AS []의 select Qeury의 결과를 하나의 가상 테이블로 만든다.   사용이유 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화시킬 수 있다. 보안에 유리하다.   종류 단순 뷰(하나의 Table에서 파생) 복합 뷰(둘 이상의 Table에서 파생)   뷰의 제거 및 변경 DROP VIEW [view_name] CREATE OR REPLACE VIEW [view_name] AS [SELECT … FROM …]   VIEW 생성시 사용가능한 옵션[FORCE/NOFORCE] & [WITH CHECK OPTION/WITH READ ONLY] FORCE 실제 TABLE이 존재하지 않는 상태에서 VIEW를 생성하게끔 하는 옵션값   CREATE FORCE VIEW [view_name] NOFORCE 기본 값으로 생략 가능하며, FORCE와 달리, 실제 TABLE이 존재하지 않는 상태에서는 VIEW가 생성되지 않는다. WITH CHECK OPTION VIEW로 생성될 TABLE의 조건절(WHERE)에 WITH CHECK OPTION을 사용하게 되면, 해당 조건절에 관련된 내용들은 VIEW를 통해서 수정할 수 없다.   EX) CREATE OR REPLACE FORCE VIEW emp_chk20 AS SELECT empno,ename,deptno From emp_copy Where deptno=20 WITH CHECK OPTION;   위 예제에서 where 절에 deptno에 관련된 with check option이 설정되어 있으므로 뷰를 통해서 deptno는 변경이 불가능하다. WITH READ ONLY 뷰를 생성 후 어떠한 DML문도 뷰를 통해서는 수행할 수 없도록 하는 기능이다.   INLINE-VIEW Sub_query문에서 바깥 쪽 SELECT 문이 FROM 절 내부에 사용된 Sub_query문으로 이때, 내부에 사용되는 Sub_query에는 별칭(Aliase)가 부여된다. 그리고 이렇게 부여된 별칭은 VIEW처럼 사용된다.   EX)SELECT a,b,별칭이름.c From tbl1 ( SELECT d,e FROM tbl2 where 조건1) 별칭이름 Where 조건2;

 

Sequence

DB내에서 숫자 값이 일정한 규칙에 의해 연속적으로 자동 증가해야 하는 경우에 사요하는 객체.   문법 CRATE SEQUENCE [sequence_name] [INCREMENT BY n] à 증감폭 [START WITH n1] à 시작값 [MAXVALUE n3 | NOMAXVALUE] à최대값 n3/최대값 없음 [MINVALUE n4 | NOMINVALUE] à최소값 n4/최소값 없음 [CYCLE | NOCYCLE] à 최대값이 되면 다시 시작값으로 간다/ CYCLE 없음 [CACHE n5 | NOCACHE] à메모리상의 sequence 캐쉬값, Default 20 SEQUENCE의 수정 및 제거 ALTER SEQUENCE [sequence_name] [변경할 항목]=[변경할 값]   DROP SEQUENCE [sequence_name] Sequence에 대한 Data Dictionary 찾기 SQL> DESC USER_SEQUENCES; SELECT * FROM USER_SEQUENCES; CURRVAL & NEXTVAL CURRVAL은 최근에 마지막으로 사용된 SEQ No.를 NEXTVAL은 다음에 사용될 SEQ No.를 알려준다.

 

INDEX

SQL명령문의 처리 속도를 향상시키기 위해 Column에 대해서 생성하는 오라클 객체. 내부 구조는 B* Tree 구조를 사용. PK나 UK로 지정된 Column에는 자동적으로 INDEX가 생성됨. (고유/비고유/단일/결합/함수기반) INDEX로 나뉜다.   INDEX의 사용 유무 환경 고려하기   사용해야 하는 환경 사용하지 말아야 하는 환경 테이블에 행의 수가 많을 때 조건절인 (Where)문에 해당 컬럼이 많이 사용될 때 검색 결과가 데이터의 2~4% 정도일 때 JOIN에 자주 사용되는 컬럼 NULL을 포함하는 컬럼이 많은 경우 테이블에 행의 수가 적을 때 where문에 해당 컬럼이 자주 사용되지 않을 때 검색 결과가 전체 데이터의 10~15% 이상 높을 때 테이블에 DML 작업이 많은 경우(입력 수정 삭제 등이 자주 일어날 때)   INDEX의 생성/제거 그리고 재생성 CREATE INDEX [index_name] ON table_name(column_name);   DROP INDEX [index_name]   ALTER INDEX [index_name] REBUILD;

 

사용자 관리

사용자 생성 CREATE USER [user_name] IDENTIFIED BY [passwd]; 사용자에게 SYSTEM 권한 부여를 위한 GRANT 명령어 GRANT [부여할SYSTEM PREVILEGE] TO [할당해줄 user_name] Ex)grant create session to scott   SYSTEM PREVILEGE(시스템 권한) 관리자와 일반사용자가 가지는 시스템권한의 범위는 다르다.   -DBA에게 할당되어진 주요 SYSTEM-PREVILEGE 시스템권한 기능 CREATE USER 새롭게 사용자를 생성하는 권한 DROP USER 사용자를 삭제하는 권한 DROP ANY TABLE 임의의 테이블을 삭제할 수 있는 권한 QUERY REWRITE 질의 재작성을 할 수 있는 권한 BACKUP ANY TABLE 임의의 테이블을 백업할 수 있는 권한   WITH ADMIN OPTION DBA가 특정 시스템권한을 특정 사용자에게 부여할 때, 사용할 수 있는 옵션으로써 이 옵션으로 권한을 할당받게 되면, 그 권한에 한해서 DBA와 동일한 효력을 가진다.   -Normal User에게 할당되어진 주요 SYSTEM-PREVILEGE 시스템권한 기능 CREATE SESSION 데이터베이스에 접속할 수 있는 권한 CREATE TABLE 사용자 스키마에서 테이블을 생성할 수 있는 권한 CREATE VIEW 사용자 스키마에서 뷰를 생성할 수 있는 권한 CREATE SEQUENCE 사용자 스키마에서 시퀀스를 생성할 수 있는 권한 CREATE PROCEDURE 사용자 스키마에서 함수를 생성할 수 있는 권한.     객체 권한 테이블이나 뷰, 시퀀스, 함수 등과 같은 객체별로DML (SELECT , INSERT, DELETE)을 사용할 수 있는 권한. 시스템 권한과는 다르게 'ON'옵션이 추가되며 그 뒤에 테이블 객체나 뷰 객체등을 기술한다. EX) GRANT/REVOKE SELECT ON emp TO scott; (scott에게 emp 테이블에 select문을 실행할 수 있는 권한을 줌/해제) 사용자에게 부여된 권한 조회   현 사용자가 다른 사용자들에게 부여한 권한 보기 SELECT * FROM user_tab_privs_made; 현 사용자가 다른 사용자로부터 부여 받은 권한 보기 SELECT * FROM user_tab_privs_recd;   WITH GRANT OPTION DBA가 특정 객체권한을 특정 사용자에게 부여할 때, 사용할 수 있는 옵션으로써 이 옵션으로 권한을 할당받게 되면, 그 권한에 한해서 DBA와 동일한 효력을 가진다.

 

DATABASE ROLE 권한 제어

ROLE ?! 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어 놓은 것. 기존 사용자들에게 일일이 특정 권한을 부여하던 방식에서 벗어나 ROLE을 만들고, 사용자들을 그 ROLE에 가입시킨다.   사전 정의된 ROLE의 종류 가장 흔히 사용되어지는 권한들을 미리 정의해 놓은 것으로   CONNECT ROLE CREATE ( SESSIN, CLUSTER, DATABASE LINK, SEQUENCE, SYNONYM, TABLE, VIEW) ALTER SESSION RESOURCE ROLE CREATE (CLUSTER, PROCEDURE, SEQUENCE, TABLE, TRIGGER) DBA ROLE 사용자들이 소유한 DB객체를 관리하고 사용자들을 작성하고 변경하고 제거할 수 있도록 하는 모든 권한.( 시스템 자원의 무제한 사용 및 시스템 관리에 필요한모든 권한을 부여할 수 있는 강력한 권한을 보유한 ROLE)   사용자 ROLE 정의 CREATE ROLE [role_name_A]; à Role 생성. GRANT [object_privs ] TO [role_name_A]; à 생성된 Role에 특정 객체권한 할당. GRANT [role_name_A] TO [user_name]; à 특정 사용자에게 Role 할당.

 

 

SYNONYM (동의어)

다른 사용자의 객체에 접근할 때 일반적인방법은 [user_name.table_name]이지만, 긴 사용자명과 테이블명에 별칭을 부여함으로써 간단하게 요약해서 접근할 수 있도록 하는 것. 테이블 별칭을 SQL문에 직접 정의하는 것은 1회성이지만 동의어는 테이블에 대한별칭을 시스템테이블에 저장해 놓으므로 그 이름으로 접근이 가능한 것이다. 가장 대표적인 예는 sys소유의dual TABLE을 접근할 때, sys.daul이 아닌 daul로 접근하는 것이다.   SYNONYM 기본 생성 규칙 CREATE [PUBLIC] SYNONYM [synonym_name] FOR user_name.object_name;

 

PL/SQL

SQL문의 단점인 비절차성 때문에 2개이상의 테이블에서 어떠한 연결 및 절차성을 필요로하는 작업에서는 사용될 수 없다. 이를 극복하기 위해 ORACLE 사에서 개발한 '절차적인 프로그래밍 언어'로서 oracle을 제외한 다른 제품군에서는 사용이 불가하다. PL/SQL 자체는 산출된 내용을 화면에 출력하는 기능이 없다. 이를 위해 'DBMS_OUTPUT' PACKAGE를 사용한다. 뒤에 'PUT_LINE' PROCEDURE를 이용하면 변수에 저장된 값을 출력할 수 있다.   구조   DECLARE SECTION(선언부) 변수나 상수를 선언하는 부분 EXCUTABLE SECTION(실행부) 절차적 언어 요소인 [제어문,반복문,함수 정의] 등 LOGIC을 기술 'BEGIN'으로 시작한다. EXCEPTION SECTION(예외 처리부) PL/SQL실행 중 오류 발생시 이를 해결하기 위한 문장으로 구성   변수선언과 대입문 변수명 자료형; EX) DECLARE VEMPNO NUMBER(4); VENAME VARCHAR2(20); 스칼라(SCALAR)변수 VJOB EMP.JOB%TYPE; VDEPT DEPT%ROWTYPE; 레퍼런스(REFERENCES)변수   선택문 IF – THEN - END IF IF 조건1 THEN 실행1 END IF; IF – THEN –ELSIF – END IF IF 조건1 THEN 실행1; ELSIF 조건2 THEN 실행2; ELSIF 조건3 THEN 실행3; …… LSIF 조건4 THEN 실행4; END IF; IF – THEN – ELSE – END IF IF 조건1 THEN 실행1; ELSE 실행2; END IF; ->조건1에 부합하면 실행1을, 그게 아니면 실행2를 실행한다.   반복문 --추후 정리

 

커서(CURSOR)

PL/SQL에서는 한 개의 행만을 반환(RETURN)하는 경우 암시적 CURSOR로서 사용가능하지만, 2개 이상의 행을 반환(RETURN)하는 경우에는 명시적으로 CURSOR를 선언해 주어야 한다. CURSOR 사용의 4단계 절차 커서의 선언 CURSOR (cursor_name) 커서의 오픈 OPEN (cursor_name) 커서에 조회환 결과를 인출해 저장 FETCH (cursor_name) INTO ….. 커서의 클로징 CLOSE (cursor_name)   CURSOR의 상태 %NOTFOUND 커서 영역의 자료가 모두 FETCH 되었나? %FOUND 커서 영역에 FETCH가 되지 않은 자료가 있는가? %ISOPEN 커서가 OPEN된 상태인가? %ROWCOUNT FETCH된 RECORD가 몇 개 있는가?

 

저장 프로시저(PROCEDURE)와 함수(FUNCTION)

PL/SQL문을 DB에 저장할 수 있게끔 해주는 저장 프로시저의 사용은 복잡한 DML문들을 호출하여 사용함으로써 성능과 호환성문제를 향상시킨다.