Oracle-style packages were introduced in MariaDB 10.3.5.
CREATE [ OR REPLACE ] [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }] PACKAGE BODY [ IF NOT EXISTS ] [ db_name . ] package_name [ package_characteristic... ] { AS | IS } package_implementation_declare_section package_implementation_executable_section END [ package_name] package_implementation_declare_section: package_implementation_item_declaration [ package_implementation_item_declaration... ] [ package_implementation_routine_definition... ] | package_implementation_routine_definition [ package_implementation_routine_definition...] package_implementation_item_declaration: variable_declaration ; variable_declaration: variable_name[,...] type [:= expr ] package_implementation_routine_definition: FUNCTION package_specification_function [ package_implementation_function_body ] ; | PROCEDURE package_specification_procedure [ package_implementation_procedure_body ] ; package_implementation_function_body: { AS | IS } package_routine_body [func_name] package_implementation_procedure_body: { AS | IS } package_routine_body [proc_name] package_routine_body: [ package_routine_declarations ] BEGIN statements [ EXCEPTION exception_handlers ] END package_routine_declarations: package_routine_declaration ';' [package_routine_declaration ';']... package_routine_declaration: variable_declaration | condition_name CONDITION FOR condition_value | user_exception_name EXCEPTION | CURSOR_SYM cursor_name [ ( cursor_formal_parameters ) ] IS select_statement ; package_implementation_executable_section: END | BEGIN statement ; [statement ; ]... [EXCEPTION exception_handlers] END exception_handlers: exception_handler [exception_handler...] exception_handler: WHEN_SYM condition_value [, condition_value]... THEN_SYM statement ; [statement ;]... condition_value: condition_name | user_exception_name | SQLWARNING | SQLEXCEPTION | NOT FOUND | OTHERS_SYM | SQLSTATE [VALUE] sqlstate_value | mariadb_error_code
The CREATE PACKAGE BODY
statement can be used when Oracle SQL_MODE is set.
The CREATE PACKAGE BODY
statement creates the package body for a stored package. The package specification must be previously created using the CREATE PACKAGE statement.
A package body provides implementations of the package public routines and can optionally have:
SET sql_mode=ORACLE; DELIMITER $$ CREATE OR REPLACE PACKAGE employee_tools AS FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2); PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2)); PROCEDURE raiseSalaryStd(eid INT); PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2)); END; $$ CREATE PACKAGE BODY employee_tools AS -- package body variables stdRaiseAmount DECIMAL(10,2):=500; -- private routines PROCEDURE log (eid INT, ecmnt TEXT) AS BEGIN INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt); END; -- public routines PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2)) AS eid INT; BEGIN INSERT INTO employee (name, salary) VALUES (ename, esalary); eid:= last_insert_id(); log(eid, 'hire ' || ename); END; FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS nSalary DECIMAL(10,2); BEGIN SELECT salary INTO nSalary FROM employee WHERE id=eid; log(eid, 'getSalary id=' || eid || ' salary=' || nSalary); RETURN nSalary; END; PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2)) AS BEGIN UPDATE employee SET salary=salary+amount WHERE id=eid; log(eid, 'raiseSalary id=' || eid || ' amount=' || amount); END; PROCEDURE raiseSalaryStd(eid INT) AS BEGIN raiseSalary(eid, stdRaiseAmount); log(eid, 'raiseSalaryStd id=' || eid); END; BEGIN -- This code is executed when the current session -- accesses any of the package routines for the first time log(0, 'Session ' || connection_id() || ' ' || current_user || ' started'); END; $$ DELIMITER ;
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/create-package-body/