当前位置：文档库 › 存储过程与视图的学习方法

存储过程与视图的学习方法

存储过程

1、存储过程是事先编好的、存储在数据库中的程序，这些程序用来完成对数据库的指定操作。

2、系统存储过程: SQL Server本身提供了一些存储过程，用于管理有关数据库和用户的信息。用户存储过程: 用户也可以编写自己的存储过程，并把它存放在数据库中，供客户端调用。

3、这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。

4、系统存储过程

概念：

它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其它的系统管理任务。

系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。

部分系统存储过程：

sp_addtype：用于定义一个用户定义数据类型。sp_configure：用于管理服务器配置选项设置。xp_sendmail：用于发送电子邮件或寻呼信息。sp_stored_procedures：用于返回当前数据库中的存储过程的清单。sp_help：用于显示参数清单和其数据类型。sp_helptext：用于显示存储过程的定义文本。sp_rename：用于修改当前数据库中用户对象的名称。Sp_who:用于显示使用数据库的当前用户sp_help：用于显示参数清单和其数据类型。sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象sp_helptext：用于显示存储过程的定义文本。

用户自定义的存储过程：

定义格式：

CREATE PROC[edure] procedure_name [ ; number ] [ @parameter data_type [ = default ][output], … uc1]AS sql_statement

procedure_name：给出存储过程名；number：对同名的存储过程指定一个序号(允许同名)；@parameter：给出参数名；data_type：指出参数的数据类型；Output:返回值参数default：给出参数的默认值；sql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。

注意事项:

存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，

即在存储过程中一般不能含有以下语句：

CREATE TABLE ；CREATE VIEW ；CREATE DEFAULT ；

CREATE RULE ；CREATE TRIGGER ；CREATE PROCEDURE

存储过程的返回值和状态信息:

无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。

如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。

我们在创建存储过程时，也可以定义自己的状态码和错误信息。

建立一个简单的触发器

create trigger test_trigger

on student for insert

as prinr '插入了一个元组'

delete from sc

where sno=(select sno from deleted)

create trigger stu_tri

on student instead of insert

if(select age from inserted)>40

print '不能插入大于岁的学生记录'

else insert into student select * from inserted

sql语句执行的时候要先编译，然后执行。存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程……

master数据库中两个非常有用的存储过程

1/第一个过程sp_MSForEachDB对于感兴趣的服务器上的每个数据库执行三条命令。

◆@command1：第一个执行的命令

◆@replacechar：用另一个占位赋替换“?”

◆@command2：第二个执行的命令

◆@command3：第三个执行的命令

◆@precommand：进入循环前执行的命令

◆@postcommand：循环结束后执行的命令

每个命令集(即使该集合只含有一条命令)作为一个批处理对每个数据库执行，所以当我们要将捕获的结果输出到文本而不是标准结果集表时，这将非常有用。

第二个过程sp_MSForEachTable接受7个参数：

◆@command1：第一个执行的命令

◆@replacechar：用另一个占位符替换“?”

◆@command2：第二个执行的命令

◆@command3：第三个执行的命令

◆@whereand：Where条件语句(或Order By 语句)

◆@precommand：进入循环前执行的命令

◆@postcommand：循环结束后执行的命令

通过对要传递的参数命名，可以跳过传递空值的要求。当要执行的命令中含有一个问号时，参数

@replacechar十分有用。@whereand参数的实现可以根据过滤器缩小输出的范围。

你还可以加入一个ORDER BY语句。下面的例子返回AdventureWorks数据库中每个数据表的行数，并按照数据表明对它们排序：

本文从多个角度阐述了SQL Server与存储过程的比较。

①为什么要使用存储过程？

因为它比SQL语句执行快。

②存储过程是什么?

把一堆SQL语句罗在一起,还可以根据条件执行不通SQL语句。

③来一个最简单的存储过程：

CREATE PROCEDURE dbo.testProcedure_AX

select userID from

USERS order by userid desc

注:dbo.testProcedure_AX是你创建的存储过程名,可以改为:AXzhz等,别跟关键字冲突就行了.AS下面就是一条SQL语句,不会写SQL语句的请回避。

④怎么在https://www.wendangku.net/doc/b3509948.html,中调用这个存储过程：

public static string GetCustomerCName

(ref ArrayList arrayCName,ref ArrayList arrayID)

{

SqlConnection con=ADConnection.createConnection();

SqlCommand cmd=new SqlCommand("testProcedure_AX",con);

https://www.wendangku.net/doc/b3509948.html,mandType=CommandType.StoredProcedure;

//调用存储过程,相当于SqlCommand cmd=new SqlCommand(“select

userID from USERS order by userid desc”,con);

con.Open();

try

{

SqlDataReader dr=cmd.ExecuteReader();

while(dr.Read())

{

if(dr[0].ToString()=="")

{

arrayCName.Add(dr[1].ToString());

}

con.Close();

return "OK!";

}

catch(Exception ex)

{

con.Close();

return ex.ToString();

}

注:其实就是把以前：

SqlCommand cmd=new SqlCommand("select userID from USERS order by userid desc",con)；中的SQL语句替换为存储过程名,再把cmd的类型标注为CommandType.StoredProcedure(存储过程)。

⑤写个带参数的存储过程：

CREATE PROCEDURE dbo.AXzhz

@startDate varchar(16),

@endDate varchar(16)

select id from table_AX where commentDateTime>

@startDate and commentDateTime<@endDate order

by contentownerid DESC

注:@startDate varchar(16)是声明@startDate 这个变量,多个变量名间用【,】隔开.后面的SQL 就可以使用这个变量了。

我怎么在https://www.wendangku.net/doc/b3509948.html,中调用这个带参数的存储过程：

public static string GetCustomerCNameCount

(string startDate,string endDate,ref DataSet ds)

{

SqlConnection con=ADConnection.createConnection();

SqlDataAdapter da=new SqlDataAdapter("AXzhz",con);

para0=new SqlParameter("@startDate",startDate);

para1=new SqlParameter("@endDate",endDate);

da.SelectCommand.Parameters.Add(para0);

da.SelectCommand.Parameters.Add(para1);

https://www.wendangku.net/doc/b3509948.html,mandType=CommandType.StoredProcedure;

try

{

con.Open();

da.Fill(ds);

con.Close();

return "OK";

}

catch(Exception ex)

{

return ex.ToString();

}

注:把命令的参数添加进去，就可以了。

⑦重新验证SQL命令执行是否成功。

CREATE PROCEDURE dbo.AXzhz

@password nvarchar(20),

@userName nvarchar(20)

declare @err0 int

update WL_user set password=@password where UserName=@userName

set @err0=@@error

select @err0 as err0

注:先声明一个整型变量@err0,再给其赋值为@@error(这个是系统自动给出的语句是否执行成功,0为成功,其它为失败),最后通过select把它选择出来。

⑧那怎么从后台获得这个执行成功与否的值：

下面这段代码可以告诉你答案:

public static string GetCustomerCName()

{

SqlConnection con=ADConnection.createConnection();

SqlCommand cmd=new SqlCommand("AXzhz",con);

https://www.wendangku.net/doc/b3509948.html,mandType=CommandType.StoredProcedure;

para0=new SqlParameter("@startDate","2006-9-10");

para1=new SqlParameter("@endDate","2006-9-20");

da.SelectCommand.Parameters.Add(para0);

da.SelectCommand.Parameters.Add(para1);

con.Open();

try

{

Int32 re=(int32)cmd.ExecuteScalar();

con.Close();

if (re==0)

return "OK!";

else

return "false";

}

catch(Exception ex)

{

con.Close();

return ex.ToString();

}

注:就是通过SqlCommand的ExecuteScalar()方法取回这个值。

⑨我要根据传入的参数判断执行哪条SQL语句：

ALTER PROCEDURE dbo.selectCustomerCNameCount

@customerID int

if @customerID=-1

begin

select contentownerid ,userCName,count(*)

as countAll from view_usercomment group by

contentownerid,userCName order by contentownerid DESC

end

else

begin

select contentownerid ,userCName,count(*)

as countAll from view_usercomment where

contentownerid=@customerID group by contentownerid

,userCName order by contentownerid DESC

end

/****************

题目：1.现在需要查询所有物料的入出库明细，出库明细时间是范围是：2010-03-01 到今天入库、出库明细需要显示的字段: 单据编号、单据日期、物料名称、物料类型、包数、数量、单据属性、相关仓库、录入人姓名。可分为两次查询，入库明细和出库明细，如有可能，将其合并成一张表显示，并且按时间、物料代码进行排序

select a.djbh as 单据编号,b.djrq as 单据日期,c.wlmc as 物料名称,c.wllx as 物料类型,

(a.sl/c.zhxs) as 包数,a.sl as 数量,b.djsx as 单据属性,e.mc as 相关仓库,d.xm as 入录人姓

名from sl_rkddts a inner join sl_rkd b on a.djbh=b.djbh inner join jxc_wlxx c on c.wldm=a.wldm inner join jb_yh d on d.dm=b.lry inner join jb_ck e on e.dm=b.ckdm

where b.djrq between '2010-3-01' and getdate()--算出从２０１０－３－０１到今天的日期order by b.djrq asc,a.wldm desc

题目：2.查询出现有"原材料"的当前库存量，单一物料库存量为：入库之和减去出库之和等于当前库存。

如果有月结则为：月结+sum(入库量)-sum(出库量)

库存量时间范围：2010年第3期到2010年第5期

物料信息表和原料入库主明细表

select a.djbh,a.sl from sl_rkddts a inner join sl_ckddts b

单据编号，序号，物料代码，饲料单价，数量，金额，生产日期

服务器：192.168.2.32 登录名：sa密码：aylsystem

使用数据库：TianJu

order by：意思是取结果集并默认按升序排序

<> 表示不等于

报表中的判断=iif(Fields!wcl.Value <> "",Fields!wcl.Value + "%" ,Fields!wcl.Value)

执行存储过程：DataSet ds = AJXSqlDbA.Utility.Query("EXEC proc_Rwwcqkfx @DepartmentID='" + DDL_bm.SelectedValue + "',@time1='" + txttime1.Text + "',@time2='" + txttime2.Text + "'", AJXSqlDbA.Utility.GetConnStr(Session["Sedlztdm"].ToString()));

创建存储过程：

create procdure 存储过程名称

定义参数(如@bmrwzs)

begin

SQL语句

end

列子：

create procedure proc_a

@bmrwzs int

begin

set

@bmrwzs=(select COUNT(*) from bmrwap a where a.shbz='已审核')

end

创建视图：

create view 视图名称

SQL语句

列子：create view uv_a

select a.bmjb,b.ygxxda_ID from bmrwap a left join rwryfp b on b.bmrwap_ID=b.bmrwap_ID

调用视图：(视图其实就是一张表(聚合几张表起来的表))

SqlDataReader Ldr_xtlx = AJXSqlDbA.Utility.ExecuteReader("select distinct sFunTitle from dbo.uv_UserDisplayFunction where iRUsersID ='" + Session["dm"].ToString() + "' and MenuTypeID like '" + Ls_xtdm + "%' ORDER BY sFunTitle", AJXSqlDbA.Utility.GetConnStr(Session["Sedlztdm"].ToString()));

SqlDataReader rdr;

while (Ldr_xtlx.Read())

{

li_hs++;

li_ls = 0;

Ls_cdmc = Ldr_xtlx["sFunTitle"].ToString();//菜单功能分类名称

rdr=AJXSqlDbA.Utility.ExecuteReader("selectdistinct

sUrlName,sUrl,MenuTypeID,sFunPic,iFuctSort,sFunTitle,iFunctionURLID from dbo.uv_UserDisplayFunction where iRUsersID = '" + Session["dm"].ToString() + "' and MenuTypeID like '" + Ls_xtdm + "%' and sFunTitle ='" + Ls_cdmc + "' order by iFuctSort", AJXSqlDbA.Utility.GetConnStr(Session["Sedlztdm"].ToString()));

switch (Ls_cdmc)

{

case "A基础资料":

Ls_Htmlstr += "基础资料:";

break;

case "B单据类":

Ls_Htmlstr += "单据管理:";

break;

case "C结算类":

Ls_Htmlstr += "数据月结:";

break;

case "D报表类":

Ls_Htmlstr += "报表中心:";

break;

default:

//Ls_Htmlstr += "基础资料:";

break;

}

--打开游标，在游标关闭或者删除前都有效

open my_cursor

--关闭游标

close my_cursor

--声明局部变量

declare @id int,@name varchar(20),@address varchar(20)

--定位到指定位置的记录

fetch absolute 56488 from my_cursor into @id,@name,@address