DBMS/오라클] 오라클 트리거 작성 예

CREATE SEQUENCE 이름
[ INCREMENT BY n ]
[ START WITH n ]
[ {MAXVALUE n | NOMAXVALUE} ]
[ {MINVALUE n | NOMINVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
-----------------------------------------------------


SQL> CREATE [ PUBLIC ] SYNONYM 시노님 명
FOR 오브젝트 명 ;
-----------------------------------------------------


CREATE [OR REPLACE] TRIGGER trigger명
timing event1 [OR event2 ...] OF 칼럼명 ON 테이블명
[FOR EACH ROW]
PL/SQL 블록
-----------------------------------------------------

실예:]

CREATE OR REPLACE TRIGGER time_emp
BEFORE insert OR update OR delete ON s_emp
BEGIN
IF to_char(sysdate,'hh24') NOT BETWEEN '09' AND '12' THEN
raise_application_error(-20201,'시간이 끝났음');
END IF;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER up_emptemp
AFTER UPDATE OF SALARY ON s_emp
FOR EACH ROW
BEGIN
UPDATE EMPTEMP
SET salary =:NEW.salary
WHERE id = :OLD.id ;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER ord_total
AFTER INSERT OR DELETE OR
UPDATE OF price, quantity ON s_item
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE s_ord
SET total = NVL(total,0)+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
ELSIF DELETING THEN
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
WHERE id = :OLD.ord_id;
ELSE
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
END IF;
END;
/

DBMS/오라클]Case Statement

출처 : http://www.techonthenet.com/oracle/functions/case.php

Oracle/PLSQL: Case Statement

---

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:

CASE  [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result
END

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a conditionis found to be true, the case statement will return the result and not evaluate the conditions any further.
result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.
If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.
You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the case statement in an SQL statement as follows: (includes the expression clause)

select table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

Or you could write the SQL statement using the case statement like this: (omits the expression clause)

select table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

The above two case statements are equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
     result := 'The owner is SYS';
ELSIF owner = 'SYSTEM' THEN
    result := 'The owner is SYSTEM'';
ELSE
    result := 'The owner is another value';
END IF;

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.
Your SQL statement would look as follows:

select table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

For Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select
CASE
  WHEN a < b THEN 'hello'
  WHEN d < e THEN 'goodbye'
END
from suppliers;

Frequently Asked Questions

---

Question:  Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.
Answer:  Yes, below is an example of a case statement that evaluates two different fields.

select supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then the case statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the case statement will return South office.

DBMS/오라클] PL/SQL 테이블

원본 출처 :: http://www.oracleclub.com/lecture/1047

PL/SQL 테이블

 

  PL/SQL 에서의 테이블은 오라클 SQL에서의 테이블과는 다르다. PL/SQL에서의 테이블은 일종의 일차원 배열이라고 생각하면 이해하기 쉬울것이다.

  테이블은 크기에 제한이 없으면 그 ROW의 수는 데이터가 들어옴에 따라 자동 증가 한다.

  BINARY_INTEGER 타입의 인덱스 번호로 순서가 정해진다.

  하나의 테이블에 한 개의 컬럼 데이터를 저장 한다.

PL/SQL 테이블 문법 및 선언예제

 
-- 선언 예제
TYPE prdname_table IS TABLE OF VARCHAR2(30) 
INDEX BY BINARY_INTEGER; 

-- prdname_table 테이블타입으로 prdname_tab변수를 선언해서 사용 
prdname_tab   prdname_table 

-- 아래 프로시저에서 사용된 예제를 보면 이해가 쉽게 갈 것이다.
    

PL/SQL 테이블 예제

 
SQL> CREATE OR REPLACE PROCEDURE Table_Test
     (v_deptno IN emp.deptno%TYPE)

    IS

     -- 각 컬럼에서 사용할 테이블의 선언 
     TYPE empno_table IS TABLE OF emp.empno%TYPE
     INDEX BY BINARY_INTEGER;

     TYPE ename_table IS TABLE OF emp.ename%TYPE
     INDEX BY BINARY_INTEGER;

     TYPE sal_table IS TABLE OF emp.sal%TYPE
     INDEX BY BINARY_INTEGER;

     -- 테이블타입으로 변수를 선언해서 사용 
     empno_tab  empno_table ;
     ename_tab  ename_table ;
     sal_tab    sal_table;

     i BINARY_INTEGER := 0;

   BEGIN

     DBMS_OUTPUT.ENABLE;

     FOR emp_list IN(SELECT empno, ename, sal 
                     FROM emp WHERE deptno = v_deptno) LOOP

      /* emp_list는 자동선언되는 BINARY_INTEGER형 변수로 1씩 증가한다. 
         emp_list대신 다른 문자열 사용가능 */

            i := i + 1;

           -- 테이블 변수에 검색된 결과를 넣는다
            empno_tab(i) := emp_list.empno ;     
            ename_tab(i) := emp_list.ename ;
            sal_tab(i)   := emp_list.sal ;

      END LOOP;

      -- 1부터 i까지 FOR 문을 실행 
      FOR cnt IN 1..i LOOP

         -- TABLE변수에 넣은 값을 뿌려줌 
         DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || empno_tab(cnt) );
         DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || ename_tab(cnt) );
         DBMS_OUTPUT.PUT_LINE( '사원급여 : ' || sal_tab(cnt));

      END LOOP;

  END; 
  /

-- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> SET SERVEROUTPUT ON ;
 
실행 결과 
SQL> EXECUTE Table_Test(10);

사원번호 : 7782
사원이름 : CLARK
사원급여 : 2450
사원번호 : 7839
사원이름 : KING
사원급여 : 5000
사원번호 : 7934
사원이름 : MILLER
사원급여 : 1300 

PL/SQL 처리가 정상적으로 완료되었습니다. 

-- emp 테이블에 있는 데이터의 입력한 부서에 해당하는 사원번호, 
-- 사원이름, 사원급여를 뿌려주는 프로시저 이다