当前位置：Gxlcms > 数据库问题 > SQL Server之存储过程

SQL Server之存储过程

时间：2021-07-01 10:21:17 帮助过：6人阅读

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

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

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

  1. <span style="color: #008080;">--</span><span style="color: #008080;">===========系统存储过程==============</span>
  2. <span style="color: #008080;">--</span><span style="color: #008080;">显示系统数据库</span>
  3. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> sp_databases
  4. </span><span style="color: #008080;">--</span><span style="color: #008080;">显示数据库详细信息</span>
  5. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> sp_helpdb
  6. </span><span style="color: #008080;">--</span><span style="color: #008080;">给指定的数据库更换名称</span>
  7. <span style="color: #0000ff;">exec</span> sp_renamedb <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aa</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bb</span><span style="color: #ff0000;">‘</span>
  8. <span style="color: #008080;">--</span><span style="color: #008080;">查看指定表名的详细信息</span>
  9. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> sp_help student
  10. </span><span style="color: #008080;">--</span><span style="color: #008080;">查看指定索引、视图、存储过程等的创建文本信息</span>
  11. <span style="color: #0000ff;">exec</span><span style="color: #000000;"> sp_helptext sp_help
  12. </span><span style="color: #008080;">--</span><span style="color: #008080;">调用存储过程必须在批处理文件第一位</span>
  13. <span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysobjects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Table1</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  14.     </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> Table1
  15. </span><span style="color: #0000ff;">go</span><span style="color: #000000;">    
  16. sp_help sp_help
  17. </span><span style="color: #008080;">--</span><span style="color: #008080;">==========系统扩展存储过程================</span>
  18. <span style="color: #0000ff;">use</span><span style="color: #000000;"> master
  19. </span><span style="color: #0000ff;">go</span>
  20. <span style="color: #008080;">--</span><span style="color: #008080;">创建文件夹bank</span>
  21. <span style="color: #0000ff;">exec</span> xp_cmdshell <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">mkdir D:\bank</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,no_output
  22.     </span><span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysdatabases <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bankDB</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  23.         </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">database</span><span style="color: #000000;"> bankDB
  24.     </span><span style="color: #0000ff;">go</span>
  25.     
  26.     <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">database</span><span style="color: #000000;"> bankDB
  27.     </span><span style="color: #0000ff;">on</span> <span style="color: #0000ff;">primary</span><span style="color: #000000;">
  28.     (
  29.         name </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bankDB</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  30.         filename </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">D:\bank\bankDB.mdf</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  31.         size </span><span style="color: #808080;">=</span><span style="color: #000000;"> 5MB,
  32.         maxsize </span><span style="color: #808080;">=</span><span style="color: #000000;"> 10MB,
  33.         filegrowth </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">15</span><span style="color: #808080;">%</span><span style="color: #000000;">    
  34.     )</span><span style="color: #ff00ff;">log</span> <span style="color: #0000ff;">on</span><span style="color: #000000;">(
  35.         name </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bankDB_log</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  36.         filename </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">D:\bank\bankDB_log.ldf</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  37.         size </span><span style="color: #808080;">=</span><span style="color: #000000;"> 5MB,
  38.         filegrowth </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">15</span><span style="color: #808080;">%</span><span style="color: #000000;">
  39.     )
  40.     
  41. </span><span style="color: #008080;">--</span><span style="color: #008080;">调用储存过程查看文件夹信息</span>
  42. <span style="color: #0000ff;">exec</span> xp_cmdshell <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">dir D:\bank\</span><span style="color: #ff0000;">‘</span>
  43. <span style="color: #008080;">--</span><span style="color: #008080;">========创建存储过程，查询Java Logic最近一次考试平均分以及未通过考试的学员名单=========</span>
  44. <span style="color: #0000ff;">use</span><span style="color: #000000;"> MySchool
  45. </span><span style="color: #0000ff;">go</span>
  46. <span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysobjects <span style="color: #0000ff;">where</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sp_getavgresult</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  47. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> sp_getavgresult
  48. </span><span style="color: #0000ff;">go</span>
  49. <span style="color: #008080;">--</span><span style="color: #008080;">创建存储过程实现</span>
  50. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">proc</span><span style="color: #000000;"> sp_getavgresult
  51.     </span><span style="color: #008000;">@returnnum</span> <span style="color: #0000ff;">int</span> output, <span style="color: #008080;">--</span><span style="color: #008080;">返回未及格的人数</span>
  52.     <span style="color: #008000;">@returnsum</span> <span style="color: #0000ff;">int</span> output, <span style="color: #008080;">--</span><span style="color: #008080;">参加考试总人数</span>
  53.     <span style="color: #008000;">@subjectName</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">30</span>), <span style="color: #008080;">--</span><span style="color: #008080;">添加科目</span>
  54.     <span style="color: #008000;">@score</span> <span style="color: #0000ff;">int</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #008080;">--</span><span style="color: #008080;">添加输入参数（及格分数）</span>
  55. <span style="color: #0000ff;">as</span>
  56. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@subjectId</span> <span style="color: #0000ff;">int</span>
  57. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@maxdate</span><span style="color: #000000;"> date
  58. </span><span style="color: #0000ff;">declare</span> <span style="color: #008000;">@avg</span> <span style="color: #0000ff;">int</span>
  59. <span style="color: #008080;">--</span><span style="color: #008080;">查询java logic课程的编号</span>
  60. <span style="color: #0000ff;">select</span> <span style="color: #008000;">@subjectId</span> <span style="color: #808080;">=</span> SubjectId <span style="color: #0000ff;">from</span> Subject <span style="color: #0000ff;">where</span> SubjectName <span style="color: #808080;">=</span> <span style="color: #008000;">@subjectName</span>
  61. <span style="color: #008080;">--</span><span style="color: #008080;">查询java logic课程最近一次考试时间</span>
  62. <span style="color: #0000ff;">select</span> <span style="color: #008000;">@maxdate</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">MAX</span>(ExamDate) <span style="color: #0000ff;">from</span> Result <span style="color: #0000ff;">where</span> SubjectId <span style="color: #808080;">=</span> <span style="color: #008000;">@subjectId</span>
  63. <span style="color: #008080;">--</span><span style="color: #008080;">查询java logic课程最近一次考试的平均分</span>
  64. <span style="color: #0000ff;">select</span> <span style="color: #008000;">@avg</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">AVG</span>(StudentResult) <span style="color: #0000ff;">from</span> Result <span style="color: #0000ff;">where</span><span style="color: #000000;"> 
  65.     SubjectId </span><span style="color: #808080;">=</span> <span style="color: #008000;">@subjectId</span> <span style="color: #808080;">and</span> ExamDate <span style="color: #808080;">=</span> <span style="color: #008000;">@maxdate</span>
  66. <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">未通过考试的人员名单：=======================</span><span style="color: #ff0000;">‘</span>
  67. <span style="color: #008080;">--</span><span style="color: #008080;">查询java logic课程最近一次考试未通过的学生名单</span>
  68. <span style="color: #0000ff;">select</span> studentName,studentResult <span style="color: #0000ff;">from</span><span style="color: #000000;"> student s 
  69. </span><span style="color: #0000ff;">inner</span> <span style="color: #808080;">join</span> Result r <span style="color: #0000ff;">on</span> r.StudentNo <span style="color: #808080;">=</span><span style="color: #000000;"> s.StudentNo
  70.         </span><span style="color: #0000ff;">where</span> SubjectId <span style="color: #808080;">=</span> <span style="color: #008000;">@subjectId</span> 
  71.         <span style="color: #808080;">and</span> ExamDate <span style="color: #808080;">=</span> <span style="color: #008000;">@maxdate</span>
  72.         <span style="color: #808080;">and</span> StudentResult <span style="color: #808080;"><</span> <span style="color: #008000;">@score</span>
  73.         
  74. <span style="color: #008080;">--</span><span style="color: #008080;">查询参加考试的总人数</span>
  75. <span style="color: #0000ff;">select</span> <span style="color: #008000;">@returnsum</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">from</span> Result <span style="color: #0000ff;">where</span> ExamDate <span style="color: #808080;">=</span> <span style="color: #008000;">@maxdate</span> <span style="color: #808080;">and</span> SubjectId <span style="color: #808080;">=</span> <span style="color: #008000;">@subjectId</span>
  76. <span style="color: #008080;">--</span><span style="color: #008080;">查询未及格的人数        </span>
  77. <span style="color: #0000ff;">select</span> <span style="color: #008000;">@returnnum</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">from</span> Result <span style="color: #0000ff;">where</span> ExamDate <span style="color: #808080;">=</span> <span style="color: #008000;">@maxdate</span> <span style="color: #808080;">and</span> SubjectId <span style="color: #808080;">=</span> <span style="color: #008000;">@subjectId</span>
  78.                                   <span style="color: #808080;">and</span> StudentResult <span style="color: #808080;"><</span> <span style="color: #008000;">@score</span>
  79.         
  80. <span style="color: #0000ff;">if</span>(<span style="color: #008000;">@avg</span> <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">70</span><span style="color: #000000;">)
  81. </span><span style="color: #0000ff;">begin</span>
  82.     <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">考试结果：优秀</span><span style="color: #ff0000;">‘</span>
  83. <span style="color: #0000ff;">end</span>
  84. <span style="color: #0000ff;">else</span>
  85. <span style="color: #0000ff;">begin</span>
  86.     <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">考试结果：较差</span><span style="color: #ff0000;">‘</span>
  87. <span style="color: #0000ff;">end</span>
  88. <span style="color: #0000ff;">go</span>
  89. <span style="color: #008080;">--</span><span style="color: #008080;">=======调用储存过程实现业务逻辑===========----</span>
  90. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@sum</span> <span style="color: #0000ff;">int</span> <span style="color: #008080;">--</span><span style="color: #008080;">参加考试总人数</span>
  91. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@num</span> <span style="color: #0000ff;">int</span> <span style="color: #008080;">--</span><span style="color: #008080;">未及格人数</span>
  92. <span style="color: #0000ff;">declare</span> <span style="color: #008000;">@percent</span> <span style="color: #0000ff;">float</span>(<span style="color: #800000; font-weight: bold;">2</span>) <span style="color: #008080;">--</span><span style="color: #008080;">及格百分比</span>
  93. <span style="color: #008080;">--</span><span style="color: #008080;">调用存储过程</span>
  94. <span style="color: #0000ff;">exec</span> sp_getavgresult <span style="color: #008000;">@num</span> output,<span style="color: #008000;">@sum</span> output,<span style="color: #008000;">@subjectName</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">java logic</span><span style="color: #ff0000;">‘</span>,<span style="color: #008000;">@score</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">60</span>
  95. <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">=========================================</span><span style="color: #ff0000;">‘</span>
  96. <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">参加考试人数为：</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">30</span>),<span style="color: #008000;">@sum</span><span style="color: #000000;">)
  97. </span><span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">未及格人数为：</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">30</span>),<span style="color: #008000;">@num</span><span style="color: #000000;">)
  98. </span><span style="color: #008080;">--</span><span style="color: #008080;">计算及格率</span>
  99. <span style="color: #0000ff;">set</span> <span style="color: #008000;">@percent</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">float</span>(<span style="color: #800000; font-weight: bold;">2</span>),(<span style="color: #008000;">@sum</span> <span style="color: #808080;">-</span> <span style="color: #008000;">@num</span>))<span style="color: #808080;">/</span><span style="color: #008000;">@sum</span> <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">100</span>
  100. <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">及格百分比：</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">30</span>),<span style="color: #008000;">@percent</span>) <span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%</span><span style="color: #ff0000;">‘</span>
  101. <span style="color: #008080;">--</span><span style="color: #008080;">判断是否要调及格分数线</span>
  102. <span style="color: #0000ff;">if</span>(<span style="color: #008000;">@percent</span> <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;">)
  103. </span><span style="color: #0000ff;">begin</span>
  104.     <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">不需要调分数线。。。</span><span style="color: #ff0000;">‘</span>
  105. <span style="color: #0000ff;">end</span>
  106. <span style="color: #0000ff;">else</span>
  107. <span style="color: #0000ff;">begin</span>
  108.     <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">需要降低分数线。。。</span><span style="color: #ff0000;">‘</span>
  109. <span style="color: #0000ff;">end</span>

 

SQL Server之存储过程

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！