Different examples for training to exam Oracle Database 19c: Program with PL/SQL 1Z0-149. Part 1.
CHAPTER 1. "Introduction in PL/SQL"
CHAPTER 2. "Overview of PL/SQL"
CHAPTER 3. "Declaring PLSQL Variables"
CHAPTER 4. "Writing executable statements"
CHAPTER 5. "Interacting with Oracle DB Server"
CHAPTER 6. "Writing Control structure"
CHAPTER 7. "Working with Composite Data Types"
CHAPTER 8. "Using explicit cursors"
CHAPTER 9. "Handling Exceptions"
CHAPTER 1. "Introduction in PL/SQL"
Task 1.1.
1. What is PL/SQL.
Solution:
--1
PL/SQL is Oracle Corporation`s procedural language for SQL and the Oracle
relational database.
CHAPTER 2. "Overview of PL/SQL"
Task 2.1.
1. Write structure of PL/SQL block (indicate optional and mandatory sections):
Solution:
--1
DECLARE --optional
variables, cursors, user-defined exceptions, types, local subprograms;
BEGIN --mandatory
SQL and PL/SQL statements;
EXCEPTION --optional
exception handling;
END; --mandatory
Task 2.2.
1. List block types, write examples.
2. Which block types are subprograms?
3. List engines in PL/SQL block.
Solution:
--1
--Anonymous block - is an executable statement
[DECLARE]
...;
BEGIN
statements;
[EXCEPTION]
...;
END;
--Procedure
CREATE OR REPLACE PROCEDURE MY_PROCEDURE IS
...;
BEGIN
statements;
[EXCEPTION]
...;
END;
--Function
CREATE OR REPLACE FUNCTION MY_FUNCTION RETURN DATATYPE IS
...;
BEGIN
statements;
RETURN value;
[EXCEPTION]
...;
END;
--2
Subprograms are procedures and functions.
--3
PL/SQL block contain: PL/SQL engine and SQL engine.
Task 2.3.
For block:
BEGIN
DBMS_OUTPUT.PUT_LINE('my_text');
END;
1. How to create script at external file and run this in SQL Developer?
2. How to run this script in SQLPlus?
3. How to run this script without writing path in SQLPlus and SQL Developer?
Solution:
--1
Create file in directory "D:\text.sql" with this data:
BEGIN
DBMS_OUTPUT.PUT_LINE('my_text');
END;
Then write in SQL_developer command and run it:
@D:\text.sql
--2
--Login to database in sqlplus.
myuser/mypass@mydatabase (for example hr/hr@orclpdb)
--then write:
SET SERVEROUTPUT ON;
@D:\text.sql
/
--3
--For SQLPlus
--You can change directory in CMD Windows with command: "CD /D D:"
--Then in CMD Windows login to SQLPLUS by command: "sqlplus hr/hr@ORCLPDB"
--and then write in SQLPlus
@text.sql
/
--In SQL Developer write and run
cd d:
@text.sql
CHAPTER 3. "Declaring PLSQL Variables"
Task 3.1.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5),
COL2 VARCHAR2(100) DEFAULT 'zero' NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
COMMIT;
Then do next:
1. Write naming rules for variables. List namespaces and objects for them.
2. What will be if we try declare two variables with same name?
Declare and display into DBMS_OUTPUT:
'var1' with number type, null not allowed;
'var2' with varchar2 (with max length) type default 10;
'var 3' constant with date type;
'VAR_4' with default number 4 and declare with rules as 'var 3';
'begin' with varchar2 type default 'begin'.
3. List types of PL/SQL variables and non-PL/SQL variables. Explain them.
4. Declare and then use SELECT INTO:
- for variable with type as TAB1.COL2 that have NOT NULL and default 'zero';
- for variable, that have same type as this first variable (do not write
TAB1.COL2); which default value will be in this variable?
5. Write variable 'v' with subtype where allows values between -1 to 5. Explain.
6. What another name of bind variables. Using TAB1 create example of bind
variables (define with two ways) with types: number and string. Display this
variables manually. Display this variables automatically.
Solution:
--1
Begin with letter,
Name long <= 30 symbols (in Oracle 12c Release 2 (12.2) <= 128).
Contain only: A-Z; a-z; 0-9; _; $; #.
One user can`t create objects with same names in one namespace.
For name not allowed using Oracle`s server-reserved words.
In double quotation marks can write any name, but always must use this name
with "". Ordinary uppercase names declared with "", you can use without "" with
case-insensitive.
- USERS, ROLES;
- TABLES, VIEWS, SEQUENCES, PRIVATE SYNONYMS, USER-DEFINED TYPES;
- PUBLIC SYNONYMS;
- INDEXES;
- CONSTRAINTS.
--2
DECLARE
--var1 NUMBER; --will be error if we try use this variable
var1 NUMBER NOT NULL := 5*2; --need value, because NOT NULL
var2 VARCHAR2(32767) DEFAULT '10';
"var 3" CONSTANT DATE := TO_DATE('01.01.2000','DD.MM.YYYY');
"VAR_4" NUMBER := 4;
"begin" VARCHAR2(100) := 'begin';
BEGIN
DBMS_OUTPUT.PUT_LINE('Var1='||var1); --will be error, if declare two "var1"
DBMS_OUTPUT.PUT_LINE('Var2='||var2);
DBMS_OUTPUT.PUT_LINE('Var3='||"var 3");
DBMS_OUTPUT.PUT_LINE('VAR_4='||vAr_4);
DBMS_OUTPUT.PUT_LINE('begin='||"begin");
END;
--3
PL/SQL variables (some types have different maximum sizes in PL/SQL and SQL):
--Scalar - can hold a single value.
"DATA TYPE" "CATEGORY" "RANGE" "NOTES"
CHAR Characters Up to 32767 bytes Fixed length characters.
(default 1 byte)
VARCHAR2 Characters Up to 32767 bytes Variable character
LONG Characters Up to 2gb-1 bytes Only for backward compatibility
NUMBER(P, S) Number P = [1;38] If value: exceeds the precision,
S = [-84; 127] then error;if exceeds the scale,
then Oracle rounds it.
BINARY_INTEGER Number Integers: Faster than NUMBER
(PLS_INTEGER) [-2,147,483,648;
2,147,483,647]
BOOLEAN Boolean TRUE, FALSE, NULL
BINARY_FLOAT Number Represents floating-
point number in
IEEE 754 format. It
requires 5 bytes to
store the value.
BINARY_DOUBLE Number Represents floating-
point number in
IEEE 754 format. It
requires 9 bytes to
store the value.
DATE Between 4712 B.C. Stores year, month, day, hour,
and A.D. 9999 minute, second
TIMESTAMP(P) Between 4712 B.C. Stores year, month, day, hour,
and A.D. 9999 minute, second and fractional
second. P - precision: 1 - 9,
default precision = 6.
TIMESTAMP WITH Between 4712 B.C. Includes a time-zone
TIME ZONE and A.D. 9999
TIMESTAMP WITH Between 4712 B.C. Includes a local time-zone
LOCAL TIME ZONE and A.D. 9999
INTERVAL YEAR Stores interval of years and
TO MONTH months
INTERVAL DAY Stores interval of days, hours,
TO SECOND minutes and seconds
--LOB data types
--CLOB
A character large object containing single-byte or multibyte characters. Maximum
size is (4gb - 1) * (DB_BLOCK_SIZE). Stores national character set data.
--NCLOB
A character large object containing Unicode characters. Both fixed-width and
variable-width character sets are supported, both using the database national
character set. Maximum size is (4 gb - 1) * (database block size); used only for
storing Unicode data.
--BLOB
A binary large object. Maximum size is (4 gb - 1) * (DB_BLOCK_SIZE initializtion
parameter (8TB to 128 TB)). Can use for store images.
--BFILE
Binary data stored in an external file (up to 4gb). Can use for store video.
--Composite data types
Can contain different data types. For example: BOOLEAN + DATE + BLOB.
--4
DECLARE
var1 TAB1.COL2%TYPE NOT NULL := 'zero'; --NOT NULL and DEFAULT from TAB1 not
--will be passed.
var2 var1%TYPE := 'new'; --DEFAULT from var1 not will be passed,
--but NOT NULL will be passed.
BEGIN
SELECT COL2,
COL2
INTO var1,
var2
FROM TAB1
WHERE COL1 = 1;
END;
--5
DECLARE
--Range of values allows only for PLS_INTEGER and its subtypes
SUBTYPE PLS IS PLS_INTEGER RANGE -1..5;
v PLS;
BEGIN
v := -1;
DBMS_OUTPUT.PUT_LINE(v);
END;
--6
/* another name is HOST variables */
/* for bind variable do not assign scale for NUMBER */
VARIABLE var1 NUMBER
VAR var2 VARCHAR2(100)
--SET AUTOPRINT ON /* this is for automatic display bind variables */
BEGIN
SELECT COL1,
COL2
INTO :var1,
:var2
FROM TAB1
WHERE COL1 = 1;
END;
/
PRINT var1 var2
CHAPTER 4. "Writing executable statements"
Task 4.1.
1. What mean identifier? Write two examples, explain it.
2. What mean delimiters? Write three examples.
3. What mean literals? Write three examples (string, number, BOOL).
4. How to write a comment (2 ways)?
5. In PL/SQL block which functions can use and which functions can`t use?
6. How assign sequence to variable?
7. List two types of data type conversion.
8. List lexical units?
Solution:
--1
--Identifiers are the names to PL/SQL objects.
v_var1,
"v var 2" --for using this identifier need write exactly the same
--2
--A delimiter is a simple or compound symbol that has a special meaning to
--PL/SQL. For example, you use delimiters to represent arithmetic operations such
--as addition and subtraction.
; + - --simple
<> != || --compound
--3
--Literals is any value assigned to a variable.
v_var := 'my_name';
v_num := 55;
v_bool := FALSE;
--4
-- this is one line comment
/* this is multi-line comment */
--5
--can use single-row functions
v_var := LENGTH(my_var);
v_var := COALESCE(my_var, 1);
--can`t use DECODE, NVL2 and GROUP functions
--6
--starting 11g:
DECLARE
v_var NUMBER;
BEGIN
v_var := my_seq.NEXTVAL;
END;
--before 11g:
DECLARE
v_var NUMBER;
BEGIN
SELECT my_seq.NEXTVAL
INTO v_var
FROM DUAL;
END;
--7
--implicit conversion
--explicit conversion
--8
It is comment, delimiters, identifiers, literals.
Task 4.2.
1. Write anonymous block with nested block:
- in comments indicate outer block and inner block;
- in outer block define variable 'v1' NUMBER with default = 1 and variable
'v2' NUMBER with default = 2;
- in inner block define variable 'v2' with default value = 3.
Display in inner block at the DBMS_OUTPUT for 'v2' both values: 2 and 3. Then
after inner block display at the DBMS_OUTPUT for 'v2' values = 3.
Solution:
--1
BEGIN
<<outer>>
--<<outer>> --for block must be unique label
DECLARE --outer block
v1 NUMBER := 1;
v2 NUMBER := 2;
BEGIN
--inner block
DECLARE
v2 NUMBER := 3;
BEGIN
DBMS_OUTPUT.PUT_LINE('v2='||outer.v2);
DBMS_OUTPUT.PUT_LINE('v2='||v2);
outer.v2 := v2;
END;
DBMS_OUTPUT.PUT_LINE('v2='||v2);
END outer;
END;
CHAPTER 5. "Interacting with Oracle DB Server"
Task 5.1.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5), COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
COMMIT;
Then do next:
1. Explain how to use DML, DDL, DCL, TCL statements in PL/SQL.
2. Which precedence have column`s name and variable. Write example how to define
variable with name 'LENGTH' and also how to use function LENGTH.
3. Explain implicit cursors and for TAB1 write example uses implicit cursors
with different cursor`s attributes.
Solution:
--1
--We can use DML, TCL directly. DDL and DCL need use with dynamic SQL.
--2
--Column`s name have precedence over variable.
DECLARE
LENGTH NUMBER := -1;
BEGIN
DBMS_OUTPUT.PUT_LINE(STANDARD.LENGTH(LENGTH));
END;
--3
--A SQL (implicit) cursor is opened by the database to process each SQL
--statement that is not associated with an explicit cursor.
BEGIN
--attribute SQL%NOTFOUND
DELETE TAB1
WHERE COL1 = 999;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NOTFOUND');
END IF;
--attributes SQL%FOUND and SQL%ROWCOUNT
DELETE TAB1
WHERE COL1 = 1;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DELETE ROWS='||SQL%ROWCOUNT);
END IF;
--after COMMIT or ROLLBACK data in attributes will be lossed.
COMMIT; --ROLLBACK;
DBMS_OUTPUT.PUT_LINE('DELETE ROWS after COMMIT or ROLLBACK='||SQL%ROWCOUNT);
--SQL%ISOPEN always has the value FALSE
IF NOT SQL%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END;
CHAPTER 6. "Writing Control structure"
Task 6.1. IF...END_IF
1. Define number`s variables: v0 = 0, v1 = 1, v2 = 2, v3 = 3, v4 = NULL.
Then using 'IF...END IF' write 1 block where:
- if v0 = v1 then display 1;
- if v0 = v2 or v3 then display 23;
- if v0 <> v4 then display 'NOT_NULL';
in otherwise display 'nothing'.
Which condition will be completed?
Solution:
--1
--In IF...END statement first met condition will be completed. Otherwise will be
--executed section 'ELSE'.
DECLARE
v0 NUMBER := 0;
v1 NUMBER := 1;
v2 NUMBER := 2;
v3 NUMBER := 3;
v4 NUMBER := NULL;
BEGIN
IF v0 = v1 THEN
DBMS_OUTPUT.PUT_LINE(1);
ELSIF v0 = v2 OR v0 = v3 THEN
DBMS_OUTPUT.PUT_LINE(23);
ELSIF v0 <> COALESCE(v4, -1) THEN --this condtition will be completed
DBMS_OUTPUT.PUT_LINE('NOT_NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('nothing');
END IF;
END;
Task 6.2. CASE
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5), COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Which condition will be completed in CASE operator?
2. For TAB1 make query using CASE (indicate type of this case) where:
- if COL1 = 1 then display COL2;
- if 2 <= COL1 <= 3 then display 22;
- if COL1 <> NULL then display 'NOT_NULL';
in otherwise display 'nothing'.
3. Define number`s variables: v0 = 0, v1 = 1, v2 = 2, v3 = 3, v4 = NULL.
Then write 1 block where use two different CASE (write type):
- if v0 = v1 then display 1;
- if v0 = v2 or v3 then display 23;
- if v0 <> v4 then display 'NOT NULL';
in otherwise display 'nothing' (explain error "CASE not found").
Solution:
--1
--First met condition will be performed, otherwise will be run section 'ELSE'
--2 CASE expression (searched)
SELECT CASE
WHEN COL1 = 1
THEN COL2
WHEN COL1 BETWEEN 2 AND 3
THEN '22' --must be string because first condition have type - string
WHEN COL1 IS NOT NULL
THEN TO_CHAR('NOT NULL')
ELSE 'nothing'
END
FROM TAB1
--3
DECLARE
v0 NUMBER := 0;
v1 NUMBER := 1;
v2 NUMBER := 2;
v3 NUMBER := 3;
v4 NUMBER := NULL;
res VARCHAR2(10);
BEGIN
--CASE expression (searched case statement)
res := CASE TRUE --TRUE is default, you may not write it
WHEN v0 = v1
THEN TO_CHAR(1)
WHEN v0 IN (v2, v3)
THEN TO_CHAR(23)
WHEN v0 <> COALESCE(v4, -1)
THEN 'NOT NULL'
ELSE
'nothing'
END;
DBMS_OUTPUT.PUT_LINE(res);
--CASE statement (searched case statement)
CASE
WHEN v0 = v1
THEN DBMS_OUTPUT.PUT_LINE(1);
WHEN v0 IN (v2, v3)
THEN DBMS_OUTPUT.PUT_LINE(23);
WHEN v0 <> COALESCE(v4, -1)
THEN DBMS_OUTPUT.PUT_LINE('NOT NULL');
ELSE --if must be processed ELSE section and it is not specified, then
--will be error 'CASE not found'
DBMS_OUTPUT.PUT_LINE('nothing');
END CASE;
END;
Task 6.3. Loops
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5), COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Define x = 0, then write a BASIC loop, where add 1 to x on each step of cycle,
then stop cycle when x = 3.
2. Write a FOR loop from 1 to 5, write a FOR loop from 5 to 1. Which type have
loop counter?
3. Define x = 0, then write a WHILE loop, where add 1 to x on each step of cycle.
Stop loop when x >= 3.
4. Write FOR loop from 1 to 4 with label 'loop1'. Then inside 'loop1' write
nested loop FOR from 1 to current step of 'loop1' with label 'loop2' where
must be condition: if 'loop1' equal 3 then stop 'loop1'.
5. Make FOR loop from 1 to 4 where display all steps, but simulate step = 0.5 and
if step = 1,5 then go to next step (also do not display step = 1,5). Explain it.
6. Demonstrate and explain situation when we use FOR loop with counter name equal
variable name default 5.
7. Demonstrate GOTO statement (include nested block), write restrictions.
Solution:
--1
DECLARE
x NUMBER := 0;
BEGIN
LOOP
x := x + 1;
DBMS_OUTPUT.PUT_LINE(x);
EXIT WHEN x = 3;
END LOOP;
END;
--2
--integer FOR loop without reverse
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
--integer FOR loop with reverse
BEGIN
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
--3
--while loop
DECLARE
x NUMBER := 0;
BEGIN
WHILE x < 3 LOOP
DBMS_OUTPUT.PUT_LINE(x);
x := x + 1;
END LOOP;
END;
--4
BEGIN
<<loop1>>
FOR i IN 1..4 LOOP
<<loop2>>
<<loop2>>
FOR j IN 1..i LOOP
EXIT loop1 WHEN i = 3;
DBMS_OUTPUT.PUT_LINE('i='||i);
DBMS_OUTPUT.PUT_LINE(' j='||j);
END LOOP loop2;
END LOOP loop1;
END;
--5
DECLARE
step NUMBER := 0.5;
BEGIN
FOR i IN 1..4 LOOP
CONTINUE WHEN i * step = 1.5; --In this loop all actions on the step =1.5
--after CONTINUE will not be performed
DBMS_OUTPUT.PUT_LINE(i * step);
END LOOP;
END;
--6
DECLARE
i NUMBER := 5;
BEGIN
--FOR i IN 1..i LOOP --will be error
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE(i); --result = 1, 2, 3
END LOOP;
DBMS_OUTPUT.PUT_LINE(i); --result = 5
END;
--7
--GOTO statement can transfer control to label wrote before a block or statement.
--Cannot transfer control into an IF statement
BEGIN
NULL;
IF 1 = 1 THEN
GOTO my;
END IF;
BEGIN
<<my>> DBMS_OUTPUT.PUT_LINE(1); --not will be displayed
END;
<<my>>DBMS_OUTPUT.PUT_LINE(2); --will be displayed
--<<my>>DBMS_OUTPUT.PUT_LINE(3); --label must be unique in its scope
END;
CHAPTER 7. "Working with Composite Data Types"
Task 7.1. RECORD.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Where we can declare records? When we can`t create and use a RECORD type?
What about record based on table with invisible column?
2. Declare a PL/SQL record: programmer-defined records. Two ways: using TAB1 and
not (but structure how as TAB1).
Then into any record load row with fields: 4, 'four'. Display values of record.
Then insert into tab1 this record (two syntax).
Then load first row from TAB1 into this record (two syntax).
3. On which database objects we can create table-based record. Declare a PL/SQL
table-based record using TAB1.
Then load first row from TAB1 into this record (two syntax). Display values of
record.
Then update TAB1 where COL1 = 2 by values from record (two syntax).
4. Declare programmer-defined record using TAB1 with nested programmer-defined
record with 3 fields: NUMBER, VARCHAR2(100), DATE.
Insert into non-nested record values from TAB1 where COL1 = 1. Insert into nested
record values: 0, 'zero', '01.01.2000'. Display values of record.
5. How much fields can store a record?
6. How to test record for nullity, equality or inequality?
Solution:
--1
Can declare in declarative part of any block, subprogram or package.
Can`t create at schema level. Can`t use as attribute data type in ADT. RECORD
type in a package spec is incompatible with identically local RECORD type.
RECORD defined on table with virtual column can`t be inserted into this table.
Enabling column visible allows access in RECORD to this column, invisible - not.
--2
DECLARE
--using table`s type
TYPE type_rec1 IS RECORD (field1 TAB1.COL1%TYPE,
field2 TAB1.COL2%TYPE);
v_rec1 TYPE_REC1;
--without using table`s type
TYPE type_rec2 IS RECORD (field1 NUMBER DEFAULT 0,
field2 VARCHAR2(100) NOT NULL DEFAULT 'zero');
v_rec2 TYPE_REC2;
BEGIN
SELECT 4,
'four'
INTO v_rec1
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(v_rec1.FIELD1||' '||v_rec1.FIELD2);
INSERT INTO TAB1 VALUES v_rec1;
INSERT INTO TAB1(COL2, COL1) VALUES(v_rec1.FIELD2, v_rec1.FIELD1);
COMMIT;
SELECT *
INTO v_rec1
FROM TAB1
WHERE COL1 = 1;
SELECT COL2,
COL1
INTO v_rec1.FIELD2,
v_rec1.FIELD1
FROM TAB1
WHERE COL1 = 1;
END;
--3
--Can create table-based record on table or view.
DECLARE
v_rec TAB1%ROWTYPE;
BEGIN
SELECT *
INTO v_rec
FROM TAB1
WHERE COL1 = 1;
DBMS_OUTPUT.PUT_LINE(v_rec.COL1||' '||v_rec.COL2);
--
SELECT COL2, COL1
INTO v_rec.COL2, v_rec.COL1
FROM TAB1
WHERE COL1 = 1;
DBMS_OUTPUT.PUT_LINE(v_rec.COL2||' '||v_rec.COL1);
--
UPDATE TAB1
SET ROW = v_rec
WHERE COL1 = 2;
UPDATE TAB1
SET COL1 = v_rec.COL1,
COL2 = v_rec.COL2
WHERE COL1 = 2;
COMMIT;
END;
--4
DECLARE
TYPE nested_rec IS RECORD (nf1 NUMBER,
nf2 VARCHAR2(100),
nf3 DATE);
TYPE rec IS RECORD (field1 TAB1.COL1%TYPE,
field2 TAB1.COL2%TYPE,
field3 NESTED_REC);
v_rec REC;
BEGIN
SELECT COL1, COL2,
0, 'zero', TO_DATE('01.01.2000','DD.MM.YYYY')
INTO v_rec.FIELD1, v_rec.FIELD2,
v_rec.FIELD3.NF1, v_rec.FIELD3.NF2, v_rec.FIELD3.NF3
FROM TAB1
WHERE COL1 = 1;
DBMS_OUTPUT.PUT_LINE(v_rec.FIELD1 ||' '||
v_rec.FIELD2 ||' '||
v_rec.FIELD3.NF1 ||' '||
v_rec.FIELD3.NF2 ||' '||
v_rec.FIELD3.NF3);
END;
--5
Record can have as many fields as necessary.
--6
Cannot be tested
Task 7.2. INDEX BY tables (associative arrays)
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Explain structure of INDEX BY tables. What size of this collection?
2. Declare INDEX BY TABLES variables:
- with scalar values;
- with non scalar values (2 ways)
Assign to these collections two elements and display them.
3. Demonstrate how to make an associative array persistent for the life of a
database session. If we change one element what will see user in another session?
Solution:
--1
Have two columns:
- Primary key of integer or string data type.
- Column of scalar or record data type.
Size of this collection depends on the values that the key data type can hold.
--2
DECLARE
--scalar
TYPE type_ind_s IS TABLE OF NUMBER --TABLE OF VARCHAR2(100)
INDEX BY PLS_INTEGER; --INDEX BY VARCHAR2(100)
v_s type_ind_s;
--record
TYPE type_rec IS RECORD (f1 NUMBER DEFAULT 0,
f2 TAB1.COL2%TYPE);
TYPE type_ind_ns IS TABLE OF type_rec
INDEX BY PLS_INTEGER;
v_ns type_ind_ns;
--rowtype
TYPE type_row IS TABLE OF TAB1%ROWTYPE
INDEX BY PLS_INTEGER;
v_rt type_row;
BEGIN
--scalar
v_s(3) := 33;
v_s(-1) := -11;
DBMS_OUTPUT.PUT_LINE(v_s(3)||v_s(-1));
--record
v_ns(2).f1 := 22; v_ns(2).f2 := 'two';
v_ns(4).f1 := 44; v_ns(4).f2 := 'four';
DBMS_OUTPUT.PUT_LINE('record for index 2: '||v_ns(2).f1||' '||v_ns(2).f2);
DBMS_OUTPUT.PUT_LINE('record for index 4: '||v_ns(4).f1||' '||v_ns(4).f2);
--rowtype
FOR i IN 1..2 LOOP
SELECT *
INTO v_rt(i)
FROM TAB1
WHERE COL1 = i;
DBMS_OUTPUT.PUT_LINE(v_rt(i).COL1||' '||v_rt(i).COL2);
END LOOP;
END;
--3
--make package specification with array and populate it in package body
CREATE OR REPLACE PACKAGE PCK IS
TYPE arr IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v arr;
END PCK;
CREATE OR REPLACE PACKAGE BODY PCK IS
BEGIN
FOR i IN 1..3 LOOP
v(i) := i * 10;
END LOOP;
END PCK;
BEGIN
DBMS_OUTPUT.PUT_LINE(PCK.v(1)); --result 10
DBMS_OUTPUT.PUT_LINE(PCK.v(3)); --result 30
PCK.v(1) := 55;
DBMS_OUTPUT.PUT_LINE(PCK.v(1)); --result 55
END;
--in another session user will see v(1) = 10, not 55
BEGIN
DBMS_OUTPUT.PUT_LINE(PCK.v(1));
END;
Task 7.3.1. INDEX BY tables (associative arrays) methods. NUMBERS.
Examine block:
TYPE t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
v_v t;
v_v(-1) := -11;
v_v(0) := 99;
v_v(1) := 11;
v_v(3) := 22;
And make next operations:
1. Create block with this variables and list methods using with INDEX BY tables.
Write and explain result for each method.
Solution:
--1
DECLARE
TYPE t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
v_v t;
BEGIN
v_v(-1) := -11;
v_v(0) := 99;
v_v(1) := 11;
v_v(3) := 22;
--EXISTS(n). Returns TRUE if n-element exists in collection.
IF v_v.EXISTS(0) THEN
DBMS_OUTPUT.PUT_LINE('EXISTS=TRUE '||v_v(0)); --result=99
ELSE
DBMS_OUTPUT.PUT_LINE('EXISTS=FALSE');
END IF;
--COUNT. Count of indexes. If collection empty, then 0.
DBMS_OUTPUT.PUT_LINE('COUNT='||v_v.COUNT); --result=4
--FIRST. Minimum index. If collection empty, then NULL.
DBMS_OUTPUT.PUT_LINE('FIRST='||v_v.FIRST); --result=-1
--LAST. Maximum index. If collection empty, then NULL.
DBMS_OUTPUT.PUT_LINE('LAST='||v_v.LAST); --result=3
--PRIOR(n). If not have preceding index, then NULL.
DBMS_OUTPUT.PUT_LINE('PRIOR='||v_v.PRIOR(3)); --result=1
--NEXT(n). If not have succeeding index, then NULL.
DBMS_OUTPUT.PUT_LINE('NEXT='||v_v.NEXT(3)); --result=NULL
--DELETE(x, y). X must be <= Y.
v_v.DELETE(1, 2); --DELETE elements with indexes between 1 and 2.
v_v.DELETE(3); --DELETE element with index = 3
v_v.DELETE; --DELETE all elements
END;
Task 7.3.2. INDEX BY tables (associative arrays) methods. STRINGS.
1. Examine block and write result for each method.
DECLARE
TYPE t IS TABLE OF VARCHAR2(100)
INDEX BY VARCHAR2(100);
v_v t;
BEGIN
v_v('one') := '11';
v_v('two') := '22';
v_v('three') := '33';
v_v('four') := '44';
IF v_v.EXISTS(0) THEN
DBMS_OUTPUT.PUT_LINE('EXISTS='||v_v(0));
ELSE
DBMS_OUTPUT.PUT_LINE('EXISTS=FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE('COUNT='||v_v.COUNT);
DBMS_OUTPUT.PUT_LINE('FIRST='||v_v.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST='||v_v.LAST);
DBMS_OUTPUT.PUT_LINE('PRIOR='||v_v.PRIOR('two'));
DBMS_OUTPUT.PUT_LINE('NEXT='||v_v.NEXT('two'));
v_v.DELETE('one', 'two');
v_v.DELETE('two');
v_v.DELETE;
END;
2. What will happen for using methods (list these methods) if you change values
of NLS_SORT and NLS_COMP after populating an associative array indexed by string?
Solution:
--1
EXISTS = FALSE
COUNT = 4
FIRST = four
LAST = two
PRIOR = three
NEXT = NULL
DELETE('one', 'two') - will be deleted v_v('one'), v_v('three'), v_v('two')
DELETE('two') - will be deleted only v_v('two'). If not found, then error not be.
DELETE - will be deleted all elements
--2
FIRST, LAST, NEXT, PRIOR might return unexpected values or raise exceptions.
Task 7.4. NESTED TABLES
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Write difference between associative arrays and nested tables.
2. What is: null collection (nested table); empty collection (nested table)?
3. Create NESTED TABLES with scalar and non-scalar (2 ways) values. Add to these
NESTED TABLES: for scalar - 6 new values without using loop (2 in declare section
and 2 in execution section with directly assigning and 2 without), for
non-scalar - 4 values using loop. Then, using loop, display all values from these
nested tables.
For scalar variable: delete sixth element, delete fiveth element, then delete 2-4
elements, then delete all elements.
4. How to add new elements (but they have NOT NULL constraint) to collection?
Solution:
--1
- Nested table have not INDEX BY clause;
- Nested table can be used in SQL;
- Initialization required;
- Extend required;
- Can be stored in database.
--2
An uninitialized nested table variable is a null collection.
Empty nested table variable is initialized nested table variable without values.
--3
DECLARE
--scalar
TYPE type_s IS TABLE OF VARCHAR2(100); --TABLE OF NUMBER;
v_s type_s := type_s('one', 'two');
--record
TYPE type_rec IS RECORD (f1 NUMBER DEFAULT 0,
f2 TAB1.COL2%TYPE);
TYPE type_ns IS TABLE OF type_rec;
v_ns type_ns := type_ns();
--rowtype
TYPE type_row IS TABLE OF TAB1%ROWTYPE;
v_rt type_row := type_row();
BEGIN
--scalar
v_s.EXTEND(2);
v_s(3) := 'three';
v_s(4) := 'four';
v_s.EXTEND(2, 4);
FOR i IN v_s.FIRST..v_s.LAST LOOP
DBMS_OUTPUT.PUT_LINE('v_s('||i||') '||v_s(i));
END LOOP;
--delete block
v_s.DELETE(6); --delete 6th element
v_s.TRIM; --v_s.TRIM(1); --delete 5th element
v_s.DELETE(2, 4); --delete 2-4 elements
v_s.DELETE; --delete all elements
DBMS_OUTPUT.PUT_LINE(v_s.COUNT);
--record
FOR i IN 1..2 LOOP
v_ns.EXTEND;
SELECT COL1, COL2
INTO v_ns(i)
FROM TAB1
WHERE COL1 = i;
DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);
END LOOP;
FOR i IN 3..4 LOOP
v_ns.EXTEND;
v_ns(i).f1 := i; v_ns(i).f2 := i;
DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);
END LOOP;
--rowtype
FOR i IN 1..2 LOOP
v_rt.EXTEND;
SELECT COL1, COL2
INTO v_rt(i)
FROM TAB1
WHERE COL1 = i;
DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
END LOOP;
FOR i IN 3..4 LOOP
v_rt.EXTEND;
v_rt(i).COL1 := i; v_rt(i).COL2 := i;
DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
END LOOP;
END;
--4
Use EXTEND(n, i)
Task 7.5. Assigning set operation results to nested table.
1. Declare fisrt nested table (with values: NULL, NULL, 1, 1, 2, 2, 3, 3), second
nested table (with values: 2, 2, 3, 3, 4, 4, NULL, NULL). Then demonstrate:
- MULTISET for nested tables, write result.
- SET for nested tables, write result.
Solution:
--1
DECLARE
TYPE nt IS TABLE OF NUMBER; --type for two variables must be same
a nt := nt(NULL, NULL, 1, 1, 2, 2, 3, 3);
b nt := nt(2, 2, 3, 3, 4, 4, NULL, NULL);
res nt; --result type must be as type of variables
BEGIN
--MULTISET EXCEPT. Returns a nested table whose elements are in the first
--nested table but not in the second nested table. Keyword ALL is default.
res := a MULTISET EXCEPT /* ALL */ b; --1 1
res := a MULTISET EXCEPT DISTINCT b; --1
--MULTISET INTERSECT. Returns a nested table whose values are common in the
--two input nested tables. Keyword ALL is default.
res := a MULTISET INTERSECT /* ALL */ b; --NULL, NULL, 2, 2, 3, 3
res := a MULTISET INTERSECT DISTINCT b; --NULL, 2, 3
--MULTISET UNION. Returns a nested table whose values are those of the two
--input nested tables. Keyword ALL is default.
--NULL,NULL,1,1,2,2,3,3,2,2,3,3,4,4,NULL,NULL
res := a MULTISET UNION /* ALL */ b;
res := a MULTISET UNION DISTINCT b; --NULL,1,2,3,4
--SET. Function takes a nested table and returns a nested table of
--the same data type without duplicates.
res := SET(a); --NULL, 1, 2, 3
res := SET(b); --2, 3, 4, NULL
FOR i IN res.FIRST..res.LAST LOOP
DBMS_OUTPUT.PUT_LINE(COALESCE(TO_CHAR(res(i)), 'NULL'));
END LOOP;
END;
Task 7.6. NESTED TABLE stored in the database.
1. Create table TABN with NESTED TABLE stores in database with columns: COL1
NUMBER, COL2 VARCHAR2(100), COL3 NESTED TABLE with numbers. Add to TABN 1 new row.
Solution:
--1
DROP TABLE TABN;
CREATE OR REPLACE TYPE type_n IS TABLE OF NUMBER;
CREATE TABLE TABN (COL1 NUMBER,
COL2 VARCHAR2(100),
COL3 TYPE_N)
NESTED TABLE COL3 STORE AS COL3_TABLE;
INSERT INTO TABN (COL1, COL2, COL3)
VALUES (1, 'one', TYPE_N(11, 22, 33));
COMMIT;
SELECT * FROM TABN;
Task 7.7. VARRAYS
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. What difference between VARRAY and other collections?
2. Define variables VARRAYS: with 4 elements with scalar and non-scalar
types (2 ways). Fill this VARRAYs by values using loop. Display values.
For any variable: delete fourth element, then delete 2-3 elements, then delete
all elements.
Solution:
--1
Varray can`t extend over its size. Varray have bounded number of elements.
Bound of varray must be a positive integer.
--2
DECLARE
--scalar
TYPE type_s IS VARRAY(4) OF NUMBER; --VARRAY(4) OF VARCHAR2(100);
v_s type_s := type_s();
--record
TYPE type_rec IS RECORD (f1 NUMBER DEFAULT 0,
f2 TAB1.COL2%TYPE);
TYPE type_ns IS VARRAY(4) OF type_rec;
v_ns type_ns := type_ns();
--rowtype
TYPE type_row IS VARRAY(4) OF TAB1%ROWTYPE;
v_rt type_row := type_row();
BEGIN
--scalar
FOR i IN 1..v_s.LIMIT LOOP
v_s.EXTEND;
v_s(i) := i;
DBMS_OUTPUT.PUT_LINE('v_s('||i||') '||v_s(i));
END LOOP;
--delete block
v_s.TRIM; --delete 4th element, but DELETE(a) NOT ALLOWED for varrays
v_s.TRIM(2); --delete 2 elements from the end of a collection
v_s.DELETE; --delete all elements, but DELETE(a, b) NOT ALLOWED for varrays
--record
FOR i IN 1..2 LOOP
v_ns.EXTEND;
SELECT COL1, COL2
INTO v_ns(i)
FROM TAB1
WHERE COL1 = i;
DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);
END LOOP;
FOR i IN 3..4 LOOP
v_ns.EXTEND;
v_ns(i).f1 := i; v_ns(i).f2 := i;
DBMS_OUTPUT.PUT_LINE('v_ns('||i||') '||v_ns(i).f1||' '||v_ns(i).f2);
END LOOP;
--rowtype
FOR i IN 1..2 LOOP
v_rt.EXTEND;
SELECT COL1, COL2
INTO v_rt(i)
FROM TAB1
WHERE COL1 = i;
DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
END LOOP;
FOR i IN 3..4 LOOP
v_rt.EXTEND;
v_rt(i).COL1 := i; v_rt(i).COL2 := i;
DBMS_OUTPUT.PUT_LINE('v_rt('||i||') '||v_rt(i).COL1||' '||v_rt(i).COL2);
END LOOP;
END;
Task 7.8. VARRAY stored in the database.
1. Create table TABV with VARRAY stored in database with columns: COL1
NUMBER, COL2 VARCHAR2(100), COL3 VARRAY with numbers. Add to TABV 1 new row.
2. Using anonymous block demonstrate:
- which method does not raise the predefined exception COLLECTION_IS_NULL;
- which method usefull only on varrays. Explain it.
Solution:
--1
DROP TABLE TABV;
CREATE OR REPLACE TYPE type_n IS VARRAY(2) OF NUMBER;
CREATE TABLE TABV (COL1 NUMBER,
COL2 VARCHAR2(100),
COL3 TYPE_N);
INSERT INTO TABV (COL1, COL2, COL3)
VALUES (1, 'one', TYPE_N(11, 22));
COMMIT;
SELECT * FROM TABV;
--2
DECLARE
TYPE var IS VARRAY(3) OF NUMBER;
v var;
BEGIN
IF NOT v.EXISTS(1) THEN --only EXISTS for NULL collection not raise exception
DBMS_OUTPUT.PUT_LINE('Not exception');
END IF;
v := var(1, NULL);
--Method LIMIT return max available quantity of elements (bound) in VARRAY.
--For INDEX BY tables and NESTED TABLES this method return NULL.
DBMS_OUTPUT.PUT_LINE(v.LIMIT); --result = 3
--but COUNT method return quantity of initialized elements with values or
--with NULL. For varrays, FIRST always returns 1 and LAST always equals COUNT
DBMS_OUTPUT.PUT_LINE(v.COUNT); --result = 2
END;
Task 7.9. Collections 'SYS.'.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. Create SYS. collections with number and string types. Explain length of types.
In number`s collection put values of TAB1.COL1 from first two rows.
In string`s collection put value 'zero'.
Using query with number`s collection put count of elements from number`s
collection to variable 'res'. Display value of this variable.
Solution:
DECLARE
--Stores varrays of NUMBERs
--VARRAY(32767) OF NUMBER
n SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
--Stores varrays of VARCHAR2s
--VARRAY(32767) OF VARCHAR2(4000)
s SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
res NUMBER;
BEGIN
--
FOR i IN 1..2 LOOP
n.EXTEND;
SELECT COL1
INTO n(i)
FROM TAB1
WHERE COL1 = i;
DBMS_OUTPUT.PUT_LINE(n(i));
END LOOP;
--
s.EXTEND;
s(s.COUNT) := 'zero';
DBMS_OUTPUT.PUT_LINE(s(s.COUNT));
--
SELECT COUNT(*)
INTO res
FROM TABLE(n);
DBMS_OUTPUT.PUT_LINE(res);
END;
Task 7.10. Collection comparisons.
1. Explain compare collection variables to the value NULL, to each
other (for equality and inequality), with SQL Multiset conditions:
- associative array;
- nested table;
- varray.
Solution:
--1
-- associative array
Cannot compare associative array variables.
--
For nested tables and varrays use the IS[NOT] NULL operator when comparing to
the NULL value.
--
Two nested table variables are equal if and only if they have the
same set of elements (in any order) and have not NULL values.
NULL nested table <> NULL nested table
If two nested table variables have the same nested table type, and that nested
table type does not have elements of a record type, then you can compare the
two variables for equality or inequality with the relational operators equal(=)
--For nested tables can use MULTISET conditions
DECLARE
TYPE nt IS TABLE OF NUMBER;
n0 nt;
n1 nt := nt();
n2 nt := nt(NULL);
n3 nt := nt(1, 2);
n4 nt := nt(1, 2, 3, 3, NULL);
BEGIN
IF n0 IS A SET THEN
DBMS_OUTPUT.PUT_LINE('IS A SET');
ELSIF n0 IS NOT A SET THEN
DBMS_OUTPUT.PUT_LINE('IS NOT A SET');
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
IF n1 IS A SET THEN DBMS_OUTPUT.PUT_LINE('IS A SET'); END IF;
IF n2 IS A SET THEN DBMS_OUTPUT.PUT_LINE('IS A SET'); END IF;
IF n3 IS A SET THEN DBMS_OUTPUT.PUT_LINE('IS A SET'); END IF;
IF n4 IS NOT A SET THEN DBMS_OUTPUT.PUT_LINE('IS NOT A SET'); END IF;
--
IF n0 IS EMPTY THEN
DBMS_OUTPUT.PUT_LINE('IS EMPTY');
ELSIF n0 IS NOT EMPTY THEN
DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY');
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
IF n1 IS EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS EMPTY'); END IF;
IF n2 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
IF n3 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
IF n4 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
--
IF 1 MEMBER OF n0 THEN
DBMS_OUTPUT.PUT_LINE('MEMBER');
ELSIF 1 NOT MEMBER OF n0 THEN
DBMS_OUTPUT.PUT_LINE('NOT MEMBER');
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
IF n1 IS EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS EMPTY'); END IF;
IF n2 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
IF n3 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
IF n4 IS NOT EMPTY THEN DBMS_OUTPUT.PUT_LINE('IS NOT EMPTY'); END IF;
--
IF 1 MEMBER OF n0 THEN
DBMS_OUTPUT.PUT_LINE('MEMBER');
ELSIF 1 NOT MEMBER OF n0 THEN
DBMS_OUTPUT.PUT_LINE('NOT MEMBER');
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
IF 1 NOT MEMBER n1 THEN DBMS_OUTPUT.PUT_LINE('NOT MEMBER'); END IF;
IF 1 MEMBER OF n2 THEN
DBMS_OUTPUT.PUT_LINE('MEMBER');
ELSIF 1 NOT MEMBER OF n2 THEN
DBMS_OUTPUT.PUT_LINE('NOT MEMBER');
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
IF 1 MEMBER n3 THEN DBMS_OUTPUT.PUT_LINE('MEMBER'); END IF;
IF 1 MEMBER OF n4 THEN DBMS_OUTPUT.PUT_LINE('MEMBER'); END IF;
--
IF n1 SUBMULTISET OF n0 THEN DBMS_OUTPUT.PUT_LINE('SUBMULTISET'); END IF;
IF n0 SUBMULTISET n1 OR
n0 NOT SUBMULTISET n1 THEN
DBMS_OUTPUT.PUT_LINE('SUBMULTISET OR NOT SUBMULTISET');
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
IF n3 SUBMULTISET n4 THEN DBMS_OUTPUT.PUT_LINE('SUBMULTISET'); END IF;
END;
Task 7.11. Using multidimensional collections.
1. Create number associative array 'aaa' with elements. Each element must
consist of number associative array 'a' with two elements (for example: 10, 20;
30, 40; ...). Fill array 'aaa' with different ways. Delete last element of array
'a' from last element of 'aaa'. Display all 'a' elements from array 'aaa'.
2. Remake point 1, but use nested table and instead DELETE replace first element
from 'aaa' by last element from 'aaa' and replace (without loop) all 'a' elements
from first 'aaa' element by value = 0.
Solution:
--1
DECLARE
TYPE t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE ttt IS TABLE OF t
INDEX BY PLS_INTEGER;
a t;
aaa ttt;
BEGIN
a(1) := 10;
a(2) := 20;
aaa(1) := a;
--
aaa(2)(1) := 30;
aaa(2)(2) := 40;
--
aaa(aaa.LAST).DELETE(aaa(aaa.LAST).LAST);
--
FOR i IN aaa.FIRST..aaa.LAST LOOP
FOR j IN aaa(i).FIRST..aaa(i).LAST LOOP
DBMS_OUTPUT.PUT_LINE(aaa(i)(j));
END LOOP;
END LOOP;
END;
--2
DECLARE
TYPE t IS TABLE OF NUMBER;
TYPE ttt IS TABLE OF t;
a t := t(10, 20);
aaa ttt := ttt(a, t(30, 40));
BEGIN
a.DELETE;
a.EXTEND(2);
a(1) := 50;
a(2) := 60;
aaa.EXTEND;
aaa(3) := a;
--
aaa.EXTEND;
aaa(4) := t(70, 80);
--
aaa(aaa.FIRST) := aaa(aaa.LAST);
aaa(aaa.FIRST)(1) := 0;
aaa(aaa.FIRST)(2) := 0;
--alternative is aaa(aaa.FIRST) := t(0, 0);
--
FOR i IN aaa.FIRST..aaa.LAST LOOP
FOR j IN aaa(i).FIRST..aaa(i).LAST LOOP
DBMS_OUTPUT.PUT_LINE(aaa(i)(j));
END LOOP;
END LOOP;
END;
Task 7.12. Operations with collections.
Examine next block:
DECLARE
TYPE a1 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE a2 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE n1 IS TABLE OF NUMBER;
TYPE n2 IS TABLE OF NUMBER;
TYPE v1 IS VARRAY(3) OF NUMBER;
TYPE v2 IS VARRAY(3) OF NUMBER;
acc1 a1;
acc2 a1;
acc3 a2;
acc1(1) := 10;
acc2(1) := 20;
acc3(1) := 30;
nest1 n1 := n1(1);
nest2 n1 := n1(2);
nest3 n2 := n2(3);
vv1 v1 := v1(1);
vv2 v1 := v1(2);
vv3 v2 := v2(3);
s1 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1);
s2 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(2);
s3 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(3);
BEGIN
acc1(1) := 10;
acc2(1) := 20;
acc3(1) := 30;
acc1 := acc2;
acc1 := acc3;
acc1(1) := acc3(1);
acc1 := a1(1);
acc1 := a1();
acc1 := a1;
acc1(1) := NULL;
nest1 := nest2;
nest1 := nest3;
nest1(1) := nest3(1);
nest1(1) := NULL;
nest1 := n2();
nest1 := n2(NULL);
nest1 := n1;
nest3 := n2(NULL);
vv1 := vv2;
vv1 := vv3;
vv1(1) := nest3(1);
vv1 := v1();
vv1 := v1(NULL);
s1 := s2;
s1 := s3;
END;
1. By commenting exclude all wrong rows.
2. Demonstrate how to use collections (each have 2 elements) in SQL queries?
Explain COLUMN_VALUE.
Solution:
--1
DECLARE
TYPE a1 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE a2 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE n1 IS TABLE OF NUMBER;
TYPE n2 IS TABLE OF NUMBER;
TYPE v1 IS VARRAY(3) OF NUMBER;
TYPE v2 IS VARRAY(3) OF NUMBER;
acc1 a1;
acc2 a1;
acc3 a2;
--acc1(1) := 10;
--acc2(1) := 20;
--acc3(1) := 30;
nest1 n1 := n1(1);
nest2 n1 := n1(2);
nest3 n2 := n2(3);
vv1 v1 := v1(1);
vv2 v1 := v1(2);
vv3 v2 := v2(3);
s1 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1);
s2 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(2);
s3 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(3);
BEGIN
acc1(1) := 10;
acc2(1) := 20;
acc3(1) := 30;
acc1 := acc2;
--acc1 := acc3;
acc1(1) := acc3(1);
--acc1 := a1(1);
acc1 := a1();
--acc1 := a1;
acc1(1) := NULL;
nest1 := nest2;
--nest1 := nest3;
nest1(1) := nest3(1);
nest1(1) := NULL;
--nest1 := n2();
--nest1 := n2(NULL);
--nest1 := n1;
nest3 := n2(NULL);
vv1 := vv2;
--vv1 := vv3;
vv1(1) := nest3(1);
vv1 := v1();
vv1 := v1(NULL);
s1 := s2;
s1 := s3;
END;
--2
--If datatype of elements is a scalar type, then the nested table or varray has
--a single column of that type, called COLUMN_VALUE.
--Associative array can`t use in SQL query. VARRAY and NESTED TABLE can use if
--they stored in database.
CREATE OR REPLACE TYPE schema_nest IS TABLE OF NUMBER;
CREATE OR REPLACE TYPE schema_varr IS VARRAY(2) OF NUMBER;
DECLARE
--TYPE loc IS TABLE OF NUMBER;
--v_loc loc := loc(1,2); !!! local nested table can`t use in SQL-query
nt schema_nest := schema_nest(1,2);
va schema_varr := schema_varr(1,2);
res NUMBER;
BEGIN
SELECT MIN(COLUMN_VALUE)
INTO res
FROM TABLE(nt);
DBMS_OUTPUT.PUT_LINE(res);
SELECT MAX(COLUMN_VALUE)
INTO res
FROM TABLE(va);
DBMS_OUTPUT.PUT_LINE(res);
END;
Task 7.13. Characteristics of PL/SQL Collection Types.
For next types:
- associative array (or index-by table);
- nested table;
- variable-size array (varray);
answer on questions:
1. Amount of elements (unbounded or bounded)?
2. Subscript type?
3. Dense or sparse?
4. Where created?
5. Can be object type attribute?
6. When collection type can be ADT?
Solution:
--1
A = Unbounded; N = Unbounded; V = Bounded.
--2
A = String or integer; N = Integer; V = Integer.
--3
A = Either; N = Starts dense, can become sparse; V = Always dense.
--4
A = Only in PL/SQL block; B = Either in PL/SQL block or at schema level;
C = Either in PL/SQL block or at schema level.
--5
A = No; B = Yes; C = Yes.
--6
Nested table and varray types can be ADT (schema-level ADT: sometimes called
root ADTs) if they standalone collection types.
CHAPTER 8. "Using explicit cursors"
Task 8.1. Explicit Cursor
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER(5) DEFAULT 0,
COL2 VARCHAR2(100) NOT NULL);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(2, 'two');
INSERT INTO TAB1(COL1, COL2) VALUES(NULL, 'three');
COMMIT;
Then do next:
1. What different in declaring and managing for implicit and explicit cursors?
2. Which commands move pointer from cursor and where.
3. Declare cursor 'cur' for TAB1. Fetch cursor into variables with different 3
types. Also for any type use in this cursor 4 attributes:
- if cursor not opened, then work with cursor;
- if cursor have row, then display values from COL1, COL2 at DBMS_OUTPUT;
- stop loop with two ways (if count of rows > 2 or cursor do not have more rows).
4. Use loop (2 ways) with cursor for TAB1 without explicitly opening cursor.
5. For TAB1 write cursor where COL2 = parameter 'p'. Also declare variable 'p'
with default 'two'. Work with cursor by 2 different ways using for parameter
variable 'p' (in first way use named notation, in second way - positional).
Explain the scope of cursor parameters and parameter`s mode.
6. For TAB1 write cursor where COL2 = 'three' and then UPDATE TAB1.COL1 using
cursor. Explain it.
Solution:
--1
Implicit cursor declared and managed by PL/SQL for all DML and PL/SQL SELECT
statements. Explicit cursor declared and managed by the programmer.
--2
OPEN - move pointer to first row in cursor, you must CLOSE cursor before reopen.
FETCH - read data from current row and move pointer to the next row.
--3
DECLARE
CURSOR cur IS
SELECT COL1, COL2 FROM TAB1;
cur_row cur%ROWTYPE;
cur_num NUMBER;
cur_col2 TAB1.COL2%TYPE;
BEGIN
IF NOT cur%ISOPEN THEN
OPEN cur;
END IF;
LOOP
FETCH cur INTO cur_row;
EXIT WHEN cur%NOTFOUND OR cur%ROWCOUNT > 2;
IF cur%FOUND THEN
DBMS_OUTPUT.PUT_LINE
('loop1 '||cur_row.COL1||' '||cur_row.COL2);
END IF;
END LOOP;
CLOSE cur;
OPEN cur;
LOOP
FETCH cur INTO cur_num, cur_col2;
DBMS_OUTPUT.PUT_LINE('loop2 '||cur_num||' '||cur_col2);
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
--4
DECLARE
CURSOR cur IS
SELECT COL1, COL2 FROM TAB1;
BEGIN
FOR i IN cur LOOP
DBMS_OUTPUT.PUT_LINE(i.COL1||' '||i.COL2);
END LOOP;
FOR i IN (SELECT COL1, COL2 FROM TAB1) LOOP
DBMS_OUTPUT.PUT_LINE(i.COL1||' '||i.COL2);
END LOOP;
END;
--5
--The scope of cursor parameters is local to the cursor, meaning that they can be
--referenced only within the query used in the cursor declaration
DECLARE
CURSOR cur(p IN VARCHAR2 DEFAULT 'one') IS --only IN parameters allowed
--CURSOR cur(p IN VARCHAR2 DEFAULT p); --will be error
SELECT COL1, COL2
FROM TAB1
WHERE COL2 = p;
p VARCHAR2(100) DEFAULT 'two';
cur_row cur%ROWTYPE;
BEGIN
OPEN cur(p => p);
--OPEN cur();
--OPEN cur;
LOOP
FETCH cur INTO cur_row;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_row.COL1||' '||cur_row.COL2);
END LOOP;
CLOSE cur;
FOR i IN cur(p) LOOP
DBMS_OUTPUT.PUT_LINE(i.COL1||' '||i.COL2);
END LOOP;
END;
--6
DECLARE
CURSOR cur IS
SELECT COL1, COL2
FROM TAB1
WHERE COL2 = 'three'
FOR UPDATE;
BEGIN
FOR i IN cur LOOP
UPDATE TAB1
SET COL1 = 3
WHERE CURRENT OF cur; --update will be if TAB1.ROWID = cur.ROWID
END LOOP;
COMMIT;
END;
CHAPTER 9. "Handling Exceptions"
Task 9.1. Exceptions.
1. What mean exception? List exception categories. What about TCL commands in
exception`s section?
2. Write block where define exception E1, then initiate this exception and display
at DBMS_OUTPUT text 'E1'.
3. Explain parameters of procedure RAISE_APPLICATION_ERROR. Write block with
variable VV = 2 and if VV > 1 then initiate this procedure not in EXCEPTION
section and write EXCEPTION section where handle this exception.
4. Write block (with exception section) where declare exception MY_ERROR_0 and
bind to number (in comment write acceptable range of numbers) of system exception
ORA-01476 "divisor is equal to zero". Then initiate this error and handle.
5. Repeat step 4, but exception declare in package.
6. Repeat step 4, but exception declare for demonstrate pass an exception from
nested block to parent block.
7. How WHEN clause works in EXCEPTION section?
Solution:
--1
Exception is a situation fired at abnormal behavior of PLSQL program.
Categories: internally defined (unnamed system exception), predefined (named
system exception), user-defined exception.
Commit, rollback, savepoint allowed in exception`s section.
--2
DECLARE
E1 EXCEPTION;
BEGIN
RAISE E1;
EXCEPTION
WHEN E1
THEN DBMS_OUTPUT.PUT_LINE('E1');
END;
--3
RAISE_APPLICATION_ERROR(X, Y, Z).
X is number in range [-20999; -20000] (remember that in some Oracle packages
numbers in range [-20005; -20000] assigned to system exceptions). Y is a message
with length <= 2048 bytes (symbols over this length will be ignored). Z is a
boolean value: TRUE - adds an error to stack, FALSE (default) - it replaces the
existing error.
DECLARE
vv NUMBER := 2;
BEGIN
IF vv > 1 THEN
RAISE_APPLICATION_ERROR(-20000, 'MyText');
END IF;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -20000 THEN
NULL;
DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
RAISE_APPLICATION_ERROR(-20000, 'MyText2');
END IF;
END;
--4
DECLARE
my_error_0 EXCEPTION;
PRAGMA EXCEPTION_INIT (my_error_0, -01476); --range: -1..-1000000
x NUMBER;
BEGIN
x := 1 / 0;
EXCEPTION
WHEN my_error_0 THEN
DBMS_OUTPUT.PUT_LINE('THIS IS my_error_0');
END;
--5
CREATE OR REPLACE PACKAGE MY_PCK_FOR_SYSTEM_ERRORS IS
my_error_0 EXCEPTION;
PRAGMA EXCEPTION_INIT (my_error_0, -01476);
END;
DECLARE
x NUMBER;
BEGIN
x := 1 / 0;
EXCEPTION
WHEN MY_PCK_FOR_SYSTEM_ERRORS.my_error_0 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
--6
BEGIN
DECLARE
my_error_0 EXCEPTION;
PRAGMA EXCEPTION_INIT (my_error_0, -01476);
x NUMBER;
BEGIN
BEGIN
x := 1 / 0;
EXCEPTION
WHEN my_error_0 THEN
DBMS_OUTPUT.PUT_LINE('NestedBlock: '||SQLCODE);
RAISE;
END;
EXCEPTION
--redundant exceptions do not allowed in one exception block
--WHEN ZERO_DIVIDE THEN
-- DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE detected');
WHEN my_error_0 THEN
DBMS_OUTPUT.PUT_LINE('my_error_0 visible');
RAISE;
END;
EXCEPTION
WHEN OTHERS THEN
--WHEN my_error_0 THEN --will be fail, because name "my_error_0" not visible
DBMS_OUTPUT.PUT_LINE(SQLERRM); --ORA-01476: divisor is equal to zero
END;
--7
WHEN clause writes in EXCEPTION section and starts if name of initialized
exception matches with name of exception from WHEN clause. Only one first matched
WHEN clause can be activated. For not matched names of exceptions can use
WHEN OTHERS clause, that must be last of all WHEN clauses. If WHEN OTHERS is not
specified in the EXCEPTION section and in another WHEN clauses not matched names
of exceptions, then exception will be passed to next block or to calling
environment.
Task 9.2. Functions for EXCEPTION section.
1. Make block where in EXCEPTION section have nested block with function that
returns code of last initiated exception.
Which result of this function will be: in parent block, in nested block,
in EXCEPTION section of nested block, in EXCEPTION section of parent block.
2. Make block with function that returns message binded to code of error. Which
length of this message? Write result for this function without argument (explain
it), with argument = 1 and with argument = 0.
3. Make block with function without arguments that returns message of current
error. Which length of this message?
4. Write procedure and write block with function that returns text about
programm stack and rows numbers. Write the execution`s result of this block.
5. Write procedure and write block with function that returns text about current
call stack and rows numbers. Write the result of this block.
Solution:
--1
DECLARE
exc EXCEPTION;
BEGIN
--result = 0
DBMS_OUTPUT.PUT_LINE('1. before exception = '||SQLCODE);
RAISE exc;
EXCEPTION
WHEN exc THEN
--result = 1
DBMS_OUTPUT.PUT_LINE('2. for exc before INNER block = '||SQLCODE);
--Inner block
BEGIN
--result = 1
DBMS_OUTPUT.PUT_LINE('3. for exc before INNER block = '||SQLCODE);
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--result = 100
DBMS_OUTPUT.PUT_LINE('4. for INNER block = '||SQLCODE);
END;
--result = 1
DBMS_OUTPUT.PUT_LINE('5. for exc after INNER block = '||SQLCODE);
END;
--2
--length <= 512 byte
BEGIN
DBMS_OUTPUT.PUT_LINE(SQLERRM);--Without argument SQLERRM take argument from
--SQLCODE, and if SQLCODE = 0, then SQLERRM
--return ORA-0000: normal, successful completion
DBMS_OUTPUT.PUT_LINE(SQLERRM(1)); --User-Defined Exception
DBMS_OUTPUT.PUT_LINE(SQLERRM(0)); --ORA-0000: normal, successful completion
END;
--3
--length <= 2000 bytes. More preferable, than SQLERRM.
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
(DBMS_UTILITY.FORMAT_ERROR_STACK);--ORA-01403: no data found
END;
--4
--Result:
--ORA-06512: at "HR.PRC_WITH_ERR", line 4
--ORA-06512: at line 2
CREATE OR REPLACE PROCEDURE PRC_WITH_ERR IS
BEGIN
DBMS_OUTPUT.PUT_LINE('This is my PRC_WITH_ERR');
RAISE NO_DATA_FOUND; --ORA-06512: at "HR.PRC_WITH_ERR", line 4
END;
BEGIN
PRC_WITH_ERR; --ORA-06512: at line 2
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
--5
--Result:
----- PL/SQL Call Stack -----
-- object line object
-- handle number name
-- 00007FF7C8AEEF88 6 procedure HR.PRC_WITH_ERR
-- 00007FF7CBACE178 2 anonymous block
CREATE OR REPLACE PROCEDURE PRC_WITH_ERR IS
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK); --line 6
END;
BEGIN
PRC_WITH_ERR; --line 2
EXCEPTION --exception not will be performed
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE);
END;
Task 9.3. Functions for EXCEPTION section. Part 2.
Do next operations:
CREATE OR REPLACE PROCEDURE PRC_WITH_ERR IS
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('--A*************************');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('--B*************************');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
PRC_WITH_ERR;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('--C*************************');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
Then do next:
1. What will be the result in DBMS_OUTPUT after executing the BEGIN...END block?
Solution:
--1
--B*************************
----- PL/SQL Call Stack -----
object line object
handle number name
00007FF70DEB97E8 3 anonymous block
NULL --this is DBMS_UTILITY.FORMAT_ERROR_BACKTRACE between BEGIN and EXCEPTION
--A*************************
----- PL/SQL Call Stack -----
object line object
handle number name
00007FF70DF936F8 7 procedure HR.PRC_WITH_ERR
00007FF70DEB97E8 5 anonymous block
ORA-06512: at "HR.PRC_WITH_ERR", line 3
--C*************************
----- PL/SQL Call Stack -----
object line object
handle number name
00007FF70DEB97E8 9 anonymous block
ORA-06512: at "HR.PRC_WITH_ERR", line 9
ORA-06512: at "HR.PRC_WITH_ERR", line 3
ORA-06512: at line 5