Oracle PL/SQL Nested Tables

These are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns.

A nested table can be considered as a single-column table that can either be in memory, or as a column in a database table. A nested table is quite similar to a VARRAY with the exception that the order of the elements is not static. Elements can be deleted or added anywhere in the nested table where as a VARRAY can only add or delete elements from the end of the array. Nested Table is known as a sparse collection because a nested table can contain empty elements.

Nested tables are a superior choice when:

You need to delete or update some elements, but not all the elements at once.

The index values are not consecutive.

We don’t have any predefined upper bound for index values.

Nested Table	Varrays	Index-by-tables
Declare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Table Of student.last_name%Type; last_name_tab last_name_type :=last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab.EXTEND; last_name_tab(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END;	Declare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Varray(10) OF student. last_name%TYPE; last_name_varray last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_varray. EXTEND; last_name_varray(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_varray(v_counter)); END LOOP; END;	Declare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Table Of student.last_name%TYPE Index By Binary_Integer; last_name_tab last_name_type; v_counter INTEGER := 0; Begin For name_rec IN name_cur Loop v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; Dbms_Output.Put_Line ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END;
Type number_tab as table of number; numb_list2 number_tab := number_tab(23,56,34,890,21); SQL> declare 2 type number_tab is table of number; 3 numb_list number_tab := number_tab(23,56,34,890,21); 4 begin 5 for indx in numb_list.first..numb_list.last loop 6 dbms_output.put_line(numb_list(indx)); 7 end loop; 8 numb_list.delete(2); 9 numb_list.delete(4); 10 for indx in numb_list.first..numb_list.last loop 11 if numb_list.exists(indx) then 12 dbms_output.put_line(numb_list(indx)); 13 end if; 14 end loop; 15 end; 16 / CREATE TYPE BeerType AS OBJECT ( name CHAR(20), kind CHAR(10), color CHAR(10) ); CREATE TYPE BeerTableType AS TABLE OF BeerType; CREATE TABLE Manfs ( name CHAR(30), addr CHAR(50), beers BeerTableType )NESTED TABLE beers STORE AS BeerTable;	Create Type varray_address_typ AS Varray(2) OF Varchar2(50); / Create Table customers_with_varray ( id Integer Primary key, first_name Varchar2(10), last_name Varchar2(10), addresses varray_address_typ ); Insert Into customers_with_varray Values ( 1, 'Steve', 'Brown', varray_address_typ( '2 State Street, Beantown, MA, 12345', '4 Hill Street, Lost Town, CA, 54321' ) ); Update customers_with_varray SET addresses = varray_address_typ( '3 New Street, Middle Town, CA, 123435', '4 Hill Street, Lost Town, CA, 54321' ) Where id = 1;

Nested table	Varrays	Index-by-tables
No maximum Length(unbounded)	Maximum length	No maximum Length(unbounded)
A nested table is similar to a VARRAY except that it is a sparse collection, meaning that it can have deleted elements contained in the collection. (Sparsity means whether there can be gaps in between the subscripts.)	A VARRAY is a dense collection, meaning that you can only add or remove objects from the end.	It can always be sparse
Can be stored in Database	Can be stored in Database	Cannot be stored in Database
Initialization: Via constructor, fetch, assignment	Via constructor, fetch, assignment	Automatic, when declared

SQL Outer Join

SQL OUTER Joins:

A NORMAL join finds values from two tables that are in a relation to each other. Usually, this relation is equality (=), but it can also be all sorts of operations that either return true or false. The important thing is that a NORMAL join only returns matching rows from both (joined) tables. Obviously, a row whose column-value is not found in the other table's joined column is not returned at all. But, sometimes, we need to show these rows as well.

The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.

A LEFT OUTER JOIN results in a table which includes joined rows and any unmatched rows from the table listed to the left. The keyword LEFT in a LEFT OUTER JOIN tells you that the resulting table will include unmatched rows from the table to the LEFT of the keyword JOIN in the FROM clause of the query. In other words when we use LEFT OUTER JOIN clause we point out that we want to get all rows from the left table listed in our FROM clause, even if they don’t have a match in the right table.

A RIGHT OUTER JOIN is just reverse of LEFT OUTER JOIN.

According to old oracle syntax(which uses ‘+’ for outer join operations) :

An outer join uses a (+) on the side of the operator (equality operator) where we want to have nulls returned if no value matches.

The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.

According to http://www.oracle.com/technology/products/oracle9i/
daily/may31.html

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
• To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

Full outer joins – They are like a left or right outer join, but all the rows from both row sources are returned - if they "join" - fine - they will be joined - but if they don't have a match in the other table by the join key(s) they are returned anyway.

Employee Table:
LN	D_Id
Kamal	31
Jones	33
Singh	33
Smith	34
Robinson	34
Jasper	36

Department Table:
Department Name	D_Id
Sales	31
Engineering	33
Clerical	34
Marketing	35

Example left outer join (ANSI 92 standard syntax):
SELECT distinct *

FROM employee

LEFT OUTER JOIN

department

ON employee.D_id = department.D_id

Example left outer join (non-standard syntax):

SELECT *

FROM employee

WHERE employee.D_id = department.D_id(+)

Example right outer join (ANSI 92 standard syntax):

SELECT *

FROM employee

RIGHT OUTER JOIN

department

ON employee.D_id = department.D_id
Example right outer join (non-standard syntax):

SELECT *

FROM employee

WHERE employee.D_id )+) = department.D_id

LN	D_Id	Department Name	D_Id
Smith	34	Clerical	34
Jones	33	Mathematicsg	33
Robinson	34	Clerical	34
Singh	33	mathematics	33
Kamal	31	Sales	31
NULL	NULL	Marketing	35

Example full outer join (ANSI 92 standard syntax):
SELECT *

FROM employee

FULL OUTER JOIN

department

ON employee.D_id = department.D_id

LN	D_Id	Department Name	D_Id
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Jasper	36	NULL	NULL
Singh	33	Engineering	33
Kamal	31	Sales	31
NULL	NULL	Marketing	35

SQL Inner Join

SQL Inner Join/ SQL Natural Join:

Inner Join = Equi Join = Natural Join is the usually same.

An inner join has an ON clause that specifies the join conditions. Rather than having a huge rowset in memory and filtering the data, this join extracts only the data that meets the join conditions. The keyword INNER is optional because a JOIN clause will be INNER by default. An inner join is called equi-join when all the columns are selected with a *, or natural join otherwise.

inner or natural joins - just a "regular" join.

Employee table:
LN	D_Id
Kamal	31
Jones	33
Singh	33
Smith	34
Robinson	34
Jasper	36

Department Table:
Department Name	D_Id
Sales	31
Engineering	33
Clerical	34
Marketing	35

Example inner join (ANSI 92 standard syntax):

SELECT * FROM employee
INNER JOIN department
ON employee.D_id = department.D_id

Example inner join (non-standard syntax):

SELECT * FROM employee, department
WHERE employee.D_id = department.D_id

Inner join result :

LN	D_Id	Department Name	D_Id
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Singh	33	Engineering	33
Kamal	31	Sales	31

SQL Self Join

SQL Self Join:

Joining a table to itself.

select a.ename, b.ename

from emp a, emp b

where a.mgr = b.empno;

SQL Cross Join

SQL Cartesian product:

You will be able to find Cartesian product with a Cartesian join. When we join every row of a table to every row of another table we get Cartesian join

SQL Cross Join:

Cross joins, where every row from one table is matched with every row from another

Cartesian join and Cross join are one and the same thing.

If T1 and T2 are two sets then cross join = T1 X T2.

Examples of a cross join:

SELECT *
FROM emp CROSS JOIN dept

SELECT *
FROM emp, dept;

In the first example above it is explicitly written that it is a CROSS JOIN but in the second one it is implicit.

Join Using Multiple Tables

Join Using Multiple Tables(more than 2)

Questions: I am looking for the resources/examples on using Oracle9i ANSI joins on multiple tables.

Most of the examples i found are using just two tables to explain the join. I'd appreciate if you could give the examples of writing complex multitable joins for Oracle9i.

I want to join tables A,B,C,D,E in such a way that tables C,D & E will have OUTER join with table A on a key column. I have used this type of join in Informix and now trying to convert it into Oracle9i.

and we said...

The same syntax you used in Informix, given that it was "ansi" style is supported in Oracle9i. There are not "advanced resources" on this cause you are making it harder then it is. It really is as straight forward as it looks.

Just use parens and keep nesting the joins:

ops$tkyte@ORA9I.WORLD> create table a ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table b ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table c ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table d ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> create table e ( x int );

Table created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> insert into a values ( 1 );

1 row created.

ops$tkyte@ORA9I.WORLD> insert into b values ( 1 );

1 row created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> select *
2 from (((( a inner join b on a.x = b.x ) left outer join c on a.x = c.x )
3 left outer join d on a.x = d.x ) left outer join e on a.x = e.x )

SQL Equijoin

SQL Equijoin:

The join condition determines whether the join is an equijoin or a non equijoin. when we relate two tables on a join condition by equating the columns from the tables, it is an equijoin. when we relate two tables on a join condition by an operator other than equality it is an non-equijoin. A query may contain equijoins as well as non-equijoins.

Examples of Equijoin:

Select emp.deptno, bonus.comm
from emp bonus
where emp.ename = bonus.ename

SELECT * FROM emp
INNER JOIN dept
ON emp.DeptID = dept.DeptID

SQL Functions

Please refer the below mentioned link for SQL FUNCTIONS. This is an excellent link for getting deep insight of all available SQL FUNCTIONS.

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88893

There are different types of SQL functions:

• single_row_function


1. numeric_function


2. character_function


3. data_mining_function


4. datetime_function
   


5. conversion_function


6. collection_function


7. XML_function


8. miscellaneous_single_row_function


• aggregate_function


• analytic_function


• object_reference_function
  


• model_function


• user_defined_function

Some of the SQL Functions with examples are as below:

LOWER(string) Converts a string to all lowercase characters

SELECT LOWER('MCMILLAN') "Lowercase" FROM DUAL;

INITCAP(string) Converts a string to initial capital letters

SELECT INITCAP('the apple') "Capitals" FROM DUAL;

UPPER(string) Converts a string to all uppercase characters

SELECT UPPER(last_name) "Uppercase" FROM emp;

LENGTH(string) Returns the number of characters in a string

SELECT LENGTH('SACHIN') "Length in characters" FROM DUAL;

AVG(expression) Returns the average of the values in a set of rows

SELECT AVG(salary) "Average" FROM emp;

Average

--------

6400

COUNT(expression) or COUNT(*)

Returns the number of rows in the set

If you include an expression, COUNT returns only the number of rows in which the expression is not null.

COUNT(*) counts all rows.

MAX(expression) Returns the largest value from a set of rows

SELECT MAX(salary) "Maximum" FROM emp;

Maximum

----------

29000

MIN(expression) Returns the smallest value from a set of rows

SELECT MAX(salary) "Maximum" FROM emp;

SUM(expression) Adds the value for all rows in the query or for all rows with the same values for columns listed in the GROUP BY clause

SELECT SUM(salary) "Total" FROM emp;

Total

----------

67140

ABS(number) Removes the sign, if any, returning a positive value

The following example returns the absolute value of -15:

SELECT ABS(-5) "Absolute" FROM DUAL;

Absolute

----------

5

GREATEST(value1,value2, …)

Returns the largest of the values in the list

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')
"Greatest" FROM DUAL;

This function is used for multiple values in the same row.

Greatest

--------

HARRY

LEAST(value1,value2, …)

Returns the smallest of the values in the list. This function is used for multiple values in the same row.

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL;

LEAST

------

HAROLD

ROUND(number, decimal places)

Rounds a value to the specified number of decimal places

The following example rounds a number to one decimal point:

SELECT ROUND(18.193,1) "Round" FROM DUAL;

Round

----------

18.2

The following example rounds a number one digit to the left of the decimal point:

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

Round

----------

20

The following examples illustrate the difference between rounding NUMBER and floating-point number values. NUMBER values are rounded up (for positive values), whereas floating-point numbers are rounded toward the nearest even value:

SELECT ROUND(1.5), ROUND(2.5) FROM DUAL;

ROUND(1.5) ROUND(2.5)

---------- ----------

2 3

SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

ROUND(1.5F) ROUND(2.5F)

----------- -----------

2.0E+000 2.0E+000

TRUNC(number,decimal places)

Cuts off a value at the specified number of decimal places

The following examples truncate numbers:

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

Truncate

----------

15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

Truncate

----------

10

SUBSTR(string, starting value, number of characters)

Extracts a portion of a string

If the starting value is 0, it is treated as 1. If the starting-value is negative, Oracle counts backward from the end of the string. If the starting value is positive, Oracle counts forward from the beginning of the string.

SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;

Substring

---------

CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;

Substring

---------

CDEF

Assume a double-byte database character set:

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL;

Substring with bytes

--------------------

CD

ADD_MONTHS(date, number of months)

Adds the specified number of months to the date value

(subtracts months if the number of months is negative)

If the result would be a date beyond the end of the month, Oracle returns the last day of the resulting month.

The following example returns the month after the hire_date in the sample table employees:

SELECT TO_CHAR(

ADD_MONTHS(hire_date,1),

'DD-MON-YYYY') "Next month"

FROM employees

WHERE last_name = 'Baer';

Next Month

-----------

07-JUL-1994

LAST_DAY(date) Returns the last day of the month that contains the date

The following statement determines how many days are left in the current month.

SELECT SYSDATE,

LAST_DAY(SYSDATE) "Last",

LAST_DAY(SYSDATE) - SYSDATE "Days Left"

FROM DUAL;

SYSDATE Last Days Left

--------- --------- ----------

30-MAY-01 31-MAY-01 1

MONTHS_BETWEEN(date1,date2)

Returns the difference between two dates expressed as whole

and fractional months

If date1 is earlier than date2, the result is negative.

The result also takes into account time differences between the two values.

The following example calculates the months between two dates:

SELECT MONTHS_BETWEEN

(TO_DATE('02-02-1995','MM-DD-YYYY'),

TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"

FROM DUAL;

Months

----------

1.03225806


NEXT_DAY(date, day name)

Returns the date of the first day of the specified name that is

later than the date supplied

This example returns the date of the next Tuesday after February 2, 2001:

SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY"

FROM DUAL;

NEXT DAY

-----------

06-FEB-2001

ROUND (datetime, format)

Returns the date-time rounded to the unit specified by the

format, or to the nearest day if no format is supplied

Note: For details on available formats, see the full

description of functions (below).

The following example rounds a date to the first day of the following year:

SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR')

"New Year" FROM DUAL;

New Year

---------

01-JAN-01

SYSDATE Returns the current date-time from the server where the database is located

The following example returns the current operating system date and time:

SELECT TO_CHAR

(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"

FROM DUAL;

NOW

-------------------

04-13-2001 09:45:51


TRUNC(datetime) Removes the time component from a date-time value. The following example truncates a date:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')

"New Year" FROM DUAL;

New Year

---------

01-JAN-92

TO_CHAR(date, format)

Converts a date to a string in the specified format

TO_CHAR(number, format)

Converts a number to a string in the specified format.

The following statement uses implicit conversion to combine a string and a number into a number:

SELECT TO_CHAR('01110' + 1) FROM dual;

TO_C

----

1111

TO_DATE(string, format)

Converts a string to a date using the specified format.

The following example converts a character string into a date:

SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.',

'NLS_DATE_LANGUAGE = American')

FROM DUAL;

TO_DATE('

---------

15-JAN-89

TO_NUMBERstring, format)

Converts a string to a number using the optional format if specified.

The following examples convert character string data into a number:

UPDATE employees SET salary = salary +

TO_NUMBER('100.00', '9G999D99')

WHERE last_name = 'Perkins';

SQL Introduction

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.

The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.

SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories: DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create	It is used to create objects(tables, views) in the database.
Alter	It is used to alter the structure of the database objects.
Drop	delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).
Truncate	remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)
Grant	assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select	Select data from the database
Insert	It is used to insert data into a table
Update	It is used to update existing data within a table
Delete	It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit	It will end the current transaction making the changes permanent and visible to all users..
Savepoint	It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back
Rollback	It will undo all the changes made by the current transaction.
Set- Transaction	It is used to define the properties of a transaction.