一、存储过程

1.1、存储过程的介绍

? ? 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后直接存储在数据库中，用户调用指定存储过程的名字和传递对应的参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都具备存储过程。

1.2、存储过程的优点和不足

存储过程(Stored Produce)的优点
序号	存储过程【Stored Produce】的优点
1	存储过程可以使得程序执行效率更高、安全性更好，因为过程建立保存之后就是已经编译并且储存到数据库中的，而直接编写sql语句则需要先通过分析器解析后再执行因此过程效率更高，并且直接编写写sql语句可能会带来一些安全性问题，如：sql注入等
2	建立存储过程对系统资源的消耗不大（这是因为存储过程只有在调用时才会执行）
3	存储过程可以用于降低网络流量（这是因为存储过程的代码是直接存储在数据库本地，直接调用就行，而不用编写大量的sql语句的代码）
4	存储过程使您能够增强对执行计划的重复使用
5	可维护性高（这是因为更新存储过程通常比更改、测试以及重新部署程序集花费更少的时间和精力）
6	代码精简一致（一个存储过程可以用于应用程序代码的不同位置）
7	增强安全性（ ①通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问； ②提高代码安全，防止 SQL注入； ③SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型）

存储过程(Stored Produce)的不足
序号	存储过程【Stored Produce】的不足
1	大量的使用存储过程，会对服务器造成很大的压力

1.3、存储过程的创建语法

参数的3种类型
序号? ??	参数类型说明
1	IN 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变
2	OUT 模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
3	IN OUT 表示该参数可以向该过程中传递值，也可以将某个值传出去

//基础语法
create [or replace] procedure 存储过程名称
(参数名  in|out 参数类型，参数名  in|out 参数类型) is|as

- - 变量声明部分

begin

- - 业务逻辑部分

end；


//详细语法内容
create [or replace] procedure 存储过程名称
(param1 in type，param2 out type) is|as

   变量1 类型（值范围）;
   变量2 类型（值范围）;

begin
   select count(*) into 变量1 from 表A where列名=param1；
   if (判断条件) then
        select 列名 into 变量2 from 表A where列名=param1；
        dbms_output.Put_line('打印信息');
   elsif (判断条件) then
        dbms_output.Put_line('打印信息');
   else
        raise 异常名（NO_DATA_FOUND）;
   end if;
exception
   when others then
        rollback;
end;

1.4、执行存储过程的语法

?方法一：

//执行存储过程语法1
CALL 存储过程名称(参数1,参数2);

//执行存储过程示例
CALL CACULATESALARY(1,5000);

方法二：

//执行存储过程语法2
BEGIN
存储过程名称(参数1,参数2);
END;


//执行存储过程示例
BEGIN
caculatesalary(1,5000);
END;

1.5、存储过程的示例

? ? 示例1：实现输入雇员的编号和涨薪资的数额先是查看原薪资；然后是更新该雇员的薪资，然后打印出来查看；最后如果更新失败则需要回滚。

表的内容如下：

?存储过程如下：

CREATE OR REPLACE PROCEDURE CaculateSalary 
(peopleNumber in NUMBER,needIncreaseSalaryNumber in NUMBER)
AS

TotalSalary NUMBER:=0;

BEGIN
	
	--获取到当前的工资
	SELECT SALARY INTO TotalSalary from PEOPLE WHERE PEOPLE.ID=peopleNumber;
	--输出张薪资前的工资
	dbms_output.put_line('涨薪前工资是：'||TotalSalary);
	--涨工资后的工资
	UPDATE PEOPLE SET SALARY=(TotalSalary+needIncreaseSalaryNumber) WHERE ID=peopleNumber;
  --输出张薪资后的工资
	dbms_output.put_line('涨薪后工资是：'||(TotalSalary+needIncreaseSalaryNumber));
	--提交事务
	commit;
	
	--异常则回滚
	EXCEPTION
	 WHEN OTHERS THEN
	 rollback;
	 dbms_output.put_line('涨薪异常执行回滚操作');
END;

?示例2：想要传入参数，然后又通过该参数传递内容出去

CREATE OR REPLACE procedure testInuptOutput
(message in out varchar2)
--当既想携带值进来，又想携带值出去，可以用in out
as

begin
  dbms_output.put_line('当前输入的内容为：'||message); --输出的为携带进来的值
  message:='我是经过处理后的值'||998;
end;

二、存储函数

2.1、存储函数介绍

? ? 存储函数（Stored Function）是创建之后保存在数据库中，且封装在oracle服务器中的一段已经完成的plsql代码片段。

2.2、存储函数的特点

存储函数(Stored Function)的特点
序号	存储函数的特点
1	在数据库启动时自动加载
2	函数没有参数输入输出之分
3	函数必须有返回值
4	调用函数时必须使用它的返回值
5	存储函数即可以在sql 语句中使用,也可以在plsql中使用

2.3、存储函数的创建语法

参数的3种类型
序号? ??	参数类型说明
1	IN 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变
2	OUT 模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
3	IN OUT 表示该参数可以向该过程中传递值，也可以将某个值传出去

//存储函数的基础语法
create [for replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型）

return 参数类型

is | as

begin


end;




//存储函数的详细语法
Create [or replace] function 函数名称（参数名称 in|out 参数类型，参数名称 in|out 参数类型，...）

Return 结果变量数据类型

Is | as

变量声明部分；

Begin

逻辑部分；

Return 结果变量；

[exception 异常处理部分]

End；

注意：end后面的；不能取消掉。参数列表里面默认是输入类型 in

2.4、执行存储函数的语法

//执行存储函数方法1（放在表达式中）

SELECT 函数名称(参数)  FROM dual;



//执行存储函数方法1示例
SELECT TIMEGROUP(TO_DATE('2022-02-12 17:30:36', 'yyyy-MM-dd hh24:mi:ss'))handleTime FROM dual;

//执行存储函数方法2

declare 

 变量名称 类型;

begin

 --赋值存储函数结果给变量
 变量名称:=存储函数名称
 --打印变量结果
 dbms_output.put_line(变量名称);

end;



//执行存储方法2示例
declare 

result varchar(16);

begin

 result:= TIMEGROUP(TO_DATE('2022-02-12 17:30:36', 'yyyy-MM-dd hh24:mi:ss'));

 dbms_output.put_line(result);

end;

2.5、存储函数示例

示例：实现对日期分组（即：以半小时开始间隔一个小时的为一组

①【比如：2022-02-13 07:30:00一直到2022-02-13 08:29:59】的归为2022-02-13 08:30:00

②【比如：2022-02-13 08:30:00一直到2022-02-13 09:29:59】的归为2022-02-13 09:30:00）

存储函数如下：

CREATE OR REPLACE FUNCTION TimeGroup(inputDate IN DATE) RETURN VARCHAR2
AS
result VARCHAR(16):='';
input_yyyy_MM_dd_hh24 VARCHAR(13):=to_char(inputDate,'yyyy-MM-dd hh24');
tmp_timegroup DATE:=TO_DATE(input_yyyy_MM_dd_hh24||'30:00','yyyy-MM-dd hh24:mi:ss');


BEGIN
  IF inputDate>=tmp_timegroup 
	THEN result:=SUBSTR(TO_CHAR((tmp_timegroup+1/24),'yyyy-MM-dd hh24:mi:ss'),0,16);
	ELSE result:=SUBSTR(TO_CHAR((tmp_timegroup),'yyyy-MM-dd hh24:mi:ss'),0,16);
	END IF;
	RETURN result;
END;

?三、存储过程和存储函数的相同点和区别

存储过程和存储函数的相同点
序号	存储过程和存储函数的相同点
1	创建语法结构相似，都可以携带多个传入参数和传出参数
2	都是一次编译，多次运行
3	都可以使用【in/ out /in out】三种模式的参数

存储过程和存储函数的区别
存储过程	存储函数
用于在数据库中完成特定的操作（或任务）【过程一般会被设计成求若干个运算结果，完成一系列的数据处理，或与计算无关的各种操作】（比如：插入、更新、删除等操作）	用于特定的数据操作【只为求得一个值】（比如：归类、分组）
程序头部使用【Procedure】声明	程序头部使用【Function】声明
程序头部声明时不需要任何返回类型	程序头部申明时必须描述返回类型，并且必须在PL/SQL块中包含一个有效的return语句
可以作为一个独立的PL/SQL语句来执行	不能够独立执行，必须作为表达式的一部分调用
可以通过out /in out 返回零个或多个值	①通过return语句返回一个值，且该返回值需要与声明的内容一致； ②也可以通过out类型的参数带出变量
SQL语句（DML或SELECT）中不可调用存储过程	SQL语句（DML或SELECT）中可调用存储函数