Advanced SQL
Database schema before class: database_day6_end_of_class.sql
Slides from class
SQL script from class
Database notes
Stored procedure
A named collection of procedural and SQL statements stored on the database server. Can have IN/OUT/INOUT parameters.
Stored function
A named collection of procedural and SQL statements stored on the database server that returns a value indicated by the RETURN statement. All parameters are IN parameters.
IN
Parameter that is input only. Its value is not changed inside stored procedure or function. All parameters in functions are IN parameters.
OUT
Parameter that is output only. Returns value to caller. Only valid in stored procedures, not functions.
INOUT
Parameter that is both input and output. Its value can be changed inside stored procedure and returned to caller. Only valid in stored procedures, not functions.
Delimiter
Separation between commands. Normally a semicolon. With stored procedures and functions, declare a temporary delimiter (such as $$ or //) to indicate all SQL commands in the stored procedure or function are considered one command. Change delimiter back to semicolon at end of stored procedure or function.
DETERMINISTIC
The function will always return the same result. Allows database to cache results for future calls because database knows the result will not change.
NON DETERMINISTIC
The function may not always return the same result.
SET @var = value
Sets variable @var to value specified. Example: SET @RestaurantID = 1111; @RestaurantID can then be used in queries.
SELECT INTO
Sets variable @var to value returned by query. Example: SELECT RestaurantName INTO @RestaurantName FROM Restaurants WHERE RestaurantID = @RestaurantID; @RestaurantName is set to the name of the restaurant with RestaurantID = @RestaurantID. Query must return only one row.
Trigger
SQL code that is automatically invoked by the database when a INSERT, UPDATE, or DELETE occurs. MySQL supports only row-level triggers (e.g., if 100 rows deleted, trigger fires 100 times).