oracle数据库procedure&package新手参考

admin管理员组

文章数量:1794759

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 is str 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) as tmp1 varchar2(50); tmp11 varchar2(50); tmp2 varchar2(50); tmp22 varchar2(50); tmp3 number; tmp4 number; tmp5 date; tmp6 number; tmp7 number; begin select 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 is type 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 is stu 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 is stu 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 is procedure 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 is cursor test_cursor is select id,name,age from 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

本文标签： 新手数据库oracleprocedurepackage