Stored Procedure 1

Stored procedures are written by flow control and SQL statements. These procedures are compiled and optimized.
And then stored in the database server. You only need to call it for use. In ORACLE, several
Associated processes can be combined to form a package.
Using Stored Procedures has the following advantages:
* The Stored Procedure capability greatly enhances the functionality and flexibility of the SQL language. Stored Procedure
It is written with a flow control statement. It has great flexibility and can complete complicated judgment and complicated
Operation.
* Ensures data security and integrity.
# Using Stored Procedures, users without permissions can indirectly access data under control
To ensure data security.
# Through the stored procedure, relevant actions can be taken together to maintain data
The integrity of the database.
* Before running the stored procedure, the database has analyzed its syntax and provided
Optimized the execution scheme. This compiled process can greatly improve the performance of SQL statements.
Because most of the SQL statement execution has been completed, the stored procedure can run at an extremely fast speed.
Line.
* Reduces network traffic.
* Put the computing program that embodies the enterprise rules into the database server:
# Centralized control.
# When enterprise rules change, you only need to change the stored procedure on the server without modifying it.
Any application. Enterprise rules are characterized by frequent changes.
When the business rule changes, you need to modify the application.
The workload is huge (modifying, releasing, and installing applications ). If
Computation is placed in the stored procedure. When enterprise rules change, you only need to modify the stored procedure.
The application does not need to change.
Different database stored procedures are written differently. In the subsequent lectures, we will introduce Oracle
And Sybase Stored Procedure usage.

2. Oracle stored procedures

The syntax for creating a stored procedure in Oracle is:
Create [or replace] procedure process name
Parameter 1 [in | Out | in out] Data Type
[, Parameter 2 [in | Out | in out] data type]...
{Is | as} PL/SQL statement
The following example describes how to write and use ORACLE database stored procedures.
You can create a stored procedure. When you modify important data in the database
The user name, date, and operation type are recorded as follows:
Create procedure update_log is
Begin
Insert into update_log_tab (use_name, update_date, Operation)
Values (user, sysdate, 'update'
End;
You can call this stored procedure at an appropriate location to record your changes to the table. For example
Create a modification trigger on the table sal_comm. After you modify the table
The time and operation are recorded in the update_log_tab of the table:
Create trigger audit_update
After update on sal_comm
For each row
Begin
Update_log
End

Three Sybase stored procedures

Although Sybase stored procedures are similar in functionality and writing to Oracle
There are some differences. The following describes the Sybase stored procedure.
Sybase can use the create procedure command to generate a stored procedure:
Create procedure stored procedure name [; number]
[[(] @ Parameter_name datatype [= default] [Output]
[, @ Parameter_name datatype [= default] [Output]... [)]
[With recompile]
As SQL _statements
The following is a stored procedure for querying the author's code, name, and birthday:
Create proc p_auths @ author_code varchar (10)
As
Select author_code, name, birthdate
From auths
Where author_code = @ author_code
The following execution process p_auths:
P_auths @ author_code = a00001
In the create proc statement, you can assign a default value to the parameter, which can be any constant.
If you do not provide a parameter value, the value is provided to the process as a parameter value.