数据库相关之SQL Server(一)

1．1、高级查询语句
1、  TRUNCATE TABLE [table_name]

删除表中的所有行，而不记录单个行删除操作。

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

2、  SP_DATABASES

返回在 sysdatabases 系统表中列出的数据库。

sp_databases 在开放式数据库连接 (ODBC) 中没有等价的过程。

3、  SP_DATATYPE_INFO

    sp_datatype_info 等价于 ODBC 中的 SQLGetTypeInfo。返回结果按 DATA_TYPE 排序，再按数据类型映射为相应 ODBC SQL 数据类型的紧密程度进行排序。

4、  SP_COLUMNS

        返回当前环境中可查询的指定表或视图的列信息。

           Sp_columns [table_name]

              Or：sp_columns @table_name = @#[table_name]@#, @column_name = @#[column_name]@#

5、  SP_TABLES

    用法同上，不再雷述。
1．2、存储过程
       所有设计优良的 Microsoft® SQL Server™ 2000 应用程序都应当使用存储过程。不论是否将应用程序的业务逻辑写入存储过程都应如此。甚至连没有业务逻辑组件的标准 Transact-SQL 语句，在用参数打包成存储过程后也能获得性能收益。编译进存储过程的 Transact-SQL 语句在执行时可省去大量的处理。
1．2．1存储过程的设计规则
l         CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语句，存储过程中的任意地方都不能使用下列语句：

CREATE DEFAULT

CREATE TRIGGER

CREATE PROCEDURE

CREATE VIEW

CREATE RULE

 

l         存储过程中参数的最大数目为 2100。

l         可以在存储过程内引用临时表。

l         如果在存储过程内创建本地临时表，则该临时表仅为该存储过程而存在；退出该存储过程后，临时表即会消失。
1．2．2创建存储过程
示例：

CREATE PROCEDURE [PR_insert_casebrief_NEW]

@p_Casebrief_code as varchar(50),@p_Object_id as integer,@p_Spy_starttime as datetime,

@p_Spy_endtime as datetime,@p_Casebrief_id as integer out

as

insert into Case_brief_telecom(Casebrief_code,object_id,Spy_starttime,Spy_endtime,)

values(@p_Casebrief_code,@p_Object_id,@p_Spy_starttime,@p_Spy_endtime)

select @p_Casebrief_id = @@identity

GO

**** p_Casebrief_id为该表的主键，自动增长。

l         Microsoft® SQL Server™ 2000 存储过程以四种方式返回数据：

1、输出参数，既可以返回数据（整型值或字符值等），也可以返回游标变量（游标是可以逐行检索的结果集）。

2、返回代码，始终是整型值。

3、SELECT 语句的结果集，这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内。

4、可从存储过程外引用的全局游标。

l         当一个存储过程调用另一个存储过程时，存储过程就会嵌套。存储过程最多可以嵌套 32 级。被调用存储过程开始执行时，嵌套级数增加一级；被调用存储过程完成执行时，嵌套级数减少一级。试图超出 32 级的最高嵌套级数时，将导致整个存储过程调用链的失败。正在执行的存储过程的当前嵌套级数存储在 @@NESTLEVEL 函数中。

l         存储过程中使用游标：
 

CREATE procedure XG_Insert_XXX

as 

declare @object_id as int

declare @CaseBriefID as varchar(4)

declare @number_style as varchar(50)

declare @strNumberType2 as varchar(50)

declare @number_code as varchar(50)

declare @strSQL as varchar(1000)

declare @strSQL1 as varchar(255)

declare @brief_code as varchar(50)

declare @creator as varchar(50)

declare @BH as varchar(50)

 

    DECLARE OBJECT_CURSOR CURSOR

    FOR SELECT distinct object_id,CaseBrief_ID,casebrief_code,table_creator from case_XXX

 

    OPEN OBJECT_CURSOR

    FETCH NEXT FROM OBJECT_CURSOR INTO @object_id,@CaseBriefID,@brief_code,@creator

    while (@@FETCH_STATUS = 0)

    BEGIN

        SELECT @BH=FullCode from View_user_depID where LoginName=@#@#+@creator+@#@#

        print @BH

        DECLARE NUMBER_CURSOR CURSOR

        FOR SELECT distinct number_style,number_code from case_XXX where object_id=@object_id

 

        OPEN NUMBER_CURSOR

        FETCH NEXT FROM NUMBER_CURSOR INTO @number_style,@number_code

        while (@@FETCH_STATUS = 0)

        BEGIN          

            set @strSQL=@#insert into CASE_XXX(CASEBRIEF_ID,NUM_TYPE,NUMBER) values(@# + @CaseBriefID + @#,@#@#@#+ @number_style + @#@#@#,@#@#@# + @number_code + @#@#@#)@#

            exec(@strSQL)

            FETCH NEXT FROM NUMBER_CURSOR INTO @number_style,@number_code

        END

        CLOSE NUMBER_CURSOR

        DEALLOCATE NUMBER_CURSOR

        FETCH NEXT FROM OBJECT_CURSOR INTO @object_id,@CaseBriefID,@brief_code,@creator

    END

    CLOSE OBJECT_CURSOR

    DEALLOCATE OBJECT_CURSOR

GO

原文转自：http://www.ltesting.net