8/4/2019 PL SQL Program

1/4

1) Sample program for pl/sql block

declarex number:=10;y number:=20;begin

z:=x+y;dbms_output.put_line('Addition Result is-->'z);end;/

2) Program for take run time values

declarex number:=&x;y number:=&y;beginz:=x+y;

dbms_ouput.put_line(z);end;/

3) Program for if condition

delcarex number:=&x;y number:=&y;beginif x < y thendbms_output.put_line('y is big');else

dbms_output.put_line('x is big');end if;end;

4) Program for do while

declarei number:=1;beginloopdbms_output.put_line(i);i:=i+1;

exit when i>=100;end loop;end;/

5) Program for " while loop"

decalrei number:=1;beginwhile i

8/4/2019 PL SQL Program

2/4

6) Program for "For Loop"

delcarex number:=1;beginfor k in x..100 loop

dbms_output.put_line(x);end loop;end;/

7) Program for "For loop in reverse"

declarex number:=1;beginfor x in reverse x..100 loopdbms_output.put_line(x);

end loop;end;/

8) Program for select ename from employee table based on empid

declareeid number:=&eid;enam varchar2(20);beginselect ename in to enam where empId=eid;dbms_output.put_line('employee name is--->'enam);end;

/

9) Program for select studentname,studentfee from student based student id and with variables as table's data types only.

declarestudId Student.sid%type:=&sid;stuName Student.sname%type;stuFee Student.sfee%type;beginselect sname,sfee into stuName,stuFee from Student where sid=studId;dbms_output.put_line('Student Name is-->'sname);dbms_output.put_line('Student Fee is-->'sfee);end;/

10) Write a procedure for adding two numbers .

create or replace procedure KishAdd(x number(6),y number(6)) asz number:=0;beginz:=x+y;dbms_output.put_line('The KishAdd procudere result is-->z);end;/

11) write a function for adding two numbers and return the result back

8/4/2019 PL SQL Program

3/4

create or replace function KisAddFun(x number(5),y number(6)) return number asz number:=0;beginz:=x+y;return z;end;/

---------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Commands :------------------

create table student(sid number(5),sname varchar2(20),sfee number(5),sloc varchar2(20));

insert into student values('1','sai',1000,'hyd'); //for one row command

insert into student values(&sid,'&sname',&sfee,'&sloc'); //for insert multiple rows at a time

alter table student add column sdob varchar2(20); //adding new column to existing table

alter table student modify sname varchar2(50); //modifying the column size to more means 20 to 50

alter table student drop column sfee; //removing the column from student table

alter table student rename sloc to studLocation ; // renaming the old columnname

to new column name

deltee from student; // for all rows deleted

delete from student where sid=1; //perticular student record is deleted

truncate table student; // all rows at a time deleted from table but structure is exists.

update student set sname='kishore' where sid=1; //updating the new values basedon new values.

update student set sname='kishore' ; //no condition so all rows updated in table student

rename student to students; //changed the table name student to students

select * from student; // u will get error because above command for changed the table name

select * from students; // u will get all the information of student table

select sid,sname from student; //selecing perticular columns

select * from student where sid=1; //selecting only satisfied rows only

select * from student where sname like 'K%' ; //selecting the records whose namestarts with k. '%' means later k may have any no of characters

8/4/2019 PL SQL Program

4/4

select * from student where sname like 'K_%; //selecting the records whose namestarts with k. '_' means later k only one letter. so one '_' for one char.

------------------------------------------------------------------

Agreegate functions :-

------------------------

select sum(sid),min(sid),max(sid),avg(sid),variance(sid),stddev(sid) from student;

select sqrt(4) from student; // same as select sqrt(4) from dual; //dual is table for execute the functions

Char functions :------------------

select chr(90),ascii('k'),substr('kishore',2),substr('kishore',3,5),replace('kishore','k','t') from dual;

select concate('Kishore',chepuri'),lower('KISHORE'),upper('kishore'),length('kishore') from dual r student r any table name;

Date Functions :-------------------

select months_between('1-aug-11','1-jun-11'),add_months('1-aug-2011',3),last_day('1-aug-2011'),next_day('18-aug-2011','thursday') from dual;