Basics of SQL Functions and Procedures 

SQL is a well developed procedural language for writing scripts which can interact with your database. There are many links on-line which give deep and detailed explanations of how to write Procedural SQL. This short document is intended to give a basic overview and introduction of some key parts of the SQL that make up a Function or Procedure to help Orixa Developers extend their Apps.

Open the Database Management Utility to view details of a Procedure

To review and edit the Functions and Procedures in your App, go to the "System" menu, and select "View Database Management Utility", the screen showed in the image below should open.

Viewing the details of a Procedure  

1. Select the "Functions/Procedures" heading in the Database Treeview.
2. Click on the name of a procedure (note that Procedures and Functions are held in separate lists, so click on the appropriate heading to see what you need). The SQL Definition of the procedure will then be shown at the far right-hard side of the screen.
3. Once a Procedure is seleted a panel will list its parameters and allow you to Execute the procedure if you wish to.

Basic Elements of a Procedure

All Procedures and Functions contain SQL enclosed by "BEGIN" "END" sections. It is normal to DECLARE a number of variables, PREPARE and OPEN Cursors and FETCH data from the cursor.

In SQL the end of a line is set with a semi-colon. If a SQL-string is added it is included in single-quotes as shown in the examples, and will display red in the editor. Note that once a single-quote has been added the SQL-string can continue over several lines. The end of a line does not end the SQL-string. To add local variables or parameters to a SQL-string use the plus sign (+) to concatenant parts of a SQL-string together, as shown in the examples.

A common error when first writing SQL-strings within Procedures occurs when the SQL-string itself contains a single quote. In such cases the single-quote must be typed twice. This is called "escaping" the single quote, and allows single quotes to be included as part of the SQL-string.

DECLARE, PREPARE, FETCH	Use one or more DECLARE statements to make variables available in your procedure. These variables can be simple data types (like FLOATs and VARCHARs) identical to the data types of a database, or they can be Statements and Cursors. Statements and Cursors are variables which can be used to SELECT and FETCH data. Once a Statement has been declared, the SQL syntax: PREPARE [StatementName] FROM  ' SELECT   [Fields] FROM [Tables]'; Can be used, coupled with the statement: OPEN [CursorName]; Once this step has been done a Cursor will be available in the Procedure which the programmer can use to manipulate data. If you want to manipulate data returned by a Cursor, use the syntax: FETCH FIRST FROM [CursorName]('[FieldName']) INTO [VariableName]. You can combine this with: WHILE NOT EOF([CursorName]) DO   FETCH NEXT FROM ... To iterate through all the records returned by a Cursor. Note that "EOF" stands for "End Of File". Finally, at the end of this procedure, the SQL Syntax "OPEN Crsr" is used. In such a situation the procedure will return a dataset, which can be viewed by the developer.
Parameters, EXECUTE IMMEDIATE	It is often useful to pass inputs from a user into a Procedure or Function. To do this add parameters to the Procedures definition Parameters are declared directly after the Procedure name, wrapped in brackets. Parameters are given a data-type when they are declared, and also marked as "IN" "OUT" or "INOUT". An "IN" parameter will need to be provided by the user, and OUT parameter will be returned by the procedure. It is common to use the SQL Syntax: EXECUTE IMMEDIATE ' [SOME SQL Statement] ';
Configuration tables, COPY FILE	Procedures can be used to manage external data outside the database. To help with this there are a number of "Configuration" and "Information" Tables which can be queried with Statements and Cursors, to provide access to files on-disk. SQL Syntax includes a number of keywords which can be used to manipulate, copy, delete and alter files on-disk. Full details of this are beyond the scope of this document. The statement references the Configuration "Updates" system-table, which contains a list of Updates waiting to run on the database. The statement uses the COPY FILE syntax to move an Update file from a Local Store to a Server Store on the cloud.
Using Parameters and Variables	Example showing the use of parameters and variables. Parameter "aLinkTable" is passed into the procedure. Note how the Statement is prepared with a Question Mark. The Cursor is then opened with the SQL "OPEN [CursorName] USING [ParameterName]; Variable "ID" is declared, and then set with FETCH FIRST FROM [CursorName] INTO [VariableName]; Once a value has been added to the ID it can be used in a later EXECUTE IMMEDIATE statement.

Basic Language Elements of Procedural SQL

1. BEGIN …END; Used to demark the start and end of a function or procedure.
2. IF [Boolean Statement] THEN [DoSomething] ELSE [DoAnotherThing] END IF; Used to create forking logic dependent on data and variables.
3. WHILE [Boolean Statement] DO [DoSomething] END WHILE; Repetition logic, to do something. Remember with a While statement you must provide an exit, or the loop will run forever, so the [Boolean Statement] must be acted on within the While statement to reset it.
4. EXECUTE IMMEDIATE [SQL Statement]; Allows one or more SQL statements to be run in a procedure.
5. DECLARE Result [DataType]; [DoSomething] RETURN Result; This SQL syntax must be used within a Function to ensure that it returns the required value.
6. FETCH FIRST/NEXT FROM [CursorName]('[FieldNames]') INTO [VariableName; Used to pull data out of a Cursor into a variable so it can be accessed by the Procedure.
7. OPEN Crsr; Used to Open a data-set based on a previously prepared Statement. If this is the last line of a Procedure, then the procedure will return a data-set which will be viewed in your App.