SQL Server:如何将代码解析为其不同的语句 [英] SQL Server: How to parse code into its different statements

查看：73
本文介绍了SQL Server:如何将代码解析为其不同的语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

作为 SQL Server:的可能解决方案我建议在每条语句后添加插入语句.

As a possible sotlution to SQL Server: How to find what lines are executed I proposed that I could add inserts statements after each statement.

将存储过程的代码拆分成不同的语句，以便在每个语句之后可以添加带有前一行的额外插入语句的有效方法是什么?如果完全相同的行出现了多次，则一旦在过程/函数/触发器内部，也需要使它们具有唯一性，并带有一些数字.

What would be an efficient way to split up code of a stored procedure into its different statements so that after each statement an extra insert statement with the previous line can be added? If the exact same line occurs more then once inside the procedure/function/trigger they also need to be made unique with some number.

不必考虑注释和样式.但是重要的是，必须遵循准确的执行流程

Comments and styling do not have to be taken into consideration. But it is important that exact execution flow can be followed

示例输入1:

/*******************************************************************************************
    description
    @param wioho
*******************************************************************************************/
CREATE PROC usp_Example1
    (
        @param VARCHAR(MAX),
        @param2 INT
    )
AS
BEGIN
    BEGIN TRY
        -- random comment
        INSERT INTO dept VALUES (@param, @param2)
        IF EXISTS (
                SELECT 1
                    FROM dept 
                    WHERE deptno = 10
            )
            THROW 50001, 'Dept 10 exists', 1
        ELSE
            INSERT INTO dept VALUES (@param, @param2)
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
END

预期的输出1(或功能上等效的):

Expected output 1 (or functionally equivelant):

/*******************************************************************************************
    description
    @param wioho
*******************************************************************************************/
CREATE PROC usp_Example1
    (
        @param VARCHAR(MAX),
        @param2 INT
    )
AS
BEGIN
    BEGIN TRY
        INSERT INTO coverageTrace VALUES ('usp_Example1', 'BEGIN TRY', 1)
        -- random comment
        INSERT INTO dept VALUES (@param, @param2)
        INSERT INTO coverageTrace VALUES ('usp_Example1', 'INSERT INTO dept VALUES (@param, @param2)', 1)
        IF EXISTS (
                SELECT 1
                    FROM dept 
                    WHERE deptno = 10
            )
            BEGIN
                INSERT INTO coverageTrace VALUES ('usp_Example1', 'IF EXISTS (SELECT 1 FROM dept WHERE deptno = 10)', 1)
                THROW 50001, 'Dept 10 exists', 1
            END
        ELSE IF 1 = 1
        BEGIN
            INSERT INTO dept VALUES (@param, @param2)
            INSERT INTO coverageTrace VALUES ('usp_Example1', 'INSERT INTO dept VALUES (@param, @param2)', 2)
        END
    END TRY
    BEGIN CATCH
        INSERT INTO coverageTrace VALUES ('usp_Example1', 'BEGIN CATCH', 1)
        THROW
    END CATCH
END

现在，如果某人未正确设置其代码样式，则该代码仍然可以正常工作. 输入示例2:

Now if someone does not properly style their code this should still work. Example input 2:

/*******************************************************************************************
    description @param wioho
*******************************************************************************************/
CREATE PROC usp_Example1(@param VARCHAR(MAX),@param2 INT) AS BEGIN
    BEGIN TRY-- random comment
INSERT INTO dept VALUES (@param, @param2) IF EXISTS (
                SELECT 1
                    FROM dept 
                    WHERE deptno = 10
            )
            THROW 50001, 'Dept 10 exists', 1 ELSE
            INSERT INTO dept VALUES (@param, @param2) END TRY BEGIN CATCH
        THROW
    END CATCH
END

这应该将(功能上)等价的代码赋予预期的输出1

This should give the (functionally) equivelant code to expected output 1

请注意，在使用block语句的情况下，此代码需要能够知道是否明确使用了BEGIN和END.因此，如果需要，代码可以显式添加它.

Please note that this code in case of a block statements needs to be able to know whether or not BEGIN and END where explicitly used. So the code can add it explicitly if it is needed.

是否有任何可重用的代码或我可以使用的正则表达式.如果可能的话，我想在SQL中执行此操作，以便我的变异测试框架可以是任何MS SQL Server上的一个文件.

Is there any code available that can be reused or perhaps regexes I can use. If possible I would like to do this within SQL so my mutation testing framework can be one file on any MS SQL Server.

请注意:这是一个测试框架，并且不是一个手动更改代码的选项，必须是自动完成的.

Please note that: this is a testing framework and manually changing code is not an option, this has to be done automaticly.

进度更新: 在@Jeroen Mostert评论之后，我开始尝试扩展事件系统.我仍然有几个问题要解决，如何正确过滤生成的XML ，以及如何只在数据库中进行跟踪而不对数据库名称进行硬编码?(由代码生成解决(不是发布，我需要在代内使用宽字符集))

Progress update: After @Jeroen Mostert comment I started experimenting with the extended event system. I still have a couple of problems to tackle, how do you properly filter the generated XML and how do you only trace in the database without hardcoding in the database name?(Fixed by code generation (didn't release I needed to use a wide character set inside generation))

当前代码:

    USE master
    GO

    DROP DATABASE IF EXISTS testMSSQLDB
    GO

    CREATE DATABASE testMSSQLDB
    GO

    USE testMSSQLDB
    GO

    CREATE TYPE ID FROM INT
    GO

    CREATE TABLE dept (
        deptno ID PRIMARY KEY
    )
    GO

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='testMSSQLTrace')  
       DROP EVENT SESSION testMSSQLTrace ON SERVER;  

    DECLARE @cmd VARCHAR(MAX) = '';
SELECT @cmd = 'CREATE EVENT SESSION testMSSQLTrace 
ON SERVER
    ADD EVENT sqlserver.module_end
    (SET collect_statement = (1)
        WHERE (sqlserver.database_name = N''' + DB_NAME() + ''')),
    --ADD EVENT sqlserver.rpc_completed
    --(WHERE (sqlserver.database_name = N''' + DB_NAME() + ''')),
    ADD EVENT sqlserver.sp_statement_completed
        (WHERE (sqlserver.database_name = N''' + DB_NAME() + ''')),
    --ADD EVENT sqlserver.sql_batch_completed
    --(WHERE (sqlserver.database_name = N''' + DB_NAME() + ''')),
    ADD EVENT sqlserver.sql_statement_completed
        (WHERE (sqlserver.database_name = N''' + DB_NAME() + '''))
    ADD TARGET package0.ring_buffer
        WITH (
            MAX_MEMORY = 2048 KB,
            -- EVENT_RETENTION_MODE = NO_EVENT_LOSS,
            MAX_DISPATCH_LATENCY = 3 SECONDS,
            MAX_EVENT_SIZE = 0 KB,
            MEMORY_PARTITION_MODE = NONE,
            TRACK_CAUSALITY = OFF,
            STARTUP_STATE = OFF
        );'

EXEC (@cmd)

    ALTER EVENT SESSION testMSSQLTrace
          ON SERVER
        STATE = STOP; 

    ALTER EVENT SESSION testMSSQLTrace
          ON SERVER
        STATE = START;  

    GO

    CREATE OR ALTER PROC usp_temp
        (
            @param INT = 10 
        )
    AS
    BEGIN
        IF @param = 10
        BEGIN
            DELETE dept
            INSERT INTO dept VALUES (@param)
            SELECT * FROM dept
        END
        ELSE
            DELETE dept
    END
    GO

    EXEC usp_temp
    EXEC usp_temp 20

    SELECT name, target_name, CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet  
    JOIN sys.dm_xe_sessions AS xe  
       ON (xe.address = xet.event_session_address)  
    WHERE xe.name = 'testMSSQLTrace'

这会生成(切掉一些部分):

This generates (cut out some parts):

<RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="12" eventCount="12" droppedCount="0" memoryUsed="2012">
<event name="sp_statement_completed" package="sqlserver" timestamp="2019-07-04T09:22:30.472Z">
    <data name="source_database_id">
      <type name="uint32" package="package0" />
      <value>22</value>
    </data>
    <data name="object_id">
      <type name="int32" package="package0" />
      <value>1916742081</value>
    </data>
    <data name="object_type">
      <type name="object_type" package="sqlserver" />
      <value>8272</value>
      <text>PROC</text>
    </data>
    <data name="duration">
      <type name="int64" package="package0" />
      <value>22</value>
    </data>
    <data name="cpu_time">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="physical_reads">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="logical_reads">
      <type name="uint64" package="package0" />
      <value>3</value>
    </data>
    <data name="writes">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="row_count">
      <type name="uint64" package="package0" />
      <value>1</value>
    </data>
    <data name="last_row_count">
      <type name="uint64" package="package0" />
      <value>1</value>
    </data>
    <data name="nest_level">
      <type name="uint16" package="package0" />
      <value>1</value>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>11</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>214</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>276</value>
    </data>
    <data name="object_name">
      <type name="unicode_string" package="package0" />
      <value />
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>INSERT INTO dept VALUES (@param)</value>
    </data>
  </event>
  <event name="sp_statement_completed" package="sqlserver" timestamp="2019-07-04T09:22:30.476Z">
    <data name="source_database_id">
      <type name="uint32" package="package0" />
      <value>22</value>
    </data>
    <data name="object_id">
      <type name="int32" package="package0" />
      <value>1916742081</value>
    </data>
    <data name="object_type">
      <type name="object_type" package="sqlserver" />
      <value>8272</value>
      <text>PROC</text>
    </data>
    <data name="duration">
      <type name="int64" package="package0" />
      <value>32</value>
    </data>
    <data name="cpu_time">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="physical_reads">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="logical_reads">
      <type name="uint64" package="package0" />
      <value>2</value>
    </data>
    <data name="writes">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="row_count">
      <type name="uint64" package="package0" />
      <value>1</value>
    </data>
    <data name="last_row_count">
      <type name="uint64" package="package0" />
      <value>1</value>
    </data>
    <data name="nest_level">
      <type name="uint16" package="package0" />
      <value>1</value>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>12</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>286</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>320</value>
    </data>
    <data name="object_name">
      <type name="unicode_string" package="package0" />
      <value />
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>SELECT * FROM dept</value>
    </data>
  </event>
</RingBufferTarget>

如何以仅保留执行语句的对象类型和对象ID保留的方式来过滤此XML?具体的信息需求是，我需要知道存储过程在执行的哪几行，一个存储过程可以调用其他存储过程，在这种情况下，我仍然需要知道该过程执行了哪些语句，并将其嵌套在第一个存储过程中程序.而且，如果同一条语句多次出现，我现在需要其(相对)行号

How can I filter this XML in a way that only the executed statement the object type and object id from where it was executed remain? The concrete information need is that I need to know what lines of a stored procedure where executed, a stored procedure can call other stored procedure, in this case I still need to know what statements the procedure executed and that it was nested in the first stored procedure. And if the same statement occurs multiple times I need to now its (relative) line number

或在谓词中: 顶级存储过程 Y 中的过程 X 执行行号为 J

Or in predicates: Procedure X in the toplevel stored procedure Y executed line Z with linenumber J

过程 X 执行的行号 I

我做了更多研究，得出的结论是，我需要所有具有<data name="nest_level"><value>2</value></data>字段的事件.其中2是大于1的任何值.

I did some more research and I concluded that I need all events that have a <data name="nest_level"><value>2</value></data> field. Where the 2 is any value greater than 1.

此 https://www.scarydba.com/2018/09/24/extended-events-and-stored-procedure-parameter-values/链接对我获取所有数据很有帮助.

This https://www.scarydba.com/2018/09/24/extended-events-and-stored-procedure-parameter-values/ link proved to be helpful for me to get all the data.

推荐答案

因此扩展事件就是解决方案，这就是我的方法:

So the extended events are the solution, this is how I have done it:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='testMSSQLTrace')  
   DROP EVENT SESSION testMSSQLTrace ON SERVER;  

DECLARE @cmd VARCHAR(MAX) = '';
SELECT @cmd = 'CREATE EVENT SESSION testMSSQLTrace 
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
        (WHERE (sqlserver.database_name = N''' + DB_NAME() + '''))
    ADD TARGET package0.ring_buffer
        WITH (
            MAX_MEMORY = 2048 KB,
            EVENT_RETENTION_MODE = NO_EVENT_LOSS,
            MAX_DISPATCH_LATENCY = 3 SECONDS,
            MAX_EVENT_SIZE = 0 KB,
            MEMORY_PARTITION_MODE = NONE,
            TRACK_CAUSALITY = OFF,
            STARTUP_STATE = OFF
        );'

EXEC (@cmd)

这将创建一个事件，该事件可在每次语句完成后触发，该事件是动态完成的，可对数据库进行过滤

This creates an event that can be fired after every statement completion, this is done dynamicly to filter on the database

然后我有3个过程使控制此事件变得容易

Then I have 3 procedures that make controlling this event easy

/*******************************************************************************************
    Starts the statement trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StartTrace
AS
BEGIN 
    ALTER EVENT SESSION testMSSQLTrace
          ON SERVER
        STATE = START; 
END
GO

/*******************************************************************************************
    Ends the statement trace, this also clears the trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StopTrace
AS
BEGIN
    ALTER EVENT SESSION testMSSQLTrace
          ON SERVER
        STATE = STOP; 
END
GO


/*******************************************************************************************
    Saves the statements trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_SaveTrace
AS
BEGIN
    DECLARE @xml XML;

    SELECT @xml = CAST(xet.target_data AS xml)
        FROM sys.dm_xe_session_targets AS xet INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)  
        WHERE xe.name = 'testMSSQLTrace'  

    INSERT INTO testMSSQL.StatementInvocations (testProcedure, procedureName, lineNumber, statement)
        SELECT testMSSQL.GetCurrentTest(), 
            OBJECT_NAME(T.c.value('(data[@name="object_id"]/value)[1]', 'int')),
            T.c.value('(data[@name="line_number"]/value)[1]', 'int'), 
            T.c.value('(data[@name="statement"]/value)[1]', 'VARCHAR(900)')
        FROM @xml.nodes('RingBufferTarget/event') T(c)
        WHERE T.c.value('(data[@name="nest_level"]/value)[1]', 'int') > 3

END
GO

这些过程分别开始和停止跟踪，最后一个将结果存储在一个表中，该表在嵌套级别进行过滤，因此不会跟踪我自己的代码.

These procedures respectivly start and stop the trace and the last one stores the result in a table where it filters on the nest level so my own code is not traced.

最后我会像这样使用它:

Finally I use it a bit like this:

start trace
start tran/savepoint
run SetUp (users code)
run test (users code)
save trace
save trace to variable
rollback tran (also catch errors and stuff like that)
save variable back to table so the trace is not rolled back

这篇关于SQL Server:如何将代码解析为其不同的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆