PL / SQL

from Wikipedia, the free encyclopedia
PL / SQL
Paradigms : procedural
Publishing year: 1991
Developer: Oracle
Current  version : 11.1   (2009)
Typing : strong , static , explicit
Important implementations : Oracle database
Influenced by: COBOL , PL / I , Ada
License : proprietary
Oracle Technology Network

PL / SQL ( P rocedural L anguage / S tructured Q uery L anguage) is a proprietary programming language of the company Oracle .

PL / SQL combines the query language SQL with a procedural programming language. The syntax is very similar to the Ada programming language .

It supports variables , conditions , loops , and exception handling . From version 8 of the Oracle RDBMS , object-oriented characteristics are also introduced.

PL / SQL is designed to work with Oracle databases . In particular, SQL commands according to the Oracle standard can be inserted in the source code. The SQL statements are not generated as character strings and transferred to a database interface (as is the case with ODBC , JDBC, etc.), but are seamlessly integrated into the program code. The syntax can be checked at the time of compilation.

The procedural extension of the SQL query language is now also implemented by many other database manufacturers. This is why this procedural SQL extension has now been standardized by the ANSI committee .

use

  • You can send PL / SQL code like SQL commands via a database front end , which is then processed directly.
  • Individual subroutines ( Stored Procedures ) or libraries of several subroutines (Stored Packages) can be saved as permanent database objects on the database server and thus expand the functionality of the database; every user of the database can call and use these subroutines. The authorizations can be assigned to individual users or user groups (so-called "roles") for each individual PL / SQL package.
  • Programming of database triggers
  • Programming in various tools ( Oracle forms , Oracle reports)

PL / SQL programs can improve performance if the call is made from an application server that is connected to the database server via a slow network connection. In this case, a message only needs to be transported over the network at the beginning and at the end of the execution. But there are also other ways of improving the performance of an application on a slow network. So z. For example, the Oracle database also runs Java programs that manipulate the database.

Basic structure

PL / SQL programs consist of blocks: 

  declare
      -- Deklarationsblock
  begin
     -- Ausführungsteil
  exception
     -- Ausnahmeverarbeitung
  end;

  /* So kommentiert man
  mehrzeilig */
  --So kommentiert man einzeilig

Variable definitions

Variables are defined in the (optional) declaration section and optionally initialized. 

  declare
      zahl1 number(2);
      zahl2 number(2) := 17;
      text varchar(20) := 'Das ist ein Text';
  begin
      select hausnummer into zahl1 from Adressverzeichnis where name='Meier' and rownum=1;
  end;

:= is the assignment operator used to assign a value to a variable.

Number variables

  variablenname number(P[,S]) := Wert;

To define a number variable, for example, write the variable name followed by the variable type NUMBER.

After this, the accuracy is written in round brackets Pas well as optionally a comma and the number of decimal places S.

In this case, the precision corresponds to the number of places the variable can contain and not to the range of values.

Selection of further data types for number variables: 

  dec, decimal, double precision, integer, int, numeric, real, smallint, binary_integer, pls_integer

Text variables

  variablenname varchar2(L) := 'Text';

To define a text variable, write the variable name followed by the variable type VARCHAR2.

The attached length constraint (integer in brackets) specifies the maximum length of the variable in bytes (provided the Length Semantic is not set to CHAR)

Selection of further data types for text variables: 

CHAR, NCHAR, NVARCHAR2, CLOB, NCLOB

The data types LONG, LONG RAW and VARCHAR are deprecated

Boolean

  variablenname boolean := true;

Can TRUE, FALSEor NULLbe.

date

  variablenname date := to_date( '01.01.2016' , 'DD.MM.YYYY');

To define a date variable, write the variable name followed by the variable type DATE. The function TO_DATEconverts the text between the first quotation marks into a date with the specified format between the second quotation marks. The function is available to convert a date into text TO_CHAR(Datum). In addition to the data type, datethere is also the data type timestamp, which has a greater precision. There are further sub-types for this: timestamp with time zonesaves the time zone with the time information, timestamp with local timezoneconverts from the time zone of the session to the local time zone of the database server.

Specify data type via column 

  Variablenname Tabellenname.Spaltenname%type;

Defines a variable of the type of the specified column.

Specify data type via table 

  Variablenname Tabellenname%rowtype;

Defines a variable for a data record / row of the type of the specified table.

Example: 

    CURSOR cursor_name IS
        SELECT *
        FROM tabelle;

    variable tabelle%rowtype;

    ...

    FOR i IN cursor_name LOOP
        variable := i;
        andere_variable := variable.SPALTENNAME;
    END LOOP;

Scope of variables

PL / SQL allows you to nest blocks. Variables declared in outer blocks are valid in all inner blocks. Variables declared in inner blocks are not valid in outer blocks.

Depending on where variables are declared, a distinction can be made between two types.

  • Local variables - these are declared in an inner block. It cannot be accessed from outside.
  • Global Variables - These are declared in an outer block and can be accessed from outside and inside.

In the following example, two variables are declared and the sum of them is assigned to a third variable in an inner block. The variables var_num1and var_num2can be accessed from anywhere in the block. On the var_resultother hand, the variable that was declared in the inner block cannot be accessed from outside. 

DECLARE
  var_num1 NUMBER;
  var_num2 NUMBER;
BEGIN
  var_num1 := 100;
  var_num2 := 200;
  DECLARE
    var_result NUMBER;
  BEGIN
    var_result := var_num1 + var_num2;
  END;
  /* Auf var_result kann hier nicht zugegriffen werden */
END;

Constants

A constant is a value in a PL / SQL block that does not change during the program run. 

DECLARE
  konstantenname CONSTANT NUMBER(3) := 10;

The value assignment of a constant must be made directly with the declaration.

Custom data types

User-defined data types are defined using: 

  type datentyp is record(feld1 typ1 [:=xyz], feld2 typ2 [:=xyz], ..., feldn typn [:=xyz]);

Example: 

  declare
    type t_adresse is record(
        hausname adresse.hausname%type,
        strasse adresse.strasse%type,
        hausnummer adresse.hausnummer%type,
        postleitzahl adresse.postleitzahl%type);
    v_adresse t_adresse;
  begin
    select hausname, strasse, hausnummer, postleitzahl into v_adresse from adresse where rownum = 1;
  end;

The example program defines its own data type called t_adressethat the fields hausname, strasse, hausnummerand postleitzahlcontains.

With this data type, a variable is v_adressedefined which is adressefilled with a data record from the table .

The attributes can be accessed using dot notation 

  v_adresse.hausname := 'Nollenburgerweg 115';

grind

Loops repeat the instructions contained in their body.

Loop loop (basic loop) 

  loop
    ...
    exit when BEDINGUNG;
  end loop;

The looploop repeats the instructions contained in your body. It can be ended with a exit whenfollowed by a termination condition.

Note: Even if the condition for this is exitmet, the statements that are in the loop body before the exitstatement are executed at least once.

While loop

  while Bedingung loop
    ...
  end loop;

The whileloop repeats the instructions contained in your body as long as the condition is met in your head.

Note: If the condition is not met in the head, the instructions in the loop body will never be executed.

For loop

  for v_counter in 1..10 loop
    ...
  end loop;

The forloop counts an index variable from a specified start value to a specified end value. The smaller value is always on the left, the larger one on the right. If you enter the keyword REVERSEafter the IN, it will count down from the larger to the smaller value.

Note: Here too, the smaller value must be on the left and the larger value on the right. 

  begin
     for i in reverse 1 .. 5 loop
      dbms_output.put_line(to_char(i));
     end loop;
  end;

  5
  4
  3
  2
  1

Note: If you do not see the expected numbers 5 to 1 when working with SQL * Plus, you have to switch the output on beforehand. 

  set serveroutput on

Cursor-for loop

  for Record-Index in (Select Mitarbeiter_Nummer from Personaltabelle)
  loop
    ...
  end loop;

The cursor forloop automatically opens the cursor, reads in the data records and closes the cursor again.

Alternatively, SELECTthe cursor's statement can also be defined beforehand in order to use it multiple times or to make the presentation more clear (especially advantageous for longer / more complex queries). 

  cursor cursor_mitarbeiter is

  Select Mitarbeiter_Nummer from Personaltabelle;
  for Record-Index in cursor_mitarbeiter

  loop
    ...
  end loop;

The employee number within the FORloop is accessed with the connection operator .: 

  Record-Index.Mitarbeiter_Nummer

conditions

With the help of conditions, one can react differently to different situations.

IF-THEN-ELSE

  declare
    v_land welt.land%type;
  begin
    dbms_output.enable(20000);
    select land into v_land from welt
      where rownum = 1;
    if v_land = 39 then
        dbms_output.put_line('Land ist 39');
    elsif v_land = 49 then
        dbms_output.put_line('Land ist 49');
    else
        dbms_output.put_line('Land unbekannt');
    end if;
  end;

ifchecks whether a condition is met. If the condition is met, the code between ifand is end ifexecuted, otherwise it is skipped. Optionally, you can elsifspecify a further condition with associated code that is executed if this condition is met. Last but not least, you can specify a general one elsewhose code will be executed if none of the previous conditions were met.

Condition structures can also be CASEexpressed using the statement.

CASE-WHEN

  DECLARE
    v_land welt.land%TYPE;
  BEGIN
    DBMS_OUTPUT.enable(20000);
    SELECT land INTO v_land FROM welt
      WHERE ROWNUM = 1;
    CASE WHEN v_land = 39 THEN DBMS_OUTPUT.put_line('Land ist 39');
         WHEN v_land = 49 THEN DBMS_OUTPUT.put_line('Land ist 49');
         WHEN v_land = 59 THEN DBMS_OUTPUT.put_line('Land ist 59');
                          ELSE DBMS_OUTPUT.put_line('Land unbekannt');
    END CASE;
  END;

Simplified form for pure value lists: 

  DECLARE
    v_land welt.land%TYPE;
  BEGIN
    DBMS_OUTPUT.enable(20000);
    SELECT land INTO v_land FROM welt
      WHERE ROWNUM = 1;
    CASE v_land
       WHEN 39 THEN DBMS_OUTPUT.put_line('Land ist 39');
       WHEN 49 THEN DBMS_OUTPUT.put_line('Land ist 49');
       WHEN 59 THEN DBMS_OUTPUT.put_line('Land ist 59');
               ELSE DBMS_OUTPUT.put_line('Land unbekannt');
    END CASE;
  END;

Exception handling

There are two types of exceptions:

  • predefined exceptions and
  • custom exceptions,

whose handling is analogous.

Predefined exceptions

These types of exceptions are automatically triggered by PL / SQL if errors occur when working with database objects (tables, views, packages, etc.) or programming errors (e.g. division by zero) occur when executing the program. The exceptions have predefined names that can be used in PL / SQL programs. Every exception has an error code consisting of the letters ORA and 5 digits. Typical exceptions of this type are

no_data_found (ORA-01403) - A SELECT query returns an empty amount of data

too_many_rows (ORA-01422) - A SELECT query returns a data set with more than one record , at a point where only a single record was expected as the result of the query.

Custom exceptions

To handle logical program errors, you can create your own exceptions in the form 

  <exception_name> exception;
  pragma exception_init(<exception_name>, -<exception_number>);

defined and by the command 

  raise <exception_name>;

to be triggered.

Exception block

The exception block is used to catch all exceptions of the associated execution section. There is thus the possibility of adding individual error handling after an exception. On the one hand, the error can be dealt with after it has been intercepted; on the other hand, the exception can be passed on to the enclosing block. If the exception is propagated to the outermost block and no appropriate error handling has been defined there, this leads to an uncontrolled termination of the program.

The general structure of an exception block is as follows: 

  begin
     -- Programmcode der eine Exception auslösen kann
     Ausführungsteil
     ...
  exception
  when <exception_name_1> then
     -- Exceptionbehandlung für die Exception <exception_name_1>
     Ausführungsteil
  ...
  when <exception_name_n> then
     -- Exceptionbehandlung für die Exception <exception_name_n>
     Ausführungsteil
  when others then
    -- Exceptionbehandlung für alle restlichen, nicht erwarteten Exceptions
     Ausführungsteil
  end;

The when othersbranch is optional; it catches all exceptions that have not been handled in the exception block until then. If this branch is missing, unhandled exceptions are passed on implicitly to the enclosing block. If an exception is to be passed on explicitly, the execution section must end with the program line raise <exception_name>;.

Comparison with T-SQL

Despite its superficial similarities, PL / SQL is fundamentally different from Transact-SQL . Taking over source code is therefore usually not an easy task. Not only because of the differences in the feature sets of the two languages, but also because of the very clear differences in how Oracle and SQL Server handle the control of parallelism and locking . However, there are software tools that can make adoption easier, such as Oracle Translation Scratch Editor and SwisSQL.

Comparable programming options with other database management systems

Web links

Wikibooks: PL / SQL  - learning and teaching materials

Individual evidence

  1. ↑ vyaskn.tripod.com .
  2. ↑ Scratchlauncher Oracle Scratchlauncher.
  3. ↑ swissql.com .