oracle 触发器 merge,[OT]函数|过程|触发器|插入(insert)|修改(Merge)

CREATE TABLE errlog

(Errcode NUMBER,Errtext CHAR(40));

--1,创建函数：

CREATE OR REPLACE

FUNCTION get_salary (p_deptno NUMBER ) RETURN NUMBER AS v_sal NUMBER;

BEGIN

IF p_deptno IS NULL THEN

RAISE_APPLICATION_ERROR(-20991,'Department number is null');

ELSIF p_deptno < 0 THEN

RAISE_APPLICATION_ERROR(-20992,'Invalide department number');

ELSE

SELECT SUM(sal) INTO v_sal FROM EMP WHERE deptno=p_deptno;

RETURN v_sal;

END IF;

END;

--2,打开包得输出output

SET SERVEROUTPUT ON

--3,调用函数

DECLARE

v_salary       NUMBER(7,2);

v_sqlcode      NUMBER;

v_sqlerr       VARCHAR2(512);

Null_deptno    EXCEPTION;

Invalid_deptno EXCEPTION;

PRAGMA EXCEPTION_INIT(null_deptno,-20991);

PRAGMA EXCEPTION_INIT(invalid_deptno,-20992);

BEGIN

v_salary :=get_salary(10);

DBMS_OUTPUT.PUT_LINE('10 department salary is :'||TO_CHAR(v_salary));

BEGIN

v_salary :=get_salary(-10);

EXCEPTION

WHEN invalid_deptno THEN

v_sqlcode := SQLCODE;

v_sqlerr  := SQLERRM;

INSERT INTO errlog

(errcode,errtext) VALUES

(v_sqlcode,v_sqlerr);

COMMIT;

END inner1;

v_salary :=get_salary(20);

DBMS_OUTPUT.PUT_LINE('20 department salary is:' ||TO_CHAR(v_salary));

BEGIN

v_salary :=get_salary(NULL);

END inner2;

v_salary :=get_salary(30);

DBMS_OUTPUT.PUT_LINE('30 department salary is :'||TO_CHAR(v_salary));

EXCEPTION

WHEN null_deptno THEN

v_sqlcode :=SQLCODE;

v_sqlerr  :=SQLERRM;

INSERT INTO errlog

(errcode,errtext

) VALUES

(v_sqlcode,v_sqlerr

);

COMMIT;

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Other errores!');

END outer;

--4,创建存储过程

create or replace PROCEDURE Get_emp_rec(Emp_number IN EMP.empno%TYPE,

Emp_ret OUT Emp%ROWTYPE) IS

BEGIN

select * into Emp_ret

FROM emp Where Empno=Emp_number;

end;

--5,调用存储过程

declare

emp_number int :=7900;

emp_ret emp%rowtype;

begin

get_emp_rec(emp_number,emp_ret);

dbms_output.put_line(emp_ret.ename);

end;

--6,触发器

create table emp_test as select * from emp;

create table emp_test1 as select * from emp;

CREATE OR REPLACE TRIGGER del_emp

BEFORE DELETE OR UPDATE ON scott.emp_test1 FOR   EACH ROW

BEGIN

INSERT INTO emp_test(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

VALUES( :old.deptno, :old.empno, :old.ename , :old.job,

:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

--7,测试触发器

select * from emp_test1;

delete from emp_test1 where deptno=20;

commit;

select count(*) from emp_test;

--8,视图触发器

create or replace view emp_view as

select deptno,count(*) total_employeer,sum(sal) total_salary

from emp_test group by deptno;

select * from emp_view;

CREATE OR REPLACE TRIGGER emp_view_delete

INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

DELETE FROM emp_test WHERE deptno= :old.deptno;

END emp_view_delete;

--9,测试视图触发器

delete from emp_view where deptno=10;

commit;

select * from emp_test;

--10,登录触发器

create table logtable(username varchar2(50),logindate date);

create or replace trigger login_his

after logon on database

begin

insert into logtable

values(user,sysdate);

end;

--11,包定义

CREATE OR REPLACE package DXJF_MGR

AS

time1 number(10) := 0;

type cursorref_t is ref cursor;

type r_rate_t is record

(

no NUMBER(10),

rate NUMBER(10),

starttime date,

endtime date,

timespanid NUMBER(10)

);

type array_rate_t IS VARRAY(100) OF r_rate_t;

function f_is2day(dt date,duration number) return number;

procedure p_test(p_result in number :=0);

END DXJF_MGR;

--12,程序类型 :函数 判断通话时间否跨越2天

CREATE OR REPLACE FUNCTION f_is2day(dt date,duration number)

return number

is

v_date1 varchar2(10);

v_date2 varchar2(10);

v_date3 date;

v_date4 date;

i_day number(10);

begin

select (dt+duration/24/60/60) into v_date3 from dual;

select trunc(dt+1,'J') into v_date4 from dual;

if v_date3<=v_date4 then

return 0;

else

return 1;

end if;

end;

--13,Unconditional INSERT ALL

create table sal_history as select empno,hiredate,sal from emp where 1=0;

create table mgr_history as select empno,mgr,sal from emp where 1=0;

insert all

into sal_history values(empno,hiredate,sal)

into mgr_history values(empno,mgr,sal)

select empno,hiredate,mgr,sal from emp

where empno >800

select * from sal_history;

select * from mgr_history;

--14,Conditional INSERT ALL

create table sal_history as select empno,hiredate,sal from emp where 1=0;

create table mgr_history as select empno,mgr,sal from emp where 1=0;

INSERT ALL

WHEN SAL > 3000 THEN

INTO sal_history VALUES(empno,hiredate,sal)

WHEN MGR > 4000 THEN

INTO mgr_history VALUES(empno,mgr,sal)

select empno,hiredate,mgr,sal from emp

WHERE empno > 1000;

truncate table sal_history;

truncate table mgr_history;

select * from sal_history;

select * from mgr_history;

select * from emp;

--15,Conditional INSERT FIRST

create table special_sal as select deptno,sal from emp where 1=0;

create table hiredate_history_00 as select deptno,hiredate from emp where 1=0;

create table hiredate_history_99 as select deptno,hiredate from emp where 1=0;

create table hiredate_history as select deptno,hiredate from emp where 1=0;

insert first

when sal > 10000 then

into special_sal values(DEPTID,SAL)

when HIREDATE like('%00%')THEN

INTO hiredate_history_00 values(DEPTID,HIREDATE)

when hiredate like('%99%')THEN

into hiredate_history_99 values(deptid,hiredate)

else

INTO hiredate_history values(DEPTID,HIREDATE)

select deptno deptid,sum(sal) sal,max(hiredate)HIREDATE

from emp

group by deptno;

select * from special_sal;

select * from hiredate_history_00

select * from hiredate_history_99

select * from hiredate_history

--16,Pivoting insert

create table sales_source_data (employee_id number(6),week_id number(2),

sales_mon number(8,2),sales_tue number(8,2),sales_wed number(8,2),

sales_thur number(8,2),sales_fri number(8,2));

insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);

commit;

select * from sales_source_data;

create table sales_info(employee_id number(6),week number(2),sales number(8,2));

insert all

INTO sales_info VALUES (employee_id,week_id,sales_MON)

INTO sales_info VALUES (employee_id,week_id,sales_TUE)

INTO sales_info VALUES (employee_id,week_id,sales_WED)

INTO sales_info VALUES (employee_id,week_id,sales_THUR)

INTO sales_info VALUES (employee_id,week_id,sales_FRI)

select EMPLOYEE_ID,week_id,sales_MON,sales_TUE,

sales_WED,sales_THUR,sales_FRIfrom sales_source_data;

select * from sales_info;

create table tmp2 as select * from emp;

delete from emp2 where rownum<10;

commit;

--17,MERGE Statement

create table emp2 as select * from emp;

MERGE INTO emp2 e2

USING emp e

ON (e2.empno = e.empno)

WHEN MATCHED THEN

UPDATE SET

e2.ename = e.ename,

e2.job = e.job,

e2.mgr = e.mgr,

e2.sal = e.sal,

e2.comm = e.comm,

e2.deptno = e.deptno

WHEN NOT MATCHED THEN

INSERT VALUES(e.empno, e.ename, e.job,e.mgr, e.hiredate, e.sal, e.comm,e.deptno);

select count(*) from emp2;

--18,MERGE Statement

create table orders_master (order_id int,order_total number(10));

create table monthly_orders (order_id int,order_total number(10));

insert into orders_master values (1,1000);

insert into orders_master values (2,2000);

insert into orders_master values (3,3000);

insert into orders_master values (4,null);

insert into monthly_orders values (2,2500);

insert into monthly_orders values (3,null);

merge into orders_master o

using monthly_orders m on (o.order_id=m.order_id)

when matched then

update set o.order_total=m.order_total

delete where (m.order_total is null)

when not matched then

insert values (m.order_id,m.order_total);

select * from orders_master;

select * from monthly_orders;

select * from orders_master;