使用缺省参数定义过程

可以使用可选参数创建外部过程和 SQL 过程。可选过程参数定义为具有缺省值。

假设您有一个使用参数定义的过程，如下所示。这是 SQL 过程的一部分还是外部过程语句无关紧要; 此处仅讨论参数定义。

       CREATE PROCEDURE UPDATE_EMPLOYEE_INFO
         (IN EMPLOYEE_NUMBER CHAR(10),
          IN EMP_DEPT CHAR(3),
          IN PHONE_NUMBER CHAR(4))
          . . .

此过程已使用了很长时间，并且从许多地方调用。现在，有人建议将此过程更新同一表中的几个其他列 (JOB 和 EDLEVEL) 是有用的。查找和更改对此过程的所有调用是一项巨大的工作，但如果添加新参数以使其具有缺省值，那么非常容易。

以下 CREATE PROCEDURE 语句中的参数定义将允许选择性地传入除员工编号以外的所有列。

       CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE_INFO
         (IN EMPLOYEE_NUMBER CHAR(10),
          IN EMP_DEPT CHAR(3) DEFAULT NULL,
          IN PHONE_NUMBER CHAR(4) DEFAULT NULL,
          IN JOB CHAR(8) DEFAULT NULL,
          IN EDLEVEL SMALLINT DEFAULT NULL)
          . . .

需要修改此过程的代码 (SQL 例程或外部程序) 以处理新参数，并在传递 NULL 值时正确处理两个现有参数。由于缺省参数是可选的，因此对该过程的任何现有调用都不需要更改; 这两个新参数将向过程代码传递值 NULL。需要新参数的任何调用者都可以在 SQL CALL 语句中包含这些参数。

虽然此示例将 NULL 用于所有缺省值，但几乎可以使用任何表达式。它可以是简单常量，也可以是复杂查询。它不能引用任何其他参数。

有几种方法可以将缺省值用于 CALL 语句。

省略末尾不需要使用的参数。
```
CALL UPDATE_EMPLOYEE_INFO('123456', 'D11', '4424');
```
缺省值将用于 JOB 和 EDLEVEL 参数。

对任何省略的参数使用 DEFAULT 关键字。
```
CALL UPDATE_EMPLOYEE_INFO('123456', DEFAULT, '4424', DEFAULT, DEFAULT);
```
所有参数都在此语句中表示。缺省值将用于 EMP_DEPT ， JOB 和 EDLEVEL 参数。

使用相应的参数名称显式命名某些参数，并省略未使用的参数。
```
CALL UPDATE_EMPLOYEE_INFO('123456', EDLEVEL => 18);
```
通过使用参数名称，其他三个参数不需要在此 CALL 语句中表示。缺省值将用于 EMP_DEPT ， PHONE_NUMBER 和 JOB 参数。

指定的自变量可以是 CALL 语句中的任意顺序。未命名的自变量必须与过程的参数定义的顺序相匹配，并且必须在任何指定的自变量之前指定。一旦在语句中使用了指定的自变量，那么还必须对该自变量后面的所有自变量进行命名。在 CALL 语句中没有自变量的任何参数都必须定义缺省值。

在以下示例中，该过程将创建一个表，其中包含指定部门中的所有员工。创建该模式的模式始终针对使用该模式的环境进行硬编码。但是，对于测试，在测试模式中创建表很方便。

CREATE OR REPLACE PROCEDURE CREATE_DEPT_TABLE2 
                   (IN P_DEPT CHAR(3),
                    IN SCHEMA_NAME VARCHAR(128) DEFAULT 'CORPDATA')
   BEGIN
     DECLARE DYNAMIC_STMT VARCHAR(1000);
     DECLARE MESSAGE CHAR(20);
     DECLARE TABLE_NAME VARCHAR(200);
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
       SET MESSAGE = 'ok';

     SET TABLE_NAME = '"' CONCAT SCHEMA_NAME CONCAT
           '".DEPT_' CONCAT P_DEPT CONCAT '_T';

     SET DYNAMIC_STMT = 'DROP TABLE ' CONCAT TABLE_NAME;
     EXECUTE IMMEDIATE DYNAMIC_STMT;

     SET DYNAMIC_STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT
       '( EMPNO CHAR(6) NOT NULL,
          FIRSTNME VARCHAR(12) NOT NULL,
          MIDINIT CHAR(1) NOT NULL,
          LASTNAME CHAR(15) NOT NULL,
          SALARY DECIMAL(9,2))';
     EXECUTE IMMEDIATE DYNAMIC_STMT;

     SET DYNAMIC_STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT
       'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
         FROM CORPDATA.EMPLOYEE
         WHERE  WORKDEPT = ?';
     PREPARE INSERT_INTO_DEPARTMENT_STMT FROM DYNAMIC_STMT;
     EXECUTE INSERT_INTO_DEPARTMENT_STMT USING P_DEPT;
END;

定义了第二个参数以传递模式名称。它具有缺省值 "CORPDATA"。这是该过程在过去使用过的值。

在生产环境中运行时， CALL 语句可能是:

CALL CREATE_DEPT_TABLE2('D21');

由于未指定 SCHEMA_NAME 参数，因此将使用缺省参数值。表 DEPT_D21 是在 CORPDATA 中创建的。

在测试环境中运行时， CALL 语句可能是:

CALL CREATE_DEPT_TABLE2('D21', 'TESTSCHEMA');

这将在指定的模式 TESTSCHEMA 中创建 tableDEPT_D21 。