Develop Oracle8i applications using PL/SQL and Java (1)

With the release of Oracle8i, Oracle supports two major programming languages, PL/SQL and Java. Today, many Oracle customers use both PL/SQL to create database applications and Java to create database applications. Since there are two database programming languages, there is a natural problem: Which of the following is better for PL/SQL and Java when an Oracle8i application is created?

Let's take a quick look at it. PL/SQL provides powerful functions for Oracle database developers, including high performance, ease of use, seamless integration with SQL, and robustness. PL/SQL is still a mature Procedural language for developing database applications, and is an ideal language for creating SQL-intensive and data-intensive applications. With the launch of Oracle8i, Oracle introduced Java in the database, providing a strong and large-scale platform for this very popular general language.

By using Enterprise JavaBean and CORBA, you can use Java to develop multi-layer, component-oriented applications. You can also use Java to develop traditional database stored procedures. Oracle8i provides a variety of features to simplify the process of establishing applications using PL/SQL and Java, and can easily combine applications written in two languages.

This article provides a technical overview of PL/SQL and Java features in Oracle8i, and provides practical guidance on how to use them to build applications. Since many Oracle customers have existing PL/SQL applications, we first describe how to combine the existing PL/SQL program with Java, extend the existing PL/SQL programs. Then, we describe the common application programming features in the two languages and explain how to use PL/SQL and Java together. In the third part, it is best to describe how to use PL/SQL and Java to establish an application (for example, when PL/SQL is the most suitable and when Java is the most suitable). Finally, we will discuss some real examples to demonstrate how Oracle customers use PL/SQL and Java in Oracle8i.

Create an application using PL/SQL and JAVA

First, let's take a look at the scenario of customers with PL/SQL applications: Customers want to add new Java code to the server to expand the original PL/SQL application. There are three special issues to consider: First, what are the main types of stored procedures supported in Oracle8i and how they can be called. Second, how does Java work in Oracle8i, how does Java interoperate with PL/SQL? Third, how does the Oracle8i server process name resolution between PL/SQL and Java. In the entire tutorial, the word "Java" refers to both standard Java and SQLJ (Embedded SQL in Java). Only when it is explicitly stated that different concepts are discussed, to indicate different meanings.

Type of Stored Procedure in Oracle8i

What types of stored procedures does Oracle8i support? Oracle8i supports four different types of stored procedures using PL/SQL or Java. All four types of stored procedures can be implemented. The four types of stored procedures are:

Stored Procedure top-level stored procedures allow customers to extend SQL with any commercial logic. You can call stored procedures by name in SQL, PL/SQL packages, and "top-level" locations.

Stored function storage functions are essentially the same as stored procedures. The difference is that stored functions return values to callers. You can call the storage function by name, or call the storage function at the SQL, PL/SQL package, or "top-level" location.

Database trigger: A trigger is a commercial rule associated with a specific table or view. When you modify a table or view, SQL automatically calls the trigger. Oracle8i provides several types of triggers: triggers that are triggered before or after SQLDML statements are executed; triggers that are triggered before or after each row is updated; event triggers (for example: triggers for logon, logout, DDL statements, database startup or shutdown, and replace (instead-of) triggers. All these trigger types can be written in PL/SQL or Java.

Object Type method Oracle8™Release 8.0 and Oracle8i provide the ability to define Composite data types using SQL. Methods of these object types can be implemented in Java or PL/SQL. For example, you can use SQL to define an order object type purchase_order_t, as shown below:

Create type purchase_order_t as object ( Pono NUMBER, Custref REF customer_info_t, Orderdate DATE, Shipdate DATE, Line_item_list line_item_list_t, Shiptoaddr address_t, MEMBER FUNCTION Total_value return number, );

Then implement the method of this object type:

Create or replace type body purchase_order_t Member function total_value RETURN NUMBER IS I INTEGER; Stock stock_info_t; Line_item line_item_t; Total NUMBER: = 0; Cost NUMBER; BEGIN // Insert the specific PL/SQL code here or call the Java stored procedure. END;

In Oracle 8i, each of the four storage program types can be called from different calling environments:

SQL statements can call functions within any SQL statement. The top-level CALL syntax uses the CALL Statement, which can be used in top-level CALL procedures and functions. CALL is a new syntax introduced with Oracle8i. PL/SQL blocks, subprograms, and packages can be called in PL/SQL subprograms, packages, or anonymous blocks. Note: Java storage programs can be stored in the anonymous BEGIN... The END block is called. Implicit call of a trigger. Finally, PL/SQL or Java stored procedures can be implicitly called during trigger execution.

Combine JAVA with existing PL/SQL programs

To understand how to combine Java applications with existing PL/SQL stored procedures, we must first understand the three steps for developing Java stored procedures:

Step 1: Write a Java stored procedure: the first step is to write a Java program to be made into a stored procedure. Can I use standard Java or SQLJ? Because stored procedures are generally SQL-intensive, writing stored procedures with SQLJ is a highly efficient method.

Public class Foo { Public static String prependHello (String tail ){ Return "Hello" + tail ;}}

Step 2: deploy and release the stored procedure: after writing a Java program using Java development tools, load it into Oracle8i. Use the loadjava command line tool provided by Oracle to load Java programs into the target database outline of Oracle8i in the form of source files, binary files,. class files, and Java. jar archive files. Loadjava is a Java program that uses the Oracle JDBC driver to connect to the server and automatically loads a group of Java programs into the server. (You can also use the create java tool, which is the SQLDDL command of SQL * Plus)

> Loadjava-user scott/tiger @ oudelsrv-1: 5521: ORCL Foo. class

After the command is executed, the foo-like method is loaded into scott's outline (see section Java, SQL, and PL/SQL names to learn about namespace resolution ). The next step is to register the method to SQL. Why is this step indispensable? If only PL/SQL is used, there is no need to explicitly register the PL/SQL process to SQL? For SQL, each PL/SQL process is automatically visible. On the contrary, when Java is used, all Java methods are invisible to SQL? To make a Java method a stored procedure, you must explicitly "publish" it to SQL to call it in SQL. Java classes and methods are not automatically published to data dictionaries, SQL, and PL/SQL for two reasons:

Most methods in Java applications are usually called by other Java programs. Besides, Java-to-Java calls occur in the Java Virtual Machine environment and do not need to be exposed to SQL.

The SQL Compiler must provide guidance on how to map parameters from SQL data types to Java data types, and correctly reload the call information to provide the SQL data type corresponding to the original actual parameter values. This requires the intervention of developers, so only some top-level Java methods need to be called in SQL and PL/SQL.