Created
June 4, 2019 21:22
-
-
Save zxdcm/f4b46c3294bd2ecfac0ece13463fbcdd to your computer and use it in GitHub Desktop.
PL/SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment