Oracle存储过程

原文出处：http://www.cnblogs.com/ToNi/p/4234370.html

什么是存储过程？
      存储过程（Procedure）是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。因此，我们可以简单的理解为：使用存储过程就像在数据库中运行方法。　

存储过程的优点：   

• 存储过程的能力大大增强了SQL语言的功能和灵活性。
• 可保证数据的安全性和完整性。
• 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
• 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
• 在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。
• 可以降低网络的通信量– 只需要传输存储过程的名称。
• 使体现企业规则的运算程序放入数据库服务器中，以便 集中控制。
• 执行速度更快 – 在数据库中保存的存储过程SQL语句都是编译过的。
• 允许模块化程序设计 – 类似方法的复用。
• 提高系统安全性 – 防止SQL注入。

      存储过程可以分为系统存储过程、扩展存储过程和用户自定义的存储过程。

系统存储过程

     我们先来看一下系统存储过程，系统存储过程由系统定义，主要存放在MASTER数据库中，名称以"SP"开头或以"XP"开头。尽管这些系统存储过程在MASTER数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

常用系统存储过程有：

• exec sp_databases; --查看数据库
• exec sp_tables;        --查看表
• exec sp_columns student;--查看列
• exec sp_helpIndex student;--查看索引
• exec sp_helpConstraint student;--约束
• exec sp_helptext 'sp_stored_procedures';--查看存储过程创建定义的语句
• exec sp_stored_procedures;
• exec sp_rename student, stuInfo;--更改表名
• exec sp_renamedb myTempDB, myDB;--更改数据库名称
• exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
• exec sp_helpdb;--数据库帮助，查询数据库信息
• exec sp_helpdb master;
• exec sp_attach_db --附加数据库
• exec sp_detach_db --分离数据库

存储过程语法：

　　在创建一个存储过程前，先来说一下存储过程的命名，看到好几篇讲存储过程的文章都喜欢在创建存储过程的时候加一个前缀，养成在存储过程名前加前缀的习惯很重要，虽然这只是一件很小的事情，但是往往小细节决定大成败。看到有的人喜欢这样加前缀，例如proc_名字。也看到这加样前缀usp_名字。前一种proc是procedure的简写，后一种sup意思是user procedure。我比较喜欢第一种，那么下面所有的存储过程名都以第一种来写。至于名字的写法采用骆驼命名法。

创建存储过程的语法如下：

CREATE PROC[EDURE] 存储过程名 
@参数1 [数据类型]=[默认值] [OUTPUT] 
@参数2 [数据类型]=[默认值] [OUTPUT]
AS 
SQL语句
EXEC 过程名[参数]

使用存储过程实例：

1.不带参数

create procedure proc_select_officeinfo --(存储过程名)
as select Id,Name from Office_Info --(sql语句)

exec proc_select_officeinfo --(调用存储过程)

2.带输入参数

create procedure procedure_proc_GetoffinfoById   --(存储过程名)
@Id int   --(参数名 参数类型)
as select Name from dbo.Office_Info where Id=@Id   --(sql语句)

exec procedure_proc_GetoffinfoById 2    --(存储过程名称之后,空格加上参数,多个参数中间以逗号分隔)

注:参数赋值是,第一个参数可以不写参数名称,后面传入参数,需要明确传入的是哪个参数名称

3.带输入输出参数

create procedure proc_office_info   --（存储过程名）
@Id int,@Name varchar(20) output    --(参数名 参数类型)传出参数要加上output
as 
begin
select @Name=Name from dbo.Office_Info where Id=@Id --(sql语句)
end

declare @houseName varchar(20) --声明一个变量,获取存储过程传出来的值
exec proc_office_info    --(存储过程名)
4,@houseName output      --(传说参数要加output 这边如果用@变量 = OUTPUT会报错，所以换一种写法)

select @houseName    --(显示值)

4.带返回值

create procedure proc_office_info   --（存储过程名）
@Id int   --(参数名 参数类型)
as 
begin
if(select Name from dbo.Office_Info where Id=@Id)=null --(sql语句)
　　begin
　　　　return -1
　　end
else
　　begin
　　　　return 1
　　end
end

declare @house varchar(20) --声明一个变量,获取存储过程传出来的值
exec @house=proc_office_info 2 --(调用存储过程,用变量接收返回值)
--注：带返回值的存储过程只能为int类型的返回值
print @house