What Is the Order of Defining Local Variables and Sub Procedures/Functions

Q

What Is the Order of Defining Local Variables and Sub Procedures/Functions? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

✍: FYIcenter.com

A

In the declaration part, you must define all local variables before defining any sub procedures or sub functions. See the following sample script:

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

SQL> EXECUTE WELCOME;
Welcome to FYICenter

Notice that variable SITE should be declared before procedure WELCOME_PRINT

2007-04-25, 5549👍, 0💬

How To Define a Sub Function
How To Define a Sub Function? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A sub function is a function defined and used inside another procedure or function. You need to define a sub function in the declaration part of the enclosing procedure or function. Sub function defini... 2007-04-25, 8323👍, 0💬

What Is the Difference between Formal Parameters and Actual Parameters
What Is the Difference between Formal Parameters and Actual Parameters? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions Formal parameter and actual parameter are two different terms related parameters used in the procedures and functions: A formal parameter is a term used to ref... 2007-04-25, 7541👍, 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, 7302👍, 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, 7025👍, 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, 6870👍, 0💬

How To Define a Sub Procedure
How To Define a Sub Procedure? - Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions A sub procedure is a named procedure defined and used inside another procedure or function. You need to define a sub procedure in the declaration part of the enclosing procedure or function. Sub proce... 2007-04-25, 5580👍, 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, 5550👍, 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, 5485👍, 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, 5389👍, 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.