IT

Personal notes on software development.
For Java technologies check my dedicated site

Pages

PL/SQL

A great tutorial: PL/SQL Tutorial

As the name implies, PL/SQL supports programming language features like conditional statements, iterative statements.
The programming constructs are similar to how you use in programming languages like Java and C++.

PL/SQL Structure:

DECLARE 
      Variable declaration 
BEGIN 
     Program Execution 
EXCEPTION 
      Exception handling
END;

Declare variables:

Syntax:
  • variable_name datatype [NOT NULL := value ];
    Dataypes:
    • Number (n,m);
    • Char (n);
    • Varchar2 (n)
    • Date;
    • Long;
    • Long raw;
    • Raw;
    • Blob;
    • Clob;
    • Nclob;
    • Bfile;
    Ex.: 
    salary number(4);
    dept varchar2(10) NOT NULL := “HR Dept”; 
    Global vs Local variables:
    DECLARE
  myGlobalVar number; 
 BEGIN 
  myGlobalVar := 100;  
  DECLARE 
   myLocalVar number; 
...
    NOTE: You can also use %type to declare variables and constants (see "Records").

    Declare Constants:

    Syntax:
    • constant_name CONSTANT datatype := VALUE;  
    Ex.:
    • salary_increase CONSTANT number (3) := 10; 
    NOTE: You can use also %type to declare variables and constants (see "Records"). 

    Records

    Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. A record can be visualized as a row of data. It can contain all the contents of a row.

    Multiples ways of declaring records:
    Syntax Usage
    TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...); Define a composite datatype, where each field is scalar.
    col_name table_name.column_name%type; Dynamically define the datatype of a column based on a database column.
    record_name record_type_name; Declare a record based on a user-defined type.
    record_name table_name%ROWTYPE; Dynamically declare a record based on an entire row of a table. Each column in the table corresponds to a field in the record.


    Multiple ways you can assign values to and from a record:
    Syntax Usage
    record_name.col_name := value; To directly assign a value to a specific column of a record.
    record_name.column_name := value; To directly assign a value to a specific column of a record, if the record is declared using %ROWTYPE.
    SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause]; To assign values to each field of a record from the database table.
    SELECT * INTO record_name FROM table_name [WHERE clause]; To assign a value to all fields in the record from a database table.
    variable_name := record_name.col_name; To get a value from a record column and assigning it to a variable.

    Conditional statements

    Syntax:
    IF condition 1 
THEN 
 statement 1; 
 statement 2; 
ELSIF condtion2 THEN 
 statement 3; 
ELSE 
 statement 4; 
END IF;

    Loops

    There are three types of loops in PL/SQL:
    • Simple Loop (similar to Java Do While)
    • While Loop (similar to Java While)
    • For Loop  (similar to Java For)
    Simple Loop
    Syntax:
    LOOP 
     statements; 
      EXIT; 
       {or EXIT WHEN condition;}
END LOOP;
    Ex:
    While Loop
    Syntax:
    WHILE <condition>
LOOP statements;
END LOOP;

    Ex:
    WHILE monthly_value <= 4000
LOOP
     monthly_value := daily_value * 31;
END LOOP;
    For Loop
    Syntax: 
    FOR counter IN [reverse] lowest_val..highest_val
  LOOP statements; 
END LOOP; 
    Ex.: 
    for i in 1..1000 loop 
     insert into a values(i,i*2);
end loop;

    for i in reverse 1..1000 loop 
    insert into a values(i,i*2);
end loop;

    Records

    A record can be visualized as a row of data. It can contain all the contents of a row. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc.  Each scalar data types in the record holds a value.
    NOTE: When you are creating a record, you are just creating a datatype, similar to creating a variable. You need to assign values to the record to use them.

    Declaration syntax:
    • TYPE record_type_name IS RECORD
      (first_col_name column_datatype,
      second_col_name column_datatype, ...);
      NOTE: If a field is based on a column from database table, you can define the field_type as follows:
      col_name table_name.column_name%type;
    • If all the fields of a record are based on the columns of a table, we can declare the record as follows:
      record_name table_name%ROWTYPE;

    Access syntax:
    Different ways you can assign values to and from a record:
    Syntax Usage
    record_name.col_name := value; To directly assign a value to a specific column of a record.
    record_name.column_name := value; To directly assign a value to a specific column of a record, if the record is declared using %ROWTYPE.
    SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause]; To assign values to each field of a record from the database table.
    SELECT * INTO record_name FROM table_name [WHERE clause]; To assign a value to all fields in the record from a database table.
    variable_name := record_name.col_name; To get a value from a record column and assigning it to a variable.

    Triggers

    A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

    Syntax: 
    CREATE [OR REPLACE ] TRIGGER trigger_name 
 {BEFORE | AFTER | INSTEAD OF } 
 {INSERT [OR] | UPDATE [OR] | DELETE} 
 [OF col_name] 
 ON table_name 
 [REFERENCING OLD AS o NEW AS n] 
 [FOR EACH ROW] 
 WHEN (condition)  
 BEGIN 
   --- sql statements  
 END;

    NOTE:
    Triggers can be fired in two different ways: by statement or by row.
    For example and update statement that updates many rows of a table can fire a trigger only one time (statement level trigger) or one time for each row updated (row level trigger).
    To specify this you use the [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed (i.e.statement level Trigger).








    Ex.: 
    CREATE or REPLACE TRIGGER price_history_trigger 
BEFORE UPDATE OF unit_price 
ON product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES 
(:old.product_id, 
 :old.product_name, 
 :old.supplier_name, 
 :old.unit_price); 
END; 

     CREATE or REPLACE TRIGGER After_Update_Row_product 
 AFTER  
 insert On product 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO product_check 
 Values('After update, Row level',sysdate); 
 END;

    Cursors

    There are 2 types of cursors:
    • Implicit cursors:
      These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
      Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
    • Explicit cursors:They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
      Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

    Implicit Cursor example:
    DECLARE var_rows number(5);
BEGIN
 UPDATE employee 
 SET salary = salary + 1000;
 IF SQL%NOTFOUND THEN
 dbms_output.put_line('None of the salaries where updated');
 ELSIF SQL%FOUND THEN
 var_rows := SQL%ROWCOUNT;
 dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
 END IF; 
END;

    Explicit Cursor example:
    Ex1 (source)
    DECLARE 
    /* DECLARANDO O CURSOR DE PRODUTOS */
    CURSOR C_PRODUTOS IS 
        SELECT * FROM TB_PRODUTOS;
        
    /* DECLARANDO UMA VARIAVEL QUE SERA O REGISTRO DA TABELA */
    REG_PRODUTO C_PRODUTOS%ROWTYPE;
    
BEGIN
    /* ABRE CURSOR */
    OPEN C_PRODUTOS
    
    LOOP
        /* LÊ UM REGISTRO DO CURSOR */
        FETCH C_PRODUTOS INTO REG_PRODUTO;
        
        /* ABANDONA O LOOP CASO SEJA O FINAL DO CURSOR */
        EXIT WHEN C_PRODUTOS%NOTFOUND;
        
        /* 
        AQUI SERA INSERIDO O CODIGO QUE IRA MANIPULAR OS DADOS COMO: 
            - INSERIR EM OUTRA TABELA, 
            - FAZER ALGUM CALCULO, 
            - ETC. 
        */
    
    END LOOP;
    
    /* FECHA O CURSOR */
    CLOSE C_PRODUTOS
END;
    TODO
    TODO
    TODO

    Posted by amano at 6/06/2012
    Labels:

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)