Plsql 简明教程

PL/SQL - Procedures

在本章中,我们将讨论 PL/SQL 中的过程。 subprogram 是执行特定任务的程序单元/模块。这些子程序组合在一起形成更大的程序。这基本上被称为“模块化设计”。可以通过称为 calling program 的另一个子程序或程序来调用子程序。

In this chapter, we will discuss Procedures in PL/SQL. A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

可以创建子程序 −

A subprogram can be created −

  1. At the schema level

  2. Inside a package

  3. Inside a PL/SQL block

在模式级别,子程序是 standalone subprogram 。它使用 CREATE PROCEDURE 或 CREATE FUNCTION 语句创建。它存储在数据库中,可以使用 DROP PROCEDURE 或 DROP FUNCTION 语句进行删除。

At the schema level, subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or the CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.

在包内创建的子程序是 packaged subprogram 。它存储在数据库中,并且仅当包使用 DROP PACKAGE 语句删除时才能删除。我们将在 'PL/SQL - Packages' 章中讨论包。

A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. We will discuss packages in the chapter 'PL/SQL - Packages'.

PL/SQL 子程序是带有一组参数的命名的 PL/SQL 块,可以使用这些参数对其进行调用。PL/SQL 提供两种子程序 −

PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms −

  1. Functions − These subprograms return a single value; mainly used to compute and return a value.

  2. Procedures − These subprograms do not return a value directly; mainly used to perform an action.

本章将介绍 PL/SQL procedure 的重要方面。我们将在下一章讨论 PL/SQL function

This chapter is going to cover important aspects of a PL/SQL procedure. We will discuss PL/SQL function in the next chapter.

Parts of a PL/SQL Subprogram

每个 PL/SQL 子程序都有一个名称,还可以有一个参数列表。与匿名 PL/SQL 块一样,命名的块还将具有以下三部分 −

Each PL/SQL subprogram has a name, and may also have a parameter list. Like anonymous PL/SQL blocks, the named blocks will also have the following three parts −

S.No

Parts & Description

1

Declarative Part It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.

2

Executable Part This is a mandatory part and contains statements that perform the designated action.

3

Exception-handling This is again an optional part. It contains the code that handles run-time errors.

Creating a Procedure

使用 CREATE OR REPLACE PROCEDURE 语句来创建过程。CREATE OR REPLACE PROCEDURE 语句的简化语法如下 −

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;

其中,

Where,

  1. procedure-name specifies the name of the procedure.

  2. [OR REPLACE] option allows the modification of an existing procedure.

  3. The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.

  4. procedure-body contains the executable part.

  5. The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Example

以下示例创建了一个简单过程,在执行时,会在屏幕上显示字符串“Hello World!”。

The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed.

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
/

当使用 SQL 提示执行以上代码时,它将会生成以下结果 −

When the above code is executed using the SQL prompt, it will produce the following result −

Procedure created.

Executing a Standalone Procedure

独立过程可以通过两种方式来调用 −

A standalone procedure can be called in two ways −

  1. Using the EXECUTE keyword

  2. Calling the name of the procedure from a PL/SQL block

通过 EXECUTE 关键字可以调用名为 'greetings' 的上述过程,如下所示 −

The above procedure named 'greetings' can be called with the EXECUTE keyword as −

EXECUTE greetings;

上述调用将显示 −

The above call will display −

Hello World

PL/SQL procedure successfully completed.

这个过程也可从另一个 PL/SQL 块调用 -

The procedure can also be called from another PL/SQL block −

BEGIN
   greetings;
END;
/

上述调用将显示 −

The above call will display −

Hello World

PL/SQL procedure successfully completed.

Deleting a Standalone Procedure

使用 DROP PROCEDURE 语句删除独立过程。语法用于删除过程是 -

A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is −

DROP PROCEDURE procedure-name;

你可以使用以下语句来删除 greetings 过程 -

You can drop the greetings procedure by using the following statement −

DROP PROCEDURE greetings;

Parameter Modes in PL/SQL Subprograms

在 PL/SQL 子程序中输出参数模式列表 -

The following table lists out the parameter modes in PL/SQL subprograms −

S.No

Parameter Mode & Description

1

IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.

2

OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.

3

IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

IN & OUT Mode Example 1

此程序查找两个值的最小值。此处,过程使用 IN 模式采用两个数字,并使用 OUT 参数返回它们的最小值。

This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.

DECLARE
   a number;
   b number;
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END;
BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

当以上代码在 SQL 提示符下执行时,它会生成以下结果:

When the above code is executed at the SQL prompt, it produces the following result −

Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.

IN & OUT Mode Example 2

此过程计算传递值值的平方。这个例子显示了我们如何可以使用相同参数来接受一个值,然后返回另一个结果。

This procedure computes the square of value of a passed value. This example shows how we can use the same parameter to accept a value and then return another result.

DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x * x;
END;
BEGIN
   a:= 23;
   squareNum(a);
   dbms_output.put_line(' Square of (23): ' || a);
END;
/

当以上代码在 SQL 提示符下执行时,它会生成以下结果:

When the above code is executed at the SQL prompt, it produces the following result −

Square of (23): 529

PL/SQL procedure successfully completed.

Methods for Passing Parameters

实际参数可以通过三种方式传递 -

Actual parameters can be passed in three ways −

  1. Positional notation

  2. Named notation

  3. Mixed notation

Positional Notation

在位置符号中,你可以调用过程为 -

In positional notation, you can call the procedure as −

findMin(a, b, c, d);

在位置符号中,第一个实际参数替换了第一个形式参数;第二个实际参数替换了第二个形式参数,以此类推。因此, a 替换为 x, b 替换为 y, c 替换为 z 替换 d 替换为 m

In positional notation, the first actual parameter is substituted for the first formal parameter; the second actual parameter is substituted for the second formal parameter, and so on. So, a is substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.

Named Notation

在已命名符号中,实际参数与形式参数使用 arrow symbol ( ⇒ ) 关联。过程调用将像以下示例 -

In named notation, the actual parameter is associated with the formal parameter using the arrow symbol ( ⇒ ). The procedure call will be like the following −

findMin(x => a, y => b, z => c, m => d);

Mixed Notation

在混合符号中,你可以在过程调用中混合这两种符号;但位置符号应先于已命名符号。

In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.

以下调用是合法的 -

The following call is legal −

findMin(a, b, c, m => d);

然而,这是不合法的:

However, this is not legal:

findMin(x => a, b, c, d);