Transcription
PL/SQL Tutorial
PL/SQL TUTORIALSimply Easy Learning by tutorialspoint.comtutorialspoint.comi
COPYRIGHT& DISCLAIMER NOTICE Allthe content and graphics on this tutorial are the property of tutorialspoint.com. Any content fromtutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without thewritten permission of tutorialspoint.com. Failure to do so is a violation of copyright laws.This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy ofthe site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorialcontent contains some errors, please contact us at webmaster@tutorialspoint.comii
ABOUT THE TUTORIALPL/SQL TutorialPL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by OracleCorporation in the early 90's to enhance the capabilities of SQL.PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.This tutorial will give you great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMSconcepts.AudienceThis tutorial is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple andeasy steps. This tutorial will give you great understanding on PL/SQL Programming concepts, and after completing thistutorial, you will be at intermediate level of expertise from where you can take yourself to higher level of expertise.PrerequisitesBefore proceeding with this tutorial, you should have a basic understanding of software basic concepts like what isdatabase, source code, text editor and execution of programs, etc. If you already have understanding on SQL and othercomputer programming language, then it will be an added advantage to proceed.Copyright & Disclaimer Notice Allthe content and graphics on this tutorial are the property of tutorialspoint.com. Any content fromtutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without thewritten permission of tutorialspoint.com. Failure to do so is a violation of copyright laws.This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy ofthe site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorialcontent contains some errors, please contact us at webmaster@tutorialspoint.comiii
Table of ContentsPL/SQL Overview . 1Features of PL/SQL . 1Advantages of PL/SQL . 2Environment . 3Step 1 . 3Step 2 . 4Step 3 . 4Step 4 . 5Step 5 . 6Step 6 . 6Step 7 . 7Step 8 . 7Step 9 . 8Step 10 . 9Step 11 . 10Final Step. 11Text Editor . 12Basic Syntax . 13The 'Hello World' Example: . 13The PL/SQL Identifiers . 14The PL/SQL Delimiters . 14The PL/SQL Comments . 15PL/SQL Program Units . 15Data Types . 17PL/SQL Scalar Data Types and Subtypes . 17PL/SQL Numeric Data Types and Subtypes . 18PL/SQL Character Data Types and Subtypes . 19PL/SQL Boolean Data Types . 19PL/SQL Datetime and Interval Types . 19PL/SQL Large Object (LOB) Data Types . 20PL/SQL User-Defined Subtypes . 21iii
NULLs in PL/SQL . 21Variables . 22Variable Declaration in PL/SQL . 22Initializing Variables in PL/SQL . 23Variable Scope in PL/SQL . 23Assigning SQL Query Results to PL/SQL Variables . 24Constants . 26Declaring a Constant . 26The PL/SQL Literals . 27Operators . 28Arithmetic Operators . 28Example: . 29Relational Operators . 29Example: . 29Comparison Operators . 30LIKE Operator: . 31BETWEEN Operator: . 31IN and IS NULL Operators: . 32Logical Operators . 33Example: . 33PL/SQL Operator Precedence . 33Example: . 34Conditions . 35Syntax: . 36Flow Diagram:. 36Example 1: . 37Example 2: . 37Syntax: . 38Flow Diagram:. 38Example: . 39Syntax: . 40Example: . 40Syntax: . 40Flow Diagram:. 41Example: . 41Syntax: . 42Flow Diagram:. 42Example: . 42Syntax: . 43iii
Example: . 43Loops . 44Syntax: . 45Example: . 45Syntax: . 46Example: . 46Syntax: . 47Example: . 47Reverse FOR LOOP Statement. 48Example: . 49Labeling a PL/SQL Loop . 50The Loop Control Statements . 51Syntax: . 51Flow Diagram:. 51Example: . 52The EXIT WHEN Statement . 52Syntax: . 52Example: . 52Syntax: . 53Flow Diagram:. 53Example: . 54Syntax: . 54Flow Diagram:. 55Example: . 55Restrictions with GOTO Statement . 56Strings . 57Declaring String Variables . 57PL/SQL String Functions and Operators . 58Example 1 . 60Example 2 . 60Arrays. 62Creating a Varray Type . 62Example 1 . 63Procedures . 65Parts of a PL/SQL Subprogram . 65Creating a Procedure . 66Example: . 66Executing a Standalone Procedure . 67Deleting a Standalone Procedure . 67iii
Parameter Modes in PL/SQL Subprograms . 67IN & OUT Mode Example 1 .
This tutorial is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple and easy steps. This tutorial will give you great understanding on PL/SQL Programming concepts, and after completing this tutorial, you will be at intermediate level of expertise from where you can take yourself to higher level of expertise. Prerequisites Before proceeding with .File Size: 1MBPage Count: 128
