1.12.1.1 터미널에서 JOB Attach 모드[편집]

expdp 아이디/비밀번호@db명 attach=JOB이름
expdp>status

1.12.1.2 SQL로 확인[편집]

• dba_datapump_jobs

SELECT owner_name, job_name, operation, job_mode, state 
  FROM dba_datapump_jobs;

• sys.v_$sqlarea

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name
     , rows_processed
     , round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
     , trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
  from sys.v_$sqlarea
 where sql_text like 'INSERT %INTO "%'
   and command_type = 2
   and open_versions > 0;

1.22.1.1 산술 명령어[편집]

UPDATE employee SET salary = salary * 1.05
                   WHERE customer_id = 5;

SELECT wage - tax FROM employee;

1.22.1.2 비교 연산자[편집]

* 보다 크다 : >
* 크거나 같다 : >=
* 보다 작다: <
* 작거나 같다: <=
* 같다: =
* 같지 않다: !=  ^=  <>  ¬= (depends on platform)

SELECT name, salary, email FROM employees WHERE salary > 40000; 
 SELECT name FROM customers WHERE customer_id < 6;

1.22.1.3 문자 명령어[편집]

• 문자열 합치기(Concatenate): ||

create or replace procedure addtest( 
a in varchar2(100), 
b in varchar2(100), c out varchar2(200) 
) 
IS
begin
C:=concat(a,'-',b);

1.22.1.4 날짜 명령어[편집]

• 더하기: +
• 빼기: -

1.22.2.1 PL/SQL 기본 타입[편집]

스칼라타입(Scalar type) (패키지 안에 선언STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG\LONG RAW, DATE, TIMESTAM)

콤포지트(결합) 타입(Composite types) (사용자 선언 타입 ):

TABLE, RECORD, NESTED TABLE and VARRAY

LOB 타입(LOB datatypes) : 구조화되지 않은 대량의 데이터를 저장하는 데 사용

1.22.2.2 %TYPE - 고정형 변수 선언(anchored type variable declaration)[편집]

고정형 변수 선언 Syntax

<var_name> <obj>%type [not null][:= <init-val>];

For 예시

name Books.title%type;   / * name은 테이블 Books의 'title' 컬럼과 동일한 유형으로 정의됩니다. * /
 
 commission number(5,2) := 12.5;
 
 x commission%type;  / * x는 변수 'commission'과 동일한 유형으로 정의됩니다. * /

Note:

1. 고정 변수를 사용하면 유형이 변경 될 때 고정 변수 유형을 유형과 자동으로 동기화 할 수 있습니다.
2. 고정 된 유형은 컴파일시 평가되므로 앵커 된 변수에서 유형의 변경 사항을 반영하도록 프로그램을 다시 컴파일하십시오.

1.22.2.3 콜렉션(Collections)[편집]

컬렉션은 정렬 된 요소그룹이며 모두 같은 유형입니다.

목록, 배열 및 기타 친숙한 데이터 유형을 포함하는 일반적인 개념입니다.

각 요소에는 컬렉션에서 해당 위치를 결정하는 고유한 첨자가 있습니다.

--Define a PL/SQL record type representing a book:
 TYPE book_rec IS RECORD 
    (title                   book.title%TYPE,
     author                  book.author_last_name%TYPE,
     year_published          book.published_date%TYPE);
 
 --define a PL/SQL table containing entries of type book_rec:
 Type book_rec_tab IS TABLE OF book_rec
      INDEX BY BINARY_INTEGER;
 
 my_book_rec  book_rec%TYPE;
 my_book_rec_tab book_rec_tab%TYPE;
 ...
 my_book_rec := my_book_rec_tab(5);
 find_authors_books(my_book_rec.author);
 ...

컬렉션을 사용해야하는 많은 이유가 있습니다.

• 새로운 최적화 컴파일러, 더 나은 통합 네이티브 컴파일 및 숫자 처리 응용 프로그램에 도움이되는 새로운 데이터 유형을 포함하여 투명한 성능 향상 덕분에 훨씬 빠른 실행 속도.

• FORALL 문은 더욱 유연하고 유용합니다.

 예를 들어, FORALL은 이제 비 연속 인덱스를 지원합니다.

• 정규 표현식은 PL/SQL에서 세 가지 새로운 함수 (REGEXP_INSTR, REGEXP_REPLACE 및 REGEXP_SUBSTR)와 REGEXP_LIKE 연산자를 사용하여 비교할 수 있습니다

<ref>이 문제에 대한 자세한 내용은 Jonathan Gennick의 "첫 번째 표현식"</ ref> .

• 컬렉션, 동등성을위한 컬렉션 비교 및 중첩 테이블에 대한 집합 연산 지원 등을 포함하도록 개선되었습니다.

1.22.2.4 묵시적 커서 와 커서 속성[편집]

INSERT, UPDATE, DELETE문을 수행했을 때 몇 건의 데이터가 영향을 받았는지, 즉 몇 건의 데이터가 새로 입력되고 갱신되고 삭제되었는지 알 수 있다.

예를 들어, 사원 테이블에서 80번 부서에 속한 사원은 총 34명인데, 이를 UPDATE 하는 경우

DECLARE
      vn_department_id employees.department_id%TYPE := 80;
    BEGIN
      -- 80번 부서의 사원이름을 자신의 이름으로 갱신
      UPDATE employees
         SET emp_name = emp_name
       WHERE department_id = vn_department_id;

      -- 몇 건의 데이터가 갱신됐는지 출력
      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

      COMMIT;
    END;

-- 결과 :     34

정확히 34건이 갱신됐음을 알 수 있다.

SQL%ROWCOUNT 커서 속성을 사용해서 해당 SQL문으로 인해 실제 처리된 결과 로우 수를 참조한 것. SQL%ROWCOUNT에서 %ROWCOUNT가 커서 속성이며 앞에 붙은 SQL은 커서 이름. 묵시적 커서의 정보를 참조할 때는 SQL로 시작되는 속성명을 사용해 참조할 수 있어 묵시적 커서를 SQL 커서라고도 한다.

• 묵시적 커서 정보 참조용 커서 속성

SQL%FOUND 결과 집합의 패치 로우 수가 1개 이상이면 TRUE, 아니면 FALSE를 반환 SQL%NOTFOUND 결과 집합의 패치 로우 수가 0이면 TRUE, 아니면 FALSE를 반환 SQL%ROWCOUNT 영향 받은 결과 집합의 로우 수 반환, 없으면 0을 반환 SQL%ISOPEN 묵시적 커서는 항상 FALSE를 반환(이 속성으로 참조할 때는 이미 해당 묵시적 커서는 닫힌 상태 이후이기 때문)

1.23.1.1 Functions[편집]

함수는 호출자에게 값을 반환해야합니다.

Syntax

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]
 RETURN [return_datatype]
 IS
     [declaration_section]
 BEGIN
     executable_section
     return [return_value]
 
     [EXCEPTION
         exception_section]
 END [function_name];

예시:

CREATE OR REPLACE  FUNCTION to_date_check_null(dateString IN VARCHAR2, dateFormat IN VARCHAR2) 
 RETURN DATE IS
 BEGIN 
     IF dateString IS NULL THEN
         return NULL;
     ELSE
         return to_date(dateString, dateFormat);
     END IF;
 END;

1.23.1.2 Procedures[편집]

프로시저는 호출자에게 값을 리턴 안된다는 점에서 함수와 다릅니다.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
 IS
     [declaration_section]
 BEGIN
     executable_section
     [EXCEPTION
         exception_section]
 END [procedure_name];

프로시저 또는 함수를 작성할 때 파라미터를 정의 할 수 있습니다.

선언 할 수있는 세 가지 유형의 매개 변수가 있습니다.

파라미터는 프로시저 나 함수에 의해 참조 될 수 있습니다.

프로시저 나 함수는 파라미터 값을 겹쳐 쓸 수 없습니다.

DEFAULT 값 선언 가능

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT] [DEFAULT ''value''] [,parameter]) ]

The following is a simple 예시 of a procedure:

/* purpose: 
목적 : courseId로 지정된 학습과정의 학생들을 보여줍니다.
*/
  
    CREATE OR REPLACE Procedure GetNumberOfStudents
       ( courseId IN number, numberOfStudents OUT number )
    IS
 
        /* 학생 수를 계산하는 더 좋은 방법이 있지만
            이것은 커서를 실제로 보여줄 수있는 좋은 기회입니다         */
 
        cursor student_cur is
        select studentId, studentName
            from course
            where course.courseId = courseId;
        student_rec    student_cur%ROWTYPE;
 
    BEGIN
        OPEN student_cur;
        LOOP
            FETCH student_cur INTO student_rec;
            EXIT WHEN student_cur%NOTFOUND;
            numberOfStudents := numberOfStudents + 1;
        END LOOP;
        CLOSE student_cur;
 
    EXCEPTION
    WHEN OTHERS THEN
          raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    END GetNumberOfStudents;

1.23.1.3 anonymous block[편집]

DECLARE 
  x NUMBER(4) := 0;
 BEGIN
   x := 1000;
   BEGIN
     x := x + 100;
   EXCEPTION
     WHEN OTHERS THEN
       x := x + 2;
   END;
   x := x + 10;
   dbms_output.put_line(x);
 EXCEPTION
   WHEN OTHERS THEN
     x := x + 3;
 END;

1.23.1.4 프로시져에 파라미터 지정 방법[편집]

스토어드 프로시저에서 파라미터를 전달하기위한 세 가지 기본 구문인

1. 위치 표기법,
2. 명명 표기법,
3. 혼합 표기법

이 있습니다.

다음 예는 매개 변수 전달에 대한 각 기본 구문에 대한 프로 시저를 호출합니다.

CREATE OR REPLACE PROCEDURE create_customer( p_name IN varchar2, 
                                              p_id IN number, 
                                              p_address IN varchar2, 
                                              p_phone IN varchar2 ) IS
 BEGIN
     INSERT INTO customer ( name, id, address, phone )
     VALUES ( p_name, p_id, p_address, p_phone );
 END create_customer;

begin
 create_customer('James Whitfield'
                , 33
                , '301 Anystreet'
                , '251-222-3154');
end;

begin
 create_customer(p_address => '301 Anystreet'
               , p_id => 33
               , p_name => 'James Whitfield'
               , p_phone => '251-222-3154');
end;

begin
 create_customer(v_name                         -- 위치 지정법
                , v_id                          -- 상동 
                , p_address=> '301 Anystreet'   -- 명명 표기법
                , p_phone => '251-222-3154'     -- 상동     
 );
end;

1.23.1.5 Table functions[편집]

CREATE TYPE object_row_type as OBJECT (
   object_type VARCHAR(18),
   object_name VARCHAR(30)
 );
 
 CREATE TYPE object_table_type as TABLE OF object_row_type;
 
 CREATE OR REPLACE FUNCTION get_all_objects 
   RETURN object_table_type PIPELINED AS
 BEGIN
     FOR cur IN (SELECT * FROM all_objects)
     LOOP
       PIPE ROW(object_row_type(cur.object_type, cur.object_name));   
     END LOOP; 
     RETURN;
 END;
 
 SELECT * FROM TABLE(get_all_objects);

1.23.2.1 Conditional Operators[편집]

• and: AND
• or: OR
• not: NOT

1.23.2.2 예시[편집]

IF salary > 40000 AND salary  <= 70000 
THEN()
ELSE IF salary>70000 AND salary<=100000
THEN()
ELSE()

1.23.2.3 If/then/else[편집]

IF [condition] THEN
     [statements]
 ELSEIF [condition] THEN
     [statements}
 ELSEIF [condition] THEN
     [statements}
 ELSEIF [condition] THEN
     [statements}
 ELSEIF [condition] THEN
     [statements}
 ELSEIF [condition] THEN
     [statements}
 ELSEIF [condition] THEN
     [statements}
 ELSEIF [condition] THEN
     [statements}
 ELSE
     [statements}
 END IF;

1.23.3.1 Associative arrays[편집]

인-메모리 테이블로 유용한 강력한 Type의 배열

1.23.3.2 예시[편집]

• 매우 간단한 예, 인덱스는 배열에 액세스하는 KEY이므로 배열의 모든 행에서 데이터를 사용하지 않는 한 전체 테이블을 반복 할 필요가 없습니다.

• 인덱스는 숫자 값일 수도 있습니다.

DECLARE
    -- Associative array indexed by string:

    -- Associative array type
    TYPE population IS TABLE OF NUMBER
        INDEX BY VARCHAR2(64);
    -- Associative array variable
    city_population  population;
    i                VARCHAR2(64);
BEGIN
    -- Add new elements to associative array:
    city_population('Smallville')  := 2000;
    city_population('Midland')     := 750000;
    city_population('Megalopolis') := 1000000;

    -- Change value associated with key 'Smallville':
    city_population('Smallville') := 2001;

    -- Print associative array by looping through it:
    i := city_population.FIRST;

    WHILE i IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE
            ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
        i := city_population.NEXT(i);
    END LOOP;

    -- Print selected value from a associative array:
    DBMS_OUTPUT.PUT_LINE('Selected value');
    DBMS_OUTPUT.PUT_LINE('Population of');
END;
/

-- Printed results:
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001

• 좀더 복잡한 예시, using a record

DECLARE
    -- Record type
    TYPE apollo_rec IS RECORD
    (
        commander   VARCHAR2(100),
        launch      DATE
    );
    -- Associative array type
    TYPE apollo_type_arr IS TABLE OF apollo_rec INDEX BY VARCHAR2(100);
    -- Associative array variable
    apollo_arr apollo_type_arr;
BEGIN
    apollo_arr('Apollo 11').commander := 'Neil Armstrong';
    apollo_arr('Apollo 11').launch :=   TO_DATE('July 16, 1969','Month dd, yyyy');
    apollo_arr('Apollo 12').commander := 'Pete Conrad';
    apollo_arr('Apollo 12').launch :=   TO_DATE('November 14, 1969','Month dd, yyyy');
    apollo_arr('Apollo 13').commander := 'James Lovell';
    apollo_arr('Apollo 13').launch :=   TO_DATE('April 11, 1970','Month dd, yyyy');
    apollo_arr('Apollo 14').commander := 'Alan Shepard';
    apollo_arr('Apollo 14').launch :=   TO_DATE('January 31, 1971','Month dd, yyyy'); 

    DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').commander);
    DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').launch);
end;
/

-- Printed results:
Neil Armstrong
16-JUL-69