2015年10月

首页2015年10月
15
Oct
0

常用多次执行SQL的创建方法

SQL创建修改规范

--1.添加表字段
--如果不存在该字段,则进行添加。
IF NOT EXISTS(
SELECT *

FROM syscolumns 
WHERE id=object_id('T_CONSUME') and Name='F_TICKETMONEY')

BEGIN

    ALTER TABLE T_CONSUME ADD F_TICKETMONEY decimal(9,2) NOT NULL DEFAULT 0 

END

--2.修改字段
--如果存在该字段,则进行修改。
IF EXISTS(
SELECT *

FROM syscolumns 
WHERE id=object_id('T_CHECKOUT_H') and Name='F_RECEIVEID')

BEGIN
ALTER TABLE T_CHECKOUT_H alter COLUMN F_RECEIVEID varchar(200)
END

--3.创建添加索引
--如果存在则进行删除
IF EXISTS (SELECT * FROM SYSINDEXES
WHERE NAME='IDX_WORKER_CARDID)')
DROP INDEX T_WORKER.IDX_WORKER_CARDID--如果存在则删除
GO

--创建索引
CREATE NONCLUSTERED INDEX [IDX_WORKER_CARDID] ON [dbo].[T_WORKER]
(

    [F_CARDID] ASC
    

)WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

---4.创建表
IF OBJECT_ID (N'T_SQLUPDATELOG') IS NULL
BEGIN --如果不存在该表,则进行创建
CREATE TABLE T_SQLUPDATELOG(

    [F_VERSION] [varchar](50) NOT NULL,
    [F_DATETIME] [datetime] NOT NULL,
    [F_SHOPID] [varchar](10) NULL,
    [F_STATE] [smallint] NOT NULL,
    [F_ERRORMSG] [varchar](max) NULL,
    [F_REMARK] [varchar](200) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

--5.删除字段
--如果存在该字段,则进行删除
IF EXISTS(
SELECT *

FROM syscolumns 
WHERE id=object_id('F_WORKERID') and Name='T_WORKER')

BEGIN
ALTER TABLE T_WORKER DROP COLUMN F_WORKERID
END

--6.创建或修改视图
--如果存在视图,则进行删除
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_CARDTYPEBYTIME')
BEGIN
DROP View V_CARDTYPEBYTIME;
END

GO
--创建视图
CREATE VIEW V_CARDTYPEBYTIME AS
SELECT

    T_CARDTYPEBYTIME.F_AUTOID,
    T_CARDTYPEBYTIME.F_CARDTYPEID,
    T_CARDTYPEBYTIME.F_GOODSID,
    T_GOODS.F_NAME AS F_GOODSNAME,
    T_CARDTYPEBYTIME.F_PRICE,
    T_CARDTYPEBYTIME.F_AMOUNT,
    T_CARDTYPEBYTIME.F_REMARK

FROM

    T_GOODS

INNER JOIN T_CARDTYPEBYTIME ON T_GOODS.F_ID = T_CARDTYPEBYTIME.F_GOODSID

--7.创建或修改触发器
--如果存在触发器,则进行删除
if exists(select name from sysobjects where name='CLIENTPRICE_ADD')
BEGIN
DROP TRIGGER [dbo].[CLIENTPRICE_ADD]
END
GO

Create TRIGGER [dbo].[CLIENTPRICE_ADD] ON [dbo].[T_CLIENTPRICE]
FOR INSERT
AS
BEGIN
DECLARE @FNEWID VARCHAR(20);
SELECT @FNEWID = F_AUTOID FROM inserted;
DECLARE @FNEWSHOPID VARCHAR(20);
SELECT @FNEWSHOPID = f_shopid FROM inserted;
EXECUTE UPDATETABLEINDEX 'T_CLIENTPRICE','F_AUTOID', @FNEWID, NULL,'ADD',@FNEWSHOPID,NULL;
END

GO

if exists(select name from sysobjects where name='CLIENTPRICE_DELETE')
BEGIN
DROP TRIGGER [dbo].[CLIENTPRICE_DELETE]
END
GO

Create TRIGGER [dbo].[CLIENTPRICE_DELETE] ON [dbo].[T_CLIENTPRICE]
FOR DELETE
AS
BEGIN
DECLARE @FOLDID VARCHAR(20);
SELECT @FOLDID = F_AUTOID FROM deleted;
DECLARE @FOLDSHOPID VARCHAR(20);
SELECT @FOLDSHOPID = f_shopid FROM deleted;
EXECUTE UPDATETABLEINDEX 'T_CLIENTPRICE','F_AUTOID', @FOLDID, NULL,'DELETE',@FOLDSHOPID,NULL;
END

GO

if exists(select name from sysobjects where name='CLIENTPRICE_UPDATE')
BEGIN
DROP TRIGGER [dbo].[CLIENTPRICE_UPDATE]
END
GO
Create TRIGGER [dbo].[CLIENTPRICE_UPDATE] ON [dbo].[T_CLIENTPRICE]
FOR UPDATE
AS
BEGIN
DECLARE @FNEWID VARCHAR(20);
DECLARE @FOLDID VARCHAR(20);
SELECT @FNEWID = F_AUTOID FROM inserted;
SELECT @FOLDID = F_AUTOID FROM deleted;
DECLARE @FOLDSHOPID VARCHAR(20);
SELECT @FOLDSHOPID = f_shopid FROM deleted;
DECLARE @FNEWSHOPID VARCHAR(20);
SELECT @FNEWSHOPID = f_shopid FROM inserted;
EXECUTE UPDATETABLEINDEX 'T_CLIENTPRICE','F_AUTOID', @FNEWID,@FOLDID,'UPDATE',@FNEWSHOPID, @FOLDSHOPID;
END

GO
10.修改约束 (外键、唯一约束、主键)
--如果存在约束,则进行删除
IF EXISTS (select * from sysobjects WHERE NAME='UNQ_呼叫编码唯一')
ALTER TABLE T_CALL DROP CONSTRAINT [UNQ_呼叫编码唯一]; --如果存在则删除
GO

ALTER TABLE T_CALL ADD CONSTRAINT [UNQ_呼叫编码唯一] UNIQUE ([F_TYPE] ASC, [F_SHOPID] ASC, [F_CALLBELLADRESS] ASC)

GO

11.修改创建函数
IF EXISTS (select * from dbo.sysobjects where id =object_id(N'[dbo].[GETHANDBRANDSTATEANDCOSTTIME]') and xtype in (N'FN', N'IF', N'TF'))
BEGIN
drop function V_CONSUMENOTCHECKOUT;
END

GO

CREATE function [dbo].[GETHANDBRANDSTATEANDCOSTTIME] (

@shopid varchar(10),
@DefaultGoods varchar(100),
@DefaultTeaType varchar(100)
)

12.查询和创建唯一键

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T_SUPPLEMENTPAYMENT]') AND name = N'SupllementPayMentIndex')
CREATE unique index [SupllementPayMentIndex] on T_SUPPLEMENTPAYMENT(F_SUPPLEMENTAUTOID,F_TYPE);
GO

13.外键 主键 删除 创建
IF EXISTS (select * from sysobjects WHERE NAME='PK_欠款主键自动编号')
ALTER TABLE T_ARREARAGE DROP CONSTRAINT [PK_欠款主键自动编号];

IF not EXISTS (select * from sysobjects WHERE NAME='PK_欠款主键自动编号')
alter table T_ARREARAGE add constraint [PK_欠款主键自动编号] primary key (F_ID);

IF EXISTS (select * from sysobjects WHERE NAME='FK_充值员工外键充值单号')
ALTER TABLE T_SUPPLEMENTWORKER DROP CONSTRAINT [FK_充值员工外键充值单号];

IF not EXISTS (select * from sysobjects WHERE NAME='FK_充值按次项目外键充值')
alter table T_SUPPLEMENTBYTIME add constraint [FK_充值按次项目外键充值] foreign key (F_SUPPLEMENTID) references T_SUPPLEMENT (F_ID);

14.修改字段可以为NULL
alter table T_SUPPLEMENTWORKER_H alter column F_SUPPLEMENTID int null

13
Oct
0

简单说说Delphi中线程的释放

简单说说Delphi中线程的释放

delphiconstructor
线程的释放方式有两种:一种是线程在运行完成后自动释放,一种是手动释放。

无论是那种释放,都应该在线程停止后进行释放。

然而线程的停止也有两种情况:一种是不需要设置标志位,直接完成;一种是由于execute方法中做了循环,需要设置标志位才能停止。
如果线程已经停止并且自动释放,再去手动停止,就会报错。

下面看代码:
1、自动停止后自动释放的线程:

[delphi] view plaincopy
constructor TTestThread.Create;
begin
inherited Create( True );
FreeOnTerminate := True;
end;

procedure TTestThread.Execute;
begin

....//功能代码  

//此方法完成后线程就已经停止了  

end;
这种情况线程会自动释放,因此不要手动释放,否则会报错

2、手动停止后自动释放的线程:
[delphi] view plaincopy
constructor TTestThread.Create;
begin
inherited Create( True );
FreeOnTerminate := True;
end;

procedure TTestThread.Execute;
begin
while not Terminated do //not Terminated do
begin

....//功能代码  

end;
end;

procedure Test
begin

t1 := TTestThread.Create( Self );  
t1.Terminate;  

end;
3、手动释放的线程:
[delphi] view plaincopy
constructor TTestThread.Create;
begin
inherited Create( True );
end;

procedure TTestThread.Execute;
begin
while not Terminated do //not Terminated do
begin

....//功能代码  

end;
end;

procedure Test
begin

t1 := TTestThread.Create( Self );  
t1.Terminate;  
t1.WaitFor;  
t1.Free;  

end;
那么,何时使用自动释放的线程,何时使用手动释放的线程呢
我的建议是:

如果这个线程运行时间很短或者能保证在系统退出前完成运行,则可以选择自动释放,因为它可以很快自动释放掉

如果这个线程运行贯穿系统运行整个期间,则要选择手动释放了