How To Use "IN" Parameter Properly

Home > Oracle > PL/SQL

Q

How To Use "IN" Parameter Properly? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

✍: FYIcenter.com

A

Here are the rules about IN parameters:

A formal IN parameter acts like constant. It can not be assigned with new values.
An actual IN parameter can take a value or a variable.
An actual IN parameter is passed by reference to the specified value or the value of the specified variable.
An actual IN parameter will not receive any value from the formal parameter.

Here is good example of a procedure with an IN parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS
  2    SITE CHAR(80) := 'FYICenter.com';
  3    PROCEDURE WELCOME_PRINT(S IN CHAR) AS
  4    BEGIN
  5      DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);
  6      -- S := 'Google.com'; -- Not allowed
  7    END;
  8  BEGIN          
  9    WELCOME_PRINT('MySpace.com');
 10    WELCOME_PRINT(SITE);
 11  END;            
 12  /

SQL> EXECUTE WELCOME;
Welcome to MySpace.com
Welcome to FYICenter.com

2007-04-25, 5176👍, 0💬

What Are the Parameter Modes Supported by PL/SQL
What Are the Parameter Modes Supported by PL/SQL? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions PL/SQL supports 3 parameter modes on procedure/function parameters: IN: This is the default mode. IN parameters allow the calling code to pass values into the procedure or function.... 2007-04-25, 6892👍, 0💬

What Are Named Parameters
What Are Named Parameters? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Named parameters are actual parameters specified not by position but by providing formal parameter names when calling the procedure or function. The main advantage of named parameters is that the caller d... 2007-04-21, 6652👍, 0💬

How To Use "IN OUT" Parameter Properly
How To Use "IN OUT" Parameter Properly? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Here are the rules about IN OUT parameters: A formal IN OUT parameter acts like an initialized variable. An actual IN OUT parameter must be a variable. An actual IN OUT parameter passes a cop... 2007-04-21, 6583👍, 0💬

Can Sub Procedure/Function Be Called Recursively
Can Sub Procedure/Function Be Called Recursively? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function: SQL> CREATE... 2007-04-25, 6402👍, 0💬

What Is the Scope of a Local Variable
What Is the Scope of a Local Variable? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions The scope of a variable can be described with these rules: A variable is valid within the procedure or function where it is defined. A variable is also valid inside a sub procedure or function... 2007-04-21, 5476👍, 0💬

How To Use "IN" Parameter Properly
How To Use "IN" Parameter Properly? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Here are the rules about IN parameters: A formal IN parameter acts like constant. It can not be assigned with new values. An actual IN parameter can take a value or a variable. An actual IN param... 2007-04-25, 5177👍, 0💬

What Is the Order of Defining Local Variables and Sub Procedures/Functions
What Is the Order of Defining Local Variables and Sub Procedures/Functions? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions In the declaration part, you must define all local variables before defining any sub procedures or sub functions. See the following sample script: SQL> CRE... 2007-04-25, 5174👍, 0💬

How To Define Default Values for Formal Parameters
How To Define Default Values for Formal Parameters? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions If you have an IN parameter, you can make it as an optional parameter for the calling statement by defining the formal parameter with the DEFAULT clause. This gives you the freedo... 2007-04-21, 5110👍, 0💬

What Happens If Recursive Calls Get Out of Control
What Happens If Recursive Calls Get Out of Control? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions What happens if your code has bug on recursive procedure calls, which causes an infinite number nested procedure calls? The answer is so good. Oracle server seems to offer no prot... 2007-04-25, 5106👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.