분류 전체보기 (667) 썸네일형 리스트형 083 - [Oracle PL/SQL] DML Triggers - row-level sample new, old 정의할것. 오류 발생 코드가 있어서 1회만 실행되는 샘플 코드DROP TABLE EMP_COPY;/CREATE TABLE EMP_COPYAS SELECT * FROM employees;/SELECT EMPLOYEE_ID, FIRST_NAME,LAST_NAME, SALARY FROM EMP_COPY;# 옵션은 row level trigger 이지만, 오류를 발생시키므로 1회만 동작된다.create or replace trigger check_salbeforeinsert or update of salaryonEMP_COPYfor each row -- row trigger 옵션begin if :new.salary (오류 발생 코드가 없어서) 여러번 트리거가 발생하는 샘플 코드creat.. 082 - [Oracle PL/SQL] DML Triggers - stament-level sample 간단한 샘플 코드 delete from departments; ----------------------------- Error report - ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found -- let us do statment level trigger create or replace trigger dept_check_time before insert or update or delete -- time, event on DEPARTMENTS -- table begin if to_number (to_char(sysdate,'hh24') ) not between 8 and 16 then raise_applicatio.. 081 - [Oracle PL/SQL] DML Triggers(stament-level/row-level) statement trigger 샘플 코드 drop table t1; / create table t1 ( emp_id number, ename varchar2(20) ); / insert into t1 values (1,'ford'); insert into t1 values (2,'aya'); ---statement trigger create or replace trigger t1_b4_update before update --this timeing + event on t1 -- table name begin DBMS_OUTPUT.PUT_LINE(':)'); end; update t1 set ename=ename||' s'; -- 업데이트가 발생하면 트리거는 업데이트 개수와 상관없이 1번 실행된다 ---.. 080 - [Oracle PL/SQL] Triggers - Types/ Event Types 079 - [Oracle PL/SQL] Triggers? 078 - [Oracle PL/SQL] INDICES OF # 테이블 인덱스가 순차적이지 않은 경우, 인덱스에 순차접근하면 오류가 발생한다. declare type emp_table_type is table of number index by binary_integer; emp_table emp_table_type; begin emp_table(1):=100; emp_table(2):=101; emp_table(3):=102; emp_table(100):=103; dbms_output.put_line(emp_table.first); dbms_output.put_line(emp_table.last); forall i in emp_table.first..emp_table.last save exceptions update employees set salary=salar.. 077 - [Oracle PL/SQL] Bulk collect - using returning 업데이트 조건이 1개인 경우의 샘플 코드 --but first returning it can be used like this create or replace procedure update_sal_x (emp_id number,p_amount number) is v_new_sal number; begin update employees set salary=salary +p_amount where employee_id=emp_id returning salary into v_new_sal; dbms_output.put_line('the new sal now is '||v_new_sal); end; select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from employees.. 076 - [Oracle PL/SQL] Bulk collect & cursor 각 단계별 차이점 비교한 샘플 코드 --------without bulk------------- declare type emp_t is table of employees%rowtype; emp_table emp_t; cursor emp_c is select * from employees; c number:=0; begin emp_table:=emp_t(); open emp_c; loop emp_table.extend; c:=c+1; fetch emp_c into emp_table(c); exit when emp_c%notfound; dbms_output.put_line(emp_table(c).first_name); end loop; close emp_c; end; ----------------------.. 이전 1 ··· 4 5 6 7 8 9 10 ··· 84 다음
