创建程序

创建程序 “创建存储过程

句法

CREATE [OR REPLACE] PROCEDURE [schema.]name
    [ ( [ [ IN | INOUT | OUT ] parameter_definition
          [ ,[ IN | INOUT | OUT ] parameter_definition ]... ] ) ]
    [ RETURNS
       [TABLE] table_name ( column_name column_type [ ,column_name column_type ]... )
        [ ,[TABLE] table_name ( column_name column_type [ ,column_name column_type ]... ) ]... ]
    [ LANGUAGE 澳门比分网址 | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'sp_routine'
|
    AS
        sql_code_block
    END_PROCEDURE;
}

描述

The 创建程序 statement is used to create routines that perform a series of 澳门比分网址 statements when invoked by a single 执行 or 称呼 statement (see 执行, 称呼)。 澳门比分网址可以包含数据操作语言(DML)和某些数据定义语言(DDL)语句( 澳门比分网址程序支持的陈述)加上用于控制执行流程或处理/报告错误条件的附加语句(参见 澳门比分网址程序语言)。

When the OR REPLACE clause is used for an existing procedure name, the effect is that of dropping the existing procedure and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a procedure with the specified name does not already exist.

创建程序 也可用于创建在Java中编写的存储过程。看 下面和 使用嵌入式Java存储过程.

Only users assigned the system DBA role can create a stored procedure (see 授予)。 If schema_name is provided, then the procedure is created in the specified schema. Otherwise, it is created in the current schema. The name of the procedure must not match any existing procedure in the same schema.

The user that creates the stored procedure becomes the owner of that stored procedure and must use the 授予 statement to give ALTER执行 privileges to other users in order for them to be able to change or execute the stored procedure. Any user that is granted ALTER to a stored procedure can also DROP the stored procedure. If a user drops and recreates a stored procedure, the new stored procedure is not the same entity as the old; that user will now be the new owner of the stored procedure. That user must 授予 all privileges to other users.

使用创建或替换语句时,与两个不同的事务同时创建相同的数据库对象时,可能会返回以下错误:
duplicate value in unique index ...
如果通过基于表达式的索引调用用户定义的函数,则任何删除或修改函数的尝试都会使用错误警告,但不会重新创建索引。

争论

If the procedure declares one or more arguments, they will appear as variables to all the statements of the procedure. Each argument to the stored procedure is declared as IN, INOUT, or OUT followed by the argument name and its type.

IN declares the argument as an input variable passed to the procedure. The final value of an IN parameter is instead discarded.

You can specify a result set as an IN parameter to a stored procedure. See 使用结果集作为存储过程的输入.

INOUT 将参数称为输入和输出变量传递给过程。它可以通过程序修改并再次返回给呼叫者。

OUT 将参数称为输出变量,可以由过程修改并返回给呼叫者。

一个人的论点’t specify the direction is assumed to be a IN argument.

arg_namearg_type parameters.

返回值

A stored procedure can optionally return one or more result sets. A result set is a temporary, in-memory table containing the results being returned from the stored procedure. To designate that the stored procedure is to return one or more result sets, use RETURNS followed by a comma separated list of table definitions, table_name ( field [,field]…​), that represent the result sets being returned. See table_name, column_namecolumn_type parameters below.

If a procedure is declared as RETURNS table_name…​, a temporary, in-memory table with the specified structure will appear in the database, and its content will be returned to the caller as an output result set. This temporary, in-memory table cannot be accessed outside of the stored procedure.

If the current connection already has a temporary, in-memory table defined with the same name, references to such table from inside the stored procedure will actually be referencing this RETURNS table.

If the current schema already has a permanent table defined with the same name, it must be accessed within the stored procedure by using its fully qualified name (i.e. schema.tablename)。

The language for a stored procedure, user defined function or trigger may be 澳门比分网址 or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE 澳门比分网址 or LANGUAGE JAVA. The default language, if none is specified, is 澳门比分网址. 澳门比分网址 stored procedures, UDFs, and triggers are defined by sql_code_block. See 澳门比分网址程序语言 想要查询更多的信息。

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See 使用嵌入式Java存储过程.

安全

您可以声明存储过程或用户定义的函数,以两种不同的方式在各种不同的方式上工作’影响数据库对象的权限。可以以两种方式之一声明过程或功能:

SECURITY INVOKER

检查调用用户的权限。如果存储过程或用户定义的函数正在插入,选择和/或更新一个或多个表,则调用用户必须具有对每个表的操作具有适当的特权。

SECURITY DEFINER

Specifies that the stored procedure or user defined function is to be executed with the privileges of the user that created it. For example, the calling user may not have privileges to 选择 or 插 INTO tables when referencing them outside the stored procedure or user defined function, but will be allowed access when executing the stored procedure or function. If a security level is not specified, this is the default.

谁获取程序上的执行权限可以访问该过程的创建者的相同表/架构/序列/ etc ..即使当前用户仍然是调用者也是如此。

外部定义

Declaring a stored procedure as EXTERNAL is done to describe a dynamically loadable Java stored procedure that is to be executed. For LANGUAGE JAVA, the EXTERNAL stored procedure defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the 创建javaclass. command used to upload to the NuoDB server the JAR with the defined code. See 创建javaclass..

澳门比分网址程序语言 - 创建程序 body

If defining this stored procedure in 澳门比分网址, sql_code_block is one or more 澳门比分网址 statements. See 澳门比分网址程序语言 想要查询更多的信息。

系统表

The body of the procedure is stored in SYSTEM.PROCEDURES in the PROCEDURETEXT column. See 程序系统表描述例1 below.

参数

schema

Name of the schema in which the procedure will be created. The default is the current schema as defined by the command. If schema is provided, and the schema does not exist, it will be created.

name

Optionally schema-qualified name of the stored procedure. If the schema does not exist, it will be created. Procedures cannot be created in the SYSTEM schema. This schema is reserved for database metadata.

parameter_definition

这通常是:

arg_name arg_type

在哪里:

arg_name specifies the variable name for the input and/or output parameter to the stored procedure and arg_type specifies the data type of the input and/or output parameter for the stored procedure.

When used as an IN table, the, parameter_definition can be:

table_name(column_name column_type [,column_name column_type]…​ )

在哪里:

table_name 指定包含从存储过程返回的结果集的临时内存表的名称。看 使用结果集作为存储过程的输入 想要查询更多的信息。

column_name

临时列中列的列名,内存表包含从存储过程返回的结果集。

column_type

Column data type of a column in the temporary, in-memory table containing the result set being returned from the stored procedure. This cannot be defined by an 澳门比分网址 domain. Column constraints cannot be defined, such as NOT NULL, DEFAULT values, etc.

When creating or altering multi-statement stored procedures in nuosql, the 放 DELIMITER command must be used prior to defining the stored procedure (see )。 This new delimiter is used to indicate the end of the stored procedure definition and will execute the 创建程序 statement. See the examples below.

例子

示例1:在过程中定义的所有语句中显示为变量的参数
放 DELIMITER @
CREATE PROCEDURE prc_player_info(IN inPlayer INTEGER) AS
   VAR outParm STRING;
   IF (inPlayer > 99)
       THROW 'Player Numbers are 0 to 99';
   END_IF;
   outParm =  (SELECT name||' plays position '||position FROM hockey.hockey WHERE number = inPlayer);
   THROW outParm;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE prc_player_info(100);
Procedure HOCKEY.PRC_PLAYER_INFO, Player Numbers are 0 to 99
EXECUTE prc_player_info(37);
Procedure HOCKEY.PRC_PLAYER_INFO, PATRICE BERGERON plays position Forward

SET OUTPUT VERTICAL;
SELECT * FROM system.procedures;
==================================== Row #1 ====================================
SCHEMA: HOCKEY
PROCEDURENAME: PRC_PLAYER_INFO
CREATOR: DBA
PROCEDURETYPE: 0
PROCEDURETEXT: (IN inPlayer INTEGER) AS    VAR outParm STRING;
IF (inPlayer > 99)        THROW 'Player Numbers are 0 to 99';    END_IF;
outParm =  (SELECT name||' plays position '||position FROM hockey.hockey
WHERE number = inPlayer);    THROW outParm; END_PROCEDURE ;

VERSION: 1
Example 2: Using the RETURNS option to return a result set of data from a database table

The statement must be a valid insert statement as supported by NuoDB. The values provided can be either a select statement, a values list, or a combination of both (see 有效语法的示例)。

/* Using select statement as values for INSERT statement */
DROP PROCEDURE IF EXISTS prc_player_position;
 DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( number INTEGER,
                        name   STRING,
                        team   STRING)
AS
       INTO tmp_tab 选择 number, name, team
                           FROM hockey.hockey
                           WHERE position = in_pos
                            ORDER BY number;
END_PROCEDURE
@
 DELIMITER ;
执行 prc_player_position('Defense');
 NUMBER        NAME         TEAM
 ------- ----------------- ------
   21    ANDREW FERENCE    Bruins
   27    DOUGIE HAMILTON   Bruins
   33    ZDENO CHARA       Bruins
   44    DENNIS SEIDENBERG Bruins
   45    AARON JOHNSON     Bruins
   54    ADAM MCQUAID      Bruins
   55    JOHNNY BOYCHUK    Bruins

/* Using a combination of select statement and a values column list to provide
/* values for INSERT.
/* In this case, using a table alias in the select statement is required */

DROP PROCEDURE IF EXISTS prc_player_position;
 DELIMITER @
CREATE PROCEDURE prc_player_position (IN in_pos STRING)
      RETURNS tmp_tab ( id       INTEGER,
                        number   INTEGER,
                        name     STRING,
                        position STRING,
                        team     STRING,
                        date     DATE,
                        active   BOOLEAN)
AS
       INTO tmp_tab 选择 h.*, current_date, 'TRUE'
                           FROM hockey.hockey h
                           WHERE h.position = in_pos
                           ORDER BY h.id;
END_PROCEDURE
@
 DELIMITER ;
执行 prc_player_position('Defense');
 ID  NUMBER        NAME        POSITION   TEAM     DATE    ACTIVE
 --- ------- ----------------- --------- ------ ---------- -------
 15    55    JOHNNY BOYCHUK     Defense  Bruins 2015-04-23  TRUE
 16    33    ZDENO CHARA        Defense  Bruins 2015-04-23  TRUE
 17    21    ANDREW FERENCE     Defense  Bruins 2015-04-23  TRUE
 18    27    DOUGIE HAMILTON    Defense  Bruins 2015-04-23  TRUE
 19    45    AARON JOHNSON      Defense  Bruins 2015-04-23  TRUE
 20    54    ADAM MCQUAID       Defense  Bruins 2015-04-23  TRUE
 21    44    DENNIS SEIDENBERG  Defense  Bruins 2015-04-23  TRUE
示例3:Java存储过程示例

使用嵌入式Java存储过程 对于在NOODB中创建和执行Java存储过程的更全面处理。

DROP PROCEDURE IF EXISTS myproc;
CREATE JAVACLASS firstprocid FROM 'test.jar';
CREATE PROCEDURE myproc(str string)
    LANGUAGE JAVA EXTERNAL 'firstprocid:com.mycompany.MyFirstJavaProc.testMethod';
称呼 myproc('Alberto');
java.sql.澳门比分网址Exception: Hello, Alberto

有关更多信息和示例,请参阅 澳门比分网址程序语言.