Temporary Tables

Temporary Table

create table #temptable {
 id int,
 name nvarchar(50)
};

select * into #temptable from UserTable;

Features

• Table name begin with '#'
• Table will be automatically dropped when session closed
• Manually drop table will be suggested
• Temporary table will be stored at database 'tempdb'
• Different session/user can create temporary table with same table name
• Index supported

Table Variables

declare @temptable table{
 id int,
 name nvarchar(50)
};

Features

• Table name begin with '@'
• Table do not need drop
• Table data only exist in memory
• Can not create from select statement

Query all columns of all tables

SELECT
    a.TABLE_NAME                as 表格名稱,
    b.COLUMN_NAME               as 欄位名稱,
    b.DATA_TYPE                 as 資料型別,
    b.CHARACTER_MAXIMUM_LENGTH  as 最大長度,
    b.COLUMN_DEFAULT            as 預設值,
    b.IS_NULLABLE               as 允許空值,
    (
        SELECT
            value
        FROM
            fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, 'column', default)
        WHERE
            name='MS_Description' 
            and objtype='COLUMN' 
            and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME
    ) as 欄位備註
FROM
    INFORMATION_SCHEMA.TABLES  a
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON ( a.TABLE_NAME=b.TABLE_NAME )
WHERE
    TABLE_TYPE in ('BASE TABLE','VIEW')
ORDER BY
    a.TABLE_NAME, ordinal_position

CTE, recursive search

WITH DepartmentSearch(DeptID, DeptParent, DeptName, OuID)
AS
(
    -- 找出簽核者所屬部門
    SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
    FROM tbDepartment d
    LEFT JOIN tbEmpDept ed ON ed.DeptID = d.DeptID
    INNER JOIN tbFlowApprove fa ON fa.Approver = ed.EmpID
    WHERE fa.FlowInsID = @FlowInsID AND fa.FlowAct = @FlowAct AND fa.ActAppendix IS NULL
    UNION ALL
    -- 遞迴向上找出主管部門
    SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
    FROM tbDepartment d
    INNER JOIN DepartmentSearch ON DepartmentSearch.DeptParent = d.DeptID
)
SELECT * FROM DepartmentSearch

other ref: 一般資料表運算式(Common Table Expressions, CTE)

for xml usage

SELECT
p.[product_name],
p.[product_imgstyle],
p.[product_color],
p.[product_size],
p.[product_weight],
p.[product_desc],
(
 SELECT l.[purchase_url], s.[store_name], s.[display_order]
 FROM [Product_StoreLink] l, [Store] s
 WHERE l.[store_id] = s.[store_id] AND l.[product_id] = p.[product_id]
 FOR XML RAW
) store
FROM Product p

Fill zero in front of number

SQL Server:

select replicate('0', (10-len('123')))+'123'

Oracle:

SELECT LPad('123',10,'0') FROM dual

DB2:

values char(repeat('0',10-length('123'))||'123',10)

Oracle SQL Developer avoid substitution

SET DEFINE OFF;
SELECT 'D&G' FROM DUAL;

OR

SELECT 'D&'||'G' FROM DUAL;

RETURNING : return value from query

SET SERVEROUTPUT ON;
DECLARE 
    v_column VARCHAR2(100);
BEGIN
    DBMS_OUTPUT.ENABLE;
    INSERT INTO table1(column1) VALUES ('TEST VALUE') RETURNING column1 INTO v_column;
    DBMS_OUTPUT.PUT_LINE(v_column);
END;

EXECUTE IMMEDIATE : execute string query

SET SERVEROUTPUT ON;

/* execute query */
DECLARE
BEGIN
    EXECUTE IMMEDIATE 'SELECT CURRENT_DATE FROM DUAL';
END;

/* execute query & set value */
DECLARE
    v_time DATE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    EXECUTE IMMEDIATE 'SELECT CURRENT_DATE FROM DUAL' INTO v_time;
    DBMS_OUTPUT.PUT_LINE(v_time);
END;

/* execute query with parameter */
DECLARE
    v_emp_id VARCHAR2(20);
    v_name VARCHAR2(20);
BEGIN
    v_name := 'Bruce';
    v_emp_id := '00987';
    EXECUTE IMMEDIATE 'UPDATE employee SET employee_name = :1 WHERE employee_id = :2'
    USING v_name, v_emp_id;
    COMMIT;
END;

/* execute procedure */
DECLARE
    v_start_index NUMBER := 24;
    v_end_index NUMBER:= 587;
    v_sum NUMBER;
    v_status NUMBER(1,0);
BEGIN
    DBMS_OUTPUT.ENABLE;
    EXECUTE IMMEDIATE 'BEGIN get_lot_amount(:1, :2, :3); END;'
    USING IN v_start_index, IN v_end_index, OUT v_sum, IN OUT v_status;

    IF v_status = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
    END IF;
END;

Java: ResultSet on store procedure

Oracle

CREATE OR REPLACE PROCEDURE get_result (
    start_pattern IN VARCHAR2,
    end_pattern IN VARCHAR2,
    result_set OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN result_set FOR
        SELECT
            sheet_id
        FROM checksheet_with_process
        WHERE
            sheet_id >= start_pattern
            AND sheet_id <= end_pattern
        ORDER BY sheet_id DESC;

END;

-- test 
SET SERVEROUTPUT ON;
DECLARE
result_set SYS_REFCURSOR;
sheet_id VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE;
    get_sheet_print_list('20100923','20101023',result_set);
    
    LOOP
        FETCH result_set INTO
            sheet_id
        ;
        EXIT WHEN result_set%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(sheet_id);
    END LOOP;
END;

ProcedureResult.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProcedureResult {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("connection string");
            stmt = conn.prepareCall("CALL get_result(?,?,?)");
            stmt.setString(1, "20101001");
            stmt.setString(2, "20101031");
            stmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
            stmt.execute();
            rs = (ResultSet) stmt.getObject(3);

            while (rs != null && rs.next()) {
                System.out.println(rs.getString("sheet_id"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e2) {
                    // TODO Auto-generated catch block
                    e2.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }

}

Cursor with parameter

SET SERVEROUTPUT ON;
DECLARE 
    id_start VARCHAR2(200);
    CURSOR get_old_id(id_pattern VARCHAR2) IS
        SELECT
        column1
        FROM table1
        WHERE column1 LIKE id_pattern
        ORDER BY column1 ASC
    ;

BEGIN
    DBMS_OUTPUT.ENABLE;
    OPEN get_old_id('2010%');
    FETCH get_old_id INTO id_start;
    DBMS_OUTPUT.PUT_LINE(id_start);
    CLOSE get_old_id;
END;

DBMS_OUTPUT in Oracle SQL Developer

SET SERVEROUTPUT ON FORMAT WRAPED;
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE('Hello oracle!');
END;

Cumulative sum

SELECT * FROM pg_account;
 
SELECT
    s.pg_id,
    s.pg_name,
    s.pg_entry,
    SUM(NVL(c.pg_entry,0)) cumulative,
    ROUND(s.pg_entry / (SELECT SUM(pg_entry) FROM pg_account)*100, 5) percentage,
    ROUND(SUM(NVL(c.pg_entry,0))/(SELECT SUM(pg_entry) FROM pg_account)*100,5) cumulative_percentage
FROM pg_account s, pg_account c
WHERE s.pg_id > c.pg_id OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry)
GROUP BY s.pg_id, s.pg_name, s.pg_entry
ORDER BY s.pg_id
;
 
SELECT
    s.pg_id,
    s.pg_name,
    s.pg_entry,
    COUNT(c.pg_id) ranking
FROM pg_account s, pg_account c
WHERE s.pg_entry < c.pg_entry OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry)
GROUP BY s.pg_id, s.pg_name, s.pg_entry, s.pg_entry
ORDER BY s.pg_entry DESC
;

PG_ID                  PG_NAME              PG_ENTRY               
---------------------- -------------------- ---------------------- 
1                      Bruce                22                     
2                      James                72                     
3                      Yilin                65                     
4                      Ted                  77                     
5                      Charles              35                     
6                      Sean                 43                     
7                      Paul                 57                     
8                      Ken                  35                     
8 個資料列已選取

PG_ID                  PG_NAME              PG_ENTRY               CUMULATIVE             PERCENTAGE             CUMULATIVE_PERCENTAGE  
---------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
1                      Bruce                22                     22                     5.41872                5.41872                
2                      James                72                     94                     17.73399               23.15271               
3                      Yilin                65                     159                    16.00985               39.16256               
4                      Ted                  77                     236                    18.96552               58.12808               
5                      Charles              35                     271                    8.62069                66.74877               
6                      Sean                 43                     314                    10.59113               77.3399                
7                      Paul                 57                     371                    14.03941               91.37931               
8                      Ken                  35                     406                    8.62069                100                    
8 個資料列已選取

PG_ID                  PG_NAME              PG_ENTRY               RANKING                
---------------------- -------------------- ---------------------- ---------------------- 
4                      Ted                  77                     1                      
2                      James                72                     2                      
3                      Yilin                65                     3                      
7                      Paul                 57                     4                      
6                      Sean                 43                     5                      
5                      Charles              35                     6                      
8                      Ken                  35                     6                      
1                      Bruce                22                     8                      
8 個資料列已選取

Select/update table where trigger against, ORA-04091 error occurs

CREATE OR REPLACE
TRIGGER udpate_sheet_processing 
AFTER INSERT ON sheet_item 
FOR EACH ROW


DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 自治事務 
    v_sheet_id VARCHAR2(50);
    v_processing_entry NUMBER(5);
    CURSOR get_processing_amount IS
        SELECT count(*)+1
        FROM sheet_item
        WHERE sheet_id = v_sheet_id
    ;
    
BEGIN
    v_sheet_id := :NEW.sheet_id;

    OPEN get_processing_amount;
    FETCH get_processing_amount INTO v_processing_entry;
    CLOSE get_processing_amount;

    UPDATE product_checksheet
    SET sheet_processing_entry = v_processing_entry
    WHERE sheet_id = v_sheet_id;
    
    COMMIT;  -- commit for update
END;

Date function : system date

Oracle

SELECT CURRENT_DATE FROM DUAL;
SELECT SYSDATE FROM DUAL;

Oracle Date Functions : http://psoug.org/reference/date_func.html

Group by Month/ Group by Week

T-SQL

-- Group by month
SELECT
    YEAR([ImportDate]) iYear,
    MONTH([ImportDate]) iMonth,
    SUM([SourceItemCount]) ItemCount,
    SUM([ImportItemCount]) ImportCount
FROM [RssFromOrgImportLog] WITH (NOLOCK)
GROUP BY YEAR([ImportDate]), MONTH([ImportDate])
ORDER BY iYear DESC, iMonth DESC

-- Group by week
SELECT
    DATEADD(dd, (DATEDIFF(dd, -53690, [ImportDate])/7) * 7, -53690) iWeek,
    SUM([SourceItemCount]) ItemCount,
    SUM([ImportItemCount]) ImportCount
FROM [RssFromOrgImportLog] WITH (NOLOCK)
GROUP BY DATEADD(dd, (DATEDIFF(dd, -53690, [ImportDate])/7) * 7, -53690)
ORDER BY iWeek DESC

Get lastest record of workitem

SQL Server

-- get lastest record of every instance

SELECT * FROM [HISTORY] H
WHERE [CREATEDTIME]  = 
    (
        SELECT MAX([CREATEDTIME]) FROM [HISTORY] M 
        WHERE M.[PROCESSINSTANCEID] = H.[PROCESSINSTANCEID] AND M.[EVENTTYPE] = H.[EVENTTYPE]
    )
ORDER BY [PROCESSINSTANCEID]

Try catch in SQL Server 2005

BEGIN
    BEGIN TRANSACTION 
    BEGIN TRY
        // QUERY HERE
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        ROLLBACK
    END CATCH
END

Detect column exist

Sql server:

-- Add new column if column not exists
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ColumnName' AND Object_ID = Object_ID(N'TableName'))
ALTER TABLE [TableName] ADD [ColumnName] VARCHAR(200)


-- Modify column name if new column not exists
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'NewColumnName' AND Object_ID = Object_ID(N'TableName'))
EXECUTE sp_rename N'TableName.OldColumnName', N'NewColumnName', 'COLUMN' 

Detect the condition is null or not

UPDATE
    [fl_news_content]
SET
    [nc_title] = @nc_title,
    [nc_summary] = @nc_summary,
    [nc_content] = @nc_content,
    [nc_date] = @nc_date,
WHERE
    1 = 1
    AND (@nc_create_oid IS NULL or [nc_create_oid] = @nc_create_oid)

OR

UPDATE
    [fl_news_content]
SET
    [nc_title] = @nc_title,
    [nc_summary] = @nc_summary,
    [nc_content] = @nc_content,
    [nc_date] = @nc_date,
WHERE
    1 = 1
    AND [nc_create_oid] = ISNULL(@nc_create_oid,[nc_create_oid])

update optional memo

UPDATE
    [fl_news_content]
SET
    [nc_title] = @nc_title,
    [nc_summary] = @nc_summary,
    [nc_content] = @nc_content,
    [nc_date] = @nc_date,
    [nc_data_source] = @nc_data_source,
    [nc_data_source_link] = @nc_data_source_link,
    [nc_sort] = @nc_sort,
    [nc_first_record] = @nc_first_record,
    [nc_status] = @nc_status,
    [nc_deleted] = @nc_deleted,
    [nc_start_date] = @nc_start_date,
    [nc_end_date] = @nc_end_date,
    [nc_lastfix_time] = GETDATE(),
    [nc_lastfix_user] = @nc_lastfix_user,
    [nc_lastfix_user_ip] = @nc_lastfix_user_ip
WHERE
    [nc_id] = @nc_id;


UPDATE
    [fl_news_content]
SET
    [nc_img_bin] = @nc_img_bin,
    [nc_img_type] = @nc_img_type,
    [nc_img_isnew] = @nc_img_isnew
WHERE
    [nc_id] = @nc_id AND @nc_img_bin IS NOT NULL;