In this article we will learn what functions, procedures and triggers in SQL Server are and how they are used and see if they are as scary, dangerous and sublime as everyone else. usually think it or not
Stored procedure
What is a Stored procedure?
- Is a program script (programming scripts) with embedded SQL statements (embedded SQL) saved in a form that has been compiled and executed by the MySQL server,
- SP allows to store application logic in database. When calling the SP for the first time, MySQL creates an execution schedule and caches it in the cache. On subsequent function calls, MySQL uses rebuild a very fast saved execution schedule with reliable performance.
- An SP is a prepared SQL code that you can save, so the code can be reused over and over again.
- So if you have an SQL query that you rewrite over and over, save it as a stored procedure, then simply call it to execute it.
- It is also possible to pass parameters to a stored procedure, so that the stored procedure can operate on the passed parameter value (s).
Why should we use Stored procedure?
- Reducing program code redundancy: Similar code in applications such as adding and updating can be saved on the database side
- Improved SSQL command execution speed
- Maintenance: If there is any change in the database, the code to be changed can be identified in the SP
- Better database security: In highly secure applications, with SP can control data access and enforce centralized security regulations.
Disadvantages of Stored procedure
- Lack of Portability
- It is difficult for SP to switch from one DBMS to another. Requires significant programming and retesting
- SQLServer: T-SQL
- Oracle: PL-SQL
- It is difficult for SP to switch from one DBMS to another. Requires significant programming and retesting
- Download DB Server
- Using SP a lot can overload the MySQL server (SQL Server has to store too many execution plans)
- Limited programming language
- SP programming is not as rich as other development platforms like Java or PHP
- Note: Future versions of MySQL may provide interfaces that allow creation of SPs using external languages, for example Java
Using Stored Procedure when?
- Perform complex mathematical operations, repeat many times
- Project requires strict execution speed. Stored Procedures provide faster execution speed than normal SQL Server commands because Stored Procedure is already stored in SQL server, so it only needs to call one command line to execute Stored Procedure, which increases speed. enforcement.
- During the Stored Procedure creation process, SQL Server has optimized these command lines, which makes the execution speed much higher than normal SQL statements.
No need to use Stored Procedure when?
- Store Procedured makes the debugging process more difficult.
So think of Stored Procedure as the last resort to optimize program execution speed. Small and medium sized projects should prioritize using ORM (Object Relation Mapping) libraries, such as Entity Framework for C #, TypeORM for NodeJs.
Procedures and Functions are the differences
| Procedure (PROCEDURE) | FUNCTION | |
|---|---|---|
| How to call | CALL | Use statements in sql like SELECT, UPDATE |
| Return value | It is possible to have one or more SELECT results and parameters out | Returns a unique value through RETURN |
| Parameters | Value (input) and reference (output) Parameters (IN, OUT, INOUT) | Only input parameter values. No tags like IN .. |
| Call procedure / function | Other procedures and functions can be called | Only other functions can be called |
Procedure / Function Creation Syntax
CREATE FUNCTION name ([parameterlist]) RETURNS datatype [options] sqlcode
CREATE PROCEDURE name ([parameterlist]) [options] sqlcode
For example
Create a procedure named uspActorList which returns the first_name list, last_name of actors sorted by first_name.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE PROCEDURE uspActorList AS BEGIN SELECT first_name, last_price FROM actor ORDER BY first_name; END; |
Create a procedure named uspActorList whose input parameter is max_id which returns the list of first_name, last_name of actors sorted by first_name and with id <max_id
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE PROCEDURE actorList(IN max_id INT) AS BEGIN SELECT first_name, last_price FROM actor WHERE actor_id < max_id ORDER BY first_name; END; |
Create a function named film_in_stock that returns quantity with input (IN) p_film_id, p_store_id, output (OUT) as p_film_count
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATA BEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count; END $$ |
Other commands
DELIMITER $$
- Commonly used when defining functions, procedures, and triggers (where you must use multiple statements). The
$$sign is used to specify the beginning of the entire procedure, inside individual statements terminated by;. That way, when the code is run in the mysql client, the client can tell where the entire procedure ends and execute it as a unit instead of executing individual statements internally. - Note that the DELIMITER keyword is just a function of the command line mysql client (and some other clients) and not a structure of regular SQL. It won’t work if you pass it over a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods for specifying non-default separators.
Assign a value to the variable
Use SET or SELECT INTO.
For example:
SET @model_year = 2018;
Call the procedure:
Call film_in_stock(1,1, @film_count);
Select @film_count;
The IF THEN Clause
1 2 3 4 5 6 7 | IF condition THEN commands; [ELSEIF condition THEN commands;] [ELSEcommands;] END IF; |
CASE clause
1 2 3 4 5 6 | CASE expression WHEN value1 THEN commands; [WHEN value2 THEN commands;] [ELSE commands;] END CASE; |
REPEAT UNTIL clause
1 2 3 4 5 | [loopname:] REPEAT commands; UNTIL condition END REPEAT [loopname]; |
WHILE Clause
1 2 3 4 | [loopname:] WHILE condition DO commands; END WHILE [loopname]; |
LEAVE clause
- LEAVE uses exit loop
- LEAVE can also be used to exit BEGIN-END
Similar to the BREAK clause in other programming languages
Handling errors through Handlers
- It is always possible that Store Procedure crashes while executing SQL commands. MySQL provides error handling techniques through Handler
- A handler needs to define after declaring variables, pointers and conditions, but before SQL statements
- Syntax declare a Handler12`DECLARE type HANDLER FOR condition1, condition2, condition3, ... statement; `
- type: CONTINUE or EXIT
- condition (s): The conditions in which the handler will be called (Ex: Not found, SqlException, ..)
- statement: The statement will execute when a condition occurs
For example: If you do not find (NOT FOUND) customer_id is the customer that has not returned the rental disk with the id stored in the store as p_inventory_id, NULL will be returned.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT READS SQL DATA BEGIN DECLARE v_customer_id INT; DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL; SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id; RETURN v_customer_id; END $$ |
Store procedure management
- Displays all existing Stored procedures in all databases
SHOW PROCEDURE STATUS; - Displays all existing functions in all databases
SHOW FUNCTION STATUS; - If you only want to display the stored procedure of a particular database use the following statement:
SHOW PROCEDURE STATUS WHERE Db = 'db_name'; - Similar to functions:
SHOW FUNCTION STATUS WHERE Db = 'db_name';SHOW FUNCTION STATUS LIKE 'repeat%';SHOW PROCEDURE STATUS LIKE 'film%'; - Displays specific information of a procedure
SHOW CREATE PROCEDURE <name procedure>;
For example
- Create a stored procedure named displayFilmInfo that takes category_id and language_id as parameters:
- If category_id and language_id are specified, return the information for movies with category_id and language using the values passed.
- If 0 is passed as a parameter to language_id, then return information for movies whose category_id is the parameter passed.
- If 0 is passed as an argument to category_id then return the movies with language_id as the parameter passed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DELIMITER $$ DROP PROCEDURE IF EXISTS displayFilmInfo; CREATE PROCEDURE displayFilmInfo(IN p_category_id INT, IN p_language_id INT) BEGIN IF(p_language_id = 0) THEN SELECT film.film_id FROM film JOIN film_category ON film.film_id = film_category.film_id AND film_category.category_id = p_category_id; ELSEIF (p_category_id = 0) THEN SELECT film.film_id FROM film WHERE film.language_id = p_language_id; ELSEIF (p_language_id > 0 AND p_category_id > 0) THEN SELECT film.film_id FROM film JOIN film_category ON film.film_id = film_category.film_id AND film_category.category_id = p_category_id WHERE film.language_id = p_language_id; END IF; END $$ DELIMITER ; # thực thi procedure CALL displayFilmInfo(1,1); |
Result
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> CALL displayFilmInfo(1,1); +---------+ | film_id | +---------+ | 19 | | 21 | | 29 | | 38 | | 56 | | 67 | .... 64 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
- Write a function that calculates the total number of rental films for each month and year. Where the input parameter is the code of the row, month, year. Then use the function in the SELECT command to show the stores and the total number of movies rented in the store for February 2006.
- Procedure
1 2 3 4 5 6 7 8 9 10 11 12 13 | DELIMITER $$ DROP PROCEDURE IF EXISTS total_movies; CREATE PROCEDURE total_movies(IN p_store_id INT, IN p_month INT, IN p_year INT) BEGIN SELECT inventory.store_id as store,count(rental.inventory_id) FROM inventory JOIN rental ON inventory.inventory_id = rental.inventory_id AND month(rental.rental_date)=p_month AND year(rental.rental_date)=p_year GROUP BY inventory.store_id HAVING inventory.store_id=p_store_id; END$$ DELIMITER ; call total_movies(1,2,2006); |
Result
1 2 3 4 5 6 7 8 9 10 11 | mysql> call total_movies(1,2,2006); +-------+----------------------------+ | store | count(rental.inventory_id) | +-------+----------------------------+ | 1 | 92 | +-------+----------------------------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.03 sec) |
- Function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DELIMITER $$ DROP FUNCTION IF EXISTS total_inventory; CREATE FUNCTION total_inventory (p_store_id INT, p_month INT, p_year INT) RETURNS int BEGIN DECLARE v_out INT; SELECT count(rental.inventory_id) INTO v_out FROM inventory JOIN rental ON inventory.inventory_id = rental.inventory_id AND month(rental.rental_date)=p_month AND year(rental.rental_date)=p_year GROUP BY inventory.store_id HAVING inventory.store_id=p_store_id; return v_out; END$$ DELIMITER ; select store_id, total_inventory(store_id,2,2006) from store; |
Result
1 2 3 4 5 6 7 8 9 | mysql> select store_id, total_inventory(store_id,2,2006) from store; +----------+----------------------------------+ | store_id | total_inventory(store_id,2,2006) | +----------+----------------------------------+ | 1 | 92 | | 2 | 90 | +----------+----------------------------------+ 2 rows in set (0.07 sec) |
Trigger
What is a trigger?
- Triggers are the process of automatically executing SQL or SP statements after or before INSERT, UPDATE, or DELETE statements.
- Applications may include: saving changes or updating data in other tables.
- The trigger runs after each table update statement so that the load can be added to the database
Syntax
1 2 3 | CREATE TRIGGER name BEFORE | AFTER INSERT |UPDATE | DELETE ON tablename FOR EACH ROW sql-code |
The internal command syntax is similar to SP
In the trigger, the code can access the columns of the current record
- OLD.columnname returns the contents of the record before it was changed or deleted (UPDATE, DELETE)
- NEW.columnname returns the contents of a new or replacement record (INSERT, UPDATE)
For example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DELIMITER $$ CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN IF (old.title != new.title) or (old.description != new.description) THEN UPDATE film_text SET title=new.title, description=new.description, film_id=new.film_id WHERE film_id=old.film_id; END IF; END;; DELIMITER ; |
Create a trigger on the payment table, each time adding or editing the payment table will update that information with information about the time to add, edit the payment_log table (create more)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | drop table payment_log; show create table payment; CREATE TABLE payment_log ( payment_id smallint(5) DEFAULT NULL, customer_id smallint(5) DEFAULT NULL , staff_id tinyint(3) unsigned DEFAULT NULL, rental_id int(11) DEFAULT NULL, amount decimal(5,2) DEFAULT NULL, payment_date datetime DEFAULT NULL, changedate DATETIME DEFAULT NULL, action VARCHAR(50) DEFAULT NULL ); DELIMITER $$ DROP TRIGGER if exists update_payment; CREATE TRIGGER update_payment AFTER UPDATE ON payment FOR EACH ROW BEGIN INSERT INTO payment_log SET action = 'update', payment_id = OLD.payment_id, customer_id = OLD.customer_id, staff_id = OLD.staff_id, rental_id = OLD.rental_id, amount = OLD.amount, payment_date = OLD.payment_date, changedate = NOW(); END$$ DELIMITER ; |
Trigger management
- Stored trigger
- Trigger is stored as plain text file in the database folder as follows: /data_folder/database_name/table_name.trg,
- To display the triggers are associated with a data tableSELECT * FROM
Information_Schema.TriggersWHERE Trigger_schema = ‘database_name’ - Delete a trigger
DROP TRIGGER tablename.triggername - Change a trigger
ALTER TRIGGER, SHOW CREATE TRIGGER, hoặc SHOW TRIGGER STATUS.
After reading, do you feel zui?