数据库存储过程详解

大强同学_7926

大强同学_7926

2019-06-14

16183人浏览

原创

DrugBank 数据库
DrugBank 数据库

访问并分析来自 DrugBank 数据库的全面药物信息,包括药物属性、相互作用、靶点、通路、化学结构和药理学数据。用于药物数据、药物发现研究、药理学研究、药物-

下载

/*
存储过程可以看作是在数据库中的存储t-sql脚本

为什么使用存储过程
1、增加性能   本地存储发送的内容少、调用快、预编译、高速缓存
       一般语句的执行:检查权限、检查语法,建立执行计划处理语句的要求
       存储过程:创建时已经检查了语法;第一次执行的时候执行计划被创建,被编译;
              再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划
2、增强安全   加密、分离(权限设置,用户只需要有执行存储过程的权限,不需要有访问存储过程所使用的对象的权限)
   
3、在transact-sql中使用非数据库技术  dll
4、编程模式——使用外部编程语言调用
   1)input
   2)output
   3)feedback 状态代码或描述性的文本
   4)模块化、可重用、可调用其他存储过程
   5)隐藏程序逻辑,便于编程
   6)可以调用动态连接库(外接的程序)
基本原则:越简单越好 单一任务
*/

/*
分类
1、系统存储过程 
   存在于master数据库,一般以sp_开头
   提供对系统表格数据调用、数据库管理功能、安全管理功能的支持
  --表格授权
  use pubs
  go
  execute sp_table_privileges stores
  --显示kylin\administrator的所有进程
  execute sp_who @loginame='W2K3SERVER\Administrator'
  --报告有关孤立的 microsoft windows nt 用户和组的信息,这些用户和组已不在 windows nt 环境中,但仍在 microsoft sql server系统表中拥有项。
  execute sp_validatelogins
2、本地存储过程   用户创建的解决特定问题的
3、临时存储过程   存储于tempdb
                    创建、调用时的数据库    使用范围           生存周期 
   #local                     不限数据库        创建时的连接有效    从创建时开始,当创建的连接中断时消失
   ##global                   不限数据库        所有连接            从创建时开始,当创建的连接中断时消失
   直接创建在tempdb的存储过程  tempdb            所有连接            从创建时开始,当数据库服务器服务停止时消失
   create proc #local
   as
   select '#local'
   go
   exec #local
   go
   create proc ##global
   as
   select '##global'
   go
   exec ##global
   go
   use tempdb
    go
    create procedure directtemp
    as
    select * from [pubs].[dbo].[authors]
    go
   use northwind
   go
   exec tempdb.dbo.directtemp

4、扩展存储过程  c++ xp
   xp_sendmail既是系统存储过程,也是扩展存储过程
   使用objectproperty来判断是否是扩展存储过程
    use master
    --扩展存储过程
    select objectproperty(object_id('sp_prepare'), 'isextendedproc')
    --非扩展存储过程
    select objectproperty(object_id('xp_logininfo'), 'isextendedproc')
5、远程存储过程
   目前版本中只是为了向后兼容,已被分布式查询替代
*/


/*
存储过程在数据库中如何存储
名字 sysobjects
文本 syscomments 
*/

/*
练习1:通过查询分析器中的对象查看器查看存储过程
*/

/*
练习2:查看存储过程的内容
       图形
       语句
*/
select * from sysobjects
select * from syscomments 
go
select * from syscomments 
where id = object_id('custorderhist')
go
select name,text
from sysobjects inner join syscomments 
on sysobjects.id = syscomments.id
where sysobjects.name = 'custorderhist'
go
sp_helptext sp_helptext
go
use northwind
go
exec sp_help custorderhist
exec sp_helptext custorderhist
exec sp_depends custorderhist
exec sp_stored_procedures 'custorderhist' 


/*
系统存储过程
以使用为主
*/

/*
本地存储过程的创建、修改、删除
1、t-sql语句
create procedure
alter procedure
drop procedure

create procedure 存储过程名字
as
存储过程文本
go 

alter procedure 存储过程名字
as
存储过程文本
go 
 
drop procedure 存储过程名字
2、企业管理器  右键
               向导
*/

/*
简单 
*/
-- -- -- select top 1 * from products
-- -- -- select top 1 * from orders
-- -- -- select top 1 * from [order details]
/*1、和视图比较*/
alter  proc sp_qry_salesdetails
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,
b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
go
print '测试'

execute sp_qry_salesdetails

--递归算法
--视图  存储过程  函数
alter view v_qry_salesdetails
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,
b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
print '测试'

select * from v_qry_salesdetails 
/*
默认情况下第一次执行时的执行计划被保存,以后执行时都是用这个执行计划,直到服务器重启或存储过程使用的表格变化时
当存储过程变化时,如:参数变化,需要重新编译、制定新的执行计划
当每次调用存储过程时强制重新编译的方法:
1、创建时指定 with recompile 
2、sp_recompile 
*/
create procedure sp1
as 
select * from customers

exec sp1

alter procedure sp1
as 
select * from customers

alter procedure sp1
with recompile
as 
select * from customers

sp_recompile sp1

--加密存储过程 with encryption 
select objectproperty(object_id('sp_qry_salesdetails'), 'isencrypted')

/*
删除存储过程
drop proc 
*/
use northwind
go
create proc dbo.sp_dropproc
as
select 'northwind.dbo.sp_dropproc'
go
exec northwind.dbo.sp_dropproc
go
use master
go
create proc dbo.sp_dropproc
as
select 'master.dbo.sp_dropproc'
go
exec master.dbo.sp_dropproc
go
use northwind
go
drop proc sp_dropproc
go
exec sp_dropproc
exec master.dbo.sp_dropproc

/*
提供输入参数 input
*/
create proc qry_salesdetails @y int,@m int --varchar(10)
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
--where convert(varchar(2),month(c.requireddate)) = @m
where year(c.requireddate) = @y and month(c.requireddate) = @m
go 
exec qry_salesdetails 1996,9
exec qry_salesdetails 9,1996
exec qry_salesdetails @m=9,@y=1996
exec qry_salesdetails @y=1996,@m=9
go
/*
northwind 数据库
orders order details 表格 *
根据指定用户ID显示此用户在1996-07-01到1997-07-01之间的订货记录 
要求存储过程文本加密 
*/


use northwind
go
--创建存储过程
-- drop proc qry_showorders 
create proc qry_showorders @custid nchar(5)
with encryption   --加密
as
if @custid is  null
-- begin
--   print '提供了不正确的参数'
--   return
-- end
select * 
from orders od inner join [order details] oddt
on od.orderid = oddt.orderid
where shippeddate >='1996-07-01' and shippeddate 0
begin
   print '有错误'
   set @e = @@error
end
   return @e
go
declare @er int
exec @er = testerror
select @er

/*
  @@rowcount
*/

select @@rowcount
select * from customers
select @@rowcount

/*
null 值
*/
create proc testreturn @a int
as 
if @a is null
begin
   return(100)
end
else if @a=@start

go
exec qry_salesdetails 6,'1996-01-01','1997-01-01'






alter proc qry_salesdetails @no int = -1,@start char(10),@end char(10)
as 
declare @sql varchar(4000)
set @sql = 'select a.productid as 商品编号,a.productname as 商品名称,
b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,
c.requireddate as 销售时间 
        from [order details] as b join products as a
    on b.productid=a.productid
    join orders as c
    on b.orderid=c.orderid  where 1=1  '
if @no is not null
     set @sql = @sql + ' and  a.productid = '+convert(varchar(10),@no)
if @start is not null  and  @end is not null
     set @sql = @sql    + ' and c.requireddate >=  '''+ @start+''''
                        + ' and c.requireddate <p>更多sql的相关技术文章,请访问<a href="http://www.php.cn/sql/" target="_self" style="color: rgb(146, 208, 80); text-decoration: underline;"><span style="color: rgb(146, 208, 80);">sql教程</span></a><span style="color: rgb(146, 208, 80);"></span>栏目进行学习!</p>

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

数据库 存储过程

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
Navicat创建数据库教程
Navicat创建数据库教程

本专题整理Navicat创建数据库相关教程,介绍新建前的准备、基础设置和创建后的检查方法。内容兼顾入门学习及实际使用需求,帮助用户了解不同数据库类型下的操作差异,为后续建表、保存数据和日常管理提供参考。

2026.11.20

20

7

Navicat入门教程大全
Navicat入门教程大全

本专题整理Navicat入门教程,从认识软件界面到开展数据库操作,介绍初次使用需要了解的知识和方法。内容兼顾连接配置、数据管理及日常使用中的疑问,方便用户按学习进度查阅,为后续实践和独立操作打好基础。

2026.11.20

20

17

Navicat数据库连接配置指南
Navicat数据库连接配置指南

从确认数据库信息到建立可用连接,本专题串联Navicat连接数据库的相关知识和操作方法。既有初次配置时需要了解的基础内容,也有连接后的检查思路,方便用户按实际需求查阅教程,逐步熟悉数据库连接的设置流程。

2026.11.20

0

10

PHP8.6数组与数据处理教程
PHP8.6数组与数据处理教程

本专题汇总PHP8.6数组与数据处理教程,涵盖CSV读取和导出、引用遍历删除、array_filter回调模式、配置键名冲突、pathinfo文件名校验、GMP大整数进制转换及UTF-8正则筛选,说明升级兼容变化、输入范围和失败处理方法。

2026.11.20

60

8

PHP8.6升级迁移教程
PHP8.6升级迁移教程

本专题汇总PHP8.6升级迁移与弃用接口改造教程,涵盖反射调用、对象属性赋值、标识符更名、构造函数与__debugInfo调整,以及插件类名检查、常量导出、ArrayIterator排序、SPL和DTO查询参数转换,帮助梳理弃用提示并完成代码兼容改造。

2026.11.20

0

10

PHP8.6安装配置与FPM运维教程
PHP8.6安装配置与FPM运维教程

本专题汇总PHP8.6正式版安装部署与FPM运维教程,涵盖Linux并存安装、独立ini配置、扩展重新编译、Nginx切换与回滚、FPM状态监控和慢日志排查,以及环境变量、请求超时、OPcache刷新和预加载配置。

2026.11.20

40

10

Nginx CVE-2026-123007排查与防护指南
Nginx CVE-2026-123007排查与防护指南

Nginx HTTP/2漏洞该怎么修复?本专题围绕CVE-2026-123007,介绍安全版本升级、模块兼容检查和配置恢复方法,以及日志排查、权限设置等后续加固操作,方便你按实际部署情况查找对应教程。

2026.11.20

40

8

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

80

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程