The Oracle FLOOR function will return the largest integer value less than the specified number. In other words, it rounds down to the nearest whole number.
It’s the opposite of the CEIL function, which rounds up.
The syntax of FLOOR is:
The number parameter can be any numeric data type. The function will return a number in the same data type as the parameter.
So, you can use FLOOR with decimal numbers, which is actually where FLOOR works best. You can use it with whole numbers too. It won’t show an error, it just might display the same value as provided (e.g. FLOOR(5) would be 5).
You can also use functions inside FLOOR. For example, FLOOR(SUM(salary)) will SUM all of the salary values, and then round them down to the nearest whole number.
Just like with all functions, the Oracle FLOOR function can also use column aliases. So, instead of having your column labelled “FLOOR(8.12)” or “FLOOR(salary)”, you can label your column “salary_rounded” or something that makes more sense.
The FLOOR function is similar to the ROUND function. However, ROUND will round up or down, where FLOOR will always round down. The TRUNC function will also do some kind of rounding, but it removes decimals from a number which may cause it to “round” down.
For more information, including the code used in the examples, read the related article here:
A trigger is a named PL/SQL unit that is stored in the database and executed ( fired ) in response to a specified event that occurs in the database.
Overview of Triggers.
A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:
A database manipulation (DML) statement ( DELETE , INSERT , or UPDATE )
A database definition (DDL) statement ( CREATE , ALTER , or DROP )
A database operation ( SERVERERROR , LOGON , LOGOFF , STARTUP , or SHUTDOWN )
The trigger is said to be defined on the table, view, schema, or database.
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on.
An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement. For more information, see Modifying Complex Views (INSTEAD OF Triggers).
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points :
Before the triggering statement executes.
After the triggering statement executes.
Before each row that the triggering statement affects.
After each row that the triggering statement affects.
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data. For more information, see Compound Triggers.