存储过程与函数
1、存储过程
存储过程的参数
形式参数和实际参数,例如,有如下一个存储过程,该过程接收一个作家代码和一个工资值,将该作家的工资改为接收到的工资值。
Java代码
create or replace procedure updateauths(
p_authscode auths.author_code%type,
p_authssalary auths.salary%type)
as
begin
update auths set salary=p_authssalary where author_code=p_authscode;
commit;
end updateauths;
create or replace procedure updateauths(
p_authscode auths.author_code%type,
p_authssalary auths.salary%type)
as
begin
update auths set salary=p_authssalary where author_code=p_authscode;
commit;
end updateauths;下面的PL/SQl块调用updateauths存储过程,将代码为A00011的作家的工资改为350元。
Java代码
declare
v_authorcode auths.author_code%type:='A00011';
v_salary auths.salary%type:=350;
begin
updateauths(v_authorcode,v_salary);
end;
declare
v_authorcode auths.author_code%type:='A00011';
v_salary auths.salary%type:=350;
begin
updateauths(v_authorcode,v_salary);
end;
v_authorcode、v_salary作为参数传递到存储过程updateauths中,这些参数是实际参数,简称实参。
p_authscode、p_authssalary就是形式参数,简称形参。
参数定义中,IN、OUT和IN OUT代表参数的三种不同模式:
IN:当调用存储过程时,该模式的形参接收对应实参的值,并且该是只读的,即不能被修改。默认为IN。
OUT:该形参被认为只能写,既只能为其赋值。在存储过程中不能读它的值。返回时,将该形参值传给相应的实参。
IN OUT:都允许。
Java代码
create or replace procedure updateauthssalary(
p_author_code in out auths.author_code%type,
p_salary in number,
p_name out auths.name%type) is
v_salary_temp number; --定义存储过程中的局部变量
begin
select salary into v_salary_temp from auths where author_code=p_author_code;
if v_salary_temp<300 then
update auths set salary=p_salary where author_code=p_author_code;
end if;
select name into p_name from auths where author code=p_author_code;
end updateauthssalary;
create or replace procedure updateauthssalary(
p_author_code in out auths.author_code%type,
p_salary in number,
p_name out auths.name%type) is
v_salary_temp number; --定义存储过程中的局部变量
begin
select salary into v_salary_temp from auths where author_code=p_author_code;
if v_salary_temp<300 then
update auths set salary=p_salary where author_code=p_author_code;
end if;
select name into p_name from auths where author code=p_author_code;
end updateauthssalary;(1)参数的数据类型
在定义一个存储过程参数时,不能指定CHAR类型和VARCHAR2类型形参的长度,也不能指定NUMBER形参的精度和标度。这些约束由实参来传递。
例如,下面的存储过程定义不合法,将产生一个编译错误:
Java代码
create or replace procedure proc_auths(
--参数定义了类型长度,将产生编译错误。
p_code in out varchar2(6),
p_salary out number(8,2)) as
begin
select salary into p_salary from auths where author_code=p_code;
end proc_auths;
create or replace procedure proc_auths(
--参数定义了类型长度,将产生编译错误。
p_code in out varchar2(6),
p_salary out number(8,2)) as
begin
select salary into p_salary from auths where author_code=p_code;
end proc_auths;修改上面存储过程的定义为:
Java代码
create or replace procedure proc_auths(
--参数定义了类型长度,将产生编译错误。
p_code in out varchar2,
p_salary out number) as
begin
select salary into p_salary from auths where author_code=p_code;
end proc_auths;
create or replace procedure proc_auths(
--参数定义了类型长度,将产生编译错误。
p_code in out varchar2,
p_salary out number) as
begin
select salary into p_salary from auths where author_code=p_code;
end proc_auths;
p_code的长度约束和p_salary的精度,标度约束由实参传递。
Java代码
delcare
v_code varchar2(6);
v_salary number(8,2);
begin
v_code:='A00001';
proc_auths(v_code,v_salary);
end;
delcare
v_code varchar2(6);
v_salary number(8,2);
begin
v_code:='A00001';
proc_auths(v_code,v_salary);
end;注意,如果使用%type为参数定义类型,那么该参数具有定义在形参上而不是通过实参传递的数据长度。
Java代码
create or replace procedure query_salary(
p_code in out auths.author_code%type,
p_salary out auths.salary%type) as
create or replace procedure query_salary(
p_code in out auths.author_code%type,
p_salary out auths.salary%type) as--那么由于author_code的长度为6,因此p_code的长度也为6。
(2)参数的传值方式
位置表示法、名称表示法
如有这样的存储过程
Java代码
create or replace procedure insert_auths(
p_code auths.author_code%type,
p_name auths.name%type,
p_sex auths.sex%type,
p_birthdate auths.birthdate%type) as
create or replace procedure insert_auths(
p_code auths.author_code%type,
p_name auths.name%type,
p_sex auths.sex%type,
p_birthdate auths.birthdate%type) as下面进行两种方法的调用:
Java代码
declare
v_code varchar2(6);
v_name varchar2(12);
v_sex number(1);
v_birthdate date;
begin
v_code:='A00021';
v_name:='张';
v_sex:=1;
v_birthdate:='5-seq-70';
--实参的位置顺序与形参的位置顺序相对应。---位置表示法
insert_auths(v_code,v_name,v_sex,v_birthdate);
--实参名与形参名对应,这样就可以重新排列参数的先后顺序。---命名表示法
end;
declare
v_code varchar2(6);
v_name varchar2(12);
v_sex number(1);
v_birthdate date;
begin
v_code:='A00021';
v_name:='张';
v_sex:=1;
v_birthdate:='5-seq-70';
--实参的位置顺序与形参的位置顺序相对应。---位置表示法
insert_auths(v_code,v_name,v_sex,v_birthdate);
--实参名与形参名对应,这样就可以重新排列参数的先后顺序。---命名表示法
end;注意,位置表示法和命名表示法在一些调用中也可以混合使用。但是,如果出现第一个用命名表示法的参数时,后面的参数也必须使用命名表示法传值。
(3)参数的缺省值
如可以这样:
p_entry_date_time auths.entry_date_time%type:sysdate,
p_sex auths.sex%type default 1
2、创建函数
函数与存储过程非常类似,都有三种模式的参数。它们都可以存储在数据库中(当然过程与函数也可以不在于数据库中),并且在块中调用。
与存储过程不同,存储过程只能作为一个PL/SQL语句调用,而函数作为表达式的一部分调用。并且它们的定义、可执行、异常处理部分是不同的。
例如,如作家表中男作家或女作家的工资在200元以上的人数大于百分之七十,则下面的函数返回TRUE,否则返回FALSE:
Java代码
create or replace function salarystat(
p_sex auths.sex%type)
return boolean is
v_currentsexauthors number;
v_maxauthors number;
v_returnvalue boolean;
v_percent constant number:=70;
begin
--获得满足条件的作家的最大数。
select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200;
select count(author_code) into v_currentsexauthors from auths where sex=p_sex;
if(v_maxauthors/v_currentsexauthors*100)>v_percent then
v_returnvalue:=true;
else
v_returnvalue:=false;
end if;
return v_returnvalue;
end salarystat;
create or replace function salarystat(
p_sex auths.sex%type)
return boolean is
v_currentsexauthors number;
v_maxauthors number;
v_returnvalue boolean;
v_percent constant number:=70;
begin
--获得满足条件的作家的最大数。
select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200;
select count(author_code) into v_currentsexauthors from auths where sex=p_sex;
if(v_maxauthors/v_currentsexauthors*100)>v_percent then
v_returnvalue:=true;
else
v_returnvalue:=false;
end if;
return v_returnvalue;
end salarystat;下面进行调用:
Java代码
declare
cursor c_auths is
select distinct sex from auths;
begin
for v_authsrecord in c_auths loop
if salarystat(v_authsrecord.sex) then
update auths set salary=salary-50 where sex=v_authsrecord.sex;
end if;
end loop;
end;
declare
cursor c_auths is
select distinct sex from auths;
begin
for v_authsrecord in c_auths loop
if salarystat(v_authsrecord.sex) then
update auths set salary=salary-50 where sex=v_authsrecord.sex;
end if;
end loop;
end;
return也可以用在存储过程中。在这种情况下,它没有参数。当执行了不带参数的return语句后,立刻将控制返回到调用环境,并将OUT和IN OUT模式的形参的当前值传给实参,然后继续执行调用存储过程后的语句。
在使用函数与存储过程时,一般情况下,如果只有一个返回值,则使用函数;如果有多个返回值则使用存储过程。尽管函数的参数可以是OUT模式,但是一般不这样使用。
3、删除过程与函数
drop procedure procedurename;
drop function functionname;
4、库存子程序和局部子程序
前面的子程序都是存储在数据库中的子程序,即库存子程序。这些子程序是由ORACLE命令创建的,并可在其它的PL/SQL块中调用。它们在创建时要进行编译,并将编译后的代码存储在数据库中。当子程序被调用时,编译后的代码从数据库中读出并执行。
一个子程序也可以在块的定义部分创建,这样的子程序被叫作局部子程序。
下面定义了一个局部函数formatname:
Java代码
declare
cursor c_allauthors is
select name,sex from auths;
v_formattedname varchar2(60);
function formatname(p_name in varchar2,p_sex in number)
return varchar2 is
v_sex varchar2(16);
begin
if p_sex=1 then
v_sex:='男';
else
v_sex:='女';
end if;
return p_name||'('||v_sex||')';
end formatname;
begin
for v_authsrecord in c_allauthors loop
v_formattedname:=
formatname(v_authsrecord.name,v_authsrecord.sex);
dbms_output.put_line(v_formattedname);
end loop;
end;
分享到:
相关推荐
存储过程、函数和触发器是PL/SQL中的核心概念,它们在数据库管理中扮演着至关重要的角色。 一、存储过程 存储过程是一组预编译的SQL语句和PL/SQL代码,可以视为数据库中的可执行对象。它们允许开发者封装一系列的...
PL/SQL 的函数与存储过程练习 PL/SQL 的函数与存储过程练习
### PL/SQL存储过程编程详解 #### 一、Oracle应用编辑方法概览 在Oracle数据库的应用开发中,存在多种编辑方法和技术,它们各有特点和适用场景。以下是对这些方法的概述: 1. **Pro*C/C++**: 这是一种C语言与...
PL/SQL是Oracle特有的SQL扩展,它结合了SQL的查询和数据操作能力与过程性编程语言的功能。 1. **PL/SQL基本概念** - **块结构**:PL/SQL代码由声明、执行和异常处理三部分组成,包括BEGIN...END块、DECLARE......
PL/SQL是Oracle数据库系统中的一个关键组成部分,它是一种过程化语言,专为数据库操作设计。这个"PL/SQL最新中文手册"显然是一份详细解释PL/SQL 7.0版本的指南,对于学习和精通Oracle数据库编程至关重要。以下是手册...
6. **子程序**:PL/SQL支持函数和过程的定义,这些子程序可以在多个地方重复使用,降低了代码的冗余。 7. **包(Packages)**:包是将相关的类型、变量、常量、过程和函数组合在一起的命名实体,便于管理和重用。 ...
在PL/SQL中,我们可以编写存储过程、函数、触发器、游标和异常处理等。这些组件对于数据库的业务逻辑处理至关重要,可以提高数据操作的安全性,减少网络通信,同时提高性能。 在"plsql_day02"这个压缩包文件中,很...
例如,在`jbpm.sql`文件中,可能包含了与业务流程管理(BPM)相关的PL/SQL存储过程或函数,用于定义和执行工作流任务。 此外,PL/SQL还支持块的概念,如匿名块,这使得在不创建独立的存储过程或函数的情况下,也能...
Oracle PL/SQL是一种强大的编程语言,它结合了SQL的数据处理能力与PL的程序设计特性,是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战(待续)"的主题中,我们将深入...
SQL and Pl/SQL培训文档 第一天 第一章 PL/SQL 简介 安装scott/tigger用户模式 Example1 标准的PL/SQL块结构 PL/SQL块类型 Example2:子程序,函数,程序包 PL/SQL代码的执行 第二章 PL/SQL数据类型 预定义数据类型 ...
它结合了SQL的查询能力与过程化编程元素,使得开发者能够创建复杂的业务逻辑和数据库应用程序。PL/SQL Developer 11.0作为一套强大的集成开发环境(IDE),提供了许多有助于提升PL/SQL开发效率的工具和功能。 1. **...
**标题与描述**:本文档围绕“Oracle 9i PL/SQL程序设计笔记”这一核心主题,深入探讨了PL/SQL语言的基础知识及其在Oracle 9i数据库环境中的应用。 **关键词**:Oracle 9i、PL/SQL #### PL/SQL程序结构详解 **2.1...
3. **对象浏览器**:可以浏览数据库中的所有对象,如表、视图、存储过程、函数、触发器等,方便管理和操作。 4. **查询工具**:支持SQL查询的执行,可以查看查询结果,进行数据筛选、排序和分组。 5. **调试器**:...
2. **数据库对象管理**:该工具允许用户直接在界面中创建、修改和删除数据库中的表、视图、索引、存储过程、函数、触发器等。这些操作都配有直观的向导,使得数据库管理工作变得简单易行。 3. **调试功能**:PL/SQL...
- **新特性概述**:介绍Oracle 11g中引入的与PL/SQL相关的新增功能,例如新的数据类型、函数、性能优化技术等。 - **性能提升技术**:提供了一些具体的例子和最佳实践,展示了如何利用Oracle 11g的新特性来进一步...
客户端工具如SQL*Plus,以及其他高级开发环境和第三方工具(如PowerBuilder),均可以调用服务器端的PL/SQL存储过程和函数。 #### 六、总结 PL/SQL为Oracle数据库的开发人员提供了一个强大而灵活的工具集,不仅...
PL/SQL是Oracle数据库系统中的一个关键组成部分,它是一种结合了SQL语言与过程编程的语言,专为数据库管理和应用程序开发设计。PL/SQL全称为“Procedural Language/Structured Query Language”,它提供了丰富的控制...
PL/SQL(Procedural Language for SQL)是一种过程化语言,它结合了SQL的数据操纵功能和过程化语言的控制结构,使用户能够灵活地控制数据操作流程。作为Oracle数据库的核心组成部分之一,PL/SQL在数据库应用程序开发...
PL/SQL是Oracle数据库特有的编程语言,它结合了SQL的查询能力与过程式编程语言的功能,使得数据库管理、数据处理和业务逻辑实现更为高效。本书针对那些希望提升PL/SQL编程技能,设计和开发复杂数据库解决方案的...
Oracle PL/SQL是一种...书中可能包含各种类型的PL/SQL程序设计技巧,从简单的存储过程和函数到复杂的事务处理和并发控制策略。通过实例学习,你将能够更好地理解和运用这些概念,提升你在Oracle数据库开发中的技能。