Skip to content

Difference between EXISTS and IN in PL/SQL

Difference between Actual and Formal Parameters in PL/SQL

Last Updated : 23 Nov, 2020

A parameter is an optional list of parameters that you define both pass information into the procedure and send information out of procedure back to the calling program. Parameter is also known as argument. When you define a parameter, you also specify the way in which it can be used. There are three different modes of parameter or argument. 1. Actual Parameters : The arguments that are passed in a function call are called actual arguments. These arguments are defined in the calling function. These are the variables or expressions referenced in the parameter list of a subprogram call. There is no need to specify datatype in actual parameter. Example :

  // X and Y NUMBER ARE ACTUAL PARAMETERS  SQL> CREATE OR REPLACE FUNCTION FUNC1(X NUMBER,                                            Y NUMBER)   2    RETURN NUMBER IS  3    R NUMBER;  4    BEGIN  5    R:=X+Y;  6    RETURN(R);  7    END;  8    /  FUNCTION CREATED.    SQL>|

2. Formal Parameters : These are the variables or expressions referenced in the parameter list of a subprogram specification. The datatype of the receiving value must be defined. The scope of formal arguments is local to the function definition in which they are used. Example :

  SQL> DECLARE  2    N1 NUMBER:=10;  3    N2 NUMBER:=20;  4    S NUMBER;  5    BEGIN  6    S:=FUNC1(N1, N2);  7    DBMS_OUTOUT.PUT_LINE('RESULT IS: '||S);  8    END;  9    /    OUTPUT: RESULT IS: 30  PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.  SQL>|

Difference between Actual and Formal Parameters :

Actual Parameters	Formal Parameters
When a function is called, the values (expressions) that are passed in the function call are called the arguments or actual parameters.	The parameter used in function definition statement which contain data type on its time of declaration is called formal parameter.
These are the variables or expressions referenced in the parameter list of a subprogram call.	These are the variables or expressions referenced in the parameter list of a subprogram specification.
Actual Parameters are the parameters which are in calling subprogram.	Formal Parameters are the parameters which are in called subprogram.
There is no need to specify datatype in actual parameter.	The datatype of the receiving value must be defined.
The parameters are written in function call are known as actual parameters.	The parameters are written in function definition are known as formal parameters.
Actual Parameters can be constant values or variable names.	Formal Parameters can be treated as local variables of a function in which they are used in the function header.

Difference between EXISTS and IN in PL/SQL

itskawal2000

Improve

Article Tags :

Similar Reads

Difference between Argument Modes in PL/SQL

Argument modes are basically used to describe the behavior of the formal parameters. There are three types of argument modes which are used in the sub program which are as follows : IN Mode, OUT Mode, and IN OUT Mode. These are explained as following : IN Mode : It is the default argument mode in th

Difference Between Decimal and Float in PL/SQL

Have you ever thought about the differences between Decimal and Float in PL/SQL? These two numeric data types play important roles in database programming that offer unique features for handling numeric values. In this article, we'll explore What are Decimals and Float along with their syntax, usage

Difference Between PARTITION BY and GROUP BY in PL/SQL

In Oracle PL/SQL, effective data manipulation is crucial for achieving optimal performance in database operations. Two essential SQL clauses often used to organize data are PARTITION BY and GROUP BY. Although both are used for structuring and analyzing data, they function quite differently and are u

Difference between EXISTS and IN in PL/SQL

PL/SQL is a procedural language designed to enable developers to combine the power of procedural language with Oracle SQL. Oracle develops and serves as one of the three key programming languages embedded in the Oracle database, alongside SQL and Java. PL/SQL includes procedural language elements su

Difference between SQL and PLSQL

Introduction SQL: Structured Query Language (SQL) is a standard Database language that is used to create, maintain and retrieve the relational database. The advantages of SQL are: SQL could be a high-level language that has a larger degree of abstraction than procedural languages.It enables the syst

Difference Between Views and Materialized Views in PL/SQL

In PL/SQL views play an important role in data accessibility, data manipulation, and query optimization. Views help restrict the user's data, which is not supposed to be accessed by a particular user. Views are instrumental in securing data by restricting the visibility of specific fields or rows ba

Difference between SQL and T-SQL

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, enabling operations like querying, updating, and deleting data. T-SQL (Transact-SQL), an extension of SQL developed by Microsoft, adds advanced features and procedural capabilities specifical

Difference Between Distinct and Group By in PL/SQL

In PL/SQL, knowing the difference between DISTINCT and GROUP BY is important for working with data effectively. Although DISTINCT and GROUP BY might seem similar, they serve different purposes. In this article, we'll explore DISTINCT and GROUP BY Clause with the syntax and various examples along the

Difference between Statement and PreparedStatement

1. Statement : It is used for accessing your database. Statement interface cannot accept parameters and useful when you are using static SQL statements at runtime. If you want to run SQL query only once then this interface is preferred over PreparedStatement. Example - //Creating The Statement Objec

Difference between ALTER and UPDATE Command in SQL

1. ALTER Command: ALTER SQL command is a DDL (Data Definition Language) statement. ALTER is used to update the structure of the table in the database (like add, delete, modify the attributes of the tables in the database). Syntax: // add a column to the existing table ALTER TABLE tableName ADD colum