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)declarebegin        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)isv_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)isbegin        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)isbegin        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  numberis        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 welcomeafterinserton bonusbegin   dbms_output.put_line('欢迎欢迎');end;insert into bonus(ename) values('张三');--行级触发器for each rowcreate  or  replace  trigger  nolosalbeforeupdateon bonusfor each rowbegin    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_idbeforeinserton bonusfor each rowbegin    select  bonus_auto.nextval  into  :new.ename  from dual;end;insert  into  bonus(sal) values(1300);