在SQL中调用子程序
如果编写的PL/SQL函数可以像Oracle内置函数一样被SQL调用,需要遵循一定的规则:
- 所有函数的参数必须是IN模式。
- 参数的数据类型和RETURN子句的返回类型必须能被Oracle数据库识别,这是因为PL/SQL兼容所有的Oracle数据类型,但是PL/SQL扩充了自己的类型,比如BOOLEAN、INTEGER、记录、集合、自定义的子类型等。
- 函数必须存储在数据库中,在客户端PL/SQL环境中定义的PL/SQL函数是不能 被SQL语句调用的到的。
- 函数不能修改数据库表,不能执行DDL语句如CREATE TABLED、DROP INDEX,不能执行DML语句如INSERT、DELETE、UPDATE、MERGE等,不能使用COMMIT或ROLLBACK提交或回滚事务。不过 当函数定义在自治事务中时,这是限制会稍稍宽松一些。因为在自治事务中会与调用事务独立出来。
- 当调用远程的函数或通过并行行为调用其他会话中的函数时,函数可能读取或写入包变量中的值,因为Oracle服务器不支持跨用户会话访问。
- 仅当函数在一个SELECT列表中被调用时,后者是VALUES或SET子句中,函数才能够更新包变量的值,如果在WHERE或GROUP BY子句中,它可能无法改写包变量的值。
- 函数不能调用其他模块,比如说存储过程或函数,否则将打断任何先前定义的规则。
- 函数不能引用一个视图。
嵌套子程序
内嵌子程序是一个过程或函数,定义在PL/SQL块声明区中,仅能被这个快调用。
嵌套子程序的定义跟普通子程序定义相似,只是因为它不需要被单独地存储在数据字典中,因此不需要使用CERAETE OR REPLACE语句,只需要直接使用FUNCTION或PROCEDURE开始定义即可。
嵌套子程序与存储子程序的异同:
存储子程序 | 嵌套子程序 |
---|---|
子程序以编译的形式存储在数据字典中,当调用过程时,不需要重新编译 | 嵌套子程序被编译为包含它的语句块的一部分,如果包含它的语句块是匿名的,并多次运行,那么每一次都必须编译子程序 |
子程序可以从对该子程序具有EXECUTE权限的用户所提交的任何语句块中调用 | 嵌套子程序只能从包含子程序的语句块中调用 |
通过将自称曾许代码与调用代码块分开,使调用块更短,更容易理解和维护 | 子程序和调用块是完全相同的,这会导致混乱,如果对调用块进行了更改,那么子程序也将被编译为包含它的重编译块的一部分 |
可以使用DBMS_SHARED_POOL.KEEP包过程把已编译伪代码锁定在共享池中以便重用,这样可以改进其性能 | 嵌套子程序不能被其他子程序锁定在共享池中 |
独立的存储子程序不能重载,但是包中的子程序可以在同一个包中重载 | 嵌套子程序可以在同一个语句块中重载 |
子程序的前向声明
PL/SQL要求必须要在调用子程序之前先定义好子程序,这在大多数情况下并没有问题,但是当遇到子程序相互调用时,情况就会变得有些复杂。
比如,子程序A调用子程序B,但是子程序B又调用子程序A:
DECLARE
v_val BINARY_INTEGER:=5;
PROCEDURE A(p_counter IN OUT BINARY_INTEGER) IS --声明嵌套子程序A
BEGIN
B