أرجو شرح هذه الأكواد..... عاجل

أرجو شرح هذه الأكواد

وهل يوجد أخطاء فى هذه الأكواد

لأن كل لما أجربها يحصل أخطاء

.Using procedure to increase the salary.

declare

dept_nonumber:=2;

percentagenumber:=3;

--create procedureraise_salary(dept_nonumber, percentagenumber DEFAULT0.5) is

cursor emp_cur is

select SAL , emp_id from EMP where DEPTNO = dept_no;

emp_rec emp_cur %rowtype;

begin

open emp_cur;

loop

fetch emp_cur into emp_rec;

exit when emp_cur%NOTFOUND;

update EMP set SAL = emp_rec.sal * ((100 + percentage)/100)

where emp_id = emp_rec .emp_id;

end loop;

close emp_cur;

commit;

end; --raise_salary;

This procedure can be called from the SQL*Plus shell using the command

execute raise salary(10, 3);

If the procedure is called only with the parameter 10, the default value 0.5 is assumed as specified in the list of parameters in the procedure definition. If a procedure is called from a PL/SQL block, the keyword execute is omitted.

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

Create a function

create function get_dept_salary(dno number) return number is

all_sal number;

all_sal := 0;

cursor emp_cur is select SAL from EMP where DEPTNO = dno and SAL is not null;

emp_rec emp_cur%rowtype;

begin

open emp_cur;

loop

fetch emp_cur into emp_rec;

Exit when emp_cur%notfound;

all_sal := all_sal + emp_rec.sal;

end loop;

close emp_cur;

return all_sal;

end get dept salary;

In order to call a function from the SQL*Plus shell, it is necessary to first define a variable to which the return value can be assigned. In SQL*Plus a variable can be defined using the command variable <variable name> <data type>;,

for example, variable salary number. The above function then can be called using the command execute :salary :=get_dept salary(20);

Note that the colon “:” must be put in front of the variable.

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

- Creating a Package

Create package manage_employee as -- package specification

function hire_emp (name varchar2, job varchar2, mgr number, hiredate date, sal number, comm number default 0, deptno number)

return number;

procedure fire_emp (emp_id number);

procedure raise_sal (emp_id number, sal_incr number);

end manage_employee;

create package body manage_employee as

function hire_emp (name varchar2, job varchar2, mgr number, hiredate date, sal number, comm number default 0, deptno number)

return number is

-- Insert a new employee with a new employee Id

new_empno number(10);

begin

select emp_sequence.nextval into new_empno from dual;

insert into emp values(new_empno, name, job, mgr, hiredate,

sal, comm, deptno);

return new_empno;

end hire_emp;

procedure fire_emp(emp_id number) is

-- deletes an employee from the table EMP

begin

delete from emp where empno = emp_id;

if SQL%NOTFOUND then -- delete statement referred to invalid emp_id

raise_application_error(-20011, ’Employee with Id ’ ||

to_char(emp_id) || ’ does not exist.’);

end if;

end fire_emp;

procedure raise_sal(emp_id number, sal_incr number) is

-- modify the salary of a given employee

begin

update emp set sal = sal + sal_incr

where empno = emp_id;

if SQL%NOTFOUND then

raise_application_error(-20012, ’Employee with Id ’ ||

to_char(emp_id) || ’ does not exist’);

end if;

end raise_sal;

end manage_employee;

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

CREATE Function IncomeLevel ( name_in varchar2 )

cursor c1 is

BEGIN

open c1;

IF monthly_value <= 4000 THEN

ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN

ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN

ELSE

END IF;

RETURN ILevel;

END IncomeLevel;