Oracle数据库的存储过程、存储函数与触发器

Oracle数据库的存储过程、存储函数与触发器

在Oracle中使用的语言是plsql过程语言，它是一组sql语句组成的语句块+处理逻辑(条件判断+循环)。

下面我就简要介绍一下plsql语言的用法

语法：
      declate
            变量的声明
       begin
            程序体(一堆sql+if和循环)
            exception
                    when  异常类型  then
                                  处理
       end;
赋值语句:
            变量名  类型:=初始值
            select    字段名    into   变量名
变量的声明:
            数字   number
            字符串    varchar2
            日期     datetime
            引用类型变量 :  变量名    表名.列名%type(引用数据库的列的类型作为某变量的数据类型)
            记录类型变量:    变量名     表名%rowtype(当声明一个变量的值是数据库的一行时使用记录类型，引用数据库表中的一行作为数据类型)

举例:

--利用引用类型变量(7499员工的姓名，职位)
declare
         v_name   varchar2(10);
         v_job    emp.job%type;
begin
       select  ename,job  into  v_name,v_job   from   emp   where empno=7499;
       dbms_output.put_line('7459的姓名是:'|| v_name);
       dbms_output.put_line('7459的职位是:'|| v_job);  
 end;

 --记录类型变量查询
declare
       v_emp    emp%rowtype;
begin
       select   *   into v_emp  from  emp  where  empno=7499;
       dbms_output.put_line('7459的姓名是:'|| v_emp.ename);
       dbms_output.put_line('7459的职位是:'|| v_emp.job);  
 end;

if语句

--判断工资的等级
declare
       v_sal   emp.sal%type;
begin
       select  sal  into v_sal  from emp  where empno=7499;
       if  v_sal>5000   then
                 dbms_output.put_line('7459的工资是:'|| v_sal||'挺高的');  
        else
                 if   v_sal>300 then
                       dbms_output.put_line('7459的工资是:'|| v_sal||'一般');
                  else
                        dbms_output.put_line('7459的工资是:'|| v_sal||'凑合');
                 end if;
       end if;
 end;

循环语句

/*
循环语句
语法1:
       loop 
           循环体(必须要有退出条件)
           exit  when  条件:如果条件满足退出
       end  loop;
语法2:
       while   条件   loop
               循环体(必须有改变条件语句)
        end   loop;
语法3:
        for   循环变量   in  范围  loop---循环变量是不需要定义的
              循环体
        end   loop;
*/
--输出1到10的数组(loop)
declare
        v_n  number:=1;
begin
        loop
             dbms_output.put_line(v_n);
             v_n:=v_n+1;
             exit  when v_n>10;
        end loop;
end;
--输出1到10的数组(while)
declare
        v_n  number:=1;
begin
       while  v_n<11 loop
              dbms_output.put_line(v_n);
             v_n:=v_n+1;
        end  loop;
end;
--输出1到10的数组(for)
declare
begin
        for i in 1.. 10  loop
             dbms_output.put_line(i);
         end loop;
end;

游标

/*
游标:
         接收查询结果集
         语法:
              声明游标
                     cursor   游标变量   is  查询sql语句------没有执行sql
               打开游标
                     open  游标变量;------执行了sql语句
               提取数据
                     fetch   游标变量   into   记录类型变量
                关闭游标
                     close  游标变量
游标的属性:
                     游标变量%found ----如果返回真就代表现在有数据，如果假就没有数据。
                     游标变量%notfound---如果返回真就代表现在没有数据，如果假就代表有数据。
*/
--输出所有员工的员工编号和姓名
declare
           cursor  v_emp_cursor  is select * from emp;--声明
           v_emp  emp%rowtype;
begin
           open   v_emp_cursor;----打开执行sql
           fetch   v_emp_cursor  into  v_emp;
           while  v_emp_cursor%found  loop
                  dbms_output.put_line('员工编号:'|| v_emp.empno);
                   fetch   v_emp_cursor  into  v_emp;
            end loop;
            close  v_emp_cursor;
end;
--返回多行值(采用的是游标类型)
/*
系统引用游标sys_refcursor
        声明游标时不指定结果集，打开游标时指定结果集装入数据
*/
--输出所有员工的员工编号
declare
        v_emp_cursor  sys_refcursor;
        v_emp  emp%rowtype;--记录类型变量
begin
        open   v_emp_cursor  for  select  * from emp;
        fetch  v_emp_cursor  into  v_emp;
        while  v_emp_cursor%found  loop
               dbms_output.put_line('员工姓名'||v_emp.ename);
                fetch  v_emp_cursor  into  v_emp;
         end  loop;
         close  v_emp_cursor;
end;
--动态的输入部门的员工编号和姓名
declare
            cursor  v_emp_cursor(v_deptno  number) is select  *  from emp where deptno=v_deptno;
            v_emp   emp%rowtype;
            v_d  number:=#
begin
            open v_emp_cursor(v_d);--打开执行sql
            fetch  v_emp_cursor  into v_emp;
            while  v_emp_cursor%found  loop
                  dbms_output.put_line('员工编号:'|| v_emp.ename);
                  fetch  v_emp_cursor into v_emp;
             end loop;
             close  v_emp_cursor;
 end;

异常

declare
           v_emp   emp%rowtype;
begin
          select  *  into   v_emp  from emp;
          exception
                  when  others then
                        dbms_output.put_line('其他异常');
end;
--自定义异常
--如果没有员工的部门就是一种异常
declare
           cursor  v_emp_cursor   is  select *  from emp where deptno=50;
           v_emp   emp%rowtype;
           v_noemp   exception;
begin
           open  v_emp_cursor;
           fetch  v_emp_cursor  into v_emp;
           if  v_emp_cursor%notfound   then
                       raise  v_noemp;
           end if;
           exception
                       when  v_noemp   then
                                       dbms_output.put_line('我异常了');
end;

存储过程

/*
存储过程
      是一段封装的plsql代码块，对重复利用的代码进行封装，一组
      为了完成特定功能的语句集，经编译后存储在数据库中，用户通过指定
      存储过程的名字并给出参数来执行它。
 语法:
      create  or  replace  procedure   存储过程名称(参数名称 in|out 参数类型)
      is|as
              声明变量
      begin
              plsql程序体;
      end;
*/
--没有返回值(增加工资输出前后工资值)
create  or replace  procedure   add_sal(v_empno in number)
is
v_sal  emp.sal%type;
begin
       select   sal   into   v_sal  from emp  where   empno=v_empno;
       dbms_output.put_line('增加前的工资:'|| v_sal);
       update emp  set sal=sal+100  where  empno=v_empno;
       commit;
        select   sal   into   v_sal  from emp  where   empno=v_empno;
         dbms_output.put_line('增加后的工资:'|| v_sal);
end;
--调用存储过程
begin
         add_sal(7369);
end;
--返回单个值
--统计某个员工年薪
create  or replace procedure  count_year_sal(v_empno  in number,v_year_sal  out  number)
is
begin
        select   sal*12+nvl(comm,0)  into v_year_sal  from emp where   empno=v_empno;
end;
--调用存储过程
declare
        v_ys  number;
begin
        count_year_sal(7369,v_ys);
        dbms_output.put_line(v_ys);
end;

--返回多行值(采用的是游标类型)
--查询指定部门下的所有员工信息(用系统引用游标实现)
create  or  replace  procedure  deptinfo(deno  in number,v_emr_cursor out  sys_refcursor)
is
begin
        open  v_emr_cursor  for  select  *   from  emp  where deptno=deno;
end;
--调用存储过程
declare
        v_emp_cursor  sys_refcursor;
        v_emp   emp%rowtype;
begin
        deptinfo(20,v_emp_cursor);
        fetch  v_emp_cursor  into v_emp;
               while  v_emp_cursor%found  loop
                      dbms_output.put_line('员工编号'||v_emp.empno);
                      fetch  v_emp_cursor  into v_emp;
                end loop;
                close  v_emp_cursor;
 end;

存储函数

/*
 存储函数
              和存储过程几乎一样。
    语法:
              create  or   replace   function   存储函数名称(参数 in|out  类型)
              return   返回类型
              is|as
              begin
                       程序体(必须有return语句返回结果)
              end;
 */
 --统计某个员工年薪
create  or  replace   function  count_year_sal_fun(v_empno in number)
return  number
is
        v_sal  number;
begin
        select  sal*12+nvl(comm,0)  into  v_sal  from emp where empno=v_empno;
        return v_sal;
 end;
 --调用存储函数
declare
        v_ysl  number;
begin
        v_ysl:=count_year_sal_fun(7499);
        dbms_output.put_line(v_ysl);
end;

存储过程与存储函数的区别:

1、创建关键字 不一样 procedure  function

2、创建函数必须使用return  指定函数返回值类型

3、函数必须在begin和end之间通过return返回一个变量

4、函数的调用必须有变量接收返回值。

5、函数可以用在select语句中。

触发器

/*
触发器
        类似于监听器，用来监听表的操作
语法:
        create  or  replace  trigger  触发器名称
        begore | after--触发时机
        insert|update|delete--触发条件
        on  表名---触发对象
        begin
            程序体
         end;
*/
--插入员工表数据后触发器自动输出欢迎语句
--但是触发器一般不使用after,因为数据回滚，触发器还是会执行，不安全。
create  or  replace  trigger welcome
after
insert
on bonus

begin
   dbms_output.put_line('欢迎欢迎');
end;

insert into bonus(ename) values('张三');

--行级触发器for each row
create  or  replace  trigger  nolosal
before
update
on bonus
for each row
begin
    if  :new.sal< :old.sal  then
        raise_application_error(-20001,'不能少我工资');
     end if;
end;

update  bonus  set sal=1000 where ename='张三';
--触发器实现id自增长
create  sequence  bonus_auto;
create  or replace  trigger  bonus_id
before
insert
on bonus
for each row
begin
    select  bonus_auto.nextval  into  :new.ename  from dual;
end;

insert  into  bonus(sal) values(1300);