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

We all know that SQL language is no judgment and process control statements, and PL/SQL is a good complement to SQL language deficiencies in this regard. A stored procedure is also a PL/SQL block, but unlike traditional PL/SQL blocks, stored procedures are stored in the database by name. It has many advantages, such as:

Stored procedures are stored in the database as named database objects. The advantage of storing in a database is obvious, because the code is not stored locally and users can log in to the database from any client and invoke or modify the code.

Stored procedures can be provided by the database security assurance, in order to use stored procedures, you need to have the authorization of the owner of the stored procedure, only authorized users or the creator himself can call to execute the stored procedure.

The information of the stored procedure is written into the data dictionary, so the stored procedure can be regarded as a common module, and PL/SQL programs written by users or other stored procedures can call it (but stored procedures and functions cannot call PL/SQL programs). A reusable function can be designed as a stored procedure.

Like procedures and functions in other high-level languages, parameters can be passed to stored procedures, and there are many ways to pass parameters. Stored procedures may or may not have a return value, and the return value of a stored procedure must be brought back through parameters; functions have certain data types, and like other standard functions, we can return the function value by calling the function name.

5. Stored procedures need to be compiled to exclude syntax errors, and can only be called if compiled.

Oracle stored procedure basic syntax

CREATE OR REPLACE PROCEDURE stored procedure name (parameter)

IS/AS

variables

BEGIN

executable portion

EXCEPTION

error processing section

END;

Take the example of hello world.

create or replace procedure hello as

say_hi varchar2(20);

begin

say_hi := 'Hello World';

dbms_output.put_line(say_hi);

end;

/

Syntax Description:

Create or replace procedure is a basic syntax for creating stored procedures.

2. There is not much difference between IS and AS in stored procedures (PROCEDURE) and functions (FUNCTION). AS can only be used in views (VIEW), but IS cannot be used in cursors (CURSOR). This is usually followed by variable declarations.

Between begin and end is the PL/SQL program body, where exception specifies the failure handling process.

call a stored procedure

begin

-- Call the procedure

hello;

end;

View and delete stored:

Query the creation script for the stored procedure hello

select * from user_source where name='HELLO';

View the status of the stored procedure hello

select * from user_objects where object_name = 'HELLO';

Focus on the status column, valid means that the stored procedure is compiled, invalid

We can see that a stored procedure has parameters that can be declared, so there must be a difference between input and output parameters about parameters.

Three types of parameters

1. IN defines an input parameter variable used to pass parameters to stored procedures

OUT defines an output parameter variable used to retrieve data from stored procedures

3. IN OUT defines an input and output parameter variable, which has both functions.

Parameters are defined as follows:

IN parameters

Syntax: Parameter Name IN Data Type DEFAULT Value;

Defines an input parameter variable used to pass parameters to stored procedures. The actual parameters of the main program can be constants, valued variables, expressions, etc. when calling stored procedures. The DEFAULT keyword is optional and is used to set default values for parameters. If no parameter is specified when the stored procedure is invoked, the parameter variable takes its default value. In stored procedures, input variables receive values passed by the main program, but cannot be assigned values.

OUT parameter

Syntax: parameter name OUT data type;

Defines an output parameter variable that is used to retrieve data from a stored procedure, i.e., the variable returns a value from the stored procedure to the main program.

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

IN OUT parameter

Syntax: Parameter name IN OUT Data type DEFAULT value;

Define an input and output parameter variable that performs both functions. When a stored procedure is called, the actual argument to the main program can only be a variable, not a constant or expression. The DEFAULT keyword is optional and is used to set default values for parameters. During storage, variables receive values passed by the main program and can participate in assignment operations or be assigned values. Variables must be assigned values at least once in a stored procedure.

1. Examples of parameters in

create or replace procedure say_hello (to_who in varchar2 default 'zhangsan') as

begin

dbms_output.put_line('Say Hi to '|| to_who);

end;

implementation

begin

-- Call the procedure

say_hello( 'peter');

end;

2. Examples of parameter out

Parameters of output mode, used to output values, ignore incoming values. It can be modified within the subroutine.

Output: After the subroutine is executed, the final value of the out mode parameter is assigned to the corresponding one at the time of the call.

Note: Out mode parameters must be called through variables.

create or replace procedure pout(p1 out int) as

begin

p1 := 33;

end;

implementation

declare

var1 int := 30;

begin

dbms_output.put_line(var1);

pout(var1);

dbms_output.put_line(var1);

end;

First output 30, second output 33.

3. Examples of parameters in and out

Input output mode: can receive the actual parameter value passed; can be modified inside the subroutine; can output (must be called with the actual parameter variable)

create or replace procedure pinout(p1 in out int) as

begin

dbms_output.put_line(p1);

p1 := 44;

end;

implementation

declare

var1 int := 40;

begin

dbms_output.put_line(var1);

pinout(var1);

dbms_output.put_line(var1);

end;

So far we've covered some basic concepts of stored procedures, and in the next blog post we'll cover some advanced features of stored procedures.

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.