Oracle存储过程

来源：互联网发布：发票自动读取数据编辑：程序博客网时间：2024/05/04 14:42

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

创建存储过程

create procedure sp_name

@[参数名] [类型],@[参数名] [类型]

begin

.........

end

以上格式还可以简写成：

create proc sp_name

@[参数名] [类型],@[参数名] [类型]

begin

.........

end

/*注：“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/

调用存储过程

1.基本语法：exec sp_name [参数名]

删除存储过程

1.基本语法：

drop procedure sp_name

2.注意事项

(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

其他常用命令

1.show procedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name

显示某一个mysql存储过程的详细信息

3、exec sp_helptext sp_name

显示你这个sp_name这个对象创建文本

命名的系统异常产生原因

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 在等待资源时超时

1.命令格式
存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用，定义存储过程的语法如下：
PROCEDURE Name [(Parameter[,Parameter,])]
IS|AS
[Local Declarations]
BEGIN
Execute statements;
[EXCEPTION Exception Handlers]
END [Name];
2.调用
存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。用EXECUT命令调用存储过程的格式如下：
SQL>EXCUTE Proc_Name(par1, par2…);
存储过程也可以被另外的PL/SQL块调用，调用的语句是：
DECLARE par1, par2;
BEGIN
Proc_Name(par1, par2…);
END;
3.释放
当某个存储过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。释放过程的语句格式如下：
SQL>DROP PROCEDURE Proc_Name;
4.实例：
编写存储过程，显示所指定雇员名所在的部门名和位置。
CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,
pdname OUT dept.dname%TYPE,
ploc OUT dept.loc%TYPE) AS
BEGIN
SELECT dname, loc
INTO pdname, ploc
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.ename = pename;
END;
调用：
VARIABLE vdname VARCHAR2(14);
VARIABLE vloc VARCHAR2(13);
EXECUTE DeptMesg('SMITH', :vdname￡? :vloc);
PRINT vdname vloc;
七、函数
1.命令格式
函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数，有一个返回值，返回值的数据类型在创建函数时定义。定义函数的语法如下：
FUNCTION Name [{Parameter[,Parameter,])]
RETURN DataTypes
IS
[Local Declarations]
BEGIN
Execute Statements;
[EXCEPTION Exception Handlers]
END [Name];
2.调用
无论在命令行还是在程序语句中，函数都可以通过函数名称直接在表达式中调用。例如：将函数Count_Num(‘女’)的返回值赋予变量Man_Num。
SQL>EXECUTE Man_Num := Count_Num('女');
3.释放
当函数不再使用时，要用DROP命令将其从内存中删除，例如：
SQL>DROP FUNCTION Count_Num;
4.实例
编写一个函数以显示该雇员在此组织中的工作天数。
CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS
vhiredate emp.hiredate%TYPE;
vday NUMBER;
BEGIN
SELECT hiredate INTO vhiredate FROM emp WHERE empno = no;
vday := CEIL(SYSDATE - vhiredate);
RETURN vday;
END;
八、触发器
1.触发器的创建规则：
①作用范围清晰；
②不要让触发器去完成Oracle后台已经能够完成的功能；
③限制触发器代码的行数；
④不要创建递归的触发器；
⑤触发器仅在被触发语句触发时进行集中的，全局的操作，同用户和数据库应用无关。
2.可以创建被如下语句所触发的触发器：
①DML语句（DELETE，INSERT，UPDATE）；
②DDL语句（CREATE，ALTER， DROP）；
③数据库操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。
3.注意事项
①触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发；
②一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次；
③SELECT 并不更改任何行，因此不能创建 SELECT 触发器.这种场合下规则和视图更适合；
④触发器和某一指定的表格有关，当该表格备删除时，任何与该表有关的触发器同样会被删除；
⑤在一个表上的每一个动作只能有一个触发器与之关联；
⑥在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器；
4.删除触发器的语句格式为：
DROP TRIGGER name ON table;
一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句，可为对一指定表的INSERT、UNPDATE或DELETE语句。触发限制是指定一个布尔表达式，当触发器激发时该布尔表达式是必须为真。触发器作为过程，是PL/SQL块，当触发语句发出、触发限制计算为真时该过程被执行。
5.实例
编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。
CREATE OR REPLACE TRIGGER del_emp_deptno
BEFORE DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno = :OLD.deptno;
END;
九、包
1.包头
创建包头的语句格式如下：
CREATE PACKAGE＜包名> IS
变量、常量及数据类型定义；
游标定义；
函数、过程定义和参数列表及返回类型；
END＜包名＞；
2.包体
创建包主体部分的语句格式如下：
CREATE PACKAGE BODY＜包名＞
AS
游标、函数、过程的具体定义；
END＜包名＞；
3.实例
包头代码：
--创建包头
CREATE PACKAGE test_package IS
--定义变量
man_num NUMBER;
woman_num NUMBER;
--定义游标
CURSOR学生;
--定义函数
CREATE FUNCTION f_count(in sex IN 学生.sex%TYPE)
--定义返回值类型
RETURN NUMBER;
--定义过程
CREATE PROCEDURE p_count(in_sex IN 学生.sex%TYPE, out_num OUT NUMBER);
--包头结束
END test_package;
包体代码：
--创建包体
CREATE PACKAGE BODY test_package AS
--游标具体定义
CURSOR 学生IS
SELECT 学号，姓名 FROM 学生 WHERE 学号 < 50;
--函数具体定义
FUNCTION f_count(in_sex IN学生.sex%TYPE)
--定义返回值类型
RETURN NUMBER IS
out_num NUMBER;
--函数体
BEGIN
IF in_sex = '男' THEN
SELECT count(sex) INTO out_num FROM 学生 WHERE性别＝'男';
ELSE
SELECT count(sex) INTO out_num FROM 学生 WHERE 性别＝'女';
END IF;
--返回函数值
RETURN(out_num);
--函数定义结束
END f_count;
--过程具体定义
PROCEDURE p_count(in_sex IN学生.sex%TYPE， out_num OUT NUMBER) AS
--过程体
BEGIN
IF in_sex = '男' THEN
SELECT count(sex) INTO out_num FROM 学生 WHERE性别 = '男';
ELSE
SELECT count(sex) INTO out_num FROM 学生 WHERE 性别＝ '女';
END IF;
--过程定义结束
END P_count;
--包体定义结束
END test_package;
oracle 分页存储过程
包头：
create or replace package JT_P_page is
-- Author : ujnjt
-- Created : 2007-8-5 16:43:50
-- Purpose : 分页功能
-- Public type declarations
type type_cur is ref cursor; --定义游标变量用于返回记录集
procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
Psql in varchar2, --产生分页数据的查询语句
Psize in number, --每页显示记录数
Pcount out number, --返回的分页数
Prowcount out number, --返回的记录数
v_cur out type_cur --返回分页数据的游标
);
end JT_P_page;
包体：
create or replace package body JT_P_page is
procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
Psql in varchar2, --产生分页数据的查询语句
Psize in number, --每页显示记录数
Pcount out number, --返回的分页数
Prowcount out number, --返回的记录数
v_cur out type_cur --返回分页数据的游标
) AS
v_sql VARCHAR2(1000);
v_Pbegin number;
v_Pend number;
begin
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into Prowcount; --计算记录总数
Pcount := ceil(Prowcount / Psize); --计算分页总数
--显示任意页内容
v_Pend := Pindex * Psize + Psize;
v_Pbegin := v_Pend - Psize + 1;
--Psql := 'select rownum as rn , t.* from pay_en_voucher t'; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;
open v_cur for v_sql;
end Pagination;
end JT_P_page;
调用方法：
-- Created on 2007-8-5 by ujnjt
declare
psqlContent varchar2(1000);
pageIndex number;
pageCount number; --返回的分页数
pageSize number; --每一页的记录数
rowscount number; -- 返回的记录总数
return_cur JT_P_PAGE.type_cur;
curr_id pay_en_voucher.id%type;
curr_rn number;
begin
psqlContent := 'select rownum as rn , t.id from pay_en_voucher t';
pageIndex := 0;
pageSize := 20;
JT_P_PAGE.Pagination(pageIndex,
psqlContent,
pageSize,
pageCount,
rowscount,
return_cur);
if return_cur%isopen then
close return_cur;
end if;
for Counter in 0 .. pageCount - 1 loop
pageIndex := Counter;
JT_P_PAGE.Pagination(pageIndex, psqlContent, pageSize, pageCount, rowscount, return_cur);
if return_cur%isopen then
dbms_output.put_line('第' || (pageIndex + 1) || '页数据开始');
loop
fetch return_cur
into curr_rn, curr_id;
if (return_cur%notfound) then
exit;
end if;
dbms_output.put_line(curr_rn);
dbms_output.put_line(curr_id);
dbms_output.put_line('--------');
end loop;
dbms_output.put_line('第' || (pageIndex + 1) || '页数据结束');
close return_cur;
dbms_output.put_line('=========================================');
end if;
end loop;
end;

0 0