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 onSQL> /Hello WorldPL/SQL procedure successfully completed.2.Write a program to find sum of two numbers from 1 to 100WRITE A PROGRAM TO FIND SUM...
Tuesday, July 29, 2014
Merging Rows
Merging RowsThe 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 equijoincondition.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 to match...
Monday, July 28, 2014
What is a PLSQL
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 Next Chapter Pl sql is stands for Procedural Language & is use...
Saturday, July 26, 2014
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---------- ----------- ------------------------- ...
Thursday, July 24, 2014
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...
Monday, July 21, 2014
Thursday, July 17, 2014
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.declarenum number;Beginnum := 200/0;Exceptionwhen zero_divide thendbms_output.put_line('You are trying to divide number by zero');End;/SQL> set serveroutput onOutput:-You are trying to divide...
Wednesday, July 16, 2014
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;OutputSQL> set serveroutput onSQL> /OConnell GETS 2600Grant GETS 2600Whalen GETS 4400Hartstein GETS 15000Fay GETS 8000Mavris GETS 6500Baer GETS 10000Higgins GETS 12000Gietz...
Saturday, July 5, 2014
Cursor For Loop
What is a cursor for loop?Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.Example :- Display list of an employees their last name & Salary.DECLARE CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;BEGIN ...
Friday, July 4, 2014
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...
Thursday, July 3, 2014
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 ] loopStetementsend 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 iteration of...
Tuesday, July 1, 2014
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...
