oracle的建表sql转成sqlserver的建表sql时的注意点 :
1.所有的comment语句需要删除。
2.clob类型转换为text类型。
3.blob类型转换为image类型。
4.number类型转换为int,number(16,2)等转换为decimal(16,2),number(18)转换为bigint。
5.default sysdate改为default getdate()。
6.to_date('2009-12-18','yyyy-mm-dd')改为cast('2009-12-18' as datetime)
sqlserver:
变量的声明:
声明变量时必须在变量前加@符号
declare @i int
变量的赋值:
变量赋值时变量前必须加set
set @i = 30
声明多个变量:
declare @s varchar(10),@a int
if语句:
Java代码
if .. begin ... end else if .. begin ... end else begin ... end
Example:
Sql代码
DECLARE @d INT set @d = 1 IF @d = 1 BEGIN PRINT '正确' END ELSE BEGIN PRINT '错误' END
多条件选择语句:
Example:
Sql代码
declare @today int declare @week nvarchar(3) set @today=3 set @week= case when @today=1 then '星期一' when @today=2 then '星期二' when @today=3 then '星期三' when @today=4 then '星期四' when @today=5 then '星期五' when @today=6 then '星期六' when @today=7 then '星期日' else '值错误' end print @week
循环语句:
Java代码
WHILE 条件 BEGIN 执行语句 END
Example:
Java代码
DECLARE @i INT SET @i = 1 WHILE @i<p>定义游标:</p><p>Sql代码 </p><pre class="brush:sql;toolbar:false">DECLARE @cur1 CURSOR FOR SELECT ......... OPEN @cur1 FETCH NEXT FROM @cur1 INTO 变量 WHILE(@@FETCH_STATUS=0) BEGIN 处理..... FETCH NEXT FROM @cur1 INTO 变量 END CLOSE @cur1 DEALLOCATE @cur1
Sql代码
AS
declare @CATEGORY_CI_TABLENAME VARCHAR(50) =''
declare @result VARCHAR(2000) = ''
declare @CI_ID DECIMAL = 0
declare @num int = 1
declare @countnum int = 1
BEGIN
select @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE
IF (@ATTRIBUTE2='A')
begin
DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE
OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
set @result = @result+@CONFIG_CODE+','
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
set @num = @num+ 1
if(@num<p>临时表:</p><p>-- Select INTO 从一个查询的计算结果中创建一个新表。 数据并不返回给客户端,这一点和普通的Select 不同。 新表的字段具有和 Select 的输出字段相关联(相同)的名字和数据类型。<br> select * into NewTable<br> from Uname<br><br>-- Insert INTO ABC Select<br> -- 表ABC必须存在 <br> -- 把表Uname里面的字段Username复制到表ABC<br> Insert INTO ABC Select Username FROM Uname<br><br>-- 创建临时表<br> Create TABLE #temp(<br> UID int identity(1, 1) PRIMARY KEY,<br> UserName varchar(16),<br> Pwd varchar(50),<br> Age smallint,<br> Sex varchar(6)<br> )<br> <br>-- 打开临时表<br> Select * from #temp</p><p> </p><p> 1、局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。<br>2、全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。<br>3、不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。<br><br>临时表对执行效率应该影响不大,只要不是太过份,相反可以提高效率特别是连接查询的地方,只要你的数据库临时表空间足够<br>游标多,会严重执行效率,能免则免!</p><p>其他:</p><p>--有输入参数的存储过程--</p><p>create proc GetComment</p><p>(@commentid int)</p><p>as</p><p>select * from Comment where CommentID=@commentid</p><p> </p><p>--有输入与输出参数的存储过程--</p><p>create proc GetCommentCount</p><p>@newsid int,</p><p>@count int output</p><p>as</p><p>select @count=count(*) from Comment where NewsID=@newsid</p><p> </p><p> </p><p>--返回单个值的函数--</p><p>create function MyFunction</p><p>(@newsid int)</p><p>returns int</p><p>as</p><p>begin</p><p>declare @count int</p><p>select @count=count(*) from Comment where NewsID=@newsid</p><p>return @count</p><p>end</p><p> </p><p>--调用方法--</p><p>declare @count int</p><p>exec @count=MyFunction 2</p><p>print @count</p><p> </p><p>--返回值为表的函数--</p><p>Create function GetFunctionTable</p><p>(@newsid int)</p><p>returns table</p><p>as</p><p>return</p><p>(select * from Comment where NewsID=@newsid)</p><p> </p><p>--返回值为表的函数的调用--</p><p>select * from GetFunctionTable(2)</p><p> </p><p> </p><p>-----------------------------------------------------------------------------------------------------------------------------------</p><p>SQLServer 存储过程中不拼接SQL字符串实现多条件查询</p><p> 以前拼接的写法<br> set @sql=' select * from table where 1=1 '<br> if (@addDate is not null) <br> set @sql = @sql+' and addDate = '+ @addDate + ' ' <br> if (@name '' and is not null) <br> set @sql = @sql+ ' and name = ' + @name + ' '<br> exec(@sql)<br>下面是 不采用拼接SQL字符串实现多条件查询的解决方案<br> 第一种写法是 感觉代码有些冗余<br> if (@addDate is not null) and (@name '') <br> select * from table where addDate = @addDate and name = @name <br> else if (@addDate is not null) and (@name ='') <br> select * from table where addDate = @addDate <br> else if(@addDate is null) and (@name '') <br> select * from table where and name = @name <br> else if(@addDate is null) and (@name = '') <br> select * from table <br> 第二种写法是 <br> select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = '') <br> 第三种写法是 <br> SELECT * FROM table where <br> addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END, <br> name = CASE @name WHEN '' THEN name ELSE @name END</p><p> </p><p>-----------------------------------------------------------------------------------------------------------------------------------</p><p> </p><p>SQLSERVER存储过程基本语法</p><p> </p><p>一、定义变量</p><p>--简单赋值 </p><p>declare @a int </p><p>set @a=5 </p><p>print @a </p><p></p><p>--使用select语句赋值 </p><p>declare @user1 nvarchar(50) </p><p>select @user1= '张三' </p><p>print @user1 </p><p>declare @user2 nvarchar(50) </p><p>select @user2 = Name from ST_User where ID=1 </p><p>print @user2 </p><p></p><p>--使用update语句赋值 </p><p>declare @user3 nvarchar(50) </p><p>update ST_User set @user3 = Name where ID=1 </p><p>print @user3 </p><p> </p><p>二、表、临时表、表变量</p><p>--创建临时表1 </p><p>create table #DU_User1 </p><p>( </p><p> [ID] [ int ] NOT NULL , </p><p> [Oid] [ int ] NOT NULL , </p><p> [Login] [nvarchar](50) NOT NULL , </p><p> [Rtx] [nvarchar](4) NOT NULL , </p><p> [ Name ] [nvarchar](5) NOT NULL , </p><p> [ Password ] [nvarchar]( max ) NULL , </p><p> [State] [nvarchar](8) NOT NULL </p><p>); </p><p>--向临时表1插入一条记录 </p><p>insert into #DU_User1 (ID,Oid,[Login],Rtx, Name ,[ Password ],State) values (100,2, 'LS' ,'0000' , '临时' , '321' , '特殊' ); </p><p></p><p>--从ST_User查询数据,填充至新生成的临时表 </p><p>select * into #DU_User2 from ST_User where ID</p><p></p><p>--查询并联合两临时表 </p><p>select * from #DU_User2 where ID</p><p></p><p>--删除两临时表 </p><p>drop table #DU_User1 </p><p>drop table #DU_User2 </p><p> </p><p>--创建临时表 </p><p>CREATE TABLE #t </p><p>( </p><p> [ID] [ int ] NOT NULL , </p><p> [Oid] [ int ] NOT NULL , </p><p> [Login] [nvarchar](50) NOT NULL , </p><p> [Rtx] [nvarchar](4) NOT NULL , </p><p> [ Name ] [nvarchar](5) NOT NULL , </p><p> [ Password ] [nvarchar]( max ) NULL , </p><p> [State] [nvarchar](8) NOT NULL , </p><p>) </p><p></p><p>--将查询结果集(多条数据)插入临时表 </p><p>insert into #t select * from ST_User </p><p>--不能这样插入 </p><p>--select * into #t from dbo.ST_User </p><p></p><p>--添加一列,为int型自增长子段 </p><p>alter table #t add [myid] int NOT NULL IDENTITY(1,1) </p><p>--添加一列,默认填充全球唯一标识 </p><p>alter table #t add [myid1] uniqueidentifier NOT NULL default (newid()) </p><p></p><p>select * from #t </p><p>drop table #t </p><p>--给查询结果集增加自增长列 </p><p></p><p>--无主键时: </p><p>select IDENTITY( int ,1,1) as ID, Name ,[Login],[ Password ] into #t from ST_User </p><p>select * from #t </p><p></p><p>--有主键时: </p><p>select ( select SUM (1) from ST_User where ID</p><p>--定义表变量 </p><p>declare @t table </p><p>( </p><p> id int not null , </p><p> msg nvarchar(50) null </p><p>) </p><p>insert into @t values (1, '1' ) </p><p>insert into @t values (2, '2' ) </p><p>select * from @t </p><p> 三、循环</p><p>--while循环计算1到100的和 </p><p>declare @a int </p><p>declare @ sum int </p><p>set @a=1 </p><p>set @ sum =0 </p><p>while @a</p><p>begin </p><p> set @ sum +=@a </p><p> set @a+=1 </p><p>end </p><p>print @ sum </p><p>四、条件语句</p><p>--if,else条件分支 </p><p>if(1+1=2) </p><p>begin </p><p> print '对' </p><p>end </p><p>else </p><p>begin </p><p> print '错' </p><p>end </p><p></p><p>--when then条件分支 </p><p>declare @today int </p><p>declare @week nvarchar(3) </p><p>set @today=3 </p><p>set @week= case </p><p> when @today=1 then '星期一' </p><p> when @today=2 then '星期二' </p><p> when @today=3 then '星期三' </p><p> when @today=4 then '星期四' </p><p> when @today=5 then '星期五' </p><p> when @today=6 then '星期六' </p><p> when @today=7 then '星期日' </p><p> else '值错误' </p><div class="aritcle_card flexRow artxards">
<div class="artcardd flexRow">
<a class="aritcle_card_img" rel="nofollow" href="/ai/4246" title="WorkBuddy开放平台"><img
src="https://img.php.cn/upload/ai_manual/001/246/273/178943668050321.png" alt="WorkBuddy开放平台" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
<div class="aritcle_card_info flexColumn">
<a rel="nofollow" href="/ai/4246" title="WorkBuddy开放平台" class="overflowclass">WorkBuddy开放平台</a>
<p class="overflowclass">WorkBuddy开放平台是一款面向智能体生态开发者的应用与连接器发布平台。</p>
</div>
<a rel="nofollow" href="/ai/4246" title="WorkBuddy开放平台" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
</a>
</div>
</div><p>end </p><p>print @week </p><p> </p><p>五、游标</p><p>declare @ID int </p><p>declare @Oid int </p><p>declare @Login varchar (50) </p><p></p><p>--定义一个游标 </p><p>declare user_cur cursor for select ID,Oid,[Login] from ST_User </p><p>--打开游标 </p><p>open user_cur </p><p>while @@fetch_status=0 </p><p>begin </p><p>--读取游标 </p><p> fetch next from user_cur into @ID,@Oid,@Login </p><p> print @ID </p><p> --print @Login </p><p>end </p><p>close user_cur </p><p>--摧毁游标 </p><p>deallocate user_cur </p><p>六、触发器</p><p> 触发器中的临时表:</p><p> Inserted <br> 存放进行insert和update 操作后的数据 <br> Deleted <br> 存放进行delete 和update操作前的数据</p><p>--创建触发器 </p><p>Create trigger User_OnUpdate </p><p> On ST_User </p><p> for Update </p><p>As </p><p> declare @msg nvarchar(50) </p><p> --@msg记录修改情况 </p><p> select @msg = N '姓名从“' + Deleted. Name + N '”修改为“' + Inserted. Name + '”' fromInserted,Deleted </p><p> --插入日志表 </p><p> insert into [LOG](MSG) values (@msg) </p><p></p><p>--删除触发器 </p><p>drop trigger User_OnUpdate </p><p>七、存储过程</p><p>--创建带output参数的存储过程 </p><p>CREATE PROCEDURE PR_Sum </p><p> @a int , </p><p> @b int , </p><p> @ sum int output </p><p>AS </p><p>BEGIN </p><p> set @ sum =@a+@b </p><p>END </p><p></p><p>--创建Return返回值存储过程 </p><p>CREATE PROCEDURE PR_Sum2 </p><p> @a int , </p><p> @b int </p><p>AS </p><p>BEGIN </p><p> Return @a+@b </p><p>END </p><p></p><p>--执行存储过程获取output型返回值 </p><p>declare @mysum int </p><p>execute PR_Sum 1,2,@mysum output </p><p>print @mysum </p><p></p><p>--执行存储过程获取Return型返回值 </p><p>declare @mysum2 int </p><p>execute @mysum2= PR_Sum2 1,2 </p><p>print @mysum2 </p><p> </p><p> </p><p>八、自定义函数</p><p> 函数的分类:</p><p> 1)标量值函数</p><p> 2)表值函数</p><p> a:内联表值函数</p><p> b:多语句表值函数</p><p> 3)系统函数</p><p> </p><p>--新建标量值函数 </p><p>create function FUNC_Sum1 </p><p>( </p><p> @a int , </p><p> @b int </p><p>) </p><p>returns int </p><p>as </p><p>begin </p><p> return @a+@b </p><p>end </p><p></p><p>--新建内联表值函数 </p><p>create function FUNC_UserTab_1 </p><p>( </p><p> @myId int </p><p>) </p><p>returns table </p><p>as </p><p>return ( select * from ST_User where ID</p><p></p><p>--新建多语句表值函数 </p><p>create function FUNC_UserTab_2 </p><p>( </p><p> @myId int </p><p>) </p><p>returns @t table </p><p>( </p><p> [ID] [ int ] NOT NULL , </p><p> [Oid] [ int ] NOT NULL , </p><p> [Login] [nvarchar](50) NOT NULL , </p><p> [Rtx] [nvarchar](4) NOT NULL , </p><p> [ Name ] [nvarchar](5) NOT NULL , </p><p> [ Password ] [nvarchar]( max ) NULL , </p><p> [State] [nvarchar](8) NOT NULL </p><p>) </p><p>as </p><p>begin </p><p> insert into @t select * from ST_User where ID</p><p> return </p><p>end </p><p></p><p>--调用表值函数 </p><p>select * from dbo.FUNC_UserTab_1(15) </p><p>--调用标量值函数 </p><p>declare @s int </p><p>set @s=dbo.FUNC_Sum1(100,50) </p><p>print @s </p><p></p><p>--删除标量值函数 </p><p>drop function FUNC_Sum1 </p><p>谈谈自定义函数与存储过程的区别:</p><p>一、自定义函数:</p><p> 1. 可以返回表变量</p><p> 2. 限制颇多,包括</p><p> 不能使用output参数;</p><p> 不能用临时表;</p><p> 函数内部的操作不能影响到外部环境;</p><p> 不能通过select返回结果集;</p><p> 不能update,delete,数据库表;</p><p> 3. 必须return 一个标量值或表变量</p><p> 自定义函数一般用在复用度高,功能简单单一,争对性强的地方。</p><p>二、存储过程</p><p> 1. 不能返回表变量</p><p> 2. 限制少,可以执行对数据库表的操作,可以返回数据集</p><p> 3. 可以return一个标量值,也可以省略return</p><p> 存储过程一般用在实现复杂的功能,数据操纵方面。</p><p> </p><p>-----------------------------------------------------------------------------------------------------------------------------------</p><p>SqlServer存储过程--实例</p><p>实例1:只返回单一记录集的存储过程。</p><p> 表银行存款表(bankMoney)的内容如下</p><p><img src="https://img.php.cn//upload/image/942/362/523/1481698483597363.png?x-oss-process=image/resize,p_40" title="表银行存款表(bankMoney)的内容" alt="表银行存款表(bankMoney)的内容" style="max-width:90%" style="max-width:90%"></p><p>要求1:查询表bankMoney的内容的存储过程</p><p>create procedure sp_query_bankMoney<br>as<br>select * from bankMoney<br>go<br>exec sp_query_bankMoney</p><p>注* 在使用过程中只需要把T-Sql中的SQL语句替换为存储过程名,就可以了很方便吧!</p><p>实例2(向存储过程中传递参数):</p><p>加入一笔记录到表bankMoney,并查询此表中userID= Zhangsan的所有存款的总金额。</p><p>Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output<br>with encryption ---------加密<br>as<br>insert into bankMoney (id,userID,sex,Money)<br>Values(@param1,@param2,@param3, @param4)<br>select @param5=sum(Money) from bankMoney where userID='Zhangsan'<br>go<br>在SQL Server查询分析器中执行该存储过程的方法是:<br>declare @total_price int<br>exec insert_bank '004','Zhangsan','男',100,@total_price output<br>print '总余额为'+convert(varchar,@total_price)<br>go</p><p>在这里再啰嗦一下存储过程的3种传回值(方便正在看这个例子的朋友不用再去查看语法内容):</p><p>1.以Return传回整数<br>2.以output格式传回参数<br>3.Recordset</p><p>传回值的区别:</p><p>output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。</p><p>实例3:使用带有复杂 SELECT 语句的简单过程</p><p> 下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。</p><p> USE pubs<br>IF EXISTS (SELECT name FROM sysobjects<br> WHERE name = 'au_info_all' AND type = 'P')<br> DROP PROCEDURE au_info_all<br>GO<br>CREATE PROCEDURE au_info_all<br>AS<br>SELECT au_lname, au_fname, title, pub_name<br> FROM authors a INNER JOIN titleauthor ta<br> ON a.au_id = ta.au_id INNER JOIN titles t<br> ON t.title_id = ta.title_id INNER JOIN publishers p<br> ON t.pub_id = p.pub_id<br>GO</p><p> au_info_all 存储过程可以通过以下方法执行:</p><p> EXECUTE au_info_all<br>-- Or<br>EXEC au_info_all</p><p> 如果该过程是批处理中的第一条语句,则可使用:</p><p> au_info_all</p><p>实例4:使用带有参数的简单过程</p><p> CREATE PROCEDURE au_info<br> @lastname varchar(40),<br> @firstname varchar(20)<br>AS<br>SELECT au_lname, au_fname, title, pub_name<br> FROM authors a INNER JOIN titleauthor ta<br> ON a.au_id = ta.au_id INNER JOIN titles t<br> ON t.title_id = ta.title_id INNER JOIN publishers p<br> ON t.pub_id = p.pub_id<br> WHERE au_fname = @firstname<br> AND au_lname = @lastname<br>GO</p><p> au_info 存储过程可以通过以下方法执行:</p><p> EXECUTE au_info 'Dull', 'Ann'<br>-- Or<br>EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'<br>-- Or<br>EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'<br>-- Or<br>EXEC au_info 'Dull', 'Ann'<br>-- Or<br>EXEC au_info @lastname = 'Dull', @firstname = 'Ann'<br>-- Or<br>EXEC au_info @firstname = 'Ann', @lastname = 'Dull'</p><p> 如果该过程是批处理中的第一条语句,则可使用:</p><p> au_info 'Dull', 'Ann'<br>-- Or<br>au_info @lastname = 'Dull', @firstname = 'Ann'<br>-- Or<br>au_info @firstname = 'Ann', @lastname = 'Dull'</p><p> </p><p></p><p> 实例5:使用带有通配符参数的简单过程</p><p>CREATE PROCEDURE au_info2<br>@lastname varchar(30) = 'D%',<br>@firstname varchar(18) = '%'<br>AS<br>SELECT au_lname, au_fname, title, pub_name<br>FROM authors a INNER JOIN titleauthor ta<br> ON a.au_id = ta.au_id INNER JOIN titles t<br> ON t.title_id = ta.title_id INNER JOIN publishers p<br> ON t.pub_id = p.pub_id<br>WHERE au_fname LIKE @firstname<br> AND au_lname LIKE @lastname<br>GO</p><p> au_info2 存储过程可以用多种组合执行。下面只列出了部分组合:</p><p> EXECUTE au_info2<br>-- Or<br>EXECUTE au_info2 'Wh%'<br>-- Or<br>EXECUTE au_info2 @firstname = 'A%'<br>-- Or<br>EXECUTE au_info2 '[CK]ars[OE]n'<br>-- Or<br>EXECUTE au_info2 'Hunter', 'Sheryl'<br>-- Or<br>EXECUTE au_info2 'H%', 'S%'</p><p> = 'proc2'</p><p>实例6:if...else</p><p> </p><p></p><p>存储过程,其中@case作为执行update的选择依据,用if...else实现执行时根据传入的参数执行不同的修改. <br>--下面是if……else的存储过程: <br>if exists (select 1 from sysobjects where name = 'Student' and type ='u' )<br>drop table Student<br>go<br><br>if exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' )<br>drop proc spUpdateStudent<br>go<br><br>create table Student<br>(<br>fName nvarchar (10),<br>fAge </p><p>smallint ,<br>fDiqu varchar (50),<br>fTel int <br>)<br>go<br><br>insert into Student values ('X.X.Y' , 28, 'Tesing' , 888888)<br>go<br><br>create proc spUpdateStudent<br>(<br>@fCase int ,<br>@fName nvarchar (10),<br>@fAge smallint ,<br>@fDiqu varchar (50),<br>@fTel int <br>)<br>as <br>update Student<br>set fAge = @fAge, -- 传 1,2,3 都要更新 fAge 不需要用 case <br>fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),<br>fTel = (case when @fCase = 3 then @fTel else fTel end )<br>where fName = @fName<br>select * from Student<br>go<br><br>-- 只改 Age <br>exec spUpdateStudent<br>@fCase = 1,<br>@fName = N'X.X.Y' ,<br>@fAge = 80,<br>@fDiqu = N'Update' ,<br>@fTel = 1010101<br><br>-- 改 Age 和 Diqu <br>exec spUpdateStudent<br>@fCase = 2,<br>@fName = N'X.X.Y' ,<br>@fAge = 80,<br>@fDiqu = N'Update' ,<br>@fTel = 1010101<br><br>-- 全改 <br>exec spUpdateStudent<br>@fCase = 3,<br>@fName = N'X.X.Y' ,<br>@fAge = 80,<br>@fDiqu = N'Update' ,<br>@fTel = 1010101</p><p><br></p>










