When should I go for a procedure or function in PL / SQL?

I am new to PL / SQL, have tried some practice examples.
I have a few questions regarding PL / SQL PROCEDURE and FUNCTION :

When do I need to switch to PROCEDURE or FUNCTION?

This means that no matter what task I perform using FUNCTION , this same task will be performed using PROCEDURE . then why should I go for this feature? Is their FUNCTION advantage over PROCEDURE in PL / SQL?

FUNCTION should return a value. Is this the only advantage to using a function, or are there other benefits to the functions?

+6
source share
8 answers

A user-defined function with certain restrictions can be used in SELECT and PL / SQL IF , while PROCEDURE cannot.

You can SELECT from FUNCTION , which CAST uses the pipeline and PIPE ROW as a table, but it is an advanced PL / SQL function that you can use much later.

Consult the Oracle Developer documentation online as it is free and very good: Designing and using stored procedures

+5
source

I would like to clarify that the answer to the question of whether to use a stored procedure or function depends entirely on your business requirement and design workflow, provided that you clearly understand the purpose of your program. If you are not clear about your purpose, simply, as your question, no coding procedures and functions will be useful.

You must keep in mind that stored procedures and functions serve different purposes in PL / SQL programming. These are the following:

  • Stored Procedures:

    a. Stored procedures are named blocks (as opposed to anonymous blocks) that are able to accept parameters and work with them.

    b. Stored procedures define an independent procedural workflow where you can perform a series of DML and / or other operations.

    from. Stored procedures must not return a value. Therefore, they cannot be called from an SQL statement. Stored procedures must be executed from a PL / SQL block name or anonymous.

    D. Advantages:

    • The procedure should not return a value (this may also be a drawback).
    • It can be used to execute a series of DML or DDL (yes, this is possible using dynamic SQL with several restrictions).
    • It can be simply called as an independent statement from a PL / SQL block. eg.

       myProcedure (x, y); 

    e. Demerits:

    • Cannot be called from SQL query - DML or SELECT .
    • Cannot be used in indexes.
  • Functions:

    a. Functions are named blocks that can take parameters and return a value.

    b. Functions also define a procedural workflow, but when used in SQL statements, you cannot execute DML or DDL.

    from. The function must be called from an SQL or PL / SQL statement, where the value returned by the function is used, that is, it is assigned to a variable, passed as a parameter, etc.

    D. Advantages:

    • Can be used in an SQL query - DML or SELECT .
    • It can be used in functional indexes if the function is deterministic (this means that for a certain set of inputs the function returns the same output every time it is called).

    e. Demerits:

    • If the function called from the SQL query contains any DML, the query fails.
    • A required function returns a value. Therefore, a function call cannot be an independent statement, similar to a procedure call.

For more information, visit Oracle Docs .

+5
source
  • The function will return a value, A "value" will be one of many things, including PL / SQL tables, link cursors, etc. Adding to this, you can use the function in SQL statements, while procedures cannot be used.
  • Procedures are used to execute business logic, where we can return multiple values ​​from a procedure using the OUT or IN OUT parameters.
  • Personally, I use the function for calculations. For example: check a specific condition, for example, get a value based on a condition, checking the condition for true or false.
  • You may have DML instructions (insert, update, delete) in a function. But you cannot call such a function in a SQL query. * For example: if you have a function that updates a table, you cannot call this function in any SQL query.

     select myFunction(field) from sometable; --will throw error. 

At your choice, whether the use of the procedure or function depends on your requirements and your comfort.

+2
source

Main advantages:

  • The function should return values ​​if the procedure may or cannot return values.
  • A function mainly used to calculate values. Where as a procedure, mainly used for executive business logic.
  • A function to extract the value, where as a procedure for manupliate the value.
  • A function should return only one value, but it takes many return types.
0
source

Whenever you want to return some value and you have to use this value, go to the function. If you want to return some value as the final result, go to the procedure.

0
source

There are more advantages to using a procedure over a function:

  • If we want to execute the Dynamically SQL statement using the execute statement immediately, we use the procedure. This cannot be done inside a function.

  • The procedure can be executed independently, and the function must be part of the executable statement, since it cannot be performed independently.

-1
source
Function

should return the value that it is used for the calculation that we use. A function should return only one value, but it takes many return types. functions we can use select statement. the procedure may or may not return a value. the procedure is that we can use dml operations. procedure that we can perform only.

-1
source

Of course, you can execute dynamic sql in a function. Write a simple function to run the test and you will see that it works.

You can use any function or procedure most of the time. The differences are whether you want to use them in an SQL statement or an IF statement, then use a function. Otherwise, use what works best for you and the calling application.

-3
source

Source: https://habr.com/ru/post/911550/


All Articles