IN clause IN SQL

An In_condition is a membership condition. it tests a value for membership in a list of values or
subquery.

The Condition defined using in Operator is also known as the membership condition.
In works with values of all data types..

When we using character,dates or non alpha -numeric strings,the list items must be enclosed with single quotation marks For example('Delhi','Mumbai') as shown in second example

SQL> select employee_id,job_id,salary
  2  from employees
  3  where employee_id in(100,102,107);

EMPLOYEE_ID JOB_ID         SALARY
----------- ---------- ----------
        100 AD_PRES         16000
        102 AD_VP           17000
        107 IT_PROG          4200

In the query above ,oracle would select only those records where the employee_id equal to either 100,102,107.
Example 2:in the query below ,oracle would select only those records where job_id is equal to 'IT_PROG','SA_REP'.
remember that this items are enclosed with single quotation marks since they are non-numeric-strings.

SQL> select employee_id,last_name,job_id
  2  from employees
  3  where job_id in('IT_PROG','SA_REP');

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        103 Hunold                    IT_PROG
        104 Ernst                     IT_PROG
        105 Austin                    IT_PROG
        106 Pataballa                 IT_PROG
        107 Lorentz                   IT_PROG
        150 Tucker                    SA_REP
        151 Bernstein                 SA_REP
        152 Hall                      SA_REP
        153 Olsen                     SA_REP
        154 Cambrault                 SA_REP
        155 Tuvault                   SA_REP

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        156 King                      SA_REP
        157 Sully                     SA_REP
        158 McEwen                    SA_REP
        159 Smith                     SA_REP
        160 Doran                     SA_REP
        161 Sewall                    SA_REP
        162 Vishney                   SA_REP
        163 Greene                    SA_REP
        164 Marvins                   SA_REP
        165 Lee                       SA_REP
        166 Ande                      SA_REP

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        167 Banda                     SA_REP
        168 Ozer                      SA_REP
        169 Bloom                     SA_REP
        170 Fox                       SA_REP
        171 Smith                     SA_REP
        172 Bates                     SA_REP
        173 Kumar                     SA_REP
        174 Abel                      SA_REP
        175 Hutton                    SA_REP
        176 Taylor                    SA_REP
        177 Livingston                SA_REP

EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        178 Grant                     SA_REP
        179 Johnson                   SA_REP

35 rows selected.

Read More

Boolean Exception In PLSQL

declare
   v_foundrows boolean := false;
begin
   for r in ( select * from employees where employee_id= &dno )
   loop
      dbms_output.put_line(r.last_name || ' ' || r.salary);
      v_foundrows := true;
   end loop;

   if not v_foundrows then
      raise no_data_found;
   end if;

exception
   when no_data_found then
      dbms_output.put_line('hello');
end;

Read More