You run a stored procedure using the CALL command, or a procedure can be run automatically using database triggers. Procedures run with triggers must be stored with no parameters.
PUT
A stored procedure can be created in any ASCII text editor, and must be loaded into the database with the PUT command as follows:
Options:
argname datatype
The parameter name and datatype.
comment
An optional comment for the parameter or, if placed after RETURN, an optional comment for the entire procedure. The comment must be enclosed in the current quote character.
filename
The filename in ASCII text format, with full path, to load as the stored procedure.
procname
The procedure name. If a procedure by this name already exists in the database, an error is generated.
PUT
Loads a procedure into the database.
RETURN datatype
Datatype of the value returned by the procedure.
NOTE: To clear previous parameters that were stored for a procedure, use the PUT command as follows:
PUT filename AS procname RETURN
If you are replacing an existing procedure, you must LOCK the procedure first with either the GET LOCK or the SET PROCEDURE command. Once the procedure is locked, it is replaced by an updated file using the PUT command. A procedure cannot be replaced unless it is locked. A procedure is automatically unlocked when replaced with the PUT command.
GET
The GET command is used to read a stored procedure from the database into an ASCII file. If the LOCK option is used with the GET command, the procedure can be replaced by using PUT.
filename
The name of the file the procedure is placed in, in ASCII text format.
GET
Retrieves a stored procedure .
LOCK
Locks the procedure so it cannot be locked or unlocked by another user. When a procedure is locked, only the user placing the lock can replace the procedure. The NAME setting is used for identification of the user.
procname
The procedure name.
SET PROCEDURE
The SET PROCEDURE command locks a procedure so it can be replaced. It works like the GET LOCK command without retrieving the stored procedure into an ASCII file. ON sets the lock; OFF releases the lock placed by the SET PROCEDURE or the GET commands.
When a procedure is locked, only the user placing the lock can replace the procedure or remove the lock. The NAME setting is used for identification of the user.
Parameters
When you load a stored procedure into a database, you specify parameters to be passed to it. These parameters are used within the procedure. When the procedure is called, the number and type of parameters passed must match the number and type specified when the procedure was stored in the database. When a parameter name is referenced in the stored procedure code, the parameter name must be preceded by a period unless it is a table or column name, then it must be preceded by an ampersand (&). For example:
UPDATE &p1 SET col = 99 WHERE col = .p2
The parameter names are specified when the procedure is stored in the database with the PUT command.
Return Values
The value to be returned by a stored procedure is specified in the procedure code following the keyword RETURN. For example, RETURN 'Los Angeles'.
The value returned must match the datatype specified when the procedure was stored.
CALL
To run a stored procedure you can use the CALL command like a function or run the CALL command at the R>prompt.
Use the CALL command like a function with the following syntax:
SET VAR v1 = (CALL procname(arglist))
Run the CALL command from the R>prompt with the following syntax:
CALL procname(arglist)
When the CALL command is run at the R>prompt, the return value from the stored procedure is placed in the variable MICRORIM_RETURN. The return value can be an expression.
CALL
Runs a procedure.
procname
The procedure name.
arglist
The parameter values separated by commas. An arglist must always be used, even if empty. For example, SET VAR v1 = (CALL procname ( )).
Example
Create the following .RMD file in RBEdit or any text editor:
*(INS.RMD:)
IF (.p1 > 105) THEN
INSERT INTO contact (custid, contlname) VALUES (.p1, .p2)
RETURN 1
ELSE
RETURN 0
ENDIF
To create the stored procedure from the .RMD file:
PUT INS.RMD AS proc1 p1 INT, p2 TEXT RETURN INTEGER
The following stored procedure example will generate one new row in contact and set v1 = 1.
SET VAR vname = 'Dunn'
SET VAR v1 = (CALL proc1 (106, .vname))
The following stored procedure example will set v1 = 0.
SET VAR vname = 'Dunn'
SET VAR v1 = (CALL proc1 (100, .vname))
To Delete a Stored Procedure
To delete a stored procedure, use the DROP command with the following syntax:
DROP PROCEDURE procname
Optionally, you can enter the following code at the R>prompt:
SET VAR vProcID = sys_proc_id IN sys_procedures +
WHERE sys_proc_name = 'procname'
DELETE ROWS FROM sys_procedures +
WHERE sys_proc_id = .vProcID
DELETE ROWS FROM sys_proc_mods +
WHERE sys_proc_id = .vProcID
DELETE ROWS FROM sys_proc_cols +
WHERE sys_proc_id = .vProcID
To Rename a Stored Procedure
To rename a stored procedure, use the RENAME command with the following syntax:
RENAME PROCEDURE procname1 TO procname2
To List Stored Procedures
With the LIST command, you can list every stored procedure in a database or list information about a specific stored procedure.
To display the name and a description for every procedure in the open database, use the following syntax:
LIST PROCEDURE
To display a specific procedure and its attributes, use the following syntax:
LIST PROCEDURE procname
This option displays the name, description, ID, date last modified, version, locked by (if locked), and if applicable, the return type and description for the specified stored procedure. If the stored procedure has parameters, the number of parameters and parameter names and attributes will be listed.
To Trace a Stored Procedure
Enter the following code at the R>prompt:
TRACE SELECT sys_proc_src FROM sys_procedures WHERE +
sys_proc_name = 'procname'