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
WRITE A...
Tuesday, July 29, 2014
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...
Monday, July 28, 2014
What is a PLSQL
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
Next Chapter
Pl sql...
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 Sql
We 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 e
where &eno=(select count(distinct(e1.salary))
from employees e1
where e.salary<=e1.salary)
Output:- Suppose You want...
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.
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...
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;
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...
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...
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 matching
the 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....
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 ] 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...
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
...
