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编程.