System variables, Stored procedures & functions, triggers & transactions Flashcards
There are two kinds of variables in MySQL, name them
global variables - affect the server overall
session variables - affect individual client connections
How can you see all system variables?
SHOW GLOBAL (or) SESSION VARIABLES;
When do changes to system variables apply?
On sessions started after the changes.
How would you set the global variable ‘port’?
SET GLOBAL port = 2222;
or
SET @@GLOBAL.port = 2222;
What is a user-defined variable
a session are session specific.
written as @var_name and are defined with a SET statement
How can you select from tables into variables?
SELECT Dname, Dnumber
FROM Company.Department LIMIT 1 INTO @x, @y;
What is a local variable?
its a variable scoped inside BEGIN - END as:
DECLARE var_name type(int) [DEFAULT value]
what is a stored procedure/function
named blocks encapsulating SQL statements and procedures
how are stored procedure and functions stored
stored in a memory cache for later calls i.e. it does not need to be reparsed, recompiled and re-optimized every time is is run
what is a stored function
computes and returns ONE value.
– you pass it something and it returns a result
– should be small and fast; does it on the fly.
what is a stored procedure
it is a group of possibly complex set of statements
What are the main differences between a stored procedure and function?
- Procedures can have input/output parameters; functions have only input parameters
- Procedures may return one or more values; functions always one mandatory value
- Procedures allow all DML statement; Functions allow only SELECT statement
- Exception can be handled in procedures but cannot be used in functions
How do you create a stored procedure with an output variable?
DELIMITER // CREATE PROCEDURE calc_value (OUT ending_value INT) BEGIN DECLARE total_value INT; SET total_value = 50; label1: WHILE total_value <= 2100 DO SET total_value = total_value * 2; END WHILE label1; SET ending_value = total_value; END // DELIMITER ;
how can you call stored procedures with output variables and see the variable??
CALL calc_value(@value); SELECT @value;
What is a trigger?
an action that takes place when certain events occur.
When does a trigger trigger?
you can set it to activate before or after the trigger event
What happens when you create multiple triggers on the same event?
by default, triggers with same event and action activate in the order they were created
the order may also be explicitly defined at trigger creation
what is the syntax for creating triggers?
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON tbl_name FOR EACH ROW [{ FOLLOWS | PRECEDES } other_trigger_name]
trigger_body
Can triggers call stored functions and procedures?
yes
When would you use triggers?
– generating some derived column values automatically
– enforcing referential integrity
– event logging and storing information on table access
– synchronous replication of tables
– imposing security authorizations
– preventing invalid transactions
what is DELIMITER //
Something you use to create and run blocks of codes in triggers and stored procedures
what is the difference between a stored procedure and function?
procedures have inputs and outputs
function only has inputs
procedures can return multiple values and functions only one mandatory value
procedures allow DML and functions only SELECT
procedures allow transactions
how do you define a stored procedure?
DELIMITER //
CREATE PROCEDURE decrementWith (IN start_value INT, IN decrement_amount INT ,OUT output INT)
BEGIN
SET output = start_value - decrement_amount;
END //
DELIMITER ;
What is a transaction?
A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database)
