oracle存储过程

最新推荐文章于 2025-04-22 17:25:01 发布

数字天下

最新推荐文章于 2025-04-22 17:25:01 发布

阅读量1.1k

点赞数 33

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/qq_22201881/article/details/146914139

版权

1.存储过程概念和优势
块是构成一个PL/SQL程序的基本单位。块包括匿名块，命名块两种。匿名块没有名称，只是一段代码。命名块分为过程和函数。
存储过程的定义：这些命名的存储到Oracle服务器中的PL/SQL块称为为存储过程和函数。
存储过程有以下的优点：
(1)存储过程的能力大大增强了SQL语言的功能和灵活性。
(2)存储过程可以用流控语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(3)可保证数据的安全性和完整性。
7.4.2存储过程的格式
存储过程的格式：
CREATE OR REPLACE PROCEDURE name [ (parameter [，parameter，…] ) ] IS //或AS
[说明:变量定义于此]
BEGIN
执行语句序列
[EXCEPTION
例外处理程序
END [name ];

1.参数
in：输入类型，
由应用程序将数据传入Oracle存储过程中，表示调用者向过程传入值。
out：输出类型，表示过程向调用者传出值。
in out：输入输出类型，兼具以上两种特性，但可读可写；既表示调用者向过程传入值，又表示过程向调用者传出值。
2.变量和常量
在PL/SQL程序中可将值存储在变量和常量中，当程序执行时，变量的值可以改变，而常量的值不能改变。
定义常量，比如定义变量长度等于10，格式如下:
chang:=10
变量必须在定义声明部分定义。为变量赋值使用SELECT INTO 语句，具体格式如下：
SELECT
{字段列表}
INTO {variable [，variable，…] }
FROM {table名}
WHERE … … …
注意：SELECT INTO语句必须保证查询出的结果集只有一条记录。
2存储过程调用，删除
存储过程可以在 SQL Plus或PL/SQL工具中调用。
在SQL Plus中调用，具体格式如下：
exec pro_name(param1， param2，…);
在PL/SQL工具中调用
打开想要调试的存储过程，在存储过程名称处右键单击，从弹出菜单中选择Test选项。
存储过程的删除，格式如下：
drop procedure pro_name;
【例7-12】商品有原先的价格，之后由于市场的原因，价格按一定比例上涨，求出上涨之后商品的价格。建立一个存储过程，输入参数为原先价格，价格上涨比列。输出参数为上涨之后的价格。
代码如下：
第七章\ccgs.sql

CREATE OR REPLACE PROCEDURE hike_prices (old_price NUMBER，
percent_hike NUMBER，
new_price OUT NUMBER)
IS
BEGIN
new_price := old_price + old_price * percent_hike / 100;
END hike_prices;

执行存储过程，输入原价格为150，上涨比列为12，通过存储过程计算出新的价格为168，如图7-22所示。
在这里插入图片描述

图7-22执行存储过程

【例7-13】建立房屋表，包括字段长，宽，建筑面积，使用面积。求出该房屋的建筑面积，使用面积。更新房屋表建筑面积，使用面积字段的数据。
第七章\ccgs.sql
–建立房屋表
create table fangwu(
name varchar2(20)，
chang number(5，2)，
kuan number(5，2)，
jianzhu number(10，2)，
shiyong number(10，2)
);
insert into fangwu values(‘王海’，4，3.5，0，0);
insert into fangwu values(‘张明’，6，4，0，0);
insert into fangwu values(‘孙坚’，8，5，0，0);
commit;
—建立存储过程，计算建筑面积和使用面积
create or replace procedure pro_fwjs
is
begin
update fangwu a set (name，jianzhu，shiyong)=(select name，changkuan+2.5， changkuan
from fangwu c where a.name=c.name);
commit;
end pro_fwjs;
7.4.4存储过程和视图的区别
(1)存储过程应用范围比视图广很多。存储过程和视图都相当于执行一段已经编译好的sql脚本。储过程可以提供多种参数，每次返回多个表数据。视图一次只能返回一个二维表的数据，且不能接受任何参数。
(2)视图就是张虚拟表，可以像对真实表一样对其进行操作，而存储过程是一组sql语句的批处理。
(3)存储过程可以包含多个语句，包括循环，控制语句等。视图只包含查询语句。
7.5存储函数
PL/SQL中的过程和函数(通常称为子程序)是PL/SQL块的一种特殊的类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用。可以把经常使用的功能定义为一个函数，就像系统自带的函数(例如大小写转换，求绝对值等函数)一样使用。
存储函数的格式如下：
CREATE OR REPLACE funtion name [ (parameter [，parameter，…] ) ] IS //或AS
[说明:变量定义于此]
BEGIN
执行语句序列
Return (结果变量)
[EXCEPTION
例外处理程序
END [name ];
存储函数的特点：
在数据库启动时自动加载。
函数必须有返回值，调用函数时必须使用它的返回值。
存储函数即可以在sql 语句中使用，也可以在plsql中使用。
调用存储函数，格式如下：
exec 函数名称
删除存储函数，格式如下：
drop funtion 函数名称

【例7-14】建立雇员工资表，插入测试数据，要求写一个函数，输入职工号后计算出职工的年薪。
具体代码如下：
第七章\ccgs.sql
–建立雇员工资表
create table SM_EMP
(
empid VARCHAR2(30)，
name VARCHAR2(30)，
salary NUMBER(6，2)，
telno CHAR(8)
);
–插入记录
insert into SM_EMP
values (‘000000001’，‘张飞飞’，5500.00，‘6678562’);
insert into SM_EMP
values (‘000000002’，‘关庭’，4500.00，‘87825626’);
insert into SM_EMP
values (‘000000003’，‘孙海’，6200.00，‘87783617’);
Commit;
–建立存储函数，实现计算职工年薪
create or replace function empincome(empno in varchar2) return number
is
v_sal number;
v_income number;
begin
select salary into v_sal from SM_EMP where empid=empno;
v_income:=v_sal*12;
return(v_income);
end empincome;