[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_FRI from 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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29611940/viewspace-1143693/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29611940/viewspace-1143693/