oracle数据库——存储函数 存储过程 触发器 包 视图

存储函数

create or replace function 函数名
  (参数)
  return 返回数据类型
  is
  begin
  end;

--求两个数的和
 create or replace function f_sum
 (
     v_a number,v_b number
 )
 return number
 is
 begin
   return nvl(v_a,0)+nvl(v_b,0);
 end;


--从新闻表中查询新闻标题，如果标题比定义的len长，就截取标题，后面换成...，否则就输出标题
          
create or replace function f_newstitle
(
     v_newstitle varchar2,
     len number
)
return varchar2
is
begin
   if length(v_newstitle)>len then
      return substr(v_newstitle,1,len-3)||'...';
   else
      return v_newstitle;
   end if;
end;

存储过程

1 .不带参数
--给所有员工工资加一百
create or replace procedure p_addsal
is
begin
  update emp set sal=sal+100;
end;


执行过程：exec p_addsal;
打开输出功能：set serveroutput on;
2.带参数
--根据部门编号返回最高工资和最低工资
create or replace procedure p_getsal
(
   v_deptno in number,
   v_maxsal out number,
   v_minsal out number
)
is  
begin
  select max(sal),min(sal) into v_maxsal,v_minsal from emp where deptno=v_deptno;
end;
  
--定义一个语句块来执行这个过程
declare
    v_max emp.sal%type;
    v_min emp.sal%type;
begin
    p_getsal(&deptno,v_max,v_min);
    dbms_output.put_line('最高工资:'||v_max|| '   最低工资:'||v_min);
end;

--根据职位查询该职位的最高工资的姓名和最低工资的姓名
create or replace procedure p_getname
(
v_job in varchar2,
v_maxsalname out varchar2,
v_minsalname out varchar2
)
is
begin
select ename into v_maxsalname from emp where sal=(select max(sal) from emp where job=v_job);
select ename into v_minsalname from emp where sal=(select min(sal) from emp where job=v_job);
end;


declare
v_maxsalname emp.ename%type;
v_minsalname emp.ename%type;
begin
  p_getname('&job',v_maxsalname,v_minsalname);
  dbms_output.put_line('最高工资的姓名:'||v_maxsalname||'最低工资的姓名:'||v_minsalname);
  end

--输入输出型参数 in out
--根据员工编号查询他的工资  
create or replace procedure p_getsal
(
    v_empno in out number
)
is
begin
   select sal into v_empno from emp where empno=v_empno;
end;

declare 
  v_empno number(10);
begin
    v_empno:=7788;
    p_getsal(v_empno);
    dbms_output.put_line('工资'||v_empno);
end;

--根据部门编号查询该部门的员工姓名，职位，薪水
create or replace procedure p_getemp
(
    v_deptno in number,
    v_result out sys_refcursor
)
is 
begin
    open v_result for select ename,job,sal from emp where deptno=v_deptno;
  end;


--调用过程
declare 
    mycur sys_refcursor;
--定义一个记录集
    type t_record is record
    (
        v_name emp.ename%type,
        v_job emp.job%type,
        v_sal emp.sal%type
    );
--用类型声明一个变量
    v_row t_record;
begin
   p_getemp(&deptno,mycur);
   loop
--提取数据
      fetch mycur into v_row;
        exit when mycur%notfound;
        dbms_output.put_line(v_row.v_name||'   '||v_row.v_job|| '   '||v_row.v_sal );
   end loop;
   close mycur;
end;


--输入季节，返回这个季节入职的员工的姓名，职位，部门名称;
  思路1：select ename,job,(select dname from dept where deptno=emp.deptno) as dname from emp 
    where decode(to_char(hiredate,'q'),1,'春',2,'夏',3,'秋',4,'冬')='春'

  思路2：select ename,job,(select dname from dept where deptno=emp.deptno) as dname from emp 
    where to_char(hiredate,'q')=instr('春夏秋冬','春')



create or replace procedure p_get
(
v_season in varchar2,
v_result out sys_refcursor
)
is
begin
  open v_result for select ename,job,
  (select dname from dept where deptno=emp.deptno) as dname from emp
  where decode(to_char(hiredate,'q'),1,'春',2,'夏',3,'秋',4,'冬')='&v_reason';
end;

declare
mycur sys_refcursor;
type t_record is record 
(
ename emp.ename%type,
job emp.job%type,
dname dept.dname%type
);
v_row t_record;
begin
  p_get('&v_season',mycur);
  loop
    fetch mycur into v_row;
    exit when mycur%notfound;
    dbms_output.put_line(v_row.ename||' '||v_row.job||' '||v_row.dname);
    end loop;
    close mycur;
    end;

触发器

定义之后不能调用，在满足某些特定条件的时候自动运行
分为表级触发器和行级触发器
根据在事件前后触发又分为 before、after
根据触发事件的不同又分为insert、update、delete
for each row表示是行级触发器，没有它就表示是表级触发器
特殊变量：
new --为一个引用最新的列值
old --为一个引用以前的列值

--只允许15号修改工资  
   create trigger tr_before_update_emp_sal
    before update of sal on emp
    for each row
   declare       
   begin
        if to_number(to_char(sysdate,'dd'))<>15 then
  --引发一个自定义的异常，错误编号在-20000和-29999之间
            RAISE_APPLICATION_ERROR(-20001,'今天不允许修改工资');
        end if;
   end;

-- 根据添加的交易记录自动更新余额
   --创建余额表
   create table account
   (
       accid number(10) not null,
       password varchar2(30) not null,
       username varchar2(30) not null,
       balance number(10,2) not null,
       opendate date not null
   );
   --创建交易表
   create table trade
   (
       accid number(10) not null,
       tradedate date not null,
       tradetype varchar2(20)  not null,
       money number(10,2) not null,
       remark varchar2(50) 
   );
   --在表中插入数据
insert into account values(10086,'123456','中国移动',10000,sysdate);
insert into account values(10010,'123456','中国联通',50000,sysdate);
   --创建触发器
   create or replace trigger tr_after_insert_trade
     after insert on trade
     for each row
   declare
   begin
     if :new.tradetype='存入' then
         update account set balance=balance+:new.money where accid=:new.accid;
      else
         update account set balance=balance-:new.money where accid=:new.accid;
      end if;
   end; 
   --在交易表中添加数据，此时查询余额表时余额也多了八百
   insert into trade values(10086,sysdate,'存入',800,null);
   
--数据校验，在进行数据操作时进行提示
   create or replace trigger tr_test
     after  insert or delete or update on account
     for each row
     declare
     begin
          if inserting then
              dbms_output.put_line('正在添加数据:'||:new.accid);
          elsif deleting then
              dbms_output.put_line('正在删除数据:'||:old.accid);
          elsif updating then        
              dbms_output.put_line('正在修改数据:'||:old.accid);
          end if;
     end;  

包

--创建包头  
 create or replace package mypackage
   is
      type t_record is record
      (
         ename varchar2(50),
         job varchar2(50),
         sal number(10,2)
      );
      type t_cursor is ref cursor return  t_record;
      function sum(v_a number,v_b number)return number;
      procedure p_getemp(v_deptno in number,v_cursor out  t_cursor);
   end;
   
--创建包体 
   create or replace package body mypackage
   is   
      function sum(v_a number,v_b number)return number
        is
         begin
           return v_a+v_b;
          end;
      procedure p_getemp(v_deptno in number,v_cursor out  t_cursor)
        is
        begin
          open v_cursor for select ename,job,sal from emp where deptno=v_deptno;
        end;
   end;

视图

--创建视图  
--虚拟的查询，建立视图的目的是方便查询，一般不做修改
   create or replace view v_emp
   as
   select ename,job,hiredate,dname,grade from emp
      inner join dept on emp.deptno=dept.deptno
      inner join salgrade on sal between losal and hisal;
   
   
   select * from v_emp
   <==>
   select * from
   (
     select * from emp
   )
   
--删除视图
drop view v_emp;
--创建别名
create synonym mm for emp;
--创建索引
create index ix_emp_job on emp(job);
--正则表达式
select * from emp where regexp_like(ename,'\d{1,5}');

--在PL/SQL中使用DDL
--将sql语句赋给一个varchar2变量,再用execute immediate 执行这个varchar2变量即可;
   declare
     v_sql varchar2(100);
     v_ename varchar2(50);
   begin
        v_sql:='select ename from emp where empno=7788';
       execute immediate v_sql into v_ename;
       dbms_output.put_line(v_ename);
   end;