SQL business rules are implemented in the programing language supported by application database engine.
If an Oracle database is the foundation of your data tier, then the programming language of SQL business rules is PL/SQL.
Consider Employees page displaying createForm1 in a project created straight from HR sample database.
None of the fields has a default value. Let’s assign default values to First Name, Last Name, Hire Date, and Salary.
Start Project Designer, activate Controllers tab, locate EMPLOYEES data controller and start creating a new SQL business rule.
Configure the rule as follows:
| Property | Value |
| Command Name | New |
| Type | SQL |
| Phase | Execute |
| Script | begin
:FIRST_NAME := 'John';
:LAST_NAME := 'Doe';
:HIRE_DATE := sysdate;
select avg(SALARY) into :SALARY from EMPLOYEES;
end; |
Save the rule.
The script is written in PL/SQL. The highlighted field names are referenced as parameters in the script text.
When executed, the script will assign static values to First Name and Last Name fields.
The current system date will be assigned to Hire Date field.
An average of all employee salaries will be assigned to Salary field.
The full power of PL/SQL is available to application developers when creating applications with Code On Time.