sqlplus 可以执行 PL/SQL 写的程序

sqlplus 可以执行 PL/SQL 写的程序。

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 3 09:59:25 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

  1  DECLARE
  2
  3      a NUMBER := 3;
  4
  5  BEGIN
  6
  7      a := a + 1;
  8
  9  END;
 10*

PL/SQL 过程已成功完成。

As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it in sqlplus or by putting the code in a file and invoking the file in the various ways we learned in Getting Started With Oracle.

PL/SQL allows you to branch and create loops in a fairly familiar way.

An IF statement looks like:

IF  THEN  ELSE  END IF;

The ELSE part is optional. If you want a multiway branch, use:

IF  THEN ...

ELSIF  THEN ...

... ...

ELSIF  THEN ...

ELSE ...

END IF;

The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:

DECLARE

    a NUMBER;

    b NUMBER;

BEGIN

    SELECT e,f INTO a,b FROM T1 WHERE e>1;

    IF b=1 THEN

        INSERT INTO T1 VALUES(b,a);

    ELSE

        INSERT INTO T1 VALUES(b+10,a+10);

    END IF;

END;

.

run;

Loops are created with the following:

LOOP

     /* A list of statements. */

END LOOP;

At least one of the statements in should be an EXIT statement of the form

EXIT WHEN ;

The loop breaks if is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

DECLARE

    i NUMBER := 1;

BEGIN

    LOOP

        INSERT INTO T1 VALUES(i,i);

        i := i+1;

        EXIT WHEN i>100;

    END LOOP;

END;

.

run;

Some other useful loop-forming statements are:

• EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.
• A WHILE loop can be formed with

      WHILE  LOOP
  
          
  
      END LOOP;

• A simple FOR loop can be formed with:

      FOR  IN .. LOOP
  
          
  
      END LOOP;

  Here, can be any variable; it is local to the for-loop and need not be declared. Also, and are constants.