1) Create a stored procedure as follows

Name : changesalaryArgument1 : empidArgument2 :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;beginselect 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);beginlow:=upper(str);up:=lower(str);return(low ||' ' || up);end;

set serveroutput on;begindbms_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 beEast Or West India Is The Best

ANS:

create or replace function init(word varchar) return varchar as low varchar2(20);beginreturn(initcap(word));end;

begin dbms_output.put_line(init('kiren is'));end;

5)create a stored procedure as follows

Name : getEmailerArgument1 : employee nameReturn type : email address of this employee

ANS:

create or replace procedure getemailer(nam varchar,mail out varchar) asbeginselect email into mail from emp2 where emp2.name=nam;end;

declaremail varchar2(20);begingetemailer('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 culoopdbms_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 emp2where name=nam;begin for e in culoopdbms_output.put_line(e.email);end loop;end getallemail;

function init(word varchar) return varchar as low varchar2(20);beginreturn(initcap(word));end init;

procedure getemailer(nam varchar,mail out varchar) isbeginselect email into mail from emp2 where emp2.name=nam;end getemailer;

function word(str varchar) return varchar as low varchar2(20) ; up varchar2(20);beginlow:=upper(str);up:=lower(str);return(low ||' ' || up);end word;

procedure changesalary(ide number,ch_salary number) is sal number;beginselect 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;