Oracle Database PL/SQL package

Source: Internet
Author: User
6) Package 1. basic concept package (Package) is used to organize logical PL/SQL blocks or elements (variables, constants, custom data types, exceptions, processes, functions, and cursors, the package is stored as a complete unit in the database and identified by name. The package consists of two independent parts: The description part and the package body part, which are stored independently in the data dictionary. The changes in the description section of the package need to re-compile the called application, and the changes in the package body do not need to re-compile the called application. 2. Create a package 1) create a package description section. Create packag <package name> is variable, constant, and data type definition; cursor definition header; function, process definition, parameter list, and return type; end <package name>; create package my_packageis man_num number; -- defines two global variables woman_num number; cursor teacher_cur; -- defines a cursor create function f_count_num (in_sex in teachers. sex % Type) return number; -- defines a function create procedure p_count_num (in_sex in teachers. sex % type, out_num out number); -- defines an end my package; 2) create package body <package name> As cursor, function, and process definition; end <package name>; create package body my_package ascursor teacher_cur is -- the cursor defines the specific select tid, tname, title, sex from teachers where TID <117; function f_count_num -- Function Definition (in_sex in teachers. sex % Type) return numberas out_num number; begin if in_sex = 'M' then select count (sex) into out_num from teachers where sex = 'M'; else select count (sex) into out_num from teachers where sex = 'F'; end if; Return (out_num); End f_count_num; Procedure p_count_num -- specific process definition (in_sex in teachers. sex % type, out_num out number) asbeginif in_sex = 'M' thenselect count (sex) into out_numfrom teacherswhere sex = 'M'; elseselect count (sex) into out_numfrom teacherswhere sex = 'F'; end if; end p_count_num; end my_package; -- end of package Definition 3. the name of the called package. variable name (constant name) package name. the name of the cursor package. function Name (process name) SQL> variable man_num numbersql> execute man_num: = my_package.f_count_num ('M') 4. delete package drop package my_package

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.