PL/SQL - SPLessons

PL/SQL Calling Functions

Chapter 25

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Calling Functions

PL/SQL Calling Functions

shape Description

Oracle allows users to affix a PL/SQL activity from SQL. This can help extend our functions and make them more useful. In this, the usage and restriction of calling functions from SQL statements are checked and a lot of filtering is done on the result set. When this function is not used, an alternate approach would be used to fetch the entire data set using the logic being used in the function and then filter the dataset.

shape Conceptual
figure

It also makes SQL more powerful by making use of PL/SQL functions. And, some SQL statements can run in parallel when calling a function and can enable the SQL statements inside the function to run in parallel. However, there is a small performance cost of context switching from SQL to PL/SQL when using the function in SQL. With the new version of oracle, the cost is going down and gives out some advantages, for instance, it outweighs the small context switch cost. By invoking Oracle built-in function UPPER, the passed character string test will be converted to upper case, and next is an example of calling a user-defined function get_dept_name inside the select. By selecting emp_id, one can get dept_name from the employee. Here, it will pass the call emp_dept_id to the function. The function can appear as a part of the condition in the Where clause or Having clause. And, it appears as a part of the select, update or insert statements.

For example, in GROUP BY clause, we get the count of employees and the department name returned by the get_dept_name function; taking in emp_dept_id from the employee table, and grouping it by the return value of a function. And, in some other places, it cannot appear in the default setting.

The capacity to place software engineer characterized PL/SQL capacities inside SQL is a capable improvement to the oracle advancement enforcement.

Perform activities in SQL

SQL is standard query language and a set-at-once language, used to apply the activity to those lines and identify the arrangement of rows. And, it doesn’t support iterative processing against individual section values.

Re-arrange SQL proclamations

All the PL/SQL code need to get modularized, and applying a condition to SQL particularly to encapsulate the outflow of concealing the convoluted capacities and intelligent specifications. The readability of SQL statements will be benefited from programmer defined functions.

Enhance the execution of SQL articulations

SQL is a non-procedural language, but other application requirements demand on procedure statement coherence in SQL. PL/SQL embedded will perform the task in a more efficient way. The SQL language is robust enough, but in few circumstances, it is very incompetent to get the required output.

Combine business rules logic into smaller numbers

Centralize business principal rational into a less figure and effectively place in the function. It can’t rehash the rational crosswise over individual PL/SQL projects and SQL explanations.

Restrictions

shape Rules

  • PLSQL Calling Functions should be declared at the schema level or in the package specifications as they have visibility to other objects in the database.
  • PLSQL Calling Functions cannot call a local function declared within another subprogram or an anonymous block.
  • A function can only take formal parameters in the IN mode and doesn’t consider IN/OUT parameters.
  • Formal parameters and the return values should be in Oracle built-in datatypes, and not in PL/SQL built-in datatypes.
  • A function can modify the state of the data in the database table or a package.
  • A select statement is supposed to return the data and not the transaction to cause commit or roll-back.

Summary

shape Key Points

  • PLSQL Calling Functions from SQL – Helps in extending the functions and make them more useful.
  • Restrictions – Restrictions are the limitations set on the functions that are used in the select statement.