oracle存储过程详细教程-阿里云开发者社区

开发者社区> dasein58> 正文

oracle存储过程详细教程

简介: 存储过程的定义: 1、存储过程是以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的,因为代码不保存在本地,用户可以在任何客户机上登录到数据库,并调用或修改代码。 2、存储过程可由数据库提供安全保证,要想使用存储过程,需要有存储过程的所有者的授权,只有被授权的用户或创建者本身才能调用执行存储过程。 3、存储过程的信息是写入数据字典的,所以存储过程可以看作是一个公用模块,用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能,可以设计成为存储过程。 4、像其他高级语言的过程和函数一样,可以传递参数给存储过程,
+关注继续查看

存储过程的定义:

1、存储过程是以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的,因为代码不保存在本地,用户可以在任何客户机上登录到数据库,并调用或修改代码。

2、存储过程可由数据库提供安全保证,要想使用存储过程,需要有存储过程的所有者的授权,只有被授权的用户或创建者本身才能调用执行存储过程。

3、存储过程的信息是写入数据字典的,所以存储过程可以看作是一个公用模块,用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能,可以设计成为存储过程。

4、像其他高级语言的过程和函数一样,可以传递参数给存储过程,参数的传递也有多种方式。存储过程可以有返回值,也可以没有返回值,存储过程的返回值必须通过参数带回;函数有一定的数据类型,像其他的标准函数一样,我们可以通过对函数名的调用返回函数值。

5、存储过程需要进行编译,以排除语法错误,只有编译通过才能调用。

二、创建存储过程

create [or replace] procedure 存储过程名

  [(参数1 类型,参数2 out 类型……)]   

  as

   变量名  类型;

  begin

    程序代码体

  end;

运用此语法我们创建了一个名为“procedure_name”的存储过程。首行的 replace 表示替换,对于Oracle的存储过程而言,我们只能创建 (create)、删除 (drop) 或替换 (replace) 它,没有类似于SQL Server的修改 (Alter) 操作。

begin 与 end 表示PL-SQL语句块的开始和结束,所有需要执行的语句都写在此处。end结束后,还需跟上 “/” 表示执行上述语句块,创建这个存储过程。

示例一:无参无返

create or replace procedure p1
--or replace代表创建该存储过程时,若存储名存在,则替换原存储过程,重新创建
--无参数列表时,不需要写()
as
begin
dbms_output.put_line('hello world');
end;

--执行存储过程方式1
set serveroutput on;
begin
p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();

示例二:有参有返

create or replace procedure p2

(name in varchar2,age int,msg out varchar2)

--参数列表中,声明变量类型时切记不能写大小,只写类型名即可,例如参数列表中的name变量的声明

--参数列表中,输入参数用in表示,输出参数用out表示,不写时默认为输入参数。

------------输入参数不能携带值出去,输出参数不能携带值进来,当既想携带值进来,又想携带值出去,可以用in out

as

begin

msg:='姓名'||name||',年龄'||age;

--赋值时除了可以使用:=,还可以用into来实现

--上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;

end;

--执行存储过程

set serveroutput on;

declare

msg varchar2(100);

begin

p2('张三',23,msg);

dbms_output.put_line(msg);

end;

示例三:参数列表中有in out参数

create or replace procedure p3

(msg in out varchar2)

--当既想携带值进来,又想携带值出去,可以用in out

as

begin

dbms_output.put_line(msg); --输出的为携带进来的值

msg:='我是从存储过程中携带出来的值';

end;

--执行存储过程

set serveroutput on;

declare

msg varchar2(100):='我是从携带进去的值';

begin

p3(msg);

dbms_output.put_line(msg);

end;

三、变量与参数

在设计存储过程的时候,我们必然会用到变量与参数,它们可以扩展代码的灵活性,让我们做到更多事情。在Oracle中,参数与变量有着截然不同的语法。

开讲之前有个小细节我想和大家提一下,我相信诸位在查找相关资料的时候一定有看到“as”和“is”这两种不同的写法,严格来说在存储过程中二者没有什么显著的差别,它们是同义词,但使用as的情况居多。值得注意的是,在创建视图的时候我们只能用as,而在声明游标的时候只能用is。

1、变量

首先让我们看看声明变量的语法[5]:

create [ or replace ] procedure procedure_name

as

[ var_1 var_type (var_size); ]

begin

-- PL-SQL blocks

end;

在这里,var_1表示变量名,var_type表示变量的类型,var_size表示取值范围(变量大小),当我们要声明一个变量的时候,这三个元素缺一不可。Oracle的变量命名遵循系统命名规则,在此我们不做赘述,但变量类型则有多种不同的分类:标量类型、复合变量类型、参照类型、大型数据对象。

1) 标量类型

标量类型既包括了系统中的标准数据类型,诸如varchar、number等;亦包括了一些比较少用的类型,比如BINARY_INTEGER、boolean等。这些类型使用广泛、声明简单,是变量类型中的基础。下面这个例子会创建一个名为“proc_findGirl”的存储过程,它会从“Employee”表中找到一个ID为6的雇员:

create or replace procedure proc_findGirl

as

girl_id number(4);

girl_name varchar(20);

girl_sex varchar(10);

girl_salary number;

begin

select emp_id, emp_name, emp_sex, emp_salary

into girl_id, girl_name, girl_sex, girl_salary

from employee

where emp_id = '6';

dbms_output.put_line('name: ' || girl_name || ' id: '

|| girl_id || ' sex: ' || girl_sex);

end proc_findGirl;

这个例子仅仅只是用来演示变量效果的,实际情况中我们肯定不会干这种在存储过程中只塞一个select语句的蠢事。上图即是执行效果,在存储过程中调用select语句必须使用变量接收查询结果,否则QQ靓号卖号平台会出现异常。

还有一种变量类型叫做 “%%TYPE[6]” ,你可以把它看做是一种动态数据类型,它由一个已经定义了的变量调用,并返回该变量的类型。比如说:

v_msg varchar(20);

v_msg_back v_msg%%TYPE;

-- 在这里,v_msg 和 v_msg_back 的类型都是 varchar(20)

这很OOP,尤其在我们使用参数的时候,我们很难确定输入的参数类型;或者是通过表格给变量赋值的时候,如果字段类型变了,我们还得跟着修改所有的过程。用一个%%TYPE就可以解决这些问题,提高了代码的可复用率和稳定性。在接下来的例子中,我们还会看到更多使用%%TYPE情况出现。

与之相似的还有%%ROWTYPE,顾名思义,它能保存一个表格中所有列的类型,你可以直接将它看做是一条行记录:

create or replace procedure proc_findGirl

as

girl employee%%ROWTYPE;

begin

select emp_id, emp_name, emp_sex, emp_salary

into girl

from employee

where emp_id = '6';

dbms_output.put_line('name: ' || girl.emp_name || ' id: '

|| girl.emp_id || ' sex: ' || girl.emp_sex);

end proc_findGirl;

-- 效果与前者一致

2) 复合变量类型

复合变量类型要比标量类型更加复杂,在这里我只做一些简单的解释。它包含以下几种类型:

a) 复合记录类型

就我个人看法而言我觉得它无论是看起来还是用起来都很像java里的结构体。我们会声明一种record类型的变量,该变量内含有多个标量类型的变量,随后声明该record类型的“对象”[7]:

type record_type_name is record (

var_name var_type(var_size)[, var_name var_type(var_size)]

);

var_record_type record_type_name;

该语法声明了一个叫做 “record_type_name” 的记录类型,里面含有复数个变量(单个变量没有声明成记录的必要)。随后,我们声明了一个名为 “var_record_type” 的 “record_type_name” 类型的变量。下面这个例子就是一种应用,如我们前面所说,使用%%TYPE可以给我们很大的帮助:

create or replace procedure proc_findGirl

as

type emp_record_type is record (

r_name employee.emp_name%%TYPE ,

r_salary employee.emp_salary%%TYPE

);

employee_record emp_record_type;

begin

select emp_name, emp_salary

into employee_record

from employee

where emp_id = '7';

dbms_output.put_line('name: ' || employee_record.r_name || ' salary: '

|| employee_record.r_salary);

b) 复合表类型(关联数组)

索引表(关联数组)是一种更为复杂的记录类型,尽管在声明的时候我们会用到 “is table of” ,但本质上来讲它更接近数组,索引表通过指定类型的索引确定其元素所在位置。下面是声明索引表的语法:

type table_type_name is table of type_name

index by index_type;

var_table table_type_name;

在这里,table_type_name 即是我们所声明的索引表的名字;type_name 是索引号的类型,它可以是标量类型,也可以是我们自己声明的记录类型,声明索引号的时,除非使用的是有固定大小或有默认大小的类型(如number、BINARY_INTEGER),否则我们必须声明其大小(如varchar2(20))。下面是一个示例,我们声明了一个叫做 v_table_emp 的索引表,其索引号类型为BINARY_INTEGER,我们将查到的一条记录保存到了表中下标(索引号)为0的位置上:

create or replace procedure proc_findGirl

as

type emp_record_type is record (

r_name employee.emp_name%%TYPE ,

r_salary employee.emp_salary%%TYPE

);

type table_employee_record is table of emp_record_type

index by binary_integer;

v_table_emp table_employee_record;

begin

select emp_name, emp_salary

into v_table_emp(0)

from employee

where emp_id = '1';

dbms_output.put_line('name: ' || v_table_emp(0).r_name || ' salary: '

|| v_table_emp(0).r_salary);

end proc_findGirl;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
WorkerMan 入门学习之(二)基础教程-Connection类的使用
一、TcpConnection类 的使用  1、简单的TCP测试 Server.php
1657 0
OTL调用Oracle存储过程
OTL很早前用过,今天写东西要调存储过程,程序写完了,调试死活通不过,折腾了一早晨。 最后才发现错误,这里总结一下: 1、代码写的不规范。 有个参数后边少写了个“,”以至于总是抱错。而单独写的测试例子就没问题,后来一步一步跟踪了后才发现。
1368 0
Oracle 教程
视频教程 Oracle DBA数据库高级工程师职业学习指南与职业规划视频课程
730 0
elasticsearch 索引存储深入详解(Elasticsearch教程03)|MVP讲堂
横扫你学习 Elasticsearch 的诸多疑惑,让你少走半年弯路!
1795 0
+关注
765
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载