Mysql 4th Edition By Paul DuBois - Part2

Yesterday i started reading Mysql 4TH Edition By Paul DuBois

I wanted to share few important quotations found from this book from the next 2 chapters (3-4) .

1) Type Conversion

Whenever a value of one type is used in a context that requires a value of another type,MySQL performs type conversion automatically according to the kind of operation you’re performing. Conversion may occur for any of the following reasons:

a) Conversion of operands to a type appropriate for evaluation of an operator
b) Conversion of a function argument to a type expected by the function
c) Conversion of a value for assignment into a table column that has a different type

2) Stored Functions and Procedures

Stored functions calculate a value to be returned to the caller for use in expressions, just like built-in functions such as COS() or HEX(). Stored procedures are executed as standalone operations using the CALL statement rather than in expressions. Use a procedure if you need only to perform a computation to produce an effect or action without returning a value, or if the computation produces result sets (which a function is not allowed to do).These are guidelines, not hard and fast rules. For example, if you need to return more than one value, you cannot use a function. But you may be able to use a procedure, because procedures support parameter types that can have their values set when the procedure executes, such that those values can be accessed by the caller after the procedure
finishes.

To create a stored function or procedure, use a CREATE FUNCTION or CREATE PROCEDURE statement

Example:

Function Demo

DELIMITER $$
DROP FUNCTION IF EXISTS `railways`.`demofunc` $$
CREATE FUNCTION `railways`.`demofunc` (p_year INT) RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM president WHERE YEAR(birth) = p_year);
END $$
DELIMITER ;


Procedure Demo

DELIMITER $$

DROP PROCEDURE IF EXISTS `railways`.`greetings` $$
CREATE PROCEDURE `railways`.`greetings` ()
BEGIN

# 77 = 16 for username + 60 for hostname + 1 for '@'
DECLARE user CHAR(77) CHARACTER SET utf8;
SET user = (SELECT CURRENT_USER());
IF INSTR(user,'@') > 0 THEN
SET user = SUBSTRING_INDEX(user,'@',1);
END IF;
IF user = '' THEN # anonymous user
SET user = 'earthling';
END IF;
SELECT CONCAT('Greetings, ',user, '!') AS greeting;

END $$

DELIMITER ;

3) Triggers

A trigger is a stored program that is associated with a particular table and is defined to activate for INSERT, DELETE, or UPDATE statements for that table.A trigger can be set to activate either before or after each row processed by the statement.The trigger definition includes a statement that executes when the trigger activates.
The following list describes some of the benefits that triggers provide:

a) A trigger can examine or change new data values to be inserted or used to update a row.This enables you to enforce data integrity constraints, such as verifying that a percentage is a value from 0 to 100. It also makes it possible to perform input data filtering.

b) A trigger can supply default values for a column based on an expression.This enables you to work around the restriction that default values in column definitions must be constants.

c) A trigger can examine the current contents of a row before it is deleted or updated. This capability can be exploited to perform logging of changes to existing rows, for example.

To create a trigger, use the CREATE TRIGGER statement.The definition indicates the particular type of statement for which the trigger activates (INSERT, UPDATE, or DELETE), and whether it activates before or after rows are modified.The basic syntax for trigger creation looks like this:

CREATE TRIGGER trigger_name # the trigger name
{BEFORE | AFTER} # when the trigger activates
{INSERT | UPDATE | DELETE} # what statement activates it
ON tbl_name # the associated table
FOR EACH ROW trigger_stmt; # what the trigger does


About the Author

Paul DuBois is a writer, database administrator, and leader in the open source and MySQL communities. He has contributed to the online documentation for MySQL and is the author of MySQL and Perl for the Web (New Riders), MySQL Cookbook, Using csh and tcsh, and Software Portability with imake (O’Reilly). He is currently a technical writer with the MySQL documentation team at Sun Microsystems.