oracle 存储过程学习

最新推荐文章于 2024-09-08 13:34:16 发布

置顶 yanghuatong

最新推荐文章于 2024-09-08 13:34:16 发布

阅读量459

点赞数

分类专栏： Oracle JavaSE 文章标签： oracle 存储数据库

本文链接：https://blog.csdn.net/yanghuatong/article/details/62227780

版权

Oracle 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

JavaSE

1 篇文章 0 订阅

订阅专栏

1.存储过程的优点缺点
1.1 存储过程可以使得程序执行效率更高、安全性更好，因为过程建立之后已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，直接写sql语句会带来安全性问题，如：sql注入
1.2 建立过程不会很耗系统资源，因为过程只是在调用才执行。
1.3 存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。
1.4 使用存储过程使您能够增强对执行计划的重复使用，
由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。
1.5 可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
1.6 代码精简一致，一个存储过程可以用于应用程序代码的不同位置。
1.7 增强安全性：
a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；
b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；
c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。

1.8缺点：
1、大量的利用过程，会对服务器压力比较大。
2、可移植行，如果数据库的类型发生变化，存储过程需要大量的重写

2.语法结构

存储过程创建语法：
       create or replace procedure 存储过程名（param1 in type，param2 out type）
as

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

    Select count(*) into 变量1 from 表A where列名=param1；

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1；

       Dbms_output。Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

    End if;

Exception

    When others then

       Rollback;

End;


注意事项：
1，  存储过程参数不带取值范围，in表示传入，out表示输出，
 In out  即可输入也可输出，不填写 默认为：传入
2，  变量带取值范围，后面接分号
3，  在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4，  用select 。。。into。。。给变量赋值
5，  在代码中抛异常用 raise+异常名

以命名的异常
命名的系统异常                          产生原因
ACCESS_INTO_NULL                   未定义对象
CASE_NOT_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置
ELSE 时
COLLECTION_IS_NULL                集合元素未初始化
CURSER_ALREADY_OPEN          游标已经打开
DUP_VAL_ON_INDEX                   唯一索引对应的列上有重复的值
INVALID_CURSOR                 在不合法的游标上进行操作
INVALID_NUMBER                       内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND                        使用 select into 未返回行，或应用索引表未初始化的 

TOO_MANY_ROWS                      执行 select into 时，结果集超过一行
ZERO_DIVIDE                              除数为 0
SUBSCRIPT_BEYOND_COUNT     元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT       使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR                             赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED                           PL/SQL 应用程序连接到 oracle 数据库时，提供了不
正确的用户名或密码
NOT_LOGGED_ON                       PL/SQL 应用程序在没有连接 oralce 数据库的情况下
访问数据
PROGRAM_ERROR                       PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL
系统包
ROWTYPE_MISMATCH                宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL                             使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR                        运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID                         无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE         Oracle 在等待资源时超时

3.范例

创建包
这里写图片描述

4.java 调用存储过程

public static void main(String[] args) throws Exception {

        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:MYORCL";
        String userName = "scott";
        String password = "scott";
        Connection conn = null;
        ResultSet rs = null;
        Class.forName(driver);
        conn = DriverManager.getConnection(url, userName, password);

        String sql = "{call p_stu.getEmpByPages2(?,?,?,?)}";
        CallableStatement cs = conn.prepareCall(sql);
        cs.setInt(1, 1);
        cs.setInt(2, 10);
        cs.registerOutParameter(3, OracleTypes.CURSOR);
        cs.registerOutParameter(4, OracleTypes.NUMBER);

        cs.execute();

        int count = cs.getInt(4);
        rs = (ResultSet) cs.getObject(3);
        System.out.println("count: " + count);
        while (rs.next()) {
            System.out.println("empno: " + rs.getInt("empno") + " name: "
                    + rs.getString("ename"));
        }

        if (rs != null) {
            rs.close();
        }

        if (cs != null) {
            cs.close();
        }

        if (conn != null) {
            conn.close();
        }

    }

5.存储过程的调试

PL/SQL Developer调试存储过程步骤
　　一、定位你的procedure
1。在屏幕左边的对象浏览器中展开procedure
2。找到你的procedure

二、打开测试窗口
1。在你的procedure上点击右键
2。在弹出的菜单中选择test
3。PL/SQL Devoloper就会打开一个测试窗口并自动生成一个调用块
4。在测试窗口的下方输入你的procedure的入口参数

三、打开编辑窗口
1。在你的procedure上点击右键
2。在弹出的菜单中选择edit
3。PL/SQL Devoloper就会打开编辑窗口
4。在需要的地方设置断点

四、开始调试
1。回到调试窗口
2。打开debug菜单，你就会看见start项已经可以用了，点击它
3。接下来就可以用调试窗口上的按钮进行单步跟踪
　　所有的参数，不管是数字类型，还是字符类型，均不要加引号
　　PL/SQL Developer中，存储过程无法调试的问题解决办法
　　在Oracle10中新建了一个用户，然后编写存储过程在PL/SQL Developer中调试，提示
　　ORA-0131: Insufficient privileges.
Note: Debugging requires the DEBUG CONNECT SESSION system privilege.
　　用管理员帐号登录后，执行
　　grant Debug Connect Session to sky_test;
　　命令后，再进行调试，一切OK。
　　
6.书写存储过程规范和注意事项

从性能角度讲，如何优化存储过程都不为过，但规范的书写习惯是写好存储过程的前提。以ORACLE 9I、PL/SQL Developer的环境为例，提几条建议：

1、代码清晰、整齐、缩进一致、注释充分。PL/SQL DEV提供了代码格式化的工具：点击Edit菜单，点击【PL/SQL Beautifier】，会自动排版布局。建议每次编写完成后都执行一下这个工具。

2、尽量不要拼装SQL。
理由：（1）PL/SQL语言不善于处理字符串，在存储过程中拼装SQL会使代码看起来很乱，维护起来很费力；（2）拼装的SQL只能动态执行，没有绑定变量，性能也不高；（3）拼装的过程中出错，调试起来很麻烦；（4）错误只能在运行期发现，而不能在编译期发现；（5）拼装的SQL在优化阶段很麻烦，一条已经优化好的语句要拆开放到代码里面去才行。

3、具体问题具体处理，不要偷懒。比如：WEB中的分页一般要进行两次查询，一次获得记录的总数，一次取得当前页的数据。很多时候，取得记录数和取得数据是不同的，把他们分别进行处理，能大大提高性能。

4、一个存储过程只干一件事情，不要尝试去写一个功能完备的存储过程。

5、编写好语句后，先按F5看看执行计划。
学会看执行计划，分析语句是否能更好地利用到索引。在数据量大的情况下，最好把参数代进去，实际执行一下，验证语句是否与所想的一样快。

6、数据量大，语句优化了还是执行很慢的时候，试试把大表变小表，小表再连接的方法。

例如：语句需要连接三个表，执行得非常慢。如果采用了临时表，
先把其中两个表的数据汇总后存储到临时表，再和大的表连接，性能会高出很多。虽然一条语句写成了三条，但是性能反而会提高。

7、一定一定不要使用游标。
与上面的原则相反，大多数时候，尽量用一条SQL完成。SQL是批量的，
而且可以在CPU的层面并行执行，在RAID的层面可以并行读取，如果使用游标，就只能一条一条的执行，性能绝对高不了。