Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Master stored procedures in MariaDB Server. This section covers creating, executing, and managing these powerful routines to encapsulate complex logic and improve application performance.
Automate tasks in MariaDB Server with stored routines. Learn to create and manage stored procedures and functions for enhanced database efficiency and code reusability.
Utilize stored functions in MariaDB Server. This section details creating, using, and managing user-defined functions to extend SQL capabilities and streamline data manipulation.
The ALTER PROCEDURE statement modifies the characteristics of an existing stored procedure, such as its security context or comment, without changing its logic.
This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement. To make such changes, you must drop and re-create the procedure using .
You must have the ALTER ROUTINE privilege for the procedure. By default, that privilege is granted automatically to the procedure creator. See .
This page is licensed: GPLv2, originally from
Stored routines have specific restrictions, such as prohibiting certain SQL statements (e.g., LOAD DATA) and disallowing result sets in functions.
The following SQL statements are not permitted inside any stored routines (stored functions, stored procedures, events or triggers).
ALTER VIEW; you can use CREATE OR REPLACE VIEW instead.
LOAD DATA and LOAD TABLE.
is permitted, but the statement is handled as a regular .
and .
References to within prepared statements inside a stored routine (use instead).
is treated as the beginning of a block, not a transaction, so needs to be used instead.
The number of permitted recursive calls is limited to . If this variable is 0 (default), recursivity is disabled. The limit does not apply to stored functions.
Most statements that are not permitted in prepared statements are not permitted in stored programs. See for a list of statements that can be used. , and are exceptions, and may be used in stored routines.
There are also further limitations specific to the kind of stored routine.
Note that, if a stored program calls another stored program, the latter will inherit the caller's limitations. So, for example, if a stored procedure is called by a stored function, that stored procedure will not be able to produce a result set, because stored functions can't do this.
This page is licensed: CC BY-SA / Gnu FDL
The DROP PROCEDURE statement permanently removes a stored procedure and its associated privileges from the database.
ALTER PROCEDURE proc_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'ALTER ROUTINEEXECUTE are granted automatically to the routine creator - see The IF EXISTS clause is a MySQL/MariaDB extension. It prevents an error from occurring if the procedure or function does not exist. ANOTE is produced that can be viewed with SHOW WARNINGS.
While this statement takes effect immediately, threads which are executing a procedure can continue execution.
IF EXISTS:
This page is licensed: GPLv2, originally from fill_help_tables.sql
ALTER ROUTINE ALTER ROUTINE and EXECUTE privileges are granted automatically to the routine creator - see The IF EXISTS clause is a MySQL/MariaDB extension. It prevents an error from occurring if the function does not exist. ANOTE is produced that can be viewed with SHOW WARNINGS.
For dropping a user-defined functions (UDF), see DROP FUNCTION UDF.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Stored procedures are precompiled collections of SQL statements stored on the server, allowing for encapsulated logic, parameterized execution, and improved application performance.
A Stored Procedure is a routine invoked with a CALL statement. It may have input parameters, output parameters and parameters that are both input parameters and output parameters.
Here's a skeleton example to see a stored procedure in action:
First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. The procedure is named Reset_animal_count. MODIFIES SQL DATA indicates that the procedure will perform a write action of sorts, and modify data. It's for advisory purposes only. Finally, there's the actual SQL statement - an UPDATE.
A more complex example, with input parameters, from an actual procedure used by banks:
See for full syntax details.
Security is a key reason. Banks commonly use stored procedures so that applications and users don't have direct access to the tables. Stored procedures are also useful in an environment where multiple languages and clients are all used to perform the same operations.
To find which stored functions are running on the server, use .
or query the in the INFORMATION_SCHEMA database directly:
To find out what the stored procedure does, use .
To drop a stored procedure, use the statement.
To change the characteristics of a stored procedure, use . However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using (which retains existing privileges), or DROP PROCEDURE followed CREATE PROCEDURE .
See the article .
This page is licensed: CC BY-SA / Gnu FDL
A Stored Function is a set of SQL statements that can be called by name, accepts parameters, and returns a single value, enhancing SQL with custom logic.
A Stored Function is a defined function that is called from within an SQL statement like a regular function and returns a single value.
Here's a skeleton example to see a stored function in action:
First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. See Delimiters in the mariadb client for more. Then the function is named FortyTwo and defined to return a tinyin. The DETERMINISTIC keyword is not necessary in all cases (although if binary logging is on, leaving it out will throw an error), and is to help the query optimizer choose a query plan. A deterministic function is one that, given the same arguments, will always return the same result.
Next, the function body is placed between statements. It declares a tinyint, X, which is simply set to 42, and this is the result returned.
Of course, a function that doesn't take any arguments is of little use. Here's a more complex example:
This function takes an argument, price which is defined as a DECIMAL, and returns an INT.
Take a look at the page for more details.
It is also possible to create .
To find which stored functions are running on the server, use :
Alternatively, query the in the INFORMATION_SCHEMA database directly:
To find out what the stored function does, use :
To drop a stored function, use the statement.
To change the characteristics of a stored function, use . Note that you cannot change the parameters or body of a stored function using this statement; to make such changes, you must drop and re-create the function using DROP FUNCTION and CREATE FUNCTION.
See the article .
This page is licensed: CC BY-SA / Gnu FDL
This page details the restrictions on stored functions, such as the inability to return result sets or use transaction control statements.
The following restrictions apply to stored functions.
All of the restrictions listed in Stored Routine Limitations.
Any statements that return a result set are not permitted. For example, a regular SELECTs is not permitted, but a SELECT INTO is. A cursor and FETCH statement is permitted.
FLUSH statements are not permitted.
Statements that perform explicit or implicit commits or rollbacks are not permitted.
Cannot be used recursively.
Cannot make changes to a table that is already in use (reading or writing) by the statement invoking the stored function.
Cannot refer to a temporary table multiple times under different aliases, even in different statements.
ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT statement which are in a stored function cannot refer to a savepoint which has been defined out of the current function.
Prepared statements (, , ) cannot be used, and therefore nor can statements be constructed as strings and then executed.
This page is licensed: CC BY-SA / Gnu FDL
ALTER PROCEDURE simpleproc SQL SECURITY INVOKER;DROP PROCEDURE [IF EXISTS] sp_nameDROP PROCEDURE simpleproc;DROP PROCEDURE simpleproc;
ERROR 1305 (42000): PROCEDURE test.simpleproc does not exist
DROP PROCEDURE IF EXISTS simpleproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1305 | PROCEDURE test.simpleproc does not exist |
+-------+------+------------------------------------------+DROP FUNCTION [IF EXISTS] f_nameDROP FUNCTION hello;
Query OK, 0 rows affected (0.042 sec)
DROP FUNCTION hello;
ERROR 1305 (42000): FUNCTION test.hello does not exist
DROP FUNCTION IF EXISTS hello;
Query OK, 0 rows affected, 1 warning (0.000 sec)
SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1305 | FUNCTION test.hello does not exist |
+-------+------+------------------------------------+DELIMITER //
CREATE PROCEDURE Reset_animal_count()
MODIFIES SQL DATA
UPDATE animal_count SET animals = 0;
//
DELIMITER ;DELIMITER //
CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
DECLARE x TINYINT;
SET x = 42;
RETURN x;
END
//
DELIMITER ;Creates a stored procedure. By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it.
When the routine is invoked, an implicit USE`` db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. USE statements within stored routines are disallowed.
When a stored procedure has been created, you invoke it by using the CALL statement (see CALL).
To execute the CREATE PROCEDURE statement, it is necessary to have the CREATE ROUTINE privilege. By default, MariaDB automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. See also Stored Routine Privileges.
The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time, as described here. Requires the SET USER privilege.
If the routine name is the same as the name of a built-in SQL function, you must use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs. This is also true when you invoke the routine later. For this reason, we suggest that it is better to avoid re-using the names of existing SQL functions for your own stored routines.
The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. It is always allowable to have spaces after a routine name, regardless of whether IGNORE_SPACE is enabled.
The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.
Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.
For valid identifiers to use as procedure names, see Identifier Names.
One can't use OR REPLACE together with IF EXISTS.
If the IF NOT EXISTS clause is used, then the procedure will only be created if a procedure with the same name does not already exist. If the procedure already exists, then a warning are triggered by default.
Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.
An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.
For each OUT or INOUT parameter, pass a user-defined variable in theCALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure
from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.
As of , each parameter can be defined as having a default value or expression. This can be useful if needing to add extra parameters to a procedure which is already in use.
DETERMINISTIC and NOT DETERMINISTIC apply only to functions. Specifying DETERMINISTC or NON-DETERMINISTIC in procedures has no effect. The default value is NOT DETERMINISTIC. Functions are DETERMINISTIC when they always return the same value for the same input. For example, a truncate or substring function. Any function involving data, therefore, is always NOT DETERMINISTIC.
CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA are informative clauses that tell the server what the function does. MariaDB does not check in any way whether the specified clause is correct. If none of these clauses are specified, CONTAINS SQL is used by default.
MODIFIES SQL DATA means that the function contains statements that may modify data stored in databases. This happens if the function contains statements like DELETE, UPDATE, INSERT, REPLACE or DDL.
READS SQL DATA means that the function reads data stored in databases but does not modify any data. This happens if SELECT statements are used, but there no write operations are executed.
CONTAINS SQL means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include SET or DO.
NO SQL means nothing, because MariaDB does not currently support any language other than SQL.
The routine_body consists of a valid SQL procedure statement. This can be a simple statement such as SELECT or INSERT, or it can be a compound statement written using BEGIN and END. Compound statements can contain declarations, loops, and other control structure statements. See Programmatic and Compound Statements for syntax details.
MariaDB allows routines to contain DDL statements, such as CREATE and DROP. MariaDB also allows stored procedures (but not stored functions) to contain SQL transaction statements such as COMMIT.
For additional information about statements that are not allowed in stored routines, see Stored Routine Limitations.
For information about invoking stored procedures from within programs written in a language that has a MariaDB/MySQL interface, see CALL.
If the optional OR REPLACE clause is used, it acts as a shortcut for the following statements, with the exception that any existing privileges for the procedure are not dropped:
MariaDB stores the sql_mode system variable setting that is in effect at the time a routine is created and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.
Procedure parameters can be declared with any character set/collation. If the character set and collation are not specifically set, the database defaults at the time of creation are used. If the database defaults change at a later stage, the stored procedure character set/collation will not be changed at the same time; the stored procedure needs to be dropped and recreated to ensure the same character set/collation as the database is used.
A subset of Oracle's PL/SQL language is supported in addition to the traditional SQL/PSM-based MariaDB syntax. See for details on changes when running Oracle mode.
The following example shows a simple stored procedure that uses an OUT parameter. It uses the DELIMITER command to set a new delimiter for the duration of the process — see Delimiters in the mariadb client.
Character set and collation:
CREATE OR REPLACE:
This page is licensed: CC BY-SA / Gnu FDL
CREATE
[OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type [DEFAULT value or expression]
type:
Any valid MariaDB data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statementStored Aggregate Functions allow users to create custom aggregate functions that process a sequence of rows and return a single summary result.
Aggregate functions are functions that are computed over a sequence of rows and return one result for the sequence of rows.
Creating a custom aggregate function is done using the CREATE FUNCTION statement with two main differences:
The addition of the AGGREGATE keyword, so CREATE AGGREGATE FUNCTION
The FETCH GROUP NEXT ROW instruction inside the loop
Oracle PL/SQL compatibility using SQL/PL is provided
Stored aggregate functions were a project by Varun Gupta.
First a simplified example:
A non-trivial example that cannot easily be rewritten using existing functions:
This uses the same marks table as created above.
This page is licensed: CC BY-SA / Gnu FDL
When binary logging is enabled, stored routines may require special handling (like SUPER privileges) if they are non-deterministic, to ensure consistent replication.
Binary logging can be row-based, statement-based, or a mix of the two. See Binary Log Formats for more details on the formats. If logging is statement-based, it is possible that a statement will have different effects on the master and on the slave.
Stored routines are particularly prone to this, for two main reasons:
stored routines can be non-deterministic, in other words non-repeatable, and therefore have different results each time they are run.
the slave thread executing the stored routine on the slave holds full privileges, while this may not be the case when the routine was run on the master.
The problems with replication will only occur with statement-based logging. If row-based logging is used, since changes are made to rows based on the master's rows, there is no possibility of the slave and master getting out of sync.
By default, with row-based replication, triggers run on the master, and the effects of their executions are replicated to the slaves. However, it is possible to run triggers on the slaves. See .
If the following criteria are met, then there are some limitations on whether stored routines can be created:
The is enabled, and the system variable is set to STATEMENT. See for more information.
The is set to OFF, which is the default value.
If the above criteria are met, then the following limitations apply:
When a is created, it must be declared as either DETERMINISTIC, NO SQL or READS SQL DATA, or else an error will occur. MariaDB cannot check whether a function is deterministic, and relies on the correct definition being used.
To create or modify a stored function, a user requires the SUPER privilege as well as the regular privileges. See for these details.
A deterministic function:
A non-deterministic function, since it uses the function:
This page is licensed: CC BY-SA / Gnu FDL
This page explains the privileges required to create, alter, execute, and drop stored routines, including the automatic grants for creators.
It's important to give careful thought to the privileges associated with stored functions and stored procedures. The following is an explanation of how they work.
To create a stored routine, the CREATE ROUTINE privilege is needed. The SUPER privilege is required if a DEFINER is declared that's not the creator's account (see below). The SUPER privilege is also required if statement-based binary logging is used. See for more details.
To make changes to, or drop, a stored routine, the privilege is needed. The creator of a routine is temporarily granted this privilege if they attempt to change or drop a routine they created, unless the variable is set to 0 (it defaults to 1).
The SUPER privilege is also required if statement-based binary logging is used. See for more details.
To run a stored routine, the privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the variable is set to 0.
The (by default DEFINER) specifies what privileges are used when a routine is called. If SQL SECURITY is INVOKER, the function body are evaluated using the privileges of the user calling the function. If SQL SECURITY is DEFINER, the function body is always evaluated using the privileges of the definer account.
If left out, the DEFINER is treated as the account that created the stored routine or view. If the account creating the routine has the SUPER privilege, another account can be specified as the DEFINER.
This clause specifies the context the stored routine or view will run as. It can take two values - DEFINER or INVOKER. DEFINER is the account specified as the DEFINER when the stored routine or view was created (see the section above). INVOKER is the account invoking the routine or view.
As an example, let's assume a routine, created by a superuser who's specified as the DEFINER, deletes all records from a table. If SQL SECURITY=DEFINER, anyone running the routine, regardless of whether they have delete privileges, are able to delete the records. If SQL SECURITY = INVOKER, the routine will only delete the records if the account invoking the routine has permission to do so.
INVOKER is usually less risky, as a user cannot perform any operations they're normally unable to. However, it's not uncommon for accounts to have relatively limited permissions, but be specifically granted access to routines, which are then invoked in the DEFINER context.
All privileges that are specific to a stored routine are dropped when a or DROP ROUTINE is run. However, if a or is used to drop and replace and the routine, any privileges specific to that routine will not be dropped.
- maria.com post on what to do after you've dropped a user, and now want to change the DEFINER on all database objects that currently have it set to this dropped user.
This page is licensed: CC BY-SA / Gnu FDL
SELECT FortyTwo();
+------------+
| FortyTwo() |
+------------+
| 42 |
+------------+DELIMITER //
CREATE FUNCTION VatCents(price DECIMAL(10,2)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE x INT;
SET x = price * 114;
RETURN x;
END //
Query OK, 0 rows affected (0.04 sec)
DELIMITER ;SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
Db: test
Name: VatCents
Type: FUNCTION
Definer: root@localhost
Modified: 2013-06-01 12:40:31
Created: 2013-06-01 12:40:31
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE
ROUTINE_TYPE='FUNCTION';
+--------------+
| ROUTINE_NAME |
+--------------+
| VatCents |
+--------------+SHOW CREATE FUNCTION VatCents\G
*************************** 1. row ***************************
Function: VatCents
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `VatCents`(price DECIMAL(10,2)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE x INT;
SET x = price * 114;
RETURN x;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciDROP FUNCTION FortyTwo;CREATE
[OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MariaDB data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statementDROP PROCEDURE IF EXISTS name;
CREATE PROCEDURE name ...;DELIMITER //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END;
//
DELIMITER ;
CALL simpleproc(@a);
SELECT @a;
+------+
| @a |
+------+
| 1 |
+------+DELIMITER //
CREATE PROCEDURE simpleproc2 (
OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
BEGIN
SELECT CONCAT('a'),f1 INTO param1 FROM t;
END;
//
DELIMITER ;DELIMITER //
CREATE PROCEDURE simpleproc2 (
OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
BEGIN
SELECT CONCAT('a'),f1 INTO param1 FROM t;
END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE simpleproc2 (
OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
BEGIN
SELECT CONCAT('a'),f1 INTO param1 FROM t;
END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists
DELIMITER ;
Query OK, 0 rows affected (0.03 sec)SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 101 |
+---------+
CALL Reset_animal_count();
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 0 |
+---------+CREATE PROCEDURE
Withdraw /* Routine name */
(parameter_amount DECIMAL(6,2), /* Parameter list */
parameter_teller_id INTEGER,
parameter_customer_id INTEGER)
MODIFIES SQL DATA /* Data access clause */
BEGIN /* Routine body */
UPDATE Customers
SET balance = balance - parameter_amount
WHERE customer_id = parameter_customer_id;
UPDATE Tellers
SET cash_on_hand = cash_on_hand + parameter_amount
WHERE teller_id = parameter_teller_id;
INSERT INTO Transactions VALUES (
parameter_customer_id,
parameter_teller_id,
parameter_amount);
END;SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
Db: test
Name: Reset_animal_count
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-06-03 08:55:03
Created: 2013-06-03 08:55:03
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciSELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME |
+--------------------+
| Reset_animal_count |
+--------------------+SHOW CREATE PROCEDURE Reset_animal_count\G
*************************** 1. row ***************************
Procedure: Reset_animal_count
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Reset_animal_count`()
MODIFIES SQL DATA
UPDATE animal_count SET animals = 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciDROP PROCEDURE Reset_animal_count();DEFINERThe creator of a routine is the account that ran the CREATE FUNCTION or CREATE PROCEDURE statement, regardless of whether a DEFINER is provided. The definer is by default the creator unless otherwise specified.
The server automatically changes the privileges in the mysql.proc table as required, but will not look out for manual changes.
Triggers can also update data. The slave uses the DEFINER attribute to determine which user is taken to have created the trigger.
Note that the above limitations do no apply to stored procedures or to events.
The main idea of DBMS_OUTPUT is:
Messages submitted by DBMS_OUTPUT.PUT_LINE() are not sent to the client until the sending subprogram (or trigger) completes. There is no a way to flush output during the execution of a procedure.
Therefore, lines are collected into a server side buffer, which, at the end of the current user statement, can be fetched to the client side using another SQL statement. Then, they can be read using a regular MariaDB Connector-C API. No changes in the client-protocol are needed.
Oracle's SQLPlus uses the procedure DBMS_PACKAGE.GET_LINES() to fetch the output to the client side as an array of strings.
For JDBC, using GET_LINES() is preferable, because it's more efficient than individual GET_LINE() calls.
MariaDB implements all routines supported by Oracle, except GET_LINES():
Procedure ENABLE() - enable the routines.
Procedure DISABLE() - disable the routines. If the package is disabled, all calls to subprograms, such as PUT() and PUT_LINE(), are ignored (or exit immediately without doing anything).
Procedure PUT_LINE() - submit a line into the internal buffer.
Procedure PUT() - submit a partial line into the buffer.
Procedure NEW_LINE() - terminate a line submitted by PUT().
Procedure GET_LINE() - read one line (the earliest) from the buffer. When a line is read by GET_LINE(), it's automatically removed from the buffer.
Procedure GET_LINES() - read all lines (as an array of strings) from the buffer - this procedure isn't implemented.
The package starts in disabled mode, so an explicit enabling is needed:
If a call for GET_LINE or GET_LINES did not retrieve all lines, then a subsequent call for PUT, PUT_LINE, or NEW_LINE discards the remaining lines (to avoid confusing with the next message). This script demonstrates the principle:
line1
0
line3
0
-
1
Oracle uses this data type as a storage for the buffer:
Like Oracle, MariaDB uses an associative array as a storage for the buffer.
This functionality is not implemented.
In Oracle, the function GET_LINES() returns an array of strings of this data type:
MariaDB does not have array data types in the C and C++ connectors, so they can't take advantage of GET_LINES() in a client program.
Fetching all lines in a PL/SQL program is implemented using a loop of sys.DBMS_OUTPUT.GET_LINE() calls:
Fetching all lines on the client side (for instance, in a C program using Connector/C) is done by using a loop of sys.DBMS_OUTPUT.GET_LINE() queries.
Oracle has the following limits:
The maximum individual line length (sent to DBMS_OUTPUT) is 32767 bytes.
The default buffer size is 20000 bytes. The minimum size is 2000 bytes. The maximum is unlimited.
MariaDB also implements some limits, either using the total size of all rows or using the row count.
Like other bootstrap scripts, the script creating DBMS_OUTPUT:
Is put into a new separate /scripts/dbms_ouput.sql file in the source directory;
Is installed into /share/dbms_ouput.sql of the installation directory.
\
DELIMITER //
CREATE FUNCTION trust_me(x INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN (x);
END //
DELIMITER ;DELIMITER //
CREATE FUNCTION dont_trust_me()
RETURNS INT
BEGIN
RETURN UUID_SHORT();
END //
DELIMITER ;CALL DBMS_OUTPUT.ENABLE;DROP TABLE t1;
CREATE TABLE t1 (line VARCHAR2(400), status INTEGER);
DECLARE
line VARCHAR2(400);
status INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('line1');
DBMS_OUTPUT.PUT_LINE('line2');
DBMS_OUTPUT.GET_LINE(line, status);
INSERT INTO t1 VALUES (line, status);
DBMS_OUTPUT.PUT_LINE('line3'); -- This cleares the buffer (removes line2) before putting line3
LOOP
DBMS_OUTPUT.GET_LINE(line, status);
INSERT INTO t1 VALUES (line, status);
EXIT WHEN status <> 0;
END LOOP;
END;
/
SELECT * FROM t1;TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;TYPE DBMSOUTPUT_LINESARRAY IS VARRAY(2147483647) OF VARCHAR2(32767);SET sql_mode=ORACLE;
DELIMITER /
DECLARE
all_lines MEDIUMTEXT CHARACTER SET utf8mb4 :='';
line MEDIUMTEXT CHARACTER SET utf8mb4;
status INT;
BEGIN
sys.DBMS_OUTPUT.PUT_LINE('line1');
sys.DBMS_OUTPUT.PUT_LINE('line2');
sys.DBMS_OUTPUT.PUT_LINE('line3');
LOOP
sys.DBMS_OUTPUT.GET_LINE(line, status);
EXIT WHEN status > 0;
all_lines:= all_lines || line || '\n';
END LOOP;
SELECT all_lines;
END;
/
DELIMITER ;CREATE AGGREGATE FUNCTION function_name (parameters) RETURNS return_type
BEGIN
ALL types of declarations
DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN return_val;
LOOP
FETCH GROUP NEXT ROW; // fetches next row FROM TABLE
other instructions
END LOOP;
ENDSET sql_mode=Oracle;
DELIMITER //
CREATE AGGREGATE FUNCTION function_name (parameters) RETURN return_type
declarations
BEGIN
LOOP
FETCH GROUP NEXT ROW; -- fetches next row from table
-- other instructions
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN return_val;
END //
DELIMITER ;CREATE TABLE marks(stud_id INT, grade_count INT);
INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8);
SELECT * FROM marks;
+---------+-------------+
| stud_id | grade_count |
+---------+-------------+
| 1 | 6 |
| 2 | 4 |
| 3 | 7 |
| 4 | 5 |
| 5 | 8 |
+---------+-------------+
DELIMITER //
CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURNS INT
BEGIN
DECLARE count_students INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
RETURN count_students;
LOOP
FETCH GROUP NEXT ROW;
IF x THEN
SET count_students = count_students+1;
END IF;
END LOOP;
END //
DELIMITER ;DELIMITER //
CREATE AGGREGATE FUNCTION medi_int(x INT) RETURNS DOUBLE
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
DECLARE res DOUBLE;
DECLARE cnt INT DEFAULT (SELECT COUNT(*) FROM tt);
DECLARE lim INT DEFAULT (cnt-1) DIV 2;
IF cnt % 2 = 0 THEN
SET res = (SELECT AVG(a) FROM (SELECT a FROM tt ORDER BY a LIMIT lim,2) ttt);
ELSE
SET res = (SELECT a FROM tt ORDER BY a LIMIT lim,1);
END IF;
DROP TEMPORARY TABLE tt;
RETURN res;
END;
CREATE TEMPORARY TABLE tt (a INT);
LOOP
FETCH GROUP NEXT ROW;
INSERT INTO tt VALUES (x);
END LOOP;
END //
DELIMITER ;SET sql_mode=Oracle;
DELIMITER //
CREATE AGGREGATE FUNCTION aggregate_count(x INT) RETURN INT AS count_students INT DEFAULT 0;
BEGIN
LOOP
FETCH GROUP NEXT ROW;
IF x THEN
SET count_students := count_students+1;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN count_students;
END aggregate_count //
DELIMITER ;
SELECT aggregate_count(stud_id) FROM marks;