Java Oracle Stored Procedure Problems

Java Oracle Stored Procedure Problems
PL/SQL is an extension of ORACLE's standard database language. ORACLE has integrated PL/SQL into ORACLE servers and other tools, in recent years, more developers and DBAs have started to use PL/SQL. This article describes basic PL/SQL syntax, structures and components, and how to design and execute a PL/SQL program.

Advantages of PL/SQL

PL/SQL has been reliably integrated into ORACLE since version 6. Once you master the advantages of PL/SQL and its unique data management convenience, it is hard to imagine that ORACLE lacks PL/SQL. PL/SQL is not an independent product. It is a technology integrated into ORACLE servers and ORACLE tools. It can regard PL/SQL as an engine in ORACLE servers, the executor of the SQL statement processes a single SQL statement, and the PL/SQL engine processes the PL/SQL block. When the PL/SQL block is processed by the PL/SQL engine, the SQL statement executor on the ORACLE server processes the SQL statements in the pl/SQL block.

PL/SQL has the following advantages:

. PL/SQL is a high-performance transaction-based language that runs in any ORACLE environment and supports all data processing commands. Use PL/SQL program units to process SQL data definitions and data control elements.

. PL/SQL supports all SQL data types and all SQL functions, and all ORACLE Object Types

. PL/SQL blocks can be named and stored on the ORACLE server, and can also be called by other PL/SQL programs or SQL commands. Any client/server tool can access the PL/SQL program, it has good reusability.

. You can use ORACLE data tools to manage the security of PL/SQL programs stored on servers. Other database users can be authorized or revoked to access PL/SQL programs.

. PL/SQL code can be written in any ASCII text editor, so it is very convenient for any operating system that ORACLE can run.

. For SQL, ORACLE must process each SQL statement at the same time. In the network environment, this means that each independent call must be processed by the oracle server, this takes a lot of server time and causes network congestion. PL/SQL sends the entire statement block to the server, which reduces network congestion.

PL/SQL Block Structure

PL/SQL is a block-structured language. The unit of a PL/SQL program is a logical block. a pl/SQL program contains one or more logical blocks, each block can be divided into three parts. Like other languages, variables must be declared before they are used. PL/SQL provides an independent part dedicated to exception handling. The following describes the different parts of PL/SQL blocks:

Declaration section)

The declaration part contains the data types and initial values of variables and constants. This part starts with the keyword DECLARE. If you do not need to DECLARE variables or constants, you can ignore this part. It must be noted that the declaration of the cursor is also in this part.

Executable section)

The execution part is the instruction part in the PL/SQL block, starting with the keyword BEGIN. All executable statements are placed in this part, and other PL/SQL blocks can also be placed in this part.

Exception section)

This part is optional. In this part, we will discuss Exception Handling in detail.

PL/SQL block syntax

[DECLARE]---declaration statementsBEGIN---executable statements[EXCEPTION]---exception statementsEND

Each statement in the PL/SQL block must end with a semicolon. The SQL statement can contain multiple rows, but the semicolon indicates the end of the statement. A row can contain multiple SQL statements separated by semicolons. Each PL/SQL block starts from BEGIN or DECLARE and ends with END. Annotation is marked.

Naming and anonymity of PL/SQL Blocks

The PL/SQL block can be a named block or an anonymous block. Anonymous blocks can be used on servers or clients.

The named program block can appear in the declaration part of other PL/SQL program blocks. It is obvious that the subroutine can be referenced in the execution part or in the exception handling part.

The PL/SQL block can be compiled independently and stored in the database. Any database-connected application can access the PL/SQL block. ORACLE provides four types of stored programs:

. Function

. Process

. Package

. Trigger

Function

The function is a named PL/SQL block stored in the database. The function accepts zero or multiple input parameters and has a return value. The data type of the returned value is defined when the function is created. The syntax for defining a function is as follows:

FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS[local declarations]BEGINexecute statements[EXCEPTIONexception handlers]END [name]

Process

A stored procedure is a PL/SQL block that accepts zero or multiple parameters as INPUT or OUTPUT, or as both INPUT and OUTPUT (INOUT ), unlike functions, stored procedures do not return values. stored procedures cannot be directly used by SQL statements. They can only be called using the EXECUT command or within the PL/SQL block. The syntax for defining stored procedures is as follows:

PROCEDURE name [(parameter[,parameter,...])] IS[local declarations]BEGINexecute statements[EXCEPTIONexception handlers ]END [name]

Package)