Shulou(Shulou.com)06/01 Report--

1. Definition

A stored procedure (Procedure) is a set of SQL statements used to perform specific database functions. The set of SQL statements passes through the

After compilation, it is stored in the database system. When in use, the user specifies the name of the defined stored procedure and gives the corresponding stored procedure parameters

To invoke and execute it to complete one or a series of database operations.

2. Creation of stored procedure

The Oracle stored procedure consists of three parts: procedure declaration, execution procedure, and stored procedure exception.

3. Benefits of using stored procedures:

1. Simplify complex operations

two。 Increase the independence of data

3. Improve security

4. Syntax for stored procedures

CREATE [OR REPLACE] PROCEDURE procedure_name [in | out | in out datatype]

IS | AS

Begin

Exception

End

4.1 stored procedures with no parameters:

Create or replace procedure p_test1 asbeginfor i in (select slbh,bdczh from dj_djb where slbh like '20170825%') loopdbms_output.enable (buffer_size= > null); dbms_output.put_line (i.slbh | |','| | i.bdczh); end loop;end

Begin

P_test1

End

4.2. Stored procedures with parameters:

4.2.1. Parameter name IN data type DEFAULT value

Define an input parameter variable to pass parameters to the stored procedure. When calling a stored procedure, the actual parameters of the main program can be constants, valued variables, expressions, and so on. The DEFAULT keyword is optional and is used to set the default value of the parameter. If the parameter is not specified when calling the stored procedure, the parameter variable takes the default value. In a stored procedure, the input variable receives the value passed by the main program, but cannot be assigned to it.

Ege:

Create or replace procedure p_test2 (v_slbh in varchar2 default 201711010025) asv_bdczh dj_djb.bdczh%type;begin select bdczh into v_bdczh from dj_djb where slbh = vested slbh; dbms_output.put_line (v_bdczh); end

4.2.2. Parameter name OUT data type

Define an output parameter variable that is used to get data from the stored procedure, that is, the variable returns a value from the stored procedure to the main program.

When calling a stored procedure, the actual parameter of the main program can only be a variable, not a constant or an expression. In a stored procedure, parameter variables can only be assigned and cannot be used for assignment, and the output variable must be assigned at least once in the stored procedure.

Ege:

Create or replace procedure p_test3 (v_slbh varchar2 default 201711010025) asbegin select bdczh into v_bdczh from dj_djb where slbh=v_slbh; dbms_output.put_line (v_slbh | |','| | v_bdczh); end;.... Call. Declare end 1 dj_djb.bdczh%type; begin p_test3 (vroombdczhh = > vroom1)

4.2.3. Parameter name IN OUT data type DEFAULT value

Define an input and output parameter variable, which has the functions of both. When calling a stored procedure, the actual parameter of the main program can only be a variable, not a constant or an expression. The DEFAULT keyword is optional and is used to set the default value of the parameter. In the stored procedure, the variable receives the value passed by the main program and can participate in the assignment operation or assign it. Variables must be assigned at least once in a stored procedure.

Note: if IN, OUT, or IN OUT are omitted, the default mode is IN.

5. View the reason for the stored procedure error:

Select * from user_errors where name=upper ('paired test 1')

By wolihaito 2018.03.26

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.