Oracle 存储过程学习

目录

Oracle 存储过程 .............................................................................................................................. 1 Oracle存储过程基础知识 ............................................................................................................... 1 Oracle存储过程的基本语法 ........................................................................................................... 2 关于Oracle存储过程的若干问题备忘 .......................................................................................... 4

1. 在Oracle中，数据表别名不能加as。 .......................................................................... 4 2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。 ................................................................................................... 5 3. 在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。 ........................................................................................................................... 5 4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错....................................................................................................................................... 5 5. 在存储过程中，关于出现null的问题 ........................................................................... 5 6. Hibernate调用Oracle存储过程 ..................................................................................... 6 用Java调用Oracle存储过程总结 ................................................................................................. 6

一、 无返回值的存储过程 ............................................................................................... 6 二、 有返回值的存储过程（非列表） ........................................................................... 8 三、 返回列表 ................................................................................................................... 9 在存储过程中做简单动态查询 ..................................................................................................... 11

一、 本地动态SQL ......................................................................................................... 12 二、 使用DBMS_SQL包 ................................................................................................. 13 Oracle存储过程调用Java方法 .................................................................................................... 16 Oracle高效分页存储过程实例 ..................................................................................................... 17

Oracle存储过程基础知识

商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。 存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。

要创建一个过程对象（procedural object），必须有 CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有 CREATE ANY PROCEDURE 权限。执行 procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示：

grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子：

execute MY_PROCEDURE( 'ONE PARAMETER');

存储过程(PROCEDURE)和函数(FUNCTION)的区别。

function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。

本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是： 存储过程是命令, 而函数是表达式的一部分。比如：

select max(NAME) FROM 但是不能 exec max(NAME) 如果此时max是函数。

PACKAGE是function，procedure，variables 和sql 语句的组合。package允许多个procedure使用同一个变量和游标。

创建 procedure的语法：

可以使用 create or replace procedure 语句， 这个语句的用处在于，你之前赋予的excute权限都将被保留。

IN, OUT, IN OUT用来修饰参数。

IN 表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。 OUT 表示PRCEDURE 通过这个变量将值传回给调用者。 IN OUT 则是这两种的组合。 authid代表两种权限：

定义者权限（difiner right 默认），执行者权限（invoker right）。

定义者权限说明这个procedure中涉及的表，视图等对象所需要的权限只要定义者拥有

权限的话就可以访问。

执行者权限则需要调用这个 procedure的用户拥有相关表和对象的权限。

Oracle存储过程的基本语法

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

7. 带参数的cursor

8. 用pl/sql developer debug

连接数据库后建立一个Test WINDOW

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

9. Pl/Sql中执行存储过程

注：在EXECUTE IMMEDIATE STR语句是SQLPLUS中动态执行语句，它在执行中会自动提交，类似于DP中FORMS_DDL语句，在此语句中str是不能换行的,只能通过连接字符"||"，或着在在换行时加上"-"连接字符。

关于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...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

可以在该语法之前，先利用select count(*) 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，定义如下：

select sum(vcount) into fcount from A where bid='xxxxxx'; 如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

if fcount is null then

fcount:=0; end if;

这样就一切ok了。

6. Hibernate调用Oracle存储过程

用Java调用Oracle存储过程总结

一、

无返回值的存储过程

例: 存储过程为(当然了，这就先要求要建张表TESTTB,里面两个字段（I_ID，I_NAME）。

二、 有返回值的存储过程（非列表）

例：存储过程为：

注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。

三、 返回列表

由于Oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，

1. 建一个程序包。如下：

2.

在Java里调用时就用下面的代码： 在这里要注意，在执行前一定要先把Oracle的驱动包放到class路径里，否则会报错的。

在存储过程中做简单动态查询

在存储过程中做简单动态查询代码 ,例如:

一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系

统控制语句，可以通过使用动态SQL来实现。

首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明：

一、 本地动态SQL

本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。 1、 本地动态SQL执行DDL语句：

需求：根据用户输入的表名及字段名等参数动态建表。

到这里，就实现了我们的需求，使用本地动态SQL根据用户输入的表名及字段名、字段类型等参数来实现动态执行DDL语句。

2、 本地动态SQL执行DML语句。

需求：将用户输入的值插入到上例中建好的dinya_test表中。

执行存储过程，插入数据到测试表中。 在上例中，本地动态

SQL执行DML语句时使用了using子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态SQL的时候，使用RETURNING INTO 子句，如：

二、 使用DBMS_SQL包

使用DBMS_SQL包实现动态SQL的步骤如下：

A、先将要执行的SQL语句或一个语句块放到一个字符串变量中。 B、使用DBMS_SQL包的parse过程来分析该字符串。 C、使用DBMS_SQL包的bind_variable过程来绑定变量。 D、使用DBMS_SQL包的execute函数来执行语句。

1、使用DBMS_SQL包执行DDL语句

以上过程编译通过后，执行过程创建表结构：

2、 使用DBMS_SQL包执行DML语句

需求：使用DBMS_SQL包根据用户输入的值更新表中相对应的记录。 查看表中已有记录：

执行过程，根据用户输入的参数更新表中的数据： 执行过程后将第二条的name字段的数据更新为新值csdn_dinya。这样就完成了使用dbms_sql包来执行DML语句的功能。

使用DBMS_SQL中，如果要执行的动态语句不是查询语句，使用DBMS_SQL.Execute或DBMS_SQL.Variable_Value来执行，如果要执行动态语句是查询语句，则要使用DBMS_SQL.define_column定义输出变量，然后使用DBMS_SQL.Execute, DBMS_SQL.Fetch_Rows, DBMS_SQL.Column_Value及DBMS_SQL.Variable_Value来执行查询并得到结果。

总结说明：

在Oracle开发过程中，我们可以使用动态SQL来执行DDL语句、DML语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同，在DDL中使用绑定变量是非法的（bind_variable(v_cursor,’:p_name’,name)），分析后不需要执行DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用DBMS_SQL.PARSE时执行的，所以DBMS_SQL.EXECUTE也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor)部分可以不要。

Oracle存储过程调用Java方法

存储过程中调用Java程序段 软件环境：

1、 操作系统：Windows 2000 Server

2、 数 据 库：企业版 3、 安装路径：C:\ORACLE 实现方法：

1

23、 java Test

4、 SQL> conn system/manager

SQL> grant create any directory to scott; SQL> conn scott/tiger

SQL> create or replace directory test_dir as 'd:\'; 目录已创建。

SQL> create or replace java class using bfile(test_dir,'TEST.CLASS') 2 /

Java 已创建。

SQL> select object_name,object_type,STATUS from user_objects; SQL> create or replace procedure test_java as language java

name 'TEST.main(http://ng.String[])'; /

过程已创建。

SQL> set serveroutput on size 5000 SQL> call dbms_java.set_output(5000); 调用完成。

SQL> execute test_java;

HELLO THIS iS A Java PROCEDURE