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 过程名

1	`DROP` `PROCEDURE` `HELLO;`

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

1	`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 函数名

1	`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

posted on 2018-03-20 16:47 土地革命阅读(140) 评论(0) 收藏举报