Lesson 7

This module covered the creation of a function using PL/SQL blocks and SQL commands. You learned the basic syntax of a function and reviewed a detailed account of creating an example. You created your own function for the course project and then used it within an UPDATE command. In addition, you practiced your documentation skills. Now that you completed this module, you should be able to:

1. List the uses of a function and identify correct syntax
2. Create and execute a function that returns a number, text, or date value
3. List uses of a function that return a Boolean value

• Glossary:
  In this module you were introduced to the following glossary terms:
  1. Function
  2. Stored function

1. CREATE FUNCTION GET_NEWS (I_DATE IN DATE) RETURN VARCHAR2 AS...
   

   
   Function can be used within a SQL query.

2. CREATE FUNCTION FIRST_LAST (ANYNAME IN VARCHAR2) RETURN BOOLEAN AS...
   

   
   Function cannot be used within a SQL query.

3. CREATE FUNCTION PUT_HERE RETURN NUMBER AS...
   

   Function requires no parameters.

4. CREATE FUNCTION GOLDEN (ICOLOR IN BOOLEAN) AS...
   

   
   Function has a syntax error.

There is a syntax difference between functions and procedures in Oracle PL/SQL. Both are subprograms, but they serve different purposes and have distinct syntactic structures. Here's a comparison:

1. Procedures
   • Purpose: Perform an action (e.g., inserting, updating, or deleting records) but do not return a value.
   • Syntax:

     CREATE [OR REPLACE] PROCEDURE procedure_name 
     [ (parameter_name [IN | OUT | IN OUT] datatype [,...]) ] 
     AS | IS 
     BEGIN 
         -- procedural statements 
     END procedure_name;
             

   • Key Characteristics:
     • Cannot be used in SQL statements.
     • Can accept parameters (IN, OUT, IN OUT).
     • Does not return a value explicitly.
   • Example:

     CREATE OR REPLACE PROCEDURE update_salary ( 
         emp_id IN NUMBER, 
         new_salary IN NUMBER 
     ) AS 
     BEGIN 
         UPDATE employees 
         SET salary = new_salary 
         WHERE employee_id = emp_id; 
     END update_salary;
             

2. Functions
   • Purpose: Compute and return a single value.
   • Syntax:

     CREATE [OR REPLACE] FUNCTION function_name 
     [ (parameter_name [IN | IN OUT] datatype [,...]) ] 
     RETURN datatype 
     AS | IS 
     BEGIN 
         -- function body 
         RETURN value; 
     END function_name;
             

   • Key Characteristics:
     • Must include a `RETURN` clause to specify the return data type.
     • Must use the `RETURN` statement to return a value.
     • Can be called from SQL statements (e.g., SELECT, WHERE, or other expressions), provided it is free of side effects.
     • Can accept parameters (IN or IN OUT).
   • Example:

     CREATE OR REPLACE FUNCTION get_employee_name ( 
         emp_id IN NUMBER 
     ) RETURN VARCHAR2 AS 
         emp_name VARCHAR2(100); 
     BEGIN 
         SELECT first_name || ' ' || last_name 
         INTO emp_name 
         FROM employees 
         WHERE employee_id = emp_id; 
      
         RETURN emp_name; 
     END get_employee_name;
             

Key Differences

Aspect	Procedure	Function
Return Value	Does not return a value explicitly.	Must return a value using the RETURN clause.
SQL Usage	Cannot be used directly in SQL statements.	Can be invoked in SQL statements if it has no side effects.
Purpose	Perform actions or operations.	Compute and return a value.
Return Clause	Not required.	Required.
Parameters	Supports IN, OUT, and IN OUT parameters.	Supports IN and IN OUT parameters (OUT not allowed).

Conclusion

• Use a procedure when you want to perform a task without needing a return value.
• Use a function when you need to compute and return a value, especially if it will be used in SQL statements.

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.

Example 2: Creating a Stored Subprogram

-- 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.

Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead. With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application.

1. You can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution.
2. PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.
3. PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient.
4. Because stored procedures execute in the database server, a single call over the network can start a large job.
5. This division of work reduces network traffic and improves response times.
6. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

• Higher Productivity PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as Perl can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as
  
  1. exception handling,
  2. encapsulation,
  3. data hiding, and
  4. object-oriented datatypes.
  PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.
• Full Portability: Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.
• Tight Security:PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

The next module looks at another type of stored PL/SQL object: the procedure.