Exercise Examples

1.Write a program to print Hello World. SQL> begin   2  dbms_output.put_line('Hello World');   3  End;   4  / PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Hello World PL/SQL procedure successfully completed. 2. Write a program to find sum of two numbers from 1 to 100 DECLARE  N...

Read More

Merging Rows

Merging Rows The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin condition. The example shown matches the employee_id in the  tab_new table to the employee_id in the employees table. If a match is found, the row is updated...

Read More

The anonymous block / What is Plsql

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 Pl sql is stands for Procedural Language & is use to processes...

Read More

Odd No Even No

1.How to fetch alternate records from a table.Like Even /Odd Records.Even Record :-select * from (select  rownum rn,employee_id,last_name from employees order by rn) where mod(rn,2)=0        RN EMPLOYEE_ID LAST_NAME---------- ----------- -------------------------        ...

Read More

Insert into select statement

1.How can U call Pl Sql Procedure From SqlWe can type  execute procedure (short term EXEC)like EXEC procedure_name;2.Write a query to find Nth highest salary from a table.select distinct(e.salary)from employees ewhere &eno=(select count(distinct(e1.salary))from employees e1where e.salary<=e1.salary)Output:- Suppose You want to find second...

Read More

PLSQL functions

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 ...

Read More

Zero_divide

In this example , a Pl sql block attempts to divide by 0. Zero_divide is a predefined exception & it is used for to trap the error in an exception block. declare num number; Begin num := 200/0; Exception when zero_divide then dbms_output.put_line('You are trying to divide number by zero'); End; / SQL> set serveroutput on Output:- You...

Read More

Cursor For Loop Using Subquery

Example:-  BEGIN   FOR EMPREC IN (SELECT * FROM EMPLOYEES) LOOP      DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);   END LOOP; END; Output SQL> set serveroutput on SQL> / OConnell GETS 2600 Grant GETS 2600 Whalen GETS 4400 Hartstein GETS 15000 Fay GETS 8000 Mavris GETS 6500 Baer...

Read More

Cursor For Loop

We can use Cursor for loop when we want to fetch & precess every record  in a cursor.Cursor for loop will terminate when  all of the records in the cursor have been fetched.Syntax:-For record_index in cursor_nameloop{Statements...}End loop;Record Index is is the name of record.Cursor Name is the name of cursor that you wish to fetch records...

Read More

Nested Loop

Nested loop is a easy way of combining data from two row sources.it takes all the rows from outer loop and for each of them it looks up row matchingthe join condition from other  inner row source.Write a pl sql program to calculate the yearly bonus that the company gives to its employees.The company has some criteria is as follows:-1. If a department_id...

Read More

Basic Loop In Oracle

Basic Loop Statement:- The Loops means run the same statements with a series of differant values. Structure of basic loop is [label  ] loop Stetements end loop[label]; with each iteration of the loop statement run and control returns to the top of the loop. To avoid an infinite loop ,we must use exit loop.exit statement exists the current...

Read More

Can not Insert null value

Definition:- You are trying to insert a null value which have not null constraint for department_name column define for it.you will have to specified value for this column or give a default value for it.Example:- DECLARE  insert_excep...

Read More