Attachment 127296 Details for Bug 98253 – DBMS

DBMS

LAB10.txt (text/plain), 4.10 KB, created by Mitesh on 2016-10-14 11:29:10 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Mitesh

Created: 2016-10-14 11:29:10 UTC

Size: 4.10 KB

patch

obsolete

>Q.1 Write a PL/SQL procedure raise_salary for raising salary of entered employee number by rupees 500. (use emp table)
>Ans.
>create or replace procedure raise_salary (eno in NUMBER)
>is
>begin
>update emp set sal=sal+500 where empno=eno;
>end raise_salary;
>
>declare
>eno number:=&eno;
>begin
>exec raise_salary(10);
>end;
>
>Q.2 Write a PL/SQL procedure replace_name to replace all names with the first character in capital and others in lower case. (use emp table)
>Ans.
>create or replace procedure replace_name (name in VARCHAR2)
>is
>update emp set ename= where empno=;
>end raise_salary;
>
>declare
>eno number:=&eno;
>begin
>exec raise_salary(10);
>end;
>
>Q.3 Write a PL/SQL procedure to display all the details of emp table for given empno.
>Ans.
>create or replace procedure emp_detail (eno in NUMBER)
>is
>cursor c1 is select * from emp where empno=eno;
>rec c1%rowtype;
>begin
>  open c1;
>  loop
>  fetch c1 into rec;
>  dbms_output.put_line(rec.empno || ' ' ||rec.ename|| ' '||rec.job||' ' ||rec.deptno);
>  exit when c1%notfound;
>  end loop;
>  close c1;
>end emp_detail;
>
>Q.4 Write a PL/SQL function getname to find  name of employee when empno is provided by user. (use emp table)
>Ans.
>Create or replace function getname(p_empno emp.empno%type) return varchar
>Is
>Cursor empname_cur(p_emp[no emp.empno%type) is select ename from emp where empno=p_empno;
>Begin
>Open empname_cur(p_empno);
>Fetch empname_cur into r_empname;
>If empname_cur%notfound then
>R_empname.ename := UNKNOWN EMPLOYEE;
>End if;
>Close empname_cur;
>Return r_empname.ename;
>End;
>
>Q.5 Write a Pl/SQL function Factorial to find factorial of the number provided by user.
>Ans.
>create or replace function factorial ( n in number) return number
>is
>  res number(5):=1;
>  i number;
>begin
>  for i in 1..N
>  loop
>  res:=res*i;
>  end loop;
> return res;
>end factorial;
> 
>declare
> num number;
>begin
> num:=factorial(&num);
> dbms_output.put_line('factorial='||num);
>end;
>
>Q.6 Write a PL/SQL function total_salary to find out total salary amount of all employees. (use emp table)
>Ans.
>Create or replace function total_salary( ) return number
>Is
>Cursor total_salary_cur(p_emp[no emp.empno%type) is select ename from emp;
>Begin
>Open total_salary_cur(p_empno);
>Fetch empname_cur into r_empname;
>If empname_cur%notfound then
>R_empname.ename := UNKNOWN EMPLOYEE;
>End if;
>Close empname_cur;
>Return r_empname.ename;
>End;
>
>Q.7Create and invoke a package that contains private and public constructs.
>    a) create package specification and package body called COMM_PACKAGE that contains G_COMM  variable for global commission  and RESET_COMM procedure as public construct. Package also have VALIDATE_COMM function which verify commission entered is more than maximum commission if so then return FALSE otherwise return TRUE. If commission is not more than max then set commission to G_COMM otherwise raise error Invalid commission. 
>Ans.
>create or replace package comm_package is
>g_comm number:=0.10;
>procedure reset_comm
>(p_comm in number);
>end comm_package;
>
>create or replace package body comm_package is
>function validate_comm (p_comm in number)
>RETURN BOOLEAN
>is
>v_max_comm number;
>begin
>select Max(comm) into v_max_comm from emp;
>if p_comm> v_max_comm then
>return(false);
>else return(true);
>end if;
>end validate_comm;
>procedure reset_comm (p_comm in number)
>is begin
>if validate_comm(p_comm) THEN g_comm:=p_comm; --reset global variable
>else
>Raise_application_error(-20210,'invalid commision');
>end if;
>end reset_comm;
>end comm_package;
>
>exec comm_package.reset_comm(1000);
>
>select comm from emp;
>
>
>Q.8 Create and invoke a package that contains private and public constructs.
>a)  Create a package specification and package body called EMP_PACK that contains NEW_EMP procedure as a public construct and VALID_DEPTID function as a private construct.
>b) Invoke the NEW_EMP procedure, using 15 as a department number. As department id 15 does not exist in the department table. You should get an error message. VALID_DEPTID will return whether deptid is valid or not.
>c) Invoke the NEW_EMP procedure, using an existing department id 20.
>
>
>

Actions: View

Attachments on bug 98253: 127296

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.