CREATE statements
Derby Reference Manual
29
The CALL (PROCEDURE) statement is used to call procedures. A call to a procedure
does not return any value.
When a procedure with definer's rights is called, the current default schema is set to the
eponymously named schema of the definer. For example, if the defining user is called
OWNER, the default schema will also be set to OWNER. The definer's rights include
the right to set the current role to a role for which the definer has privileges. When the
procedure is first invoked, no role is set; even if the invoker has set a current role, the
procedure running with definer's rights has no current role set initially.
When a procedure with invoker's rights is called, the current default schema and current
role are unchanged initially within the procedure. Similarly, if SQL authorization mode is
not enabled, the current default schema is unchanged initially within the procedure.
When the call returns, any changes made inside the procedure to the default current
schema (and current role, if relevant) are reset (popped).
For information about definer's rights, see
EXTERNAL SECURITY
.
Syntax
CALL
procedure-Name
( [ expression [, expression]* ] )
Example
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2))
PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME
'com.example.sales.calculateRevenueByMonth';
CALL SALES.TOTAL_REVENUE(?,?,?);
CREATE statements
Use the CREATE statements to create functions, indexes, procedures, roles, schemas,
synonyms, tables, triggers, and views.
CREATE FUNCTION statement
The CREATE FUNCTION statement allows you to create Java functions, which you can
then use in an expression.
The function owner and the
automatically gain the EXECUTE privilege
on the function, and are able to grant this privilege to other users. The EXECUTE
privileges cannot be revoked from the function and database owners.
For details on how Derby matches functions to Java methods, see
Syntax
CREATE FUNCTION
function-name
( [
FunctionParameter
[,
FunctionParameter
] ] * ) RETURNS
ReturnDataType
[
FunctionElement
]
*
function-Name
. ]
If schema-Name is not provided, the current schema is the default schema. If a qualified
procedure name is specified, the schema name cannot begin with SYS.
FunctionParameter
[ parameter-Name ] DataType
PararameterName must be unique within a function.
