Stored procedure and Trigger in SQL Server

Tram Ho

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
  • 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 callCALLUse statements in sql like SELECT, UPDATE
Return valueIt is possible to have one or more SELECT results and parameters outReturns a unique value through RETURN
ParametersValue (input) and reference (output) Parameters (IN, OUT, INOUT)Only input parameter values. No tags like IN ..
Call procedure / functionOther procedures and functions can be calledOnly 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.

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

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

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

CASE clause

REPEAT UNTIL clause

WHILE Clause

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 Handler

    • 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.

Store procedure management

  • Displays all existing Stored procedures in all databasesSHOW PROCEDURE STATUS;
  • Displays all existing functions in all databasesSHOW 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 procedureSHOW CREATE PROCEDURE <name procedure>;

For example

  1. 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.

Result

  1. 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

Result

  • Function:

Result

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

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

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)

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.Triggers WHERE Trigger_schema = ‘database_name’
  • Delete a triggerDROP TRIGGER tablename.triggername
  • Change a triggerALTER TRIGGER, SHOW CREATE TRIGGER, hoặc SHOW TRIGGER STATUS.

After reading, do you feel zui?

Share the news now

Source : Viblo