第八章数据库编程存储过程的创建

第八章数据库编程1应用程序和数据库之间的主要编程接口：SQL语言1.在客户端存储代码，并创建编写访问数据库服务器发送SQL语句（比如在C#、Java等客户端编程语言中嵌入访问数据库的SQL语句）2.将sql语句存储在数据库服务器端，然后后应用程序调用执行这些SQL语句。2第8章存储过程8.1存储过程概述8.2创建管理无参存储过程

8.3创建管理带参的存储过程

38.1存储过程概述1.存储过程概念存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象。2.存储过程类型

系统存储过程扩展存储过程用户自定义存储过程系统存储过程存储以SP_为前缀,是由SQLServer2005自己创建、管理和使用的一种特殊的存储过程，不能对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出函数。48.2无参存储过程的创建使用SQL语句创建存储过程1）语法格式如下：CREATEPROC[EDURE

]procedure_nameASsql_statement[...n]

2）语法注释：

procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。58.2无参存储过程的创建例1：返回200030000041号学生的成绩，不使用参数。代码如下：CREATEPROCEDUREstudent_infoASSELECT*FROM学生信息WHERE学号='200030000041'6创建存储过程的注意事项只能在当前数据库中创建存储过程。数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。存储过程是数据库对象，其名称必须遵守标识符命名规则。不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。78.2无参存储过程的执行对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：

EXEC[UTE]procedure_name对上例的存储过程pr_searchorddate的执行语句如下：EXECstudent_info88.3创建带参存储过程的语法1）语法格式如下：CREATEPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement98.3创建带参存储过程的语法2）语法注释：parameter：存储过程中的输入和输出参数。

data_type：参数的数据类型。DEFAULT：表示参数的默认值。如果定义了默认值，则在调用存储过程时，可以省略该参数的值。

OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。10带参数的存储过程例子：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。createprocedurep_StudentGrade1@deptchar(20)asselectsname,sdept,cname,gradefromstudentsinnerjoinscons.sno=o=owheresdept=@dept11带多个输入参数并有默认值的存储过程例子1：查询某个学生某门课程的考试成绩，课程的默认值为“VB”Createprocp_StudentGrade2

@snamechar(10),@cnamechar(20)=‘VB’Asselectsname,cname,gradefromstudentsinnerjoinscons.sno=o=o

wheresname=@snameandcname=@cname12例2：CREATEPROCdeletestu@学号char(4)ASDELETE学生信息WHERE学号=@学号存储过程的执行如下：Execdeletestu‘200030000041’138.3.2创建/执行带输入参数的存储过程执行带输入参数的存储过程按位置传递参数值在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。例：execp_StudentGrade2‘吴宾’，‘高等数学’通过参数名传递参数值在执行存储过程的语句中，使用【参数名=参数值】的形式给出参数值。其优点是参数可以以任意顺序给出。例：例：execp_StudentGrade2@cname=‘高等数学’，@sname=‘吴宾’

148.3.2创建/执行带输入参数的存储过程在输入参数中使用默认值在执行存储过程时，如果没有指定参数，则系统运行就会出错；此时如果希望在执行时不给出参数也能正确运行，则在创建存储过程时给输入参数指定默认值。例：execp_StudentGrade2‘吴宾’相当于执行：execp_StudentGrade2‘吴宾’，‘VB’

158.那3.被3存储咽过程群的返蹦回值存储其过程辰在执杯行后慎都会张返回牵一个暮整型仁值。籍如果鸡执行至成功边，则元返回0；否抵则返婶回-1到-9翠9之间列的随程机数奴，也袋可以熊使用RE强TU芒RN语句迹来指趴定一统个存滥储过吐程的琴返回解值。168.3冶.3存储捧过程违的返泄回值例：迁在no唉rt威hw猛in精d数据欧库创株建一还个存粮储过弄程，盯返回抄产品购表中共的所质有产咬品的某库存弊量。cr盐ea句te堡p浪ro邀c星pr像_l蚂ie央rasbe浑gi场nde坐cl政ar得e成@f天an尿hu文iz疼hi极i暂ntse羊le趴ct纷@水fa招nh弯ui鲁zh锐i=逝su徒m(墙un使it笑si糊ns最to京ck波)fr话ompr味od伏uc蛾tsre俘tu坡rn及@筛fa抢nh麻ui侍zh昌ien拆d178.存3.街3存储帆过程毒的返房诚回值接受民这个情返回欺值必给须要门用变学量来来接收de酒cl映ar猾e谦@j饼ie抢sh闻ou悦zh绳i辈in抓tex挽ec男@修ji营es闷ho悟uz剪hi扶=p完r_波li狸erpr外in臂t奴@j由ie烤sh善ou轿zh锯i188.矛3.称3存储巷过程怠的返掉回值上例钳中返前回的迅值也秒可以天用ou曲tp峡ut参数分来返讽回，裤可以夺将上摧例的货存储科过程杜改写怀为：cr娘ea择te司p桑ro筒c晴pr抱_l拼ie阔r_旧2@f棵an犁hu昏iz丘hi迎i堡nt粱o次ut辅pu降tasbe奖gi销nse你le道ct颜@蜡fa韵nh钻ui郑zh朋i=似su辞m(修un合it君si疤ns评to柄ck菊)fr径om暖p彼ro贷du杯ct默sen盗d198.贼3.献3存储戏过程分的返也回值接收ou编tp熟ut的返忘回值廉也必鹿须要团用变搭量，托如：de忌cl束ar傲e档@j纺ie时sh提ou必zh钻i摇in疏tex签ec伴p柜r_粉li悼er疤_2吴@繁ji携es可ho铁uz蜘hi局o棵ut孩pu麻tpr铸in伶t猾@j析ie假sh蜜ou掘zh馆i注意锦：1)re恳tu截rn返回警的是线整数肠，ou玻tp舒ut可以车返回成任何泊数据奇。2)如果跌让re岩tu丹rn返回问非整蜂数值驶，在传创建共存储爬过程就时不厚会出驻错，礼但是睬运行沙存储么过程燥时将截会出难错。20例：必统计湖全体疯学生纪人数究，并世将统尽计结赢果用词输出凝参数得返回Cr柴ea浸te呢p诊ro做cp_织co丈un忙t@t帐ot篇alin啊tou仿tp抛utAsse埋le亭ct膨@删to齿ta曾l=冰co许un蹄t(长*)fr则om贡s布tu骨de惕nt执行摇此存惠储过改程:De他cl笑ar赔e苍@re崭sin止tEx册ecp_不co加un戏t@re掘sou赠tp第utPr树in碍t嫂@re捷s其中:蔽de煌cl书ar掘e为变顺量声叔明语刻句语法吩格式悠：de甚cl怨ar偿e筹@局部门变量粘名缸数据村类型@re厅s:变量排名。pr晴in筒t：输蔑出语摸句，符表示睬将后强边变惭量的导值显示示在脑屏幕肤上语法念格式蛛：pr昌in界t抖‘A待SC已II文本熄字符屯串’|@局部铜变量都名|字符眯串表庸达式218.宪4查看鱼存储再过程1.使用令对象止资源昂管理导器查瞧看存逗储过呢程2.使用糊系统正存储予过程写查看葬存储耍过程水信息在SQ清L厚Se芒rv蜡er中，食可以珍使用sp却_h职el刺pt谦ex稍t、sp讯_d雅ep喜en锅ds、sp揭_h视el够p等系尼统存鹿储过呈程来五查看改存储裳过程驼的不榨同信测息。1）使赛用sp姐_h该el混pt睁ex嗓t查看烟存储本过程幻玉的文偶本信泪息.其语抬法格片式为纺：sp史_h特el简pt唐ex缎t存储房诚过程螺名2）使姥用sp旬_d兽ep序en蹦ds查看号存储逝过程咐的相秧关性.其语期法格全式为扇：sp辉_d辆ep适en菠ds存储品过程阴名3）使医用sp犹_h焦el载p查看笨存储梯过程唉的一个般信栏息.其语数法格亩式为盾：sp步_h续el前p存储安过程久名228.别5修改湿存储六过程修改野存储许过程烦语法径格式嗽为：AL梦TE贡R棕PR桶OC[D驱UR遍E]pr残oc瘦ed屠ur俭e_闲na气me[{座@p泽ar鸽am残et钓erda输ta萄_t种yp完e}[=de堪fa恶ul起t]端[O干UT被PU扭T][默,…旱n][W砌IT蜡H笋{R菌EC浑OM猫PI瞒LE勺|桐E性NC涛RY响PT摘IO需N炼|哲RE念CO怪MP两IL盆E,援EN退CR定YP国TI聋ON惊}蔑]ASSq乓l_逗st轿at迟em粥en堡t238.仔5修改萝存储熄过程例：割修改贱存储逗过程st隆ud箱en澡t_咱in饼fo,查询计学生画信息嘱表中某个选指定顿学生氏信息席。其程旁序清怀单如尸下：AL惕TE钳RPR奥OCst纲ud敞en撇t_践in脆fo@学号va派rc贵ha撇r(竹14倡)AsSe缴le恒ct除*Fr鸭om学生捡信息Wh字er农e学号=@学号GO248.棋6编译撤存储拢过程在我敢们使烫用了宿一次寇存储蝴过程物后，争可能谦会因霞为某哲些原睛因，扮必须评向表划中新见增加爬数据奔列或烫者为宿表新皮添加幸索引絮，从枪而改宅变了救数据旋库的侍逻辑反结构秘。这闭时，季需要认对存走储过都程进偷行重共新编未译，SQ妹L塔Se盏rv骄er提供午三种背重新悠编译同存储影过程伏的方恒法：1、在筝建立攻存储追过程拖时设萌定重闭新编脸译语法尸格式快：CR葛EA轻TE守PR纵OC四ED肠UR灵Epr榴oc锄ed钥ur巨e_圣na价meWI临TH帮RE壳CO稿MP封IL免EASsq择l_榜st劝at杂em烘en国t2、在织执行触存储发过程炕时设篮定重崖编译语法妥格式俯：EX蜻EC叮UT诵Epr城oc研