Sql Server和Oracle常用的SQL脚本

---

插入字段

//添加字段并附默认值
alter table GSPAuditEvent add ISPRECUT char(1) NOT NULL DEFAULT ('1')

alter table GSPAuditEvent add(ISPRECUT char(1) DEFAULT '1' NOT NULL)

//判断字段是否存在进行添加
IF NOT EXISTS(SELECT 1 FROM SYS.SYSCOLUMNS A WHERE A.ID = OBJECT_ID('GSPAUDITEVENT') AND A.NAME = 'ISPRECUT')
BEGIN
    ALTER TABLE GSPAUDITEVENT ADD ISPRECUT char(1) NOT NULL DEFAULT ('0')
END
GO
UPDATE GSPAUDITEVENT SET ISPRECUT='1' WHERE FunctionID='框架'

判断字段是否存在

IF NOT EXISTS(SELECT 1 FROM SYS.SYSCOLUMNS A WHERE A.ID = OBJECT_ID('GSPAUDITEVENT') AND A.NAME = 'ISPRECUT')
BEGIN
    ALTER TABLE GSPAUDITEVENT ADD ISPRECUT char(1) NOT NULL DEFAULT ('1')
END

DECLARE EXISTFLAG INT;
BEGIN
  SELECT COUNT(1) INTO EXISTFLAG1 FROM USER_TAB_COLS WHERE COLUMN_NAME='ISPRECUT ' AND TABLE_NAME='GSPAUDITEVENT ';
  IF EXISTFLAG = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE GSPAUDITEVENT ADD ISPRECUT char(1) DEFAULT (1) NOT NULL ';
  END IF;
END;

判断表是否存在

if object_id(N'GSPAuditEventCus',N'U') is null
select * into GSPAuditEventCus from GSPAuditEvent
GO

DECLARE
existFlag NUMBER;
BEGIN
SELECT COUNT(1) INTO existFlag FROM USER_TABLES WHERE TABLE_NAME = UPPER('GSPAUDITEVENTCUS') ;
IF existFlag = 0 THEN
EXECUTE IMMEDIATE
'create TABLE GSPAuditEventCus as select * from GSPAuditEvent';
END IF;
END;
GO