(window.webpackJsonp=window.webpackJsonp||[]).push([[2362],{2770:function(a,t,s){"use strict";s.r(t);var e=s(31),n=Object(e.a)({},(function(){var a=this,t=a.$createElement,s=a._self._c||t;return s("ContentSlotsDistributor",{attrs:{"slot-key":a.$parent.slotKey}},[s("h1",{attrs:{id:"packages"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#packages"}},[a._v("#")]),a._v(" Packages")]),a._v(" "),s("h2",{attrs:{id:"overloading"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#overloading"}},[a._v("#")]),a._v(" Overloading")]),a._v(" "),s("p",[a._v("Functions and procedures in packages can be overloaded. The following package "),s("strong",[a._v("TEST")]),a._v(" has two procedures called "),s("strong",[a._v("print_number")]),a._v(", which behave differently depending on parameters they are called with.")]),a._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("create")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("or")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("replace")]),a._v(" package TEST "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("is")]),a._v("\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("procedure")]),a._v(" print_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("p_number "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("in")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("integer")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("procedure")]),a._v(" print_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("p_number "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("in")]),a._v(" varchar2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("end")]),a._v(" TEST"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("create")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("or")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("replace")]),a._v(" package body TEST "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("is")]),a._v("\n\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("procedure")]),a._v(" print_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("p_number "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("in")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("integer")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("is")]),a._v("\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("begin")]),a._v("\n    dbms_output"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(".")]),a._v("put_line"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'Digit: '")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("||")]),a._v(" p_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("    \n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("end")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n  \n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("procedure")]),a._v(" print_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("p_number "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("in")]),a._v(" varchar2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("is")]),a._v("\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("begin")]),a._v("\n    dbms_output"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(".")]),a._v("put_line"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'String: '")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("||")]),a._v(" p_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("    \n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("end")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("end")]),a._v(" TEST"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),a._v("\n\n")])])]),s("p",[a._v("We call both procedures. The first with integer parameter, the second with varchar2.")]),a._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("set")]),a._v(" serveroutput "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("on")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token comment"}},[a._v("-- call the first procedure")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("exec")]),a._v(" test"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(".")]),a._v("print_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),s("span",{pre:!0,attrs:{class:"token number"}},[a._v("3")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token comment"}},[a._v("-- call the second procedure")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("exec")]),a._v(" test"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(".")]),a._v("print_number"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'three'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n\n")])])]),s("p",[a._v("The output of the above script is:")]),a._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("SQL")]),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v(">")]),a._v(" \nDigit: "),s("span",{pre:!0,attrs:{class:"token number"}},[a._v("3")]),a._v("\nPL"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("SQL")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("procedure")]),a._v(" successfully completed\nString: three\nPL"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("SQL")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("procedure")]),a._v(" successfully completed\n\n")])])]),s("h3",{attrs:{id:"restrictions-on-overloading"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#restrictions-on-overloading"}},[a._v("#")]),a._v(" Restrictions on Overloading")]),a._v(" "),s("p",[a._v("Only local or packaged subprograms, or type methods, can be overloaded. Therefore, you cannot overload standalone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode")]),a._v(" "),s("h2",{attrs:{id:"define-a-package-header-and-body-with-a-function"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#define-a-package-header-and-body-with-a-function"}},[a._v("#")]),a._v(" Define a Package header and body with a function.")]),a._v(" "),s("p",[a._v("In this example we define a package header and a package body wit a function. "),s("br"),a._v("\nAfter that we are calling a function from the package that return a return value. "),s("br")]),a._v(" "),s("p",[s("strong",[a._v("Package header")]),a._v(":")]),a._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("CREATE")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("OR")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("REPLACE")]),a._v(" PACKAGE SkyPkg "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("AS")]),a._v("\n\n       "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("FUNCTION")]),a._v(" GetSkyColour"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("vPlanet "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("IN")]),a._v(" VARCHAR2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),a._v("\n       "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("RETURN")]),a._v(" VARCHAR2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n       \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("END")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),a._v("\n\n")])])]),s("p",[s("strong",[a._v("Package body")]),a._v(":")]),a._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("CREATE")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("OR")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("REPLACE")]),a._v(" PACKAGE BODY SkyPkg "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("AS")]),a._v("\n\n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("FUNCTION")]),a._v(" GetSkyColour"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("vPlanet "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("IN")]),a._v(" VARCHAR2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),a._v("\n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("RETURN")]),a._v(" VARCHAR2\n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("AS")]),a._v("\n               vColour VARCHAR2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),s("span",{pre:!0,attrs:{class:"token number"}},[a._v("100")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),a._v(" :"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token boolean"}},[a._v("NULL")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("BEGIN")]),a._v("\n               "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("IF")]),a._v(" vPlanet "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'Earth'")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("THEN")]),a._v("\n                       vColour :"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'Blue'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n               ELSIF vPlanet "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'Mars'")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("THEN")]),a._v("\n                       vColour :"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'Red'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n               "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("END")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("IF")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n\n               "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("RETURN")]),a._v(" vColour"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("END")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n        \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("END")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),a._v("\n\n")])])]),s("p",[s("strong",[a._v("Calling the function from the package body")]),a._v(":")]),a._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("DECLARE")]),a._v("\n        vColour VARCHAR2"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),s("span",{pre:!0,attrs:{class:"token number"}},[a._v("100")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("BEGIN")]),a._v("\n        vColour :"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),a._v(" SkyPkg"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(".")]),a._v("GetSkyColour"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("vPlanet "),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("=")]),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v(">")]),a._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[a._v("'Earth'")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n        DBMS_OUTPUT"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(".")]),a._v("PUT_LINE"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v("(")]),a._v("vColour"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(")")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[a._v("END")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[a._v(";")]),a._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[a._v("/")]),a._v("\n\n")])])]),s("h2",{attrs:{id:"package-usage"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#package-usage"}},[a._v("#")]),a._v(" Package Usage")]),a._v(" "),s("p",[a._v("Packages in PLSQL are a collection of procedures, functions, variables, exceptions, constants, and data structures.  Generally the resources in a package are related to each other and accomplish similar tasks.")]),a._v(" "),s("p",[a._v("Why Use Packages")]),a._v(" "),s("ul",[s("li",[a._v("Modularity")]),a._v(" "),s("li",[a._v("Better Performance/ Funtionality")])]),a._v(" "),s("p",[a._v("Parts of a Package")]),a._v(" "),s("p",[a._v("Specification -  Sometimes called a package header.  Contains variable and type declarations and the signatures of the functions and procedures that are in the package which are "),s("strong",[a._v("public")]),a._v(" to be called from outside the package.")]),a._v(" "),s("p",[a._v("Package Body - Contains the code and "),s("strong",[a._v("private")]),a._v(" declarations.")]),a._v(" "),s("p",[a._v("The package specification must be compiled before the package body, otherwise the package body compilation will report an error.")]),a._v(" "),s("h4",{attrs:{id:"syntax"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#syntax"}},[a._v("#")]),a._v(" Syntax")]),a._v(" "),s("li",[a._v("\nCREATE [OR REPLACE] PACKAGE package_name\n[AUTHID {CURRENT_USER | DEFINER}]\n{IS | AS}\n[PRAGMA SERIALLY_REUSABLE;]\n[collection_type_definition ...]\n[record_type_definition ...]\n[subtype_definition ...]\n[collection_declaration ...]\n[constant_declaration ...]\n[exception_declaration ...]\n[object_declaration ...]\n[record_declaration ...]\n[variable_declaration ...]\n[cursor_spec ...]\n[function_spec ...]\n[procedure_spec ...]\n[call_spec ...]\n[PRAGMA RESTRICT_REFERENCES(assertions) ...]\nEND [package_name];\n")]),a._v(" "),s("li",[a._v("\nCREATE OR REPLACE PACKAGE PackageName IS\nFUNCTION FunctionName(parameter1 IN VARCHAR2, paramter2 IN NUMBER) RETURN VARCHAR2;\nEND PackageName;\n")]),a._v(" "),s("li",[a._v("\nCREATE [OR REPLACE] PACKAGE BODY package_name\n{IS | AS}\n[PRAGMA SERIALLY_REUSABLE;]\n[collection_type_definition ...]\n[record_type_definition ...]\n[subtype_definition ...]\n[collection_declaration ...]\n[constant_declaration ...]\n[exception_declaration ...]\n[object_declaration ...]\n[record_declaration ...]\n[variable_declaration ...]\n[cursor_body ...]\n[function_spec ...]\n[procedure_spec ...]\n[call_spec ...]\nEND [package_name];\n")]),a._v(" "),s("li",[a._v("\nCREATE OR REPLACE PACKAGE BODY PackageName IS\nFUNCTION FunctionName(parameter1 IN VARCHAR2, paramter2 IN NUMBER) RETURN VARCHAR2 IS\n**declarations**\nBEGIN\n**statements to execute**\nRETURN **varchar2 variable**\nEND FunctionName;\nEND PackageName;\n")])])}),[],!1,null,null,null);t.default=n.exports}}]);