SQLSERVER SQL编程集合 (part 1)

/ 变量的使用

/**/ /*变量声明方法*/

( 1 )

DECLARE   @customer_count   INT ,  @total_sales   REAL ,  @best_sales   DATETIME

( 2 )

DECLARE   @customer_count   INT 、

 

/**/ /*变量赋值方法*/

( 1 )

SET   @customer_count   =   0

( 2 )

SELECT   @customer_count   =   0

( 3 )

DECLARE   @total_id    CHAR ( 6 )

SELECT   @total_id   =  stor_id  FROM  discounts

 

/ 函数

CREATE   FUNCTION  ufn_getDiscount( @stor_id   char ( 4 ))

RETURNS   REAL

BEGIN

RETURN  ( SELECT  discount  FROM  discounts  WHERE  stor_id  =   @stor_id )

END

 

SELECT  lowqty, dbo.ufn_getDiscount(stor_id) /**/ /*函数返回单值时在调用是必须加拥有者名字*/

FROM  discounts

WHERE  dbo.ufn_getDiscount(stor_id)  >   6

 

/**/ /*RETURNS 对返回表进行描述*/

CREATE   FUNCTION  ufn_getDiscountsInfo( @stor_id   char ( 4 ))

RETURNS   @info_table   TABLE

(

info_lowqty   INT ,

highqty       INT ,

discount      INT  

)

BEGIN

INSERT   @info_table   SELECT  Lowqty, highqty, discOunt  FROM  discounts

                   WHERE  stor_id  =   @stor_id

RETURN

END

 

SELECT  discount  FROM  ufn_getDiscountsInfo( 8042 )

 

 

 

存储过程

CREATE   PROCEDURE  usp_add_coder

@name          VARCHAR ( 30 ),

@address       VARCHAR ( 30 ),

@office        INT   =   1 ,   /**/ /*初值=1*/

@manager       INT   =   1

AS

INSERT   INTO  coder(name, address, office, manager)

VALUES  ( @name ,  @address ,  @office ,  @manager )

 

EXECUTE  usp_add_coder  ' ckcs ' ,  ' 福建 ' ,  1 ,  2

 

 

触发器

CREATE   TRIGGER  tri_update_order  ON  orders

FOR   UPDATE

AS

DECLARE   @rowcount   int

SET   @rowcount   =   @@rowcount

IF  ( UPDATE (EmployeeID)  OR   UPDATE (Freight))   /**/ /*只要有更新EmployeeID或Freight的值触发器将被触动*/

BEGIN

IF  ( @rowcount   >   1 )

BEGIN

ROLLBACK   TRANSACTION

RAISERROR ( ' 每次更新只能更新一条记录 ' ,  16 ,  2 )

END

ELSE   BEGIN

UPDATE  employee_2  SET  salary  =  salary  -  

  ( SELECT  Freight  FROM  DELETED  WHERE  (employee_2.emp_id  =  ( SELECT  EmployeeID  FROM  DELETED)))

END

END

 

CREATE   TRIGGER  tri_delete_employee  ON  employee

FOR   DELETE

AS

IF   @@rowcount   >   1    /**/ /*判断操作是否影响到两行*/

BEGIN  

ROLLBACK   TRANSACTION

RAISERROR ( ' 你不能同时删除两个雇员 ' ,  16 ,  2 )

END

ELSE   BEGIN

UPDATE  employee_1  SET  NAME  =   ' jinlp '  

   WHERE  emp_id  =  ( SELECT  employee_id  FROM  DELETED)

UPDATE  employee_2  SET  NAME  =   ' jinlp2 '

   WHERE  emp_id  =  ( SELECT  employee_id  FROM  DELETED) 

END

 

/**/ /*类型（1）@checking_balance和@loc_balance在存储过程里面定义*/

/**/ /*存储过程的定义*/

CREATE   PROCEDURE  usp_process_check

@account_number    INT ,

@check_number      INT ,

@check_amount      INT ,

@checking_balance     REAL  OUTPUT,

@loc_used             REAL  OUTPUT,

@loc_balance          REAL  OUTPUT

AS

SET   @loc_used   =   0    /**/ /*设置处始值*/

SELECT   @checking_balance   =  check_balance  FROM  customers   /**/ /*对@checking_balance进行赋值*/

                             WHERE  account_number  =   @account_number

SELECT   @loc_balance   =  ( SELECT  loc_balance  FROM  customers

                        WHERE  account_number  =   @account_number )

IF    @checking_balance   <   @check_amount

SET   @loc_used   =  ( @check_amount   -   @checking_balance )

SET   @checking_balance   =   @checking_balance   -   @check_amount   +   @loc_used

INSERT   INTO  checks 

    VALUES ( @account_number ,  @check_number ,  @check_amount )

UPDATE  customers  SET  loc_balance  =   @loc_balance , checking_balance  =   @checking_balance

    WHERE  account_number  =   @account_number

 

 

/ 建立数据库

CREATE   DATABASE  home_database

ON

(

NAME  =   ' MYhome_data ' ,

FILENAME  =   ' e:database backupMYhome_data.mdf ' ,

SIZE  =  10MB,

MAXSIZE  =  15MB,  /**/ /*或是使用unlimited*/

FILEGROWTH  =  1MB 

)

LOG   ON

(

NAME  =   ' MYhome_log ' ,

FILENAME  =   ' e:database backupMYhome_log.log ' ,

SIZE  =  3MB,

MAXSIZE  =  6MB,

FILEGROWTH  =  1MB

)

 

// 游标

 

DECLARE  cur_payroll_work_3 SCROLL  CURSOR

FOR   SELECT   *   FROM  orders   --  MS-SQL强制把 SCROLL 游标设为 READONLY

 

OPEN  cur_payroll_work_3   --  使用数据填充游标

 

FETCH   NEXT   FROM  cur_payroll_work_3  --  下一行

 

FETCH  PRIOR  FROM  cur_payroll_work_3   --  上一行

 

FETCH  FIRST  FROM  cur_payroll_work_3  --  第一行

 

FETCH  LAST  FROM  cur_payroll_work_3  --  最后一行

 

FETCH  RELATIVE  1   FROM  cur_payroll_work_3

 

FETCH  RELATIVE  - 1   FROM  cur_payroll_work_3

 

FETCH  ABSOLUTE  1   FROM  cur_payroll_work_3   -- 第1行

 

FETCH  ABSOLUTE  - 1   FROM  cur_payroll_work_3   -- 倒数第一行

 

-- CLOSE 将释放游标占有的资源，但是游标在当前会话中仍然存在。

CLOSE  cur_payroll_work_3

 

-- deallocate会消毁游标，执行它后游标不再存在。

DEALLOCATE  cur_payroll_work_3

 

--  MS-SQL强制把 SCROLL 游标设为 READONLY 可使用DYNAMIC游标实现可更改的游-- 标

DECLARE   cur_orders_4   CURSOR  DYNAMIC 

FOR   SELECT   *   FROM  orders

FOR   UPDATE   OF  ShipAddress, ShipVia

OPEN  cur_orders_4

 

FETCH  RELATIVE  1   FROM  cur_orders_4   -- 可回滚

 

DELETE   FROM  orders  WHERE   CURRENT   OF  cur_orders_4   -- 可更新

 

UPDATE  orders  SET  ShipAddress  =   ' 福建 '   WHERE   CURRENT   OF  cur_orders_4   -- 可更新

这是我在学习SQL编程时对一些常见的SQL语句的摘录和总结，希望对你有些帮助,主要是针对MS SQLSERVER编程.