oracle数据库procedure&package新手参考

         Hi，我是frank。

        今天尝试写自己的blog，因为其实通过CSDN上面别人的博客学到了很多知识，对我本人而言重要性仅次于书籍。这是一个开放的平台，我也希望能在帮助别人的同时记录下自己的成长。上来直接写数据库可能太突兀，下一次我想先写我的工作方向和内容，以便同行和前辈们能够给予帮助和指导。

        第一篇blog，本来想写解决toad无法连接oracle或是监听程序的问题，但是目前数据库运行良好，等下次出现类似情况，我再写吧hhh
        好了言归正传，我想写的是oracle中的procedure（存储过程）和package（包）

        首先存储过程是什么？我们可以看做是一个PL/SQL语句的集合。百度百科比我说的好，可自行百度。

基础语法是：

create or replace procedure name is/as
局部变量声明
例如：temp1 varchar2(40);
begin
    sql语句 例如：select name from student;
end;

细讲起来内容太多，这里给出一个我之前写的例子

CREATE OR REPLACE procedure SCOTT.my isstr varchar2(30);begin    str:='231';    if NVL(str,1)=1 then        dbms_output.put_line('有毒!');    else         dbms_output.put_line('正常!');    end if;end;/

exec my;

exec 过程名

用来测试过程是否正常输出，当然你也可以jdbc连接Java测试。

nvl函数用来判断是否为null，如果不为null执行else输出“正常”，

为null时，值为第二个参数值1，执行输出“有毒”。。。

another~hhh略长，原理相同

CREATE OR REPLACE procedure SCOTT.prc_entry(para1 in varchar2,para2 in number,para3 in varchar2)astmp1 varchar2(50);tmp11 varchar2(50);tmp2 varchar2(50);tmp22 varchar2(50);tmp3 number;tmp4 number;tmp5 date;tmp6 number;tmp7 number;beginselect to_char(sysdate,'yyyymmdd') into tmp1 from dual;select to_char(sysdate,'dd') into tmp11 from dual;select sq_ae.nextval into tmp3 from dual;select courseNumber into tmp2 from activity where actId=para1 and courseId=1;insert into ACCOUNTENTRYDETAIL(certifiNumber,entryId,courseNumber,debitAccount) values(tmp1||tmp11,tmp3,tmp2,para2);select sq_ae.nextval into tmp4 from dual;select courseNumber into tmp22 from activity where actId=para1 and courseId=2;insert into ACCOUNTENTRYDETAIL(certifiNumber,entryId,courseNumber,creditAccount) values(tmp1||tmp11,tmp4,tmp22,para2);insert into accountEntrySummary values(sq_aes.nextval,tmp1||tmp11,tmp1,para3,para2,'制单','总经理','副总经理');select sysdate into tmp5 from dual;select sq_dc.nextval into tmp6 from dual;insert into dailyCourse (id,accountPeriod,clearTime,courseNumber,debitAccount)values(tmp6,'会计结算日',tmp5,tmp2,para2);select sq_dc.nextval into tmp7 from dual;insert into dailyCourse (id,accountPeriod,clearTime,courseNumber,creditAccount)values(tmp7,'会计结算日',tmp5,tmp22,para2);end;/

*注意 in代表传入参数 out代表传出参数，变量名称最好写规范，我这里偷懒了。。。

同时这里也用到了序列，使用nextval让它自增哦

------------------------------------------------------------------------------------------------------------

package就是包，封装函数，存储过程，游标等等。因为工作要用到package，所以还是很重要。

如何创建包，首先包分为两部分，package spec（包头）和package body（包体）。

包实际上实现了复用，以及对函数和存储过程的分类管理，便于后期维护修改（记得写注释！）

所以通俗地说，就叫打包。把过程放在一个包里，看起来就清爽~

不写语法了直接看例子：

包头的定义：

CREATE OR REPLACE package SCOTT.pkg is----------------------------------------------DATE           AUTHOR     --201612.12      frank--------------------------------------------procedure find_student (v_id in test.id%type);procedure infor_student (c_id in test.id%type,               c_name in test.name%type,               c_age in test.age%type);end pkg;/

包体的定义：

CREATE OR REPLACE package body SCOTT.pkg is ----------------------------------------------DATE           AUTHOR     --201612.12      frank----------------------------------------------查询信息    procedure find_student (v_id in test.id%type)                is                v_name varchar2(30);                v_age number;                begin                    select name,age into                     v_name,                    v_age                    from test                    where id=v_id;                    dbms_output.put_line('姓名:'||v_name||' 年龄:'||v_age);                end;  --写入信息                                 procedure infor_student(c_id in test.id%type,               c_name in test.name%type,               c_age in test.age%type)               is               begin                    insert into test values                   (c_id,                   c_name,                   c_age);                   dbms_output.put_line('写入信息成功!');               exception                    when dup_val_on_index then                   dbms_output.put_line('已存在信息!');                                           end;end pkg;/

包里有两个存储过程，这里最后加入了异常处理。

用到的表名叫test 包含三列 （id，name，age）

-------------------------------------------------------------------------------------------
包头的定义：

CREATE OR REPLACE package SCOTT.record_pkg istype record_type is record(name test.name%type,age test.age%type);procedure record_proc;function record_func return record_type;end record_pkg;/

record表示的是记录，包含三个字段 ID，name，age
包里有一个function和一个procedure
包体的定义：

CREATE OR REPLACE package body SCOTT.record_pkg is--查询并放入record（单行）procedure record_proc isstu record_type;begin    select name,age    into stu     from test    where id=1;    DBMS_OUTPUT.PUT_LINE(stu.name||stu.age);end;function record_func return record_type isstu record_type;begin    select name,age    into stu     from test    where id=1;    return stu;end;end record_pkg;/

DBMS_OUTPUT.PUT_LINE();是数据库的输出类似Java中常用的system.out.print();
再来一例：
包头的定义：

CREATE OR REPLACE package SCOTT.cursor_pkg isprocedure cursor_proc;procedure imcursor_proc;end cursor_pkg;/

包体的定义：

CREATE OR REPLACE package body SCOTT.cursor_pkg is--for游标查询procedure cursor_proc is    begin        for rec in        (select         id,name,age         from test)    loop        dbms_output.put_line(rec.id||' '||rec.name||' '||rec.age);    end loop;    end;--隐式游标 写入表并显示procedure imcursor_proc iscursor test_cursor isselect id,name,agefrom test;test_rec test_cursor%rowtype;    begin        open test_cursor;    loop        fetch test_cursor into test_rec;        exit when test_cursor%notfound;        insert into t values(        test_rec.id,        test_rec.name,        test_rec.age);        dbms_output.put_line(test_rec.id||' '||test_rec.name||' '||test_rec.age);    end loop;    close test_cursor;    end;    end cursor_pkg;/

测试两种游标，包里使用两个过程。
以for游标测试为例：

同样使用exec 包名.存储过程名
可以看到，输出了表中的记录tom 和 jerry的id，name还有age~
OK！到这里粗略的说了一些，给了几个例子，希望对你有用~
                                                                                                                                           2016.12.13