Oracle stored procedure Package "go"

First, why use stored procedures?

If you often need to perform specific actions in your application, you can work with resumes on a specific process based on these actions. The use of procedures can simplify the development and maintenance of client programs, but also improve the performance of client programs.

Second, the advantages of the process?

1. precompilation: Stored procedures are pre-compiled and placed in the database, reducing the time spent compiling statements.

2, Cache: Pre-compiled stored procedures will enter the cache, so for frequently executed stored procedures, in addition to the first execution, other times the execution speed will be significantly increased.

3. Reduce network transmission: Especially for some stored procedures that process data, you do not have to send data to the client as many times as you would directly use SQL statements.

4. High maintainability: Updating stored procedures typically takes less time and effort than changing, testing, and deploying the application.

5, code reuse: A reusable stored procedure can be applied to multiple locations of the application.

6, enhance security: through the user authorization to the stored procedure access, they can provide access to specific data, improve data security, to prevent SQL injection.

Third, disadvantages:

1. If you need to make changes to the input and output parameters of the stored procedure, you should also change the program.

2. Portability is poor: because the stored procedure binds the application's business processing to the database, using stored procedures to handle business logic limits the application.

Iv. Creating a stored procedure

1 --1. Simple stored Procedure2 Create or Replace procedureprocedure_test3(p_idinch varchar, P_status outvarchar)--p_id as input parameter, p_status as output parameter4  as5T_namevarchar2( -);6T_count Number:=0;7 begin8  SelectVotetitle,vatesum intoT_name,t_count fromVotemasterwhereId=p_id;--Note: There is no: To assign a value9  ifT_count<=0  ThenTenP_status:=T_name||': Poor'; Oneelsif T_count>0  andT_count<3  Then AP_status:=T_name||': Good'; -  Else -P_status:=T_name||': Excellent'; the  End if; - End; - --Execution - Declare  +Out_paramvarchar2( -); - begin  +Procedure_test ('1', Out_param); A Dbms_output.put_line (out_param); at End; -  - --2. Stored Procedures with Cursors - Create or Replace procedureprocedure_cursor_test -(p_idinch varchar2, P_status outvarchar2) -  as inVote Votemaster%RowType--object that declares an object (Votemaster) type -   cursorMy_cur is Select *  fromVotemaster;--declaring a cursor and populating the data to begin +   OpenMy_cur;--Open Cursor - Loop the           FetchMy_cur intoVote;--loops a cursor and puts it into the object *           Exit  whenMy_cur%NotFound--If there is no data, exit directly $           ifVote.id=p_id ThenPanax NotoginsengP_status:=Vote.votetitle||':'||vote.vatesum; -              --if you want to terminate the loop, you can exit directly; the           End if; +        EndLoop; A   CloseMy_cur;--Close Cursors the End; + --Execution - Declare  $Out_paramvarchar2( -); $ begin  -Procedure_cursor_test ('1', Out_param); - Dbms_output.put_line (out_param); the End;

V. Package

1, Package: Package is a set of related procedures, functions, variables, cursors, constants, such as PL/SQL programming elements of the combination. It has the characteristics of object-oriented programming language and is the encapsulation of these PL/SQL programming elements. Packages are similar to classes in C + + or Java programs, and variables are equivalent to member variables in classes, procedures and functions are equivalent to methods, and the related modules are categorized into packages, allowing developers to use object-oriented methods for the development of stored procedures to improve system performance. As with classes, program elements in packages are also divided into common and private elements, the difference being that they allow access to a different range of programs, i.e. their scopes are different. Common elements can be called not only by functions, procedures in packages, but also by PL/SQL blocks outside the package. A private element can only be called by a function or procedure inside the package.

2, the advantages of using the package: in PL/SQL design, the use of the package can not only make the program modular, external hiding the information used in the package, and write the package can improve the efficiency of the program. Because when the program first invokes a function or procedure inside a package, Oracle calls the entire package into memory, and when the elements in the package are called again, Oracle reads directly from memory without the need for disk IO operations, which makes the execution of the program more efficient.

3. A package consists of two parts:

(1), package definition: The package Definition section declares elements of data types, variables, constants, cursors, subroutines, and functions within a package, which are common elements of a package.

(2), Package body: The package theme defines the specific implementation of the package Definition section, and can also declare and implement private elements in the package body.

1 --Package Definition2 Create or ReplacePackage T_package3  is4   --Defining the Process5   procedureAppend_proc (tvarchar2, a outvarchar2);6   --Overloading of Procedures7   procedureAppend_proc (t Number, a outvarchar2);8   --Defining Functions9   functionAppend_fun (tvarchar2)return varchar2;Ten    One End;

1 --Package Theme2 Create or ReplacePackage Body T_package3  is4v_tvarchar2( -);5   --Private member functions6   functionPrivate_fun (tvarchar2)return varchar2  is7   begin8v_t:=T||'Hello';9      returnv_t;Ten   End; One   --implementation Process A   procedureAppend_proc (tvarchar2, a outvarchar2) is -   begin -A:=T||'Hello';  the   End; -   --Overloading of Procedures -   procedureAppend_proc (t Number. Aboutvarchar2) is  -   begin  +A:=T||'Hello';  -   End; +   --implementation Functions A   functionAppend_fun (tvarchar2)  at   return varchar2  is -   begin -v_t:=T||'Hello'; -      returnv_t; -   End; - End;

http://zxf-noimp.iteye.com/blog/1145442

Oracle stored procedure Package "go"