sql 游标、存储过程
--新建视图 v_desk
create view v_desk
as
select
a.*,
b.usingType_code,
b.sOrderNo
from t_desk a
left join t_using_desk b on a.id = b.desk_id
go
--新建台号时向 t_using_desk 插入记录
--台号显示的时候查 v_desk
--去掉表 t_dish_orderedNumber
--t_order有些字段改为float
--t_order_temp增加 price float字段
--t_using_desk增加 sOrderNo nvarchar(20) 字段
--t_dishes 修改 ordered_number float
-----------------------------
--发送过程：先选台号，若该台号处于正在使用状态（在表 t_using_desk 中 usingType_code = '02'），
--则表明是加菜，从 t_using_desk 中获取该台号对应的 sOrderNo，传入存储过程 sp_orderDishes；(在 sp_orderDishes 中将有菜品的台号的 singType_code 置为‘02’，在结账时重新置为 ‘01’)
--若该台号处于空闲状态(在表 t_using_desk 中 usingType_code = '01')，则表明该台号即将被使用，需生成 sOrderNo， 向 t_order 表中增加该订单的信息，然后执行存储过程 sp_orderDishes
-------------------------
--创建存储过程 sp_orderDishes
--1、将临时表 t_order_temp 中的数据插入到 t_order_dishes
--2、将表 t_using_desk 中对应的 desk_id 的 usingType_code 置为 '02'（使用中）
--3、更新 t_dishes 的 ordered_number
--4、删除临时表 t_order_temp 中该 userId 对应的数据
--drop procedure sp_orderDishes
create procedure sp_orderDishes
@orderNo nvarchar(20),
@userId uniqueidentifier,
@deskId uniqueidentifier
as
begin transaction
insert into t_order_dishes(sOrderNo, dish_id, price, order_num, subtotal, taste_id1, taste_id2, remark, waiter)
select @orderNo, dish_id, price, order_num, price*order_num, taste_id1, taste_id2, remark, userId
from t_order_temp
where userId = @userId
IF @@ROWCOUNT > 0
update t_using_desk set usingType_code = '02', sOrderNo = @orderNo where desk_id = @deskId
if @@error 0
begin
rollback transaction--发生错误则回滚事务，无条件退出l
return
end
DECLARE @DishId uniqueidentifier
declare @OrderNum float
DECLARE My_Cursor CURSOR--定义游标
FOR (SELECT dish_id, order_num FROM t_order_temp where userId = @userId) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @DishId, @OrderNum; --读取第一行数据(将 t_order_temp 表中的 dish_id 放到 @DishId 变量中)
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE t_dishes SET ordered_number = ordered_number + @OrderNum WH
sql分页存储过程
sql分页存储过程
CREATE PROCEDURE [dbo].[dbTab_PagerHelper]
@TableName VARCHAR(50), --表名
@FieldNames VARCHAR(1000), --显示列名，如果是全部字段则为*
@WhereString VARCHAR(256) = NULL, --查询条件 不含'WHERE'字符，如[id]>5 AND [userid]>10000
@OrderField VARCHAR(256) = NULL, --排序不含'ORDER BY'字符，当@SortType=3时生效，必须指定ASC或DESC，建议在最后加上主键
@OrderType TINYINT, --排序规则（1:单列正序ASC；2:单列倒序DESC；3:多列排序；）
@PageIndex INT, --当前页数
@PageSize INT, --每页输出的记录数
@RecorderCount INT = 0 --记录总数，如果小于等于0则重新统计总数
AS
使用实例： exec dbTab_PagerHelper 'a_proxy','*','','customer_name',1,1,15,12
自己用的sql语句
自己用的，对别人无用
create table t_classify_method
(
id uniqueidentifier primary key default newid(),
name_ch nvarchar(20),
naem_en nvarchar(60),
d_code nvarchar(2)
)
go
insert into t_classify_method(id, name_ch, name_en, d_code) values ('2F71F378-6D5F-473C-A43C-B34216532E5D', '按蒸制方式分', 'By cooking method', '10')
go
insert into t_classify_method(id, name_ch, name_en, d_code) values ('5A089703-DD0F-4189-ADBD-51661C5A49B2', '按地域菜系分', 'By Cuisine ', '20')
go
create table t_dish_type
(
id uniqueidentifier primary key default newid(),
name_ch nvarchar(20),
naem_en nvarchar(50),
d_code nvarchar(2),
classify_code nvarchar(2),
item_index int,
deleted_flag bit default 0
)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('44A09637-51AF-4D7C-94A9-4346D41C8D53', '特色菜类', 'Special dishes', '01', '10', 1)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('67BDF3CE-3C58-48D9-B10D-169082D52CD5', '炒菜类', 'Stir-fries', '02', '10', 2)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('81165D4A-A49D-4008-9C74-4EC27609E9DA', '火锅类', 'Chafing dish', '03', '10', 3)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('78DC1E62-42D7-4B4D-8069-3E1500126492', '汤菜类', 'Soup', '04', '10', 4)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('9F59125C-6896-4BE8-8E7C-941F6CF88C3F', '清蒸类', 'Steamed dishes', '05', '10', 5)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('B01C0007-310A-46A1-834A-745B28A37FA8', '主食类', 'Staple food', '06', '10', 6)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('2C53093C-5BA8-4D84-9151-90BD56C35C26', '酒水类', 'Drinks', '07', '10', 7)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('C3E21EBB-86B2-4D84-99A0-B1A28749456C', '鲁菜', 'Shandong Cuisine', '08', '20', 1)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('7BA617C6-05F9-4737-B699-8776D93817E4', '川菜', 'Sichuan Cuisine', '09', '20', 2)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('172EDB24-3729-40DA-AE0B-E1164DAE33B5', '粤菜', 'Guangdong Cuisine', '10', '20', 3)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('388A0F43-0BCE-4285-8290-757E7DD08926', '闽菜', 'Fujian Cuisine', '11', '20', 4)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('8B6D107E-4CFA-461F-BB76-FCA70D5A60BC', '苏菜', 'Jiangsu Cuisine', '12', '20', 5)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('95D50EF5-6230-46E6-BD9C-F7F7779E7602', '浙菜', 'Zhejiang Cuisine', '13', '20', 6)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('4A31EF34-46AF-4378-99AA-5E80F2B596E1', '湘菜', 'Hunan Cuisine', '14', '20', 7)
go
insert into t_dish_type(id, name_ch, name_en, d_code, classify_code, item_index) values ('358C5D85-FB36-4B56-BB10-16E62A14EB56', '徽菜', 'Anhui Cuisine', '15', '20', 8)
go
create table t_dish_unit
(
id uniqueidentifier primary key default newid(),
name_ch nvarchar(5),
naem_en nvarchar(20),
d_code nvarchar(2),
deleted_flag bit default 0
)
go
create table t_dishes
(
id uniqueidentifier primary key default newid(),
name_ch nvarchar(20),
naem_en nvarchar(50),
dishType_id1 uniqueidentifier,
dishType_id2 uniqueidentifier,
price nvarchar(12),
dishUnit_id uniqueidentifier,
ordered_number int,
dishMemo nvarchar(500),
dishImage image,
create_time time default getdate(),
companyNo nvarchar(5),
deleted_flag bit default 0
)
go
sql函数和事务
自己备用的，对别人没有用
CREATE FUNCTION SplitStr (@splitString varchar(8000), @separate varchar(10))
RETURNS @returnTable table(id int, col_Value varchar(50))
AS
BEGIN
declare @thisSplitStr varchar(50)
declare @thisSepIndex int
declare @lastSepIndex int
declare @i int
set @lastSepIndex = 0
set @i = 1
if Right(@splitString ,len(@separate)) <> @separate set @splitString = @splitString + @separate
set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex)
while @lastSepIndex <= @thisSepIndex
begin
set @thisSplitStr = SubString(@splitString ,@lastSepIndex,@thisSepIndex-@lastSepIndex)
set @lastSepIndex = @thisSepIndex + 1
set @thisSepIndex = CharIndex(@separate,@splitString ,@lastSepIndex)
insert into @returnTable values(@i, @thisSplitStr)
set @i = @i + 1
end
return
END
go
--drop procedure sp_add_userFunction
create procedure sp_add_userFunction
@functionList varchar(5000),
@userId varchar(50)
as
DECLARE @count INTEGER
DECLARE @index INTEGER
declare @functionId varchar(50)
set @count = (select count(*) from SplitStr(@functionList,','))
set @index = 0
begin transaction
delete from xt_user_function where user_id = @userId
if @@error <> 0
begin
rollback transaction--发生错误则回滚事务，无条件退出l
return
end
while @index<@count
begin
set @functionId = (select col_Value from SplitStr(@functionList,',') where id = @index + 1)
insert into xt_user_function(function_id, user_id) values (@functionId, @userId)
SET @index=@index+1
end
if @@error <> 0
begin
rollback transaction--发生错误则回滚事务，无条件退出l
return
end
commit transaction --两条语句都完成，提交事务
go
RSS订阅
653
1
