oracle学习笔记——sql脚本范例

最新推荐文章于 2024-08-05 17:42:49 发布

qxc10086

最新推荐文章于 2024-08-05 17:42:49 发布

阅读量539

点赞数

分类专栏： oracal 文章标签： oracle

本文链接：https://blog.csdn.net/qxc10086/article/details/47905621

版权

oracal 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1、创建表的脚本范例
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’[DIMRoles]’) AND xtype = ‘U’)

BEGIN

CREATE TABLE [DIMRoles](

[Key] [int] NOT NULL,

[Name] [nvarchar](256) NOT NULL,

PRIMARY KEY CLUSTERED

(

[Key] ASC

) ON [PRIMARY],

UNIQUE NONCLUSTERED

(

[Name] ASC

) ON [PRIMARY]

END

注意事项：

A、一定要先判断表是否存在，不存在才创建，而不能存在删除原表进行新表创建，重复执行会导致客户数据丢失！

2、修改表范例
2.1增加字段，增加控制（索引，不能为空等等）
IF NOT exists(select * from syscolumns where id=object_id(‘DIMUsers’) and name=’Phone’)

BEGIN

ALTER TABLE dbo.DIMUsers ADD Phone NVARCHAR(20) NOT NULL DEFAULT(”)

END

注意事项：

A、一定要判断字段是否存在表，不存在才创建。

B、一定看清楚判断的字段和表是否一样，不要上面判断A表，下面修改B表。

2.2修改删除字段
修改：

IF exists(select * from syscolumns where id=object_id(‘DIMUsers’) and name=’Phone’)

BEGIN

ALTER TABLE dbo.DIMUsers ALTER COLUMN Phone NVARCHAR(50)

END

删除：

IF exists(select * from syscolumns where id=object_id(‘DIMUsers’) and name=’Phone’)

BEGIN

ALTER TABLE dbo.DIMUsers DROP COLUMN Phone

END

注意事项：

A、一定要判断表的字段存在，不存在的话不存在修改和删除的意义。

3、初始化表数据范例
注意事项：

1、切勿使用delete 你要插入的表没有加上条件。

2、如果自增长字段最好关闭自增长，然后删除先插入你要加入到这张表的数据，因为这个ID是固定的。

SET IDENTITY_INSERT DIMUsers ON

DELETE FROM DIMUsers WHERE [Key] IN(1,2,3,4);

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (1, N’sa’, N’123’, 0, N’系统管理员’, N’SA’, getdate());

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (2, N’administrator’, N”, 0, N’系统管理员’, N’ADMINISTRATOR’, getdate());

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (3, N’admin’, N’123’, 0, N’系统管理员’, N’ADMIN’, getdate());

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (4, N’public’, N”, 1, N’公共聊天室’, N’PUBLIC’, getdate());

SET IDENTITY_INSERT DIMUsers OFF

如果系统的主键不是自增长的最好办了，上面的例子是最难办的自增长例子。

4、修改函数、存储过程、视图、索引
注意事项：

A、因为这些东西重复更新的话可以删除原来的保证最新的就可以，所以只需要判断是否存在即可。

函数示例：

IF OBJECT_ID(‘FUN_GetChildList’) IS NOT NULL

DROP FUNCTION FUN_GetChildList

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[FUN_GetChildList] (

@Table Nvarchar(100),

@Value Nvarchar(100)

)

RETURNS @tResult TABLE (

– 定义结果表

RESULT Nvarchar(100)

)

BEGIN

– 子类型表、父类型表、子类型数

DECLARE @tChild TABLE(RESULT Nvarchar(100))

DECLARE @tFather TABLE(RESULT Nvarchar(100))

DECLARE @Count SmallInt

– 预先置传入的值到@tFather中

INSERT INTO @tFather

VALUES (@Value)

WHILE 1 = 1

BEGIN

– 循环中，先将@tFather中内容放到@tResult中；

INSERT INTO @tResult SELECT RESULT FROM @tFather

– 将@tChild清空，再取@tFather的所有子类型到@tChild中；

DELETE FROM @tChild

– 根据不同的表追加写到IF内部的语句

IF @Table ='DIMDEPT'

BEGIN

  INSERT INTO @tChild

  SELECT RESULT = did

  FROM DIMDEPT

  WHERE pdid IN (SELECT RESULT FROM @tFather)

END

– 当@tChild空时，退出；

  SELECT @Count = COUNT(*) FROM @tChild

  IF @Count = 0

  BREAK

– 将@tFather清空，并将@tChild中内容给@tFather。

  DELETE FROM @tFather

  INSERT INTO @tFather SELECT RESULT FROM @tChild

END

RETURN

END

存储过程示例：

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’[DIMAddFriend]’) AND OBJECTPROPERTY(id,N’IsProcedure’) = 1)

DROP PROC DIMAddFriend

create proc [dbo].[DIMAddFriend](@user nvarchar(256), @friend nvarchar(256))

begin

insert into DIMUserRelationship (HostKey,GuestKey,Relationship,RenewTime)

select host.[Key] as HostKey,guest.[Key] as GuestKey,0,getdate()

from DIMUsers host,DIMUsers guest

where

   (host.UpperName=upper(@user) or host.UpperName=upper(@friend)) and

   (guest.UpperName=upper(@friend) or guest.UpperName=upper(@user)) and

   host.[Key]<>guest.[Key]

end

视图示例：

由于视图判断没有办法所以版本统一，所以写了2个版本。

2005以上版本：

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID=OBJECT_ID(N’VB_AccountPayableDetail’) )

DROP VIEW VB_AccountPayableDetail

CREATE VIEW VB_AccountPayableDetail

SELECT

  A.COMPANY_ID,

  A.ACCOUNT_PAYABLE_ID,

  A.SNO,

  A.MATERIAL_ID,

  A.MATERIAL_NO,

  A.MATERIAL_NAME,

  A.MATERIAL_SPECIFICATION,

  A.CURRENCY_ID,

  EXCHANGE_RATE =  CAST(A.EXCHANGE_RATE AS NUMERIC(18,2)),

  A.UNIT_ID,

  QUANTITY = CAST(A.QUANTITY AS NUMERIC(18,2)),

  UNIT_PRICE = CAST(A.UNIT_PRICE AS NUMERIC(18,2)),

  TAX_RATE = CAST(A.TAX_RATE AS NUMERIC(18,2)),

  AMOUNT = CAST(A.AMOUNT AS NUMERIC(18,2)),

  A.REMARK,

  CURRENCY = B.NAME,

  UNIT = C.NAME

FROM ACCOUNT_PAYABLE_ITEM A

  LEFT JOIN S_CODELIB B

   ON A.COMPANY_ID = B.COMPANY_ID

   AND A.CURRENCY_ID = B.ID

   LEFT JOIN S_CODELIB C

   ON A.COMPANY_ID = C.COMPANY_ID

   AND A.UNIT_ID = C.ID

2000版本：

IF EXISTS (SELECT * FROM sysviews WHERE OBJECT_ID=OBJECT_ID(N’VB_AccountPayableDetail’) )

DROP VIEW VB_AccountPayableDetail

CREATE VIEW VB_AccountPayableDetail

SELECT

  A.COMPANY_ID,

  A.ACCOUNT_PAYABLE_ID,

  A.SNO,

  A.MATERIAL_ID,

  A.MATERIAL_NO,

  A.MATERIAL_NAME,

  A.MATERIAL_SPECIFICATION,

  A.CURRENCY_ID,

  EXCHANGE_RATE =  CAST(A.EXCHANGE_RATE AS NUMERIC(18,2)),

  A.UNIT_ID,

  QUANTITY = CAST(A.QUANTITY AS NUMERIC(18,2)),

  UNIT_PRICE = CAST(A.UNIT_PRICE AS NUMERIC(18,2)),

  TAX_RATE = CAST(A.TAX_RATE AS NUMERIC(18,2)),

  AMOUNT = CAST(A.AMOUNT AS NUMERIC(18,2)),

  A.REMARK,

  CURRENCY = B.NAME,

  UNIT = C.NAME

FROM ACCOUNT_PAYABLE_ITEM A

  LEFT JOIN S_CODELIB B

   ON A.COMPANY_ID = B.COMPANY_ID

   AND A.CURRENCY_ID = B.ID

   LEFT JOIN S_CODELIB C

   ON A.COMPANY_ID = C.COMPANY_ID

   AND A.UNIT_ID = C.ID

索引示例：

if NOT exists(select * from sysindexes where name=’IX_mis_AskForLeaveSheetFact_001’)

BEGIN

CREATE NONCLUSTERED INDEX [IX_mis_AskForLeaveSheetFact_001] ON [dbo].[mis_AskForLeaveSheetFact]

(