Reference no: EM131070419
Answer the questions below APA format and Cite work 300 words or less
1. What is the difference between using a procedure versus a function and when should we use each?
Discuss some ways that we can code more efficient functions and procedures.
Respond to the following below APA format and Cite work 200 words or less
2. At first glance, the difference between a procedure and a function can seem a bit confusing, as they both are stored units that can receive inputs and produce outputs. However, the key difference is that a function is used as part of an expression, whereas a procedure cannot be used in an expression (Casteel, 2013, p. 237). Likewise, where a procedure can serve as an entire statement, a function cannot.
To make the above point clearer, it is worth noting that we have used built-in functions plenty of times. For instance, the TO_CHAR function has been issued in many examples and can be used to convert a value to a VARCHAR2 datatype (Oracle, n.d.). The following query demonstrates this:
SELECT TO_CHAR('01110' + 1) FROM dual
Since functions cannot stand on their own, there's no point to calling TO_CHAR without assigning it to a variable or using it in an expression.
This is because they must return a value, and that value needs to be ‘handled'.
Another subtle difference between the two is how the RETURN statement is utilized. In a function the RETURN statement must return a value. In fact, a function can implement multiple RETURN statements. However, only one RETURN statement is executed based on which one is encountered first. Usually, this scenario involves conditional statements to determine which value is returned. The RETURN statement in a procedure does not return a value. It is implemented merely to control program flow. "When the RETURN statement is executed, the procedure immediately returns execution to the next statement after the procedure call." (Casteel, 2013, p. 228).
References
Casteel, J. (2013). Oracle 11g: PL/SQL Programming. Boston, MA: Course Technology.
Oracle.(n.d.).TO_CHAR (number). Retrieved from https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions181.htm
Respond to the following below APA format and Cite work 200 words or less
3. There are several differences between procedure and function. One difference is that stored procedures compiled only once and can be called again and again without being compiled each time, this improves performance and saves time, on the other hand functions compile each time they get called. Second, functions are restricted to returning a single value, while procedures can have multiple OUT parameters. Third, functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can't.
Lastly, procedures are generally used for performing business logic while function are used for computations.
Learn PL/SQL: Procedures and Functions. (2006). Retrieved May 24, 2016, from https://it.toolbox.com/blogs/oracle-guide/learn-plsql-procedures-and-functions-13030.