Difference between procedure and function in oracle plsql

-

 "It is the most accepted and guaranteed question asked in plsql interview for fresher, intermediate and experienced candidates."

INTRODUCTION:

Function is calculating the program result on the basis of the given input. It will be returning value or control to the code or calling program. Procedures are calculating or performing the tasks in particular way on the basis of the given inputs. It will be returning the control to the calling function or code, but it will not return any value. In SQL server and DBMS, procedure is termed as stored procedure. It saves their time in terms of reusability and maintainablity.Oracle procedure and function are called as subprograms which are stored in the database as database objects. Both of them are the block of codes which is used for performing operations and encapsulate logic for reuse.



FUNCTION:

It can be defined as the set of various instructions received as input from the users for performing the certain tasks. It will return the value which is basic on certain logic or calculation. It is used in calculation of the inputs and will provide outputs on the basis of given inputs. It can be pre-defined, and user defined. It will perform the required tasks on the basis of the block of codes. It is also called as the named plsql blocks. It always returns the results in the form of scalar value or table. It is used when the value needs to be computed or returned often within a query or part of an expression. It will return a value using the RETURN statement and the return type is specified in the function declaration. It has only IN parameters which is used to return a value and computed on the basis of input parameters.

PROCEDURE:

It can be defined as the set of various instructions received as input from the users for performing the certain tasks. It will not return the value on the basis of given inputs specified. It is also called as sub-programs. It is the set of plsql commands which is stored in the database with the name as like function. It is used and executed in the anonymous block or with help of exec keyword. It is useful in performance management and optimization, security and database management. It is used in the repetitive tasks and help in automation work. It does not return the value directly and it can return the value through OUT parameters.




DIFFERENCE:

Function

Procedure

It will calculate the program result on the basis of the given input.

It will perform the certain tasks in the particular order on the basis of inputs given.

It does not provide the support to try catch blocks.

It provides support to the try catch blocks.

It can be called or used within the SQL query. In other words, it can be used directly in SQL queries. SELECT statements or part of an expression like WHERE or ORDER BY. Select statements can have the function calls.

It cannot be called in the SQL query. In other words, it cannot be directly used in SQL queries or SELECT statements. It is invoked using the EXECUTE or Call statements. Select statements cannot have the procedure calls.

It can return the returning value or control to the code or calling function.

It can return the control but cannot return value to the code or calling function.

DML statements or DML queries are not used.

DML statements or DML queries are used. It can run complex processes.

It must deal with expressions.

It must not deal with expressions.

It can be called using procedure.

It cannot be called using function.

Explicit exception handling is used.

Explicit exception handling is not used.

Compilation occurs when it is called in the program.

Compilation occurs once and it cannot be repeatedly and don’t have to compile every single time.

It is not possible to insert, update and delete the data.

It is possible to insert, update and delete the data as operation work recommended to be changes on the database or server as per time.

It is used in an expression.

It is not used in the expression.

It can accept the output parameters. It uses OUT parameter.       

It can accept the input parameters. It uses IN, OUT and INOUT parameter.

Temporary tables are not used.

Temporary tables are used.

Transaction is not managed.

Transaction is managed.

In the header part, return clause is not present.

In the header part, return clause is present.

Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more