• Judgment statement
• The value of select is passed to a variable

 IF(@v_count <> 0) 
  BEGIN
    SELECT @v_team_id=team_id FROM TABLE T WHERE T.column=1;
  END
    ELSE
    BEGIN
    SET@v_team_name='nothing';
  END

• Multi-line to one-line

  https://www.cnblogs.com/CreateMyself/p/9058380.html
  oracle:listagg

SELECT STUFF((SELECT '/' + T. role_name FROM src_role T FOR XML PATH('')), 1, 1, '');

SELECT NAME,ID 
,STUFF((SELECT ','+HOBBY FROM [USER] T2 WHERE T2.NAME=T1.NAME FOR XML PATH(")),1,1,") 
FROM [USER] T1 
GROUP BY NAME,ID 

• Query result judgment output

select WORK_CERT_TYPE_CODE,CASE t.WORK_CERT_TYPE_CODE WHEN 'WORK_CERT_TYPE_1' THEN 'High Voltage Electrical Certificate' ELSE 'Live Operation Certificate for Distribution Line' END WORK_CERT_TYPE_name from HR_USER_WORK_CERT

oracle

select WORK_CERT_TYPE_CODE,decode(t.WORK_CERT_TYPE_CODE,'WORK_CERT_TYPE_1','High Voltage Electrical Certificate','Live Operation Certificate for Distribution Line') WORK_CERT_TYPE_name from HR_USER_WORK_CERT

• Determine whether a value is null or not, or replace it

ISNULL(oldTeamID, 0)

oracle

nvl(oldTeamID,0)

• Executing other stored procedures in a stored procedure is a problem with getdate(), which will cause errors if it is directly used as a parameter.

  set @startDate = GETDATE();
    execute hr.TeamUserInsert @pNewTeamId,@pUserId, -1, @startDate, null, @pActionBy

• update table alias

UPDATE T SET T.TID=1,T.TNAME='Name',T.TClass=1 FROM [TABLE] T WHERE T.ID=10

oracle

UPDATE TABLE T SET T.TID=1,T.TNAME='Name',T.TClass=1 WHERE T.ID=10

• Create Sequences

  https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017

CREATE SEQUENCE event_seq
MINVALUE 1
MAXVALUE 999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
 --When in use
GO
SELECT next value for event_seq
//Fill in next value for event_seq if the column default value is set to a sequence
select * from sys.sequences

• View recent statements executed by the database

  https://www.cnblogs.com/chenjianxiang/p/7833588.html

SELECT TOP 1000 
       ST.text AS 'Executing SQL Sentence',
       QS.execution_count AS 'Number of executions',
       QS.total_elapsed_time AS 'time consuming',
       QS.total_logical_reads AS 'Number of logical reads',
       QS.total_logical_writes AS 'Number of logical writes',
       QS.total_physical_reads AS 'Number of physical reads',       
       QS.creation_time AS 'execution time' ,  
       QS.*
FROM sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE QS.creation_time BETWEEN '2019-07-24 00:00:00' AND '2019-07-25 11:00:00' 
ORDER BY QS.creation_time desc;

• Connector
  +

  oracle

  ||
• int-to-string expression and elimination of left and right spaces

select convert(varchar(10),1234)
SELECT CAST(12 AS varchar(10))
SELECT LTRIM(RTRIM(CAST(user_id AS varchar(20)))) 

oracle

select to_char(1) from dual
trim(to_char(user_id)) 

• Separate strings into tables such as 1,2,3

ALTER FUNCTION dbo.splitl (
    @String VARCHAR(MAX),
    @Delimiter VARCHAR(MAX)
) RETURNS @temptable TABLE (items VARCHAR(MAX)) AS
BEGIN
    DECLARE @idx INT=1
    DECLARE @slice VARCHAR(MAX) 
    IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0
        RETURN
    WHILE @idx != 0
    BEGIN
        SET @idx = CHARINDEX(@Delimiter,@String)
        IF @idx != 0
            SET @slice = LEFT(@String,@idx - 1)
        ELSE
            SET @slice = @String
        IF LEN(@slice) > 0
            INSERT INTO @temptable(items) VALUES(@slice)
        SET @String = RIGHT (@String, LEN(@String) - @idx)
        IF LEN(@String) = 0
            BREAK
    END
    RETURN
END
SELECT * FROM dbo.splitl('a#b#c#d','#')

Source: https://my.oschina.net/cwalet/blog/267386

oracle

select split(coor_dept, ',') from reform_monitor where dj_no = pDj_no

• Create temporary tables

Create temporary tables
       Method 1:
     create table temporary table name (field 1 constraint,
                      Field 2 constraints,
                  .....)
     create table # temporary table name (field 1 constraint,
                          Field 2 constraints,
                      .....)
        Method 2:
     select * into Temporary table name from your table;
     select * into # temporary table name from your table;
Note: The above # represents the local temporary table and # represents the global temporary table.

• Function returns table format

ALTER FUNCTION [dbo].[returnTable]
( 
)
RETURNS @Tables TABLE(one int,two VARCHAR(20),three date ,four date)
AS
BEGIN
     insert @Tables select * from student
     RETURN 
END

-- SET NOCOUNT { ON | OFF } SET NOCOUNT ON

Every time we debug an SQL statement using a query analyzer, we usually see some information that reminds us how many rows are currently affected and what information is this? Is this information useful when we call it? Can it be closed? 

The answer is that this information is not useful in our client application. It is the DONE_IN_PROC information for each statement in the stored procedure. 

We can use SET NOCOUNT to control this information in order to improve program performance. 

• Query table structure information

  https://blog.csdn.net/wangxw1803/article/details/90749909

SELECT (case when a.colorder=1 then d.name else null end) Table name, 
a.colorder Field serial number,a.name Field name,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) Identification, 
(case when (SELECT count(*) FROM sysobjects 
WHERE (name in (SELECT name FROM sysindexes 
WHERE (id = a.id) AND (indid in 
(SELECT indid FROM sysindexkeys 
WHERE (id = a.id) AND (colid in 
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) 
AND (xtype = 'PK'))>0 then '√' else '' end) Primary key,b.name type,a.length Number of bytes occupied, 
COLUMNPROPERTY(a.id,a.name,'PRECISION') as length, 
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as Decimal digits,(case when a.isnullable=1 then '√'else '' end) Allowed empty, 
isnull(e.text,'') Default values,isnull(g.[value], ' ') AS [Explain]
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype 
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id 
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='Table to query' --If only the specified table is queried,Add this condition
order by a.id,a.colorder

• Query all database names in the database

SELECT Name FROM Master..SysDatabases ORDER BY Name

• Query the table names of all user databases

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name