调用oracle存储过程详解课件

《调用oracle存储过程详解课件》由会员分享，可在线阅读，更多相关《调用oracle存储过程详解课件（34页珍藏版）》请在金锄头文库上搜索。

1、第7讲 PL/SQL 块的结构和实例,PL/SQL 块的结构和实例介绍,学习目标 理解oracle 的pl/sql 概念 掌握pl/sql 编程技术（包括编写过程、函数、触发器.） pl/sql 的介绍 pl/sql 是什么 pl/sql(procedural language/sql)是oracle 在标准的sql 语言上的扩展。 pl/sql 不仅允许嵌入sql 语言，还可以定义变量和常量，允许使用条件语句和 循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。,PL/SQL 块的结构和实例,学习必要性 1.提高应用程序的运行性能 2.模块化的设计思想【分页的过程，订单的过程

2、，转账的过程。】 3.减少网络传输量 4.提高安全性（sql 会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL 就不会） 为什么PL/SQL 会快呢？ 不好的地方： 移植性不好（换数据库就用不了）,PL/SQL 块的结构和实例,sqlplus 开发工具 sqlplus 是oracle 公司提供的一个工具，这个因为我们在以前介绍过的： 举一个简单的案例： 编写一个存储过程，该过程可以向某表中添加记录。 一个简单的存储过程 CREATE OR REPLACE PROCEDURE proc_helloworldISBEGIN DBMS_OUTPUT.put_line (Hello Wor

3、ld!);END;/,PL/SQL 块的结构和实例,需要在屏幕上显示出DBMS_OUTPUT.put_line的输出字符串，需要做一个小小的设置SQL show serveroutputserveroutput OFF SQL set serveroutput on SQL show serveroutput ON SIZE 10000 FORMAT WORD_WRAPPED 1.创建一个简单的表 Sql 代码 1. create table mytest(name varchar2(30),passwd varchar2(30);,PL/SQL 块的结构和实例,2.创建过程 Sql 代码 1

4、. create or replace procedure sp_pro1 is 2. begin-执行部分 3. insert into mytest values(杨世顺,m1234)； 4. end; 5. / replace:表示如果有sp_pro1，就替换 如何查看错误信息：show error; 如何调用该过程： 1）exec 过程名（参数值1，参数值2.）；SQL执行 2）call 过程名（参数值1，参数值2.）；程序调用,PL/SQL 块的结构和实例,pl/sqln developer开发工具 pl/sql developer 是用于开发pl/sql 块的集成开发环境(ide)

5、，它是一个独立 的产品，而不是oracle 的一个附带品。 举一个简单案例： 编写一个存储过程，该过程可以删除某表记录。 Sql 代码 1. create or replace procedure sp_pro2 is 2. begin-执行部分 3. delete from mytest where name=杨世顺； 4. end;,PL/SQL 块的结构和实例,pl/sql 基础 pl/sql 介绍 开发人员使用pl/sql 编写应用模块时，不仅需要掌握sql 语句的编写方法， 还要掌握pl/sql 语句及语法规则。pl/sql 编程可以使用变量和逻辑控制语句， 从而可以编写非常有用的功

6、能模块。比如：分页存储过程模块、订单处理存储过 程模块、转账存储过程模块。而且如果使用pl/sql 编程，我们可以轻松地完成 非常复杂的查询要求。 pl/sql 可以做什么 简单分类 过程(存储过程) 函数 块(编程) 触发器 包,PL/SQL 块的结构和实例,编写规范 编写规范n 1.注释 单行注释 - Sql 代码 1. select * from emp where empno=7788; -取得员工信息 多行注释 /*.*/来划分 2.标志符号的命名规范 1).当定义变量时，建议用v_作为前缀v_sal 2).当定义常量时，建议用c_作为前缀c_rate 3).当定义游标时，建议用_c

7、ursor 作为后缀emp_cursor 4).当定义例外时，建议用e_作为前缀e_error,PL/SQL 块的结构和实例,pl/sql 块介绍 介绍n 块(block)是pl/sql 的基本程序单元，编写pl/sql 程序实际上就是编写 pl/sql 块，要完成相对简单的应用功能，可能只需要编写一个pl/sql 块，但是 如果想要实现复杂的功能，可能需要在一个pl/sql 块中嵌套其它的pl/sql 块。 块n结构示意图 pl/sql 块由三个部分构成：定义部分，执行部分，例外处理部分。 如下所示： declare /*定义部分定义常量、变量、游标、例外、复杂数据类型*/ begin /*

8、执行部分要执行的pl/sql 语句和sql 语句*/ exception /*例外处理部分处理运行的各种错误*/ end; 定义部分是从declare 开始的，该部分是可选的； 执行部分是从begin 开始的，该部分是必须的； 例外处理部分是从exception 开始的，该部分是可选的。 可以和java 编程结构做一个简单的比较。,PL/SQL 块的结构和实例,pl/sql 块的实例(1) 实例1-只包括执行部分的pl/sql 块n Sql 代码 1. set serveroutput on -打开输出选项 2. begin 3. dbms_output.put_line(hello); 4.

9、 end; 相关说明： dbms_output 是oracle 所提供的包(类似java 的开发包)，该包包含一些过程， put_line 就是dbms_output 包的一个过程。,PL/SQL 块的结构和实例,pl/sql 块的实例(2) 实例2-包含定义部分和执行部分的pl/sql 块n Sql 代码 1. declare 2. v_ename varchar2(5); -定义字符串变量 3. begin 4. select ename into v_ename from emp where empno= 相关说明： 4. v_sal number(7,2); 5. begin 6. -

10、执行部分 7. select ename,sal into v_ename,v_sal from emp where empno= 15./,PL/SQL 块的结构和实例,pl/sql分类 - 过程，函数，包，触发器 过程 过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指 定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分； 通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus 中可以 使用create procedure 命令来建立过程。,PL/SQL 块的结构和实例,实例如下： 1.请考虑编写一个过程，可以输入雇员名，新工

11、资，可修改雇员的工资 2.如何调用过程有两种方法； exec call 3.如何在java 程序中调用一个存储过程 问题：如何使用过程返回值？ 特别说明： 对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。 create procedure sp_pro3(spName varchar2, newSal number) is -不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java 写方法 时的参数一样 Sql 代码 1. begin 2. -执行部分，根据用户名去修改工资 3. update emp set sal=newSal where ename=

12、spName; 4. end; 5. /,PL/SQL 块的结构和实例,java 程序中调用一个存储过程 /演示java 程序去调用oracle 的存储过程案例 5. try 6. /1.加载驱动 7. Class.forName(oracle.jdbc.driver.OracleDriver); 8. /2.得到连接 9. Connection ct = DriverManager.getConnection(jdbc:o racle:thin:127.0.0.1:1521:MYORA1,scott,m123); 11. /3.创建CallableStatement 12. Callable

13、Statement cs = ct.prepareCall(call sp_pr3(?,?); 13. /4.给?赋值 14. cs.setString(1,SMITH); 15. cs.setInt(2,10); 17. cs.execute(); 18. /关闭 19. cs.close(); 20. ct.close(); 21. catch(Exception e) 22. e.printStackTrace(); 23. 24. 25.,PL/SQL 块的结构和实例,1.使用各种if 语句 2.使用循环语句 3.使用控制语句goto 和null； 条件分支语句 pl/sql 中提供了

14、三种条件分支语句ifthen，if then else，if then elsif then 这里我们可以和java 语句进行一个比较 简单的条件判断 if thenn 问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就 给该员工工资增加10%。 Sql 代码 1. create or replace procedure sp_pro6(spName varchar2) is 2. -定义 3. v_sal emp.sal%type; 4. begin 5. -执行 6. select sal into v_sal from emp where ename=spName;

15、 7. -判断 8. if v_sal2000 then 9. update emp set sal=sal+sal*10% where ename=spName; 10. end if; 11.end; 12./,PL/SQL 块的结构和实例,二重条件分支 if then else 问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0 就在原来 的基础上增加100；如果补助为0 就把补助设为200； Sql 代码 1. create or replace procedure sp_pro6(spName varchar2) is 2. -定义 3. v_comm m%type; 4

16、. begin 5. -执行 6. select comm into v_comm from emp where ename=spName; 7. -判断 8. if v_comm0 then 9. update emp set comm=comm+100 where ename=spName; 10. else 11. update emp set comm=comm+200 where ename=spName; 12. end if; 13.end; 14./,PL/SQL 块的结构和实例,多重条件分支 if then elsif thenn 问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT 就给他的工资增加1000，如果该雇员的职位是MANAGER 就给他的工资增加500， 其它职位的雇员工资增加200。 Sql 代码 1. create or replace procedure sp_pro6(spNo number) is 2. -定义 3. v_job emp.job%t