إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

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


لا توجد ردود على هذا الموضوع

#1 predator jaw

predator jaw

    عضو نشط

  • الأعضــاء
  • 253 مشاركة
  • الاسم الأول:Ramy
  • اسم العائلة:Omar
  • البـلـد: Country Flag

تاريخ المشاركة 16 December 2011 - 08:18 PM

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

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

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

.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 )
RETURN varchar2 IS
monthly_value number(6);
ILevel varchar2(20);

cursor c1 is
select monthly_income
from employees
where name = name_in;

BEGIN

open c1;
fetch c1 into monthly_value;
close c1;

IF monthly_value <= 4000 THEN
ILevel := 'Low Income';

ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
ILevel := 'Avg Income';

ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
ILevel := 'Moderate Income';

ELSE
ILevel := 'High Income';

END IF;

RETURN ILevel;

END IncomeLevel;