欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-03 23:32:59
...

存储函数

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;