Maria DB: Programa

Los datos expuestos en este blog, son solo de índole informativo. Por favor realiza siempre una copia de seguridad antes de realizar cualquier cambio en tu proyecto.

Maria DB: Programa

Este tutorial de MariaDB explica cómo Crear y eliminar programas En MariaDB, con sintaxis y ejemplos.

¿Qué es un programa en MariaDB?

En MariaDB, los procedimientos son procedimientos almacenados a los que se pueden pasar parámetros. No devuelve un valor como una función.

crear un programa

Así como puede crear procedimientos en otros idiomas, también puede crear sus propios procedimientos en MariaDB. Miremos más de cerca.

sintaxis

La sintaxis para crear un procedimiento en MariaDB es:

crear 
[ DEFINER = { CURRENT_USER | user_name } ] 
PROCEDIMIENTO nombre del procedimiento [ (parameter datatype [, parameter datatype]) ]

[ LANGUAGE SQL
| DETERMINISTIC
| NOT DETERMINISTIC
| { CONTAINS SQL 
  | NO SQL
  | READS SQL DATA
  | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'comment_value'

BEGIN

   declaration_section

   executable_section

END;

DEFINER clause
Optional. If not specified, the definer is the user that created the procedure. If you wish to specify a different definer, you must include the DEFINER clause where user_name is the definer for the procedure.
procedure_name
The name to assign to this procedure in MariaDB.
parameter

One or more parameters passed into the procedure. When creating a procedure, there are three types of parameters that can be declared:

  1. IN – The parameter can be referenced by the procedure. The value of the parameter can not be overwritten by the procedure.
  2. OUT – The parameter can not be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
  3. IN OUT – The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.

LANGUAGE SQL
It is in the syntax for portability but will have no impact on the function.
DETERMINISTIC
It means that the function will always return one result given a set of input parameters.
NOT DETERMINISTIC
It means that the function may return a different result given a set of input parameters. The result may be affected by table data, random numbers or server variables.
CONTAINS SQL
It is the default. It is an informative clause that tells MariaDB that the function contains SQL, but the database does not verify that this is true.
NO SQL
It is an informative clause that is not used and will have no impact on the function.
READS SQL DATA
It is an informative clause that tells MariaDB that the function will read data using SELECT statements but does not modify any data.
MODIFIES SQL DATA
It is an informative clause that tells MariaDB that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.
declaration_section
The place in the procedure where you declare local variables.
executable_section
The place in the procedure where you enter the code for the procedure.

Example

Let’s look at an example that shows how to create a procedure in MariaDB:

DELIMITER //

CREATE procedure CalcValue ( OUT ending_value INT )
DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 50;

   label1: WHILE total_value <= 3000 DO
     SET total_value = total_value * 2;
   END WHILE label1;

   SET ending_value = total_value;

END; //

DELIMITER ;

You could then reference your new procedure as follows:

CALL CalcValue (@variable_name);

SELECT @variable_name;

Drop procedure

Once you have created your procedure in MariaDB, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in MariaDB is:

DROP procedure [ IF EXISTS ] nombre del programa;

nombre_procedimiento El nombre del procedimiento que se va a eliminar.

ejemplo

Veamos un ejemplo de cómo eliminar un procedimiento en MariaDB.

Por ejemplo:

DROP procedure CalcValue;

Este ejemplo elimina el procedimiento denominado CalcValue.

(Visited 14 times, 1 visits today)