برامج وتوابع وإجرائيات (Oracle)

[Executioner]

بسم الله الرحمن الرحيم
السلام عليكم ورحمة الله وبركاته
أما بعد:

هذه بعض البرامج (declare) وبعض التوابع (functions) والـ (procedures) التي قد أخذناها أثناء العملي أو التي قد أخذناها كوظيفة، عسى أن تستفيدوا منها قبل الفحص إن شاء الله.

أرجو إطلاعي على الأخطاء في حال ورودها.

ولا تنسوا أخوكم من صالح الدعاء.

;););)

[Executioner]

1- طباعة أسماء الأقسام وبجانب كل منها عدد الموظفين، إضافة لذلك وضع dash تحت كل حرف من حروف اسم القسم (ط1).

Declare
vcount number;
vdname dept.dname%type;
begin
for x in 10..40 loop
if mod(x,10)=0 then
select count(*) into vcount from emp
where deptno= x;
select dname into vdname from dept
where deptno= x;
dbms_output.put(vdname);
if vcount<>0 then
dbms_output.put_Line(': ' || vcount);
else
dbms_output.put_Line(': no employees found');
end if;
for x in 1..length(vdname) loop
dbms_output.put('-');
end loop;
dbms_output.put_line(''); -- must put ''
end if;
end loop;
end;
/

[Executioner]

2- طباعة أسماء الأقسام وبجانب كل منها عدد الموظفين، إضافة لذلك وضع dash تحت كل حرف من حروف اسم القسم (ط2):

Declare
vcount number;
vdname dept.dname%type;
dash varchar2(50);
begin
for x in 10..40 loop
if mod(x,10)=0 then
dash:= null;
select count(*) into vcount from emp
where deptno= x;
select dname into vdname from dept
where deptno= x;
dbms_output.put(vdname);
if vcount<>0 then
dbms_output.put_Line(': ' || vcount);
else
dbms_output.put_Line(': ' || 0);
end if;
for x in 1..length(vdname) loop
dash:= '-' || dash;
end loop;
dbms_output.put_line(dash);
end if;
end loop;
end;
/

[Executioner]

3- طباعة أسماء الأقسام وعدد الموظفين في كل قسم بالإضافة لذلك مجموع المعاشات:

Declare
cursor help is
select count(*) vcount, sum(sal) vsal, dname from emp e, dept d
where d.deptno= e.deptno
group by dname;
begin
for h_rec in help loop
dbms_output.put_line('dname: ' || h_rec.dname ||
', count emps: ' || h_rec.vcount ||
', sum sal: ' || h_rec.vsal);
end loop;
end;
/

[Executioner]

4- طباعة أسماء المهن وعدد الموظفين في كل مهنة بالإضافة لذلك مجموع المعاشات:

Declare
cursor j is
select distinct(job) from emp;
vsal number;
vcount number;
begin
for j_rec in j loop
select count(*), sum(sal) into vcount, vsal from emp
where job= j_rec.job; -- instead of group by job.
dbms_output.put_line('*************************');
dbms_output.put_line(j_rec.job);
dbms_output.put_line(vcount);
dbms_output.put_line(vsal);
end loop;
end;
/

[Executioner]

5- طباعة أسماء الأقسام ثم يأتي بعد اسم كل قسم أسماء الموظفين التابعين لهذا القسم وفي النهاية مجموع الرواتب (ط1):

Declare
dno dept.deptno%type;
cursor d is select * from dept;
cursor e is select * from emp
where deptno= dno;
sumsal number:=0;
begin
for d_rec in d loop
sumsal:=0;
dbms_output.put_line('*************');
dbms_output.put_line(d_rec.dname || ':');
dno:= d_rec.deptno;
for e_rec in e loop
dbms_output.put_line(e_rec.ename);
sumsal:= sumsal+e_rec.sal;
end loop;
dbms_output.put_line('sal is: ' || sumsal);
end loop;
end;
/

[Executioner]

6- طباعة أسماء الأقسام ثم يأتي بعد اسم كل قسم أسماء الموظفين التابعين لهذا القسم وفي النهاية مجموع الرواتب (ط2):

Declare
cursor d is select * from dept;
cursor e(dno number) is select * from emp
where deptno= dno;
sumsal number:=0;
begin
for d_rec in d loop
sumsal:=0;
dbms_output.put_line('*************');
dbms_output.put_line(d_rec.dname || ':');
for e_rec in e(d_rec.deptno) loop
dbms_output.put_line(e_rec.ename);
sumsal:= sumsal+e_rec.sal;
end loop;
dbms_output.put_line('sal is: ' || sumsal);
end loop;
end;
/

[Executioner]

7- طباعة أسماء الأقسام ومجموع الرواتب في كل قسم، وتحت كل اسم قسم أسماء المهن في هذا القسم ومجموع الرواتب لكل مهنة ضمن كل قسم، ومن ثم أسماء الموظفين التابعين لكل من مهنة من هذا القسم، وأخيرا مجموع الرواتب الكلي:

Declare
cursor d is select * from dept;
cursor j(dno number) is select job from emp
where deptno= dno
group by job;
cursor e(dno number, vjob varchar2) is select * from emp
where deptno= dno and job= vjob;
sumsal1 number:=0;
sumsal2 number:=0;
sumsal3 number:=0;
begin
for d_rec in d loop
sumsal1:=0;
sumsal2:=0;
dbms_output.put_line(d_rec.dname || ':');
for x in 1..length(d_rec.dname) loop
dbms_output.put('=');
end loop;
dbms_output.put_line('');
for j_rec in j(d_rec.deptno) loop
dbms_output.put_line('* ' || j_rec.job || ':');
for e_rec in e(d_rec.deptno,j_rec.job) loop
dbms_output.put_line(e_rec.ename);
sumsal1:= sumsal1+ e_rec.sal;
end loop;
sumsal2:= sumsal2+ sumsal1;
dbms_output.put_line('sum sal is: ' || sumsal1);
end loop;
sumsal3:= sumsal3+ sumsal2;
dbms_output.put_line('sum sal in this department is: ' || sumsal2);
end loop;
dbms_output.put_line('all sum sal is: ' || sumsal3);
end;
/

[Executioner]

8- أنشئ (function) يمرر لها رقم القسم حيث ترد عدد الموظفين وتحسب مجموع المعاشات لهذا القسم:

create or replace function GetSumSal(no in number, sumsal out number) return number is
vcount number;
Begin
select sum(sal), count(*) into sumsal, vcount from emp
where deptno= no;
return (vcount);
end;
/
sho err
/
Declare
summ number;
vcount number;
Begin
vcount:= GetSumSal(10,summ);
dbms_output.put_line('Count emps is: ' || vcount);
dbms_output.put_line('Sum sal is: ' || summ);
end;

[Executioner]

9- أنشئ (procedure) يمرر له رقم القسم حيث يقوم بحساب مجموع المعاشات لهذا القسم:

create or replace procedure GetSum(no in number, SumSal out number) is
Begin
select sum(sal) into SumSal from emp
where deptno= no;
end;
/
sho err
/
Declare
x number:=0;
Begin
GetSum(10,x);
dbms_output.put_line('Sum sal is: ' || x);
end;

[Executioner]

10- أنشئ (procedure) يمرر له رقم القسم فيطبع أسماء الموظفين لهذا القسم ويحسب مجموع المعاشات:

create or replace procedure GetSumSal(no in number, SumSal out number) is
cursor a is select * from emp
where deptno= no;
Begin
for a_rec in a loop
dbms_output.put_line(a_rec.ename);
end loop;
select sum(sal) into SumSal from emp
where deptno= no;
end;
/
sho err
/
Declare
summ number;
Begin
GetSumSal(10,summ);
dbms_output.put_line('Sum sal is: ' || summ);
end;

[Executioner]

11- بالإعتماد على (procedure) السابق أنشئ (function) يعيد مجموع المعاشات الكلي ويطبع أسماء الأقسام وأسماء الموظفين:

create or replace procedure GetSumSal(no in number, SumSal out number) is
cursor a is select * from emp
where deptno= no;
Begin
for a_rec in a loop
dbms_output.put_line(a_rec.ename);
end loop;
select sum(sal) into SumSal from emp
where deptno= no;
end;
/
sho err
/
create or replace function Get return number is
summ number;
allsum number:=0;
cursor b is select * from dept
where deptno in (select deptno from emp
having count(*)<> 0
group by deptno);
Begin
for b_rec in b loop
dbms_output.put_line(b_rec.dname || ':');
GetSumSal(b_rec.deptno,summ);
allsum:= allsum+ summ;
end loop;
return allsum;
end;
/
sho err
/
Declare
summ number;
Begin
summ:= Get();
dbms_output.put_line('Sum sal is: ' || summ);
end;

[Executioner]

12- أنشئ (function) يمرر لها الدرجة الوظيفية فتقوم بإعادة عدد الموظفين التابعين له:

create or replace function CountEmps(vgrade1 in number) return number is
vcount number:=0;
vgrade2 number;
cursor a is select * from emp;
Begin
for a_rec in a loop
select grade into vgrade2 from salgrade, emp
where sal between losal and hisal and empno= a_rec.empno;
if vgrade1=vgrade2 then
vcount:= vcount +1;
end if;
end loop;
return vcount;
end;
/
sho err
/
Declare
Begin
dbms_output.put_line('Count Emps Is: ' || CountEmps(3));
end;
/

[Executioner]

13- أنشئ (procedure) يقوم بطباعة الدرجات الوظيفية وبعد كل درجة وظيفية أسماء الموظفين التابعة لها ثم عدد الموظفين وفي النهاية العدد الكلي للموظفين التابعين لجميع الدرجات الوظيفية، بالإعتماد على السؤال السابق (ط1):

create or replace function CountEmps(vgrade1 in number) return number is
vcount number:=0;
vgrade2 number;
cursor a is select * from emp;
Begin
for a_rec in a loop
select grade into vgrade2 from salgrade, emp
where sal between losal and hisal and empno= a_rec.empno;
if vgrade1=vgrade2 then
vcount:= vcount +1;
dbms_output.put_line(a_rec.ename);
end if;
end loop;
return vcount;
end;
/
sho err
/
create or replace procedure Get is
cursor b is select * from salgrade;
sumcount1 number:=0;
sumcount2 number:=0;
Begin
for b_rec in b loop
dbms_output.put_line('Grade is: ' || b_rec.grade);
sumcount1:= CountEmps(b_rec.grade);
sumcount2:= sumcount2+ sumcount1;
dbms_output.put_line('Count emps is: ' || sumcount1);
end loop;
dbms_output.put_line('Count all emps is: ' || sumcount2);
end;
/
sho err
/
Declare
Begin
Get();
end;
/

[Executioner]

14- أنشئ (procedure) يقوم بطباعة الدرجات الوظيفية وبعد كل درجة وظيفية أسماء الموظفين التابعة لها ثم عدد الموظفين وفي النهاية العدد الكلي للموظفين التابعين لجميع الدرجات الوظيفية، بالإعتماد على السؤال السابق (ط2):

create or replace function CountEmps(vgrade1 in number) return number is
vcount number:=0;
vgrade2 number;
cursor a is select * from emp;
Begin
for a_rec in a loop
select grade into vgrade2 from salgrade, emp
where sal between losal and hisal and empno= a_rec.empno;
if vgrade1=vgrade2 then
vcount:= vcount +1;
end if;
end loop;
return vcount;
end;
/
sho err
/
create or replace procedure Get is
cursor a is select * from emp;
cursor b is select * from salgrade;
sumcount1 number:=0;
sumcount2 number:=0;
x number;
Begin
for b_rec in b loop
dbms_output.put_line('Grade is: ' || b_rec.grade);
for a_rec in a loop
select grade into x from emp, salgrade
where sal between losal and hisal and empno= a_rec.empno;
if b_rec.grade= x then
dbms_output.put_line(a_rec.ename);
end if;
end loop;
sumcount1:= CountEmps(b_rec.grade);
sumcount2:= sumcount2+ sumcount1;
dbms_output.put_line('Count emps is: ' || sumcount1);
end loop;
dbms_output.put_line('Count all emps is: ' || sumcount2);
end;
/
sho err
/
Declare
Begin
Get();
end;
/

[So SAD]

مشكور كتير كتير ودائما لا تحرمنا من كل اشياءك المفيدة

[samah]

مشكور أخي الكريم نشالله يارب الله بوفقك بحسنتنا .
تقبل احترامي .

[beutifulwoman]

شكره الك يا Executioner

[mansor_t]

يسلمو وشكرا كتير يا معلمنا !

[*Islam_Rose*]

بسم الله الرحمن الرحيم
السلام عليكم
يعطيك العافية أخي
إن شاء الله ناجح بتفوق في هذه المادة و جميع المواد
جزاك الله ألف خير
و إن شاء الله يوفقك في علمك و حياتك و يفتح عليك و يباركلك و يزيدك من فضله
آمين
و السلام عليكم

[Executioner]

السلام عليكم ورحمة الله وبركاته:

إن شاء الله بتكونوا استفدتم من البرامج وصرتم أحسن مني

وأنا آسف على التأخر كان بودي كملهم كلهم قبل الفحص بشي يومين بس للأسف ما لحقت

على كل حال الحمد لله، أنتم ادعولي تتيسر أموري

والله يوفقني ويوفقكم جميعا

[Executioner]

15- أنشئ (sequence) يبدأ من -100 بقفزة مقدارها 15 (ملاحظة هامة: لم يذكر إن كانت هذه القيمة موجبة أو سالبة) ليصل إلى القيمة -50 ومن ثم يعيد العد (الدورة) بدءا من قيمة حددها؟ ثم حدد قيمتي الـ (cache) الدنيا (min) والعليا(max):

create SEQUENCE s1
start with -100
increment by 15
minvalue -200
maxvalue -50
cycle
cache 5;

select * from user_sequences;
select s1.nextval from dual;
select * from user_sequences;

--drop sequence s1;

في ملاحظة: بعد التنفيذ قوموا بتجاهل تعليمة إنشاء (sequence) ومن ثم نفذوا البرنامج المرة تلو المرة لتروا النتائج وفي النهاية فعلوا آخر تعليمة لحذف ال (sequence).

[Executioner]

16- أنشئ (sequence) يبدأ من -100 بقفزة مقدارها 5 (ملاحظة هامة: لم يذكر إن كانت هذه القيمة موجبة أو سالبة) ليصل إلى القيمة -200 ومن ثم يعيد العد (الدورة) بدءا من قيمة حددها؟ ثم حدد قيمتي الـ (cache) الدنيا (min) والعليا(max):

create SEQUENCE s1
start with -100
increment by -5
minvalue -200
maxvalue -125
cycle
cache 5;

select * from user_sequences;
select s1.nextval from dual;
select * from user_sequences;

--drop sequence s1;

[Executioner]

17- المطلوب إنشاء (trigger) عند إدخال سجل موظف يقوم بما يلي: 1) يضع رقم الموظف رقما تسلسليا من آخر رقم كان. 2) في حال أدخل رقم القسم (null) نجعل رقم مديره (null) أما في حال إذا ما أدخل رقم قسم غير موجود نضيف سجل جديد إلى جدول (dept) ونضع فيه اسم قسم ما جديد ونضع له رقم القسم أقرب إلى 5 (بحيث يكون أقرب عدد قابل القسمة على صفر) ونضع المدير (null) أما في حال أدخل رقم قسم صحيح نقوم بالبحث عن المدير العام لهذا القسم ونضعه مديرا له(ط1):


create or replace function help(Xdeptno in number) return number is
X number:= null;
Begin
select empno into X from emp
where Xdeptno= deptno and mgr not in
(select empno from emp where Xdeptno= deptno);
return X;
Exception
when others then
select empno into X from emp
where Xdeptno= deptno and mgr is null;
return X;
end;
/
sho err
/
create or replace trigger tgr1 before insert on emp for each row
declare
Xcount number;
Xdno number;
Xeno number;
Begin
select max(empno) into Xeno from emp;
:new.empno:= NVL(Xeno,0)+1;
if :new.deptno is null then -- not said = null.
:new.mgr:= null;
else
select count(*) into Xcount from dept
where deptno= :new.deptno;
if Xcount= 0 then -- there is two things? ? ! !
select max(deptno) into Xdno from dept;
insert into dept (deptno, dname) values (Xdno+5-mod(Xdno,5),'NewDept');
:new.deptno:= Xdno+5-mod(Xdno,5);
:new.mgr:= null;
--:new.empno= 1+ Xeno;
else
:new.mgr := help(:new.deptno);
end if;
end if;
end;
/
sho err
/
select deptno,empno,mgr from emp
group by deptno,empno,mgr;
--delete from emp
-- where ename = 'BBB';
insert into emp(deptno,sal,ename,comm)
values(30,1000,'BBB',200);
select * from emp;

[Executioner]

18- المطلوب إنشاء (trigger) عند إدخال سجل موظف يقوم بما يلي: 1) يضع رقم الموظف رقما تسلسليا من آخر رقم كان. 2) في حال أدخل رقم القسم (null) نجعل رقم مديره (null) أما في حال إذا ما أدخل رقم قسم غير موجود نضيف سجل جديد إلى جدول (dept) ونضع فيه اسم قسم ما جديد ونضع له رقم القسم أقرب إلى 5 (بحيث يكون أقرب عدد قابل القسمة على صفر) ونضع المدير (null) أما في حال أدخل رقم قسم صحيح نقوم بالبحث عن المدير العام لهذا القسم ونضعه مديرا له (ط2):


create or replace function help(Xdeptno in number) return number is
X number:= null;
Begin
select empno into X from emp
where Xdeptno= deptno and nvl(mgr,0) not in
(select empno from emp where Xdeptno= deptno);
return X;
end;
/
sho err
/
create or replace trigger tgr1 before insert on emp for each row
declare
Xcount number;
Xdno number;
Xeno number;
Begin
select max(empno) into Xeno from emp;
:new.empno:= NVL(Xeno,0)+1;
if :new.deptno is null then -- not said = null.
:new.mgr:= null;
else
select count(*) into Xcount from dept
where deptno= :new.deptno;
if Xcount= 0 then -- there is two things? ? ! !
select max(deptno) into Xdno from dept;
insert into dept (deptno, dname) values (Xdno+5-mod(Xdno,5),'NewDept');
:new.deptno:= Xdno+5-mod(Xdno,5);
:new.mgr:= null;
--:new.empno= 1+ Xeno;
else
:new.mgr := help(:new.deptno);
end if;
end if;
end;
/
sho err
/
select deptno,empno,mgr from emp
group by deptno,empno,mgr;
--delete from emp
--where ename = 'AAA';
insert into emp(deptno,sal,ename,comm)
values(null,1000,'AAA',200);
select * from emp;

[Executioner]

19- فقط لمعرفة كيف نقوم بالطباعة على الشاشة، لأن بعض الطلاب لا يعرفون كيفية استخدام هذه التعليمة (حيث أحيانا يضعون فاصلة منقوطة لهذه التعليمة فلا يقوم بالتنفيذ):

set SERVEROUTPUT ON
declare
Begin
dbms_output.put_line('Halloo.');
end;
/

[Executioner]

20- معالجة خطأ: الخطأ موجود في الكتلة الثانية المتواجدة في الكتلة الأولى، والكتلة الثانية لم تستطع معالجة الخطأ فانتقل إلى الأعلى فعالجته الكتلة الأولى (الكتلة الثانية ضمن الأولى):


Declare
x number:= 3;
y number:= 0;
Begin
dbms_output.put_line('The result is: ');
Declare
z number;
Begin
z:= x/y;
dbms_output.put_line('is: ' || z);
Exception
when Too_Many_Rows then
dbms_output.put_line('Error: too many rows.');
End;
Exception
when Zero_divide then
dbms_output.put_line('is: ' || 0);
dbms_output.put_line('Error: zero divid.');
End;

[Executioner]

21- معالجة أخطاء: ضمن كتلة رئيسية هناك ثلاث كتل وفي كل كتلة خطأ مختلف عن الآخر وقد عالجت الكتلة الرئيسية بقية أنواع الأخطاء:


Declare
Begin
Declare
x number:= 3;
y number:= 0;
z number;
Begin
z:= x/y;
dbms_output.put_line('The result is: ' || z);
Exception
when zero_divide then
dbms_output.put_line('The result is: ' || 0);
dbms_output.put_line('Error: zero divid.');
End;

Declare
Xsal number;
Begin
select sal into Xsal from emp;
dbms_output.put_line('The sal is: ' || Xsal);
Exception
when too_many_rows then
dbms_output.put_line('Error: too many rows.');
End;

Declare
Xename varchar2(10);
Begin
select ename into Xename from emp
where ename like '%a%';
dbms_output.put_line('The ename is: ' || Xename);
Exception
when no_data_found then
dbms_output.put_line('Error: no data found.');
End;
Exception
when others then
dbms_output.put_line('Error: others.');
End;

[Executioner]

22- معالجة خطأ متواجد ضمن (Exception) حيث أن هناك كتلة برمجية ضمن هذا الـ (Exception):

Declare
Xsal number;
Begin
select sal into Xsal from emp;
Exception
when others then
Declare
x number:= 2;
y number:= 0;
Begin
dbms_output.put_line('The result is: ' || x/y);
Exception
when zero_divide then
dbms_output.put_line('The result is: ' || 0);
dbms_output.put_line('Error: zero divide.');
End;
End;

[amer-ab]

شكراا لك صديقي العزيز


ولمشاهدة نتائج الخرج نقوم بوضع الامر التالي
''''''''''''''''set serveroutput on'''''''''''''''''''''''
فيقوم sqlplus بعرض النتائج