Oracle entry-3 packages

From: http://www.dezai.cn/blog/article.asp? Id = 248

OracleProgramA package consists of a packet header and a package body (also known as a package description and a package body). It is a type of variable cursor that combines multiple programmer modules (such as function Stored Procedure variables) A function.
1. the difference between a package and a general program module is that a package consists of two parts. A packet header can be used as an external interface, while a package body can be seen as the specific implementation of a specific interface service, the declaration part of the package body is similar to the declaration part of functions or stored procedures in PL/SQL. but the variables, constants, and cursors in the package body are invisible to the package users.
2. the role of a package: a package is like the implementation of a specific class in an object-oriented process. It has the characteristics of structure, reusability, and modularity, at the same time, the variables and constants defined in the package can be defined as invisible or visible based on the needs of the business logic. The package separates functional interfaces from functional implementations to allowCodeIt is easier to maintain, while maintaining data security in the package body to prevent users from directly accessing the data.
3. Baotou (package description)
Baotou is a collection of Oracle packages and applications. It is used to define public components (variables, constants, stored procedures, functions, cursors, and so on) in the package ). The public components defined by Baotou can be used not only in the package, but also in other stored procedures or functions.

4. Package body (package body)

5. Baotou format
Create or replace package package_name is |
[Pragma serially_reusable;]
Public Data Type Definition
Public variable Declaration
Public constant Declaration
Public Exception error Declaration
Public cursor Declaration
Public Function Declaration
Public Process Declaration
End package_name;

6. package format
Create or replace package body package_name is |
[Pragma serially_reusable;]
Private Data Type Definition
Private variable Declaration
Private constant Declaration
Private Exception error Declaration
Private function description and definition
Private process description and definition
Public cursor Definition
Public Function Definition
Public Process Definition
Begin
Execution part (initialization part)
End package_name;

7. Use of packages

<1> call the Public variable of the package
Call format for public components: package name. component name
Eg. Execute my_pkg.v_sqlerrm: = 'test
<2> call the Public Functions of the package
<3> call the public storage process of a package
<4> use multiple packages
<5> delete a package
Delete the stored procedure in the package: Drop procedure stored procedure name
Delete the function drop function process name in the package
Delete package drop package body package name
Delete entire package drop package name

<6> ViewSource code

<7> package modification

8 Use of built-in system packages

9. Notes for using the package \
<1> before creating a package, you must specify the user with the right to use the package and the access permission. You must consider the package body statement and the business logic that needs to be implemented in the package.
<2> to delete a package, you must have the package or the permission to drop any procedure.
<3> Pragma serially_reusable determines whether the created package can be called continuously. If omitted, the running status of the package will be placed in the user's global zone (PGA, the running status of the package will be placed in the system global zone (SGA)
<4> the declaration of public functions in the public stored procedure should be placed after other declarations.
<5> The Name Of The package body and header created by a user in the database is unique.
<6> when creating headers and packages, you can use show errors to view compilation errors.
<7> the stored procedure function name in the header must be consistent with the name of the stored procedure and function implemented in the package body.

10. Net calls the Oracle package

Call functions in the package:

String oracle_conn = "User ID = user; Password = password; Data Source = server ";

using (oracleconnection conn = new oracleconnection (oracle_conn)
{< br> Conn. open ();
oraclecommand COM = new oraclecommand ("test_net.f_count", Conn); // call package
COM. commandtype = commandtype. storedprocedure;

// Input parameters
Oracleparameter p_input = new oracleparameter ("str", oracletype. varchar, 10 );
P_input.direction = system. Data. parameterdirection. input;
P_input.value = "function ";

// Return parameters

Oracleparameter p_output = new oracleparameter ("result", oracletype. varchar, 100 );
P_output.direction = system. Data. parameterdirection. returnvalue;
Com. Parameters. Add (p_input );
Com. Parameters. Add (p_output );
Com. executenonquery ();
Conn. Close ();
Response. Write (p_output.value.tostring ());
}

Process in the call package:

String oracle_conn = "User ID = user; Password = password; Data Source = server ";

Using (oracleconnection conn = new oracleconnection (oracle_conn ))
{
Conn. open ();
Oraclecommand COM = new oraclecommand ("test_net.p_serach", Conn );
Com. commandtype = commandtype. storedprocedure;
Oracleparameter p_input = new oracleparameter ("MYCS", oracletype. cursor );
P_input.direction = system. Data. parameterdirection. output;

Com. Parameters. Add (p_input );
Oracledataadapter da = new oracledataadapter (COM );
Dataset DS = new dataset ();
Da. Fill (DS, "test ");

Conn. Close ();
}

11. Java call package process
Package JDBC;
/**
* Author: May
* Time: 15: 09: 23
*/
Import java. SQL .*;
Import oracle. JDBC. Driver .*;
Public class proctest {

Public static void main (string [] ARGs ){
Proctest Pc = new proctest ();
PC. showcontent ();
}

String sdbdriver = "oracle. JDBC. Driver. oracledriver ";
String sconnstr = "JDBC: oracle: thin: @ 10.3.8.48: 1521: oradb ";


Connection connect = NULL;
Resultset rs = NULL;

Public proctest (){
Try {
Class. forname (sdbdriver );
}
Catch (classnotfoundexception e ){
System. Err. println (E. getmessage ());
}
}
Public resultset showcontent ()

{

Try {

Connect = drivermanager. getconnection (sconnstr, "shuibj", "shuibj ");
Callablestatement stmt = connect. preparecall ("{call pkg_test.get (?,?,?)} ");

Stmt. setstring (1, "all"); // enter the Parameter

Stmt. registeroutparameter (2, types. Char); // The output parameter is a common parameter.
Stmt. registeroutparameter (3, oracletypes. cursor); // The output parameter is the result set parameter.

Stmt.exe cutequery ();

Rs = (oraclecallablestatement) stmt). getcursor (3); // get the output result set Parameter

Resultsetmetadata rsmd = Rs. getmetadata ();
Int numberofcolumns = rsmd. getcolumncount ();
String STR = stmt. getstring (2 );


System. Out. println ("the second parameter is:" + Str );
System. Out. println ("result set columns" + numberofcolumns );

// List records in the result set
Resultsetmetadata MD = Rs. getmetadata ();
Int ncolumns = md. getcolumncount ();
For (INT I = 1; I <= ncolumns; I ++ ){
System. Out. Print (Md. getcolumnname (I) + (I = ncolumns )? "\ N": "\ t "));
If (I = 2) system. Out. Print ("\ t ");
}

While (Rs. Next ()){
For (INT I = 1; I <= ncolumns; I ++ ){
System. Out. Print (Rs. getstring (I) + (I = ncolumns )? "\ N": "\ t "));
}

}
}
Catch (sqlexception ex ){
System. Err. println (ex. getmessage () + "database connection error! ");
}

Return Rs;

}

}