Database Systems

1
Database Systems SQL

• SQL OPTIMIZATION
• The key to an optimized database is good design.
• There are some basic physical constraints that
  databases must work around.
• Disk Seeks Average 10 ms, aka 100 seeks a
  second. Only real optimization is to distribute
  data across multiple disks. Seek time per table
  is hard to improve.
• Disk Reading Writing 10 to 20 MB per second,
  best way to optimize is to distribute data across
  multiple disks
• CPU Cycles Data must be processed. Smaller data
  fits in memory and thus faster to process.

2
Database Systems SQL

• SQL OPTIMIZATION
• The more extensive your permissions are, the less
  optimized your database will be.
• Table level, column level permissions, resource
  counting, etc can be problematic if you have a
  large number of statements being executed.
  However, if you have a few very queries that
  execute over a large amount of data, this factor
  may not be as significant.
• USING EXPLAIN
• Using EXPLAIN helps you understand how your query
  executes. It informs you what order tables are
  joined and what indexes are used to join them. If
  you notice joins on unindexed fields, you can
  index them to improve performance. If tables are
  being joined in an order that you do not think is
  optimum, you can force MySQL to implement the
  joins in a specific order.
• MORE ON EXPLAIN

3
Database Systems SQL

• OPTIMIZATION
• The more extensive your permissions are, the less
  optimized your database will be.
• Table level, column level permissions, resource
  counting, etc can be problematic if you have a
  large number of statements being executed.
  However, if you have a few very queries that
  execute over a large amount of data, this factor
  may not be as significant.
• USING EXPLAIN
• Using EXPLAIN helps you understand how your query
  executes. It informs you what order tables are
  joined and what indexes are used to join them. If
  you notice joins on unindexed fields, you can
  index them to improve performance. If tables are
  being joined in an order that you do not think is
  optimum, you can force MySQL to implement the
  joins in a specific order.
• To execute an EXPLAIN simply type
• EXPLAIN SqlQuery

4
Database Systems SQL

• OPTIMIZATION
• Suppose that you have the SELECT statement shown
  here and that you plan to examine it using
  EXPLAIN
• EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
• tt.ProjectReference,
  tt.EstimatedShipDate,
• tt.ActualShipDate, tt.ClientID,
• tt.ServiceCodes, tt.RepetitiveID,
• tt.CurrentProcess,
  tt.CurrentDPPerson,
• tt.RecordVolume, tt.DPPrinted,
  et.COUNTRY,
• et_1.COUNTRY, do.CUSTNAME
• FROM tt, et, et AS et_1, do
• WHERE tt.SubmitTime IS NULL
• AND tt.ActualPC et.EMPLOYID
• AND tt.AssignedPC et_1.EMPLOYID
• AND tt.ClientID do.CUSTNMBR

5
Database Systems SQL

• OPTIMIZATION
• For this example, make the following assumptions
  
• The columns being compared have been declared as
  follows

The tables have the following indexes
6
Database Systems SQL

• OPTIMIZATION
• The tt.ActualPC values are not evenly
  distributed.
• Initially, before any optimizations have been
  performed, the EXPLAIN statement produces the
  following information
• table type possible_keys key key_len ref rows
  Extra
• et ALL PRIMARY NULL NULL NULL 74
• do ALL PRIMARY NULL NULL NULL 2135
• et_1 ALL PRIMARY NULL NULL NULL 74
• tt ALL AssignedPC, NULL NULL NULL 3872
• ClientID,
• ActualPC
• range checked for each record (key map 35)
• Because type is ALL for each table, this output
  indicates that MySQL is generating a Cartesian
  product of all the tables.
• For the case at hand, this product is 74 2135
  74 3872 45,268,558,720 rows.

7
Database Systems SQL

• OPTIMIZATION
• The tt.ActualPC values are not evenly
  distributed.
• Initially, before any optimizations have been
  performed, the EXPLAIN statement produces the
  following information
• table type possible_keys key key_len ref rows
  Extra
• et ALL PRIMARY NULL NULL NULL 74
• do ALL PRIMARY NULL NULL NULL 2135
• et_1 ALL PRIMARY NULL NULL NULL 74
• tt ALL AssignedPC, NULL NULL NULL 3872
• ClientID,
• ActualPC
• range checked for each record (key map 35)
• Because type is ALL for each table, this output
  indicates that MySQL is generating a Cartesian
  product of all the tables.
• For the case at hand, this product is 74 2135
  74 3872 45,268,558,720 rows.

8
Database Systems SQL

• OPTIMIZATION
• One problem here is that MySQL can use indexes on
  columns more efficiently if they are declared as
  the same type and size.
• In this context, VARCHAR and CHAR are considered
  the same if they are declared as the same size.
• tt.ActualPC is declared as CHAR(10) and
  et.EMPLOYID is CHAR(15), so there is a length
  mismatch.
• To fix this disparity between column lengths, use
  ALTER TABLE to lengthen ActualPC from 10
  characters to 15 characters.
• ALTER TABLE tt MODIFY ActualPC VARCHAR(15)

9
Database Systems SQL

• OPTIMIZATION
• Now tt.ActualPC and et.EMPLOYID are both
  VARCHAR(15).
• Executing the EXPLAIN statement again produces
  this result
• table type possible_keys key key_len ref
  rows Extra
• tt ALL AssignedPC, NULL NULL NULL
  3872 Using ClientID, where
• ActualPC
• do ALL PRIMARY NULL NULL NULL
  2135
• range checked for each record (key map 1)
• et_1 ALL PRIMARY NULL NULL NULL
  74
• range checked for each record (key map 1)
• et eq_ref PRIMARY PRIMARY 15 tt.ActualPC
  1
• This is not perfect, but is much better The
  product of the rows values is less by a factor of
  74. This version executes in a couple of seconds.

10
Database Systems SQL

• OPTIMIZATION
• A second alteration can be made to eliminate the
  column length mismatches for the tt.AssignedPC
  et_1.EMPLOYID and tt.ClientID do.CUSTNMBR
  comparisons
• ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), -gt
  MODIFY ClientID VARCHAR(15)
• After that modification, EXPLAIN produces the
  output shown here
• table type possible_keys key
  key_len ref rows Extra
• et ALL PRIMARY NULL NULL
  NULL 74
• tt ref AssignedPC, ActualPC 15
  et.EMPLOYID 52 Using ClientID, where
  ActualPC
• et_1 eq_ref PRIMARY PRIMARY 15
  tt.AssignedPC 1
• do eq_ref PRIMARY PRIMARY 15
  tt.ClientID 1

11
Database Systems SQL

• OPTIMIZATION
• At this point, the query is optimized almost as
  well as possible.
• The remaining problem is that, by default, MySQL
  assumes that values in the tt.ActualPC column are
  evenly distributed, and that is not the case for
  the tt table.
• Fortunately, it is easy to tell MySQL to analyze
  the key distribution
• ANALYZE TABLE tt
• With the additional index information, the join
  is perfect and EXPLAIN produces this result

12
Database Systems SQL

• OPTIMIZATION
• table type possible_keys key key_len ref
  rows Extra
• tt ALL AssignedPC NULL NULL NULL
  3872 Using ClientID, where ActualPC
• et eq_ref PRIMARY PRIMARY 15
  tt.ActualPC 1
• et_1 eq_ref PRIMARY PRIMARY 15
  tt.AssignedPC 1
• do eq_ref PRIMARY PRIMARY 15
  tt.ClientID 1
• Note that the rows column in the output from
  EXPLAIN is an educated guess from the MySQL join
  optimizer.
• You should check whether the numbers are even
  close to the truth by comparing the rows product
  with the actual number of rows that the query
  returns.
• If the numbers are quite different, you might get
  better performance by using STRAIGHT_JOIN in your
  SELECT statement and trying to list the tables in
  a different order in the FROM clause.

13
Database Systems SQL

• SQL OPTIMIZATION
• SPEEDING UP SELECTS
• When the data stored in a database changes, the
  statistics used to optimize queries are not
  updated automatically. Therefore, use the ANALYZE
  TABLE command on each table to speed up results.
• WHERE CLAUSE OPTIMIZATION
• First note that any optimizations for the WHERE
  clause of a SELECT query also work for the WHERE
  clauses of DELETE and UPDATE queries.
• Not all optimizations performed by MySQL are
  documented

14
Database Systems SQL

• SQL OPTIMIZATION
• Some of the optimizations performed by MySQL
  follow
• Removal of unnecessary parentheses
• ((a AND b) AND c OR (((a AND b) AND (c AND d))))
  -gt
• (a AND b AND c) OR (a AND b AND c AND d)
• Constant folding
• (altb AND bc) AND a5 -gt bgt5 AND bc AND a5
• Constant condition removal (needed because of
  constant folding)
• (Bgt5 AND B5) OR (B6 AND 55) OR (B7 AND 56)
  -gt B5 OR B6
• These optimizations occur automatically and
  therefore you do not have to worry about
  performing them yourself.

15
Database Systems SQL

• SQL OPTIMIZATION
• Examples of very fast queries
• Some examples of queries that are very fast
• SELECT COUNT() FROM tbl_name
• SELECT MIN(key_part1),MAX(key_part1) FROM
  tbl_name
• SELECT MAX(key_part2) FROM tbl_name WHERE
  key_part1constant
• SELECT ... FROM tbl_name ORDER BY
  key_part1,key_part2,... LIMIT 10
• SELECT ... FROM tbl_name ORDER BY key_part1 DESC,
  key_part2 DESC, ... LIMIT 10

16
Database Systems SQL

• SQL OPTIMIZATION
• Indexes are ignored for the ltgt operatorSELECT
  FROM tab WHERE score ltgt 0This can be a
  problem if the table is very slanted (eg gt99 of
  the rows have the value that is filtered). The
  obvious workaround is to use a UNION(SELECT
  FROM tab WHERE score gt 0) UNION(SELECT FROM
  tab WHERE score lt 0)

17
Database Systems SQL

• SQL OPTIMIZATION
• The query optimizer also does badly on examples
  likeSELECT id FROM foo WHERE bar IN (SELECT bar
  FROM baz WHERE qux'foo')
• where foo is a large table and baz a small one.
  Doing the subselect first would allow the use on
  an index to eliminate most of foo, which is what
  happens if you saySELECT foo.id FROM foo, baz
  WHERE foo.barbaz.bar and baz.qux'foo

18
Database Systems SQL

• SQL OPTIMIZATION
• Indices lose their speed advantage when using
  them in OR-situations (4.1.10)SELECT FROM a
  WHERE index1 'foo'UNIONSELECT FROM a WHERE
  index2 'baar'is much faster thanSELECT
  FROM a WHERE index1 'foo' OR index2 'bar'

19
Database Systems SQL

• SQL OPTIMIZATION
• ORDER BY
• Optimization of the ORDER BY clause may not be as
  intuitive as one might think.
• Observe the following queries that optimize
  properly
• SELECT FROM t1 ORDER BY key_part1,key_part2,...
  
• SELECT FROM t1 WHERE key_part1constant ORDER
  BY key_part2
• SELECT FROM t1 ORDER BY key_part1 DESC,
  key_part2 DESC
• SELECT FROM t1 WHERE key_part11 ORDER BY
  key_part1 DESC, key_part2 DESC
• The reason these work is because there exists a
  compound key and the order of ORDER BY clause
  follows the sequence of fields listed in the
  compound key.

20
Database Systems SQL

• SQL OPTIMIZATION
• ORDER BY
• In some cases, MySQL cannot use indexes to
  resolve the ORDER BY, although it still uses
  indexes to find the rows that match the WHERE
  clause. These cases include the following
• You use ORDER BY on different keys
• SELECT FROM t1 ORDER BY key1, key2
• You use ORDER BY on non-consecutive parts of a
  key
• SELECT FROM t1 WHERE key2constant ORDER BY
  key_part2
• You mix ASC and DESC
• SELECT FROM t1 ORDER BY key_part1 DESC,
  key_part2 ASC
• The key used to fetch the rows is not the same as
  the one used in the ORDER BY
• SELECT FROM t1 WHERE key2constant ORDER BY
  key1
• Note the difference between key and key_part!

21
Database Systems SQL

• SQL OPTIMIZATION
• GROUP BY
• While there are other considerations, a general
  rule of thumb is that only GROUP BYs over a
  single table, those grouped by the first
  consecutive indexes, and those using only MIN or
  MAX as aggregation functions are optimized.
• For example
• Assuming there is an index idx(c1,c2,c3) on table
  t1(c1,c2,c3,c4)
• SELECT c1, c2 FROM t1 GROUP BY c1, c2
• SELECT DISTINCT c1, c2 FROM t1
• SELECT c1, MIN(c2) FROM t1 GROUP BY c1
• SELECT c1, c2 FROM t1 WHERE c1 lt const GROUP BY
  c1, c2
• SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2
  gt const GROUP BY c1, c2 SELECT c2 FROM t1 WHERE
  c1 lt const GROUP BY c1, c2
• SELECT c1, c2 FROM t1 WHERE c3 const GROUP BY
  c1, c2

22
Database Systems SQL

• SQL OPTIMIZATION
• INSERT
• The time required for inserting a row is
  determined by the following factors, where the
  numbers indicate approximate proportions
• Connecting (3)
• Sending query to server (2)
• Parsing query (2)
• Inserting row (1 size of row)
• Inserting indexes (1 number of indexes)
• Closing (1)

23
Database Systems SQL

• SQL OPTIMIZATION
• INSERT
• If you are inserting many rows from the same
  client at the same time, use INSERT statements
  with multiple VALUES lists to insert several rows
  at a time. This is considerably faster (many
  times faster in some cases) than using separate
  single-row INSERT statements.
• When loading a table from a text file, use LOAD
  DATA INFILE. This is usually 20 times faster than
  using INSERT statements. See Section 13.2.5,
  LOAD DATA INFILE Syntax.
• While outside of the scope of what I wish to
  cover, if you are loading a large amount of data
  in proportion to the current size of the table,
  it may be more efficient to drop the indexes,
  load the table via a LOAD DATA INFILE, and then
  reinstate the indexes.

24
Database Systems SQL

• SQL OPTIMIZATION
• INSERT
• To speed up INSERT operations that are performed
  with multiple statements for non-transactional
  tables, lock your tables
• LOCK TABLES a WRITE
• INSERT INTO a VALUES (1,23),(2,34),(4,33)
• INSERT INTO a VALUES (8,26),(6,29)
• ... UNLOCK TABLES
• INSERT, UPDATE, and DELETE operations are very
  fast in MySQL, but you can obtain better overall
  performance by adding locks around everything
  that does more than about five successive inserts
  or updates. If you do very many successive
  inserts, you could do a LOCK TABLES followed by
  an UNLOCK TABLES once in a while (each 1,000 rows
  or so) to allow other threads access to the
  table. This would still result in a nice
  performance gain.
• INSERT is still much slower for loading data than
  LOAD DATA INFILE, even when using the strategies
  just outlined.

25
Database Systems SQL

• SQL OPTIMIZATION
• GENERAL TIPS
• Use persistent connections to the database to
  avoid connection overhead.
• Run OPTIMIZE TABLE to defragment the table after
  you have deleted a lot of rows from it.
• Try to keep column names simple. For example, in
  a table named customer, use a column name of name
  instead of customer_name. I DISAGREE!
• To make your names portable to other SQL servers,
  you should keep them shorter than 18 characters.

26
Database Systems SQL

• SQL TRANSACTIONS
• It is often important to ensure a series of
  statements occur as an atomic unit or do not
  occur at all.
• For example, if you wanted to transfer money from
  one account to another, you would not want the
  removal of the funds from one account to occur
  without the depositing of those funds in the
  second account. If something happened to prevent
  the depositing of the funds, then you would want
  the withdrawal cancelled.
• This is accomplished through the use of
  transactions.
• The syntax is simple
• START TRANSACTION
• Any SQL commands you wish to execute atomically
• COMMIT

27
Database Systems SQL

• SQL TRANSACTIONS
• Note that some statements can not be rolled back.
  These are typically ones that alter the
  definition of the database/table structure.
• If statements like these are included within a
  transaction, then if another statement fails
  within the transaction, then a full rollback to
  the beginning of the transaction can not occur.
• Transactions can be broken up so that you can
  rollback to a specific point within the
  transaction using the SAVEPOINT command.
• SAVEPOINT identifier
• ROLLBACK WORK TO SAVEPOINT identifier
• RELEASE SAVEPOINT identifier

28
Database Systems SQL

• TRIGGERS
• STORED PROCEDURES
• USER DEFINED VARIABLES
• CONTROL FLOW
• DATE AND TIME FUNCTIONS

29
Database Systems SQL

• VIEWS
• A view in SQL is a great way to present
  information to a user in another way than the
  logical table structure.
• You might do this to limit the access of certain
  fields, i.e. Social Security Number or Date of
  birth.
• You might wish to derive a field. i.e age from
  date of birth.
• You might wish to denormalize a series of tables
  and remove the ID fields so they do not confuse
  someone generating reports from the data.

30
Database Systems SQL

• VIEWS
• The actual syntax has more options than this, but
  a simple form for creating a view is as follows
• CREATE VIEW ViewName AS SelectStatement
• So if you had the following table

You could great a view that would show the
following
BaseballPlayers view
BaseballPlayers table
The BaseballPlayers2 view can be created with the
following statement CREATE VIEW
BaseballPlayers2 AS SELECT LastName, FirstName,
Year(DOB) AS Age FROM BaseballPlayers
31
Database Systems SQL

• VIEWS
• A View name can not be the same name as a table
  that already exists.
• Views must have unique column names.
• Columns selected can be column names or
  expressions. If you create an expression, name
  it!
• A view can be created from many kinds of SELECT
  statements. It can refer to base tables or other
  views. It can use joins, UNION, and subqueries.

32
Database Systems SQL

• VIEWS
• A view definition is subject to the following
  restrictions
• The SELECT statement cannot contain a subquery in
  the FROM clause.
• The SELECT statement cannot refer to system or
  user variables.
• Any table or view referred to in the definition
  must exist.
• The definition cannot refer to a TEMPORARY table,
  and you cannot create a TEMPORARY view.
• The tables named in the view definition must
  already exist.
• You cannot associate a trigger with a view.

33
Database Systems SQL

• VIEWS
• Some views are updatable.
• You can use them in statements such as UPDATE,
  DELETE, or INSERT to update the contents of the
  underlying table.
• For a view to be updatable, there must be a
  one-to-one relationship between the rows in the
  view and the rows in the underlying table.
• There are also certain other constructs that make
  a view non-updatable.
• To be more specific, a view is not updatable if
  it contains any of the following
• Aggregate functions (SUM(), MIN(), MAX(),
  COUNT(), and so forth)
• DISTINCT
• GROUP BY
• HAVING
• UNION or UNION ALL
• Subquery in the select list
• Certain joins (see additional join discussion
  later in this section)
• Non-updatable view in the FROM clause

34
Database Systems SQL

• VIEWS
• A view is not updatable if it contains any of the
  following
• Certain joins (see additional join discussion
  later in this section)
• Non-updatable view in the FROM clause
• A subquery in the WHERE clause that refers to a
  table in the FROM clause
• Refers only to literal values (in this case,
  there is no underlying table to update)
• ALGORITHM TEMPTABLE (use of a temporary table
  always makes a view
• With respect to insertability (being updatable
  with INSERT statements), an updatable view is
  insertable if it also satisfies these additional
  requirements for the view columns
• There must be no duplicate view column names.
• The view must contain all columns in the base
  table that do not have a default value.
• The view columns must be simple column
  references and not derived columns.

35
Database Systems SQL

• DATE/TIME FUNCTIONS
• There are many date and time functions included
  in mySQL to help manipulate temporal values.
• A word of caution, manipulating date/time values
  in the result of a SELECT statement should not
  impact performance, however manipulating
  date/time values within a WHERE clause may have a
  performance impact.
• Specifically, a performance impact will usually
  occur if a date/time function is applied to a
  date/time field from a table as part of a WHERE
  clause.
• SELECT FROM websites WHERE DATEADD(create_date,
  30) CURRENT_DATE()
• However, a date/time function applied to a scalar
  should not have a performance impact.
• SELECT FROM websites WHERE create_date
  CURRENT_DATE()
• When a function is applied to an indexed field,
  the effectiveness of the index is limited.

36
Database Systems SQL

• DATE/TIME FUNCTIONS

37
Database Systems SQL

• DATE/TIME FUNCTIONS

38
Database Systems SQL

• TRIGGERS
• Triggers are database objects associated with
  permanent tables and are activated when a
  particular event occurs.
• Triggers are very useful to insure an action
  occurs at the database level, regardless of what
  the user-program may do.
• The syntax to create a trigger is as follows
• CREATE TRIGGER trigger_name trigger_time
  trigger_event
• ON tbl_name
• FOR EACH ROW trigger_stmt

39
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• In addition to the built-in functions and
  procedures you can create your own using the
  following syntax
• CREATE PROCEDURE ProcedureName (parameter list)
• routine_body
• or
• CREATE FUNCTION FunctionName (parameter list)
• routine_body
• RETURNS type
• The routine_body consists of a valid SQL
  procedure statement.
• A valid SQL procedure statement may be a SELECT
  or INSERT or it can be a compound statement using
  a BEGIN and END.
• Compound statements can contain declarations,
  loops, and other compound structures.

40
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• Stored routines have some limitations
• Stored functions can not do explicit or implicit
  commits or rollbacks, however stored procedures
  can.
• Stored routines can not use load data infile.
• Check the online documentation for more
  restrictions.
• To see which procedures are installed you can
  useSELECT type,db,name,param_list FROM
  mysql.proc

41
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• Simple Example
• CREATE FUNCTION Yankees (s CHAR(20)) RETURNS
  CHAR(50)
• RETURN CONCAT(Yankees , ',s,'!')
• SELECT Yankees(Rock')
• Returns
• Yankees Rock!
• THIS IS SUPPOSED TO WORK, BUT ALAS IT DOES NOT.

42
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• Simple Example
• delimiter //
• CREATE PROCEDURE simpleproc (OUT param1 INT)
• BEGIN
• SELECT COUNT() INTO param1 FROM t
• END
• //
• delimiter
• Delimiter is required because the procedure
  contains a semicolon.

43
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• More complex example to populate the table with
  values
• DROP PROCEDURE IF EXISTS build_tableDELIMITER
  '/'CREATE PROCEDURE build_table()BEGINDECLARE
  i INTEGERDECLARE v INTEGERSET i 1SET v
  100WHILE i lt 125 DOINSERT into mytable VALUES
  (i, v)SET i i 1SET v v 2END
  WHILEEND/DELIMITER ''/

44
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• SELECTING INTO VARIABLES
• SELECT col_name,... INTO var_name,...
  table_expr
• This SELECT syntax stores selected columns
  directly into variables. Therefore, only a single
  row may be retrieved.
• SELECT id,data INTO x,y FROM test.t1 LIMIT 1

45
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• CURSORS
• CREATE PROCEDURE curdemo()
• BEGIN
• DECLARE done INT DEFAULT 0
• DECLARE a CHAR(16)
• DECLARE b,c INT
• DECLARE cur1 CURSOR FOR SELECT id,data FROM
  test.t1
• DECLARE cur2 CURSOR FOR SELECT i FROM test.t2
• DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
  SET done 1
• OPEN cur1
• OPEN cur2
• REPEAT FETCH cur1 INTO a, b
• FETCH cur2 INTO c
• IF NOT done THEN
• IF b lt c THEN
• INSERT INTO test.t3 VALUES (a,b)

46
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• IF STATEMENTS
• IF search_condition THEN statement_list
• ELSEIF search_condition THEN statement_list ...
  
• ELSE statement_list
• END IF

47
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• CASE STATEMENTS
• CASE case_value
• WHEN when_value THEN statement_list
• WHEN when_value THEN statement_list ...
• ELSE statement_list
• END CASE

48
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• LOOP STATEMENTS
• begin_label LOOP
• statement_list
• END LOOP end_label
• LEAVE label
• This statement is used to exit any labeled flow
  control construct.
• It can be used within BEGIN ... END or loop
  constructs (LOOP, REPEAT, WHILE).

49
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• REPEAT STATEMENTS
• begin_label REPEAT
• statement_list
• UNTIL search_condition
• END REPEAT end_label
• The statement list within a REPEAT statement is
  repeated until the search_condition is true.
  Thus, a REPEAT always enters the loop at least
  once. statement_list consists of one or more
  statements.
• A REPEAT statement can be labeled. end_label
  cannot be given unless begin_label also is
  present. If both are present, they must be the
  same.

50
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• More complex example to populate the table with
  values
• ---- Then use the commands--DROP TABLE IF
  EXISTS mytable---- create the tableCREATE
  TABLE mytable (id INTEGER, value INTEGER) ----
  call stored procedure to fill table with
  dataCALL build_table() ---- display table and
  its new dataSELECT from mytable

51
Database Systems SQL

• USER DEFINED FUNCTIONS and PROCEDURES
• ------------
• i v
• ------------
• 1 100
• 2 102
• 3 104
• 4 106
• 5 108
• .
• .
• .
• 120 338
• 121 340
• 122 342
• 123 344
• 124 346
• 125 348

52
Database Systems SQL

• TRIGGERS
• CREATE TRIGGER trigger_name trigger_time
  trigger_event
• ON tbl_name
• FOR EACH ROW trigger_stmt
• trigger_time is the trigger action time. It can
  be BEFORE or AFTER to indicate that the trigger
  activates before or after the statement that
  activated it.
• trigger_event indicates the kind of statement
  that activates the trigger. The trigger_event can
  be one of the following
• INSERT The trigger is activated whenever a new
  row is inserted into the table for example,
  through INSERT, LOAD DATA, and REPLACE
  statements.
• UPDATE The trigger is activated whenever a row
  is modified for example, through UPDATE
  statements.
• DELETE The trigger is activated whenever a row
  is deleted from the table for example, through
  DELETE and REPLACE statements. However, DROP
  TABLE and TRUNCATE statements on the table do not
  activate

53
Database Systems SQL

• TRIGGERS
• Note that you can not have two triggers defined
  for a table with the same trigger action time.
• If you want to execute more than one statement in
  a trigger, use a BEGIN... END Construct.
• Observe the following trigger that when a row is
  deleted from the table, copies an archival
  version of the row to another table
• CREATE TRIGGER test1test2 BEFORE DELETE ON test1
• FOR EACH ROW
• INSERT INTO test2 set test2.C1 old.C1
• Note, old refers to the row that is about to be
  deleted.

54
Database Systems SQL

• TRIGGERS
• I have experienced inconsistent behavior.
• Triggers may be created even if you get an error
  message when defining it.
• Errors when executing a trigger does not
  necessarily impact the operation triggering the
  trigger.
• Sometimes the action occurs even if the error
  message about the trigger fires and makes it seem
  as if it doesnt.
• Be careful with BEFORE triggers. Constraints may
  occur, where an insert will fail, but actions
  from your BEFORE trigger will succeed.

55
Database Systems SQL

• TRIGGERS
• Given the following tables
• After the following trigger is installed
• CREATE TRIGGER test1test2 BEFORE DELETE ON test1
• FOR EACH ROW INSERT INTO test2 set test2.C1
  old.C1,
  test2.C2old.C2, test2.C3old.C3
• and the following SQL query executed
• DELETE FROM test1

test2 table
test1 table
56
Database Systems SQL

• TRIGGERS
• All the data from table test1 is moved into table
  test2 and removed from table test1.
• CREATE TRIGGER test1test2 BEFORE DELETE ON test1
• FOR EACH ROW INSERT INTO test2 set test2.C1
  old.C1,
  test2.C2old.C2, test2.C3old.C3
• DELETE FROM test1

test1 table
test2 table
57
Database Systems SQL

• TRIGGERS
• Imagine you wished to track who made changes and
  inserts into your database. You can do this with
  triggers and a few extra fields added to every
  table.
• While I havent mentioned this before, it is
  standard practice to include the following 4
  fields in every table in your database
• ID_CREATE
• DATE_CREATED
• ID_MODIFIED
• DATE_MODIFIED
• When a record is first inserted into a table, the
  current user ID and date/time is recorded in the
  ID_CREATE and DATE_CREATED fields.
• Similarly, when a record is modified, the current
  user ID and date/time is recorded in the
  ID_MODIFIED and DATE_MODIFIED fields.

58
Database Systems SQL

• TRIGGERS
• When a record is first inserted into a table, the
  current user ID and date/time is recorded in the
  ID_CREATE and DATE_CREATED fields.
• We accomplish this with the following code
• CREATE TRIGGER test4INS BEFORE INSERT ON test4
• FOR EACH ROW
• set new.ID_CREATE USER(),
  new.DATE_CREATED DATE(NOW())
• Notice, the use of BEFORE, we need to set values
  before we insert them into the database.
• Otherwise you will get an error similar to
• ERROR 1362 (HY000) Updating of NEW row is not
  allowed in after trigger

59
Database Systems SQL

• TRIGGERS
• When a record is modified, the current user ID
  and date/time is recorded in the ID_MODIFIED and
  DATE_MODIFIED fields.
• We accomplish this with the following code
• CREATE TRIGGER test4UPD BEFORE UPDATE ON test4
• FOR EACH ROW
• set new.ID_MODIFIED USER(),
  new.DATE_MODIFIED DATE(NOW())
• Notice, the use of BEFORE, we need to set values
  before we insert them into the database.