Oracle笔记：pl/sql过程、函数、包

    pl/sql简介

    pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展，是非常强大的数据库过程语言。

    优点：1) 提高应该程序的运行性能；2) 模块化的设计思想）；3) 减少网络传输量；4) 提高安全性。（可以把某些字段等封装在过程中）
    缺点：移植性不好。

    pl/sql编写规范 

    1.注释：
    单行注释：--
    多行注释：/*...*/
    2.标识符号命名规范
    定义变量时，建议用v_作为前缀；
    定义常量时，建议用c_作为前缀；
    定义游标时，建议用_cursor作为后缀；
    定义例外时，建议用e_作为前缀。

    常用命令：

    查看错误信息：show error;
    调用过程：1)exec 过程名(参数值1，参数值2...)； 2)call 过程名(参数值1，参数值2...)；
    打开输出选项：set serveroutput on

    pl/sql块

    块是pl/sql的基本程序单元,主要包括过程（存储过程）、函数、触发器、包。

    块由三个部分构成：定义部分、执行部分、例外处理部分。

    例：（scott表的emp表）。
    declare --定义部分（可选）
       v_ename varchar2(5);
       v_sal number(7,2);
    begin --执行部分（必须）
       select ename,sal into v_ename,v_sal from emp where empno=&no;
       dbms_output.put_line('用户名：'||v_ename ||' 工资：'||v_sal);
       --把查询结果放入变量中，并在控制台输出。
    exception --例外处理部分（可选）
       when no_data_found then
       dbms_output.put_line('请输入正确的员工编号！');
    end;

    过程

    用于执行特定的操作。建立过程的命令： create procedure
    例：
    create procedure a_pro1(aName varchar2,newSal number) is
    begin
      --根据员工姓名，修改员工工资。
      update emp set sal=newSal where ename =aName;
    end;

    执行该过程：
    exec a_pro1('scott',3532);

    1）无返回值的存储过程
    例：有一张表book,有书号、书名、出版社三个字段。请编写一个过程向该表中插入数据。
    --in表示这是一个输入参数（默认为in）
    --out表示这是一个输出参数
    create or replace procedure test_pro(tBookID in number,tBookName in varchar2,tPublishHouse in varchar2) is
    begin
      insert into book values(tBookID,tBookName,tPublishHouse);
    end;

    2) 有返回值的存储过程（非列表）
    例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
    create or replace procedure test_pro2(tno in varchar2,tName out varchar2) is
    begin
     select ename into tName from emp where empno=tno;
    end;

    3）有返回值的存储过程（列表[结果集]）
    例：编写一个过程，输入部门号，返回该部门所有雇员信息。
    分析：由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表也不例外，但由于返回值是集合，所以要用package,要分两部分：
    a) 创建一个包
     create or replace package test_package as
        type test_cursor is ref cursor;
     end test_package;
    b)建立存储过程
     create or replace procedure test_pro1(tno in number,t_cursor out test_package.test_cursor) is
     begin
       open t_cursor for select * from emp where deptno=tno;
     end;

    函数

    用于返回特定的数据，当建立函数时，在函数头头部必须包含return子句，而在函数体内必须包含return语句返回的数据，可以使用create function来建立函数。
    例：
    --输入雇员的姓名，返回该雇员的年薪。
    create function test_function1(testName varchar2) return
    number is yearSal number(7,2);
    begin
       select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=testName;
       return yearSal;
    end;

    调用该函数：
    var mySal number;
    call test_function1('SCOTT') into:mySal;

    包

    用于在逻辑上组合过程和函数，它由包规范和包体两部分组成，可以使用create package命令创建包。
    例：
    --创建一个包test_package,声明该包有一个过程和一个函数。
    create package test_package is
    procedure update_sal(name varchar2,newsal number);
    function annual_income(name varchar2) return number;
    end;

    包的规范只包含了过程和函数的说明，包体用于实现包规范中的过程和函数。
    建立包体可以使用create package body命令：
    create or replace package body is
    procedure test_package......
    function annual_income......

    调用包的过程或函数
    在过程和函数前需带包名，如果要访问其它方案的包，还需要在包名前加方案名。
    exec 包名.过程/函数；