Plsql Stored Procedure Examples

1) Create a stored procedure as follows Name : changesalary Argument1 : empid Argument2 :percentage of amount to hike (total two arguments) Give a salary hike to specific employee ANS: create or replace procedure changesalary(ide number,ch_salary number) is sal number; begin select salary into sal from employee where employee.id=ide; update employee set salary=(sal+sal*ch_salary*.01) where employee.id=ide; end; Exec changesalary(1,20); select * from employee; 3) Create function to return an inputted string in upper case and another in lower case ANS: create or replace function word(str varchar) return varchar as low varchar2(20) ; up varchar2(20); begin low:=upper(str); up:=lower(str); return(low ||' ' || up); end; set serveroutput on; begin dbms_output.put_line(word('kiren')); end; 4) Create a function named InitCapitalization Return type : String Argument : String

Purpose : if inputted string is , east or west india is the best result would be East Or West India Is The Best ANS: create or replace function init(word varchar) return varchar as low varchar2(20); begin return(initcap(word)); end; begin dbms_output.put_line(init('kiren is')); end;

5)create a stored procedure as follows Name : getEmailer Argument1 : employee name Return type : email address of this employee ANS: create or replace procedure getemailer(nam varchar,mail out varchar) as begin select email into mail from emp2 where emp2.name=nam; end; declare mail varchar2(20); begin getemailer('arun',mail); dbms_output.put_line(mail); end;

6) Create s stored procedure as follows Name : getAllEmails Argument : employee name

Return type : all email address if this user has many email address, make a relation as Contacts has many emails ANS: create or replace procedure getallemail(nam varchar) is cursor cu is select email from emp2 where name=nam; begin for e in cu loop dbms_output.put_line(e.email); end loop; end; exec getallemail('arun'); 7) Put all these routines in to a package named mypack” ANS: create or replace package mypack is procedure getallemail(nam varchar); function init(word varchar) return varchar; procedure getemailer(nam varchar,mail out varchar); function word(str varchar) return varchar; procedure changesalary(ide number,ch_salary number); end mypack;

create or replace package body mypack as procedure getallemail(nam varchar) is cursor cu is select email from emp2 where name=nam; begin for e in cu loop dbms_output.put_line(e.email); end loop; end getallemail;

function init(word varchar) return varchar as low varchar2(20); begin return(initcap(word)); end init; procedure getemailer(nam varchar,mail out varchar) is begin select email into mail from emp2 where emp2.name=nam; end getemailer; function word(str varchar) return varchar as low varchar2(20) ; up varchar2(20); begin low:=upper(str); up:=lower(str); return(low ||' ' || up); end word; procedure changesalary(ide number,ch_salary number) is sal number; begin select salary into sal from employee where employee.id=ide; update employee set salary=(sal+sal*ch_salary*.01) where employee.id=ide; end changesalary; end mypack;