电脑安全

您当前的位置:主页 > 电脑安全 >

MSSQL SERVER认识存储过程语法-网络编程

来源:[db:来源]标题:   存储过程    编辑:电脑技术全能网 时间:2019-09-18 12:31

原标题:MSSQL SERVER认识存储过程语法-网络编程
   中心提醒:创立存储进程,存储进程是保留起来的能够接收和前往用户供给的参数的 Transact-SQL 语句的聚集  CREATE PROCEDURE  创立存储进程,存储进程是保留起来的能够接收和前往用户供给的参数的 Transact-SQL 语句的聚集。能够创立一个进程供永恒应用,或在一个会话中常设应用(部分常设进程),或在全部会话中常设应用(全局常设进程)。也能够创立在 Microsoft SQL Server启动时主动运转的存储进程。  语法  CREATE PROC [ EDURE ] procedure_name [ ; number ]  [ { @parameter data_type }  [ VARYING ] [ = default ] [ OUTPUT ]  ] [ ,...n ]  [ WITH  { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]  [ FOR REPLICATION ]  AS sql_statement [ ...n ]  参数  procedure_name  新存储进程的称号。进程名必需合乎标识符规矩,且关于数据库及其全部者必需独一。无关更多信息,请拜见应用标识符。  要创立部分常设进程,能够在 procedure_name 后面加一个编号符 (#procedure_name),要创立全局常设进程,能够在 procedure_name 后面加两个编号符 (##procedure_name)。完全的称号(包含 # 或 ##)不能超越 128 个字符。指定进程全部者的称号是可选的。  ;number  是可选的整数,用来对同名的进程分组,以便用一条 DROP PROCEDURE 语句便可将同组的进程一同撤除。比方,名为 orders 的利用顺序应用的进程能够定名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将撤除全部组。假如称号中包括定界标识符,则数字不该包括在标识符中,只应在 procedure_name 前后应用恰当的定界符。  @parameter  进程中的参数。在 CREATE PROCEDURE 语句中能够申明一个或多个参数。用户必需在履行进程时供给每个所申明参数的值(除非界说了该参数的默许值)。存储进程最多能够有 2.100 个参数。  应用 @ 标记作为第一个字符来指定参数称号。参数称号必需合乎标识符的规矩。每个进程的参数仅用于该进程自身;雷同的参数称号能够用在别的进程中。默许情形下,参数只能取代常量,而不能用于取代表名、列名或别的数据库工具的称号。无关更多信息,请拜见 EXECUTE。  data_type  参数的数据范例。全部数据范例(包含 text、ntext 和 image)都可以用作存储进程的参数。不外,cursor 数据范例只能用于 OUTPUT 参数。假如指定的数据范例为 cursor,也必需同时指定 VARYING 和 OUTPUT 要害字。无关 SQL Server 供给的数据范例及其语法的更多信息,请拜见数据范例。  阐明 关于能够是 cursor 数据范例的输入参数,没有最大数量的限度。  VARYING  指定作为输入参数支撑的成果集(由存储进程静态结构,内容能够变更)。仅实用于游标参数。  default  参数的默许值。假如界说了默许值,不用指定该参数的值便可履行进程。默许值必需是常量或 NULL。假如进程将对该参数应用 LIKE 要害字,那末默许值中能够包括通配符(*、_、[] 和 [^])。  OUTPUT  标明参数是前往参数。该选项的值能够前往给 EXEC[UTE]。应用 OUTPUT 参数可将信息前往给挪用进程。Text、ntext 和 image 参数可用作 OUTPUT 参数。应用 OUTPUT 要害字的输入参数能够是游标占位符。  n  表现最多能够指定 2.100 个参数的占位符。  {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}  RECOMPILE 标明 SQL Server 不会缓存该进程的打算,该进程将在运转时从新编译。在应用非典范值或常设值而不盼望笼罩缓存在内存中的履行打算时,请应用 RECOMPILE 选项。  ENCRYPTION 表现 SQL Server 加密 syscomments 表中包括 CREATE PROCEDURE 语句文本的条目。应用 ENCRYPTION 可避免将进程作为 SQL Server 复制的一局部公布。  阐明 在进级进程中,SQL Server 应用存储在 syscomments 中的加密解释来从新创立加密进程。  FOR REPLICATION  指定不能在定阅效劳器上履行为复制创立的存储进程。.应用 FOR REPLICATION 选项创立的存储进程可用作存储进程挑选,且只能在复制进程中履行。本选项不能和 WITH RECOMPILE 选项一同应用。  AS  指定进程要履行的操纵。  sql_statement  进程中要包括的恣意数量和范例的 Transact-SQL 语句。但有一些限度。  n  是表现此进程能够包括多条 Transact-SQL 语句的占位符。  解释  存储进程的最大巨细为 128 MB。  用户界说的存储进程只能在以后数据库中创立(常设进程除外,常设进程老是在 tempdb 中创立)。在单个批处置中,CREATE PROCEDURE 语句不能与别的 Transact-SQL 语句组合应用。  默许情形下,参数可为空。假如通报 NULL 参数值而且该参数在 CREATE 或 ALTER TABLE 语句中应用,而该语句中援用的列又不同意应用 NULL,则 SQL Server 会发生一条过错信息。为了避免向不同意应用 NULL 的传记递 NULL 参数值,应向进程中增加编程逻辑或为该列应用默许值(应用 CREATE 或 ALTER TABLE 的 DEFAULT 要害字)。  倡议在存储进程的任何 CREATE TABLE 或 ALTER TABLE 语句中都为每列显式指定 NULL 或 NOT NULL,比方在创立常设表时。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项操纵 SQL Server 为列指派 NULL 或 NOT NULL 特征的方法(假如在 CREATE TABLE 或 ALTER TABLE 语句中没有指定的话)。假如某个衔接履行的存储进程对这些选项的设置与创立该进程的衔接的设置差别,则为第二个衔接创立的表列能够会有差别的为空性,而且表示出差别的行动方法。假如为每个列显式申明了 NULL 或 NOT NULL,那末将对全部履行该存储进程的衔接应用雷同的为空性创立常设表。  在创立或变动存储进程时,SQL Server 将保留 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。履行存储进程时,将应用这些原始设置。因而,全部客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在履行存储进程时都将被疏忽。在存储进程中呈现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储进程的功效。  别的 SET 选项(比方 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS)在创立或变动存储进程时不保留。假如存储进程的逻辑取决于特定的设置,应在进程扫尾增加一条 SET 语句,以确保设置准确。从存储进程中履行 SET 语句时,该设置只在存储进程实现之前无效。以后,设置将规复为挪用存储进程时的值。这使一般的客户端能够设置所需的选项,而不会影响存储进程的逻辑。  阐明 SQL Server 是将空字符串说明为单个空格仍是说明为真正的空字符串,由兼容级别设置操纵。假如兼容级别小于或即是 65,SQL Server 就将空字符串说明为单个空格。假如兼容级别即是 70,则 SQL Server 将空字符串说明为空字符串。无关更多信息,请拜见 sp_dbcmptlevel。  取得无关存储进程的信息  若要表现用来创立进程的文本,请在进程地点的数据库中履行 sp_helptext,并应用进程名作为参数。  阐明 应用 ENCRYPTION 选项创立的存储进程不能应用 sp_helptext 检查。  若要表现无关进程援用的工具的报表,请应用 sp_depends。  若要为进程重定名,请应用 sp_rename。  援用工具  SQL Server 同意创立的存储进程援用尚不存在的工具。在创立时,只停止语法检讨。履行时,假如高速缓存中尚无无效的打算,则编译存储进程以天生履行打算。只要在编译进程中才剖析存储进程中援用的全部工具。因而,假如语法准确的存储进程援用了不存在的工具,则仍能够胜利创立,但在运转时将失利,由于所援用的工具不存在。无关更多信息,请拜见耽误称号剖析和编译。  耽误称号剖析和兼容级别  SQL Server 同意 Transact-SQL 存储进程在创立时援用不存在的表。这类才能称为耽误称号剖析。不外,假如 Transact-SQL 存储进程援用了该存储进程中界说的表,而兼容级别设置(经过履行 sp_dbcmptlevel 来设置)为 65,则在创立时会收回忠告信息。而假如在运转时所援用的表不存在,将前往过错信息。无关更多信息,请拜见 sp_dbcmptlevel 和耽误称号剖析和编译。  履行存储进程  胜利履行 CREATE PROCEDURE 语句后,进程称号将存储在 sysobjects 体系表中,而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中。第一次履行时,将编译该进程以断定检索数据的最好拜访打算。  应用 cursor 数据范例的参数  存储进程只能将 cursor 数据范例用于 OUTPUT 参数。假如为某个参数指定了 cursor 数据范例,也必需指定 VARYING 和 OUTPUT 参数。假如为某个参数指定了 VARYING 要害字,则数据范例必需是 cursor,而且必需指定 OUTPUT 要害字。  阐明 cursor 数据范例不能经过数据库 API(比方 OLE DB、ODBC、ADO 和 DB-Library)绑定到利用顺序变量上。由于必需先绑定 OUTPUT 参数,利用顺序才能够履行存储进程,以是带有 cursor OUTPUT 参数的存储进程不能经过数据库 API 挪用。只要将 cursor OUTPUT 变量赋值给 Transact-SQL 部分 cursor 变量时,才能够经过 Transact-SQL 批处置、存储进程或触发器挪用这些进程。  Cursor 输入参数  在履行进程时,以下规矩实用于 cursor 输入参数:  关于只进游标,游标的成果会合前往的行只是那些存储进程履行停止时处于或超越游标地位的行,比方:   在进程中的名为 RS 的 100 行成果集上翻开一个非转动游标。  进程提取成果集 RS 的头 5 行。  进程前往到其挪用者。  前往到挪用者的成果集 RS 由 RS 的第 6 到 100 行构成,挪用者中的游标处于 RS 的第一行之前。   关于只进游标,假如存储进程实现后,游标位于第一行的后面,则全部成果集将前往给挪用批处置、存储进程或触发器。前往时,游标将位于第一行的后面。  关于只进游标,假如存储进程实现后,游标的地位超越最初一行的开头,则为挪用批处置、存储进程或触发器前往空成果集。  阐明 空成果集与空值差别。  关于可转动游标,在存储进程履行停止时,成果会合的全部行均会前往给挪用批处置、存储进程或触发器。前往时,游标保存在进程中最初一次履行提取时的地位。  关于恣意范例的游标,假如游标封闭,则将空值通报回挪用批处置、存储进程或触发器。假如将游标指派给一个参数,但该游标从未翻开过,也会呈现这类情形。  阐明 封闭状况只要在前往时才有影响。比方,能够在进程中封闭游标,稍后再翻开游标,而后将该游标的成果集前往给挪用批处置、存储进程或触发器。  常设存储进程  SQL Server 支撑两种常设进程:部分常设进程和全局常设进程。部分常设进程只能由创立该进程的衔接应用。全局常设进程则可由全部衔接应用。部分常设进程在以后会话停止时主动撤除。全局常设进程在应用该进程的最初一个会话停止时撤除。平日是在创立该进程的会话停止时。  常设进程用 # 和 ## 定名,能够由任何用户创立。创立进程后,部分进程的全部者是独一能够应用该进程的用户。履行部分常设进程的权限不能授与其余用户。假如创立了全局常设进程,则全部用户都可以拜访该进程,权限不能显式废止。只要在 tempdb 数据库中存在显式 CREATE PROCEDURE 权限的用户,才能够在该数据库中显式创立常设进程(不应用编号符定名)。能够授与或废止这些进程中的权限。  阐明 频仍应用常设存储进程会在 tempdb 中的体系表上发生争用,从而对机能发生负面影响。倡议应用 sp_executesql 取代。sp_executesql 不在体系表中存储数据,因而能够幸免这一成绩。  主动履行存储进程  SQL Server 启动时能够主动履行一个或多个存储进程。这些存储进程必需由体系治理员创立,并在 sysadmin 牢固效劳器脚色下作为后盾进程履行。这些进程不能有任何输出参数。  对启动进程的数量没无限制,然而要留神,每个启动进程在履行时都市占用一个衔接。假如必需在启动时履行多个进程,但不须要并行履行,则能够指定一个进程作为启动进程,让该进程挪用别的进程。如许就只占用一个衔接。  在启动时规复了最初一个数据库后,即开端履行存储进程。若要跳过这些存储进程的履行,请将启动参数指定为跟踪标志 4022。假如以最低设置启动 SQL Server(应用 -f 标志),则启动存储进程也不会履行。无关更多信息,请拜见跟踪标志。  若要创立启动存储进程,必需作为 sysadmin 牢固效劳器脚色的成员登录,并在 master 数据库中创立存储进程。  应用 sp_procoption 能够:  将现有存储进程指定为启动进程。  结束在 SQL Server 启动时履行进程。  检查 SQL Server 启动时履行的全部进程的列表。   存储进程嵌套  存储进程能够嵌套,即一个存储进程能够挪用另一个存储进程。在被挪用进程开端履行时,嵌套级将增添,在被挪用进程履行停止后,嵌套级将增加。假如超越最大的嵌套级,会使全部挪用进程链失利。可用 @@NESTLEVEL 函数前往以后的嵌套级。  若要估量编译后的存储进程巨细,请应用以下机能监督计数器。  机能监督器工具名 机能监督计数器称号   SQLServer:缓冲区治理器 高速缓存巨细(页面数)   SQLServer:高速缓存治理器 高速缓存掷中率   高速缓存页   高速缓存工具计数*  * 种种分类的高速缓存工具都可以应用这些计数器,包含特别 sql、预备 sql、进程、触发器等。  无关更多信息,请拜见 SQL Server:Buffer Manager 工具和 SQL Server:Cache Manager 工具。  sql_statement 限度  除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外(这两个语句必需是批处置中唯一的语句),任何 SET 语句都可以在存储进程外部指定。所抉择的 SET 选项在存储进程履行进程中无效,以后规复为本来的设置。  假如其余用户要应用某个存储进程,那末在该存储进程外部,一些语句应用的工具名必需应用工具全部者的称号限制。这些语句包含:  ALTER TABLE  CREATE INDEX  CREATE TABLE  全部 DBCC 语句  DROP TABLE  DROP INDEX  TRUNCATE TABLE  UPDATE STATISTICS   权限  CREATE PROCEDURE 的权限默许授与 sysadmin 牢固效劳器脚色成员和 db_owner 和 db_ddladmin 牢固数据库脚色成员。sysadmin 牢固效劳器脚色成员和 db_owner 牢固数据库脚色成员能够将 CREATE PROCEDURE 权限让渡给其余用户。履行存储进程的权限授与进程的全部者,该全部者能够为别的数据库用户设置履行权限。  示例  A. 应用带有庞杂 SELECT 语句的简略进程  上面的存储进程从四个表的连接中前往全部作者(供给了姓名)、出书的册本以及出书社。该存储进程不应用任何参数。  USE pubs  IF EXISTS (SELECT name FROM sysobjects   WHERE name = 'au_info_all' AND type = 'P')  DROP PROCEDURE au_info_all  GO  CREATE PROCEDURE au_info_all  AS  SELECT au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  GO  au_info_all 存储进程能够经过以下方式履行:  EXECUTE au_info_all  -- Or  EXEC au_info_all  假如该进程是批处置中的第一条语句,则可应用:  au_info_all  B. 应用带有参数的简略进程  上面的存储进程从四个表的连接中只前往指定的作者(供给了姓名)、出书的册本以及出书社。该存储进程接收与通报的参数准确婚配的值。  USE pubs  IF EXISTS (SELECT name FROM sysobjects   WHERE name = 'au_info' AND type = 'P')  DROP PROCEDURE au_info  GO  USE pubs  GO  CREATE PROCEDURE au_info   @lastname varchar(40),   @firstname varchar(20)   AS   SELECT au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  WHERE au_fname = @firstname  AND au_lname = @lastname  GO  au_info 存储进程能够经过以下方式履行:  EXECUTE au_info 'Dull', 'Ann'  -- Or  EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'  -- Or  EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'  -- Or  EXEC au_info 'Dull', 'Ann'  -- Or  EXEC au_info @lastname = 'Dull', @firstname = 'Ann'  -- Or  EXEC au_info @firstname = 'Ann', @lastname = 'Dull'  假如该进程是批处置中的第一条语句,则可应用:  au_info 'Dull', 'Ann'  -- Or  au_info @lastname = 'Dull', @firstname = 'Ann'  -- Or  au_info @firstname = 'Ann', @lastname = 'Dull'  C. 应用带有通配符参数的简略进程  上面的存储进程从四个表的连接中只前往指定的作者(供给了姓名)、出书的册本以及出书社。该存储进程对通报的参数停止形式婚配,假如没有供给参数,则应用预设的默许值。  USE pubs  IF EXISTS (SELECT name FROM sysobjects   WHERE name = 'au_info2' AND type = 'P')  DROP PROCEDURE au_info2  GO  USE pubs  GO  CREATE PROCEDURE au_info2  @lastname varchar(30) = 'D*',  @firstname varchar(18) = '*'  AS   SELECT au_lname, au_fname, title, pub_name  FROM authors a INNER JOIN titleauthor ta  ON a.au_id = ta.au_id INNER JOIN titles t  ON t.title_id = ta.title_id INNER JOIN publishers p  ON t.pub_id = p.pub_id  WHERE au_fname LIKE @firstname  AND au_lname LIKE @lastname  GO  au_info2 存储进程能够用多种组合履行。上面只列出了局部组合:  EXECUTE au_info2  -- Or  EXECUTE au_info2 'Wh*'  -- Or  EXECUTE au_info2 @firstname = 'A*'  -- Or  EXECUTE au_info2 '[CK]ars[OE]n'  -- Or  EXECUTE au_info2 'Hunter', 'Sheryl'  -- Or  EXECUTE au_info2 'H*', 'S*'  D. 应用 OUTPUT 参数  OUTPUT 参数同意内部进程、批处置或多条 Transact-SQL 语句拜访在进程履行时期设置的某个值。上面的示例创立一个存储进程 (titles_sum),并应用一个可选的输出参数和一个输入参数。 12全文浏览

上一篇:用共享游标提升 MSSQL 性能-网络编程

下一篇:没有了