Use of stored procedures in Oracle

① Input parameter: If a caller passes the parameter to a stored procedure, no matter how the stored procedure calls the parameter, the value of this parameter cannot be changed. It can be considered that the value of this parameter is read-only. ② Output

① Input parameter: If a caller passes the parameter to a stored procedure, no matter how the stored procedure calls the parameter, the value of this parameter cannot be changed. It can be considered that the value of this parameter is read-only. ② Output

1. Basic Application of Stored Procedures

1. Create a stored procedure (SQL window)

Create or replace procedure update_staff
As
Begin
Update staff set name = 'xy ';
Commit;
End update_staff;

The stored procedure is suitable for updating, especially for updating a large amount of data.

2. view the information of stored procedures in the data dictionary (SQL window)

Select object_name, object_type, status from user_objects where lower (object_name) = 'Update _ staff'

3. View stored procedure statements (SQL window)

Select * from user_source where lower (name) = 'Update _ staff'

4. Execute the Stored Procedure (Command window)

Execute update_staff;

5 Advantages of Stored Procedures

① Improve database execution efficiency. Use SQL interfaces to update databases. If updates are complex and frequent, you need to connect to the database frequently.
② Improve security. Stored procedures are stored as objects in the database and can be assigned permissions.
③ Reusability.

Stored Procedure with input parameters

1. Create a stored procedure (SQL window)

Create or replace procedure update_staff (in_age in number)
Begin
Declare newage number;
Begin
Newage: = in_age + 10;
Update staff set age = newage;
Commit;
End;
End update_staff;

2. Execute the Stored Procedure (Command window)

Execute update_staff (10 );

3 Default Value

Only the in parameter can have default values, such

Create or replace procedure update_staff (in_name in varchar2, in_age in number default 20)
During the call, only execute update_staff ('xy') can be written ');

Stored Procedure with three output parameters

1. Create a stored procedure (SQL window)

Create or replace procedure update_staff (in_age in number, out_age out number)
Begin
Update staff set age = in_age;
Select age into out_age from student where num = 1;
Commit;
End update_staff;

The stored procedure does not display the specified return value, but the output parameters can be output.

2. output the stored procedure result (Command window)

Set serverout on;
Declare age number;
Begin
Update_staff (20, age );
Dbms. output. put_line (age );
End;

Stored Procedure with Input and Output

The most typical application is to exchange two values.

1. Create a stored procedure (SQL window)

Create or replace procedure swap (param1 in out number, param2 in out number)
Begin
Declare param number;
Begin
Param: = param1;
Param1: = param2;
Param2: = param;
End;
End swap;

2. output the stored procedure result (Command window)

Set serverout on;
Declare p1 number: = 25;
P2 number: = 35;
Begin
Swap (p1, p2 );
Dbms_output.put_line (p1 );
End;

Summary of five parameters

① Input parameter: If a caller passes the parameter to a stored procedure, no matter how the stored procedure calls the parameter, the value of this parameter cannot be changed. It can be considered that the value of this parameter is read-only.
② Output parameters: can be used as return values. It can be considered writable.
③ Input and output parameters: the parameters of this type are the most similar to those of java methods. input methods are readable and writable (except final identifiers ).
④ The Parameter order is summarized as follows: a parameter with a default value should be at the end of the parameter list, because sometimes you need to omit this parameter. Parameters without default values can follow "in-> out-> in out ".