PL/SQL

gistfile1.txt

1. Базовая структура блока plsql

Declare - необязательно
(const, vars, types, cursors, user exceptions)

Begin - обязательно
(PL/SQL, SQL commands)

Exception - необязательно
Действий при возникновении ошибки

END; - обязательно

Declare 
B_name varchar2(100);

Begin 
Select naem into B_NAME from Salers where ID = 106
dbms_output.put_line('фамилия '||B_name);
Exception
When others then
dbms_output.put_line('error happend')

end;

Типы блоков : 
1. Анонимные блоки
2. Процедуры 
3. Функции 

binary_integer vs pls_integer

pls_integer will raise a run time exception on overflow
pls_integer - fast in execution, operates on machine aritimetic 

binary_integer - operes on library aritmetic

char - fixed length
varchar2 - dynamic length
date
timestamp
boolean (true/false, null) (null value is undefined)

Naming:
1. starts from [a-zA-Z]
2. can include [a-zA-z1-9], $,_, #
3. No reserved words 

:= (assigment)

declare 
b_start_date date;
b_name varchar(100) not null
b_sum number := 0 
k_limit constant number:=1000

%type attribute (variable with the same type as table type, variable type )

var1 real(14,2);
var2 var1%Type; //var 2 has the same type as var 1 [real(14,2)]
var_f1 user1.tbl1.f1%type - same type as tbl1 column

%rowtype 

delcares a record 

Составные типы данных

PL/SQL record
pl/sql table (has to contain primary key that's used to create indexes + scalar type or record) 
nested table 
varray (dynamic array)


DML можно
DDL & DCL - явным образом нельзя

IF COND THEN
STATEMENTS;
[ELSEIF COND THEN 
STATEMENTS; ]
[ELSE
STATEMENTS;]
END IF;

CASE 
WHEN COND1 THEN ACTION
WHEN COND2 THEN ACTION

[ELSE ACTION]

END CASE;

LOOPS:

LOOP
FOR
WHILE

LOOP 

STATEMENT;

EXIT [WHEN COND]

END LOOP;

FOR COUNTER_NAME IN [REVERSE]
UPPER_BOUND..LOWER_BOUND LOOP

STATEMENTS

END LOOP;

WHILE CONDITION LOOP

STATEMENTS;

END LOOP;

BEGIN
  ≪l_0uter≫
   FOR v_0uterlndex IN 1.. 50 LOOP
     ≪l_Inner≫
      FOR v_lnnerlndex IN 2.. 10 LOOP
          IF v_0uterindex > 40 THEN
               EXIT l_0uter; - выход из обоих циклов
           END IF;
      END LOOP l_Inner;
   END LOOP l_0uter;

Удобно выходить из вложенных циклов

Cursors:

1. Implicit - creates every single time when ( select .. into / DML ops  excuted ) 
2. Explicit  (declared)
3. Cursor variables (have type REF CURSOR)
4. Cursor expressions (cast select to REF CURSOR)

SQL%ROWCOUNT - amount of selected / changed of rows by last command
SQL%FOUND
SQL%NOTFOUND
%SQLISOPEN - False. PL/sql closes implicit cursors after execution

Explicit cursor:

declaration (associate with query, assign name) ->
open (query execution; result rows are active set) ->  
fetch (get current row from active set, every single fetch moves cursor -> 
close (release active set, since that moment, cursor can be open again)

Declaration

CURSOR name [params]
[return type]
IS query
[FOR UPDATE [of (list of column)][NOWAIT]]

CURSOR BY PLAYERS 
IS 
SELECT PLAYER_ID, FML
FROM PLAYERS
WHERE DOC_ID = 1 

CURSOR AUTHOUR_cUR1(I_ID IN NUMBER)
IS 
SELECT ROWID 
FROM AUTHORS
WHERE ID > I_ID;

BEGIN

OPEN BY_PLAYERS;
OPEN AUTHOR_CUR1(50);

...
END;

FETCH DONT THROW EXCEPTION IF NO RESULT OR END OF ACTIVE SET
FETCH exit FOR WHEN ACTIVE SET BECAME EMPTY 

close by_players
close author_cur1;

Explicit cursor attributes

%ISOPEN
%NOTFOUND
%FOUND
%ROWCOUNT
%BULK_EXCEPTIONS
%BULK_ROWCOUNT

for + cursor:

for var in cursor loop

end loop;