Categories of SQL Statements Data definition statements Data

Categories of SQL Statements • • • Data definition statements Data manipulation statements Cursor optimization statements Dynamic management statements Data access statements Data integrity statements Optimization statements Routine Definition statements Auxiliary statements Client/server connection statements Optical subsystem statements 1

Data Definition Statements ALTER FRAGMENT ALTER INDEX ALTER ROUTINE CLOSE DATABASE CREATE CAST CREATE DISTINCT TYPE CREATE INDEX CREATE PROCEDURE CREATE ROLE CREATE SCHEMA CREATE TABLE CREATE TRIGGER DATABASE DROP CAST DROP INDEX DROP ROLE DROP SYNONYM DROP TRIGGER RENAME COLUMN RENAME TABLE ALTER FUNCTION ALTER PROCEDURE ALTER TABLE CREATE AGGREGATE CREATE DATABASE CREATE EXTERNAL TABLE CREATE OPAQUE TYPE CREATE PROCEDURE FROM CREATE ROW TYPE CREATE SYNONYM CREATE TEMPORARY TABLE CREATE VIEW DROP AGGREGATE DROP DATABASE DROP PROCEDURE DROP ROW TYPE DROP TABLE DROP VIEW RENAME DATABASE TRUNCATE 2

Data Manipulation Statements DELETE LOAD UNLOAD Cursor Manipulation Statements CLOSE FETCH FREE PUT Optimization Statements SET AUTOFREE SET DEFERRED_PREPARE INSERT SELECT UPDATE DECLARE FLUSH OPEN SET AUTOFREE 3

Dynamic Management Statements ALLOCATE COLLECTION ALLOCATE ROW DEALLOCATE DESCRIPTOR DESCRIBE EXECUTE IMMEDIATE GET DESCRIPTOR SET DEFERRED_PREPARE Data Access Statements GRANT LOCK TABLE REVOKE FRAGMENT SET LOCK MODE SET SESSION AUTHORIZATION SET TRANSACTION MODE ALLOCATE DESCRIPTOR DEALLOCATE COLLECTION DEALLOCATE ROW EXECUTE FREE PREPARE SET DESCRIPTOR GRANT FRAGMENT REVOKE SET ISOLATION SET ROLE SET TRANSACTION UNLOCK TABLE 4

Data Integrity Statements BEGIN WORK ROLLBACK WORK SET LOG SET TRANSACTION MODE STOP VIOLATIONS TABLE Optimization Statements SET EXPLAIN SET PDQPRIORITY SET SCHEDULE LEVEL UPDATE STATISTICS COMMIT WORK SET DATABASE OBJECT MODE SET PLOAD FILE START VIOLATIONS TABLE SET OPTIMIZATION SET RESIDENCY SET STATEMENT CACHE 5

Routine Definition Statements ALTER FUNCTION ALTER ROUTINE CREATE FUNCTION FROM CREATE PROCEDURE FROM DROP FUNCTION DROP ROUTINE EXECUTE PROCEDURE ALTER PROCEDURE CREATE FUNCTION CREATE PROCEDURE CREATE ROUTINE FROM DROP PROCEDURE EXECUTE FUNCTION SET DEBUG FILE TO 6

Auxiliary Statements INFO GET DIAGNOSTICS WHENEVER Client/Server Connection Statements CONNECT SET CONNECTION Optical Subsystem Statements ALTER OPTICAL CLUSTER DROP OPTICAL CLUSTER RESERVE OUTPUT SET DATASKIP DISCONNECT CREATE OPTICAL CLUSTER RELEASE SET MOUNTING TIMEOUT 7

Data Type 8

Data Type 9

CREATE DATABASE 10

CREATE SCHEMA 11

CREATE TABLE 12

CREATE TABLE 1 13

CREATE TABLE 2 14

CREATE TABLE 3 15

CREATE TABLE 4 16

CREATE TABLE 5 17

CREATE INDEX 18

TRIGGER • A mechanism that resides in the database. • It is available to any user who has permission to use it. • Specifies that when a particular action, an insert, a select, a delete, or an update, occurs on a particular table, the database server should automatically perform one or more additional actions. • The additional actions can be INSERT, DELETE, UPDATE, EXECUTE PROCEDURE or EXECUTE FUNCTION statements. 19

CREATE TRIGGER 1 20

CREATE TRIGGER 2 21

CREATE TRIGGER 3 22

CREATE TRIGGER 4 23

CREATE TRIGGER 5 24

CREATE PROCEDURE 1 25

CREATE PROCEDURE 2 26

CREATE PROCEDURE 3 27

Example 1: CREATE TRIGGER upqty UPDATE OF quantity ON items BEFORE (EXECUTE PROCEDURE upd_items_p 1) Example 2: CREATE PROCEDURE upd_items_p 1() DEFINE GLOBAL old_qty INT DEFAULT 0; LET old_qty = (SELECT SUM(quantity) FROM items); END PROCEDURE; CREATE PROCEDURE upd_items_p 2() DEFINE GLOBAL old_qty INT DEFAULT 0; DEFINE new_qty INT; LET new_qty = (SELECT SUM(quantity) FROM items; IF new_qty > old_qty * 1. 50 THEN RAISE EXCEPTION – 746, 0, ‘Not allowed / rule violation; ’ END IF END PROCEDURE; 28

Example 3: CREATE TRIGGER up_items UPDATE OF quantity ON items BEFORE(EXECUTE PROCEDURE upd_items_p 1()) AFTER(EXECUTE PROCEDURE upd_items_p 2()); 29

Example 4: FOR EACH ROW clause, REFERENCING clause CREATE TABLE log_record (item_num SMALLINT, ord_num INTEGER, username CHAR (8), update_time DATETIME YEAR TO MINUTE, old_qty SMALLINT, new_qty SMALLINT); CREATE TRIGGER upqty UPDATE OF quantity ON items REFERENCING OLD AS pre_upd NEW AS post_upd FOR EACH ROW (INSERT INTO log_record VALUES (pre_upd. item_num, pre_upd. order_num, USER, CURRENT year to fraction(3), pre_upd. quantity, post_upd. quantity)); 30

Example 5: WHEN condition CREATE TRIGGER up_price UPDATE OF unit_price ON stock REFERENCING OLD AS pre NEW AS post FOR EACH ROW WHEN (post. unit_price > pre. unit_price * 2) (INSERT INTO warn_tab VALUES (pre. stock_num, pre. order_num, pre. unit_price, post. unit_price, CURRENT)) 31

Example 6: passing data to SPL procedure CREATE TRIGGER upd_totpr UPDATE OF quantity ON items REFERENCING OLD AS pre_upd NEW AS post_upd FOR EACH ROW(EXECUTE PROCEDURE calc_totpr(pre_upd. quantity, post_upd. quantity, pre_upd. total_price) INTO total_price) CREATE PROCEDURE calc_totpr(old_qty SMALLINT, new_qty SMALLINT, total MONEY(8)) RETURNING MONEY(8); DEFINE u_price LIKE items. total_price; DEFINE n_total LIKE items. total_price; LET u_price = total / old_qty; LET n_total = new_qty * u_price; RETURN n_total; END PROCEDURE 32

Zdroje: Informix Dynamic Server 2000, Product Documentation 33