oracle存储过程.docx

oracle存储过程.docx

《oracle存储过程.docx》由会员分享，可在线阅读，更多相关《oracle存储过程.docx（13页珍藏版）》请在冰豆网上搜索。

oracle存储过程

前言

该文章主要讲解Oracle中存储过程（procedures）、包（package）、函数（function）和类型（type）的使用。

主要讲解存储过程，其它只是顺带讲解一下

Oracle存储过程-procedures

Plsql创建存储过程和执行存储过程

建存储过程　

　　在plsql左边的浏览窗口选择procedures，会列出所有的存储过程，右击文件夹procedures单击菜单“new",弹出templatewizard窗口。

在窗口中填充存储过程名称和参数。

然后单击ok，右边出现建立存储过程的代码窗口，我们就可以在里面填写编写存储过程代码。

写完之后，可以点击工具栏上的“齿轮”图标（或者F8快捷键）来编译存储过程并保存到DB中。

另外，单击“保存”按钮，可将创建存储过程的sql语句保存在文件中，作备分之用

Plsql中测试（调用）存储过程的方式

1、在plsql左边的浏览窗口右键刚创建的存储过程，选择“TEST‘，右边会出现新的测试窗口。

在创建下侧可以设置参数。

然后点击F8执行调用存储过程操作。

 说明:

在测试窗口的下面你可以看到有一个参数列表,作输入参数值和查看out型参数值用在调试代码上方有个工具条：

 第一个放大镜形（startdebugger），开始调试。

　第二个绿色三角形（run）,执行。

 第三个stepinto,单步调试。

 进入调试代码窗口后，可随意设置断点，方法就不用说了吧~ 

2、plsql中其它方式调用存储过程

在sql的执行窗口中只能这样调用"callOUT_TIME（）;"，这样执行就是把”callOUT_TIME（）;“当成一个sql语句，而execOUT_TIME（）;不是一个sql语句，是一个执行体，执行体调用必须在命令窗口。

要在sql窗口中之行也可以，这样调用：

begin

 OUT_TIME（）;

end;

注：

在命令窗口中两种方式都可以调用

execOUT_TIME（）;--相当于执行一个plsql块，即把”OUT_TIME（）“看成plsql块调用。

callOUT_TIME（）;--相当于用一个方法“OUT_TIME（）”，把“OUT_TIME（）”看成一个方法。

Oracle中调用存储过程

DECLARE 

 p1VARCHAR2（100）;

 p2VARCHAR2（100）;

 p3VARCHAR2（100）;

BEGIN

 p1:

='dd';

 p2:

='3';

 testprocedure（p1,p2,''）;

END;

Oracle存储过程的基本语法

基本结构

CREATEORREPLACEPROCEDURE存储过程名字

（

   参数1INNUMBER,

   参数2OUTNUMBER

）IS

变量1INTEGER:

=0;//变量的生命并初始化

变量2DATE;

BEGIN

END存储过程名字

说明：

（1）存储过程参数不带取值范围，in表示传入，out表示输出,inout表示输入输出参数。

（存储过程的返回值是以参数形式返回的）

（2）变量带可以取值范围，后面接分号

（3）IS可以用AS代替

（4）当存储过程没有参数时，可以不带后面的括号，调用时候也可以不带。

（5）oracle声明变量时，变量名称在前面，变量类型在后面

（6）变量只能在声明块定义，不能在plsql块中定义。

（plsql块就是begin和endprocedurename之间的部分）

SELECT...INTOvarnameFROM...给变量赋值

 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常（如果没有记录抛出NO_DATA_FOUND）

 例子：

 BEGIN

 SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;

 EXCEPTION

 WHENNO_DATA_FOUNDTHEN

     xxxx;

 END;

 ...

通配类型操作符

1、tab.fieldname%type:

 通配表的某个字段的类型，如

mynametab1.name%type;

变量myname的类型就是tab1中的name的类型。

2、tab%rowtype:

 通配表的所有字段类型（实际上相对于一个对象类型）

usetab1%rowtype;

use就是一个对象（它里面的属性就是tab1表的所有字段）

IF判断

 IFV_TEST=1THEN

   BEGIN

      dosomething

   END;

 ENDIF;

while循环

 WHILEV_TEST=1LOOP

 BEGIN

 XXXX

 END;

 ENDLOOP;

变量赋值

 V_TEST:

=123;

打印信息

使用内置对象DBMS_OUTPUT的put_line方法打印信息：

DBMS_OUTPUT.put_line（'xx'）;

字符串

Oracle存储过程中字符串只能用单引号

for..in..循环遍历数组

在Oracle中本是没有数组的概念的，数组类型需要我们自己去定义（用Table定义），每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历。

//array是myPackage下定义的TestArray类型（该类型是用Table定义的）

createorreplaceproceduretest（myarrayinmyPackage.TestArray）as

inumber;

begin

i:

=1; 

foriin1..myarray.countLOOP     

dbms_output.putline（i）;   

 endLOOP;

endtest;

立即执行sql语句

EXECUTEIMMEDIATEsqlclause

用pl/sqldeveloperdebug

 连接数据库后建立一个TestWINDOW

 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

游标（cursor）

Oracle中Cursor是非常有用的，它是从表中检索出结果集，一般用于遍历临时表中的查询结果。

游标分类（类型）：

1、隐示游标；

1）.对于Select…INTO…语句，一次只能从数据库中获取到一条数据，对于这种类型的DMLSql语句，就是隐式Cursor。

例如：

Select/Update/Insert/Delete操作。

2）作用：

可以通过隐式Cusor的属性来了解操作的状态和结果，从而达到流程的控制。

Cursor的属性包含：

SQL%ROWCOUNT整型代表DML语句成功执行的数据行数

SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功

SQL%NOTFOUND布尔型与SQL%FOUND属性返回值相反

SQL%ISOPEN布尔型DML执行过程中为真，结束后为假

3）隐式Cursor是系统自动打开和关闭Cursor.

4）可以用作参数被传递

Begin

UpdateempSetSAL=SAL+0.1WhereJOB='CLERK';

IfSQL%FoundThen

DBMS_OUTPUT.PUT_LINE（'已经更新！

'）;

Else

DBMS_OUTPUT.PUT_LINE（'更新失败！

'）;

EndIf；

End；

2、显示游标-CURSOR

对于从数据库中提取多行数据，就需要使用显式Cursor。

显式Cursor的属性包含：

游标的属性  返回值类型  意   义

 %ROWCOUNT  整型 获得FETCH语句返回的数据行数 

%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假 

%NOTFOUND  布尔型与%FOUND属性返回值相反

 %ISOPEN布尔型游标已经打开时值为真，否则为假 

不能用作参数被传递

3.REF游标（动态Cursor）

显式游标的使用

1、声明游标：

cursor  游标名（参数名类型） is  select语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。

如果定义了参数，则必须在打开（open）游标时传递相应的实际参数。

2、打开游标：

open  游标名（实参）;//参数可选

3、移动游标，并获取游标所在行数据到变量中：

fetch  游标名 into  变量;//注意变量的类型要自己定义

4、游标语法:

close  游标名;

遍历循环游标

1、For循环游标

循环自动打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。

处理完后自动关闭游标。

For变量名In游标名--此处的变量可以是隐式变量（不需要定义）

Loop

数据处理语句；

EndLoop；

---以下是遍历person表的例子

createorreplaceprocedureaais

Cursormycurisselect*fromperson;

pperson%rowtype;

begin

forpinmycurLoop

DBMS_OUTPUT.PUT_LINE（p.name）;

endloop;

endaa;

2、Loop循环显示游标

Loop

Fatch游标名InTo变量；

ExitWhen游标名%NotFound；

EndLoop；

自定义定义游标类型：

TYPE游标类型名REFCURSOR;

游标使用例子

实例1

createorreplaceproceduretest（）as

Cursorcurisselectnamefromstudent;

namevarchar（20）;

begin

fornameincursorLOOP

begin

dbms_output.putline（name）; 

end;

endLOOP;

endtest;

实例2

v_rowtab1%rowtype; -- 匹配tab1表中一行所有的数据类型

cursor v_cur is select * from t_test;-- 声明游标

begin

  open v_cur;-- 打开游标

  loop

    fetch v_cur into v_row;-- 将游标所在行的数据转存到v_row中

    exit when v_cur%notfound; -- 当游标到最后一行时跳出

    dbms_output.put_line（'id='||v_row.t_id||'  name='||v_row.t_name||'  msg='||v_row.t_msg）;

  end loop;

  close v_cur;-- 关闭游标

exception

  when others then dbms_output.put_line（'throwexception:

others'）;

end;

关于oracle存储过程的若干问题备忘

1.在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a;--正确

select a.appname from appinfo as a;--错误

 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

  select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译

  select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：

Compilation 

  Error:

 PLS-00428:

 an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"nodatafound"异常。

  可以在该语法之前，先利用selectcount（*）from查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行

select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示

ORA-01422:

exact fetch returns more than requested number of rows

5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：

create table A（

id varchar2（50） primary key not null,

vcount number（8） not null,

bid varchar2（50） not null -- 外键 

）;

如果在存储过程中，使用如下语句：

select sum（vcount） into fcount from A where bid='xxxxxx';

如果A表中不存在bid="xxxxxx"的记录，则fcount=null（即使fcount定义时设置了默认值，如：

fcountnumber（8）:

=0依然无效，fcount还是会变成null），这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

if fcount is null then

    fcount:

=0;

end if;

这样就一切ok了。

6.Hibernate调用oracle存储过程

        this.pnumberManager.getHibernateTemplate（）.execute（

                new HibernateCallback（） {

                    public Object doInHibernate（Session session）

                            throws HibernateException, SQLException {

                        CallableStatement cs = session

                                .connection（）

                                .prepareCall（"{call modifyapppnumber_remain（?

）}"）;

                        cs.setString（1, foundationid）;

                        cs.execute（）;

                        return null;

                    }

                }）;

Oracle的包-package

封装过程（procedure）、函数（function）和变量。

（包的创建在包视图下创建-跟存储过程不是同一个树节点）

注意，在包中声明的过程（procedure）和函数（function）必须在包体中定义实现。

基本语法：

createpackage包名

as

变量声明

存储过程声明

自定义函数声明

end包名;

/

createpackage包名

as

存储过程的代码实现

自定义函数的代码实现

end包名;

/

包的调用

包名.方法名（参数）;

包名.变量

Oracle的函数-function

基本语法：

create  function  函数名（参数）

return  返回值类型

as

  变量声明、初始化

begin

  ........

  return  返回的值;

exception

  .......

end  函数名;

参数：

in  入参

注：

只有入参的类型。

在存储过程和自定义函数中的参数的传递（入参和出参）不能使用%type或%rowtype匹配，不能使用空值null，但是存储过程可以返回空值。

Oracle的类型-type

Oracle提供自定义类型功能，用户可以在plsql的浏览器窗口的type下面或者在存储过程或者函数下为oracle定义数据类型。

用户可以定义记录数据类型（Record）和记录表类型（Table）

//Record--->ObjectTable--->List