KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.
-
Upload
alan-tucker -
Category
Documents
-
view
229 -
download
7
Transcript of KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.
![Page 1: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/1.jpg)
KKUI
Manažérske informačné SystémyCvičenia
2010
Zdenek Marhefka
![Page 2: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/2.jpg)
KKUI
Obsah cvičení
1. Úvod do databázových systémov2. Návrh dátového modelu3. Programovacie jazyky SQL, PLSQL, Java4. Programovanie v PLSQL5. Princípy Data Warehousing-u a OLAP6. Relačná prezentačná vrstva DWH7. Multidimenzionálna prezentačná vrstva DWH8. OLAP Spreadsheet Addin9. Olacle Discoverer
2
![Page 3: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/3.jpg)
KKUI
Zdroje informácií
Oracle Technology Network
http://www.oracle.com/technetwork/index.html− Dokumentácia firmy Oracle− Vývojové prostriedky − Príklady zdrojových kódov, sample data a demá
Oracle OLAP
http://www.oracle.com/technetwork/database/options/olap/index.html Oracle OLAP option
http://wiki.oracle.com/page/Oracle+OLAP+Option
Alternatívny zdroj informácií o OLAP v prostredí ORACLE
3
![Page 4: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/4.jpg)
KKUI
Zdroje informácií
Oracle Database 10g Release 2 (10.2) Documentation
http://www.oracle.com/technetwork/database/database10g/documentation/index.html
V lokálnom adresári:
\Mis\oracle10g\index.htm
\Mis\oracle10g\index.pdx
4
![Page 5: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/5.jpg)
KKUI
4 Programovanie v PLSQL
4.1 Úvod do PLSQL
4.2 Programovací jazyk PLSQ Konštrukcie jazyka Anonymné bloky Uložené procedúry Programové balíky Príkazy jazyka
4.3 Vytvorenie programového balíka
5
![Page 6: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/6.jpg)
KKUI
4.1 Úvod do PLSQL
PL/SQL (Procedural Language/SQL) is a procedural extension of Oracle-SQL that offers language constructs similar to those in imperative programming languages.
The basic construct in PL/SQL is a block. Blocks allow designers to combine logically related (SQL-) statements into units. In a block, constants and variables can be declared, and variables can be used to store query results. Statements in a PL/SQL block include SQL statements, control structures (loops), condition statements (if-then-else), exception handling, and calls of other PL/SQL blocks.
PL/SQL blocks that specify procedures and functions can be grouped into packages. A package is similar to a module and has an interface and an implementation part.
6
![Page 7: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/7.jpg)
KKUI
PLSQL – ako to funguje
The PL/SQL Engine and the Oracle Database Server:
7
![Page 8: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/8.jpg)
KKUI
PLSQL – ako to funguje
The program unit is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.
8
![Page 9: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/9.jpg)
KKUI
Konštrukcie jazyka
PL/SQL blocks can include the following PL/SQL language constructs: Variables and constants
Variables and constants can be declared within a procedure, function, or package.
CursorsCursors can be declared explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle data. Cursors also can be declared implicitly (to support other data manipulation actions) by the PL/SQL engine.
ExceptionsExceptions PL/SQL lets you explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code.
9
![Page 10: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/10.jpg)
KKUI
Anonymné bloky
Structure of PL/SQL-Blocks[<Block header>]
[declare
<Constants>
<Variables>
<Cursors>
<User defined exceptions>]
begin
<PL/SQL statements>
[exception
<Exception handling>]
end;
10
![Page 11: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/11.jpg)
KKUI
Uložené procedúry
create [or replace] procedure <procedure name> [(<list of parameters>)] is
<declarations>
begin
<sequence of statements>
[exception
<exception handling routines>]
end [<procedure name>];
create [or replace] function <function name> [(<list of parameters>)] return <data type> is
<declarations>
begin
<sequence of statements>
[exception
<exception handling routines>]
end [< function name >];
11
![Page 12: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/12.jpg)
KKUI
Programové balíky
create [or replace] package <package name> as -- specification (visible part)
-- public type and object declarations
-- subprogram specifications
<declarations>
end [< package name>];
create [or replace] package body <package name> as -- body (hidden part)
-- private type and object declarations
-- subprogram bodies
<declarations>
[ begin
-- initialization part starts here
<sequence of statements>]
end [<package name>];
12
![Page 13: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/13.jpg)
KKUI
Príkazy jazyka
IF-THEN-ELSE CASE (Oracle 9i, Oracle 10g, Oracle 11g) LOOP FOR LOOP CURSOR FOR LOOP WHILE LOOP Repeat Until Loop EXIT WHEN NULL
13
![Page 14: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/14.jpg)
KKUI
IF-THEN-ELSESyntax #1: IF-THEN
IF condition THEN
{...statements...}
END IF;
Syntax #2: IF-THEN-ELSE
IF condition THEN {...statements...}
ELSE {...statements...}
END IF;
Syntax #3: IF-THEN-ELSIF
IF condition THEN {...statements...}
ELSIF condition THEN {...statements...}
ELSE {...statements...}
END IF;
14
![Page 15: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/15.jpg)
KKUI
CASECASE [ expression ]
WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result
END
15
![Page 16: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/16.jpg)
KKUI
LOOPLOOP
{.statements.}
END LOOP;
The LOOP statement is terminated when it encounters either an EXIT statement or when it encounters an EXIT WHEN statement that evaluated to TRUE.
Example:LOOP
monthly_value := daily_value * 31; EXIT WHEN monthly_value > 4000;
END LOOP;
16
![Page 17: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/17.jpg)
KKUI
FOR LOOPFOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP {.statements.}
END LOOP;
17
![Page 18: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/18.jpg)
KKUI
CURSOR FOR LOOPFOR record_index in cursor_name
LOOP {.statements.}
END LOOP;
or using SELECT statement
FOR record_index in ( SELECT <list of columns>
FROM <list of tables>
WHERE <conditions>
)
LOOP {.statements.}
END LOOP;
18
![Page 19: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/19.jpg)
KKUI
WHILE LOOPWHILE condition
LOOP {.statements.}
END LOOP;
19
![Page 20: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/20.jpg)
KKUI
Repeat Until LoopLOOP
{.statements.} EXIT WHEN boolean_condition;
END LOOP;
20
![Page 21: KKUI Manažérske informačné Systémy Cvičenia 2010 Zdenek Marhefka.](https://reader035.fdocument.pub/reader035/viewer/2022062321/56649f1a5503460f94c2f96b/html5/thumbnails/21.jpg)
KKUI
21