Points to note when converting oracle's table creation sql into sqlserver's table creation sql:
1. All comment statements need to be deleted.
2. Convert clob type to text type.
3. Convert blob type to image type.
4.The number type is converted to int, number(16,2), etc. are converted to decimal(16,2), and number(18) is converted to bigint.
5.default sysdate is changed to default getDate().
6.to_date('2009-12-18','yyyy-mm-dd') is changed to cast('2009-12-18' as datetime)
SQLSERVER:
Declaration of variables:
Must be used when declaring variables Add the @ symbol in front of the variable
DECLARE @I INT
Assignment of variables:
When assigning a variable, you must add set before the variable
SET @I = 30
Declare multiple variables:
DECLARE @s varchar(10),@a INT
if statement:
Java code
if .. begin ... end else if .. begin ... end else begin ... end
Example:
Sql code
DECLARE @d INT set @d = 1 IF @d = 1 BEGIN PRINT '正确' END ELSE BEGIN PRINT '错误' END
Multiple criteria selection statement:
Example:
Sql code
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 @weekLoop statement:
Java code
WHILE 条件 BEGIN 执行语句 END
Example:
Java code
DECLARE @i INT SET @i = 1 WHILE @i<1000000 BEGIN set @i=@i+1 END
Define cursor:
Sql code
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 code
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<@countnum)
begin
set @result = @result+@CONFIG_CODE+','
end
else if(@num=@countnum)
begin
set @result = @result +@CONFIG_CODE
end
END
CLOSE MyCursor
DEALLOCATE MyCursor
set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result +') select '+ @result +' from '+@CATEGORY_CI_TABLENAME +' where CI_ORDER_LINE_ID='+@KEY_ID
end
else if((@ATTRIBUTE2='U'))Temporary table:
-- Select INTO Create a new table from the calculation results of a query. The data is not returned to the client, which is different from ordinary Select. The new table's fields have the same names and data types associated with the Select's output fields. * SELECT * Into Newtable
From Uname
--- Insert Into SELECT
-Table ABC must exist
in the table of the field of UNAME. ame
--- Create Temporary Table
Create Table #Temp (
UID int Identity (1, 1) Primary Key,
Username Varchar (16),
PWD VARCHAR (50), Age Smallint,
Sex Varchar (6)
------------------------------------------------ Open the temporary table
Select * from #temp
2. The global temporary table (starting with ##) is also valid for other connections and will be automatically deleted when the current connection and other connections that have accessed it are disconnected.
3. Regardless of whether it is a local temporary table or a global temporary table, as long as the connection has access rights, you can use drop table #Tmp (or drop table ##Tmp) to explicitly delete the temporary table.
Temporary tables should have little impact on execution efficiency, as long as it is not too excessive. On the contrary, it can improve efficiency, especially in connection queries. As long as your database temporary table space is sufficient
Many cursors will seriously affect execution efficiency. If possible, avoid it. !
--Function that returns a single value--create function MyFunction(@newsid int)returns intasbegindeclare @count intselect @count=count(*) from Comment where NewsID=@newsidreturn @countend --call method--declare @count intexec @count=MyFunction 2print @count --return value A function for the table--Create function GetFunctionTable(@newsid int)returns tableasreturn(select * from Comment where NewsID=@newsid) --The return value is Table function call--select * from GetFunctionTable(2) ---------------------------- -------------------------------------------------- -------------------------------------------------- ---SQLServer does not splice SQL strings in the stored procedure to implement multi-condition query
The previous splicing method
set @sql=' select * from table where 1=1 '
if (@addDate is not null)
set @sql = @sql+' and addDate = '+ @addDate + ' '
if ( @name '' and is not null)
set @sql = @sql+ ' and name = ' + @name + ' '
exec(@sql)
The following is a multi-condition query without splicing SQL strings The solution
The first way of writing is that the code feels a bit redundant
if (@addDate is not null) and (@name '')
select * from table where addDate = @addDate and name = @name
else if (@addDate is not null) and (@name = '')
select * from table where addDate = @addDate
else if (@addDate is null) and (@name '')
select * from table where and name = @name
else if(@addDate is null) and (@name = '')
select * from table
The second way to write it is
select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = '')
The third way of writing is
SELECT * FROM table where
addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END,
name = CASE @name WHEN '' THEN name ELSE @name END
----------------------------------------- -------------------------------------------------- --------------------------------------------
SQLSERVER Stored Procedure Basic syntax
1. Define variables
--simple assignment
declare @a int
set @a=5
print @a
--use select statement to assign values
declare @user1 nvarchar (50)
select @user1='Zhang San'
print @user1
declare @user2 nvarchar(50)
select @user2 = Name from ST_User where ID=1
print @user2
- -Use update statement to assign value
declare @user3 nvarchar(50)
update ST_User set @user3 = Name where ID=1
print @user3
2. Table, temporary table, table variable
--Create Temporary table 1
create table #DU_User1
( [ID] [ int ] NOT NULL ,
[Oid] [ int ] NOT NULL ,
[Login] [nvarchar](50) [Rtx] [nvarchar](4) NOT NULL ,
[Name] [nvarchar](5) NOT NULL ,
[Password] [nvarchar]( max ) NULL ,
[State] [nvarchar](8) NOT NULL
);
--Insert a record into temporary table 1
insert into #DU_User1 (ID,Oid,[Login],Rtx, Name,[Password],State) values (100,2, 'LS ' ,'0000' , 'Temporary' , '321' , 'Special' );
--Query data from ST_User and fill it into the newly generated temporary table
select * into #DU_User2 from ST_User where ID
--Query and join two temporary tables
select * from #DU_User2 where ID
--Delete two temporary tables
drop table # DU_User1
drop table # DU_User2
--Create temporary table
CREATE TABLE #t
(
[ID] [ int ] NOT NULL ,
[Oid ] [ int ] NOT NULL ,
[Login] [nvarchar]( 50) NOT NULL ,
[nvarchar] (4)
[State] [nvarchar](8 ) NOT NULL , ) --Insert the query result set (multiple data) into the temporary table insert into #t select * from ST_User --Cannot insert like this --select * into # t from dbo.ST_User --Add a column for the int type auto-increasing subsection alter table #t add [myid] int NOT NULL IDENTITY(1,1) --Add a column and fill the world by default Unique identifier alter table #t add [myid1] uniqueidentifier NOT NULL default (newid()) select * from #t drop table #t --Add an auto-increasing column to the query result set --When there is no primary key: select IDENTITY(int,1,1) as ID, Name,[Login],[Password] into #t from ST_User select * from #t--Yes Primary key: select (select SUM (1) from ST_User where ID--define table variable declare @t table
( id int not null , msg nvarchar(50) null ) insert into @t values (1, '1' ) insert into @t values (2, '2' ) select * from @t 3. Loop --while loop calculates the sum from 1 to 100 declare @a int declare @ sum int set @a=1 set @ sum =0
while @abegin set @ sum +=@a set @a+=1 end print @ sum 4. Conditional statements
--if,else conditional branch if(1+1 =2) begin print 'right' end else begin print 'wrong' end --when then conditional branch declare @today int declare @ week nvarchar(3) set @today=3 set @week= case when @today=1 then 'Monday' when @today=2 then 'Tuesday' when @today=3 then 'Wednesday' can be 'Sunday' else 'Value error' end print @week 5. Cursordeclare @ID int declare @Oid int declare @Login varchar (50 ) --Define a cursor
declare user_cur cursor for select ID,Oid,[Login] from ST_User --open cursor open user_cur
while @@fetch_status=0
begin
--read cursor
fetch next from user_cur into @ID,@Oid,@Login
print @ID
; user_cur
--Destroy the cursor
deallocate user_cur
6. Trigger
Temporary table in trigger:
Inserted
Storage data after insert and update operationsDeleted
Store the data before delete and update operations Data
--Create trigger Create clare @msg nvarchar(50)
--@msg record modifications
select @msg = N ' The name is changed from "' + Deleted. Name + N '" to "' + Inserted. Name + '"' fromInserted,Deleted
--Delete trigger drop trigger User_OnUpdate 7. Stored procedure --Create stored procedure with output parameters CREATE PROCEDURE PR_Sum @a int , @b int , @ sum int output AS BEGIN set @ sum =@a+@b END --Create Return value stored procedure CREATE PRO CEDURE PR_Sum2 @a int , @b int AS BEGIN Return @a+@b END --Execute the stored procedure to obtain the output type return value declare @mysum int execute PR_Sum 1,2,@mysum output print @mysum
8. Custom functions
Classification of functions:
1) Scalar-valued function
2) Table-valued function
a: Inline table-valued function
b: Multi-statement table-valued function
3) System function
--New Scalar valued function
create function FUNC_Sum1
(
@a int ,
@b int
)
returns int
as
begin
return @a+@b
end
--New inline table-valued function
create function FUNC_UserTab_1
(
@myId int
)
returns table
as
return (select * from ST_User where ID
- -New multi-statement table-valued function
create function FUNC_UserTab_2
(
@myId int
)
returns @t table
(
] NOT NULL ,
[[Login] [nvarchar] (50) not null, [rtx] [nvarchar] (4) not null, [name] [nvarchar] (5) not null, [password] [nvarchar] (nvarchar] (nvarchar] (nvarchar] (nvarchar] (nvarchar] max ) NULL , [State] [nvarchar](8) NOT NULL ) as begin insert into @t select * from ST_ User where ID return end--Call table-valued function select * from dbo.FUNC_UserTab_1(15) --Call scalar-valued function declare @s int set @s=dbo.FUNC_Sum1(100,50) print @s --Delete scalar value function drop function FUNC_Sum1 Let’s talk about the difference between custom functions and stored procedures: 1. Custom functions: 1. Can return table variables 2 . There are many restrictions, including Output parameters cannot be used; Cannot use temporary tables; Operations inside the function cannot affect the external environment; Result sets cannot be returned through select; Cannot update, delete, database tables ； 3. Must return a scalar value or table variable Custom functions are generally used in places with high reusability, simple functions, and strong competition. 2. Stored procedures 1. Cannot return table variables 2. Few restrictions, can perform operations on database tables, and can return data sets 3. Can return a scalar value, or return can be omitted Stored procedures are generally used to implement complex functions and data manipulation. ------------------------------------------------- -------------------------------------------------- ----------------------------------SqlServer stored procedure--ExampleExample 1: Return only Stored procedure for a single recordset. The contents of the table bank deposit table (bankMoney) are as follows
as
select * from bankMoney
go
exec sp_query_bankMoney
with encryption ---------encryption
as
insert into bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
go
in SQL Server The method to execute this stored procedure in the query analyzer is:
declare @total_price int
exec insert_bank '004','Zhangsan','male',100,@total_price output
print 'The total balance is'+convert(varchar,@ total_price)
go
2. In the output format Return parameters
3. Recordset
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
INNER JOIN titles
The au_info_all stored procedure can be executed by:
EXECUTE au_info_all
-- Or
EXEC au_info_all
If the procedure is the first statement in the batch, you can use:
au_info_all
Example 4: Using A simple procedure with parameters
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
use using using using ta. The _info stored procedure can be accessed via The following method is executed:
EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
EXECUTE au_info @firstname = 'Ann', @ lastname = 'Dull'
-- OrEXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = ' Ann', @lastname = 'Dull'
If the process is the first statement in the batch, you can use:
au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull' , @firstname = 'Ann'
au_info @firstname = 'Ann', @lastname = 'Dull'
Example 5: Using a simple procedure with wildcard parameters
CREATE PROCEDURE au_info2
@ lastname varchar(30) = 'D%',@firstname varchar(18) = '%'
ASSELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
The au_info2 stored procedure can be used in many combinations implement. Only some combinations are listed below:
EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
EXECUTE au_info2 @firstname = 'A%'
-- OrEXECUTE au_info2 '[CK]ars[ OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'
= 'proc2'
Example 6: if...else
stored procedure, in which @case is used as the selection basis for executing update, and if...else is used to implement different modifications according to the parameters passed in during execution.
--The following is the stored procedure of if...else ：if exists (select 1 from sysobjects where name = 'Student' and type ='u' )
drop table Studentgo
if exists (select 1 from sysobjects where name = 'spUpdateStudent' and type ='p' )
drop proc spUpdateStudent
go
create table Student
(
fName nvarchar (10),
fAge
smallint ,
fDiqu varchar (50),
fTel int
)
go
insert into Student values ('X.X.Y ' , 28, 'Tesing' , 888888)
go
create proc spUpdateStudent
(
@fCase int ,
@fName nvarchar (10),
@fAge smallint ,
@fDiqu varchar (50),
@fTel int
)
as
update Student
set fAge = @fAge, -- Pass 1,2,3 to update fAge. No need to use case
fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),
fTel = (case when @fCase = 3 then @fTel else fTel end )
where fName = @fName
select * from Student
go
-- only change Age
exec spUpdateStudent
@fCase = 1,
@ fName = N'X. 'X.
@fAge = 80,
@fDiqu = N'Update' ,
@fTel = 1010101
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
