oracle 存储过程、函数和触发器用法实例详解_Oracle

oracle 存储过程、函数和触发器用法实例详解

2020-05-14 14:08怀素真 Oracle

这篇文章主要介绍了oracle 存储过程、函数和触发器用法,结合实例形式详细分析了oralce 存储过程、函数和触发器具体功能、原理、定义、使用方法及相关操作注意事项,需要的朋友可以参考下

本文实例讲述了oracle 存储过程、函数和触发器用法。分享给大家供大家参考，具体如下：

一、存储过程和存储函数

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

创建存储过程

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

语法：

				?

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

									as

									PLSQL子程序体;

				?

									--给指定员工涨工资

									create procedure addSal(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;

调用存储过程

				?

									--方法一

									begin

									addSal(7369);

									end;

									--方法二

									exec addSal(7369);

存储函数

函数为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个return子句，用于返回函数值。函数说明要指定函数名，结果值的类型，以及参数类型等。
创建语法：

				?

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

									RETURN 函数值类型

									AS

									PLSQL子程序体;

				?

									--查询指定员工的年收入

									create function queryEmpSal(empid in number)

									return number

									as

									  psal emp.sal%type;

									  pcomm emp.comm%type;

									begin

									  select sal,comm into psal,pcomm from emp where empno=empid;

									  return (psal*12) + nvl(pcomm,0);

									end;

函数的调用

				?

									declare

									 psal number;

									begin

									 psal:=queryEmpSal(7369);

									 dbms_output.put_line(psal);

									end;

或

				?

									begin

									 dbms_output.put_line(queryEmpSal(7369));

									end;

过程和函数中的IN和OUT

一般来讲，过程和函数的区别在于函数可以有一个返回值，而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。
什么时候用存储过程或函数？
原则：如果只有一个返回值，用存储函数，否则，就用存储过程。

创建包和包体

什么是包和包体？
包是一组相关过程、函数、变量、常量、类型和游标等PL/SQL程序设计元素的组合。包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。
包体是包定义部分的具体实现。
包由两个部分组成：包定义和包主体。

				?

									--包定义

									create [or replace] package 包名 as

									[公有数据类型定义]

									[公有游标声明]

									[公有变量、常量声明]

									[公有子程序声明]

									end 包名;

				?

									--包主体

									create [or replace] package body 包名 as

									[私有数据类型定义]

									[私有变量、常量声明]

									[私有子程序声明和定义]

									[公有子程序定义]

									begin

									PL/SQL子程序体;

									end 包名;

				?

									--创建mypackage包

									create or replace package mypackage as

									 procedure total(num1 in number, num2 in number, num3 out number);

									end mypackage;

				?

									--mypackage包体

									create or replace package body mypackage as

									--计算累加和的total过程

									procedure total(num1 in number, num2 in number, num3 out number) as

									 tmp number := num1;

									begin

									 if num2 < num1 then num3 := 0;

									 else num3 := tmp;

									  loop

									   exit when tmp > num2;

									   tmp := tmp + 1;

									   num3 := num3 + tmp;

									  end loop;

									 end if;

									end total;

									end mypackage;

(*注意：包定义和包体要分开创建)
调用包

				?

									declare

									 num1 number;

									begin

									 mypackage.total(1, 5, num1);

									 dbms_output.put_line(num1);

									end;