第10课pl/sql语言之创建 包和过程等

Pl/sql是oracle/sql是oracle在标准的sql语言上的扩展，他不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句等等。

1，  过程，函数，触发器是pl/sql语言编写的；

2，  过程，函数，触发器存在oracle中；

3，  Pl/sql编写的过程，函数，触发器可以再别的应用程序中调用，

从而简化编程；

优点：提高应用程序的运行性能；模块化的设计思想；减少网络传输量；提高安全性；

Plc/sql: 缺点：

 移植性不好；换一种数据库，存储过程就不通用了；

下面举例说明怎么用Pl/sql语言来写存储过程等：

1，向表中添加记录：

  create or replace procedure sp_pro1 is

begin

insert  into mytest values('jone','123');---假如有一个表,里面有姓名和密码两个字段；

end;

/

上面就是创建了一个过程；我们可以用 exec sp_pro1;  exec sp_pro1(参数1，参数2)，  call sp_pro1,call sp_pro1(参数1，参数2)来调用这个过程；

如果过程创建失败，可以通过show error  来查看错误信息；

例子2：创建一个删除记录的过程：

create or replace sp_pro2 is 

begin

delete from mytest where name='jone';

end;

/

开发人员有必要掌握pl/sql语言进行数据库的编程:如编写过程：分页存储过程模块，订单处理存储过程模块，转账存储过程模块等非常复杂的查询等。

其实不仅是写过程，不过过程是最主要的而已，看还可以写什么模块：

块包括（过程，函数，触发器，包）；块是最基本的程序单元，复杂的查询往往需要多个块的嵌套；

编写块的规范：

1，  单行注释--, 多行注释：/*…….*/;

2，  定义变量： 变量以v_开头， 常量以c_开头，定义游标,以_cursor为后缀，定义例外,建议用e_开头；

 

块的组成：

定义部分(declear)：定义变量 常量，例外，复杂数据类型等等；

执行部分：(begin):要执行的pl/sql语句；

例外处理部分(exception):处理运行的各种错误；

End; 

这几部分都不是必须的；

块的举例：

首先打开输出项： set serveroutput on/off   可以再数据库命令窗口显示输出内容；

块：

 begin

dbms_output.putline('hello!');       dbms_output 是oracle的包，put_line是这个包里的过程或者函数；   包里包含的是过程或者函数；

end;

如果没有Set serveroutput on， 那么不会有显示hello;

例子2：带有定义的块：

Declare

     v_namevarchar2(25);

 Begin

Select  ename into  v_name  from   emp  where      empno=&no;

Dbms_output.put_line('雇员名字：'||v_name);

End;

/

///其中&no表示从控制条接受一个empno;

例子3：带有例外的例子

如果查找不到数据，或者说找错了数据，可能会造成不必要的麻烦，所以有必要进行例外的处理：

Oracle 的一个很重要的例外是no_data_found

 

declare

v_namevarchar2(5);

v_salnumber(7,2);

begin

select   ename,sal   into  v_name,v_sal   from   emp  where    empno=&no;

dbms_output.put_line('用户名'||v_name||'工资'||v_sal);

exception

when   no_data_found   then

dbms_output.put_line('朋友，你的编号有错！');

end;

/

块中最重要的还是过程，

过程当然可以有输入，输出参数，通过输入参数，将数据传递到执行部分，通过输出参数，将数据传递到应用环境

用命令create procedure 来创建过程

例1：输入员工的名字，新工资，可以修改雇员的工资；

Create procedure  or replace  sp_pro3(spname varchar2 ,newsal number) is

Begin

Update emp set  sal=newsal where ename=spname;

End;

例子2：

在java 里使用过程：

 

try{

               Class.forName("oracle.jdbc.driver.OracleDriver");

               Connectionct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora","scott","tiger");

               CallableStatement  cs=ct.prepareCall("call sp_pro3(?,?)");

               cs.setString(1,"SMITH");

               cs.setInt(2,10);

               cs.execute();

               cs.close();

               ct.close();

        }catch(Exception e)

        {

               e.printStackTrace();

              

        }

函数比较少用：如下使用

函数与过程有异曲同工之妙，用create function 函数名 ，在函数头必须包含return字据，函数体必须包含return 数据；

例子：

 

create  or   replace   function   sp_fun1(spname  varchar2  )  return   number

  is

  yearsal   number(7,2);--创建一个函数，输入参数为名字，返回年薪；

begin

select  sal*12+nvl(comm,0)*12  into  yearsal   from    emp   where   ename=spname;

return   yearsal;

end;

/

调用函数：

Var  abc number;              //先定义一个变量，类型为number 

Call sp_fun1(‘SCOTT’)into abc;

包：

包在逻辑上组合过程和函数，他由包规范和包体两部分组成。

用命令create package 创建包：

实例：

Create or   replace   package   sp_pa   is

procedure    update_sal(name   varchar2,newsal   number);

Function   annual_income(name   varchar2)return  number;

End;

/

以上就是包规范，包含过程和函数的说明，但是没有实现（包体）；

创建包体：

Create  or   replace   package  body   sp_pa  is

Procedure   update_sal(name  varchar2,newsal   number)

is

Begin

Update   emp  set  sal=newsal  where   ename=name;

End;

Function   annual_income(name   varchar2)

return   number  

is

annual_sal    number;

Begin

Select    12*sal+nvl(comm,0)*12    into    annual_sal    from   emp

Where   ename=name;

Return    annual_sal;

End;

End;

/

以上创建了包体；

 

调用execute   sp_pa.update_sal('SCOTT',123);

包在返回多值的应用：

返回值为多值的存储过程：

如果要返回一个表，用多个参数显然是不合时宜的，Eu根据部门号返回该部门所有员工信息，那么这时候就要用到包packkage,

首先建立一个包：

create   or   replace   package   testpackage  as

type   test_cursor   is   refcursor;

end  testpackage;

 

在建立存储过程：

create   or   replace    procedure   testpackage

(myno  in   number,p_cursor  out   testpackage.test_cursor)  is

begin

open   p_cursor    for   select * from    emp    where    deptno=myno;

end;

/

 

综合练习：编写一个存储过程，要求可以输入表名，每页显示记录数，当前页。但会记录数，总页数，和返回的结果集。

create   or    replace     package     testpackage2    as

type      test_cursor   is    refcursor;

end   testpackage2;

/

create  or    replace    procedure     fenye

(tablename    in    varchar2,--表名

pagesize   in    number,--每页记录数

pagenow    in   number ,--当前为第几页

myrow   in    number,--总的记录数

mypagecount    out    number,--总页数

p_cursor    out    testpackage2.test_cursor--返回的记录集

) is

--定义部分

v_sql   varchar2(1000);

--定义两个整数

v_begin      number:=(pagenow-1)*pagesize+1;

v_end      number:=pagenow*pagesize;

begin

--执行部分

v_sql:='select * from

(select t1.*,rownum rn from (select * from '||tablename||') t1 where rownum<='||v_end||')

where rn>='||v_begin;

--把游标和sql关联

openp_cursorforv_sql;

---计算myrow和 mypage

--组织一个sql;

v_sql:='select count(*)from '||tablename;

execute     immediate   v_sql    into    myrow;

if     mod(myrow,pagesize)=0   then

mypagecount:=myrow/pagesize;

else

mypagecount:=myrow/pagesize+1;

endif;

--关闭游标

--close p_cursor;

end;

 

在java中调用此分页存储过程：

try{

        //1，加载驱动

        Class.forName("oracle.jdbc.driver.OracleDriver");

        //2，建立连接：

        Connectionct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora","scott","tiger");

        CallableStatementcs=ct.prepareCall("call fenye(?,?,?,?,?,?)");

        //给输入参数赋值，前面3个是输入参数

        cs.setString(1, "emp");//查询emp表

        cs.setInt(2, 5);//每页5条记录

        cs.setInt(3, 2);//显示第2页

        //

        //对于输出参数，要先注册才能使用

        cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

        cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

        cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

        cs.execute();

        //取出总记录数，注意这里的getInt(4),是参数表的位置

        int rownum=cs.getInt(4);//总的记录数

        int pagecount=cs.getInt(5);//总页数

        ResultSet rs=(ResultSet)cs.getObject(6);

        //显示分页存储果果的查询结果

        System.out.println("表的总记录数为："+rownum);

        System.out.println("总页数"+pagecount);

        while(rs.next())

               System.out.println("编号："+rs.getInt(1)+"  名字："+rs.getString(2)+"  薪水："+rs.getInt(6));

        }catch(Exception e)

        {

               e.printStackTrace();     

        }

例外详解：

例外处理分类：

1，  预定义例外：oracle自带的，处理常见的错误，如违背oracle语法等，oracle会提示错误为何种错误；

2，  非预定义例外：处理预定义例外不能处理的例外，很少用到；

3，  自定义例外：自己来定义错误，自己遇见，如发生某种情况，就以例外提醒自己，发生了什么错误；

 

例外例子：

在emp表中，编写一个过程，接受雇员的编号，并显示该雇员的姓名，但是若表中没有这个编号，oracle就会出现预定义例外，我们有必要对这个例外进行处理：

//注意之前要打开oracle显示功能：set serveroutput on;

declare

v_name    emp.ename%type;

begin

select    ename   into    v_name    from    emp    where    empno=&bb;

dbms_output.put_line('名字：'||v_name);

exception

when    no_data_found   then

dbms_output.put_line('表中没有此编号！');

end;

oracle有预定义20多个：

下面介绍常见的几个：

1，  case_not_found: when子句中，如果没有完整的条件分支，就会触发case_not_found的例外，例子如下：

create or    replace procedure    sp_pro6(spno number) is

v_sal    emp.sal%type

begin

select sal into    v_sal from emp where empno=spno;

case

when v_sal<1000then

update emp set   sal=sal+100 where empno=spno;

when v_sal<2000then

update emp set  sal=sal+200 where empno=spno;

end case;

exception

when case_not_found  then

dbms_output.put_line(‘没有包括所有的case’);

end;

/

 

2，dup_val_on_index：在唯一索引对应的列上插入重复的值时，会隐含的触发此例外。如下：表中已经存在10了；

3，invalid-cursor:当试图在不合法的游标上进行操作时，会促发该例外，例如没有打开游标，而提取游标的数据，或者关闭没有打开的游标等

4，invalid_number:当输入数据有误时，会触发该例外。

5，no_data-found :表中无此数据，例子见上

6，too_many_rows:返回多行到一个单行数据上：如：

7，zero_divide:分母为0；

 

8，value_error:如果变量的长度不足以容纳实际数据；如

9,其他预定义例外：

Login_denide  当用户非法登入时，会触发此例外

Not_logged_on  如果用户没有登入就执行dml操作

Storage_error   如果超出了内存空间或者内存被损坏

Timeout_on_resource如果在等待资源时，出现了超时

 

非预定义例外：用于处理与预定义例外无关的oracle错误，使用预定义例外只能处理21个oracle错误，比如在pl/sql块中执行dml语句时，违反了约束规定等，在这样的情况下，也可以处理oracle各种例外；使用较少。

 

自定义例外：oracle对一些错误，是不能检测出来的：如有一个过程，根据雇员编号，更新雇员的薪水，若

表中没有该雇员编号，明显发生错误，但是oracle不能检测出来，这时需要人为定义例外，这就叫做自定义例外。

如：

create    or    replace procedure    ex_test(smnumber)

is

myex    exception;--创建自定义例外

begin

update    emp   set     sal=sal+100   where    empno=sm;

if    sql%not   found   then--如果例外为没有找到数据，

raise  myex;     --执行自定义例外

endif;

exception

when    myext    hen--自定义例外的内容

dbms_output.put_line('没有该编号');

end;