Oracle + MySQL [存储过程和函数]

概述

ORACLE 提供 可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。
存储过程和函数统称为PL/SQL子程序

存储过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

存储过程

存储就是在数据库服务器中封装了一段或多段SQL语句，完成某一特定功能的PL/SQL代码块。它可以被用户随时调用，这种方式极大的节省了用户的时间，也提高了程序的执行效率。另外存储过程还可以在编程语言中调用。

存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。

优点

1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。
2. 建立过程不会很耗系统资源，因为过程只是在调用才执行。
3. 可重复使用。
4. 可维护性高。

缺点

大量的利用过程，会对服务器压力比较大

语法

创建存储过程的语法
create [or replace] procedure 名字[(参数 in|out|in out 参数数据类型,...)]
is|as
   声名部分;
begin
   plsql代码块;
   exception 
     异常处理;
end;

调用
1.在plsql块中调用 
begin
    name();
end
2.call命令调用  --call 名字(); 
3.execute命令调用（sqlplus中输出）    
 /*Sqlplus 小黑窗
set serverout on;  打开服务输出
execute 用户名.过程名();
Begin 
过程名();
end;*/

删除存储过程
drop procedure 名字;

参数

无参数
create or replace procedure p
is
    cursor cur is select * from a;
begin
    for v in cur loop
        dbms_output.put_line(v.name)
    end loop;
end;

三种模式
IN  用于接受调用程序的值。默认的参数模式,可写可不写
传入
/* 根据传入的id，查询该id下的名字年龄
create or replace procedure p(vid in number) --变量类型，多个变量逗号隔开
is

begin
    for v in (select * from a where id = vid) loop
        dbms_output.put_line(v.name || v.age)
    end loop;
end;
--调用
declare
--v number;
begin
    --v := 10;
    --p(vid => v);
    p(6);--如果两个变量就逗号隔开
end;
call p(vid => 6);
*/
OUT  用于向调用程序返回值 
输出
/*根据传入的id，查询名字并以输出参数的方式返回
create or replace procedure p(vid in number,va out a%rowtype)
is

begin
    select * into va from a where id = vid;
end;
declare
    va a%rowtype;
begin
    p(6,va);
    dbms_output.put_line(va.name || va.age)
end;
*/
IN OUT  用于接受调用程序的值，并向调用程序返回更新的值
/*根据传入的id，查询名字并以输出参数的方式返回
create or replace procedure p(va in out a%rowtype)
is

begin
    select * into va from a where id = va,id;
end;
declare
    va a%rowtype;
begin
    va.id := 6;
    p(va); --声明几个传几个 声明什么传什么
    dbms_output.put_line(va.name || va.age)

end;
*/

以id为参数，输出工资
CREATE OR REPLACE PROCEDURE PP(AID IN A.ID%TYPE)
IS
    ASAL A.SAL%TYPE;
BEGIN
    SELECT SAL INTO ASAL FROM A WHERE ID = AID;
        DBMS_OUTPUT.PUT_LINE(ASAL);
EXCEPTION
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('没有这个id');
END;
CALL PP(1);

以ID为参数，输出年龄最大的十个
CREATE OR REPLACE PROCEDURE PP(A IN OUT  A%ROWTYPE)
IS
    CURSOR CUR IS SELECT * FROM A WHERE ID = A.ID ORDER BY DESC AGE;
BEGIN
    FOR V IN CUR LOOP
        IF CUR%ROWCOUNT <= 10 THEN
            DBMS_OUTPUT.PUT_LINE(A.NAME||A.SEX);
        END IF;
    END LOOP;
END;

DECLARE
    Q A%ROWTYPE;
BEGIN
    Q,ID := 1;
    PP(Q);
END;

MySQL
create table student(
id int(2) PRIMARY KEY,
sname VARCHAR(100),
age int(30)
);

delimiter$$ --开头
CREATE PROCEDURE P2 (
    IN ID INT,
    IN SNAME VARCHAR (255), --varchar后面加范围
    IN AGE INT
)
BEGIN
    INSERT INTO STUDENT
VALUES
    (ID, SNAME, AGE);

END$$ --结尾 

存储过程的优化

1. 利用一些sql语句(聚合函数)来替一些小循环
2. 采用拼凑语句，将更新语句在循环中拼凑后，在统一更新
3. 中间结果存放临时表，加索引
4. 少用游标
5. 事务越短越好
6. 查找语句尽量不要放在循环内。

函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。

创建
create [or replace] function 名称[(形式参数 参数类型,...)]
return 返回值数据类型 --不需要加长度
is
   声名变量;
begin
  plsql代码块;
  return 返回值;--return后面的内容不执行
end;

访问函数的方式
1.使用 SQL 语句
Select 函数名字() from dual;

2.使用 PL/SQL 块
declare
   v varchar2(30);
begin
   v:=f1;
  dbms_output.put_line(v);
end;

删除
drop function 名字

参数

无参数
/*
create or replace function f
return varchar2
is

begin
    return '哈哈哈';
end;
select f from dual;
*/

有参数 --只能有一个，多个返回第一个
/*接受一个数，返回1到这个数的和
create or replace function f(num number)
return number
is
    sum number := 0;
begin
    for v in 1..num loop
        sum := sum+v;
    end loop;
    return sum;
end;
select f(10) from dual; --55
declare

begin
    dbms_output.put_line(f(3)); --6
    end;
*/

存储过程和函数的区别

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

1. 存储过程可以执行包括修改表等一系列数据库操作
   （一般用于在数据库中完成特定的业务或任务)
2. 函数不能用于执行一组修改全局数据库状态的操作。
   （一般用于特定的数据查询或数据转转换处理)

对于存储过程来说可以返回参数，而函数只能返回值或者表对象。

1. 函数只能返回一个变量，存储过程可以返回多个。
2. 存储过程的参数可以有IN,OUT, IN OUT三种类型，而函数只能有IN类。
3. 存储过程声明时可以定义返回类型，也可以不定义返回类型
   函数声明时必须要定义返回类型，申请时且程序体中必须定义 return 语句

存储过程一般是作为一个独立的部分来执行
函数可以作为查询语句的一个部分来调用（SELECT调用）

• 由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
• SQL语句中不可用存储过程，而可以使用函数。