oracle学习笔记——sql脚本范例
来源:互联网 发布:mac 输入法 emoji表情 编辑:程序博客网 时间:2024/04/30 04:01
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]
) 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
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FUN_GetChildList] (
@Table Nvarchar(100),
@Value Nvarchar(100)
)
RETURNS @tResult TABLE (
– 定义结果表
RESULT Nvarchar(100)
)
AS
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
GO
create proc [dbo].[DIMAddFriend](@user nvarchar(256), @friend nvarchar(256))
as
begin
insert into DIMUserRelationship (HostKey,GuestKey,Relationship,RenewTime)select host.[Key] as HostKey,guest.[Key] as GuestKey,0,getdate()from DIMUsers host,DIMUsers guestwhere (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
go
CREATE VIEW VB_AccountPayableDetail
AS
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.NAMEFROM 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
go
CREATE VIEW VB_AccountPayableDetail
AS
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.NAMEFROM 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]
(
[begin_date] ASC,[begin_time] ASC
)
INCLUDE ( [Fact_leave_id],
[e_id],
[end_date],
[end_time],
[holidayNo]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
END
统计信息示例:
这个没有2000版本的只有2005版本
IF NOT EXISTS(SELECT NAME FROM sys.stats WHERE name=’pms_exam_emp_001’)
BEGIN
CREATE STATISTICS [pms_exam_emp_001] ON [dbo].[pms_exam_emp]([p_e_id], [e_type], [e_id])
END
这个统计信息是什么东东,这个玩意是跟索引配合的,在进行数据优化的时候很有用处。
以上脚本都要记住的就如果你要增加、修改、删除一个表,视图,函数,存储过程,索引,字段都必须判断是否存在。
5、关于脚本上传到svn的规范
1、文件命名规则:
A、每天建立一个文件夹
B、文件名称以设计的表名,字段名,函数,存储过程,日期时间,姓名
示例:doa_deletefield_BOM_alter_201109061105_wenjl.sql
存储过程名称+动作+时间+姓名
个人觉得加上时间比较好,这样可以有效避免存储过程函数等更改的先后问题。最好可以统一开发库。
- oracle学习笔记——sql脚本范例
- Oracle学习笔记——动态SQL
- Oracle学习笔记——Oracle和SQL基础
- Oracle学习笔记——Oracle和SQL基础
- Oracle学习笔记——Oracle和SQL基础
- Oracle学习笔记——Oracle和SQL基础
- 常用的sql脚本范例
- SQL 注入脚本学习笔记
- oracle sql学习笔记
- Oracle + SQL 学习笔记
- Oracle + SQL 学习笔记
- Oracle Sql学习笔记
- 10.Oracle深度学习笔记——Oracle统计数据自动采集脚本
- Oracle学习笔记(一)——Oracle服务启动、停止脚本
- Oracle学习笔记——PL/SQL编程基础
- oracle 学习笔记——sql的优化
- Oracle学习笔记——PL/SQL块
- Oracle 学习笔记8 —— PL/SQL 定义变量
- Tempter of the Bone(HDU 深搜)
- UIControl
- 使用DBCP进行数据源配置
- 使用Genymotion调试出现错误INSTALL_FAILED_CPU_ABI_INCOMPATI
- UNIX文件mode_t详解
- oracle学习笔记——sql脚本范例
- SpringMVC文件下载与上传
- 模运算的规则
- leetCode 117.Populating Next Right Pointers in Each Node II (为节点添加右指针) 解题思路和方法
- 通过递归实现java xml转json
- 《Java网络编程》读书笔记(二)socket编程
- oralce学习笔记——sql注释
- Skype安装&Skype问题解决
- iOS 之CoreLocation基本使用