Create Oracle Function with Syntax

In Oracle PL/SQL, there is a distinction between functions and procedures in terms of their syntax and purpose. Both functions and procedures are program units that encapsulate a set of SQL and PL/SQL statements to perform specific tasks. However, they differ in their usage and syntax.
A procedure in Oracle PL/SQL is a subprogram that performs a specific action or set of actions. It is primarily used for its procedural capabilities and is not designed to return a value. Procedures can have input and output parameters, allowing them to accept values and modify data within the database. The syntax for creating a procedure in Oracle PL/SQL follows a specific format:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
[IS | AS]
BEGIN
    -- PL/SQL statements
END [procedure_name];

Here, procedure_name is the name of the procedure, and you can define input (IN), output (OUT), or both (IN OUT) parameters as per the requirements of the procedure.
On the other hand, a function in Oracle PL/SQL is also a subprogram but is designed to return a value. It encapsulates a set of statements and calculates and returns a single value. Functions can be used within SQL statements, expressions, and PL/SQL code to perform calculations or retrieve specific values. The syntax for creating a function in Oracle PL/SQL is as follows:

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
RETURN return_type
[IS | AS]
BEGIN
    -- PL/SQL statements
    RETURN value;
END [function_name];

Here, function_name represents the name of the function, and you can specify the input and output parameters, if any. The return_type indicates the data type of the value returned by the function. Within the function's body, you can define the necessary statements and calculations, and the result is returned using the RETURN statement.
In summary, the primary difference between functions and procedures in Oracle PL/SQL lies in their purpose and the way they handle values. Procedures are used for their procedural capabilities and do not return a value, while functions are designed to return a single value and can be used within SQL statements and expressions. Understanding this syntax distinction is essential for effectively utilizing functions and procedures in Oracle PL/SQL development.

There are two types of subprograms called procedures and functions, which can accept parameters and be invoked (called).
The SQL CREATE PROCEDURE statement lets you create standalone procedures that are stored in the database.
The SQL CREATE FUNCTION statement lets you create standalone functions that are stored in an Oracle database. These stored (schema level) subprograms can be accessed from SQL. As shown in Example 2, a subprogram is like a miniature program, beginning with a header followed by an

1. optional declarative part,
2. an executable part, and
3. an optional exception-handling part.

-- including OR REPLACE is more convenient when updating a subprogram
CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT commission_pct / 100 INTO commission FROM employees
WHERE employee_id = emp_id;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE employees SET salary = salary + bonus*commission
WHERE employee_id = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
commission := 0;
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
CALL award_bonus(150, 400);

When called, this procedure accepts an employee Id and a bonus amount. It uses the Id to select the employee's commission percentage from a database table and, at the same time, convert the commission percentage to a decimal amount. Then, it checks the commission amount. If the commission is null, an exception is raised; otherwise, the employee's salary is updated.