发点以前学db2时写的笔记，忘了的话就来lu看一下
--#
--# DATA TYPE
--#
TYPE BYTE PRECISION
SMALLINT: 2 5
INTEGER: 4 10
BIGINT: 8 19
NUMERIC/DECIMAL: (p,s)
REAL: 32
DOUBLE/FLOAT: 64
DATE: 10 yyyy-mm-dd
TIME: 8 hh.mm.ss
TIMESTAMP: 26 yyyy-mm-dd-hh.mm.ss.nnnnnn
CLOB(n): 2G
DBCLOB(n): 2G
BLOB(n): 2G
TYPE LENGTH
CHAR(n): 254
VARCHAR(n): 32672
GRAPHIC(n): 127
VARGRAPHIC(n): 16336
--#
--# CREATE TABLE
--#
CREATE TABLE test ( col1 CHAR(10), col2 CHAR(10), col3 INTEGER, col4 INTEGER,
col5 GENERATED ALWAYS AS ( 'AAA' || RTRIM(LTRIM(col1)) || RTRIM(LTRIM(col2)) )
col6 GENERATED ALWAYS AS ( col3 + col4 ) )
CREATE TABLE tbname1 LIKE tbnam2
CREATE ALIAS name1 FOR name2
DROP ALIAS name1
--#
--# CONSTRAINTS
--#
NOT NULL
WITH DEFAULT '...'
--# Unique Constraint
----# DB2 will automatically add a unique index on the columns under a unique constraint with the same as it
ALTER TABLE tbname ADD CONSTRAINT rule1 UNIQUE ( team, position )
--# Check Condition Constraints
CREATE TABLE tbname ( ..., jobcode INTEGER, ... CHECK (jobcode IN ( 10, 20, 30 ) )
ALTER TABLE tbname ADD CONSTRAINT check2 CHECK ( salary < 1000 AND bonus <= salary )
--# Primary Key
CREATE TABLE tbname ( col1 CHAR(4) NOT NULL, col2 ..., PRIMARY KEY ( col1, col2 )
--#Foreign Key
----# Parent key must be a primary key or have a unique index; It's better to add a index on a foreign key
CREATE TABLE department ( deptno CHAR(3) NOT NULL PRIMARY KEY, deptname VARCHAR(32), location VARCHAR(32) )
CREATE TABLE employee ( empno CHAR(6) NOT NULL PRIMARY KEY, name VARCHAR(32) NOT NULL, deptno CHAR(6), manager CHAR(6),
CONSTRAINT fk1 FOREIGN KEY ( deptno ) REFERENCES department( deptno ) ON DELETE CASCADE,
CONSTRAINT fk2 FOREIGN KEY ( manager ) REFERENCES employee( empno ) ON DELETE CASCADE )
----# Delete Rule: ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE NO ACTION | RESTRICT
--#
--# VIEW
--#
CREATE VIEW low_pay AS SELECT name, salary FROM staff WHERE salary < 2000
CREATE VIEW low_pay_dept ( dept, min_salary ) AS
SELECT dept, MIN(salary) FROM staff GROUP BY dept
CREATE VIEW staff_org AS
SELECT name, location, salary FROM staff s INNER JOIN org o ON s.dept = o.deptnumb WHERE job <> 'Mgr'
--#Read-only View & Symmetric View
--#
--# TRIGGER
--#
CREATE TRIGGER tr1 BEFORE INSERT ON staff
CREATE TRIGGER tr2 AFTER DELETE ON staff
CREATE TRIGGER tr3 AFTER UPDATE OF jobcode, salary ON staff
--# The Granularity of Trigger
FOR EACH STATEMENT | ROW
--# Transition Variable
REFERENCING OLD AS new_row OLD AS old_row
REFERENCING OLD_TABLE AS old_table NEW_TABLE AS new_table
--# Trigger Conditions
WHEN ( new_row.salary < old_row.salary )
WHEN ( new_row.salary < ( SELECT MAX(salary) FROM emp WHERE jobcode = new_row.jobcode ) )
WHEN ( SELECT COUNT(*) FROM old_table ) > 100 )
--# Create a Trigger
CREATE TRIGGER reorder AFTER UPDATE OF qty ON stock REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
WHEN ( new_row.qty <= 5 )
INSERT INTO order VALUES ( new_row.itemno, CURRENT TIMESTAMP )
DROP TRIGGER reorder
----# A before trigger must be a row trigger, and must be tagged as "NO CASCADE"
CREATE TRIGGER tr1 NO CASCADE BEFORE INSERT ON emp REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
SET ( salary, bonus ) = ( SELECT salary, bonus FROM pay WHERE jobcode = new_row.jobcode )
CREATE TRIGGER tr2 NO CASCADE BEFORE INSERT ON bb REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
SET ( new_row.col2 ) = ( SELECT MAX(seq) FROM seq_table )
CREATE TRIGGER tr3 NO CASCADE BEFORE UPDATE OF salary ON emp REFERENCING OLD AS old_row NEW AS new_row
FOR EACH ROW MODE DB2SQL WHEN ( new_row.salary > 1.5 * old_row.salary )
SET new_row.salary = 1.5 * old_row.salary
CREATE TRIGGER tr4 NO CASCADE BEFORE DELETE ON emp REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL
WHEN ( importence(old_row.jobcode, old_row.project) > 20 )
SIGNAL SQLSTATE '70010' ( 'Do Not Fire This Person' )
CREATE TRIGGER tr5 NO CASCADE BEFORE INSERT ON shipments REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
SET pkgid = generate_unique()
----# generate_unique() generates a CHAR(13) FOR BIT DATA
---# After Trigger
CREATE TRIGGER tr1 AFTER UPDATE OF salary ON emp REFERENCING OLD AS old_row NEW AS new_row
FOR EACH ROW MODE DB2SQL WHEN ( new_row.salary < old_row.salary )
BEGIN ATOMIC
VALUES ( logEvent( 'Salary decrease', CURRENT TIMESTAMP, old_row.empno );
SIGNAL SQLSTATE '70011' ( 'Emplyee's Salary Is Decreased: ' || old_row.empno );
END
----# If a atomic fails, it will rollback and rerurn SQLCODE -723
CREATE TRIGGER tr2 AFTER UPDATE ON temps REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
WHEN ( new_row.temp > ( SELECT hightemp FROM extremes WHERE place = new_row.place )
OR ( SELECT hightemp FROM extremes WHERE place = new_row.place ) IS NULL )
UPDATE extremes SET hightemo = new_row.hightemp, highdate = CURRENT DATE WHERE place = new_row.place
CREATE TRIGGER tr3 AFTER INSERT ON temps REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
INSERT INTO extremes ( place, hightemp, highdate, lowtemp, lowdate )
VALUES ( new_row.place, new_row.temp, CURRENT DATE, new_row.temp, CURRENT DATE )
CREATE TRIGGER tr4 AFTER INSERT ON accounts REFERENCING NEW_TABLE AS new_table FOR EACH STATEMENT MODE DB2SQL
INSERT INTO account_changes ( type, when, bywhom, nrows )
VALUES ( 'I', CURRENT TIMESTAMP, USER, ( SELECT COUNT(*) FROM new_table ) )
--#
--# SEQUENCE
--#
CREATE SEQUENCE seq1 START WITH 1000 INCREMNET BY 1 NO MAXVALUE NO CYCLE NO CACHE
NEXTVAL / PREVVAL FOR seq1
CREATE TABLE test ( col1 CHAR(10),
col2 INTEGER GENERATED ALWAYS AS INDENTITY START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE NO CACHE )
INSERT INTO test VALUES ( 'a', DEFAULT )
--#
--# ENCRYPTION
--#
CREATE TABLE enc ( col1 CHAR(10), col2 VARCHAR(30) FOR BIT DATA )
INSERT INTO enc VALUES ('a3', ENCRYPT( 'a3value', 'Taipei', 'City') )
SELECT DECRYPT_CHAR(col2, 'Taipei') FROM enc WHERE col1 = 'a3'
SELECT GETHINT(col2) FORM enc WHERE col1 = 'a3'
SET ENCRYPTION PASSWORD = 'Taipei'
INSERT INTO enc VALUES ( 'a4', ENCRYPT('a4value') )
SELECT DECRYPT_CHAR(col2) FROM enc WHERE col1 = 'a4'
|
