Collection Types. This PLSQL tutorial continues the introduction to the use of collections in Oracle PL/SQL.
EXECUTE IMMEDIATE. By Amar Kumar Padhi EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards.
It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead, EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Usage tips 1. If the DML command is processed via EXECUTE IMMEDIATE, one needs to explicitly commit any changes that may have been done before or as part of the EXECUTE IMMEDIATE itself. 2. 3. 4. 5. Object Types. Object-oriented programming is especially suited for building reusable components and complex applications.
In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database. Declaring and Initializing Objects in PL/SQL An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list. Currently, you cannot define object types in a PL/SQL block, subprogram, or package. Replacement of type. A trigger that fires several times at different triggering events, ability to force triggers of the same type to follow a sequence, and the new CONTINUE statement are some of the new gems that make PL/SQL programming easier. See Series TOC Since its inception, PL/SQL has been the language of choice for programming in Oracle Database.
Over a period of time, we have seen the language evolve into a comprehensive development platform by virtue of more and more functionality that requires less coding. PL/SQL Collections and Records. Arrays must be (have consecutive subscripts).
So, you cannot delete individual elements from an array. Initially, nested tables are dense, but they can become (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE . That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts. Understanding Varrays Items of type VARRAY are called . Text description of the illustration pls81017_varray_of_size_10.gif. SQL Performance Analyzer. Learn how to accurately predict changes on the actual SQL statements issued against your database - plus, monitor SQL performance in real time (in Release 2 only).
See Series TOC In a different installment of this series you learned about Database Replay, that great tool for capturing the real workload in your database and replay them at will. Database Replay is part of what Oracle calls the Real Application Testing option, with the emphasis on the word "real"—the workload replayed is actually what occurred in your database. In Database Replay, the entire captured workload is replayed against the database. But what if you don't want to do that? This area is where the other important component of the Real Application Testing family—SQL Performance Analyzer (SPA)—shines. Les fonctions analytiques. Nous allons d'abord voir un premier exemple d'utilisation des fonctions analytiques pour calculer une moyenne mobile.
Supposons qu'on ait une table de cotation boursière (les cotations ont lieu les jours ouvrés uniquement) : create table COTATION ( isin varchar2 (20), dte date, ouv number, max number, min number, clo number, volume number, constraint pk_cotation primary key (isin, dte)) Remarque : le lecteur pourra se reporter à l'annexe afin de créer les deux tables utilisées dans cet article. La moyenne mobile à 5 jours est la moyenne des clôtures du jour en cours ainsi que des 4 jours précédents. La méthode traditionnelle est : Avec les fonctions analytiques, la requête s'écrit simplement : Analytic functions by Example. This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples.
The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples. 11g-pivot.html. Present information in a spreadsheet-type crosstab report from any relational table using simple SQL, and store any data from a crosstab table to a relational table.
See Series TOC Pivot As you know, relational tables are, well, tabular—that is, they are presented in a column-value pair. Consider the case of a table named CUSTOMERS. Compound Triggers et autres ... Bulk Collect with PL/SQL. In PL/SQL, using bulk collect, it is possible to select the values from mutliple rows in one go.
Example #1 A table with two columns is created... ... and filled with some values: insert into bc values (11,'elf'); insert into bc values (12,'zwoelf'); insert into bc values (13,'dreizehn'); insert into bc values (14,'vierzehn'); commit; An anonymous block is executed. In the body, the select statement used bulk collect to select all rows in the table in one go into the variables. Finally, a loop over the variables' elements prints the values. BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP « Venzi's Weblog. After more and more reads about BULK COLLECT and FORALL and their performance improvements I decided to have a closer look on it by myself to see how powerful they really are.
So I built a little test-case which inserts all entries from the all_object view into another table. The inserts happens on three different ways: First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is. The second way is a simple FOR – IN LOOP with the insert of the cursor variables.
And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see. CLOB « Large Objects « Oracle PL/SQL Tutorial.