linuxism

• by 권남 
• 2005/07/05 13:05 
• kwon37xi.egloos.com/1508634

$ sqlplus system/manager
SQL> show sga

$ sqlplus "sys/passwd as sysdba"

$ sqlplus "sys/passwd as sysdba"
startup -- DB 인스턴스 시작
startup force -- DB가 실행중일 경우 강제로 종료했다 시작
startup restrict -- 일반 사용자 접근 금지 상태로 DB 시작

$ sqlplus "sys/passwd as sysdba"
startup nomount; -- NO Mount 단계
alter database mount; -- Mount 단계
alter database open; -- Open 단계

$ sqlplus "sys/passwd as sysdba"
shutdown normal -- 세션,트랜잭션 종료시까지 대기후 종료
shutdown transactional -- 트랜잭션 종료시까지 대기후 종료
shutdown immediate -- 즉시 종료. 모든 DML 롤백
shutdown abort -- 비정상 즉시 종료. 백업과 복구시에만 사용.

$ sqlplus "/nolog"

$ exp mlb/mlb file=결과덤프파일.dmp compress=n rows=n tables=테이블명

1. 첫번째 방법
SELECT 'You '||Chr(38)||' Me' FROM DUAL;

2. 두번째 방법
SET DEFINE OFF
SELECT 'You & me' FROM DUAL;

DEFINE_EDITOR=gvim.exe

SET NEWPAGE 0
SET TERMOUT OFF
SET VERIFY OFF
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 500
SPOOL procedure_name.sql
SELECT TEXT FROM USER_SOURCE WHERE NAME='프로시져혹은함수이름' ORDER BY LINE;
SPOOL OFF

SHOW ERRORS

SET NUM 15

#!/bin/sh
# 오늘 날짜를 셸 변수로 지정
TODAY=`date +"%Y%m%d"`

sqlplus username/password << ENDSQL
-- 아래는 출력시 불필요한 형식 꾸미기가 안들어가게 한다.
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON

SPOOL 저장할파일_${TODAY}_ORIG
SELECT COL1 || '|' || COL2 || '|' || COL3 -- 실행할 쿼리
FROM MYTABLE;
SPOOL OFF
QUIT
ENDSQL

# 처음과 마지막 쿼리 잔재 제거. 일단 스풀을 실행해보고나서 파일 위,아래에 추가되는
# 불필요한 줄수를 알아본다. (여기서는 위에 2줄과 맨 아래 1줄)
sed -e "1,2d" -e "$d" 저장할파일_${TODAY}_ORIG > 저장할파일_${TODAY}

# 최종적으로 "저장할파일_${TODAY}"만 남겨둔다
rm 저장할파일_${TODAY}_ORIG

SET TIMING ON

SET SERVEROUTPUT ON SIZE 4000;
Execute DBMS_OUTPUT.PUT_LINE(SYSDATE) 

• SQL*Plus FAQ
  
• SQL*Plus set commands

피엘에스큐엘 (PL/SQL)은 상용 관계형 데이터베이스 시스템인 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나이다.

[편집]특징

주로 자료 내부에서 SQL 명령문만으로 처리하기에는 복잡한 자료의 저장이나 프로시저와 트리거 등을 작성하는 데 쓰인다. 범용 언어인 C와 C++ 그리고 파스칼 및 포트란 등의 프로그래밍 언어와는 다른 점으로 범용 언어들이 컴퓨터 시스템에서 특정한 작업을 처리하기 위해 만들어진 언어라고 볼 때 PL/SQL은 단지 오라클의 관계형 데이터베이스 (RDBMS)에서만 사용된다는 점이다.

PL/SQL의 구조는 에이다 (Ada)의 본떠 만들어졌다고 알려져 있으며, 얼핏 보면 그 구조가 범용 언어인 파스칼의 구문과 비슷하다. PL/SQL 외에도 각 관계형 데이터베이스마다 확장 언어들이 있다. 이러한 확장 언어의 대표적인 예로 마이크로소프트의 마이크로소프트 SQL 서버에 있는 TSQL이 있다.

[편집]같이 보기

• SQL
• 오라클 DBMS
• 관계형 데이터베이스

PL / SQL 은 Oracle 사의 가 Oracle Database 를 위해 컴퓨터 의 데이터베이스 언어 SQL 을 직접 확장하는프로그래밍 언어 이다.

원래 비 절차적 언어 이다 SQL을 절차적 언어 ( P rocedural L anguage)로 확장하는 것으로부터 "PL / SQL '로 명명되었으며이 부가 기능을 가진다. PL / SQL을 사용하면 절차적 언어 포함된 SQL 의 작업과 마찬가지로 변수의 사용과 if, for, loop 등의 제어 구조를 설명하고 업무 로직을 데이터베이스 에 구현할 수 있으며, 문의 결과 줄을 한 개씩 처리하는 경우에는 커서 에 따르면 FETCH 루프에서 작업을 수행할 수있다.

다른 데이터베이스 언어의 차이 [ 편집 ]

마찬가지로 데이터베이스 언어 SQL 의 확장으로, Sybase ASE와 Microsoft SQL Server 에서 구현되는 Transact-SQL 과 비교되는 경우가 있지만, 문법과 구문의 차이뿐만 아니라 명령의 SQL 엔진에서 처리되는 방법 이 다르다. Transact-SQL은 모든 구문을 SQL 엔진만으로 처리하지만, PL / SQL의 경우 PL / SQL 구문은 PL / SQL 엔진의 분석 및 실행을 해 데이터 조작 언어 (DML; SELECT / INSERT / UPDATE / DELETE) 구문은 기존의 SQL 엔진에서 해석하고 실행하도록 내부 분기하고있다. 따라서 Transact-SQL에서 SELECT 결과는 저장 프로 시저 에서 결과 집합을 직접 호출 원래로 반환하는 것이 가능하다, PL / SQL에서 그것을 할 ​​수 없기 때문에 패키지 정의 커서를 통한引渡し및 배열 변수를 사용하여 데이터 반환 등으로 피해야한다.

덧붙여서, PostgreSQL 의 PL / pgSQL 이 PL / SQL을 추천 구현된 것이지만, 데이터베이스 관리 시스템 (DBMS)의 구현에 의존하는 부분 (트랜잭션 관리의 개념이 다르다)과 출력 인수의 취급 다른 등 완벽한 호환성은 없다. IBM 의 DB2 V9.7에서 Oracle에서 환승을 노리고 PL / SQL을 지원하고있다.

문법 [ 편집 ]

Ada 언어와의 관계 [ 편집 ]

PL / SQL의 문법은 Ada 언어를 기반으로하고있다. Oracle Corporation CEO의 래리 엘리슨 이 Oracle을 개발하기 전에 국방부를위한 시스템 개발을 Ada 언어로 가고 있었다. 그 시스템 계획은 실패했지만 그 경위에서 Ada 언어를 바탕으로하는 채용했다고 추측된다. 덧붙여서 시스템의 계획 이름은 "Oracle" 이라고했다.

데이터 형식 [ 편집 ]

데이터 형식 은 변수는 문자열 같은 기본 형식 이외에 중첩 되는 테이블 (배열 형식하지만 첨자는 연속되지 않아도 목록 구조 같은 액세스 가능), 레코드 형식 ( 구조체 같은 것), 레코드 형식의 배열 인덱스된 테이블 ( 연상 배열 , 해시 테이블과 같은 것)이 가능하다. 그 이외에 PL / SQL에서는 "XX 테이블 YY 열 유형"또는 "XX 테이블 레코드의 형태"라는 간접적인 형식이 가능하기 때문에 테이블과 같은 의존하는 데이터베이스 개체 수정에 대해서도 다시 분석 (다시 컴파일)하기만하면, 또 다시 컴파일에서 문제가 발생하지 않는 경우에 한해 실행시 PL / SQL 엔진이 자동으로 다시 컴파일을 시도한다는 이점이있다.

블록과 예외 [ 편집 ]

PL / SQL은 기본적으로 BEGIN ~ END 또는 DECLARE ~ BEGIN ~ END 에 의한 "블록"에 코드를 기술한다. PL / SQL 블록 내에서 발생하는 예외 오류 (Exception)를 catch하려는 경우에는 BEGIN 과 END 사이에 더 EXCEPTION 절을 삽입하여 거기에서END 절 사이에 예외 처리 를 기술한다. 이 블록은 중첩될 설명을 할 수 있기 때문에, Java 언어의 try ~ catch 처럼 이용할 수있다. (※ 이것은 Java 언어 원조가 아닌 Ada → C + + 언어 → Java 언어처럼 캐치업되어 갔다 결과이다)

변수, 상수, 커서 같은 정의는 DECLARE ~ BEGIN 에 기술한다.

이 블록을 직접 적용을 "익명 블록"라고 즉시 실행된다. 한편, 저장 프로 시저 · 저장 기능 데이터베이스 트리거 패키지를 즉시 실행되지 않고 서버에 계산된 형태로 등록된다 ( 동적 SQL 은 제외).

댓글 [ 편집 ]

주석은 행의 시작 부분에 '-'라고 기술하고 개행까지의 또는 C 언어와 같이 / * ~ * /로 둘러싼 부분 (이 범위에 포함된 개행도 주석으로 인식되는)에 기술 한다.

저장 프로 시저 저장 기능 [ 편집 ]

저장 프로 시저 · 저장 기능은 PL / SQL 코드의 이름을 지정하여 분석 코드를 서버에 등록하고 호출할 수있는 상태로 만들 수있는 기능. 프로 시저는 반환 없음, ​​함수는 반환값이있다는 차이 뿐이다. 일반 SQL 문과 비교하여 저장 기능 시저는 실행 시에 해석 되었기 때문에 처리가 빠르지만, 참조하는 데이터베이스 스키마를 변경하면 (예 : 테이블의 구성을 변경하는 경우) 재분석 ( 컴파일)가 필요하게된다.

저장 기능은 일반 SQL 문장의 함수로 사용할 수있다.

데이터베이스 트리거 [ 편집 ]

등록되어있는 테이블에 행을 추가, 업데이트, 삭제 전후의시기에 자동으로 처리를 실행하는 PL / SQL 블록을 등록할 수있다. 이것을 데이터베이스 트리거 라고 부른다. 데이터를 처리하는 1 개씩 처리할 수 있으며 변경 전과 변경 후 값을 사용할 수있다. 시작 조건을 세세하게 설정할 수이다. 본래는 "제약"정의 관리할 수없는 참조 무결성 을 관리하기 위해 사용하는 것이다. 업무 처리를 암시적으로 기본 너무 업무 처리의 변경이있는 경우 다른 응용 프로그램의 동작에 영향을 미칠 수 있으므로주의하여 사용해야한다.

오류 처리 등 고려해야 할 점은 많지만, 비정규화된 테이블 동기화에 사용하면 편리하다.

패키지 [ 편집 ]

PL / SQL이 Ada 언어의 특징을 현저하게 나타내는 곳에 "패키지"의 개념을 도입하고있는 곳이다. 이것은 C 언어 에 의한 정적 변수와 헤더 파일 의 액세스 제어 기능과 유사한 것으로, 끈기있는 변수 또는 다른 패키지에서 참조를 허락하지 않는 변수와 저장 프로 시저 등을 만들 수있다 .

하지만 인스턴스 화할 수 없기 때문에 객체 지향 언어 의 클래스 는 본질적으로 다르다. 다만, 패키지가 처음 불린 경우에만 실행되는 '자동 첫 코드 (INIT 블록) "을 기술할 수 점은 C 언어 등에 볼 수없는 특징적인 점이다.

내장 / 유틸리티 패키지 [ 편집 ]

주로 데이터베이스 정의 (CREATE DATABASE 문장)가 완료된 후 실행하는 SQL 스크립트로 catproc.sql 있지만,이 시점에서 유틸리티 패키지 (DBMS_XXX) 커녕 표준 SQL 함수 COUNT ()도 정의되지 않은 상태이다. 이러한 기능은 일반적으로 데이터베이스 카탈로그를 정의하는 스크립트 catalog.sql 실행 후 catproc.sql라는 스크립트를 실행하여 정의된다. 패키지 STANDARD에 정의된있는 프로 시저 함수는 호출할 때 패키지 이름을 지정하지 않고도 호출할 수있다.

이 catproc는 OUI에서 제품 설치와 데이터베이스 정의를 위해 또는 별도 DBCA (Database Configuraton Assistant)에서 데이터베이스를 정의하는 경우, DBCA 데이터베이스 정의 스크립트를 생성한 경우 스크립트에 포함된 형태로 실행되므로 너무 의식할 필요는 없지만, 정의 데이터베이스의 동작이 이상한 경우는이 부분을 검토하고 경우에 따라서는 스크립트를 다시 실행 할 필요도있다. PSR (Patch Set Release : 소위 통합 패치)를 적용한 경우에도이 스크립트를 다시 실행해야합니다.

유틸리티 패키지는 버퍼 출력을위한 DBMS_OUTPUT 패키지와 파일 출력을위한 UTL_FILE 패키지, 응용 프로그램 간의 동기화 통신에 사용할 수 DBMS_PIPE, DBMS_ALERT 패키지, 동적 SQL을 실현하기위한 DBMS_SQL 패키지 등이있다.

유틸리티 패키지의 이름은 DBMS_ 또는 UTL_로 시작하기 때문에 사용자가 새 패키지를 만들 때이 이름을 피하 작성해야한다.

예제 [ 편집 ]

※ 주 DBMS_OUTPUT의 대상을 SQL * Plus는 SQL의 대화형 인터페이스하려면 다음 SQL * Plus의 설정을해야한다. 단말기와 같은 표준 출력에 작업을 PL / SQL 엔진이 기본적으로 가지는 기능이 아니라 유틸리티 도구의 기능에 의존하기 때문이다.

 SET SERVEROUTPUT ON

익명 블록 예

 DECLARE 
    HW     VARCHAR2 ( 100 )  : =  'Hello World!' ; 
 BEGIN 
    DBMS_OUTPUT . PUT_LINE ( 'Hello World!' )  ; 
    DBMS_OUTPUT . PUT_LINE ( 'VARIABLE hw =' | | HW )  ; 
 END ; 
 /

저장 프로 시저 예제

 CREATE  OR  REPLACE  PROCEDURE HelloWorld ( str IN  VARCHAR2 ) 
 AS 
    / * 정의 부분은 AS에서 BEGIN된다. * / 
    HW     VARCHAR2 ( 100 )  : =  'Hello World!' ; 
 BEGIN 
    DBMS_OUTPUT . PUT_LINE ( 'Hello World!' )  ; 
    DBMS_OUTPUT . PUT_LINE ( 'VARIABLE hw =' | | HW )  ; 
    DBMS_OUTPUT . PUT_LINE ( 'Parameter str =' | | str )  ; 
 END ; 
 /

예외 처리 예제

 DECLARE 
     FName EMP . ename % TYPE  ;  
 BEGIN 
     SELECT ename INTO fname FROM EMP ; 
 EXCEPTION 
     WHEN  TOO_MANY_ROWS  THEN   - 미리 정의된 예외 식별자 
        DBMS_OUTPUT . PUT_LINE ( '라인이 너무 많습니다' )  ; 
     WHEN  NO_DATA_FOUND  THEN 
        DBMS_OUTPUT . PUT_LINE ( '레코드가 건도 없습니다 ' )  ; 
     WHEN  OTHERS  THEN  - 기타 예외는 여기서 캐치 
        DBMS_OUTPUT . PUT_LINE ( 'Oracle 오류 :' | | SQLERRM )  ; 
 END ; 
 /

커서 처리의 예 (제어 구조도 다소)

 DECLARE 
     CURSOR C1 IS 
         SELECT ename FROM EMP ; 
     FNAME EMP . ename % TYPE  ;  
 BEGIN 
     / * 커서 c1를 열고 모든 레코드를 페치 분할까지 반복
         페치마다 테이블 emp 컬럼 ename을 검색하면서 볼 수 있습니다 * / 
     OPEN C1 ; 
     LOOP 
         FETCH C1 INTO fname ; 
         EXIT  WHEN C1 % NOTFOUND ;  - 커서 상태를 확인 
          DBMS_OUTPUT . PUT_LINE ( fname ) ; 
         IF  LENGTH ( fname )  <  5  THEN   - 문자열 길이 확인 
             DBMS_OUTPUT . PUT_LINE ( '→ TOO SHORT' )  ; 
         ELSE 
             DBMS_OUTPUT . PUT_LINE ( '→ TOO LONG!' ) ; 
         END  IF  ; 
     END  LOOP  ; 
     CLOSE C1 ; 
 EXCEPTION 
     WHEN  OTHERS  THEN  - 기타 예외는 모두 여기에서 catch 
        DBMS_OUTPUT . PUT_LINE ( 'Oracle 오류 :' | | SQLERRM )  ; 
 END  ; 
 /