存储过程
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;
分享到:
相关推荐
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...
oracle 存储过程实例 oracle存储过程实例
oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel
oracle 存储过程 unwrap 图形解密 工具
oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO ...
可以将SQL Server存储过程转为oracle存储过程的工具
ibatis调用oracle存储过程分页
帆软报表调用Oracle存储过程如果存储过程定义中没有参数,但是设计器中缺弹出一个storeParameter1参数的解决插件
4、开发指南:《Oracle存储过程入门指南&100+种真实业务场景存储过程实例.pdf》,可以帮助初学者系统学习。 5、资源包括“通用存储过程整理” 和 “真实业务存储过程整理” 两部分,通用适合各种系统,可以无缝隙...
oracle存储过程加密破解 0
使用java实现oracle存储过程。 共有3个小例子。实现的功能 1、无返回值的存储过程 如 insert 2、有返回值的存储过程(非列表)select id from tab 3、返回列表 如:select * from tab 顺便鄙视下csdn,作为一个it...
oracle存储过程学习经典入门 非常好的 初学者必看
oracle 存储过程 函数 dblink 绝对对工作和平时学习有价值的资料。针对个人具体情况做修改即可使用
Oracle存储过程中使用临时表 会话级临时表 事务级临时表
oracle 存储过程批量提交
oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记
行业内Oracle存储过程最基本的开法规范,适合oracle入门小白学习
oracle 存储过程实例 oracle 存储过程实例 oracle 存储过程实例 oracle 存储过程实例
linux系统中使用shell脚本对oracle存储过程进行编译
oracle存储过程+日期+定时任务Job oracle存储过程+日期+定时任务Job oracle存储过程+日期+定时任务Job