This is the 7th day of my participation in the September Challenge.

SQL > CREATE METHOD;

Create a method in a class.

The outline

CREATE [STATIC] METHOD name (parameter_list) 
   [ characteristics ]
   [ LANGUAGE SQL ]
   BEGIN
code_body ;
   END

CREATE [STATIC] METHOD name (parameter_list) 
    [ characteristics ]
    LANGUAGE OBJECTSCRIPT
   { code_body }
Copy the code

parameter

  • name– The name of the method to be created in the stored procedure class.

The name must be a valid identifier. Procedure names can be qualified (schema.procName) or unqualified (procName). Unqualified procedure names accept the default schema name. The name must be followed by parentheses, even if no arguments are specified.

  • parameter_list– Optional – The list of arguments passed to the method.

The argument list is enclosed in parentheses, and the arguments in the list are separated by commas. Parentheses are required even if no arguments are specified.

  • characteristics– Optional – Specify one or more keywords for method characteristics.

The allowed keywords are RETURNS, FOR, FINAL, PRIVATE, PROCEDURE, and SELECTMODE. You can specify the characteristic keyword phrase RESULT SETS, DYNAMIC RESULT SETS, or DYNAMIC RESULT SETS n, where n is an integer. These phrases are synonyms; The DYNAMIC keyword and the integer n are no-ops, providing compatibility. Multiple features are separated by Spaces (a space or newline character). Features can be specified in any order.

  • LANGUAGE OBJECTSCRIPT ，LANGUAGE SQL– Optional – Programming language for the code body. Specifies the language object script (for object scripts) or languageSQL. If you omitLANGUAGEClause, defaults toSQL.
  • code_body– The program code for the method.SQLProgram codeBEGINKeyword starts withENDKeyword end.code_bodyEach complete SQL statement in the;).objectScript program code is enclosed in curly braces.

ObjectScript lines must be indented.

describe

The CREATE METHOD statement creates a class METHOD. This class method may or may not be a stored procedure. To create a method in a class exposed as an SQL stored procedure, you must specify the PROCEDURE keyword. By default, CREATE METHOD does not CREATE a METHOD that also stores the program; The CREATE PROCEDURE statement always creates a method that is also a stored PROCEDURE.

The optional STATIC keyword is provided to indicate that the method being created is a STATIC (class) method, not an instance method. This keyword provides no actual function.

To create a method, you must have the %CREATE_METHOD administrative permission specified by the GRANT command. If you try to create a method for an existing class that has a defined owner, you must log in as the owner of that class. Otherwise, the operation will fail with a SQLCODE -99 error.

If the class definition is a deployed class, methods cannot be created in the class. This operation failed with a SQLcode-400 error with % MSG Unable to execute DDL that modifies a Deployed class: ‘className ‘.

The following two examples show the creation of the same class method. The first example uses CREATE METHOD, and the second example defines class methods in the User class. Letters:

CREATE METHOD RandCaseLetter(IN caps CHAR) 
  RETURNS INTEGER 
  PROCEDURE 
LANGUAGE OBJECTSCRIPT
{
:Top
	if caps = "U" {
		s x = $random(91) 
		if x > 64 {
			q $char(x)
		} else {
			g Top
		}
	} elseif caps="L" { 
		s x = $random(123)  
		if x > 97 {
			q $char(x)
		} else {
			g Top
		}
	} else{q "case must be 'U' or 'L'"}}}Copy the code

Background classes that are automatically created

Class User.methRandCaseLetter Extends %Library.RegisteredObject [ ClassType = "", DdlAllowed, Owner = {yx}, Not ProcedureBlock ]
{

ClassMethod RandCaseLetter(caps As %Library.String(MAXLEN=1)) As %Library.Integer(MAXVAL=2147483647,MINVAL=-2147483648) [ SqlName = RandCaseLetter, SqlProc ]
{
Top
		 IF caps="U" {SET x=$RANDOM(91) IF x>64 {QUIT $CHAR(x)}
		   ELSE {GOTO Top}}
		 ELSEIF caps="L" {SET x=$RANDOM(123) IF x>97 {QUIT $CHAR(x)}
		   ELSE {GOTO Top}}
		 ELSE {QUIT "case must be 'U' or 'L'"}}}Copy the code
Class User.Letters Extends %Persistent [ DdlAllowed ] 
{
ClassMethod RandCaseLetter(caps) As %String [ SqlName = RandomLetter, SqlProc ]
{ 
Top
	if caps = "U" {
		s x = $random(91) 
		if x > 64 {
			q $char(x)
		} else {
			g Top
		}
	} elseif caps="L" { 
		s x = $random(123)  
		if x > 97 {
			q $char(x)
		} else {
			g Top
		}
	} else {
		q "Case must be 'U' or 'L'"}}}Copy the code

parameter

name

The name of the method to create. This name can be unqualified (StoreName) and accepts the system-wide default schema name, or it can be qualified by specifying the schema name (patience.storename). You can use the $SYSTEM. SQL. Schema. The Default () method to determine the scope of the current SYSTEM’s Default mode. The initial system-wide default schema name is SQLUser, which corresponds to the class package name User.

Note that the FOR feature (described below) overrides the class name specified in Name. If a method with this name already exists, the operation will fail with a SQLCODE -361 error.

The name of the generated class is the package name corresponding to the schema name, followed by a dot, then “meth”, and finally the specified name. For example, if the unqualified method name RandomLetter accepts the initial default mode SQLUser, the resulting class name will be: user.methrandomLetter.

parameter-list

A list of parameters used to pass values to a method. Parameter lists are enclosed in parentheses, and parameter declarations in the list are separated by commas. Parentheses are required even if no arguments are specified. Each parameter declaration in the list consists of (in order) :

  • An optional keyword specifying that the parameter mode isIN(Input value),OUT(Output value) orINOUT(Modify the value).

If omitted, the default parameter mode is IN.

  • Parameter name.

Parameter names are case sensitive.

  • Parameter data type.
  • Optional: Default value.

You can specify the DEFAULT keyword followed by a DEFAULT value; The DEFAULT keyword is optional. If no default value is specified, NULL is assumed.

The output value of the method is automatically converted from Logical format to Display/ODBC format.

By default, method input values are not converted from Display/ODBC format to Logical format. However, you can use $SYSTEM. SQL. Util. SetOption (” SQLFunctionArgConversion “) method within the scope of the SYSTEM configuration display to the logical transformation input. You can use the $SYSTEM. SQL. Util. GetOption (” SQLFunctionArgConversion “) to determine if this option is the current configuration.

The following example specifies two input parameters, both with default values. Specify the optional DEFAULT keyword for the first argument and omit the second argument:

CREATE METHOD RandomLetter(IN firstlet CHAR DEFAULT 'A'.IN lastlet CHAR 'Z')
BEGIN
-- SQL program code
END
Copy the code