Oracle stored Procedures (1)

Description

Creating a stored procedure is much like writing a normal PL/SQL program, such as including the life part, the execution part, and the exception part. But there are a lot of differences between the two implementations, such as: Creating a stored procedure requires using the procedure keyword, After the keyword is the procedure name and the parameter list; creating a stored procedure does not require the use of the DECLARE keyword, but instead uses create or replace.

To create a Process object (procedural object), you must have create PROCEDURE system permissions. If the process object needs to be used by another user schema, then you must have the CREATE any PROCEDURE permission. You may need to excute permissions when executing procedure. Or excute any PROCEDURE permissions. If you assign permissions individually, the following example shows:

Grant execute on My_procedure to Jelly

The basic syntax is as follows:

CREATE [OR REPLACE] PROCEDURE stored procedure name [(Parameter [in| Out| In out] data type ...)
{As|is}
[Description section]
BEGIN
Executable section
[EXCEPTION
Error Handling section]
END [procedure name];

Where: Optional keyword or REPLACE means that if a stored procedure already exists, it is overwritten with a new stored procedure and is typically used for the rebuild of the stored procedure. The parameters section is used to define multiple parameters (which can be omitted if there are no parameters). There are three types of parameters: in, out, and in. If no parameter is specified, the default is in. The keyword as can also be written as is, followed by the Description section of the procedure, where you can define local variables for the procedure. Writing stored procedures can be done using any text editor or directly in a sql*plus environment, and written stored procedures must be compiled in a sql*plus environment, generate compiled code, and the original code and compiled code will be stored in the database during compilation. A successful compilation of stored procedures can be invoked in an Oracle environment. A stored procedure can be deleted when it is not needed. The person who deletes the stored procedure is the creator of the procedure or the person who owns the drop any procedure system permission. The syntax for deleting a stored procedure is as follows:

DROP PROCEDURE stored procedure name;

Advantages of stored Procedures

• Execution is highly efficient because the stored procedure is precompiled, that is, when it is created, and the SQL statement is executed once and compiled once. Calling a stored procedure can significantly reduce the number of interactions with the database.
• Reduce network traffic because when the stored procedure executes, only the call stored procedure name is required and no large number of SQL statements are passed.
• Beneficial for reuse.

Disadvantages of stored procedures

• The portability is very poor, if the stored procedure written on Oracle, porting to MySQL needs to be modified.
• Code that is poor in readability, implements a simple logic, and the code is very long.

Purpose of the stored procedure

• Test data: You can use stored procedures to create millions of of data in the table.
• Data synchronization: Data synchronization between two tables is performed according to a certain business logic.
• Data mining.

Stored Procedure Considerations

• When the amount of data is large (100,000 +), be sure to do stress testing and some stored procedures will be problematic in the case of large data volumes.
• If the number of insertions or updates is more, in order to improve efficiency, you can execute 10,000 times, and then commit once.
• If you insert a record first, no commit, and then update the record, it can cause a deadlock. If the same record is updated successively, and there is no commit, it can also cause deadlock. Because the latter statement waits for the previous statement to be committed. If this is the case, a commit is required.
• Do not forget to write a commit in the stored procedure.

Performance testing

• Inserting 400,000 data with a stored procedure takes 10 seconds.
• It took 25 seconds to traverse and judge 400,000 data.
• 800,000 SQL judgements + 400,000 SQL Inserts = 25 seconds.

Extended

• The stored procedure executes very slowly, possibly because the UPDATE statement caused the deadlock, or the statement execution is slow (need to build an index).
• The stored procedure compiles very slowly, it is possible that the current stored procedure is executing and is locked. (Unlock with DBA account).

Instance:

(1) Create a table

CREATE TABLE Dept (ID int,name varchar (), address varchar (20));

(2) Creating a stored procedure

Create or replace procedure pro_insertdept is
Begin
INSERT INTO Dept values (+, ' Shimang ', ' Beijing ');
Commit
Dbms_output.put_line (' Insert sucess! ');
End Pro_insertdept;

(3) Execute stored procedure

Sql> Execute pro_insertdept;

The PL/SQL process has completed successfully.

Sql> select * FROM dept;

ID NAME----------------------------------------------------------------------ADDRESS--------------------------- -------------

Shimang Beijing

Oracle stored Procedures (1)