[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;

取得當前的日期和時間

DB2

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;

查詢欄位值為純數字

DB2

SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn, '^[0-9]+$')

修改結構

新增欄位

DB2

ALTER TABLE mytable
  ADD COLUMN mycolumn1 VARCHAR (45) DEFAULT
  ADD COLUMN mycolumn2 VARCHAR (20) DEFAULT;

調整欄位大小

DB2

ALTER TABLE mytable 
  ALTER COLUMN mycolumn1 SET DATA TYPE VARCHAR (200)
  ALTER COLUMN mycolumn2 SET DATA TYPE VARCHAR (200);

重新命名欄位

DB2

ALTER TABLE mytable
  RENAME COLUMN mycolumn1 TO mycolumn11;

預存程序

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');