CREATE PACKAGE BODY
You are viewing an old version of this article. View
the current version here.
The CREATE PACKAGE BODY
statement can be used when Oracle SQL_MODE is set, or in any mode from MariaDB 11.4.
In Oracle mode, the PL/SQL dialect is used, while in non-Oracle mode, SQL/PSM is used.
Syntax (Oracle mode)
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
Contents
Description
The CREATE PACKAGE BODY
statement can be used when Oracle SQL_MODE is set, or in any mode from MariaDB 11.4.
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:
- package-wide private variables
- package private routines
- forward declarations for private routines
- an executable initialization section
Examples
Oracle mode:
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 ;
Non-Oracle mode, from MariaDB 11.4:
DELIMITER $$ CREATE OR REPLACE PACKAGE pkg PROCEDURE p1(); FUNCTION f1() RETURNS INT; END; $$ DELIMITER ; DELIMITER $$ CREATE OR REPLACE PACKAGE BODY pkg -- variable declarations DECLARE a INT DEFAULT 11; DECLARE b INT DEFAULT 10; -- routine declarations PROCEDURE p1() BEGIN SELECT CURRENT_USER; END; FUNCTION f1() RETURNS INT BEGIN RETURN a; END; -- package initialization section SET a=a-b; END; $$ DELIMITER ;
See Also
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.