General MySQL Oracle SQL Server

Define Variables before Procedures and Functions in Oracle

Q

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

✍: 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

 

⇒ Formal Parameters and Actual Parameters in Oracle

⇐ Run-Away Recursive Calls in Oracle

⇑ Creating Oracle PL/SQL Procedures and Functions

⇑⇑ Oracle Database Tutorials

2018-03-18, 2242🔥, 0💬

General MySQL Oracle SQL Server