PL / SQL

--Veriable decleration
DECLARE
  eName1 VARCHAR2(10) := 'Hello';
BEGIN
  dbms_output.put_line(eName1);
END;
/
--Veriable decleration constant
--The value can not changed
DECLARE
  eName1 CONSTANT VARCHAR2(10) := 'Hello';
BEGIN
  dbms_output.put_line(eName1);
END;
/
--Not Null
DECLARE
  --A variable declared NOT NULL must have an initialization assignment
  eName1 VARCHAR2(10) NOT NULL:= 'Hello';
BEGIN
  dbms_output.put_line(eName1);
  --eName1 := null; -- Error -- Expression is of wrong type
END;
/
--------------------------------------------------------------------------
--Number type veriable
DECLARE
  eNumber NUMBER(10):= 4500;
BEGIN
  dbms_output.put_line(eNumber);
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--veriable Concatination||
DECLARE
  eNumber VARCHAR(10):= 'Hello';
BEGIN
  dbms_output.put_line('This Name is ' || eNumber);
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Convert number value to char string
DECLARE
  eSalary NUMBER(10):= 4560;
BEGIN
  dbms_output.put_line('This Emp Salary is ' || To_CHAR(eSalary));
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- *SQL
-- For writing a programm in file and execute just "ed f1(file name)"
-- For getting out put in *SQL Command -- set serverout on
DECLARE
  eMsg Varchar(100);
BEGIN
  eMsg := 'Hello PL SQL World';
  dbms_output.put_line(eMsg);
END;
/
--------------------------------------------------------------------------
--Memory veriable
DECLARE
  VSalary NUMBER(10,2);
  EName   VARCHAR(50);
BEGIN
  SELECT SALARY,
    FIRST_NAME
  INTO VSalary,
    EName
  FROM EMPLOYEES
  WHERE FIRST_NAME = 'Lex';
  dbms_output.put_line('The Salary of '|| EName || TO_CHAR(VSalary));
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- IF Condition
DECLARE
  vSalary NUMBER(10,2);
BEGIN
  SELECT salary INTO vSalary FROM employees WHERE first_name = 'Neena';
  IF(vSalary > 6000) THEN
    dbms_output.put_line('Earn more then tom' || vSalary);
  ELSE
    IF (vSalary = 17000) THEN
      dbms_output.put_line('Earn NO then tom' || vSalary);
    ELSE
      dbms_output.put_line('Earn LESS then tom' || vSalary);
    END IF;
  END;
  /
  --------------------------------------------------------------------------
  --------------------------------------------------------------------------
-- LOOP
-- Three kind of loop available in PL SQL 1. LOOP ... LOOP END , 2. While (Condition) Loop .. End Loop
-- FOR ... END LOOP
DECLARE
  c1 NUMBER := 0;
BEGIN
  LOOP
    dbms_output.put_line('Hello');
    c1:=c1 +1;
    EXIT
  WHEN c1 = 5;
  END LOOP;
END;
/
--------------------------------------------------------------------------
 --------------------------------------------------------------------------
-- LOOP
-- While (Condition) Loop .. End Loop
DECLARE
  c1 NUMBER := 10;
BEGIN
  WHILE (c1 < 13)
  LOOP
    dbms_output.put_line('Hello ' || c1);
    c1:=c1 +1;
  END LOOP;
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- LOOP
-- FOR Loop .. End Loop
BEGIN
  FOR i IN 10..13
  LOOP
    dbms_output.put_line('Hello ');
  END LOOP;
END;
/
--------------------------------------------------------------------------
 --------------------------------------------------------------------------
-- Impliciti Cursors
BEGIN
  DELETE FROM employees WHERE employee_id = 120;
  IF SQL%FOUND THEN
    dbms_output.put_line('The row to be deleted was not found');
  ELSE
    dbms_output.put_line('The row was deleted');
  END IF;
END;
/
--------------------------------------------------------------------------
 --------------------------------------------------------------------------
-- Impliciti Cursors
BEGIN
  DELETE FROM employees WHERE employee_id = 120;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('The row to be deleted was not found');
  ELSE
    dbms_output.put_line('The row was deleted');
  END IF;
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Impliciti Cursors
BEGIN
  DELETE FROM employees WHERE employee_id = 120;
  IF SQL%FOUND THEN
    dbms_output.put_line('The row to be deleted was not found');
  ELSE
    dbms_output.put_line(SQL%ROWCOUNT ||'The row was deleted');
  END IF;
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Explicit Cursors (Work area for sql)
-- There are some steps need to follow to work on explicit cursor
-- 1.Declare a cursor
-- 2.open the cursor
-- 3.Fetching the cursor
-- 4.Close cursor
--
-- Proble raise and why we need to use explicit cursor please take a look on below query which return more then one row
-- If more then depno available wiht 20 then which row will be set in vName memory variable.
BEGIN
  DECLARE
    vName VARCHAR2(45);
  BEGIN
    SELECT ename INTO vName FROM employees WHERE deptno = 20;
    dbms_output.put_line(SQL%ROWCOUNT ||'The row was deleted');
  END;
  /
-- Correct SQL -------------------
BEGIN
  DECLARE
    vName VARCHAR2(45);
    CURSOR c1
    IS
      SELECT FIRST_NAME FROM employees WHERE DEPARTMENT_ID = 50;
  BEGIN
    OPEN c1;
    LOOP
      FETCH c1 INTO vName;
      --statement to exit loop
      dbms_output.put_line(vName);
    END LOOP;
  END;
  /
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Data type and row type
BEGIN
  DECLARE
    vEmp employees%ROWTYPE;
  BEGIN
    SELECT * INTO vEmp FROM employees WHERE EMPLOYEE_ID = 101;
    dbms_output.put_line(vEmp.FIRST_NAME);
    dbms_output.put_line(vEmp.EMPLOYEE_ID);
  END;
  /
--------------------------------------------------------------------------

Java Collections

  The core collection interfaces. 

Collection represents the group of objects. Depending on the method of storing and retrieving
Collections are basically divided into three parts – Set, Map and List.
Where Set does not contain duplicate values, Map contains key value type of data whereas List can have a duplicate values stored in it sequentially.

Collection —
The root of the collection hierarchy.
A collection represents a group of objects known as its elements.
Some types of collections allow duplicate elements, and others do not.
Some are ordered and others are unordered.
The Java platform doesn’t provide any direct implementations of this interface but provides implementations of more specific sub interfaces, such as Set and List.

Set — a collection that cannot contain duplicate elements

List —
An ordered collection (sometimes called a sequence).
Lists can contain duplicate elements.
The user of a List generally has precise control over where in the list each element is inserted and can access elements by their integer index (position).

Queue —
A collection used to hold multiple elements prior to processing.
Besides basic Collection operations, a Queue provides additional insertion, extraction, and inspection operations.
Queues typically, but do not necessarily, order elements in a FIFO (first-in, first-out) manner.
Among the exceptions are priority queues, which order elements according to a supplied comparator or the elements’ natural ordering.

Map —
An object that maps keys to values.
A Map cannot contain duplicate keys; each key can map to at most one value.
If you’ve used Hashtable, you’re already familiar with the basics of Map.

HashSet :
This class implements the Set interface, backed by a hash table (actually a HashMap instance).

It makes no guarantees as to the iteration order of the set; in particular, it does not guarantee that the order will remain constant over time.

This class permits the null element.

LinkedHashSet :
Hash table and linked list implementation of the Set interface, with predictable iteration order.

This implementation differs from HashSet in that it maintains a doubly-linked list running through all of its entries.

This linked list defines the iteration ordering, which is the order in which elements were inserted into the set (insertion-order).

Note that insertion order is not affected if an element is re-inserted into the set. (An element e is reinserted into a set s if s.add(e) is invoked when s.contains(e) would return true immediately prior to the invocation).

Interface SortedSet:
A set that further guarantees that its iterator will traverse the set in ascending element order, sorted according to the natural ordering of its elements (see Comparable), or by a Comparator provided at sorted set creation time.

Several additional operations are provided to take advantage of the ordering. (This interface is the set analogue of SortedMap).

TreeSet:
This class implements the Set interface, backed by a TreeMap instance. This class guarantees that the sorted set will be in ascending element order, sorted according to the natural order of the elements (seeComparable), or by the comparator provided at set creation time, depending on which constructor is used.

ArrayList:
Resizable-array implementation of the List interface.

Implements all optional list operations, and permits all elements, including null. In addition to implementing the List interface, this class provides methods to manipulate the size of the array that is used internally to store the list.

Vector:
This class is roughly equivalent to ArrayList except it is Synchronized.

LinkedList:
Linked list implementation of the List and Queue interfaces. Implements all optional operations, and permits all elements (including null).

 HashMap:
The HashMap class is roughly equivalent toHashtable, except that it is unsynchronized and permits nulls

HashTable:
Hash table based implementation of the Map interface.
This implementation provides all of the optional map operations, and permits null values and the null key. This class makes no guarantees as to the order of the map; in particular, it does not guarantee that the order will remain constant over time.
LinkedHashMap:
Hash table and linked list implementation of the Map interface, with predictable iteration order.
This implementation differs from HashMap in that it maintains a doubly-linked list running through all of its entries.
This linked list defines the iteration ordering, which is normally the order in which keys were inserted into the map (insertion-order).
Note that insertion order is not affected if a key is re-inserted into the map.

TreeMap:
This class guarantees that the map will be in ascending key order, sorted according to the natural order for the key’s class (seeComparable), or by the comparator provided at creation time, depending on which constructor is used.

Note :To synchronize the Set Concrete classes, use below code:

Set s = Collections.synchronizedSet(CONCREATECLASS_OBJECT);

To Synchronize the List Concreate classes :

List list = Collections.synchronizedList(CONCREATECLASS_OBJECT);

Note:- http://www.cs.cmu.edu/~adamchik/15-121/lectures/Collections/collections.html

Basic approach to choosing a collection

List-:
Most cases where you just need to store or iterate through a "bunch of things" and later iterate through them.

Set:-

• Remembering "which items you've already processed", e.g. when doing a web crawl;
• Making other yes-no decisions about an item, e.g. "is the item a word of English", "is the item in the database?" , "is the item in this category?" etc.

Map:-
Used in cases where you need to say "for a given X, what is the Y"? It is often useful for implementing in-memory caches or indexes. For example:

• For a given user ID, what is their cached name/User object?
• For a given IP address, what is the cached country code?
• For a given string, how many instances have I seen?

Queue:-

• Often used in managing tasks performed by different threads in an application (e.g. one thread receives incomming connections and puts them on a queue; other "worker" threads take connections off the queue for processing);
• For traversing hierarchical structures such as a filing system, or in general where you need to remember "what data to process next", whilst also adding to that list of data;
• Related to the previous point, queues crop up in various algorithms, e.g. build the encoding tree for Huffman compression.