분류 전체보기 (658) 썸네일형 리스트형 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; ----------------------.. 075 - [Oracle PL/SQL] Bulk Binding - bulk_exceptions, bulk collect 벌크로 데이터를 입력할때 오류에 대한 자세한 정보를 가져올 수 있는 방법에 대해서 알아보자 각 로우별 발생한 오류를 모두 확인하는 샘플 코드임. drop table ename; / create table ename as select distinct first_name from employees; / select first_name from ename; declare type ename_t is table of varchar2(100); ename_table ename_t:=ename_t(); c number:=0; errors number; begin for i in (select * from ename ) loop c:=c+1; ename_table.extend; ename_table(c):=i.fir.. 이전 1 ··· 3 4 5 6 7 8 9 ··· 83 다음
