数据技术应用 3

项目9使用存储过程管理数据——拓展项目任务单任务名称“新闻发布系统”数据库存储过程的创建与管理任务编号3-4任务目标1．能正确理解存储过程的概念和应用场景。2．会使用可视化界面T-SQL脚本创建和管理存储过程。3．能正确理解事务的概念和类型。4．会使用可视化界面和T-SQL脚本创建和管理事务。任务描述新闻发布系统的常见业务包括数据参数化查询，统计各类新闻数量、数据批量修改等。请借助存储过程封装如下操作：1．创建一个存储过程查询用户评论列表。（1）查询用户编号、用户名称、用户密码、评论标题，评论内容。（2）调用存储过程，验证存储过程执行结果。2．创建一个存储过程根据输入的用户编号查询某用户的评论信息。（1）输入参数：用户编号；输出内容：用户编号、用户名称、用户密码、评论标题和评论内容。（2）调用存储过程，验证存储过程执行结果。3．创建一个存储过程根据输入的新闻栏目标题计算新闻数量。（1）输入参数：新闻栏目名称；输出内容：栏目编号、栏目名称、新闻数量。（2）调用存储过程，验证存储过程执行结果。4．创建一个事务向新闻信息表中插入3条记录，并检验若插入相同的新闻标题，则回滚事务，否则提交。5．创建一个事务删除用户的同时，删除用户评论记录。完成方式¨小组协作完成¨个人单独完成负责人参与人员工作流程1．创建一个存储过程查询用户评论列表。（1）查询用户编号、用户名称、用户密码、评论标题，评论内容。CREATEPROCEDUREsp_GetUserCommentsASBEGINSELECTu.user_idAS用户编号,u.usernameAS用户名称,u.passwordAS用户密码,c.titleAS评论标题,c.contentAS评论内容FROMtb_usersuJOINtb_commentscONu.user_id=c.user_id;END;GO（2）调用存储过程，验证存储过程执行结果。EXECsp_GetUserComments;2．创建一个存储过程根据输入的用户编号查询某用户的评论信息。（1）输入参数：用户编号；输出内容：用户编号、用户名称、用户密码、评论标题和评论内容。CREATEPROCEDUREsp_GetCommentsByUser@UserIDINTASBEGINSELECTu.user_idAS用户编号,u.usernameAS用户名称,u.passwordAS用户密码,c.titleAS评论标题,c.contentAS评论内容FROMtb_usersuJOINtb_commentscONu.user_id=c.user_idWHEREu.user_id=@UserID;END;GO（2）调用存储过程，验证存储过程执行结果。EXECsp_GetCommentsByUser@UserID=1001;3．创建一个存储过程根据输入的新闻栏目标题计算新闻数量。（1）输入参数：新闻栏目名称；输出内容：栏目编号、栏目名称、新闻数量。CREATEPROCEDUREsp_CountNewsByType@TypeNameNVARCHAR(50)ASBEGINSELECTt.type_idAS栏目编号,t.type_nameAS栏目名称,COUNT(n.news_id)AS新闻数量FROMtb_typestLEFTJOINtb_newsnONt.type_id=n.type_idWHEREt.type_name=@TypeNameGROUPBYt.type_id,t.type_name;END;GO（2）调用存储过程，验证存储过程执行结果。EXECsp_CountNewsByType@TypeName='体育';4．创建一个事务向新闻信息表中插入3条记录，并检验若插入相同的新闻标题，则回滚事务，否则提交。CREATEPROCEDUREsp_InsertNewsBatchASBEGINBEGINTRYBEGINTRANSACTION;--插入第一条INSERTINTOtb_news(title,type_id,content,author_id)VALUES('新闻标题1',1,'内容1',1001);--插入第二条INSERTINTOtb_news(title,type_id,content,author_id)VALUES('新闻标题2',2,'内容2',1002);--插入第三条（故意重复）INSERTINTOtb_news(title,type_id,content,author_id)VALUES('新闻标题1',3,'内容3',1003);--重复标题COMMITTRANSACTION;ENDTRYBEGINCATCHIF@@TRANCOUNT>0ROLLBACKTRANSACTION;DECLARE@ErrorMessageNVARCHAR(4000);SET@ErrorMessage=ERROR_MESSAGE();RAISERROR('插入失败:%s',16,1,@ErrorMessage);ENDCATCHEND;GO--验证存储过程EXECsp_InsertNewsBatch;--预期会回滚5．创建一个事务删除用户的同时，删除用户评论记录。CREATEPROCEDUREsp_DeleteUserWithComments@UserIDINTASBEGINBEGINTRYBEGINTRANSACTION;--删除用户评论DELETEFROMtb_commentsWHEREuser_id=@UserID;--删除用户DELETEFROMtb_usersWHEREuser_id=@UserID;--验证删除是否成功IF@@ROWCOUNT=0BEGINRAISERROR('用户不存在',16,1);ROLLBACK;ENDELSECOMMITTRANSACTION;ENDTRYBEGINCATCHIF@@TRANCOUNT>0ROLLBACKTRANSACTION;DECLARE@ErrorMessageNVARCHAR(4000);SET@ErrorMessage=ERROR_MESSAGE();RAISERROR('删除失败:%s',16,1,@ErrorMessage);ENDCATCHEND;GO--验证存储过程EXECsp_DeleteUserWithComments@UserID=1001;

信创技能拓展知识与技能拓展1达梦数据库创建存储过程：存储过程是一种预编译的SQL代码集合，它可以接受输入参数，执行一系列数据库操作，并返回输出结果或影响数据库状态。在达梦数据库中，存储过程的创建、调用和管理都遵循一套标准的语法和流程。其定义语法如下：CREATE[ORREPLACE]PROCEDURE<存储过程名定义>[(<参数名><参数模式><参数类型>[<默认值表达式>]{,<参数名><参数模式><参数类型>]})][WITHENCRYPTION]AS|IS[<声明部分>]BEGIN<执行部分>[<异常处理部分>]END;1.存储过程可以带有参数，这样在调用存储过程时就需指定相应的实际参数，如果没有参数，过程名后面的圆括号和参数列表就可以省略。2.声明部分进行变量声明。3.可执行部分是存储过程的核心部分，由SQL语句和流控制语句构成。支持的SQL语句包括：（1）数据查询语句（SELECT）（2）数据操纵语句（INSERT、DELETE、UPDATE）（3）游标定义及操纵语句（DECLARECURSOR、OPEN、FETCH、CLOSE）（4）事务控制语句（COMMIT、ROLLBACK）（5）动态SQL执行语句（EXECUTEIMMEDIATE）知识与技能拓展2达梦数据库调用存储过