PL/SQL Functions

PL/SQL is a loadable procedural language.

Functions created using PL/SQL can be used in any place where you can use built-in functions. For example, you can create calculation functions with complex conditions and use them to define operators or use them for index expressions.

SQL is used by most databases as a query language, which is portable and easy to learn. Each SQL statement must be executed independently by a database server.

This means that the client application performs the following processes for each query: send a query to the database server, wait for the query to be received, receive and process the result, perform related calculation, and then send more queries to the server. If the client and the database server are not on the same machine, this process also causes inter-process communication and network load.

PL/SQL enables a whole computing part and a series of queries to be grouped inside a database server. This makes procedural language available and SQL easier to use. In addition, the client/server communication cost is reduced. The advantages of PL/SQL are as follows:

• Extra round-trip communication between clients and servers is eliminated.
• Intermediate results that are not required by clients do not need to be sorted or transmitted between the clients and servers.
• Parsing can be skipped in multiple rounds of queries.

PL/SQL can use all data types, operators, and functions in the SQL statements. The syntax for creating functions using PL/SQL is CREATE FUNCTION.

PL/SQL is a loadable procedural language. Its application method is similar to that of Stored Procedure. The difference is that Stored Procedure has no return value, and PL/SQL functions have return values.

XML data can be used as the input parameter, output parameter, user-defined variable, and return value of a user-defined function.