PL/SQL (I) PDF

University Politehnica of Bucharest PL/SQL (I) Ș.L. Dr. Ing. Ciprian-Octavian Truică [email protected]  Overview PL/SQL PL/SQL Main Features PL/SQL Engine 10/7/2024 2 ...

University Politehnica of Bucharest PL/SQL (I) Ș.L. Dr. Ing. Ciprian-Octavian Truică [email protected]  Overview PL/SQL PL/SQL Main Features PL/SQL Engine 10/7/2024 2  Examples Database 10/7/2024 3  Overview PL/SQL PL/SQL Main Features PL/SQL Engine 10/7/2024 4  PL/SQL PL/SQL (Procedural Language/SQL) Is the Oracle procedural extension of SQL Is a portable, high-performance transaction- processing language Is a proprietary language 10/7/2024 5  PL/SQL Advantages of PL/SQL: Tight Integration with SQL High Performance High Productivity Portability Scalability Manageability Support for Object-Oriented Programming 10/7/2024 6  PL/SQL Tight Integration with SQL PL/SQL supports SQL data manipulation statements DML Cursor control statements (OPEN, LOOP, CLOSE) Transaction control statements TCL 10/7/2024 7  PL/SQL Tight Integration with SQL DML (Data Manipulation Language) SELECT INSERT UPDATE DELETE TCL (Transaction Control Language) COMMIT ROLLBACK SAVEPOINT 10/7/2024 8  PL/SQL Tight Integration with SQL PL/SQL supports All Oracle SQL Functions All SQL Operators Pseudocolumns A pseudocolumn behaves like a table column but is not actually stored in the table E.g., COLUMN_VALUE, ROWID, ROWNUM, XMLDATA 10/7/2024 9  PL/SQL Tight Integration with SQL PL/SQL supports all SQL data types No need for conversions between PL/SQL and SQL data types Attributes for data type correlation %ROWTYPE attribute to declare a record that represents either a full or partial row of a database table or view %TYPE attribute to declare a data item of the same data type as a previously declared variable or column 10/7/2024 10  PL/SQL Tight Integration with SQL PL/SQL supports Running SQL query Processing the rows of the result set one at a time PL/SQL functions can be declared and defined in the WITH clauses of SQL SELECT statements (CTE – Common Table Expressions) 10/7/2024 11  Examples 10/7/2024 12  PL/SQL Tight Integration with SQL PL/SQL supports Static SQL SQL whose full text is known at compile time Dynamic SQL SQL whose full text is not known until run time Make applications more Flexible Versatile 10/7/2024 13  PL/SQL Tight Integration with SQL Dynamic SQL Drawbacks Performance loss The execution plan for dynamic queries cannot be cached Hard to debug The error management becomes more unreliable. Maintenance is difficult because the schema is hard coded in the dynamic code. Security can be compromised with SQL injection 10/7/2024 14  PL/SQL High Performance PL/SQL Statements can be organized into blocks (BEGIN … END) Blocks significantly reduce traffic between the application and the database. High performance is achieved through: Bind Variables Subprograms Optimizer 10/7/2024 15  PL/SQL High Performance Bind Variables Are placeholders for actual values in SQL statements Allow the database server to prepare the statement once and execute it multiple times without reparsing or reanalyzing it. The PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind variables when embedding a SQL data manipulation statements directly in a block Oracle Database can reuse these SQL statements each time the same code runs PL/SQL does not create bind variables automatically when you use dynamic SQL They can be used with dynamic SQL by specifying them explicitly 10/7/2024 16  PL/SQL High Performance PL/SQL supports subprograms: Functions Procedures Subprograms Are stored in executable form Can be invoked repeatedly Are cached lowering memory requirements Are shared among users lowering invocation overhead A single invocation over the network can start a large job Reducing network traffic Improving response times Optimizer The PL/SQL compiler has an optimizer that can rearrange code for better performance. 10/7/2024 17  PL/SQL High Productivity PL/SQL Is the same in all environments Can be used to write compact code for manipulating data Is a scripting language Can query, transform, and update data in a database Supported by many other Oracle tools (Forms Builder, Apex, etc.) 10/7/2024 18  PL/SQL Portability PL/SQL is a portable and standard language for Oracle development PL/SQL applications can be run on any operating system and platform where Oracle Database runs PL/SQL applications can be run on different environments (e.g., server, client application, etc.) 10/7/2024 19  PL/SQL Scalability PL/SQL stored subprograms increase scalability by centralizing application processing on the database server The shared memory facilities of the shared server let Oracle Database support thousands of concurrent users on a single node Oracle Connection Manager can be used to multiplex network connections 10/7/2024 20  PL/SQL Manageability PL/SQL stored subprograms increase manageability: Only one copy of a subprogram can be maintained on the database server Rather than one copy on each client system Any number of applications can use the subprograms Subprograms can be changed without affecting the applications that invoke them 10/7/2024 21  PL/SQL Support for Object-Oriented Programming PL/SQL supports OOP Allows defining object types that can be used in object- oriented designs Abstract Data Types (ADT): Consists of a data structure and subprograms that manipulate the data The variables that form the data structure are called attributes. The subprograms that manipulate the attributes are called methods. 10/7/2024 22  Overview PL/SQL PL/SQL Main Features PL/SQL Engine 10/7/2024 23  PL/SQL Main Features PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages SQL statements can be issued from a PL/SQL program Complex problems can be broken into easily understandable subprograms Subprograms can be reused in multiple applications 10/7/2024 24  PL/SQL Main Features PL/SQL is a procedural language PL/SQL supports the declaration of Constants Variables Using PL/SQL, a developer can Control program flow Define subprograms Handle runtime errors 10/7/2024 25  PL/SQL Main Features PL/SQL provides: Blocks Variables and Constants Error Handling Subprograms Packages Triggers Input and Output Data Abstraction Control Statements Conditional Compilation Processing a Query Result Set One Row at a Time 10/7/2024 26  PL/SQL Main Features Blocks The basic unit of a PL/SQL source program is the block The block groups related declarations and statements. A PL/SQL block is defined by the keywords: DECLARE - declarative part BEGIN - an executable part EXCEPTION - an exception-handling part END. Only the executable part is required (BEGIN... END) A block can have a label. 10/7/2024 27  PL/SQL Main Features Blocks 10/7/2024 28  PL/SQL Main Features Blocks Between DECLARE and BEGIN Declaration part of the block A developer can declare here: Variables Cursors User-defined data types User-defined exceptions Subprograms (Functions, Procedures) This part is optional 10/7/2024 29  PL/SQL Main Features Blocks Between BEGIN and EXCEPTION Executable part of the block Contains Executable statements SQL queries Control structures Loop structures The EXECEPTION part of the block is optional In this case, the block ends with the key-word END. 10/7/2024 30  PL/SQL Main Features Blocks Between EXCEPTION and END Exception handler part of the block This part is optional 10/7/2024 31  PL/SQL Main Features Blocks Block example: BEGIN null; END; / 10/7/2024 32  PL/SQL Main Features Blocks There should be at least on instruction in the execution part of the block (BEGIN … END) Each instruction in a PL/SQL block ends with a semicolon (;) There is no semicolon after DECLARE, BEGIN and EXCEPTION There is a semicolon after END To run the block in SQL*Plus use: Slash (/) Run (r) 10/7/2024 33  Examples 10/7/2024 34  Examples 10/7/2024 35  PL/SQL Main Features Blocks Declarations are local to the block Declarations cease to exist when the block completes execution This helps to avoid cluttered namespaces for variables and subprograms Blocks can be nested: A block is an executable statement A block can appear in another block wherever an executable statement is allowed. 10/7/2024 36  PL/SQL Main Features PL/SQL Nested Blocks 10/7/2024 37  PL/SQL Main Features PL/SQL Nested Blocks 10/7/2024 38  PL/SQL Main Features Blocks A block can be Submitted to an interactive tool (such as SQL*Plus or Enterprise Manager) Embedded in an Oracle Precompile Embedded in an OCI (Oracle Call Interface) program The interactive tool or program runs the block one time 10/7/2024 39  PL/SQL Main Features Blocks The block is not stored in the database It is called an anonymous block (even if it has a label) An anonymous block is compiled each time it is loaded into memory An anonymous block compilation has three stages: 1. Syntax checking: PL/SQL syntax is checked, and a parse tree is generated. 2. Semantic checking: Type checking and further processing on the parse tree. 3. Code generation 10/7/2024 40  Examples 10/7/2024 41  PL/SQL Main Features Variables and Constants In the DECLARATION part of a PL/SQL block there can be declared Variables Constants They can be use them wherever you can use an expression As the program runs The values of variables can change The values of constants cannot 10/7/2024 42  PL/SQL Main Features Variables and Constants Example DECLARE A NUMBER(5,2); PI CONSTANT NUMBER(3,2) := 3.14; BEGIN A := PI * &r ** 2; END; / 10/7/2024 43  PL/SQL Main Features Error Handling PL/SQL supports error handlers to efficiently detect errors When an error occurs: An exception is raised Normal execution stops The control transfers to the exception-handling part of the PL/SQL block. 10/7/2024 44  PL/SQL Main Features Subprograms A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly If the subprogram has parameters, their values can differ for each invocation PL/SQL has two types of subprograms 1. Procedures – does not return a result 2. Functions – returns a result PL/SQL also lets you invoke external programs written in other languages 10/7/2024 45  PL/SQL Main Features Packages A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly A package is a schema object that groups logically related PL/SQL Types Variables Constants Subprograms Cursors Exceptions 10/7/2024 46  PL/SQL Main Features Packages A package is compiled and stored in the database Applications can share package Developers can write their own packages Oracle provides many product-specific packages 10/7/2024 47  PL/SQL Main Features Triggers A trigger Is a named PL/SQL unit Is stored in the database Runs in response to an event that occurs in the database 10/7/2024 48  PL/SQL Main Features Triggers A developer can specify The event to be handled by the trigger Whether the trigger fires Before the event After the event Whether the trigger runs for each: Event Row affected by the event 10/7/2024 49  PL/SQL Main Features Input and Output Most PL/SQL input and output (I/O) is done with SQL statements These statements Store data in database tables Query those tables PL/SQL I/O is also done with PL/SQL packages that Oracle Database supplies 10/7/2024 50  PL/SQL Main Features Input and Output 10/7/2024 51  PL/SQL Main Features Data Abstraction A developer can specify their own data types Data structures can be used to achieve data abstraction Developers can use: Cursors Composite Variables The %ROWTYPE Attribute The %TYPE Attribute Abstract Data Types 10/7/2024 52  PL/SQL Main Features Data Abstraction A cursor Is a pointer to a private SQL area that stores information about processing a specific SQL statement PL/SQL SELECT INTO statement Is used to retrieve the rows of the result set one at a time. Cursor attributes can be used to get information about the state of the cursor (e.g., how many rows the statement has affected so far) 10/7/2024 53  PL/SQL Main Features Data Abstraction A composite variable has internal components An internal component can be accessed individually Entire composite variables can be passed to subprograms as parameters 10/7/2024 54  PL/SQL Main Features Data Abstraction PL/SQL has two kinds of composite variables: Collections The internal components (elements) are always of the same data type Each element is accessed by its unique index. E.g., lists and arrays Records The internal components (fields) can be of different data types Each field can be accessed by its name A record variable can hold A table row Some columns from a table row. 10/7/2024 55  PL/SQL Main Features Data Abstraction The %ROWTYPE attribute Is used to declare a record that represents either A full row of a database table or view Partial row of a database table or view The %TYPE attribute Is used to declare a data item of the same data type as a previously declared variable or column 10/7/2024 56  Examples 10/7/2024 57  Examples 10/7/2024 58  Examples 10/7/2024 59  Examples 10/7/2024 60  PL/SQL Main Features Data Abstraction An Abstract Data Type (ADT) consists of a data structure and subprograms that manipulate the data The variables that form the data structure are called attributes The subprograms that manipulate the attributes are called methods ADTs are stored in the database Instances of ADTs can be Stored in tables Used as PL/SQL variables. 10/7/2024 61  PL/SQL Main Features Data Abstraction ADTs reduce complexity by separating a large system into logical reusable components ADTs are created using CREATE TYPE Statement. ADTs are also called user-defined types and object types 10/7/2024 62  PL/SQL Main Features Conditional Compilation Conditional compilation enables developers to customize the functionality in a PL/SQL application without removing source text For example: Use new features with the latest database release Disable new features when running the application in an older database release Activate debugging or tracing statements in the development environment Hide debugging or tracing statements when running the application at a production site 10/7/2024 63  PL/SQL Main Features Processing a Query Result Set One Row at a Time Inside PL/SQL, the results of a SQL query can be processed one row a time This can be achieved by using: A basic loop An individual statements to Run the query Retrieve the results Finish processing 10/7/2024 64  Overview PL/SQL PL/SQL Main Features PL/SQL Engine 10/7/2024 65  PL/SQL Engine The PL/SQL compilation and runtime system is an engine The PL/SQL engine compiles and runs PL/SQL units The PL/SQL engine can be installed: In the database In an application development tool (e.g., Oracle Forms) Regardless of the environment, the PL/SQL engine accepts as input any valid PL/SQL unit 10/7/2024 66  PL/SQL Engine 10/7/2024 67  PL/SQL Engine The engine Runs procedural statements Sends SQL statements to the SQL engine in the database PL/SQL units are processed by: The database if the database PL/SQL engine is used The local PL/SQL engine if an application development tool is used If a PL/SQL unit contains no SQL statements The local engine processes the entire PL/SQL unit This is useful if the application development tool can benefit from conditional and iterative control 10/7/2024 68  PL/SQL Engine Why using PL/SQL Engine is useful: Oracle Forms applications frequently use SQL statements to Test the values of field entries Do simple computations By using PL/SQL instead of SQL Avoid calls to the database 10/7/2024 69  Examples 10/7/2024 70  Examples 10/7/2024 71  Bibliography Usha Krishnamurthy et al. Oracle® Database: SQL Language Reference 19c, Oracle Corporation, 2022 [pdf] Usha Krishnamurthy et al. Oracle® Database: SQL Language Reference 21c, Oracle Corporation, 2022 [pdf] Usha Krishnamurthy et al. Oracle® Database: SQL Language Reference 23ai. Oracle Corporation 2024 [pdf] Louise Morin et al. Oracle® Database: Database PL/SQL Language Reference 19c, Oracle Corporation, 2020 [pdf] Louise Morin et al. Oracle® Database: Database PL/SQL Language Reference 21c, Oracle Corporation, 2021 [pdf] Sarah Hirschfeld et al. Oracle® Database: Database PL/SQL Language Reference 23ai. Oracle Corporation 2024 [pdf] 10/7/2024 72 PL/SQL (III) Ș. L. Dr. In g. Ci pr i an -Oc tav i an Tr ui că c ip ri a n. tru i ca @up b.r o A s. Dr d. In g. A le x an dr u Pe tr e sc u A le x.pe tr e sc u@u pb. ro Overview - Composite data types Records Co llectio n Types: Asso ciative Arrays, VAR RAYS, Nested Tables Co llectio n Features  Composite data types A composi te data type sto res val ues t hat have in ternal co mpo nent s. PL/SQ L lets y ou d efine two kinds of co mpo site d ata typ es: Rec or d C ol lection A composi te vari able can be passed to su bpro gra ms as parameters and its in ternal co mpon en ts can b e accessed in dividua lly. Internal co mpo nents c an be either Sca lar or C omposite. RECORD Type  Records The internal components of a record can have different data types ​and are called fields.​ Each field of a record variable can be accessed by its name, e.g., variable_name.field_name. ​ Record variables can be: Copies of existing record variables o Created using the attribute %ROWTYPE or %TYPE o Inherits the only structure, not the values User defined o Define a RECORD type o Create a variable of that type  Records A RECORD type defined in a PL/SQL block is a local type available only in the block. It is s tored in the da ta base o nly if the blo ck is in a s ubprogra m o r pa ckag e. A RECORD type defined in a pa ckag e s pecifica tion is a public item and can be r efer enced fro m outsid e the pa ckag e by qualif ying it with the pac kage name (pac kage_name.type_name). It is s tored in the database until you drop the pa ckag e with the DROP PACKAGE statement. A RECORD type ca nnot be cr eated at s chem a level (a REC ORD type ca nnot be an ADT attribute data type).  Records To define a RECORD type, specify the record's na me and def ine its f ields. To def ine a f ield, specify the field's na me and a its data type. The in iti al value of a f ield is N UL L. The N OT NUL L co nst raint can be specifi ed fo r a field ( a non -NUL L in iti al val ue must also be speci fi ed ). A non-N UL L initi al value is o ptio nal wi tho ut the N OT NUL L co nst raint. A RECORD type defined in a packa ge s pec if icati on is in co mpat ible with an i dentical ly d efined local REC ORD type. A f ield declared wi th %RO WTYP E or %TYP E stil l has an in iti al N UL L value. ( the variable do es no t in herit the initi al val ue of the referenced i tem)  Records Records can not be tested nat ively for nu lli ty, equal ity, or in equali ty. These BO OLEAN ex pressions are il legal: My_Reco rd IS N ULL My_Reco rd_1 = My _Reco rd _2 My_Reco rd_1 > My _Reco rd _2  Records Record var iables a re allowed o nly in these places : On the right side o f the SET c la use in an UPDATE statement (Only 1 SET cla use is allowed in this cas e). T his c an be c ombined with the " ROW" keywor d on the left side. In the VALUES cla use of a n INSERT statement In the INTO subclause of a RETURN ING c la use Record var iables a re n ot a llow ed in a SELECT list, a WHERE cla use, a GROUP BY cla use or in an ORDER BY c la use. The "ROW" keywor d ca nnot be used with a subquery.  Records The following restriction s apply to record in serts and updates: If the VALUES cla use of a n INSERT statement co ntains a RECORD va riable, n o oth er va ria ble or va lue is allowed in the cla us e If the INTO subc lause of a RETURN ING c la use contains a RECORD va riable, no other va ria ble or va lue is allowed in the s ubc laus e Thes e a re n ot s up ported: Nes ted RECORD types Fun ctions that return a RECORD type Rec ord inser ts a nd updates using the EXECUTE IMMEDIATE statement (Dynamic SQL) PL/SQL engine cares about type compatibility so as long as our instructions leverage the proper types, all is good. Collection Types  Collection types The in ternal component s of a co ll ec tio n have the same d ata type and are called elements. El ements o f a co ll ec tio n variable can be accessed by its un ique i ndex, e. g., variable_name (in dex) C ol lections vari ables can be: C opi es of exi stin g co mpo site variables (created using t he at tribute %T YPE - Inherits the only structure, not the values) User def ined – d efine a co llectio n typ e and t hen create a variab le o f that ty pe  Collection types C ol lections have o ne dimensi on. A mult idimensi on al co llectio n can be mo deled b y using a coll ec tion whose elements are coll ec tions. Develop ers can create: A coll ec tion of records : a co ll ec tio n that has elements r ec or ds and accessing an item "variable_name (in dex).fi el d_name" A record that c ontains co ll ec tio ns: a record that has items co ll ec tio ns and accessing a f ield "variable_name. field_name (in dex) " A coll ec tion of coll ec tions: a coll ec tion that has i tems a coll ection and accessing an item " variable_name (in dex_1) (in dex_2) …(in dex_n) "  Collection types Collectio Number ADT n of Index Dense Uninitialized or Defined in Type Type Elements Type Sparse Status Attr? Associative String array Unspecified PLS_INTEGER Either Empty In PL/SQL block or No (or index-by (BINARY_INTEGER package table) ) Only VARRAY In PL/SQL block or defined (variable- Specified Integer Always dense Null package or at size array) at schema level schema level Only Nested Starts In PL/SQL block or defined table Unspecified Integer dense, can Null package or at become sparse at schema level schema level  Number of Elements, Index, Dense or Sparse Nu mber of Elements ? Sp ecified: the ma xim um number of elements in the collection Uns pecified : the ma xim um number of elements in the c ollec tio n is the u pper limit of the index type Dens e o r Sp arse? A den se collection has n o gaps between elemen ts, ever y element between the first and las t element is defin ed (meaning it ha s a value, the value can be NULL unless the element has a NOT N ULL c onst raint ). A s parse collection h as gap s between elements.  Uninitialized Status An empty co llectio n exi sts b ut has no elements. The EXTEND method must be invoked to add elements to an empty co llectio n. A nu ll co llectio n (also called an ato mical ly nul l co llectio n) do es no t exis t. The co llectio n must be i ni tialized to chang e it fro m a null co llectio n to an exi sting co llectio n. The EXTEND method cannot be used to i ni tialize a null collect ion. Initializ ation: Making it empty Assi gning a non-NUL L value to it  Defined in Sa me as for RECORD: if it's in a block, it is local / if it's in a program specific ation , it is pu blic. Can be stored as par t o f a s ubprogra m else it is gone with the s essio n. A collection type defin ed at s chem a level is a standalone type. It is s tored in the database until yo u drop it with the DROP TYPE Statement. Can be created with the CREATE [OR REPLACE] TYPE Can be modified using the ALTER TYPE A c ollec tion typ e defin ed in a p acka ge s pecif ication is incomp atible w ith a n identica lly defin ed loca l or s ta nda lon e c ollec tion typ e.  ADT Attribute Data Type To be an ADT( Abstract Data Type) At tribu te Data Type, a co llectio n type must be a sta ndalone co llectio n type. Transl ating No n -PL /SQL Comp osite Ty p es to P L/SQL Co mpo site Typ es: Non-PL/SQL Composite Type Equivalent PL/SQL Composite Type Hash table Associative array Unordered table Associative array Set Nested table Bag Nested table Array VARRAY Associative Arrays  Associative Arrays An ass oci ative array ( formerl y cal led P L/S QL ta ble or in dex-by table) is a set of key-val ue p airs where each key is a un ique i ndex. The in dex is used to locate t he assoc iated val ue with t he syntax " variab le_name (in dex) ". An ass oci ative array ( like a d atabase t able) is empty ( but no t null) unti l it i s po pula ted. It can ho ld an un specif ied number o f elements (accessib le wit ho ut knowi ng their p ositi ons). Unlike a d atab ase table, it do es no t need dis k space and can not be manipulat ed wit h DML statements.  Associative Arrays - Indexes The data ty pe o f in dex can be: A str ing type (VARC HAR2, VARC HAR, STRING, or LON G) P LS_I NTEGER/B INARY_INTEGER Indexes are stored in so r t or der, but are not cr eated in order. The sor t or der is d etermi ned b y the init ializati on p arameter N LS_S ORT and N LS_C OMP for st ri ng types. Natio nal Languag e Supp or t (N LS) parameters suc h as N LS_S ORT, N LS_C OMP, and N LS_DATE_FORMAT affect associati ve arrays indexed by str ing.  Associative Arrays An ass oci ative array is appr opr iate fo r: A rel ativel y s mal l lo okup tab le constructed in memo r y each time t he sub p ro gram is invoked or t he package that declares it i s initiali zed. To pass co ll ec tio ns to and fro m the data base server: Dec lare formal su bprogra m parameters of ass oci ative array types > Bind the host a rrays to the correspo nding actual parameters ( use O racle C all Interface ( OC I) or an Oracle Preco mpil er) > PL /SQL au tomati call y co nverts between ho st arrays and asso ciative arrays i ndexed by P LS_I NTEGER ( You can not bin d an assoc iative array in dexed b y VARC HAR)  Associative Arrays T he a ssoc ia tive a rra y ty pe c a nn ot be de c la red a t sc h em a leve l a s it is int end ed f o r te mp o rar y da t a stora ge. To ma ke a n a ssoc ia tive a rra y pe rsist en t de cla re it in a pa c k ag e spec if i c at ion o r po pu lat e it in the pa c k ag e body. To pa ss a n a ssoc ia tive a rra y va ria ble a s a pa ra me ter t o a s ta nda lo ne su bpr ogra m t he t ype o f th at va ria ble mu s t be d ecla re d in a pa c k ag e spec if i c at io n. Do ing s o ma ke s the ty pe a va ila ble t o t he in voked su bp rogra m ( w hich de cla re s a f or ma l p ara me te r o f t ha t ty pe) a nd th e in vokin g su bpr ogra m o r a no nymo u s b loc k ( w hich decla re s an d pa s s es th e va ria ble o f tha t t ype ). T he m ost ef f ic ie nt wa y t o p ass c olle c tion s t o a nd f ro m t he da t aba s e s e rv er is t o u s e a ssoc ia tive a rra ys w ith FORA LL s t at eme nt an d B ULK C OLLEC T cla us e. VARRARAYS  VARRARYS A var ray (var iabl e-si ze array) is an array who se nu mber of elements can vary from zero ( empty ) t o the decla red maxi mum size. An un ini tial ized varray variable is a nu ll co ll ec tion. A var ray variable must be in iti alized by either making it empty or assig ning a non-N UL L value to it. A var ray is appr opr iate when the maximum numb er o f need ed elements is kno w n and the elements are acc es sed sequenti ally. A var ray might be impracti cal for st oring many elements bec ause al l element s must be stored or retrieved at the same time.  VARRARYS - Index Use the syntax "variable_name(index)" to access an element of a varray variable, where t he lower bound of index is 1 and the upper bound is the current number of elements. The upper bound changes as you add or delet e elements but cannot exceed the maximum size. Nested Tables  Nested Tables A nested table i s a col le cti on t ype th at s tor es an unspecif ied number of r ows i n no particu lar o rder. When re tr ie vi ng a nested table valu e f rom the database PL/ SQL gi ves the r ows con secutive indexes s tar tin g at 1. Th e syntax is "vari ab le _n ame (index )". A nested table 's indexes and row order might not rema in stable when s torin g or retrieving the nested table to and fr om the da ta bas e. The amount of mem ory that a nested table var ia ble occupies can increase or dec rea se dyn amic ally as elements a re added or deleted. An u nin itia lized nested table var ia ble is a n ull collection. A n ested table is initialized by either mak ing it emp ty o r assigning a n on-NULL va lue to it.  Nested Tables Concept ually a nested tabl e is like a one-dimensional array with an arbitrary number of elements, however, there are differences: The size of a nested table can increase dynamically : an array has a declared (maxi mum) number of elements, a nested table does not A nested tabl e is dense i nitiall y but can become sparse as elements are deleted (at arbitrar y indexes), whereas the array remains dense  Nested Tables A nested tabl e is appropriate when: The (maximum)number of elements is not set Index values are not consecutive To delete or update some elements, but not all elements simul taneously Collection Features  Collection Constructors A collection constructor (constructor) is a system-defined function that has the same name as a collection type and returns a collection of that type.  Assigning Values to Collection Variables Constructor: Invoke a constructor to create a collect ion and assign values to the collection elements Assignment: To assign the value of another exist ing collection variable Subprogram: Pass the collection variable to a subprogram / Use the OUT or IN OUT parameter / Assign the value inside the subprogram A collecti on can be assigned to a collecti on variable if they have the same data type. Having the same element type is not enough for an assignment.  Assigning Values to Collection Variables To make a collection va riable n ull as sign either a NULL va lue o r a NULL collection of the sa me data type. The SQ L MULTISET opera to rs (MULTI SET EXCEPT, MULTISET INTERSECT, MULTISET UNION ) comb ine two n ested tables into a s ingle nested table. The elements of the two n ested table s must have comp ara ble data types. The res ult of the SQ L SET function invo cation takes a n ested table ar gument a nd returns a nested table of the same data type whos e elements are distinct. The functio n eliminates du plica te elements.  Collection Methods A collection method is a PL/SQL subprogram, either a function that returns information about a collection or a procedure that operates on a collection. Collect ion methods make collect ions easier t o use and applications easier to maintain. The syntax for a method invocation is "collection_name.method". Method Type Description DELETE Procedure Deletes elements from collection. TRIM Procedure Deletes elements from end of varray or nested table. EXTEND Procedure Adds elements to end of varray or nested table. EXISTS Function Returns TRUE if and only if specified element of varray or nested table exists. FIRST Function Returns first index in collection. LAST Function Returns last index in collection. COUNT Function Returns number of elements in collection. LIMIT Function Returns maximum number of elements that collection can have. PRIOR Function Returns index that precedes specified index. NEXT Function Returns index that succeeds specified index.  Collection Methods - DELETE DELETE is a procedure that r emo ves elements f ro m a collection. "DELETE" removes a ll elements fro m a co llection of an y typ e. This operation imm ediately frees the memor y alloc ated by the deleted elements. For an as socia tive a rray o r n ested table (but n ot a va rray): D ELETE( n) re moves t he e leme nt w ho se in de x is n if tha t e lem ent exis t s , o the rw is e it do es no t hing D ELETE( m,n ) re moves a ll e leme nt s w ho se in de xes a re in the ra ng e m..n if bo th m a nd n e xis t a nd m Greater than val2 > val1 = val2 operators Different than val1 val2 != Different than val1 != val2 ~= Different than val1 ~= val2 ^= Different than val1 ^= val2 OPERATOR OPERATION EXAMPLE Verifies if a val BETWEEN BETWEEN value is in a val1 AND val2 range IN Verifies if a val IN(val1, SQL value is in a list val2, …, val3) operators Verifies a LIKE specified patter val LIKE pattern n Verifies if a IS NULL val IS NULL value is NULL PRECEDENCE OPERATORS OPERATION 1 ** Exponential 2 +, - Identity, negation 3 *, / Multiplication, division Operators Addition, subtraction, co 4 +, -, || ncatenation =, , =, , precedence !=, ~=, ^=, IS Comparison and SQL 5 NULL, LIKE, operators BETWEEN, IN. 6 NOT Logical negation 7 AND Conjunction 8 OR Inclusion Symbol Meaning := Assignment operator => Association operator % Attribute indicator ' String delimiter. Component selector Other ( ) Expression or list delimiter : Host variable indicator , Item separator symbols > Label delimiter Multiline comment delimiter " Quoted identifier delimiter.. Range operator @ Remote access indicator -- Single-line comment indicator ; Statement terminator Control statements  Control Statements Conditional selection statements Loop statements Sequential control statements Co nd i tio na l s el ec ti on s tate men ts IF Statement The condition can contain​: Comparison operators​ Logical operators ​ SQL operators​  Co nd i tio na l s el ec ti on s tate men ts CASE Statement Simple CASE The selector is an expression (typically a single variable) Each selector_value can be either a literal or an expression (cannot use NULL) The simple CASE statement runs the first statements for which selector_value equals selector Remaining conditions are not evaluated If no selector_value equals selector the CASE statement runs else_statements if they exist Raises the predefined exception CASE_NOT_FOUND otherwise Searched CASE The searched CASE statement runs the first statements for which condition is true Remaining conditions are not evaluated  L o op Sta tem ents Basic LOOP Statement With each iteration of the loop: The statements are run Control returns to the top of the loop To prevent an infinite loop and exit the loop: Use a statement Raised exception  EXIT statements T he E X IT s tate me nt en ds the c urren t i terati o n o f a lo o p u nc o ndi tio n al ly. I t tr ans fer s c o ntr o l to th e en d o f e ithe r the c urren t l o op o r an en cl o si ng la bel ed l oo p. T he E X IT WHE N s tate me nt e nd s the c ur r ent ite ra tio n o f a l oo p w he n th e c o nd it io n i n i ts W HE N c la us e i s tru e. Th e c o nd it io n i n th e W HE N c la us e i s evalu ated ea ch ti m e c o ntr ol r ea c hes the E X IT WHE N s tate me nt. The E X IT WHE N s tate me nt d o es no thi ng i f t he c o nd i tio n i s no t tr ue.  CONTINUE statement T he C O NT I N UE s tate me nt c o nti nu es the c urren t i terati o n o f a lo o p u nc o ndi tio n al ly. I t tr ans fer s c o ntr o l t o the n ext itera tio n o f e ithe r the c urren t l o op o r an en cl o si ng la bel ed l oo p. T he C O NT I N UE W HE N s tate me nt c o nti nues th e c ur r ent it era tio n of a l o op wh en t he c o nd i tio n i n i ts W HE N c la us e i s tr ue. T he c o nd i tio n i n t he W HE N c la us e i s evalu ated eac h tim e co ntr o l r ea ch es the C O NTI N UE W HE N s tate me nt. The C O NTI N UE W HE N s tate me nt d o es no thi ng i f t he c o nd iti o n i s not tr ue.  Loop Stat ement s WHILE LOOP Statement The WHILE LOOP statement runs one or more statements while a condition is true. If the condition is true the statements are run, then control returns to the top of the loop where condition is evaluated again. The WHILE LOOP stops when the condition is false. A statement inside the loop must make the condition false or null to prevent an infinite loop. An EXIT, EXIT WHEN, CONTINUE, or CONTINUE WHEN in the statements can cause the loop or the current iteration of the loop to end early.  Loop Stat ement s FOR LOOP Statement The FOR LOOP statement runs one or more statements while the loop index is in a specified range. Without REVERSE: The value of index starts at lower_bound and increases by one with each iteration of the loop until it reaches upper_bound The statements never run if lower_bound is greater than upper_bound With REVERSE: The value of index starts at upper_bound and decreases by one with each iteration of the loop until it reaches lower_bound If upper_bound is less than lower_bound, then the statements never run An EXIT, EXIT WHEN, CONTINUE, or CONTINUE WHEN in the statements can cause the loop or the current iteration of the loop to end early.  Condit ional select ion st atements: GOTO The sequential control statement GOTO transfers control to a label unconditionally. Can be used to exit a LOOP. NULL statement only passes control to the next statement  Bibliography Usha Krishnamur thy et al. Oracle® Database: SQL Language Reference 19c , Oracle Corporation, 2022 [pdf] Usha Krishnamur thy et al. Oracle® Database: SQL Language Reference 21c , Oracle Corporation, 2022 [pdf] Louise Morin et al. Oracle® Database: Database PL/SQL Language Reference 19c, Oracle Corporat ion, 2020 [pdf] Louise Morin et al. Oracle® Database: Database PL/SQL Language Reference 21c, Oracle Corporat ion, 2021 [pdf]  Memes E: https://www.dailydot.com /unclick /lord -farquaad-e-meme / "What do the Number Mean" : COD Gandalf A Wizard Is Never Late : Lord of the Rings TLDR : https://www.seoreseller.com /wp -content /uploads /2014/01/tldr- cat.jpg Anya Pointer : https://www.instagram.com /programmer.meme /p /CysHW - 4LgLL/ Perhaps: https://en.meming.world /wiki /Perhaps Unlimited Power: Star Wars I made this : https://knowyourmeme.com /memes /i -made-this  University Politehnica of Bucharest PL/SQL (IV) Ș.L. Dr. Ing. Ciprian-Octavian Truică [email protected]  Overview PL/SQL Error Handling Cursors 10/15/2024 2  Overview PL/SQL Error Handling Cursors 10/15/2024 3  PL/SQL Error Handling Exceptions (PL/SQL runtime errors) can arise from Design faults Coding mistakes Hardware failures Many other sources A developer Cannot anticipate all possible exceptions Can write exception handlers that lets the program continue to operate in their presence Any PL/SQL block can have an exception-handling part: EXCEPTION The exception-handling part can have one or more exception handlers 10/15/2024 4  PL/SQL Error Propagation 10/15/2024 5  PL/SQL Error Propagation 10/15/2024 6  PL/SQL Error Propagation 10/15/2024 7  PL/SQL Error Propagation When an exception is raised in the executable part of the block The executable part stops Control transfers to the exception-handling part If there is no enclosing block: If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation. If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus) If there is an enclosing block: Control transfers to the next statement of the enclosing block after an exception handler runs The exception propagates if an exception is raised in a block that has no exception handler The exception reproduces itself in successive enclosing blocks until a block has a handler for it or there is no enclosing block If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome 10/15/2024 8  PL/SQL Error Handling Exception categories: 1. Internally defined The runtime system raises internally defined exceptions implicitly (automatically). Always has an error code Does not have a name unless PL/SQL gives it one or you give it one E.g. ORA-00060 (deadlock detected while waiting for resource) ORA-27102 (out of memory) 10/15/2024 9  PL/SQL Error Handling The exception categories are: 2. Predefined A predefined exception is an internally defined exception that PL/SQL has given a name. E.g.: ORA-06500 (PL/SQL: storage error) has the predefined name STORAGE_ERROR 3. User-defined Developers can declare their own exceptions in the declarative part of any PL/SQL Anonymous block Subprogram Package User-defined exceptions are raised explicitly E.g.: Declare an exception named insufficient_funds to flag overdrawn bank accounts 10/15/2024 10  PL/SQL Error Handling Has Error Raised Raised Category Definer Code Has Name Implicitly Explicitly Internally Runtime Always Only if you Yes Optionally defined system assign one Predefined Runtime Always Always Yes Optionally system User-defined User Only if you Always No Always assign one SQLCODE – function to get the error code SQLERRM – function to get the error message 10/15/2024 11  PL/SQL Error Handling Internally defined exception Internally defined exceptions (ORA-n errors) Are described in Oracle Database Error Messages Reference The runtime system raises them implicitly (automatically). Does not have a name unless either PL/SQL gives it one or a developer gives it one Can be handled only with OTHERS exception handlers. To give a name to an internally defined exception, do the following in the declarative part of the appropriate anonymous block, subprogram, or package: Declare the name Associate the name with the error code of the internally defined exception 10/15/2024 12  PL/SQL Error Handling Internally defined exception example INSERT INTO employees(first_name, last_name) VALUES('Ion', 'Ionescu'); 10/15/2024 13  PL/SQL Error Handling Internally defined exception example 10/15/2024 14  PL/SQL Error Handling Pragma A pragma is an instruction to the compiler that is processed at compilation time A pragma begins with the reserved word PRAGMA followed by the name of the pragma Some pragmas have arguments A pragma may appear before a declaration or a statement Additional restrictions may apply for specific pragmas The extent of a pragma’s effect depends on the pragma A pragma whose name or argument is not recognized by the compiler has no effect. 10/15/2024 15  PL/SQL Error Handling Predefined exceptions Predefined exceptions are internally defined exceptions that have predefined names PL/SQL declares the names of the predefined exceptions globally in the package STANDARD The runtime system raises predefined exceptions implicitly (automatically) Developers can write exception handlers specifically for predefined exceptions because they have names To explicitly raise an exception use RAISE 10/15/2024 16  PL/SQL Error Handling Predefined exceptions example 10/15/2024 17  PL/SQL Error Handling User-defined exceptions Developers can declare their own exceptions in the declarative part of any PL/SQL anonymous block, Subprogram, or Package An exception name declaration has this syntax: exception_name exception; Developers can attach to a user defined exception An exception code (-20999 și -20000) using PRAGMA exception_init(exception_name, exception_code); An exception message using the procedure RAISE_APPLICATION_ERROR(exception_code, exception_message [, TRUE | FALSE]); TRUE – PL/SQL puts exception_code on top of the error stack FALSE (default) – PL/SQL replaces the error stack with exception_code (i.e., all previous errors are replaced with the current one) 10/15/2024 18  PL/SQL Error Handling User-defined exceptions Example 10/15/2024 19  Overview PL/SQL Error Handling Cursors 10/15/2024 20  PL/SQL Static SQL Static SQL is a PL/SQL feature that allows SQL syntax to be used directly in a block PL/SQL static SQL statements (some have a modified syntax): SELECT INSERT UPDATE DELETE MERGE COMMIT ROLLBACK SAVEPOINT SET TRANSACTION LOCK TABLE 10/15/2024 21  PL/SQL Static SQL A PL/SQL static SQL statement can have a PL/SQL identifier (names given to a PL/SQL object) wherever its SQL counterpart can have a placeholder for a bind variable The PL/SQL identifier must identify either a variable or a formal parameter Bind variables Are used to reuse the query parse tree and execution plan when new values are given Can be used in dynamic PL/SQL code, but that's not good practice! Are a placeholder for values within the query. Unlike substitution variables, these are not prompted for when you come to compile the code. 10/15/2024 22  PL/SQL Dynamic SQL 10/15/2024 23  PL/SQL Static SQL 10/15/2024 24  PL/SQL Static SQL The values of some variables are undefined after PL/SQL code runs a DML statement: After a FETCH or SELECT statement raises an exception The values of the define variables after that statement are undefined. After a DML statement that affects zero rows The values of the OUT bind variables are undefined unless the DML statement is a BULK or multiple-row operation 10/15/2024 25  PL/SQL Static SQL Static SQL includes these SQL pseudocolumns (for different types of objects): CURRVAL and NEXTVAL LEVEL Hierarchical Query Pseudocolumns (SELECT queries with START WITH... CONNECT BY ) Returns 1 for a root row, 2 for a child of a root, and so on. OBJECT_VALUE Returns system-generated names for the columns of an object table, XMLType table, object view, or XMLType view Useful for Identifying the value of a substitutable row in an object table Creating object views with the WITH OBJECT IDENTIFIER clause. ROWID - returns the physical address of the row ROWNUM - returns a number indicating the order in which Oracle selects the row from a table or set of joined rows for each row returned by a query 10/15/2024 26  PL/SQL Static SQL To access the values of a sequence in SQL statements: CURRVAL pseudocolumn Returns the current value of the sequence NEXTVAL pseudocolumn Increments the sequence Returns the new value Use dot notation to reference these pseudocolumns : sequence_name.CURRVAL sequence_name.NEXTVAL Each time sequence_name.NEXTVAL is reference The sequence is incremented immediately and permanently Commit or roll back on the transaction will not decrement the sequence 10/15/2024 27  PL/SQL Static SQL Use sequence_name.CURRVAL and sequence_name.NEXTVAL in a PL/SQL expression wherever NUMBER expression can be used. Note: Using sequence_name.CURRVAL or sequence_name.NEXTVAL to provide a default value for an ADT method parameter causes a compilation error PL/SQL evaluates every occurrence of sequence_name.CURRVAL and sequence_name.NEXTVAL SQL evaluates a sequence expression for every row in which it appears. 10/15/2024 28  Cursors A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement A session cursor lives in session memory until the session ends When the session ends the cursor ceases to exist Cursor types: Implicit Constructed and managed by PL/SQL Explicit Construct and manage by the developer 10/15/2024 29  Cursors Cursor attributes Are used to get information about any session cursor Can be referenced in procedural statements, but not in SQL statements Query the dynamic performance view V$OPEN_CURSOR to list the session cursors that each user session currently has opened and parsed The number of cursors that a session can have open simultaneously is determined by: The amount of memory available to the session The value of the initialization parameter OPEN_CURSORS 10/15/2024 30  Cursors Generally, PL/SQL parses An explicit cursor only the first time the session opens it A SQL statement (creating an implicit cursor) only the first time the statement runs All parsed SQL statements are cached A SQL statement is reparsed only if it is aged out of the cache by a new SQL statement PL/SQL does not reparse the associated query if an explicit cursor is closed and immediately reopened 10/15/2024 31  Implicit Cursors An implicit cursor is a session cursor that is constructed and managed by PL/SQL PL/SQL opens an implicit cursor every time SELECT or DML statement is executed A developer: Cannot control an implicit cursor Can get information from its attributes. 10/15/2024 32  Implicit Cursors The syntax of an implicit cursor attribute value is SQL%attribute An implicit cursor is also called a SQL cursor SQL%attribute always refers to the most recently run SELECT or DML statement If no such statement has run, the value of SQL%attribute is NULL An implicit cursor closes after its associated statement runs Its attribute values remain available until another SELECT or DML statement runs 10/15/2024 33  Implicit Cursors The most recently run SELECT or DML statement might be in a different scope: To save an attribute value for later use, assign it to a local variable immediately Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before testing it The implicit cursor attributes are: SQL%ISOPEN Attribute: Is the Cursor Open? SQL%FOUND Attribute: Were Any Rows Affected? SQL%NOTFOUND Attribute: Were No Rows Affected? SQL%ROWCOUNT Attribute: How Many Rows Were Affected? SQL%BULK_ROWCOUNT SQL%BULK_EXCEPTIONS 10/15/2024 34  Implicit Cursors SQL%ISOPEN always returns FALSE An implicit cursor always closes after its associated statement runs SQL%FOUND returns: NULL if no SELECT or DML statement has run TRUE if a SELECT statement returned one or more rows or a DML statement affected one or more rows FALSE otherwise SQL%NOTFOUND returns: NULL if no SELECT or DML statement has run FALSE if a SELECT statement returned one or more rows or a DML statement affected one or more rows TRUE otherwise 10/15/2024 35  Implicit Cursors The SQL%NOTFOUND attribute is not useful with the PL/SQL SELECT INTO statement: If the SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, before you can check SQL%NOTFOUND. A SELECT INTO statement that invokes a SQL aggregate function always returns a value (possibly NULL), thus the SQL%NOTFOUND attribute is always FALSE, so checking it is unnecessary. 10/15/2024 36  Implicit Cursors SQL%ROWCOUNT returns: NULL if no SELECT or DML statement has run Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement (an INTEGER) If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1 This is not the actual number of rows that satisfy the query The value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction The value of SQL%ROWCOUNT is not restored To the value it had before the savepoint when a transaction rolls back to a savepoint To the original value in the parent transaction when an autonomous transaction ends 10/15/2024 37  Implicit Cursors To allow a FORALL statement to continue even if some of its DML statements fail, include the SAVE EXCEPTIONS clause Use SQL%BULK_EXCEPTIONS in the FORALL clause to find the element that caused an error Use SQL%BULK_ROWCOUNT to get the total number of rows affected by the FORALL statement 10/15/2024 38  Implicit Cursors 10/15/2024 39  Explicit Cursors An explicit cursor is a session cursor that the developer constructs and manages The developer Declares and defines an explicit cursor Gives the cursor name associating a query with it (typically, the query returns multiple rows). Can process the query result set in either of these ways: Using OPEN-FETCH-CLOSE Open the explicit cursor (with the OPEN statement) Fetch rows from the result set (with the FETCH statement) Close the explicit cursor (with the CLOSE statement) Use the explicit cursor in a cursor FOR LOOP statement 10/15/2024 40  Explicit Cursors The developer cannot Assign a value to an explicit cursor Use a cursor in an expression Use a cursor as a formal subprogram parameter or a host variable A developer can reference an explicit cursor or cursor variable by its name unlike an implicit cursor An explicit cursor or cursor variable is called a named cursor 10/15/2024 41  Declaring and Defining Explicit Cursors The developer can either Declare an explicit cursor first and then define it later in the same block, subprogram, or package, Declare and define it at the same time. An explicit cursor declaration, which only declares a cursor, has this syntax: CURSOR cursor_name [ parameter_list ] RETURN return_type; An explicit cursor definition has this syntax: CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] IS select_statement; 10/15/2024 42  Opening an Explicit Cursors After declaring and defining an explicit cursor, the developer can open it using the OPEN statement The OPEN statement does the following: 1. Allocates database resources to process the query 2. Processes the query: Identifies the result set If the query references variables or cursor parameters, their values affect the result set If the query has a FOR UPDATE clause, locks the rows of the result set 3. Positions the cursor before the first row of the result set 10/15/2024 43  Fetching Data with Explicit Cursors After opening an explicit cursor, the developer can fetch the rows of the query result set with the FETCH statement The basic syntax of a FETCH statement that returns one row is: FETCH cursor_name INTO into_clause; The into_clause is either a list of variables or a single record variable The variable list or record must have a corresponding type- compatible variable or field for each column that the query returns The %TYPE and %ROWTYPE attributes are useful for declaring variables and records for use in FETCH statements 10/15/2024 44  Fetching Data with Explicit Cursors The FETCH statement Retrieves the current row of the result set Stores the column values of that row into the variables or record Advances the cursor to the next row The developer can Use the FETCH statement inside a LOOP statement Exit the LOOP when the FETCH statement runs out of rows (use %NOTFOUND) PL/SQL does not raise an exception when a FETCH statement returns no rows 10/15/2024 45  Closing an Explicit Cursors The developer can close an open explicit cursor with the CLOSE statement The CLOSE statement allows the allocated resources for the cursor to be reused. After closing a cursor, the developer cannot Fetch records from the cursor's result set Reference the cursor's attributes. PL/SQL raises the predefined exception INVALID_CURSOR otherwise The developer can reopen a closed cursor The explicit cursor must be closed before trying to reopen it. PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN otherwise 10/15/2024 46  Explicit Cursors %ISOPEN and %ROWCOUNT Attributes %ISOPEN returns TRUE if its explicit cursor is open FALSE otherwise. %ISOPEN is useful for checking that an explicit cursor is not already open before you try to open it If a developer tries to open an explicit cursor that is already open PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN %ROWCOUNT returns: Zero after the explicit cursor is opened but before the first fetch Otherwise, the number of rows fetched (an INTEGER) 10/15/2024 47  Explicit Cursors %FOUND and %NOTFOUND Attributes %FOUND returns: NULL after the explicit cursor is opened but before the first fetch TRUE if the most recent fetch from the explicit cursor returned a row FALSE otherwise %FOUND is useful for determining whether there is a fetched row to process. %NOTFOUND returns: NULL after the explicit cursor is opened but before the first fetch FALSE if the most recent fetch from the explicit cursor returned a row TRUE otherwise %NOTFOUND is useful for exiting a loop when FETCH fails to return a row 10/15/2024 48  Explicit Cursors Example 10/15/2024 49  Variables in Explicit Cursors Queries An explicit cursor query can reference any variable in its scope When opening an explicit cursor PL/SQL evaluates any variables in the query Uses those values when identifying the result set Changing the values of the variables later does not change the result set 10/15/2024 50  Explicit Cursors Example 10/15/2024 51  Explicit Cursors Parameters Developers can Create an explicit cursor that has formal parameters Pass different actual parameters to the cursor each time you open it A developer can use a formal cursor parameter anywhere a constant can be used in the cursor query A developer cannot reference formal cursor parameters outside the cursor query Use different names for formal and actual cursor parameters to avoid confusion A developer can specify a default value for formal parameters of a cursor 10/15/2024 52  Explicit Cursors Parameters Example 10/15/2024 53  Cursors FOR LOOP Statement The cursor FOR LOOP statement lets developers run a SELECT statement and then immediately loop through the rows of the result set This statement can use either an implicit or explicit cursor (but not a cursor variable) If only a SELECT statement is specified inside the FOR LOOP statement This form of the cursor FOR LOOP statement uses an implicit cursor Is called an implicit cursor FOR LOOP statement The implicit cursor is internal to the statement The statement cannot be referenced with the name SQL If an explicit cursor is specified inside the FOR LOOP statement Is called an explicit cursor FOR LOOP statement 10/15/2024 54  Cursors FOR LOOP Statement The cursor FOR LOOP statement implicitly declares its loop index The loop index is %ROWTYPE record variable of the type that the cursor returns This index record is local to the loop and exists only during loop execution. Statements inside the loop can reference the record and its fields They can reference virtual columns only by aliases 10/15/2024 55  Cursors FOR LOOP Statement After declaring the loop index record variable, the FOR LOOP statement Opens the specified cursor Fetches a row from the result set and stores it in the record with each iteration of the loop Closes the cursor when there are no more rows to fetch The cursor also closes if a statement inside the loop Transfers control outside the loop (i.e., using GOTO) PL/SQL raises an exception 10/15/2024 56  Cursors FOR LOOP Statement Example 10/15/2024 57  Cursor Variables A cursor variable is like an explicit cursor, except that: It is not limited to one query It can be used to open a cursor variable for a query, process the result set, and then use the cursor variable for another query. A value can be assigned to it It can be used in an expression It can be a subprogram parameter It can be used to pass query result sets between subprograms It can be a host variable It can be used to pass query result sets between PL/SQL stored subprograms and their clients It cannot accept parameters Whole queries can be passed to it (the queries can include variables) 10/15/2024 58  Cursor Variables A cursor variable offers flexibility because it is a pointer Its value is the address of an item, not the item itself Before a cursor variable can be referenced it must point to a SQL work area By opening the SQL work area By assigning it the value of an opened PL/SQL cursor variable Host cursor variable Cursor variables and explicit cursors are not interchangeable, one cannot be used where the other is expected E.g., a cursor variable cannot be used in a cursor FOR LOOP statement. 10/15/2024 59  Cursor Variable To create a cursor variable Declare a variable of the predefined type SYS_REFCURSOR Define a REF CURSOR type and then declare a variable of that type. Informally, a cursor variable is sometimes called a REF CURSOR The basic syntax of a REF CURSOR type definition is: TYPE type_name IS REF CURSOR [ RETURN return_type ] The REF CURSOR type and cursor variables of that type are strong if return_type is specified, otherwise, they are weak. SYS_REFCURSOR and cursor variables of that type are weak 10/15/2024 60  Cursor Variable Only queries that return the specified type can be associated with a strong cursor variable Any query can be associated with a weak cursor variable Weak cursor variables are more error-prone than strong ones, but they are also more flexible. Weak REF CURSOR types are interchangeable with each other and with the predefined type SYS_REFCURSOR. The value of a weak cursor variable can be assigned to any other weak cursor variable The value of a strong cursor variable can be assigned to another strong cursor variable only if both cursor variables have the same type (not merely the same return type). 10/15/2024 61  Assigning Values to a Cursor Variable PL/SQL cursor variable can be initialized with Another PL/SQL cursor variable A host cursor variable The syntax is: target_cursor_variable := source_cursor_variable; If source_cursor_variable is open, then after the assignment, target_cursor_variable is also open The two cursor variables point to the same SQL work area If source_cursor_variable is not open, opening target_cursor_variable after the assignment does not open source_cursor_variable 10/15/2024 62  Variables in a Cursor Variable The query associated with a cursor variable can reference any variable in its scope When a cursor variable is opened with the OPEN FOR statement, PL/SQL Evaluates any variables in the query Uses those values when identifying the result set Changing the values of the variables later does not change the result set 10/15/2024 63  Opening a Cursor Variable A cursor variable can be opened with the OPEN FOR statement 1. Associates the cursor variable with a query (typically, the query returns multiple rows) The query can include placeholders for bind variables The bind variable values can be specified in the USING clause of the OPEN FOR statement 2. Allocates database resources to process the query 3. Processes the query: Identifies the result set If the query references variables, their values affect the result set If the query has a FOR UPDATE clause, locks the rows of the result set 4. Positions the cursor before the first row of the result set 10/15/2024 64  Fetching Data with a Cursor Variable After opening a cursor variable the rows of the query result set can be fetched with the FETCH statement The return type of the cursor variable must be compatible with the INTO clause of the FETCH statement PL/SQL catches incompatibility At compilation time if the cursor variable is strong At run time if the cursor variable is weak The predefined exception ROWTYPE_MISMATCH is raised before the first fetch in case of incompatibility for a weak cursor variable 10/15/2024 65  Closing a Cursor Variable A cursor variable needs to be closed before reopening it (that is, using it in another OPEN FOR statement) The query previously associated with a cursor variable is lost after it is reopened A cursor variable is closed with the CLOSE statement Using the CLOSE statement allows the resources associated with the cursor variable to be reused After closing a cursor variable Its records cannot be fetched Its result set cannot be accessed Its attributes cannot be referenced Trying any of these raises the predefined exception INVALID_CURSOR 10/15/2024 66  Cursor Variable Example 10/15/2024 67  Cursor Expressions A CURSOR expression returns a nested cursor It has this syntax: CURSOR ( subquery ) CURSOR expression can be Used in a SELECT statement that is not a subquery Passed to a function that accepts a cursor variable parameter A cursor expression cannot be used with an implicit cursor 10/15/2024 68  Cursor Expressions Example 10/15/2024 69  FOR UPDATE The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them SELECT FOR UPDATE Locks rows for update It ensures that no other user can change those values before an update By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement. 10/15/2024 70  FOR UPDATE 10/15/2024 71  FOR UPDATE When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause Is a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE Restricts the statement to the current row of the cursor When SELECT FOR UPDATE queries multiple tables, it locks only rows whose columns appear in the FOR UPDATE clause. 10/15/2024 72  Bibliography Usha Krishnamurthy et al. Oracle® Database: SQL Language Reference 19c, Oracle Corporation, 2022 [pdf] Usha Krishnamurthy et al. Oracle® Database: SQL Language Reference 21c, Oracle Corporation, 2022 [pdf] Louise Morin et al. Oracle® Database: Database PL/SQL Language Reference 19c, Oracle Corporation, 2020 [pdf] Louise Morin et al. Oracle® Database: Database PL/SQL Language Reference 21c, Oracle Corporation, 2021 [pdf] 10/15/2024 73  University Politehnica of Bucharest PL/SQL (V) Ș.L. Dr. Ing. Ciprian-Octavian Truică [email protected]  Overview Subprograms 10/28/2024 2  Subprograms A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly If the subprogram has parameters, their values can differ for each invocation A subprogram is either a Procedure used to perform an action Function used to compute and return a value Subprograms support the development and maintenance of reliable and reusable code 10/28/2024 3  Subprograms Features Modularity Subprograms let you break a program into manageable, well- defined modules Easier Application Design Implementation details of the subprograms can be deferred until the main program is tested The programs can be refined one step at a time Easier to test modules independently Maintainability The implementation details of a subprogram can be changed without changing its invokers Packageability Subprograms can be grouped into packages 10/28/2024 4  Subprograms Features Reusability Any number of applications, in many different environments, can use the same package subprogram or standalone subprogram Better Performance Each subprogram is compiled and stored in executable form, which can be invoked repeatedly Because stored subprograms run in the database server, a single invocation over the network can start a large job This division of work Reduces network traffic Improves response times Stored subprograms are cached and shared among users Lowers memory requirements Lowers invocation overhead 10/28/2024 5  Subprograms Types Subprogram can be Nested subprograms: Created inside the WITH clause Created inside a PL/SQL block (which can be another subprogram) Package subprograms: created inside a package Standalone subprograms: are created at schema level 10/28/2024 6  Nested Subprograms A subprogram created inside a PL/SQL block or WITH clause is a nested subprogram In the WITH clause Declare functions that will be used in the SQL statement Declare procedures that can only be used in subprograms Nested subprograms can be Declared and defined at the same time First declared and then defined in the same block A nested subprogram is stored in the database only if it is nested in A standalone subprogram Package subprogram 10/28/2024 7  Nested Subprograms 10/28/2024 8  Nested Subprograms 10/28/2024 9  Package Subprograms A subprogram created inside a package is a package subprogram Developers can Declare it in the package specification Define it in the package body It is stored in the database until the package is dropped 10/28/2024 10  Standalone Subprograms A subprogram created at schema level is a standalone subprogram A developer can create it with the statements: CREATE [OR REPLACE] FUNCTION function_name CREATE [OR REPLACE] PROCEDURE procedure_name It is stored in the database until it is dropped with the statements: DROP FUNCTION function_name; DROP PROCEDURE procedure_name; 10/28/2024 11  Stored Subprograms A stored subprogram is either A package subprogram A standalone subprogram A stored subprogram is affected by the AUTHID and ACCESSIBLE BY clauses These clauses can appear in the CREATE FUNCTION, CREATE PROCEDURE, and CREATE PACKAGE statements The AUTHID clause affects the name resolution and privilege checking of SQL statements that the subprogram issues at run time The ACCESSIBLE BY clause specifies a white list of PL/SQL units that can access the subprogram 10/28/2024 12  Subprogram Invocations A subprogram invocation has this form: subprogram_name [ ( [ parameter [, parameter]... ] ) ] If the subprogram has no parameters, or specifies a default value for every parameter Omit the parameter list Specify an empty parameter list A procedure invocation is a PL/SQL statement: procedure_name(val_parameter_1, val_parameter_2,...); A function invocation is an expression: value := function_name(val_parameter_1, val_parameter_2,...); IF function_name(val_parameter_1, val_parameter_2,...) THEN... 10/28/2024 13  Subprogram Properties Each subprogram property (e.g., AUTHID, ACCESSIBLE BY, etc.) can appear only once in the subprogram declaration The properties can appear in any order Properties appear before the IS or AS keyword in the subprogram heading The properties cannot appear in nested subprograms Only the ACCESSIBLE BY property can appear in package subprograms 10/28/2024 14  Subprogram Parts A subprogram begins with a subprogram heading, which specifies The subprogram name The subprogram parameter list (optional) Like an anonymous block, a subprogram has these parts: Declarative part (optional) This part declares and defines local types, cursors, constants, variables, exceptions, and nested subprograms These items cease to exist when the subprogram completes execution This part can also specify pragmas The declarative part of a subprogram does not begin with the keyword DECLARE, as the declarative part of an anonymous block does Executable part (required) This part contains one or more statements that assign values, control execution, and manipulate data Exception-handling part (optional) This part contains code that handles runtime errors 10/28/2024 15  Additional Subprogram Parts For Functions A function has the same structure as a procedure, except that: 1. A function heading must include a RETURN clause The RETURN clause specifies the data type of the value that the function returns A procedure heading cannot have a RETURN clause 2. In the executable part of a function, every execution path must lead to a RETURN statement The PL/SQL compiler issues a compile-time warning otherwise The RETURN statement is optional and not recommended in a procedure 10/28/2024 16  Additional Subprogram Parts For Functions A function declaration can include these options: Option Description DETERMINISTIC option Helps the optimizer avoid redundant function invocations. PARALLEL_ENABLE option Enables the function for parallel execution, making it safe for use in slave sessions of parallel DML evaluations. PIPELINED option Makes a table function pipelined, for use as a row source. RESULT_CACHE option Stores function results in the PL/SQL function result cache. 10/28/2024 17  Additional Subprogram Parts For Functions 10/28/2024 18  Additional Subprogram Parts For Functions 10/28/2024 19  Subprogram Parts Return RETURN Statement The RETURN statement immediately ends the execution of the subprogram or anonymous block that contains it A subprogram or anonymous block can contain multiple RETURN statements RETURN Statement in Function Every execution path must lead to a RETURN statement and every RETURN statement must specify an expression The RETURN statement assigns the value of the expression to the function identifier and returns control to the invoker, where execution resumes immediately after the invocation In a pipelined table function, a RETURN statement need not specify an expression 10/28/2024 20  Subprogram Parts Return 10/28/2024 21  Subprogram Parts Return RETURN Statement in Procedure The RETURN statement returns control to the invoker, where execution resumes immediately after the invocation The RETURN statement cannot specify an expression RETURN Statement in Anonymous Block The RETURN statement exits its own block and all enclosing blocks The RETURN statement cannot specify an expression 10/28/2024 22  Subprogram Parts Return 10/28/2024 23  Subprogram Parts Return 10/28/2024 24  Forward Declarations If nested subprograms in the same PL/SQL block invoke each other, then one requires a forward declaration, because a subprogram must be declared before it can be invoked. A forward declaration declares a nested subprogram but does not define it A developer must define it later in the same block The forward declaration and the definition must have the same subprogram heading 10/28/2024 25  Forward Declarations 10/28/2024 26  Subprogram Parameters If a subprogram has parameters, their values can differ for each invocation Formal parameters must be declared in the subprogram heading to have subprogram with parameters In each formal parameter declaration specify Mandatory: the name and data type of the parameter Optional: the mode and default value Reference the formal parameters by their names in the execution part of the subprogram 10/28/2024 27  Subprogram Parameters When invoking the subprogram, specify the actual parameters whose values are to be assigned to the formal parameters Corresponding actual and formal parameters must have compatible data types To avoid confusion, use different names for formal and actual parameters Actual parameters (including default values of formal parameters) can be evaluated in any order LOB parameters cannot be used in a server-to-server remote procedure call (RPC) 10/28/2024 28  Subprogram Parameters 10/28/2024 29  Subprogram Parameters If the data type of a formal parameter is a constrained subtype, then: If the subtype has the NOT NULL constraint, then the actual parameter inherits it If the subtype has the base type VARCHAR2, then the actual parameter does not inherit the size of the subtype If the subtype has a numeric base type, then the actual parameter inherits the range of the subtype, but not the precision or scale In a function The clause RETURN datatype declares a hidden formal parameter The statement RETURN value specifies the corresponding actual parameter The same rul