Oracle stored procedure-Package

 

Package is an object in PLSQL.

Like a stored procedure, an object is stored in a stored procedure. In addition to syntax

There is nothing to say. Similar to Java, package is the relationship between interfaces and implementation classes. Package

It is mainly for interface-oriented programming (personal understanding ). You can compare the knowledge of Java interfaces with the package.

You can include variable definitions, method definitions, and stored procedure definitions.

Package is divided into two parts: Package Description and package body.

The package description is similar to the interface definition, and the package body is similar to the implementation class.

Package description format:

create or replace package ppppppppppppp is  -- Author  : MY  -- Created : 2013/7/11 16:19:16  -- Purpose :     -- Public type declarations  type <TypeName> is <Datatype>;    -- Public constant declarations  <ConstantName> constant <Datatype> := <Value>;  -- Public variable declarations  <VariableName> <Datatype>;  -- Public function and procedure declarations  function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;end ppppppppppppp;

 

Package declaration format:

create or replace package body bbbbbbbbbbbbb is  -- Private type declarations  type <TypeName> is <Datatype>;    -- Private constant declarations  <ConstantName> constant <Datatype> := <Value>;  -- Private variable declarations  <VariableName> <Datatype>;  -- Function and procedure implementations  function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is    <LocalVariable> <Datatype>;  begin    <Statement>;    return(<Result>);  end;begin  -- Initialization  <Statement>;end bbbbbbbbbbbbb;

 

The relationship between the package description and the package body is 1-0 or 1-1.

If only variables are declared and methods and stored procedures are not involved, only the package instructions can be correctly compiled and used.

In the package description, in addition to defining variables, other objects, such as methods and stored procedures, are defined. You must have a package body and implement the defined methods and stored procedures in the package body. (This is similar to the interface implementation class, that is, the single inheritance is different from the Java interface ). If this function is not implemented, no compilation error is reported, and an error is reported during the call.

All objects in the package are called by the package name. variable name.

So far, the benefits and significance of the package should be clear.

First, the meaning of the package is to share. This is a very important issue in programming. In Java, we write a class,

The purpose is to call. In other places, you can use the new method to repeat the method, or

This class is a data type. This is easy to understand. However, in PLSQL, we declare a custom data type.

(Type). If it is defined only in the declaration of a code block, other objects cannot be shared. The problem is that if the returned results are of this type, how can other objects be recognized?

With the package, PLSQL is more like programming than simply combining SQL statements.

Second, the package structure is similar to the interface in Java. The benefits of this design can be considered as the benefits of interface-oriented programming.

(Note: The definition in the package description must be exactly the same as the declaration in the package body! Including the variable name !)

 

Package Description:

 

Create or replace package lsy_package is -- type type_myrecord1 is record (r_codecode lsy_codebook.codecode % type not null: = 'I am default value', r_codename limit % type, r_result varchar2 (32) default null, r_flag number default 0); -- constant variable Declaration (must be assigned, similar to final in Java) date_pattern_str constant varchar2 (32): = 'yyyy-mm-dd hh24: MI: ss'; -- variable declaration v_pub_yes varchar2 (32): = 'yes'; myexception_package_one exception; -- exception procedure procc (PPP in number); -- stored procedure function funcff (FFF in number) return number; -- Method end lsy_package;

 

Package body:

Create or replace package body lsy_package is -- private type declaration type body_type is record (r_one varchar2 (32) not null: = '', r_two number ); -- Private constant variable declaration pub_body_true constant varchar2 (32): = '1'; pub_body_false constant varchar2 (32): = '0'; -- private variable declaration v_now Date: = sysdate; v_now_str varchar2 (32); -- Implement the inherited methods and stored procedures (you can also use them privately, so they can only be used in the package body and cannot be called) procedure procc (PPP in number) is RR number; begin RR: = PPP + 1; dbms_output.put_line ('procc called: '| RR); Exception when myexception_package_one then raise_application_error (-20000, 'lsy _ package custom exception: XXX! ', False); when others then raise; end procc; -- function funcff (FFF in number) return number is res number; begin Res: = fff + 1; dbms_output.put_line ('funcff called: '| res); Return res; end; begin -- this code block takes only one v_now_str: = to_char (v_now, date_pattern_str) at the first call ); dbms_output.put_line ('initialization time: '| v_now_str); End lsy_package;

 

Anonymous code block test;

-- Created on 2013/7/11 by lsy -- package test 1 declare v_one plugin; begin v_one.r_codecode: = 'qq'; v_one.r_codename: = 'ww '; v_one.r_result: = 'success! '; V_one.r_flag: = 1; dbms_output.put_line ('v _ One. r_codecode: '| v_one.r_codecode |', Region: '| v_one.r_codename |', v_one.r_result: '| v_one.r_result |', v_one.r_flag: '| v_one.r_flag ); dbms_output.put_line ('lsy _ package. date_pattern_str: '| lsy_package.date_pattern_str); dbms_output.put_line ('lsy _ package. v_pub_yes: '| lsy_package.v_pub_yes); dbms_output.put_line ('lsy _ package. funcff (3 ): '| Lsy_package.funcff (3); lsy_package.procc (1); end;/* print v_one.r_codecode: QQ, v_one.r_codename: WW, v_one.r_result: Success !, V_one.r_flag: Usage: yyyy-mm-dd hh24: MI: sslsy_package.v_pub_yes: yesfuncff called: 4lsy_package.funcff (3): 4procc called: 2 */