九、存储过程——提高程序执行的效率-数据库-半夏微凉

九、存储过程——提高程序执行的效率

首先区分一下存储过程和视图的区别：

存储过程

存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：

可以在单个存储过程中执行一系列SQL语句。

可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。

存储过程的功能取决于数据库所提供的功能。

视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。分布式查询也可用于定义使用多个异类源数据的视图。如果有几台不同的服务器分别存储组织中不同地区的数据，而您需要将这些服务器上相似结构的数据组合起来，这种方式就很有用。

通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

补充：在内部来说其实两者实现基本一致，视图更重于数据展示，而存储过程更重于数据内部逻辑处理

1.什么是存储过程。

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

2.存储过程的作用。

1）简单化复杂的操作。

2）增加数据独立性。

3）提高安全性。

4）提高性能。

3.存储过程语法。

CREATE OR REPLACE PROCEDURE [schema.] procedure_name
[parameter_name [[in] datatype[{:=|default}expression]
|{out|in out}[nocopy]datatype][,...]
{is | as}
BODY;

3.创建存储过程（范例）。

例1：

创建：

set serveroutput on
create or replace procedure pro_lentim as
begin
dbms_output.put_line('我的第一个存储过程');
end;
/
执行：
begin
pro_lentim;
end;
/
我的第一个存储过程

例2：

4.查看存储过程。

select * from user_source where NAME='PRO_LENTIM' order by LINE; --从user_source表中查询过程或函数需把名称大写。

5.查看存储过程错误。

show errors procedure pro_lentim;

6.无参存储过程示例。（存储过程无参数）

create or replace procedure pro_wucang as
begin
update t_avg
set t_avg.type = '促销'
where t_avg.jiage in (
select t_avg.jiage from (
select *from t_avg order by t_avg.jiage desc
)
where rownum < 4
);
commit;
end;
/
执行存储过程，验证是否成功。
EXEC pro_wucang;
SQL> select * from t_avg order by jiage asc;
ID NAME SHULIANG TYPE JIAGE
---------- ---------- ---------- ---------- ----------
1 苹果 5 促销 12
2 桃子 8 促销 30
3 莉 10 促销 33
1 芒果 6 45
2 李子 12 77
3 柠檬 8

7.存储过程中使用游标

set serveroutput on
create or replace procedure pro_youbiao as
pro_typeid t_type.typeid%type;
pro_typename t_type.type%type;
cursor cur_type is select typeid from t_type group by typeid;
begin
open cur_type;
loop
fetch cur_type into pro_typeid;
exit when cur_type%notfound;
select t_type.type into pro_typename from t_type where typeid=pro_typeid;
if sql%found then
dbms_output.put_line('----------------------------------');
dbms_output.put_line(pro_typename||'：');
end if;
for a in (select * from t_procedure where type=pro_typename)
loop
dbms_output.put_line('商品名称：'||a.name||'商品数量：'||a.shuliang||'商品价格：'||a.jiage);
end loop;
end loop;
end;
/
SQL> exec pro_youbiao
----------------------------------
水果：
商品名称：苹果商品数量：100商品价格：9
商品名称：芒果商品数量：100商品价格：5
----------------------------------
电子：
商品名称：小米4商品数量：50商品价格：1499
商品名称：mini平板商品数量：20商品价格：2000
商品名称：MP3商品数量：50商品价格：300
----------------------------------
家具：
商品名称：双人床商品数量：3商品价格：1000

8.存储过程中的DDL语句。(创建临表)

书上例题解法：

set serveroutput on
create or replace procedure pro_ddl as
b_create varchar2(500);
b_delete varchar2(500);
b_insert varchar2(500);
b_yes_no varchar2(10);
b_type t_type.type%type;
b_output t_procedure%rowtype;
cursor cur_a is select type from t_type group by type;
cursor cur_b(a_type varchar) is select * from (select * from t_procedure where type=a_type order by jiage asc) where rownum<2;
begin
select count(1) into b_yes_no from all_tables t where t.TABLE_NAME='T_PROCEDURE_TMP';
b_create:='create global temporary table T_PROCEDURE_TMP (ID varchar2(10),NAME VARCHAR2(10),SHULIANG VARCHAR2(10),TYPE VARCHAR2(10),JIAGE VARCHAR2(10)) ON COMMIT PRESERVE ROWS';
b_delete:='delete from T_PROCEDURE_TMP';
b_insert:='insert into T_PROCEDURE_TMP (ID,NAME,SHULIANG,TYPE,JIAGE) VALUES
('''
||b_output.ID
||''','''
||b_output.NAME
||''','''
||b_output.SHULIANG
||''','''
||b_output.TYPE
||''','''
||b_output.JIAGE
||''')';
if b_yes_no=0 then
execute immediate b_create;
dbms_output.put_line('成功创建t_procedure_tmp临时表');
else
execute immediate b_delete;
dbms_output.put_line('t_procedure_tmp临时表已存在，且已清空表数据');
end if;

open cur_a;
loop
fetch cur_a into b_type;
exit when cur_a%notfound;
open cur_b(b_type);
fetch cur_b into b_output;
if cur_b%found then
if b_output.jiage<20 then
dbms_output.put_line('产品ID：'||b_output.id||'产品名称：'||b_output.name||'价格：'||b_output.jiage);
else
execute immediate 'insert into T_PROCEDURE_TMP (ID,NAME,SHULIANG,TYPE,JIAGE) VALUES
('''
        ||b_output.ID
       ||''','''
       ||b_output.NAME
       ||''','''
       ||b_output.SHULIANG
       ||''','''
       ||b_output.TYPE
       ||''','''
        ||b_output.JIAGE
       ||''')';
end if ;
end if;
close cur_b;
end loop;
commit;
close cur_a;
execute immediate 'update T_PROCEDURE_TMP SET TYPE=''促销''';
end;
/

SQL> EXEC PRO_ddl
t_procedure_tmp临时表已存在，且已清空表数据
产品ID：1产品名称：芒果价格：5
PL/SQL procedure successfully completed
SQL> select * from T_PROCEDURE_TMP
2 /
ID NAME SHULIANG TYPE JIAGE
---------- ---------- ---------- ---------- ----------
6 双人床 3 促销 1000
5 MP3 50 促销 300

注：

1、表名最好全用大写，用小写可能会出现查不到表的情况。

2、有一个疑问是我用b_insert去替换插入语句时居然无效，用来创建表和删除表数据时都没有问题，为什么插入数据时就不能用了呢？感觉真是奇怪。

3.execute immediate语句不得于执行效率，能少用就少用。

--------------------------------------------------------------------------------------

个人练习代码：

declare
a_output t_procedure%rowtype;
cursor cur_a is select * from t_procedure where jiage<20;
begin
open cur_a;
loop
fetch cur_a into a_output;
exit when cur_a%notfound;
dbms_output.put_line('产品id:'||a_output.id||'产品名称：'||a_output.name||'产品价格：'||a_output.jiage);
end loop;
end;
/
set serveroutput on
declare
b_create varchar2(200):='create global temporary table t_procedure_tmp(id varchar2(10),name varchar2(10),shuliang varchar2(10),type varchar2(10),jiage varchar2(10))on commit preserve rows';
b_delete varchar2(200):='delete from t_procedure_tmp';
b_insert varchar2(200);
b_yes_no varchar2(10);
b_output t_procedure%rowtype;
cursor cur_b is select * from t_procedure where jiage>20;
begin
select count(1)into b_yes_no from SYS.ALL_TABLES t where TABLE_NAME='T_PROCEDURE_TMP';
if b_yes_no=0 then
execute immediate b_create;
dbms_output.put_line('临表t_procedure_tmp创建成功');
else
execute immediate b_delete;
dbms_output.put_line('表记录删除成功');
end if;
open cur_b;
loop
fetch cur_b into b_output;
exit when cur_b%notfound;
execute immediate 'insert into t_procedure_tmp (ID,NAME,SHULIANG,TYPE,JIAGE) values
('''
||b_output.ID
||''','''
||b_output.NAME
||''','''
||b_output.SHULIANG
||''','''
||b_output.TYPE
||''','''
||b_output.JIAGE
||''')';
end loop;
commit;
close cur_b;
execute immediate 'update T_PROCEDURE_TMP set type=''促销商品''';
end;
/

9.有参的存储过程。

create or replace procedure pro_youcang(a_type in varchar2) as -----输入参数a_type
a_id t_type.typeid%type;
begin
select t_type.typeid into a_id from t_type where t_type.type=a_type;
if sql%found then
dbms_output.put_line('-----------------------------');
dbms_output.put_line(a_type||'：');
end if;
for my_data_rec in (select * from t_procedure where t_procedure.type=a_type)-----for in 语句的灵活使用
loop
dbms_output.put_line('产品名称'||my_data_rec.name||'产品类别：'||my_data_rec.type||'产品价格：'||my_data_rec.jiage);
end loop;
exception
when no_data_found then
dbms_output.put_line('没有该类别数据');
when too_many_rows then
dbms_output.put_line('数据过多！');
end;
/
SQL> exec pro_youcang('电子');
-----------------------------
电子：
产品名称小米4产品类别：电子产品价格：1499
产品名称mini平板产品类别：电子产品价格：2000
产品名称MP3产品类别：电子产品价格：300
PL/SQL procedure successfully completed
SQL> exec pro_youcang('测试');
没有该类别数据

10.有默认值的带参数的存储过程。

create or replace function fun_moren return varchar2 is
begin
dbms_output.put_line('------已经进入到函数---------');
dbms_output.put_line('默认值是“水果”');
return '水果';
end fun_moren;
/
SQL> select fun_moren() from dual;
FUN_MOREN()
--------------------------------------------------------------------------------
水果
------已经进入到函数---------
默认值是“水果”

create or replace procedure pro_youcang(a_type in varchar2 default fun_moren()) as -----添加了默认的参数（使用function函数）
a_id t_type.typeid%type;
begin
select t_type.typeid into a_id from t_type where t_type.type=a_type;
if sql%found then
dbms_output.put_line('-----------------------------');
dbms_output.put_line(a_type||'：');
end if;

for my_data_rec in (select * from t_procedure where t_procedure.type=a_type)-----for in 语句的灵活使用
loop
dbms_output.put_line('产品名称'||my_data_rec.name||'产品类别：'||my_data_rec.type||'产品价格：'||my_data_rec.jiage);
end loop;
exception
when no_data_found then
dbms_output.put_line('没有该类别数据');
when too_many_rows then
dbms_output.put_line('数据过多！');
end;
/
SQL> exec pro_youcang;
------已经进入到函数---------
默认值是“水果”
-----------------------------
水果：
产品名称苹果产品类别：水果产品价格：9
产品名称芒果产品类别：水果产品价格：5

11.存储过程中输出类型参数的使用

create or replace procedure pro_out(a_name in varchar2,a_id out varchar2) as
begin
select typeid into a_id from t_type where type=a_name;
if sql%found then
dbms_output.put_line(a_name||'对应的id是：'||a_id);
end if;
exception
when no_data_found then
dbms_output.put_line('没有数据');
when too_many_rows then
dbms_output.put_line('数据重复');
end pro_out;
/
create or replace procedure pro_out_yingyong(a_name in varchar2,a_jiage number) as
b_id t_type.typeid%type;
b_output t_procedure%rowtype;
begin
pro_out(a_name,b_id);
if sql%found then
dbms_output.put_line('-------------------');
dbms_output.put_line(a_name||' 产品的ID是：'||b_id);
end if;
for my_data_rec in (select * from t_procedure a where type_id=b_id and a.jiage<a_jiage)
loop
dbms_output.put_line('产品名称：'||my_data_rec.name||'产品价格：'||my_data_rec.jiage);
end loop;
end;
/
SQL> exec pro_out_yingyong('电子',700);
电子对应的id是：3
-------------------
电子产品的ID是：3
产品名称：MP3产品价格：300

12.存储过程中in out函数的调用。

create or replace procedure pro_inout(a_id in varchar2,a_jiage in out number) as
begin
update t_procedure t set t.jiage=t.jiage*(1-a_jiage) where t.type_id=a_id;
if sql%found then
a_jiage:=sql%rowcount;
end if;
end pro_inout;
/
------------------------------
create or replace procedure pro_inout_yingyong(b_name in varchar2) as
b_n number;
b_id t_type.typeid%type;
begin
select t_type.typeid into b_id from t_type where t_type.type=b_name;
pro_inout(b_id,b_n);
if b_n>0 then
dbms_output.put_line('共修改了'||b_n||'条记录');
end if;
exception
when too_many_rows then
dbms_output.put_line('数据过多');
when no_data_found then
dbms_output.put_line('没有数据');
end;
/
SQL> exec pro_inout_yingyong('水果')
共修改了2条记录

13.使用plsql工具创建存储过程。

14.修改存储过程

create or replace procedure procedure_name.....

15.删除存储过程

drop procedure procedure_name;

半夏微凉

九、存储过程——提高程序执行的效率

评论回复