事务控制是一种能够把数据库的一组修改作为整体提交给数据库以保证数据的一致性和完整性的机制。如果其中有一个操作失败，则所有操作失败。
2.5 存储过程的使用
在程序设计中，系统主要的复杂的操作我都都是通过存储过程来实现的。
例如：使用库存调整操作时，我们用P_ADJUST_STORE来完成。
procedure Tadjust_store.okbuttonClick(Sender: TObject);
begin
if not damo.Database1.InTransaction then
damo.Database1.StartTransaction; 开始一个事务
with P_ADJUST_STORE do //P_ADJUST_STORE为存储过程
begin
Params[1].AsInteger:=strtoint(edit_id.Text);
Params[2].AsFloat:=strtofloat(edit_adjust.Text);
Params[3].AsInteger:= table1.Fields[0].AsInteger ;
Params[4].AsInteger:= person_id ;// person_id为全局变量人员ID
Prepare;
execproc;
end;
try
damo.Database1.Commit; 事务提交
except
begin
damo.Database1.Rollback; 事务回滚
showmessage('数据提交失败！ ');
end;
end;
以下是几种典型存储过程。
日处理存储过程：
CREATE PROCEDURE P_DIALY_DO
@dialy_date char(12),
@person_id smallint
AS
DECLARE @goods_id int
DECLARE @quantity float
DECLARE @amount money
DECLARE temp_cursor CURSOR FOR
SELECT A.GOODS_ID,SUM(A.QUANTITY),SUM(A.QUANTITY*B.COST_PRICE)
FROM POS_SALE A, GOODS_INFO B
WHERE convert(char(12),WORK_DATE,102)=@dialy_date
AND A.GOODS_ID=B.GOODS_ID GROUP BY A.GOODS_ID
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @goods_id, @quantity, @amount
WHILE (@@FETCH_STATUS=0)
BEGIN
if EXISTS(SELECT * FROM STORE_DETAIL WHERE GOODS_ID = @goods_id)
BEGIN
UPDATE STORE_DETAIL
SET QUANTITY=QUANTITY-@quantity , AMOUNT=AMOUNT-@amount
WHERE GOODS_ID = @goods_id
END
FETCH NEXT FROM temp_cursor INTO @goods_id,@quantity,@amount
END
INSERT DIALY_DAN
VALUES( @dialy_date,'已做',@person_id)
CLOSE temp_cursor
DEALLOCATE temp_cursor
查询每日商品销售汇总的存储过程：
CREATE PROCEDURE P_DIALY_SALE
AS
CREATE TABLE #temp_table
(
amount float,
work_date char(12),
sale_cost float,
gain float
)
INSERT INTO #temp_table
SELECT
a.amount,convert(char(12),a.work_date,102) AS work_date,
(c.cost_price*a.quantity) AS sale_cost,
(a.amount-c.cost_price*a.quantity) AS gain
FROM pos_sale a,small_type b,goods_info c
WHERE a.goods_id=c.goods_id
AND c.small_type=b.small_type_id
SELECT work_date, sum(amount) AS sum_amount , sum(sale_cost) AS sum_sale_cost , sum( gain ) AS sum_gain
FROM #temp_table
GROUP BY work_date, ORDER BY work_date
商品审核入库存储过程
CREATE PROCEDURE P_GOODS_ENTER
@enter_dan_id int
AS
DECLARE @goods_id int
DECLARE @quantity float
DECLARE @amount money
DECLARE temp_cursor CURSOR FOR
SELECT GOODS_ID,QUANTITY,AMOUNT FROM ENTER_GOODS_DETAIL
WHERE ENTER_DAN_ID=@enter_dan_id
OPEN temp_cursor
FETCH NEXT FROM temp_cursor INTO @goods_id,@quantity,@amount
WHILE (@@FETCH_STATUS=0)
BEGIN
if EXISTS(SELECT * FROM STORE_DETAIL WHERE GOODS_ID = @goods_id)
BEGIN
UPDATE STORE_DETAIL
SET QUANTITY=QUANTITY+@quantity ,
AMOUNT=AMOUNT+@amount
WHERE GOODS_ID = @goods_id
END
ELSE
BEGIN
INSERT STORE_DETAIL
VALUES(@goods_id,@quantity,@amount,getdate())
END
FETCH NEXT FROM temp_cursor INTO @goods_id,@quantity,@amount
END
CLOSE temp_cursor
DEALLOCATE temp_cursor
查询部门销售汇总的存储过程
CREATE PROCEDURE P_SEARCH_DEP_SALE
AS
CREATE TABLE #temp_table
(
large_type_id int,
dep_name char(10),
quantity float,
amount float,
work_date char(12),
sale_cost float,
gain float
)
INSERT INTO #temp_table
SELECT
b.large_type_id,b.name AS dep_name,
a.quantity,a.amount,convert(char(12),a.work_date,102) AS work_date,
(c.cost_price*a.quantity) AS sale_cost,
(a.amount-c.cost_price*a.quantity) AS gain
FORM pos_sale a,large_type b,goods_info c
WHERE a.goods_id=c.goods_id
AND c.large_type=b.large_type_id
SELECT work_date, large_type_id ,dep_name,
sum( quantity) AS sum_quantity , sum(amount) AS sum_amount , sum(sale_cost) AS sum_sale_cost , sum( gain ) AS sum_gain
FROM #temp_table
GROUP BY work_date, large_type_id ,dep_name ORDER BY work_date
查询商品分类汇总的存储过程
中小型超市管理系统规划(三)由毕业论文网（www.huoyuandh.com）会员上传。
