Constraints

It Enforce Rule On Table

We can create constraint at the time of Creating Table

      1. Column Level Declaration 
2. Table Level Declaration

1. We can provide own name. 

          Format : [part_of_project_name-table_name-column_name-constraint_type]
           Example : gmind_emp_id_pk _uk _ck _fk

            2. system name name                                                

             Format  :  sys_cn 
Example : SYS_C405323

We can create constraint after the table has been created

Types

 

Primary Key

Not allowed Null value
Not allowed you to enter duplicate value
Ex :  Employee_id,  Student_id

Unique Key

allowed Null value
Not allowed you to enter duplicate value
Ex  :  Contact_no, email

  Foreign Key

 It will allowed you to enter NULL and Duplicate value.

c1(pk)  :  1  2   3    
c2(fk)  :  1  1  2  3       4  Not allowed


Allowed you to enter duplicate value and null value

Not Null

It will not allowed you to enter null value

   Check

 You can check your own condition

Simple Table for understanding


------------------------------------
             Duplicate    NULL
------------------------------------
Primary Key      X       X
Unique Key       X       Allowed 
Foreign Key   Allowed    Allowed
------------------------------------


creating table with all constraint type

 
 Create table my_stu_details
(
S_id       Number(2),
S_name     Varchar2(30) Not Null,   --SYS_Cn
s_mail     Varchar2(30),
s_gender   char(1),
s_did      Number(3),
Constraint my_stu_sid_pk   PRIMARY KEY (s_id),
constraint my_stu_mail_uk  UNIQUE  (s_mail)  ,
Constraint my_stu_gen_ck   CHECK (s_gender IN ('M', 'F', 'm', 'f')),
Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
);


DESC my_stu_details;

------------------------------------
Name       Type            Nullable
------------------------------------
S_ID     NUMBER(2)
S_NAME   VARCHAR2(30)
S_MAIL   VARCHAR2(30)      Y
S_GENDER  CHAR(1)           Y
S_DID    NUMBER(3)         Y
------------------------------------


Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (1   , 'name1' , 'name1@gmail.com' , 'm'     , 60   );



 Select * from my_stu_details;


 
Validating Primary Key

 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (1   , 'name2' , 'name2@gmail.com' , 'F'     , 90   );

ORA-00001: unique constraint (HR.MY_STU_SID_PK) violated

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (NULL, 'name2' , 'name2@gmail.com' , 'F'     , 90   );
                    
ORA-01400: cannot insert NULL into (HR.MY_STU_SID_PK) violated

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (2   , 'name2' , 'name2@gmail.com' , 'F'     , 90   );

Select * from my_stu_details;

 
Validating NOT NULL Constraint


 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (3   , NULL    , 'name3@gmail.com' , 'F'     , 80   );

ORA-01400: cannot insert NULL into ("HR"."MY_STU_DETAILS"."S_NAME")

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (3   , 'Name3' , 'name3@gmail.com' , 'F'     , 80   );

Select * from my_stu_details;

 
Validating Unique Key Constraint


 
--Duplicate Check


 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (4   , 'Name4' , 'name3@gmail.com' , 'F'     , 80   );

ORA-00001: unique constraint (HR.MY_STU_MAIL_UK) violated

--Null Check

Insert into my_stu_details (s_id, s_name  , s_mail  , s_gender, s_did) 
                    values (4   , 'Name4' , null    , 'M'     , 70   );


Select * from my_stu_details;

 
Validating Check constraint


 
Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (5   , 'Name5' , 'name5@gmail.com'  , 'j'      , 70   );

ORA-02290: check constraint (HR.MY_STU_GEN_CK) violated

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (5   , 'Name5' , 'name5@gmail.com'  , 'M'      , 70   );

Select * from my_stu_details;

 
Validating Foreign Key


 
Select * from Departments;

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (6   , 'Name6' , 'name6@gmail.com'  , 'F'      , 3   );

 
ORA-02291: integrity constraint (HR.MY_STU_DID_FK) violated - parent key not found


 
Departments table  :  parent table
my_stu_details     :  Child table

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (6   , 'Name6' , 'name6@gmail.com'  , 'F'      , 90   );
                    

Select * from my_stu_details;

--------------------------------------------------------
s_id     s_name   s_mail                s_gender  s_did
--------------------------------------------------------
 1 name1  name1@gmail.com   m     60
 2 name2  name2@gmail.com   F     90
 3 Name3  name3@gmail.com   F     80
 4 Name4                   M     70
 5 Name5  name5@gmail.com   M     70
 6 Name6  name6@gmail.com   F     90
--------------------------------------------------------



Deleting the Primary key value which is referred by Foreign Key


Select * from Departments;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------------------------------------------------------
60        IT        103        1400           <----  Delete


-- don't execute this query

--Delete From Departments
--Where  Department_id = 60;

1) ON DELETE SET NULL

-------------------------------------------------
S_ID S_NAME S_MAIL         S_GENDER  S_DID
-------------------------------------------------
6 Name6 name6@gmail.com F   90
1 name1 name1@gmail.com m   -
2 name2 name2@gmail.com F   90
3 Name3 name3@gmail.com F   80
4 Name4 -               M   70
5 Name5 name5@gmail.com M   70
-------------------------------------------------

2) ON DELETE SET CASCADE

-------------------------------------------------
S_ID S_NAME S_MAIL         S_GENDER  S_DID
-------------------------------------------------
6 Name6 name6@gmail.com F   90
2 name2 name2@gmail.com F   90
3 Name3 name3@gmail.com F   80
4 Name4 -               M   70
5 Name5 name5@gmail.com M   70
-------------------------------------------------

Syntax

Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
ON DELETE CASCADE

Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
ON DELETE SET NULL


Constraint Related Data Dictionary Table information.


To view constraint Information

SELECT c.owner,
       c.constraint_name,
       c.constraint_type,
       c.table_name,
       c.r_constraint_name,
       c.status
  FROM user_constraints c
 WHERE c.table_name = 'MY_STU_DETAILS';

 
------------------------------------------------------------------------------------
owner  constraint_name constraint_type table_name        r_constraint_name  status 
------------------------------------------------------------------------------------
 HR SYS_C004023       C       MY_STU_DETAILS                    ENABLED
 HR MY_STU_GEN_CK     C       MY_STU_DETAILS              ENABLED
 HR MY_STU_SID_PK     P       MY_STU_DETAILS              ENABLED
 HR MY_STU_MAIL_UK    U       MY_STU_DETAILS              ENABLED
 HR MY_STU_DID_FK     R       MY_STU_DETAILS     DEPT_ID_PK    ENABLED
------------------------------------------------------------------------------------



To view constraint name with corresponding column name

SELECT * FROM user_cons_columns c
WHERE c.table_name = 'MY_STU_DETAILS';

-------------------------------------------------------------
owner  constraint_name   table_name    column_name  position 
-------------------------------------------------------------
  HR    MY_STU_MAIL_UK MY_STU_DETAILS  S_MAIL 1
  HR    MY_STU_SID_PK MY_STU_DETAILS  S_ID           1
  HR    MY_STU_GEN_CK    MY_STU_DETAILS  S_GENDER
  HR SYS_C004023 MY_STU_DETAILS  S_NAME
  HR MY_STU_DID_FK    MY_STU_DETAILS  S_DID          1 
-------------------------------------------------------------


Creating Composite Primary Key


Creating Primary Key with more than one number of column

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column_2 , column3);

In user_cons_columns  table position if the column1 will be 1, position of column2 will be 2 and column3 will be 3.


creating constraint after the table creation


ALTER TABLE table_name
ADD CONSTRAINT constraint_name CONSTRAINT_TYPE(column_name); 

ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 

ALTER TABLE table_name
DROP PRIMARY KEY; 


To drop related foreign key


ALTER TABLE table_name
DROP PRIMARY KEY CASCADE;

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;  

 
Disable constraint with related foreign key constraint

 
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name CASCADE;

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;  

Interview Questions

• What are all the type of constraint available in oracle?
• Difference between PRIMARY KEY and UNIQUE key.
• Difference between PRIMARY KEY ans FOREIGN KEY.
• Difference between UNIQUE key and FOREIGN KEY.
• Is it possible to create tow primary key in single table.
• Write a syntax to create FOREIGN constraint.
• How to create constraint after the table has been created?
• How to enable and disable a constraint.
• Which data dictionary table contains information about constraints?
• Which Data Dictionary table contains information about constraint with corresponding column name?
• What is composite primary key?
• Explain ON DELETE CASCADE and ON DELETE SET NULL option.

~Thanks For Reading~
Post your valuable feedback below

listagg in Oracle 11g

Oracle 11gR2 listagg built-in function allows for many table columns to be displayed within a single row, a non-first-normal form display

Syntax :

     listagg(column_value,'delimiter') WITHIN GROUP(ORDER BY column_name)


--Example 1:

CREATE TABLE test_ins(empid NUMBER,product VARCHAR2(30));

BEGIN
   INSERT INTO test_ins VALUES( 101,'pendrive');
   INSERT INTO test_ins VALUES( 102,'toy');
   INSERT INTO test_ins VALUES( 101,'ipod');
   INSERT INTO test_ins VALUES( 102,'hat');
   INSERT INTO test_ins VALUES( 103,'cpu');
   INSERT INTO test_ins VALUES( 104,'pen');
   INSERT INTO test_ins VALUES( 104,'car');
   INSERT INTO test_ins VALUES( 104,'mat');
   INSERT INTO test_ins VALUES( 105,'tv');
   INSERT INTO test_ins VALUES( 106,'laptop');
   commit;
END;

SELECT * FROM test_ins;

    SELECT empid "Employee",
           listagg(product,
               ',') within
    GROUP (ORDER BY empid) "Products"
     FROM test_ins
 GROUP BY empid;

-->-- Result set --<--

 

DROP TABLE test_ins;

-->-- Example 2

select department_id "Department id", listagg(first_name,', ') WITHIN GROUP(ORDER BY employee_id) "Employees"
FROM employees
WHERE department_id IN (10,20,30,60)
GROUP BY department_id;

-->-- Result set --<--

Condition and Looping Statement

• IF 
• IF ELSE
• Multiple IF ELSE
• LOOP
• FOR LOOP
• WHILE LOOP

create table test_udt(id NUMBER, salary NUMBER);

BEGIN
INSERT INTO test_udt VALUES(001,23000);
INSERT INTO test_udt VALUES(002,43000);
INSERT INTO test_udt VALUES(003,78000);
INSERT INTO test_udt VALUES(004,25000);
INSERT INTO test_udt VALUES(005,26000);
INSERT INTO test_udt VALUES(006,90000);
Commit;
END;


select * from test_udt;


IF

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 1;
    
    IF v_salary > 20000 THEN
       UPDATE test_udt t
          SET t.salary = v_salary+10005
        WHERE t.id = v_id  ;
    END IF;
    
    commit;
END;
/

IF ELSE

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 2;
    
    IF v_salary < 20000 THEN
       UPDATE test_udt t
          SET t.salary = v_salary+10005
        WHERE t.id = v_id  ;
    ELSE    
        UPDATE test_udt t
          SET t.salary = v_salary+20007
        WHERE t.id = v_id  ;
    END IF;
    commit;
END;
/


--same example with less number of lines

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 2;
    
    IF v_salary < 20000 THEN
        v_salary := v_salary+10005;
    ELSE    
        v_salary := v_salary+20007;
    END IF;

       UPDATE test_udt t
          SET t.salary = v_salary
        WHERE t.id = v_id  ;
    commit;
END;
/


select * from test_udt;

Multiple IF ELSIF

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 3;
    
    IF v_id = 1 THEN
        v_salary := 1000;
        dbms_output.put_line('1 inside');
    ELSIF v_id = 2  THEN
        v_salary := 2000;
        dbms_output.put_line('2 inside');
    ELSIF v_id = 3 THEN
        v_salary := 3000;
        dbms_output.put_line('3 inside');
    ELSIF v_id = 4 THEN
        v_salary := 4000;
        dbms_output.put_line('4 inside');
    END IF;

       UPDATE test_udt t
          SET t.salary = v_salary
        WHERE t.id = v_id  ;
    commit;
END;
/

select * from test_udt;


LOOP

--syntax

LOOP
--statements
--increment 
--exit statement
END LOOP;


CREATE TABLE prod_details (pid VARCHAR2(3), pname VARCHAR2(30));

DECLARE 
    i NUMBER := 1;
    x VARCHAR2(3);
BEGIN
  LOOP
      x := lpad(i,3,'0');
      dbms_output.put_line('x value : '|| x);
      INSERT INTO prod_details VALUES(i, 'Product '||i);
      EXIT WHEN i >900;
      i := i+1;
      
      IF MOD(i,100) = 0 THEN
          commit;
          dbms_output.put_line('commit executed');
      END IF;
      
  END LOOP;
END;

SELECT * FROM prod_details ORDER BY 1;

DROP TABLE prod_details;

FOR LOOP

BEGIN
    FOR I in 1..5 
    LOOP
     Dbms_output.put_line('The value of i : '||i);
    END LOOP;
END;
/

FOR LOOP - Reverse

BEGIN
FOR I in REVERSE 14..50 
LOOP
Dbms_output.put_line('The value of i : '||i);
END LOOP;
END;
/

WHILE LOOP

DECLARE
i_val NUMBER:=0;
BEGIN
WHILE (i_val < 11) 
LOOP
dbms_output.put_line('The value of I is '||i_val);
i_val := i_val + 1;
END LOOP;
END;
/

sample output:

The value of I is 0
The value of I is 1
The value of I is 2
The value of I is 3
The value of I is 4
The value of I is 5
The value of I is 6
The value of I is 7
The value of I is 8
The value of I is 9
The value of I is 10

Bulk Collect

    This is used for array fetches
    With this you can retrieve multiple rows of data with a single round trip.
    This reduces the number of context switches between the pl/sql and sql engines. 
    You can use bulk collect in both dynamic and static sql.
    You can use bulk collect in select, fetch into and returning into clauses.
    SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.
    You can fetch into multiple collections with one column each.
    You can use the limit clause of bulk collect to restrict the no of rows retrieved.
   


SELECT * FROM product_details;

-->-- Bulk collect in FETCH INTO --<--


DECLARE
  TYPE alldata_typ IS TABLE OF product_details%ROWTYPE;
  dta alldata_typ;
  CURSOR proddt_cur IS
    SELECT * FROM product_details;
BEGIN
  OPEN proddt_cur;
  FETCH proddt_cur BULK COLLECT
    INTO dta;
  CLOSE proddt_cur;

  FOR i IN dta.FIRST .. dta.COUNT LOOP
    dbms_output.put_line(dta(i).p_name || ' department id is ' || dta(i).p_id);
  END LOOP;
END;
/


-->-- Bulk collect in SELECT clause --<--

DECLARE
  TYPE alldata_typ IS TABLE OF product_details%ROWTYPE;
  dta alldata_typ;
BEGIN
  SELECT * BULK COLLECT INTO dta FROM product_details;
  FOR i IN dta.FIRST .. dta.LAST LOOP
    dbms_output.put_line(dta(i).p_name || ' department id is ' || dta(i).p_id);
  END LOOP;
END;



-->-- LIMIT in Bulk collect --<--

DECLARE
  TYPE alldata_typ IS TABLE OF product_details%ROWTYPE;
  dta alldata_typ;
  CURSOR proddt_cur IS
    SELECT * FROM product_details;
BEGIN
  OPEN proddt_cur;
  FETCH proddt_cur BULK COLLECT
    INTO dta LIMIT 30;
  CLOSE proddt_cur;

  FOR i IN dta.FIRST .. dta.COUNT LOOP
    dbms_output.put_line(dta(i).p_name || ' department id is ' || dta(i).p_id);
  END LOOP;
END;
/



-->-- Multiple fetches in INTO clause --<--

SELECT * FROM product_details;

DECLARE
  TYPE alldata_typ1 IS TABLE OF product_details.p_id%TYPE;
  TYPE alldata_typ2 IS TABLE OF product_details.p_name%TYPE;
  dta1 alldata_typ1;
  dta2 alldata_typ2;
  CURSOR proddt_cur IS
    SELECT p_id, p_name FROM product_details;
BEGIN
  OPEN proddt_cur;
  FETCH proddt_cur BULK COLLECT
    INTO dta1, dta2;
  CLOSE proddt_cur;

  FOR i IN dta1.FIRST .. dta1.COUNT LOOP
    dbms_output.put_line('Department Id : ' || dta1(i));
  END LOOP;

  FOR i IN dta2.FIRST .. dta2.COUNT LOOP
    dbms_output.put_line('Department Name : ' || dta2(i));
  END LOOP;

END;
/


DROP TABLE dep_details;

DROP TABLE product_details;

Ref Cursor

    This is unconstrained cursor which will return different types depends upon the user input.
    Ref cursors cannot be closed implicitly.
    Ref cursor with return type is called strong cursor.
    Ref cursor without return type is called weak cursor.
    You can declare ref cursor type in package spec as well as body.
    You can declare ref cursor types in local subprograms or anonymous blocks.


CREATE OR REPLACE PROCEDURE ref_c_sp(dep_det_c IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN dep_det_c
  FOR
  SELECT * FROM dep_details d;
END;
/


DECLARE
   dep_det_c  SYS_REFCURSOR;
   tab_data   dep_details%ROWTYPE;
BEGIN
   ref_c_sp(dep_det_c);
 
   LOOP
      FETCH dep_det_c INTO tab_data;
      EXIT WHEN dep_det_c%NOTFOUND;
      dbms_output.put_line('Department Name : '|| tab_data.d_name);
   END LOOP;
   CLOSE dep_det_c;
END;


/*sample output*/

Department Name : Admin
Department Name : HR
Department Name : Sales
Department Name : Marketing


DROP PROCEDURE ref_c_sp;

Cursor

    

SQL Private work Area where the query get passed and executed.

Types
******
    Implicit(SQL)
    Explicit
        -->-- advanced cursor types --<--
    Parametrized Cursor
    Ref Cursor

Implicit
********

    ORACLE implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor.
   
    PL/SQL lets you refer to the most recent implicit cursor as the SQL” cursor. So, although you cannot use the OPEN,
    FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information
    about the most recently executed SQL statement.

Cursor Stages
**************

        OPEN cursor_name
        FETCH (with in loop)
        CLOSE cursor_name
 
Explicit

    The set of rows returned by a query can consist of zero, one, or multiple rows,
    depending on how many rows meet your search criteria. When a query returns multiple rows,
    you can explicitly define a cursor to process the rows. You use three commands to control the cursor

Cursor Attributes
*****************

    %FOUND
    %NOTFOUND
    %ISOPEN
    %ROWCOUNT
    %BULK_ROWCOUNT        --<-- used to handle error while using bulk collection.
    %BULK_EXCEPTIONS    --<-- used to handle error while using bulk collection.

Cursor Declaration

    DECLARE
        CURSOR <cursor_name>
            IS
        <SELECT statement>
        BEGIN
             --> some stmt
        END;
        /

Cursor Loop

• Loop
• While Loop
• For Loop

Cursor Clauses

• Return
• For update
• Where current of
• Bulk collect

-->-- creating table

create table product_details
(
p_id        NUMBER,
p_name      VARCHAR2(30),
p_order_dt  DATE
);


-->-- Inserting data

BEGIN
  FOR i IN 1 .. 75 LOOP
    INSERT INTO product_details VALUES (i, 'prod_name_' || i, SYSDATE + i);
  END LOOP;
  commit;
END;


-->-- selecting data

SELECT * FROM product_details;

---------------------------------------------
p_id    p_name        p_order_dt
---------------------------------------------
1     prod_name_1     10/11/2014 3:48:32 PM
2     prod_name_2     10/12/2014 3:48:32 PM
3     prod_name_3     10/13/2014 3:48:32 PM
4     prod_name_4     10/14/2014 3:48:32 PM
5     prod_name_5     10/15/2014 3:48:32 PM
6     prod_name_6     10/16/2014 3:48:32 PM
7     prod_name_7     10/17/2014 3:48:32 PM
8     prod_name_8     10/18/2014 3:48:32 PM
9     prod_name_9     10/19/2014 3:48:32 PM
10    prod_name_10    10/20/2014 3:48:32 PM
11    prod_name_11    10/21/2014 3:48:32 PM
12    prod_name_12    10/22/2014 3:48:32 PM
13    prod_name_13    10/23/2014 3:48:32 PM
14    prod_name_14    10/24/2014 3:48:32 PM
15    prod_name_15    10/25/2014 3:48:32 PM
16    prod_name_16    10/26/2014 3:48:32 PM
17    prod_name_17    10/27/2014 3:48:32 PM
18    prod_name_18    10/28/2014 3:48:32 PM
19    prod_name_19    10/29/2014 3:48:32 PM
20    prod_name_20    10/30/2014 3:48:32 PM
---------------------------------------------


----------------------------------------------
Implicit Cursor
----------------------------------------------

         --will update soon

----------------------------------------------
Explicit Cursor
----------------------------------------------

**********************************************
Processing cursor data by using LOOP
**********************************************
   
DECLARE
  all_data product_details%ROWTYPE;
  --cursor declaration
  CURSOR prod_detail_cur IS
    SELECT * FROM product_details
    WHERE ROWNUM <=5;
BEGIN
  OPEN prod_detail_cur;
     LOOP
        FETCH prod_detail_cur
         INTO all_data;
         EXIT WHEN prod_detail_cur%NOTFOUND;  --<-- if next record not found means control will come out from the loop
         dbms_output.put_line('Product id : ' || all_data.p_id ||
                              ' Product Name :  ' || all_data.p_name ||
                              ' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
     END LOOP;
  CLOSE prod_detail_cur;
END;


/*sample output*/

Product id : 1 Product Name :  prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name :  prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name :  prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name :  prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name :  prod_name_5 ( Orderd on : 15-OCT-14 )


DECLARE
  all_data product_details%ROWTYPE;
  --cursor declaration
  CURSOR prod_detail_cur IS
    SELECT * FROM product_details
    WHERE ROWNUM <=5;
BEGIN
  OPEN prod_detail_cur;
     LOOP
        FETCH prod_detail_cur
         INTO all_data;
         dbms_output.put_line('Product id : ' || all_data.p_id ||
                              ' Product Name :  ' || all_data.p_name ||
                              ' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
         EXIT WHEN prod_detail_cur%FOUND;  --<-- if next record found means control will come out from the loop
     END LOOP;
  CLOSE prod_detail_cur;
END;


/*sample output*/

Product id : 1 Product Name :  prod_name_1 ( Orderd on : 11-OCT-14 )



DECLARE
  all_data product_details%ROWTYPE;
  --cursor declaration
  CURSOR prod_detail_cur IS
    SELECT * FROM product_details
    WHERE ROWNUM <=5;
BEGIN
  OPEN prod_detail_cur;
     LOOP
        FETCH prod_detail_cur
         INTO all_data;
         EXIT WHEN prod_detail_cur%NOTFOUND; 
         dbms_output.put_line('Product id : ' || all_data.p_id ||
                              ' Product Name :  ' || all_data.p_name ||
                              ' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
     END LOOP;
  CLOSE prod_detail_cur;
 
  IF prod_detail_cur%ISOPEN THEN
     CLOSE prod_detail_cur;
     dbms_output.put_line('prod_detail_cur cursor closed');
  ELSE
     dbms_output.put_line('prod_detail_cur cursor Already closed');
  END IF;
  
END;


/*sample output*/

Product id : 1 Product Name :  prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name :  prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name :  prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name :  prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name :  prod_name_5 ( Orderd on : 15-OCT-14 )
prod_detail_cur cursor Already closed




DECLARE
  all_data product_details%ROWTYPE;
  --cursor declaration
  CURSOR prod_detail_cur IS
    SELECT * FROM product_details
    WHERE ROWNUM <=5;
BEGIN
  OPEN prod_detail_cur;
     LOOP
        FETCH prod_detail_cur
         INTO all_data;
         EXIT WHEN prod_detail_cur%NOTFOUND; 
         dbms_output.put_line('Product id : ' || all_data.p_id ||
                              ' Product Name :  ' || all_data.p_name ||
                              ' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
     END LOOP;
  -->-- CLOSE prod_detail_cur;
 
  IF prod_detail_cur%ISOPEN THEN
     CLOSE prod_detail_cur;
     dbms_output.put_line('prod_detail_cur cursor closed');
  ELSE
     dbms_output.put_line('prod_detail_cur cursor Already closed');
  END IF;
  
END;


/*sample output*/

Product id : 1 Product Name :  prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name :  prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name :  prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name :  prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name :  prod_name_5 ( Orderd on : 15-OCT-14 )
prod_detail_cur cursor closed




**********************************************
Processing cursor data by using WHILE LOOP
**********************************************


DECLARE
  all_data product_details%ROWTYPE;
  --cursor declaration
  CURSOR prod_detail_cur IS
    SELECT * FROM product_details WHERE ROWNUM <= 5;
BEGIN
  OPEN prod_detail_cur;
  FETCH prod_detail_cur
    INTO all_data;
  WHILE prod_detail_cur%FOUND LOOP
 
    dbms_output.put_line('Product id : ' || all_data.p_id ||
                         ' Product Name :  ' || all_data.p_name ||
                         ' ( Orderd on : ' || TRIM(all_data.p_order_dt) || ' )');
    FETCH prod_detail_cur
      INTO all_data;
  END LOOP;
  CLOSE prod_detail_cur;
END;

/*sample output*/

Product id : 1 Product Name :  prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name :  prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name :  prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name :  prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name :  prod_name_5 ( Orderd on : 15-OCT-14 )


************************************************************
Processing cursor data by using FOR LOOP - CURSOR FOR LOOP
************************************************************

DECLARE
  --cursor declaration
  CURSOR prod_detail_cur IS
    SELECT * FROM product_details WHERE ROWNUM <= 5;
BEGIN
  FOR i IN prod_detail_cur
  LOOP
    dbms_output.put_line('Product id : ' || i.p_id ||
                         ' Product Name :  ' || i.p_name ||
                         ' ( Orderd on : ' || TRIM(i.p_order_dt) || ' )');
  END LOOP;
END;


/*sample output*/

Product id : 1 Product Name :  prod_name_1 ( Orderd on : 11-OCT-14 )
Product id : 2 Product Name :  prod_name_2 ( Orderd on : 12-OCT-14 )
Product id : 3 Product Name :  prod_name_3 ( Orderd on : 13-OCT-14 )
Product id : 4 Product Name :  prod_name_4 ( Orderd on : 14-OCT-14 )
Product id : 5 Product Name :  prod_name_5 ( Orderd on : 15-OCT-14 )



Parameterized Cursor
*********************

    This was used when you are going to use the cursor in more than one place with different values for the same where clause.
    Cursor parameters must be in mode.
    Cursor parameters may have default values.
    The scope of cursor parameter is within the select statement.



CREATE TABLE dep_details(d_id NUMBER,d_name VARCHAR2(30), location_id NUMBER);

BEGIN
INSRT INTO dep_details VALUES(001,'Admin',1010);
INSRT INTO dep_details VALUES(002,'HR',1010);
INSRT INTO dep_details VALUES(003,'Sales',1020);
INSRT INTO dep_details VALUES(004,'Marketing',1020);
commit;
END;

SELECT * FROM dep_details;

----------------------------
d_id   d_name    location_id
----------------------------
   1   Admin        1010
   2   HR           1010
   3   Sales        1020
   4   Marketing    1020
----------------------------


DECLARE
  -->--Declaring parameterized cursor
  CURSOR dep_det_c(p_location_id NUMBER) IS
    SELECT * FROM dep_details d WHERE d.location_id = p_location_id;

  all_data dep_details%ROWTYPE;
BEGIN
  dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
  OPEN dep_det_c(1010);
  LOOP
    FETCH dep_det_c
      INTO all_data;
    EXIT WHEN dep_det_c%NOTFOUND;
    dbms_output.put_line('Department id : ' || all_data.d_id ||
                         ' Department Name : ' || all_data.d_name);
  END LOOP;
  CLOSE dep_det_c;

  dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-'); 
  -->-- opening same cursor with different input value
 
  OPEN dep_det_c(1020);
  LOOP
    FETCH dep_det_c
      INTO all_data;
    EXIT WHEN dep_det_c%NOTFOUND;
    dbms_output.put_line('Department id : ' || all_data.d_id ||
                         ' Department Name : ' || all_data.d_name);
  END LOOP;
  CLOSE dep_det_c;
  dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
END;
/


/*sample output*/

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Department id : 1 Department Name : Admin
Department id : 2 Department Name : HR
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Department id : 3 Department Name : Sales
Department id : 4 Department Name : Marketing
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-