一:存储过程
创建存储过程,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限
删除存储过程,是过程的创建者或者拥有DROP ANY PROCEDURE系统权限
修改存储过程,则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人
执行(或调用)存储过程,是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人
1:语法
1)创建
CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
{AS|IS}
[说明部分]
BEGIN
可执行部分
[EXCEPTION
错误处理部分]
END [过程名];
说明:
OR REPLACE 表示如果存在就覆盖存储过程
参数有三种形式:IN、OUT和IN OUT。则默认为IN。
关键字AS也可以写成IS,后跟过程的说明部分,可以在此定义过程的局部变量。
2)删除存储过程:
DROP PROCEDURE 存储过程名;
3)修改存储过程:
ALTER PROCEDURE 存储过程名 COMPILE;
4)执行存储过程
EXECUTE 模式名.存储过程名[(参数...)];
或
BEGIN
模式名.存储过程名[(参数...)];
END;
另外:参数可以是变量、常量或表达式
要其它用户执行存储过程须要给其它用户授权
2:参数说明
OUT 定义一个输出参数变量,用于从存储过程获取数据
IN OUT 定义一个输入、输出参数变量,兼有以上两者的功能
1)参数名 IN 数据类型 DEFAULT 值;
定义一个输入参数变量,用于传递参数给存储过程。
可以是常量、有值变量或表达式
DEFAULT 关键字为可选项,用来设定参数的默认值。如果在调用存储过程时不指明参数,则参数变量取默认值
在存储过程中,输入变量接收主程序传递的值,但不能对其进行赋值。
2)参数名 OUT 数据类型;
定义一个输出参数变量,用于从存储过程获取数据,即变量从存储过程中返回值给主程序。
在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。
在存储过程中,参数变量只能被赋值而不能将其用于赋值,而且必须给输出变量至少赋值一次。
3)参数名 IN OUT 数据类型 DEFAULT 值;
定义一个输入、输出参数变量,兼有以上两者的功能。
在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。
DEFAULT 关键字为可选项,用来设定参数的默认值。
在存储过程中,变量接收主程序传递的值,同时可以参加赋值运算,也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。
补充:如果省略IN、OUT或IN OUT,则默认模式是IN。
调用它时参数个数与位置可以不一致,用以下形式调用:
二:函数
创建函数,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限
删除函数,需要是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人
修改函数,需要是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人
执行函数,需要是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人
1:语法
1)创建:
CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)]
RETURN 数据类型
{AS|IS}
[说明部分]
BEGIN
可执行部分
RETURN (表达式)
[EXCEPTION
错误处理部分]
END [函数名];
说明:
参数是可选的,但只能是IN类型(IN关键字可以省略)。
在定义部分的RETURN 数据类型,用来表示函数的数据类型,也就是返回值的类型,不可省略。
在可执行部分的RETURN(表达式),用来生成函数的返回值,其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句,但只有一个RETURN语句会被执行,一旦执行了RETURN语句,则函数结束并返回调用环境。
2)删除
DROP FUNCTION 函数名;
3)修改
ALTER PROCEDURE 函数名 COMPILE;
4)执行
变量名:=函数名(...)
三:对存储过程 和 函数的查看(可以通过对数据字典【USER_SOURCE】的访问来查询存储过程或函数的有关信息)
1:查询某个存储过程序内容
2:查看数据字殿
3:查看存储过程的参数
4:查看发生编辑错误
5:查询一个存储过程或函数是否是有效状态(即编译成功)
VALID表示该存储过程有效(即通过编译),INVALID表示存储过程无效或需要重新编译。它的状态会改变,这与它依赖外部表(表删除修改等操作)有关系
6:查看存储过程与表的依赖关系
说明
问题:
三:包
包是用来存储相关程序结构的对象,它存储于数据字典中。
包由两个分离的部分组成:包头(PACKAGE)和包体(PACKAGE BODY)。
包头是包的说明部分,是对外的操作接口,对应用是可见的;
包体是包的代码和实现部分,对应用来说是不可见的黑盒。
包中可以使用的程序:
函数(FUNCTION) 带参数、具有返回值的命名的程序模块
变量(VARIABLE) 存储变化的量的存储单元
常量(CONSTANT) 存储不变的量的存储单元
游标(CURSOR) 用户定义的数据操作缓存区,在可执行部分使用
类型(TYPE) 用户定义的新的结构类型
异常(EXCEPTION) 在标准包中定义或由用户自定义,用于处理程序错误
说明:
出现在包头中的称为:公有元素,
出现在包体中的称为:私有元素,
出现在包体的过程(或函数)中的称为:局部变量
私有元素(PRIVATE) 在包体的说明部分说明 只能被包内部的其他部分访问
局部变量(LOCAL) 在过程或函数的说明部分说明 只能在定义变量的过程或函数中使用
注意:在包体中出现的过程或函数,如果需要对外公用,就必须在包头中说明,包头中的说明应该和包体中的说明一致。
包有以下优点:
* 包可以方便地将存储过程和函数组织到一起,每个包又是相互独立的。在不同的包中,过程、函数都可以重名,这解决了在同一个用户环境中命名的冲突问题。
* 包增强了对存储过程和函数的安全管理,对整个包的访问权只需一次授予。
* 在同一个会话中,公用变量的值将被保留,直到会话结束。
* 区分了公有过程和私有过程,包体的私有过程增加了过程和函数的保密性。
* 包在被首次调用时,就作为一个整体被全部调入内存,减少了多次访问过程或函数的I/O次数。
1)创建包头和包体(包的创建应该先创建包头部分,然后创建包体部分)
包头
CREATE [OR REPLACE] PACKAGE 包名
{IS|AS}
公有变量定义
公有类型定义
公有游标定义
公有异常定义
函数说明
过程说明
END;
包体
CREATE [OR REPLACE] PACKAGE BODY 包名
{IS|AS}
私有变量定义
私有类型定义
私有游标定义
私有异常定义
函数定义
过程定义
END;
2)删除包头:
DROP PACKAGE 包头名删除包体:
DROP PACKAGE BODY 包体名
3)重新编译包头:
ALTER PACKAGE 包名 COMPILE PACKAGE
重新编译包体:
ALTER PACKAGE 包名 COMPILE PACKAGE BODY
DBMS_DDL 编译过程函数和包
DBMS_SESSION 改变用户的会话,初始化包等
DBMS_TRANSACTION 控制数据库事务
DBMS_MAIL 连接Oracle*Mail
DBMS_LOCK 进行复杂的锁机制管理
DBMS_ALERT 识别数据库事件告警
DBMS_PIPE 通过管道在会话间传递信息
DBMS_JOB 管理Oracle的作业
DBMS_LOB 操纵大对象
DBMS_SQL 执行动态SQL语句
可以将已经存在的存储过程或函数添加到包中,方法是去掉过程或函数创建语句的CREATE OR REPLACE部分,将存储过程或函数复制到包体中 ,然后重新编译即可。
如果需要将私有过程或函数变成共有过程或函数的话,将过程或函数说明部分复制到包头说明部分,然后重新编译就可以了。
包的例子:
CREATE OR REPLACE PACKAGE EMPLOYE --包头部分
IS
PROCEDURE SHOW_DETAIL;
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);
PROCEDURE SAVE_EMPLOYE;
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);
END EMPLOYE;
/
CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分
IS
EMPLOYE EMP%ROWTYPE;
-------------- 显示雇员信息 ---------------
PROCEDURE SHOW_DETAIL
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);
DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
END SHOW_DETAIL;
----------------- 从EMP表取得一个雇员 --------------------
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)
AS
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
END GET_EMPLOYE;
---------------------- 保存雇员到EMP表 --------------------------
PROCEDURE SAVE_EMPLOYE
AS
BEGIN
UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=
EMPLOYE.EMPNO;
DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');
END SAVE_EMPLOYE;
---------------------------- 修改雇员名称 ------------------------------
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)
AS
BEGIN
EMPLOYE.ENAME:=P_NEWNAME;
DBMS_OUTPUT.PUT_LINE('修改名称完成!');
END CHANGE_NAME;
---------------------------- 修改雇员工资 --------------------------
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)
AS
BEGIN
EMPLOYE.SAL:=P_NEWSAL;
DBMS_OUTPUT.PUT_LINE('修改工资完成!');
END CHANGE_SAL;
END EMPLOYE;
执行: EXECUTE EMPLOYE.GET_EMPLOYE(7788);
学习参考:http://blog.csdn.net/x125858805/article/details/7581806