《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.14 创建存储过程

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

本节书摘来自异步社区出版社《PowerShell V3—SQL Server 2012数据库自动化运维权威指南》一书中的第2章,第2.14节,作者:【加拿大】Donabel Santos,更多章节内容可以访问云栖社区“异步社区”公众号查看。

2.14 创建存储过程

本方案展示了如何用PowerShell和SMO创建加密的存储过程。

2.14.1 准备

我们要用PowerShell创建的加密存储过程的等价T-SQL代码如下:

CREATE PROCEDURE [dbo].[uspGetPersonByLastName] @LastName [varchar]
(50)
WITH ENCRYPTION
AS
SELECT 
  TOP 10 
  BusinessEntityID,
  LastName
FROM 
  Person.Person
WHERE 
  LastName = @LastName

2.14.2 如何做…

按照如下步骤使用PowerShell创建存储过程uspGetPersonByLastName。

1.通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2.导入SQLPS模块,创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3.添加如下脚本并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
#storedProcedure class on MSDN: 
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.storedproc 
edure.aspx
$sprocName = "uspGetPersonByLastName"
$sproc = $db.StoredProcedures[$sprocName]
#if stored procedure exists, drop it
if ($sproc)
{
   $sproc.Drop()
}
$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure
-ArgumentList $db, $sprocName
#TextMode = false means stored procedure header 
#is not editable as text
#otherwise our text will contain the CREATE PROC block
$sproc.TextMode = $false
$sproc.IsEncrypted = $true
$paramtype = [Microsoft.SqlServer.Management.SMO.Datatype]::VarChar(50);
$param = New-Object –TypeName
Microsoft.SqlServer.Management.SMO.StoredProcedureParameter –ArgumentList
$sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)
#Set the TextBody property to define the stored procedure. 
$sproc.TextBody = @" 
SELECT 
  TOP 10 
  BusinessEntityID,
  LastName
FROM 
  Person.Person
WHERE 
  LastName = @LastName
"@

# Create the stored procedure on the instance of SQL Server. 
$sproc.Create()
#if later on you need to change properties, can use the Alter method

4.检查是否存储过程已创建。

(1)打开SSMS。

(2)展开AdventureWorks2008R2数据库。

(3)展开“Programmability | Stored Procedures”。

(4)查看存储过程是否在这里。

image

5.在PowerShell中测试存储过程。在同一个会话中,输入如下代码并运行。

$lastName = "Abercrombie"
$result = Invoke-Sqlcmd `
-Query "EXEC uspGetPersonByLastName @LastName=`'$LastName`'" `
-ServerInstance "$instanceName" `
-Database $dbName 
$result | Format-Table –AutoSize

2.14.3 如何实现…

为了创建存储过程,首先需要初始化SMO StoredProcedure对象。创建这个对象时,需要传递数据库句柄和存储过程名作为参数。

$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure
-ArgumentList $db, $sprocName

你可以设置存储过程对象的一些属性,如是否加密。

$sproc.IsEncrypted = $true

如果定制了TextMode = $true,你需要自己创建存储过程的头部。如果你有参数,这些将会在你的文本头部被定义,例如:

$sproc.TextMode = $true
$sproc.TextHeader = @"
CREATE PROCEDURE [dbo].[uspGetPersonByLastName]
 @LastName [varchar](50)
AS
"@

否则,如果TextMode = $false,技术上允许PowerShell自动生成头部,基于你给定的其他属性和参数设置。你也需要逐一创建参数对象并将它们添加到存储过程中。

$sproc.TextMode = $false
$paramtype = [Microsoft.SqlServer.Management.SMO.
Datatype]::VarChar(50);
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.
StoredProcedureParameter -ArgumentList $sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)

当创建存储过程时,使用字符串设置存储过程对象的TextBody属性定义。

$sproc.TextBody = @" 
SELECT 
  TOP 10 
  BusinessEntityID,
  LastName
FROM 
  Person.Person
WHERE 
  LastName = @LastName
"@

一旦头部信息、定义和存储过程属性就位,你可以调用Create方法,将CREATEPROC语句发送到SQL Server,并创建存储过程。

# Create the stored procedure on the instance of SQL Server. 
$sproc.Create()
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
3月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
4月前
|
SQL 数据库连接 Linux
数据库编程:在PHP环境下使用SQL Server的方法。
看看你吧,就像一个调皮的小丑鱼在一片广阔的数据库海洋中游弋,一路上吞下大小数据如同海中的珍珠。不管有多少难关,只要记住这个流程,剩下的就只是探索未知的乐趣,沉浸在这个充满挑战的数据库海洋中。
104 16
|
4月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
9月前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
598 3
|
5月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
162 9
|
6月前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
610 6
|
7月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
168 11
|
7月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
7月前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制,旨在检测潜在威胁和漏洞,监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息,帮助管理员追踪数据库中的特定活动,确保数据安全和合规性。SQL Server审核分为服务器级和数据库级,涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警,帮助快速响应安全事件。
136 1
|
8月前
|
SQL Java 数据库连接
canal-starter 监听解析 storeValue 不一样,同样的sql 一个在mybatis执行 一个在数据库操作,导致解析不出正确对象
canal-starter 监听解析 storeValue 不一样,同样的sql 一个在mybatis执行 一个在数据库操作,导致解析不出正确对象