sql 存储过程Procedure详解(1/2)

存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库教程中，用户通过指定存储过程的名称并给出参数来执行。

    存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

    由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

系统存储过程

    系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

    常用系统存储过程有：

exec sp_databases; --查看数据库exec sp_tables;        --查看表exec sp_columns student;--查看列exec sp_helpIndex student;--查看索引exec sp_helpConstraint student;--约束exec sp_stored_procedures;exec sp_helptext '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_rename 'stu', 'stud';select * from stud;--列重命名exec sp_rename 'stud.name', 'sName', 'column';exec sp_help 'stud';--重命名索引exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';exec sp_help 'student';--查询所有存储过程select * from sys.objects where type = 'P';select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
 

Ø 用户自定义存储过程

   1、 创建语法

create proc | procedure pro_name    [{@参数数据类型} [=默认值] [output],     {@参数数据类型} [=默认值] [output],     ....    ]as    SQL_statements

   2、 创建不带参数存储过程

--创建存储过程if (exists (select * from sys.objects where name = 'proc_get_student'))    drop proc proc_get_studentgocreate proc proc_get_studentas    select * from student;--调用、执行存储过程exec proc_get_student;

   3、 修改存储过程

--修改存储过程alter proc proc_get_studentasselect * from student;

   4、 带参存储过程

--

带参存储过程if (object_id('proc_find_stu', 'P') is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;

 

   5、 带通配符参数存储过程

--带通配符参数存储过程if (object_id('proc_findStudentByName', 'P') is not null)    drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as    select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';

   1 2