Oracle存储过程和函数

7
3.0
调用过程
• 执行存储过程的语法格式
EXEC[UTE] procedure_name [ (parameter,…n) ]
8
•
执行存储过程插入记录
SQL> EXEC addUser('0001','zhangsan','zhang01');
3.0
调用过程
• 在PL/SQL块中调用存储过程
DECLARE v_id Users.id%type := '0002'; v_name Users.uname%type := 'lisi'; v_pwd Users.pwd%type :='li02'; BEGIN addUser(v_id, v_name, v_pwd); END;
第三章
存储过程和函数
3.0
2011-7-25 威迅教育开发部监制
回顾
1. 找出最高工资的编号,更新他的工资为70%. 2. 输出姓名,原来工资,现在工资.(游标实现)
2
3.0
目标
1. 理解子程序的概念和作用 2. 掌握使用PL/SQL语言创建和调用存储过程 和函数 3. 掌握在Java语言中调用存储过程和函数 4. 掌握事务在子程序中的应用
CREATE OR REPLACE PROCEDURE findEmp( p_empno IN emp.empno%type, p_ename OUT emp.ename%type )AS BEGIN select ename into p_ename from emp where empno = p_empno; EXCEPTION when NO_DATA_FOUND then p_ename := null; END;
3.0
总结
1.
25
2.
3. 4.
5. 6. 7.
Oracle 子程序是命名的PL/SQL块，它存放在数据字典中， 可以在不同用户和应用程序之间共享。子程序包括存储过 程和函数。 子程序只编译一次，并以可执行文件形式存储，因此调用 过程快速而且高效。如果不考虑数据库的移植性，可以使 用子程序来实现应用程序的业务逻辑。 存储过程没有返回值，但可以通过OUT和IN OUT参数返 回多个值。存储过程作为一条PL/SQL语句来调用。 函数必须指定一个返回值，并在函数体中用RETURN语句 来返回。在函数中使用OUT或IN OUT参数返回多个值是 一种不良的编程习惯。函数是作为PL/SQL表达式的的一 部分来调用的。 事务是用于确保数据库的一致性。事务把数据库从一个一 致状态带到下一个一致状态。 事务ACID原则：原子性、一致性、隔离性、持久性。 事务处理的主要语句：commit、rollback。 3.0
3.0
过程的参数模式
• •
11
过程的形参有三种模式：IN, OUT, IN OUT IN参数：输入型参数，IN模式的参数在传 递时，实参可以是变量，也可以是常量
EXEC addUser('0001','zhangsan','zhang01'); /*实参为常量*/ --或者 addUser(v_id, v_name, v_pwd); /*实参为变量*/
12
3.0
过程的参数模式
• 调用带OUT参数的存储过程（实参只能是 变量，不能是常量 ）
DECLARE v_deptno emp.deptno%type; v_count number; BEGIN v_deptno := &部门编号; /*输入一个部门编号*/ countSal(v_deptno, v_count); /*调用存储过程*/ dbms_output.put_line('人数为：' || v_count); END;
13
3.0
过程的参数模式
• • •
14
IN OUT参数：输入输出型参数。这是一种 最灵活的方式，该类型是IN和OUT的组合。 IN OUT参数的实参既可以传值给过程，又 可以从过程获得返回值。 调用时实参只能是变量，不能是常量
3.0
过程中的异常处理
• 为了不让存储过程因为出现异常而终止执 行，可以在过程中进行异常处理
3
3.0
概述
• • PL/SQL块主要有两类：命名块和匿名块 匿名块(以DECLARE或BEGIN开始)每次使 用都要编译，并且不能从其他地方调用
4
•
命名块也叫做子程序或过程，主要有三类： • 存储过程 • 函数 • 程序包
3.0
概述
• 子程序有如下一些优点： 执行速度快 提高系统性能 确保数据库的安全 允许自动执行 本章主要介绍命名块结构，包括过程和函 数。以及在过程（函数）应用数据库事务
19
3.0
函数
• • 函数是用来计算值的的一种子程序 函数与过程在结构上很相似，不同的是函 数有一条RETURN语句，用来返回值
20
3.0
创建函数
• 假设我们要查询一个用户‘zhangsan’是否 在Users表中存在，创建函数如下：
CREATE OR REPLACE FUNCTION existUser( p_name Users.uname%type )RETURN boolean IS t_count number; BEGIN select count(uname) into t_count from Users where uname = p_name; if t_count > 0 then return true; /*如果存在返回true */ else return false; /*如果不存在的返回false */ end if; END existUser;
15
3.0
过程的修改和删除
• 过程的修改，仍然使用create or replace procedure命令
16
CREATE OR REPLACE PROCEDURE countSal( p_count OUT number /*out参数*/ )AS BEGIN select count(*) into p_count from emp where sal >= 1500; END countSal;
9
3.0
过程的语法
•
10
CREATE PROCEDURE用于创建存储过程
CREATE [OR REPLACE] PROCEDURE [schema.]proc_name [ (parameter parameter_mode datatype,...n)] IS | AS [local_declaration] BEGIN sql_statement EXCEPTION exception_handler END procedure_name;
17
3.0
事务与存储过程
• 存储过程或许是保证事务正确的最容易 、 最可理解的方法 如果遵守“一个存储过程调用就是一个事 务”的编程范例，可以更轻松地控制事务 和建立新事务
18
•
3.0
事务与存储过程
• 示例：
/*一个把资金从储蓄帐户转移到支票帐户的ATM事务 */ CREATE OR REPLACE PROCEDURE savingsToChecking ( p_accid number, /*指定帐户编号*/ p_num number /*指定需要转帐的金额*/ ) AS BEGIN update accounts set balance = balance - p_num where accid = p_accid and type = 'savings'; /*资金从储蓄帐户转出*/ update accounts set balance = balance + p_num where accid = p_accid and type = 'checking'; /*资金转入支票帐户*/ commit; /*事务完成提交*/ EXCEPTION when others then rollback; /*出现任何异常则回退该事务*/ END;
22
3.0
函数的语法
• create function命令用于创建函数
23
CREATE [OR REPLACE] FUNCTION [schema.]function_name /*函数名*/ [ (parameter parameter_mode datatype,...n)] /*参数定义部分*/ RETURN return_type /*定义返回值类型*/ IS | AS [local_declaration] /* /*局部变量声明*/ */ BEGIN function_body /*函数体部分*/ RETURN expression /*返回语句*/ EXCEPTION exception_handler /*异常处理部分*/ END function_name;
•
源自文库
过程的删除，使用drop procedure
DROP PROCEDURE countSal ;
3.0
事务处理
• • 事务是用于确保数据库的一致性 事务必须满足四个原则（ACID） 原子性(atomicity) 一致性(consistency) 隔离性(isolation) 持久性(durability)
3.0
过程的参数模式
• • OUT参数：输出型参数。通过OUT参数可 以在过程调用后获得一个返回值 示例：
/*计算指定部门中，工资在1500元以上的员工人数 */ CREATE PROCEDURE countSal( p_deptno IN emp.deptno%type, /*in参数*/ p_count OUT number /*out参数*/ )AS BEGIN select count(*) into p_count from emp where sal >= 1500 and deptno = p_deptno; END countSal;
21
3.0
函数的调用
• 在PL/SQL中调用函数
DECLARE existed boolean; BEGIN existed := existUser('zhangsan'); /*调用函数*/ if existed then dbms_output.put_line('该用户存在'); else dbms_output.put_line('该用户不存在'); end if; END;
5
•
3.0
存储过程
• 主要内容 • 创建过程 • 调用过程 • 过程的语法 • 过程的参数模式 • 过程中的异常处理 • 过程的修改和删除
6
3.0
创建过程
• 如果要经常性对表实施插入操作，可以使 用存储过程以提高执行效率
CREATE OR REPLACE PROCEDURE addUser ( p_id Users.id%type, p_name Users.uname%type, p_pwd Users.pwd%type )AS BEGIN insert into Users(id, uname, pwd) values(p_id, p_name, p_pwd); commit; END addUser;
3.0
函数与过程的比较
• •
24
函数和过程都是子程序，有很多相同之处。 函数和过程的区别如下：
过程 函数
作为表达式的一部分来调用,则重 于求值. 必须包含RETURN语句 必须通过RETURN语句返回一个值
作为一个PL/SQL语句来执行, 则重DML操作 可以没有RETURN语句 可以通过参数返回一个值