Self-study of company demand knowledge-Role and usage of Oracle Package

Company requirement knowledge self-taught-functions and usage of Oracle Package simplify application design, improve application performance, implement information hiding, and overload subroutines. 1. Does Oracle Package play a role (benefit) in addition to storing stored procedures in a pile )? Do you think it is very important to classify stored procedures in different categories, and different package stored procedures can be renamed. Package can not only classify stored procedures, but also define common variables/types in the package, which facilitates programming and reduces the compilation overhead of the server. 2. How to add existing stored procedures to a Package? Copy and pasty, But the package name is required for calling. 3. In addition to SQL Plus, is there any tool for Package? There are also convenient third-party tools, but you have to find them yourself. Use a third-party tool, such as SQL navigator. Www.quest.com 4. If I use SQL Plus to compile a Package, do I always compile all the stored procedures in the Package? A pack is also a kind of block named pl/SQL, and stored procedures and functions are loaded into the memory at the database startup. The overhead size is hard to judge, because you do not need a package, but you still need to use PL/SQL to complete the package function. The server also has overhead. Comparatively speaking, the SQL syntax analysis and interpretation processes are less costly. "The process generally has no more than 20 rows. The key to using a non-subprocess is whether a reusable sub-process can be defined. The efficiency of using a sub-process is not low. The role of a package: the package can place any statement (process, function, cursor, cursor, type, variable) that appears in the block declaration in the package, which is equivalent to a container. the benefit of putting a declaration statement into a package is that you can reference it from other PL/SQL blocks, so the package provides full variables for PL/SQL. the package is divided into two parts: the packet header and the package body. how to Create a package? 1) header: syntax format: create or replace PACKAGEpackage_name/* header name */IS | AS pl/SQL _package_spec/* defines the process, function, return type, variable, definitions of constants and data types */definitions of headers should follow the following principles: 1) the position of package elements can be arbitrarily arranged. however, in the declaration part, the object must be declared before reference. 2) the header does not describe any type of elements. for example, the header can only contain process and function description statements without declaring any exceptions and types. 3) Any declaration of procedures and functions must only describe the subroutine and its parameters, and there cannot be any code description. The implementation of code can only appear in the package body. it is different from block Declaration. In block declaration, the code of the process and function can appear in the declaration part at the same time. 2. package body: syntax format: create or replace package body package_name/* The PACKAGE name must be consistent with the PACKAGE name of the Baotou */IS | AS pl/SQL _package_body/* cursor, function, specific process definition */The package body is independent of each other. The package body can only be compiled after the packet header is compiled. the specific implementation code segment of the subroutine with the header in the package. in addition, the package body can include additional declarations with the full-sentence attributes of the package body, but these additional declarations are invisible to the header. EG: Define a Baotou

Create or replace package select_tableISTYPE tab_02 is record (itnum_1 varchar2 (1), itnum_2 varchar2 (1); TYPE tab_03 is record (itnum_1 varchar2 (1), itnum_2 varchar2 (1 ), itnum_3 varchar2 (1); TYPE tab_04 is record (itnum_1 varchar2 (1), itnum_2 varchar2 (1), itnum_3 varchar2 (1), itnum_4 varchar2 (1 )); TYPE tab_05 is record (itnum_1 varchar2 (1), itnum_2 varchar2 (1), itnum_3 varchar2 (1), itnum_4 varchar2 (1), itnum_5 varchar2 (1 )); TYPE tab_06 is record (itnum_1 varchar2 (1), itnum_2 varchar2 (1), itnum_3 varchar2 (1), itnum_4 varchar2 (1), itnum_5 varchar2 (1 ), itnum_6 varchar2 (1); TYPE cur_02 is ref cursor RETURNtab_02; TYPE cur_03 is ref cursor RETURNtab_03; TYPE cur_04 is ref cursor RETURNtab_04; TYPE cur_05 is ref cursor restart; TYPE cur_06 is ref cursor RETURNtab_06; END select_tab; EG: create or replace package test_packageISFUNCTION average (cnum IN char) return number; PRODURE student_grade (cur out select_table.cur_04 ); -- the Data Type of CUR is cur_o4END test_package in the select_table PACKAGE; package body: create or replace package body test_packageIS/* FUNCTION implementation start */FUNCTION average (cnum IN char) return number; ASavger NUMBER; beginselect avg (CJ) INTO avger FROM XS_KC where kch = cnum group bykch; RETURN (avger); END average; /* function implementation end * // * Process Implementation start */PRODURE student_grade (cur out select_table.cur_04); asopen cur forselect xs. XH, XS. XM, KC. KCM, XS_KC.CJFROM XS, XS_KC, kcwhere xs. XH = XS_KC.XH AND XS_KC.KCH = KC. KCH; END student_grade;

 

/* Process Implementation ends */END test_package; overload: The functions and processes in the package can be overloaded with the following conditions and cannot be reloaded: 1. if the parameters of the two subprograms are different in name and type, the two programs cannot be overloaded. PROCEDURE overloadME (p_theparameterIN number); PROCEDURE overloadME (p_theparameterOUT number); IN, OUT is a parameter type, and number is a data type. the two processes must be of different types and cannot be reloaded. 2. you cannot overload two functions based on their return types, for example, FUNCTION overloadMeETooRETURN DATE; FUNCTION overloadMeETooRETURN NUMER; 3. the class families of parameters of the overloaded subroutine must be different. For example, the CHAR and VARCHAR2 attributes must be of the same class family, so they cannot be overloaded. PROCEDURE overloadME (p_theparameterIN char); PROCEDURE overloadME (p_theparameterIN varchar2); 4. you can also reload the packaging subroutine. package initialization. when the package subroutine is called for the first time, the package is initialized. that is, read the package from the hard disk to the memory and enable the compiled code of the called subroutine. at this time, the system allocates memory units for all variables defined in the package. each session has a copy of the open package variable to ensure that two sessions running the same Steamed Stuffed Bun program use different memory units. in most cases, the initialization code runs when the package is initialized for the first time. to implement this function, you can add an initialization code after all objects in the package body. syntax format:

Create or replace package BODYpackage_nameIS | AS ...... BEGINInitialization_code; -- the initialization code to run END;

 

ORACLE built-in Package 1. DBMS_ALERT package: used for database alarms and allows inter-session communication. DBMS_JOB: used for Task Scheduling Service 3. DBMS_LOB: used to process large object operations. 4. DBMS_PIPE package: used for database pipelines and allows inter-session communication. DBMS_ SQL package: used to execute the dynamic SQL6.UTL _ FILE package: used for FILE input and output, except for the UTL_FILE package stored on the server and client, other packages are stored on the server.