oracle存储过程和存储函数

存储过程

什么是存储过程【procedure】？

事先运用oracle语法，写好的一段具有业务功能的程序片段，长期保存在oracle服务器中

语言远程访问，类似于java中的函数。

为什么要用存储过程？
    （1）PLSQL每次执行都要整体运行一遍，才有结果
    （2）PLSQL不能将其封装起来，长期保存在oracle服务器中
    （3）PLSQL不能被其它应用程序调用，例如：Java

存储过程与PLSQL是什么关系？
存储过程是PLSQL的一个方面的应用，PLSQL是存储过程的基础

存储过程的概念

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

创建无参存储过程hello，无返回值，语法：create or replace procedure 过程名 as PLSQL程序

删除存储过程hello，语法：drop procedure 过程名

调用存储过程方式一，exec 存储过程名

调用存储过程方式二，PLSQL程序

调用存储过程方式三，Java程序

创建无参存储过程HELLO，无返回值，语法：CREATE OR REPLACE PROCEDURE 过程名 AS PLSQL程序

CREATE OR REPLACE PROCEDURE HELLO
AS
BEGIN
	DBMS_OUTPUT.PUT_LINE('第一个存储过程');
END;
/

删除存储过程HELLO，语法：DROP PROCEDURE 过程名

DROP PROCEDURE HELLO;

调用存储过程方式一，EXEC 存储过程名

EXEC HELLO;

调用存储过程方式二，PLSQL程序

BEGIN 
	HELLO;
END;
/

调用存储过程方式三，JAVA程序

使用CALLABLESTATEMENT类进行调用

创建有参存储过程RAISESALARY(编号)，为7369号员工涨10%的工资，演示IN的用法，默认IN，大小写不敏感

CREATE OR REPLACE PROCEDURE RAISESALARY(PEMPNO IN NUMBER)
AS
BEGIN
	UPDATE EMP SET SAL=SAL*1.2 WHERE EMPNO=PEMPNO;
END;
/

调用
EXEC RAISESALARY(7369);

创建有参存储过程FINDEMPNAMEANDSALANDJOB(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示OUT的用法

CREATE OR REPLACE PROCEDURE FINDINFO(PEMPNO IN NUMBER,PENAME OUT VARCHAR2,PJOB OUT VARCHAR2,PSAL OUT NUMBER)
AS
BEGIN
	SELECT ENAME,JOB,SAL INTO PENAME,PJOB,PSAL FROM EMP WHERE EMPNO=7788;
END;
/

调用
DECLARE
	PENAME EMP.ENAME%TYPE;
	PJOB EMP.JOB%TYPE;
	PSAL EMP.SAL%TYPE;
BEGIN
	FINDINFO(7788,PENAME,PJOB,PSAL);
	DBMS_OUTPUT.PUT_LINE('7788员工的姓名是'||PENAME||'工作是'||PJOB||'薪资是'||PSAL);
END;
/

什么情况下用EXEC调用，什么情况下用PLSQL调用存储过程？
EXEC适合存储过程没有返回值
PLSQL适合存储过程含有多个返回值

存储函数

【1】创建无参存储函数GETNAME，有返回值，语法：CREATE OR REPLACE FUNCTION 函数名 RETURN 返回类型 AS PLSQL程序段

CREATE OR REPLACE FUNCTION GETNAME RETURN VARCHAR2
AS
BEGIN
	RETURN 'hello function';
END;
/

【2】删除存储函数GETNAME，语法：DROP FUNCTION 函数名

DROP FUNCTION GETNAME;

【3】调用存储函数方式一，PLSQL程序

declare
	name varchar2(50);
begin
	name:=getName();
	dbms_output.put_line(name);
end;
/

【4】调用存储函数方式二，JAVA程序

【5】创建有参存储函数findempincome(编号)，查询7369号员工的年收入，演示IN的用法，默认IN

create or replace function findNumber(pempno in number) return number
as 
	psal number;
begin
	select sal into psal from emp where empno=pempno;
	return psal;
end;
/

调用
declare 
	income number;
begin
	income:=findNumber(7369);
	dbms_output.put_line(income);
end;
/

【6】创建有参存储函数findempnameandjobandsal(编号)，查询7788号员工的的姓名(RETURN)，职位(OUT)，月薪(OUT)，返回多个值

create or replace function findSix(pempno in number,pjob out varchar2,psal out varchar2) return varchar2
as
	pename emp.ename%type;
	--pjob emp.job%type;
	--psal emp.sal%type;
begin
	select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
	return pename;
end;
/


-- 调用
declare
	pename emp.ename%type;
	pjob emp.job%type;
	psal emp.sal%type;
begin
	pename:=findSix(7788,pjob,psal);
	dbms_output.put_line(pename||pjob||psal);
end;
/

过程函数适合场景

声明：适合不是强行要你使用，只是优先考虑

什么情况下【适合使用】存储过程？什么情况下【适合使用】存储函数？

【适合使用】存储过程： 无返回值或者由多个返回值时适合过程。
【适合使用】存储函数：有且只有一个返回值时，适合函数。
   
什么情况【适合使用】过程函数，什么情况【适合使用】SQL？
【适合使用】过程函数：
   》需要长期保存在数据库中
            》需要被多个用户重复调用
            》业务逻辑相同，只是参数不一样
   》批操作大量数据，例如：批量插入很多数据


【适合使用】SQL：
   》凡是上述反面，都可使用SQL
   》对表，视图，序列，索引，等这些还是要用SQL