Quiz 2 (week 2 cursors, week 3, week 4) Flashcards
A cursor is a _____ to this context area
A cursor can hold ___ __ ___rows of data
The set of rows the cursor holds is referred to as the _____ ____
pointer
zero or more
active set
____ ___ Are automatically created by Oracle whenever a ___ statement is executed
DML (INSERT, UPDATE,
DELETE)
%_____ - true if 1 or more rows are affected
%____ - the opposite of %____
%_____ - the number of rows processed
SQL%FOUND (true if 1 or more rows are affected)
SQL%NOTFOUND (the opposite of SQL%FOUND)
SQL%ROWCOUNT (the number of rows processed)
Explicit cursors are programmer defined cursors for gaining more control over the ___ ___
Once opened, the data must be moved from the ___ ____ to a ___ ___ to be used by the program.
Variables must be populated ___ __ __ __ of the data in the cursor.
context area
temp area
local variable
in the same order
Declare cursor syntax
DECLARE
l_id sh.Customers.Cust_ID%TYPE
l_fname sh.Customers.Cust_First_Name%TYPE
l_lname sh.Customers.Cust_Last_Name%TYPE
CURSOR cr IS SELECT Cust_ID, Cust_First_Name, Cust_Last_Name
FROM sh.Customers
WHERE Cust_ID < 100;
CURSOR name IS [select statement]
fetch from cursor into variables without bulk collect syntax
BEGIN
OPEN cr;
LOOP
FETCH cr INTO l_id, l_fname, l_lname;
DBMS_OUTPUT>PUT_LINE(rowNames);
EXIT WHEN cr%NOTFOUND;
END LOOP;
CLOSE cr;
END;
cursor attributes
cr%ISOPEN
cr%NOTFOUND
cr%FOUND
cr%ROWCOUNT
bulk collect rows syntax
DECLARE
TYPE Rec_Cust IS RECORD
(l_id NUMBER,
name VARCHAR2(20));
TYPE Arr_Cust IS TABLE OF Rec_Cust; l_Cust Arr_Cust
define cursor
BEGIN
OPEN cr;
FECTH cr BULK COLLECT INTO l_cust;
CLOSE cr;
FOR i IN 1 .. l_Cust.LAST LOOP
DBMS_OUTPUT>PUT_LINE(rows);
END LOOP; END;
Make a record that has the data types of the table
make a table of the record
define cursor
open cr, bulk collect, close cr
for loop
how to access rows of a table with an index
tableName(1)
forall loop
when to use
syntax
can only include one DML
FORALL i IN 1..l_Cust.LAST
INSERT INTO table Values l_cust(i)
___ ___ - Is a named block of PL\SQL
code that is saved in in the
DBMS
Procedures can be executed
using ____
Stored Procedures can
___ ____
perform ___ or ___
Stored procedures
EXECUTE
return values
perform DDL or DMLs
Procedure syntax
CREATE OR REPLACE PROCEDURE hello (parameterName IN VARCHAR2)
IS
returnedVar VARCHAR2(24);
BEGIN
END;
ways to call a procedure
procedureName(val1, val2)
CALL procedureName(val1, val2)
procedure_name(param2 => val2, param1 => val1)
assignment operator
:=
what does IN mean in procedures
where to use
read only, you can use the data, but you cannot change it
use in the parameter section
what does OUT mean in procedures
where to use
writable variable
use in the parameter section
what does INOUT mean in procedures
readable and writable
how to get an empty table with the table structure of another table
CREATE TABLE tableName AS
SELECT * FROM otherTable
WHERE 1=2;
1 is never equal to 2, so no rows will be selected
a __ always returns a value
a ___ cant make charges to the database, like DML statements
function
function
the return keyword is optional for functions t/f
f
function syntax
CREATE OR REPLACE FUNCTION functionName (param1 dataType)
RETURN dataType
IS
PL/SQL block
functions can return multiple values t/f
f
only one
you can use functions in select statements t/f
t
functions, procedures and variables can be put together in a package
syntax
CREATE OR REPLACE PACKAGE name IS
procedure/function block;
procedure/function block;
END name;
EXCEPTION
syntax
where to use
EXCEPTION
WHEN ZERO_DIVIDE THE
statements;
after BEGIN, before END
exceptions
zero_divide
case_not_found
no_data_found - for select into or cursor fetch
rowtype_mismatch
too_many_rows
value_error
how to define an exception
syntax
DECLARE
exceptionName EXCEPTION;
price := 10;
BEGIN
IF price < 0 THEN
RAISE exceptionName;
EXCEPTION
WHEN exceptionName THEN
statements;
END;
A ____ is a named block of PL/SQL code that is executed or fired automatically when a particular type of SQL is executed.
used with ___ and ___ statements
trigger
DML and DDL
Trigger syntax
CREATE OR REPLACE TRIGGER triggerName
BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
IF :NEW.rcolName != UPPER(:NEW.colName) THEN
:NEW.colName := UPPER(:NEW.colName);
END IF;
END;
Dynamic SQL composes a DML or DDL statement at __ __
it is useful when
____
____
run time
need to run a DDL
SQL can only be formed at run time
Dynamic SQL syntax
CREATE OR REPLACE PROCEDURE name (varName IN VARCHAR2,
varName2 IN VARCHAR2)
IS
l_string VARCHAR(240);
BEGIN
l_string := ‘ALTER TABLE ‘ || varName|| ‘ ADD CONSTRAINT PK_’|| varName1||’ Primary key (‘||varName2||’)’;
EXECUTE IMMEDIATE l_string;
END;
exec name(val1, val2);
DML with Dynamic SQL
syntax
CREATE OR REPLACE PROCEDURE name (varName IN VARCHAR2,
varName2 IN VARCHAR2,
l_oldVal IN VARCHAR2,
l_newVal IN VARCHAR2)
IS
l_string VARCHAR(240);
BEGIN
l_string := ‘UPDATE ‘||varName1||’ SET ‘|| varName2||’ = :a WHERE ‘ || varName2 || ‘ = :b’;
EXECUTE IMMEDIATE l_string USNG l_newVal, l_oldVal;
END;
exec name(val1, val2, val3, val4);
_____ is the process of consolidating data from different data sources to support business needs
integration
Main data resources
___
___
___
___
OLTP
application specific databases
event logs
social media
data quality
redundancy
accuracy
consistency
currency
timeliness
conformance
referential integrity
There are three techniques that form the building blocks of
any integration approach:
* Data ___
* Data ____
* Data ____
Consolidation
Federation
Propagation
data ____
Usually done for Data Warehousing using Extract Transform Load (ETL) techniques
___ only databases
has a ___ schema
consolidation
read only
fixed schema
data ___
is the creation of a virtual database that aggregates data from distributed sources giving them a common data model. It is an approach to data integration that provides a single source of data for front end applications
___ on demand
provides ___ ___ to consolidated data
____ (no ___ defined in advanced)
federation
consolidation
virtual view
flexible, no schema
Data _____
is the mechanism of copying data within the case hierarchy. By sharing
data among cases, you save time and provide relevant information to caseworkers. Data propagation is not limited to subcases. Data can also be propagated when creating spin- off cases. Data propagation happens on case creation
Data is ___ across the databases
can be ___ (data is consistent) or __
propagation
replicated
synchronous or asynchronous
___
meaning
is a process that is used to integrate data or move data from
one source to another
ETL
extract transform load
ETl - Extract
It is a common practice to first load the extracted data into an ____ storage area. this is often referred to as a “staging database” or “temporary repository
___ extract is when the whole data is copied
___ extract is when the deltas(mean new or modified records)- the changes are copied
intermediate
Static
Incremental
ETL - cleanse extracted data
Integrating different sources of data exposes ___ issues in the original data sets
Extract process should have processes that expose and reject
____ data
Extract process must __ __ __ __. Instead it will send an error report to the owners of the data source to fix the data at the source
quality
erroneous
not fix the errors
ETL - transform - record level transformation
Is a process of changing the ___ of the source tables through
- __: Restricting rows using the WHERE clause in the select statement)
- ___: Projecting columns from more than one table)
- ___: think of it as the opposite of joining. Convert tables that are not in the third normal form to smaller tables that do not have redundancies
- ____: converting data from more detailed to summary statistics on the
data
structure
Selection
Joining
Normalization
Aggregation
ETL - TRANSFORM – FIELD LEVEL TRANSFORMATION
Is concerned with changing the data in the fields (columns) that include
* ___ __change
* Use functions and algorithms to change data from source to
destination (for example change US dollars to Canadian
dollars)
Data type
ETL - load
insert the ___ ___ to tis final destination (usually data warehouse or data mart)
transformed data
____ ___ - Databases that are used to analyze and summarize data, and report on the business entities
data warehouse
data ___ is a subset of a data ___
mart, warehouse
data marts uses ___ models
centered around __ __
dimensional, not the same as relational
one subject
data warehouses
____ change over time
___ only
___ ___: focused on business entities
___: Comes from multiple systems and need to fit one structure
slow
read
Subject Oriented
Integrated
data marts have less data sources t/f
t
____ ___ - is a model that organizes
data into two main types of tables: fact tables and dimension tables.
the fact table is located at the center, surrounded by dimension tables
___ Table: has quantitative values
___ tables: has categories and their descriptions
All the primary keys of the dimension tables should be ___ keys (system generated keys).Most of the time it is a sequential integer
star schema
Facts
Dimensions
surrogate
GRAIN OF THE FACT TABLE
The minimum unit of reporting should be an ___ of all the dimensions
The grain is a crucial concept in data warehousing, influencing how data is aggregated and analyzed in conjunction with dimension
attributes
Think of the fact table as an associative entity. This means that its primary key is a composite key that contains all the foreign keys of the entities that associates it which is the dimension tables.
intersection
data mart
Longer duration means that the
fact table becomes much bigger
t/f
t
create star schema
create tables
CREATE SEQUENCE Dim_ID;
for each dimension table
INSERT INTO DIM_name
SELECT Dim_ID.NEXTVAL,col1, col2
FROM table
create table with %rowtype
create cursor with select statement that joins the dimension tables
group by dimension table ids
bulk collect into created table
—-cleanse
for i IN 1..table.last loop
if table(i).col is null then
table(i).col := 0;
end if;
end loop;
use forall to insert from table to fact table
