Categoria: PL/SQL Oracle

PL/SQL Oracle

Nested table

In PL SQL esiste una raccolta di dati efficiente e adattabile: la tabella annidata. La tabella annidata è simile a una matrice unidimensionale ma con alcune differenze: Un array ha il numero di dichiarazioni di elementi, la tabella nidificata no. La dimensione della tabella nidificata può aumentare utilizzando il metodo di estensione. Un array ha sempre elementi consecutivi: la tabella annidata ha inizialmente elementi consecutivi ma può diventare sparsa quando gli elementi vengono eliminati. Per creare una tabella nidificata devi usare questa sintassi: type type_name is table of element_type[size element]; table_name type_name; Di seguito un esempio di creazione e utilizzo...

PL/SQL Oracle / SQL Server

Binary Search

Uno degli algoritmi di ricerca più interessanti è l’algoritmo di ricerca binaria. Questo è l’algoritmo più veloce (algoritmo di alberi binari a parte) per cercare dati all’interno di un gruppo di elementi. In effetti viene eseguito in un peggior confronto log2 (x) prima di trovare (o meno) i dati. L’unico prerequisito è: l’insieme di elementi in cui cercare i dati deve essere ordinato. Di seguito un esempio di questo algoritmo.int binary_search( int arr[], int tot_el, int data ){/* Declaration and initialization of variables */int el = 0;int me = 0;int count = 0;/* Start program */el = tot_el-1;while (count <=...

PL/SQL Oracle / SQL Server

Record data type

In Pl Sql esistono molti tipi di dati: varchar2, number, float, double, boolean, ecc, ma può capita di voler utilizzare una struttura che contiene diversi tipi di dati, la chiamiamo tipo Record. È semplicemente un nuovo tipo di dati. Il tipo di record è formato da un gruppo di una o più colonne con proprie nome e tipo di dati. Per creare il tipo di record è necessario utilizzare la parola chiave TYPE in modo da istruire il compilatore su questo nuovo tipo di dati. Ci sono due modi per crearlo, il primo è a “livello di database” che può...

PL/SQL Oracle

Goal Search Algorithm

Description: Goal search algorithm or search algorithm of the limit of a monotonically increasing sequence bounded above. 1) Purpose: Research of limit of a monotonically increasing sequence bounded above. 2) Example: Suppose to want to create a 10 meters high tower with a tolerance of 10 millimeters, having a given number of bricks, each of different height, stacked one over another. To make the tower we will be obligated to use bricks in Last In First Out order. Suppose to have this stack of bricks named XdataTable: 908 906 832 454 464 522 45 229 5057 433 930 214 1...

PL/SQL Oracle

Hide Pl/Sql source code

To hide PlSql source code you can use both: dynamic obfuscation the wrap utility You might have to wrap some of yours PlSql objects just to provide the security of your code. Once wrapped, your code can’t be displayed from the static data dictionary views [ *_SOURCE ]. You can wrap the following types of PlSql objects Package specification Package body Type specification Type body Function Procedure However there are some utilities that you can use to read wrapped code, so it is not considered an high-security method to protect your code. Some limitations are wrapped files are not downward...

PL/SQL Oracle

Execution flow of an sql statements

  The execution flow can be divided into four stage. Not all Sql Statements will use all the stages: Parse:  at the beginning the statement must be parsed, this mean check the syntax and associate it with the cursor. It also verifies if the privilege are correct and if the objects referenced really exists Bind:         this stage is execute only when the statement contain input data to be supplied at runtime. For each placeholder, values must be supplied to complete the SQL statement Execute:    at this point the server execute the statement, This is the...

PL/SQL Oracle

Create triggers on system events

System events are particular database states that can be used to fire a system trigger. Is possibile to create triggers for these events at DATABASE or SCHEMA level. When a triggering event occurs, the database will open an autonomous transaction scope, fire the trigger and commit any transaction imbedded in the trigger. The available system events are: AFTER STARTUP        Causes the DataBase to fire the trigger whenever the database is opened. This event is valid only with DATABASE, not with SCHEMA BEFORE SHUTDOWN      Causes the DataBase to fire the trigger whenever an instance of the...

PL/SQL Oracle

A simple procedure for create a flat csv file from table

Sometimes can be useful to produce a CSV file semicolon separated from a table. Here a simple dynamic procedure that works without write sql scripts pl sql code. Just put in some parameters and execute it from everywhere. With little customizations you can add a parameter for separator, add “” for strings, columns to avoid etc. CREATEORREPLACEprocedureTABLETOCSV( P_OWNERinvarchar2,–owner of the table P_TABLENAMEinvarchar2, –tablename P_PATHinvarchar2default‘/’, — server shared path where file can be putted in P_FILENAMEinvarchar2,–exported filename P_HEADERinvarchar2default‘Y’,–flag Y/N for header presence P_DATEFORMATinvarchar2default‘dd/mm/yyyy’ –-dateformat in csv )as     sql_scriptvarchar2(2000);     initnumber:=0;     v_ownervarchar2(30):=upper(P_OWNER);     v_table_namevarchar2(100):=upper(P_TABLENAME);     flg_headervarchar2(1):=P_HEADER;     v_headervarchar2(1000);     dt_fvarchar2(10):=P_DATEFORMAT;...

PL/SQL Oracle

AUTHID property

The AUTHID property of a stored PLSQL unit, determines the authorization under which a PLSQL subprogram operates at run-time. You can use two clause:  . CURRENT_USER : when the subprogram is executed , it will run using the rights of the person that invoked the code, not with the creator’s rights  . DEFINED : when the subprogram is executed , it will run using the rights of the person that created the code, not with the invoker’s rights (triggers and views always behave in                         this way). This is...

PL/SQL Oracle

Create an Index By Table of Record

An index by table is an associative array. An associative array never has more than two columns: the variable being indexes and the index value. However you can combine a record with an associative array to tie multiple values to the same index.   In this case the array still has a variable and an index, but the record variable now contains multiple distinct values. In the below example you’ll see how to associate a record to an associative array.   DECLARE   TYPE r_impiegati IS RECORD (                        ...