/
变量的使用
/*变量声明方法*/
( 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 -- 可更新
/*变量声明方法*/
( 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 -- 可更新