Advanced Database Functionality

Advanced Database Functionality - Procedure Example CREATE...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Advanced Database Functionality Sequences Some databases let you create a table with an automatically incrementing key (autonumber). DB2 uses sequences CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE; INSERT INTO INVOICE VALUES (NEXTVAL FOR INV_NUMBER_SEQ, 10019, CURRENT DATE); INSERT INTO LINE VALUES (PREVVAL FOR INV_NUMBER_SEQ, 1,'13-Q2/P2', 1, 14.99); INSERT INTO LINE VALUES (PREVVAL FOR INV_NUMBER_SEQ, 2,'23109-HB', 1, 9.95); DELETE FROM LINE WHERE INV_NUMBER = prevval for INV_NUMBER_SEQ; DELETE FROm INVOICE WHERE INV_NUMBER = PREVVAL for inv_number_SEQ; DROP SEQUENCE INV_NUMBER_SEQ; Procedures Substantially reduce network traffic and increase performance No transmission of individual SQL statements over network Help reduce code duplication by means of code isolation and code sharing Minimize chance of errors and cost of application development and maintenance
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Procedure Example CREATE PROCEDURE PRC_PROD_DISCOUNT LANGUAGE SQL BEGIN UPDATE PRODUCT SET P_DISCOUNT = P_DISCOUNT + .05 WHERE P_ONHAND >= P_MIN*2; END CALL PRC_PROD_DISCOUNT; update product p1 set p_discount = (select p_discount from ch06_SALESCO.PRODUCT P2 WHERE P1.P_CODE = P2.P_CODE); Triggers drop trigger TRG_PROD_REORDER; drop trigger TRG_PROD_REORDER2; CREATE TRIGGER TRG_PROD_REORDER BEFORE INSERT ON PRODUCT REFERENCING NEW AS N FOR EACH ROW IF (N.P_ONHAND <= N.P_MIN) THEN SET N.P_REORDER = 1; ELSE SET N.P_REORDER = 0; END IF CREATE TRIGGER TRG_PROD_REORDER2 BEFORE update of p_onhand, p_min ON PRODUCT REFERENCING NEW AS N FOR EACH ROW IF (N.P_ONHAND <= N.P_MIN) THEN SET N.P_REORDER = 1; ELSE SET N.P_REORDER = 0;...
View Full Document

Page1 / 2

Advanced Database Functionality - Procedure Example CREATE...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online