Transcription
Learn PL/SQL in 1 DayBy Krishna RungtaCopyright 2019 - All Rights Reserved – Krishna RungtaALL RIGHTS RESERVED. No part of this publication may be reproducedor transmitted in any form whatsoever, electronic, or mechanical,including photocopying, recording, or by any informational storage orretrieval system without express written, dated and signed permissionfrom the author.
Table Of ContentChapter 1: What Is PL/SQL? Introduction & Architecture1. What is PL/SQL?2. Architecture of PL/SQL3. Advantage of Using PL/SQLChapter 2: SQL Vs PL/SQL Vs T-SQL: Key Differences1. Difference between SQL and PL/SQL2. Difference Between T-SQL and PL-SQL3. Difference between SQL and T-SQLChapter 3: PL/ SQL Block: STRUCTURE, Syntax,ANONYMOUS Example1.2.3.4.What is PL/SQL block?Block StructurePL/SQL Block SyntaxTypes of PL/SQL blockChapter 4: PL/SQL First Program: Hello World Example1.2.3.4.5.What is SQL* Plus?Connecting to DatabaseHow to write a simple program using PL/SQLDeclaring and usage of variables in the programComments in PL/SQLChapter 5: Oracle PL/SQL Data Types: Character, Number, Boolean,Date, LOB
1.2.3.4.5.6.What is PL/SQL Datatypes?CHARACTER Data Type:NUMBER Data Type:BOOLEAN Data Type:DATE Data Type:LOB Data Type:Chapter 6: Oracle PL/SQL Variable Identifiers Tutorial with Examples1.2.3.4.5.Properties of IdentifiersNaming Conventions of IdentifiersVariables – An IdentifierDeclaration of VariablesData storing in VariablesChapter 7: Oracle PL/SQL Collections: Varrays, Nested & Index byTables1.2.3.4.5.6.What is Collection?VarraysNested TablesIndex-by-tableConstructor and Initialization Concept in CollectionsCollection MethodsChapter 8: Oracle PL/SQL Records Type with ExamplesChapter 9: Oracle PL/SQL IF THEN ELSE Statement: ELSIF, NESTED-IF1. What are Decision-Making Statements?2. IF-THEN Statement3. IF-THEN-ELSE Statement4. IF-THEN-ELSIF Statement5. NESTED-IF Statement
Chapter 10: Oracle PL/SQL: CASE Statement with Examples1. What is CASE Statement?2. SEARCHED CASE StatementChapter 11: Oracle PL/SQL LOOP with Example1.2.3.4.5.6.What are Loops?Introduction to Loops ConceptLoop Control StatementsTypes of Loop in PL/SQLBasic Loop StatementLabeling of LoopsChapter 12: Oracle PL/SQL FOR LOOP with Example1. What is For Loop?2. Nested LoopsChapter 13: Oracle PL/SQL WHILE LOOP with ExampleChapter 14: Oracle PL/SQL Stored Procedure & Functions withExamples1.2.3.4.5.6.Terminologies in PL/SQL SubprogramsWhat is Procedure in PL/SQL?What is Function?Similarities between Procedure and FunctionProcedure Vs. Function: Key DifferencesBuilt-in Functions in PL/SQLChapter 15: Oracle PL/SQL Exception Handling: Examples to Raise Userdefined Exception
1.2.3.4.5.6.7.What is Exception Handling in PL/SQL?Exception-Handling SyntaxTypes of ExceptionPredefined ExceptionsUser-defined ExceptionPL/SQL Raise ExceptionImportant points to note in ExceptionChapter 16: Oracle PL/SQL Insert, Update, Delete & Select Into[Example]1.2.3.4.5.DML Transactions in PL/SQLData InsertionData UpdateData DeletionData SelectionChapter 17: Oracle PL/SQL Cursor: Implicit, Explicit, Cursor FOR Loop[Example]1.2.3.4.5.What is CURSOR in PL/SQL?Implicit CursorExplicit CursorCursor AttributesFOR Loop Cursor statementChapter 18: Oracle PL/SQL BULK COLLECT: FORALL Example1.2.3.4.What is BULK COLLECT?FORALL ClauseLIMIT ClauseBULK COLLECT AttributesChapter 19: Autonomous Transaction in Oracle PL/SQL:Commit, Rollback
1. What are TCL Statements in PL/SQL?2. What is Autonomous TransactionChapter 20: Oracle PL/SQL Package: Type, Specification, Body[Example]1.2.3.4.5.6.7.8.9.10.11.12.What is Package in Oracle?Components of PackagesPackage SpecificationPackage BodyReferring Package ElementsCreate Package in PL/SQLForward DeclarationsCursors Usage in PackageOverloadingDependency in PackagesPackage InformationUTL FILE – An OverviewChapter 21: Oracle PL/SQL Trigger Tutorial: Instead of, Compound [Example]1.2.3.4.5.6.7.What are Triggers in PL/SQL?Benefits of TriggersTypes of Triggers in OracleHow to Create Trigger:NEW and :OLD ClauseINSTEAD OF TriggerCompound TriggerChapter 22: Oracle PL/SQL Object Types Tutorial with EXAMPLES1.2.3.4.5.6.7.What is Object Type in PL/SQL?Components of Object TypesCreate Object in OracleDeclaration Initialization of Object TypeConstructorsInheritance in Object TypeEquality of PL/SQL Objects
Chapter 23: Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate &DBMS SQL1.2.3.4.What is Dynamic SQL?Ways to write dynamic SQLNDS (Native Dynamic SQL) - Execute ImmediateDBMS SQL for Dynamic SQLChapter 24: Nested Blocks & Variable Scope in Oracle PL/SQL Tutorial[Example]1. Nested Block Structure2. Scopes in Nested Block: Variable Scope
Chapter 1: What Is PL/SQL? Introduction &ArchitectureWhat is PL/SQL?PL/SQL is an extension of Structured Query Language (SQL) that is used inOracle. Unlike SQL, PL/SQL allows the programmer to write code in aprocedural format. Full form of PL/SQL is “Procedural Languageextensions to SQL”.It combines the data manipulation power of SQL with the processingpower of procedural language to create super powerful SQL queries.PL/SQL means instructing the compiler ‘what to do’ through SQL and ‘howto do’ through its procedural way.Similar to other database languages, it gives more control to theprogrammers by the use of loops, conditions and object-orientedconcepts.Architecture of PL/SQLThe PL/SQL architecture mainly consists of following threecomponents:1. PL/SQL block2. PL/SQL Engine3. Database ServerPL/SQL block:This is the component which has the actual PL/SQL code. Thisconsists of different sections to divide the code logically
(declarative section for declaring purpose, execution section forprocessing statements, exception handling section for handlingerrors)It also contains the SQL instruction that used to interact with thedatabase server.All the PL/SQL units are treated as PL/SQL blocks, and this is thestarting stage of the architecture which serves as the primary input.Following are the different type of PL/SQL units.Anonymous Block FunctionLibrary ProcedurePackage BodyPackage Specification TriggerTypeType BodyPL/SQL EnginePL/SQL engine is the component where the actual processing of thecodes takes place.PL/SQL engine separates PL/SQL units and SQL part in the input (asshown in the image below).The separated PL/SQL units will be handled by the PL/SQLengine itself.The SQL part will be sent to database server where the actualinteraction with database takes place.It can be installed in both database server and in the applicationserver.Database Server:This is the most important component of Pl/SQL unit whichstores the data.The PL/SQL engine uses the SQL from PL/SQL units to interact withthe database server.It consists of SQL executor which parses the input SQL statements and
execute the same.Below is the pictorial representation of Architecture of PL/SQL.PL/SQL Architecture DiagramAdvantage of Using PL/SQL1. Better performance, as SQL is executed in bulk rather than asingle statement2. High Productivity3. Tight integration with SQL4. Full Portability5. Tight Security6. Support Object Oriented Programming concepts.
Chapter 2: SQL Vs PL/SQL Vs T-SQL: KeyDifferencesSQL is the standard language to query a database.PL SQL basically stands for “Procedural Language extensions to SQL.” This isthe extension of Structured Query Language (SQL) that is used in Oracle.T-S QL basically stands for "Transact-SQL." This is the extension ofStructured Query Language (SQL) that is used in Microsoft.Difference between SQL and PL/SQLSQLSQL is a single query that is usedto perform DML and DDLoperations.It is declarative, that defines whatneed to be done, rather than howthings need to be done.Execute as a single statement.Mainly used to manipulate data.Interaction with a Databaseserver.Cannot contain PL/SQL code in it.PL/SQLPL/SQL is a block of codes thatused to write the entire programblocks/ procedure/ function, etc.PL/SQL is procedural that defineshow the things needs to be done.Execute as a whole block.Mainly used to create anapplication.No interaction with the databaseserver.It is an extension of SQL, so that itcan contain SQL inside it.Difference Between T-SQL and PL- SQLT-SQLT-SQL is a Microsoft product.Full Form of TL SQL is TransactStructure Query language.T-SQL gives a high degree ofcontrol to programmers.PL-SQLPL-SQL is developed by Oracle.Full Form of PL SQL is ProceduralLanguage Structural QueryLanguage.It is a natural programminglanguage that blends easily withthe SQL
T-SQL performs best withMicrosoft SQL serverIt is easy and simple tounderstand.T-SQL allows inserting multiplesrows into a table using the BULKINSERT statement.PL-SQL performs best with Oracledatabase server.PL-SQL is complex to understand.PL/SQL supports oops conceptslike data encapsulation, functionoverloading, and informationhiding.SELECT INTO statement used in T- INSERT INTO statement must beSQLused in PL/SQLIn T-SQL NOT EXISTS clause used In PL/SQL, there is a MINUSalong with SELECT statements.operator, which could be usedwith SELECT statementsDifference between SQL and T-SQLSQLSQL is a programming languagewhich focuses on managingrelational databases.This is used for controlling andmanipulating data where largeamounts of information arestored about products, clients,etc.SQL queries submittedindividually to the databaseserver.The syntax was formalized formany commands; some of theseare SELECT, INSERT, UPDATE,DELETE, CREATE, and DROP.T-SQLT-SQL is a procedural extensionused by SQL Server.T-SQL has some features that arenot available in SQL. Likeprocedural programmingelements and a local variable toprovide more flexible control ofhow the application flows.T-SQL writes a program in such away that all commands aresubmitted to the server in a singlegoIt also includes special functionslike the converted date () andsome other functions which arenot part of the regular SQL.
Chapter 3: PL/ SQL Block: STRUCTURE,Syntax, ANONYMOUS ExampleWhat is PL/SQL block?In PL/SQL, the code is not executed in single line format, but it isalways executed by grouping the code into a single element calledBlocks. In this tutorial, you are going to learn about these blocks.Blocks contain both PL/SQL as well as SQL instruction. All these instructionwill be executed as a whole rather than executing a single instruction at atime.Block StructurePL/SQL blocks have a pre-defined structure in which the code is to begrouped. Below are different sections of PL/SQL blocks.1. Declaration section2. Execution section3. Exception-Handling sectionThe below picture illustrates the different PL/SQL block and theirsection order.
Declaration SectionThis is the first section of the PL/SQL blocks. This section is an optional part.This is the section in which the declaration of variables, cursors, exceptions,subprograms, pragma instructions and collections that are needed in theblock will be declared. Below are few more characteristics of this part.This particular section is optional and can be skipped if nodeclarations are needed.This should be the first section in a PL/SQL block, if present. Thissection starts with the keyword ‘DECLARE’ for triggers andanonymous block. For other subprograms, this keyword will notbe present. Instead, the part after the subprogram name definitionmarks the declaration section.This section should always be followed by execution section.
Execution SectionExecution part is the main and mandatory part which actually executes thecode that is written inside it. Since the PL/SQL expects the executablestatements from this block this cannot be an empty block, i.e., it should haveat least one valid executable code line in it. Below are few morecharacteristics of this part.This can contain both PL/SQL code and SQL code.This can contain one or many blocks inside it as a nested block. Thissection starts with the keyword ‘BEGIN’.This section should be followed either by ‘END’ or ExceptionHandling section (if present)Exception-Handling Section:The exception is unavoidable in the program which occurs at run-time andto handle this Oracle has provided an Exception-handling section in blocks.This section can also contain PL/SQL statements. This is an optional sectionof the PL/SQL blocks.This is the section where the exception raised in the executionblock is handled.This section is the last part of the PL/SQL block.Control from this section can never return to the execution block. Thissection starts with the keyword ‘EXCEPTION’.This section should always be followed by the keyword ‘END’.The Keyword ‘END’ marks the end of PL/SQL block.PL/SQL Block SyntaxBelow is the syntax of the PL/SQL block structure.
DECLARE --optional declarations BEGIN--mandatory executable statements. At least one executable statement ismandatory EXCEPTION --optional exception handles END;/--mandatoryNote: A block should always be followed by ‘/’ which sends theinformation to the compiler about the end of the block.Types of PL/SQL blockPL/SQL blocks are of mainly two types.1. Anonymous blocks2. Named BlocksAnonymous blocks:
Anonymous blocks are PL/SQL blocks which do not have any namesassigned to them. They need to be created and used in the same sessionbecause they will not be stored in the server as database objects.Since they need not store in the database, they need no compilationsteps. They are written and executed directly, and compilation andexecution happen in a single process.Below are few more characteristics of Anonymous blocks.These blocks don’t have any reference name specified for them.These blocks start with the keyword ‘DECLARE’ or ‘BEGIN’.Since these blocks do not have any reference name, these cannot bestored for later purpose. They shall be created and executed in thesame session.They can call the other named blocks, but call to anonymous block isnot possible as it is not having any reference.It can have nested block in it which can be named or anonymous. Itcan also be nested in any blocks.These blocks can have all three sections of the block, in whichexecution section is mandatory, the other two sections areoptional.Named blocks:Named blocks have a specific and unique name for them. They are storedas the database objects in the server. Since they are available as databaseobjects, they can be referred to or used as long as it is present on the server.The compilation process for named blocks happens separately whilecreating them as a database objects.Below are few more characteristics of Named blocks.These blocks can be called from other blocks.The block structure is same as an anonymous block, except it will neverstart with the keyword ‘DECLARE’. Instead, it will start with
the keyword ‘CREATE’ which instruct the compiler to create it as adatabase object.These blocks can be nested within other blocks. It can also containnested blocks.Named blocks are basically of two types:1. Procedure2. FunctionWe will learn more about these named blocks in “Procedure” and“Function” topics in later tutorial.SummaryAfter this tutorial, you should be aware of PL/SQL blocks and its types,different sections of blocks and their usages. The detailed description of thenamed PL/SQL blocks will be covered in the later tutorial.
PL/SQL engine is the component where the actual processing of the codes takes place. PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below). The separated PL/SQL units will be handled by the PL/SQL engine itself. The SQL part will be sent to database server where the actual interaction with database takes .
