2

SQL문 기본

데이타베이스에서 데이타를 질의한다.(Select)
데이타를 입력(Insert), 수정(Update), 삭제(Delete)한다.
데이타 구조를 생성(Create), 수정(Alter), 삭제(Drop)한다
데이타 액세스를 조절한다(Grant)

1. SQL문의 종류
SQL문의 종류에는 
DDL(Data Definition Language테이블/데이타의 구조를 조작하는 명령)
DML(Data Manupulation Language : 데이타를 조작하는 명령)
Session Control(세션을 제어하는 명령)
System Control(시스템을 제어하는 명령)
Transaction Control(트랜잭션을 제어하는 명령)

등이 있다. (아래 표 참고)


Data Definition
Data Manupulation
System Control

ALTER
ANALYZE
AUDIT
COMMENT
CREATE
DROP
GRANT
NOAUDIT
RENAME
REVOKE
TRUNCATE
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
SELECT
UPDATE
ALTER SYSTEM

Transaction Control

COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION

Session Control

ALTER SESSION
SET ROLE

Table 2-1. SQL문 종류
ALTER : 데이타베이스를 변경할 때 사용
ANALYZE : 데이타베이스의 통계자료를 생성시에 사용
AUDIT: SQL문을 auditing하기 위해 지정하는 명령.
COMMENT : table, view, snapshot 또는 column에 관한 comment를 데이터 딕셔너리에 
           입력할 때 사용
CREATE : table, index, view, snapshot, cluster, database, database link 등과 같은 
           database object를 생성시에 사용
DROP : database object삭제하는 명령.
RENAME : table, view, sequence, private synonym의 이름을 변경하는 명령.
NOAUDIT :AUDIT에 의해 선택된 SQL문의 audit를 정지시키는 명령.
REVOKE : object의 권한을 취소하는 명령.
TRUNCATE : 테이블이나 cluster의 모든 row를 삭제하는 명령.
DELETE : 테이블이나 view의 base테이블의 row를 삭제하는 명령
EXPLAIN PLAN : SQL문의 실행계획을 결정하는 명령.
INSERT : 테이블, VIEW의 BASE테이블에 ROW를 추가하는 명령.
LOCK TABLE : 1개 또는 여러 개의 테이블을 LOCK하는 명령.
SELECT : 테이블, VIEW, SNAPSHOT에서 ROW를 구해오는 명령.
UPDATE : TABLE, VIEW의 BASE TABLE의 존재하는 ROW의 값을 변경하는 명령.
ALTER SESSION : 유저가 권한이 있는 세션을 제어하는 명령.
SET ROLE : 현재의 세션의 ROLE을 ENABLE/DISABEL시키는 명령.
ALTER SYSTEM : 오라클 인스턴스를 변경하는 명령.
COMMIT: 현재의 트랜잭션을 종료하고, 트랜잭션 도중 변경되어진 데이타를 확정하는 명령.
ROLLBACK : 현재의 트랜잭션을 취소하고 변경되어진 데이타를 변경전의 데이타로 복구.
SAVEPOINT : 트랜잭션에서 나중에 ROLLBACK할 위치를 지정하는 명령.
SET TRANSACTION : 현재의 트랜잭션에서 트랜잭션을 READ-ONLY로 하거나 READ-WRITE로 설정, 
         트랜잭션에서 사용될 ROLLBACK SEGMENT를 지정한다. 

TIP> SQLPLUS에서 HELP를 보는 방법
SQL>HELP [명령어] 
하면 된다.
2. 기본적인 SQL문
SELECT 한개 혹은 여려개의 테이블에서 ROW를 리턴한다.
INSERT 테이블에 새로운 ROW를 추가한다.
UPDATE 테이블의 ROW를 수정한다.
DELETE 테이블에서 ROW를 삭제한다.

다음 SQL문들은 Embeddec SQL문에서 CURSOR를 정의하고 조작하는데 사용된다.
DECLARE CURSOR를 선언한다.
ALLOCATE CURSOR변수에 메모리를 할당한다.
OPEN QUERY를 사용가능하게 수행하여 active set을 생성한다.
FETCH active set에서 각 ROW를 가져온다.
CLOSE CURSOR를 종료한다.

SELECT 문
SELECT문에서 사용되는 예약어들은
INTO, FROM, WHERE , CONNECT BY, START WITH,GROUP BY,HAVING,ORDER BY,
FOR UPDATE OF 등이 있다.
EXEC SQL SELECT ename, job, sal + 2000
     INTO :emp_name, :job_title, :salary
     FROM emp
     WHERE empno = :emp_number;
INSERT문
EXEC SQL INSERT INTO emp (empno, ename, sal, deptno)
     VALUES ( :emp_number, :emp_name, :salary, :dept_number);

subquery의 사용
subquery는 INSERT문장이나 CREATE TABLE문장 뒤에서, UPDATE문의 컬럼을 명시하는 
것으로 사용가능하다.
EXEC SQL INSERT INTO emp2 (empno, ename, sal, deptno)
     SELECT empno, ename, sal, deptno from emp
     WHERE  job = :job_title;

UPDATE문의 사용

EXEC SQL UPDATE emp
    SET  sal = :salary, comm = :commission
    WHERE empno = :emp_number;

UPDATE문에서도 subquery를 사용할 수 가 있는데 SET절에서 컬럼 뒤에 사용한다.
EXEC SQL UPDATE emp
    SET  sal = (SELECT AVG(sal) *1.1 FROM emp WHERE deptno = 20)
    WHERE empno = :emp_number;


DELETE문

EXEC SQL DELETE emp
    WHERE empno = :emp_number;
INSERT를 제외한 SELECT, UPDATE, DELETE에서는 WHERE절을 사용가능한데, 이 WHERE절은 
테이블의 특정한 ROW를 찾는 검색조건이다.

3. CURSOR의 사용

여러 개의 ROW를 query할 경우 사용한다.
CURSOR의 사용은 DECALRE, OPEN, FETCH, CLOSE의 4개의 명령으로 사용 가능하다.
처음에 DECALRE문으로 CURSOR를 선언한다. 커서의 선언은 명시적으로 SQL QUERY문에 커서의 
이름을 대응시키는 것이다.

EXEC SQL DECLARE emp_cursor CURSOR FOR
    SELECT ename, empno, sal
      FROM emp
     WHERE deptno = :dept_number;

CURSOR명에는 하이픈(“-“)이 들어가는 안되고, 길이는 상관이 없는데 처음 31자는 의미가 
있어야 한다. ANSI모드에서는 18자로 제한되어 있다.
CURSOR를 정의한 SELECT문장에는 INTO절은 사용하지 않는다.

실제적으로 CURSOR를 실행하는 부분은 OPEN이다.

EXEC SQL OPEN emp_cursor;

커서를 DECLARE하기 전에 먼저 OPEN을 하면 OPEN에서 에러가 발생된다.
커서는 OPEN시점에서 실제적으로 QUERY를 수행하여 데이타를 추출한다.
OPEN이 되었다면 데이타는 FETCH문으로 어플리케이션 프로그램으로 가져온다.

EXEC SQL FETCH emp_cursor
     INTO :emp_name, :emp_number, :salary;

이 FETCH문장은 더 이상 가져올 데이타가 없을 때까지 실행되어야 하니까, LOOP한에 위치하게 
된다..

while(1){
    EXEC SQL FETCH emp_cursor
     INTO :emp_name, :emp_number, :salary;
    if(sqlca.sqlcode == 1403)  {
       printf("No Data Found !\n");
       break;
    } else if(sqlca.sqlcode != 0 ) {
        printf(" Oracle Error Detected!!: %d\n%s\n", sqlca.sqlcode,sqlca.sqlerrmc);
        break;
     }
     printf("%s:%s:%d\n",emp_name, emp_number, salary);
}

더 이상 데이타가 존재하지 않으면 CLOSE를 사용하여 커서를 FREE시켜야 한다.

EXEC SQL CLOSE emp_cursor;

이렇게 함으로써 커서가 점유하고 있던 resource를 데이타베이스에게 되돌려 준다.
위의 cursor의 사용을 그림으로 한다.
 
커서의 사용은 위의 그림과 같이 동작된다. 이 커서가 다시 사용된다면 CLOSE하지 말고 
DECLARE에서 사용된 호스트 변수에 원하는 값을 다시 부여하고 OPEN부터 다시 수행시켜 
PARSING의 부하를 줄일 수 있다.
커서는 재사용 될 수 있다.

EXEC SQL DECLARE emp_cursor CURSOR FOR
    SELECT ename, job 
    FROM  emp
    WHERE empname = :emp_number;

while(1) {
   scanf(emp_number,"%s");
   if(strcmp(deptno,"0")==0) break;
   EXEC SQL OPEN emp_cursor
   while(1) {
      EXEC SQL FETCH emp-cursor
          INTO :emp_name, :job_title;
      if(sqlca.sqlcode == 1403)  break;
      if(sqlca.sqlcode != 0) {
         printf("ORACLE Error !!1 %d",sqlca.sqlcode);
         break;
      }
      printf("%s%s\n",emp_name, job_title);
   }
}

EXEC SQL CLOSE emp_cursor;

4. 내부커서, 외부커서
오라클은 SQL문을 수행하기 위하여 PGA(Program Global Area)작업영역을 사용한다. 
PGA는 SQL문장 수행에 필요한 정보들을 저장하는 CURSOR에 의해 각 SQL문을 제어 하도록 
구성되어 있다. 커서에는 내부 커서와 외부커서가 있다. 모든 DDL과 DML에 대해 내부적으로 
커서를 생성시킨다. 그러나 다중처리 단위를 여러 번 수행시키고자 한다면 외부커서를 
선언하여 사용해야만 한다. 

내부커서
오라클은 SQL문장에 대해서 무조건 내부 커서를 생성하며 이 커서는 보관 커서를 지정하지 
않는 이상 DECLARE/OPEN/FETCH/CLOSE 작업을 매번 자동적으로 수행된다. 
또한 한개의 ROW를 추출하는 문장이나 다중처리로 추출하는 경우도 마찬가지로 작동된다.

외부커서
앞에서 살펴본 프로그래머가 커서를 선언하여 OPEN/FETCH/CLOSE를 하는 커서를 말한다.

HOLD_CURSOR의 사용
루프내에서 사용되어질 프로그램에 대해서는 파싱은 한번만 하면 된다. 그렇게 하기 위해서는 
프로그래머가 임의적으로 HOLD_CURSOR, RELEASE_CURSOR옵션을 지정해 주어야만 내부커서를 
보관할 수 있다.
만약 이렇게 보관시켜야 할 커서의 숫자가 10개가 넘어간다면 MAXOPENCURSOR 옵션을 늘려 
주어야 한다. 이 옵션은 기본적으로 10개로 잡혀있다.
Data Definition	Data Manupulation	System Control
ALTER ANALYZE AUDIT COMMENT CREATE DROP GRANT NOAUDIT RENAME REVOKE TRUNCATE	DELETE EXPLAIN PLAN INSERT LOCK TABLE SELECT UPDATE	ALTER SYSTEM
		Transaction Control
		COMMIT ROLLBACK SAVEPOINT SET TRANSACTION
	Session Control
	ALTER SESSION SET ROLE