Oracle学习（12）：存储过程，函数和触发器

最新推荐文章于 2021-04-03 04:27:12 发布

oO晨晨晨Oo

最新推荐文章于 2021-04-03 04:27:12 发布

阅读量1k

点赞数

分类专栏：数据库 Oracle学习文章标签： oracle 数据库触发器存储过程存储函数

本文链接：https://blog.csdn.net/qhwc2009/article/details/46340185

版权

数据库同时被 2 个专栏收录

33 篇文章 0 订阅

订阅专栏

Oracle学习

19 篇文章 0 订阅

订阅专栏

存储过程和存储函数

l 存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

注意：存储过程与存储函数声明变量时，用的是as 而不是declare

存储过程与存储函数区别

存储过程不带有返回值，存储函数有返回值

存储过程

创建存储过程

l用CREATE PROCEDURE命令建立存储过程

l语法：

create [or replace] PROCEDURE过程名(参数列表)

PLSQL子程序体；

创建存储过程简单示例

/*
第一个存储过程：Hello World

调用存储过程：
1. exec sayHello();
2. begin
sayHello();
end;
/

*/
create or replace procedure sayHello
as
--变量说明
begin

dbms_output.put_line('Hello World');

end;
/

调用存储过程

方法一

set serveroutput on

begin

raisesalary(7369);

end;

方法二

set serveroutput on

exec raisesalary(7369);

存储过程（涨工资实例）

实例一

为指定的职工在原工资的基础上长10%的工资，并打印涨工资前和涨工资后的工资

代码：

**************************************************************************************************

/*
为指定的职工在原工资的基础上长10%的工资,并打印涨工资前和涨工资后的工资

可能用到的sql语句
update emp set sal = sal * 1.1 where empno = empid;

*/

create or replace procedure raiseSalary(empid in number)
as
pSal emp.sal%type; --保存员工当前工资
begin
--查询该员工的工资
select sal into pSal from emp where empno=empid;
--给该员工涨工资
update emp set sal = sal * 1.1 where empno = empid;

--打印涨工资前后的工资
dbms_output.put_line('员工号:' || empid || ' 涨工资前:' || psal || ' 涨工资后' || psal * 1.1);
end;
/

**************************************************************************************************

实例二

为指定员工增加指定额度的工资(传递多个参数)

代码：

**************************************************************************************************

create or replace procedure raiseSalary2(empid in number, rate in NUMBER)
as
pSal emp.sal%type; --保存员工当前工资
begin
--查询该员工的工资
select sal into pSal from emp where empno=empid;
--给该员工涨工资
update emp set sal = sal * rate where empno = empid;

--打印涨工资前后的工资
dbms_output.put_line('员工号:' || empid || ' 涨工资前:' || psal || ' 涨工资后' || psal * rate);
end;
/

**************************************************************************************************

存储函数

l函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

l建立存储函数的语法：

CREATE [OR REPLACE] FUNCTION函数名(参数列表)

RETURN 函数值类型

PLSQL子程序体；

存储函数示例

示例：查询某职工的年收入。

代码：

****************************************************************************

/*
查询某职工的总收入。
*/

create or replace function queryEmpSalary(empid in number)
RETURN NUMBER
as
pSal number; --定义变量保存员工的工资
pComm number; --定义变量保存员工的奖金
begin
select sal,comm into pSal, pcomm from emp where empno = empid;
return psal*12+ nvl(pcomm,0);
end;
/

****************************************************************************

存储函数的调用

调用一

declare

v_salnumber;

begin

v_sal:=queryEmpSalary(7934);

dbms_output.put_line('salary is:' ||v_sal);

end;

调用二

begin

dbms_output.put_line('salaryis:' ||queryEmpSalary(7934));

end;

过程和函数中的in和out

l一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。

l但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

带out函数的存储过程示例

/*
out参数：查询某个员工的姓名，月薪和职位
*/
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;
/

在out参数中使用游标

l首先申明包结构

l然后创建包体

在Java语言中访问游标类型的out参数

代码实现：

package demo.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;

import org.junit.Test;

import demo.utils.JDBCUtills;

/*
 * Statement < PreparedStatement < CallableStatement
 */
public class TestOracle {
/*
 * create or replace
procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
 */
	@Test
	public void testProcedure(){    //存储过程测试用例
		//{call procedure-name(??/)}
		String sql = "{call queryEmpInfo(?,?,?,?)}";
		Connection conn = null;
		CallableStatement call = null;
		try{
			conn = JDBCUtills.getConnection();
			call = conn.prepareCall(sql);
			//Set value to param
			call.setInt(1, 7839);
			//declare out parameter
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			call.registerOutParameter(4, OracleTypes.VARCHAR);
			//run
			call.execute();
			//get returned values
			String name = call.getString(2);
			double sal = call.getDouble(3);
			String job = call.getString(4);
			System.out.println(name);
			System.out.println(sal);
			System.out.println(job);
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			JDBCUtills.release(conn, call, null);
		}
	}
/*
 * create or replace
function queryEmpIncome(eno in number)
return number	
 */
	@Test
	public void testFunction(){     //存储函数测试用例
		//{?=call procedure-name(??/)}
		String sql = "{?= call queryEmpIncome(?)}";
		Connection conn = null;
		CallableStatement call = null;
		try{
			conn = JDBCUtills.getConnection();
			call = conn.prepareCall(sql);
			call.registerOutParameter(1, OracleTypes.NUMBER);
			call.setInt(2, 7839);
			call.execute();
			
			double income = call.getDouble(1);
			System.out.println(income);
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			JDBCUtills.release(conn, call, null);
		}
	}


	@Test
	public void testCursor(){    //访问游标测试用例
		String sql = "{call MYPACKAGE.queryEmpList(?,?)}";   //注意此句要有{}
		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try{
			conn = JDBCUtills.getConnection();
			call = conn.prepareCall(sql);
			call.setInt(1, 20);
			call.registerOutParameter(2, OracleTypes.CURSOR);
			call.execute();
			
			rs = ((OracleCallableStatement)call).getCursor(2);
			while(rs.next()){
				String name = rs.getString("ename");
				double sal = rs.getDouble("sal");
				System.out.println(name+ "   " + sal);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			JDBCUtills.release(conn, call, rs);
		}
	}
}

在Java语言中调用

在Java语言中调用存储过程

在Java语言中调用存储函数

什么时候用存储过程/存储函数？

原则：如果只有一个返回值，用存储函数；否则，就用存储过程。

触发器

触发器定义

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

触发器的类型

语句级触发器

•在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。

行级触发器（FOR EACH ROW）

•触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

创建触发器

CREATE [or REPLACE] TRIGGER 触发器名

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [OF列名]}

ON 表名

[FOR EACH ROW [WHEN(条件) ] ]

PLSQL块

触发器简单实例

/*
第一个触发器:对update
*/
create or replace trigger sayHello
after update
on emp
begin

dbms_output.put_line('Hello World');

end;
/

行级触发器

触发语句与伪记录变量的值

触发器应用场景实例

场景一（语句触发器）

/*
触发器应用场景一：实现复杂的安全性检查

限制非工作时间向数据库emp插入数据

1. 周末：星期六星期日 to_char(sysdate,'day')
2. <9 or >18点 to_number(to_char(sysdate,'hh24'))

*/
create or replace trigger securityEmp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期三','星期六','星期日')
or to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
raise_application_error(-20001,'不能在非工作时间插入数据');

end if;
end;
/

场景二（行级触发器）

/*
触发器应用场景二：确认数据

涨工资不能越涨少

行级触发器的两个伪记录变量 :old , :new
*/
create or replace trigger checksal
before update
on emp
for each row
begin

if :old.sal > :new.sal then
raise_application_error(-20002,'涨后的工资不能少于涨前的。涨后:'||:new.sal||' 涨前：'||:old.sal);

end if;

end;
/

触发器练习

限制每个部门只招聘 5 名职工，超过计划则报出错误信息

*****************************************************************************

/*
练习：限制每个部门只招聘5名职工，超过计划则报出错误信息
*/
create or replace trigger limitEmpCount
before insert on emp
for each row
declare
pCount number;-- 保存每个部门的员工数
begin
select count(*) into pcount from emp where deptno=:new.deptno;
if pcount > 5 then raise_application_error(-20004,'部门:' || :new.deptno || ' 员工已有5人');
end if;
end;

oO晨晨晨Oo

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习（12）：存储过程，函数和触发器

存储过程和存储函数l存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。注意：存储过程与存储函数声明变量时，用的是as 而不是declare存储过程与存储函数区别存储过程不带有返回值，存储函数有返回值存储过程创建存储过程l用CREATE PROCEDURE命令建立存储过程l语法：
复制链接

扫一扫