[SQL] 筆記

整理實務上常遇到的。

查詢資料

取前幾筆

DB2

SELECT * FROM zoo FETCH FIRST 1 ROWS ONLY

取後幾筆

DB2

SELECT * FROM zoo ORDER BY critter FETCH FIRST 1 ROWS ONLY

分頁查詢

DB2

SELECT * FROM (SELECT rownumber() OVER (ORDER BY critter) as ROWID, a.* FROM zoo a) WHERE ROWID BETWEEN 11 AND 20

取得今天的日期

DB2

SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMMDD') FROM SYSIBM.SYSDUMMY1;
SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMMDD') FROM mytable;
--或是
SELECT date(current timestamp) FROM SYSIBM.SYSDUMMY1;
SELECT date(current timestamp) FROM mytable;

查詢純數字欄位

SELECT * FROM mytable WHERE mycolumn LIKE '%[0-9]%'

預存程序

DB2
傳回單一結果集，參考https://www.ibm.com/docs/zh-tw/db2/11.5?topic=procedures-returning-result-sets-from-sql

CREATE OR REPLACE PROCEDURE sp_read_emp(IN p_job VARCHAR(10))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE c_emp CURSOR WITH RETURN FOR
    SELECT salary, bonus, comm
    FROM employee
    WHERE job != p_job;

  OPEN c_emp;
END;

CALL sp_read_emp('PRES');