Oracle Foundation <1>-Stored procedures

One: Advantages of stored procedures

(1. Modularity: Through subroutines, can be used to implement a business logic, a business function.

(2. reusability: Once a stored procedure is created, it can be used in any number of applications.

(3. Serviceability: Can simplify maintenance operations

(4. Security: Users can set permissions.)

Disadvantages:

1. Migration issues

2. recompiling the issue

3. Change of requirements

II: Usage of stored procedures

1. Creation of stored procedures

Create syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name--Name

[(Paraameter_list)]--the parameters to be used

{Is/as}

[local_declarations]--locally declared variables

BEGIN

What the executable_statements;--does

[EXCEPTION]

[Exception_handlers]--Exception handling section

END [Procedure_name]--Name

　　Description :

Procedure_name: The name of the stored procedure;

Paraameter_list: Parameter list, in and out parameters can be used to represent input and output parameters. Options available

As represents the other variable declaration, is indicates that the cursor declaration is displayed

Local_declarations: local declarations, optional

Executable_statements: Executable statement

Exception_handlers: Exception handling statement, optional

OR REPLACE: Optional. If not, create a stored procedure if there is an error, the inclusion exists will be replaced.

2. Calls to Stored procedures

Call Syntax:

Execute procedure_name (parameters_list);

Description

Execute: Executes the command, which can be abbreviated to exec.

Procedure_name: The name of the stored procedure.

Parameters_list: A list of parameters for the stored procedure.

　　　　1) command line mode:2) plsql mode: --PL/SQL mode, do not need to use exec

3, the parameter mode of the stored procedure:

(1) In: input

(2) Out: Output

(3) in-out: input \ Output

Define stored procedure parameter syntax:

parameter_name [in| Out| In out] datetype [{: = | default} expression]

Attention:

　　1) The parameter in mode is the default mode. If no parameter mode is specified, the default is in. For out and in out parameters, it must be explicitly specified.

2) You can specify a default value for the in parameter in the parameter list, and out and in is not available.

4. Authorization of Stored procedure

　　Grammar:

*grant execute on procedure_name (stored procedure name) to user--gives users the right to execute

*grant execute on procedure_name (stored procedure name) to user with GRANT option -the user can also have this permission granted to other users

5. Deletion of stored procedures

　　Grammar:

Dropp procedure name;

Three. Program block tracking and debugging

1, debugging under the Sql*plus

Grammar:

Show errors (the type of the program block to view procedure) (the name of the program block); --See if a program block has a compilation error

2, in PL/SQL Developer tool debugging

F9: Start Debugging

Ctrl+r: Execution

CTRL + N: Single Step Into

Ctrl+o: one-step skip

Ctrl+t: Step Outside

Four. Procedure and cursor usage

　　 --Stored Procedures with cursors
CREATEORREPLACEPROCEDURE pro_emp_in (emp_cur sys_refcursor) AS
V_name EMP. Ename%TYPE;
V_sal EMP. SAL%TYPE;
BEGIN
LOOP
FETCH Emp_cur
      into V_name, v_sal;
    Dbms_output. Put_Line ( ' name:|| V_name | | '    sal:|| v_sal);
    exit when Emp_cur%notfound;
  end LOOP;
end;

--calls a stored procedure with a cursor :
declare
  Emp_cur Sys_refcursor;
begin
  for
    select ename, SAL from EMP; Pro_emp_in (emp_cur => emp_cur);
  close Emp_cur;
end;

　　

--Calling a stored procedure with a cursor with an output typeDECLAREV_name EMP. Ename%TYPE; V_sal EMP. SAL%TYPE; Emp_cur Sys_refcursor;--declaring system cursor TypesBEGINPro_emp (emp_cur=Emp_cur); LOOPfetch emp_cur into v_ NAME, V_sal; Dbms_output. Put_Line ( ' name:|| V_name | |  '  Sal: ||  v_sal); exit when emp_cur%< Span style= "color: #000000;" >notfound; end LOOP; close Emp_cur; END;                 

< Span style= "color: #0000ff;" > Five. Procedure and transaction usage

(1) Self-service: is a separate transaction initiated by another transaction processing (primary transaction).

Autonomous transaction: is an independent transaction initiated by 01 transactions. Autonomous transactions can suspend primary transactions, that is, transactions within their own stored procedures, and the primary transaction is resumed when the autonomous transaction has finished processing.

　　PRAGMA autonomous_transaction; --defined as autonomous transactions, not being committed by other transactions, impact of rollback

　　Summary of self-service:

1. The change of the result of the autonomic transaction processing does not depend on the state or final configuration of the primary transaction.

2. When a self-service transaction commits or rolls back, it does not affect the results of the master transaction.

3, the self-service submission once submitted, the results of the autonomy transaction changes for other transactions is the courseware. This means that you can access the updated information without waiting for the primary transaction to commit.

4. Autonomous transaction processing can initiate other autonomous transaction processing.

　　

Oracle Foundation <1>-Stored procedures