概述
ORACLE 提供 可以把PL/SQL 程序存储在数据库中,并可以在任何地方来运行它。这样就叫存储过程或函数。
存储过程和函数统称为PL/SQL子程序
存储过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据。
存储过程
存储就是在数据库服务器中封装了一段或多段SQL语句,完成某一特定功能的PL/SQL代码块。它可以被用户随时调用,这种方式极大的节省了用户的时间,也提高了程序的执行效率。另外存储过程还可以在编程语言中调用。
存储在数据库中,并通过输入、输出参数或输入/输出参数与其调用者交换信息。
优点
- 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译。
- 建立过程不会很耗系统资源,因为过程只是在调用才执行。
- 可重复使用。
- 可维护性高。
缺点
大量的利用过程,会对服务器压力比较大
语法
创建存储过程的语法
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$$ --结尾
存储过程的优化
- 利用一些sql语句(聚合函数)来替一些小循环
- 采用拼凑语句,将更新语句在循环中拼凑后,在统一更新
- 中间结果存放临时表,加索引
- 少用游标
- 事务越短越好
- 查找语句尽量不要放在循环内。
函数
函数是通过关键字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;
*/
存储过程和函数的区别
一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。
- 存储过程可以执行包括修改表等一系列数据库操作
(一般用于在数据库中完成特定的业务或任务) - 函数不能用于执行一组修改全局数据库状态的操作。
(一般用于特定的数据查询或数据转转换处理)
对于存储过程来说可以返回参数,而函数只能返回值或者表对象。
- 函数只能返回一个变量,存储过程可以返回多个。
- 存储过程的参数可以有IN,OUT, IN OUT三种类型,而函数只能有IN类。
- 存储过程声明时可以定义返回类型,也可以不定义返回类型
函数声明时必须要定义返回类型,申请时且程序体中必须定义 return 语句
存储过程一般是作为一个独立的部分来执行
函数可以作为查询语句的一个部分来调用(SELECT调用)
- 由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。
- SQL语句中不可用存储过程,而可以使用函数。