Procedure,function,packages,cursors

[Ahmad.Hasan]

CREATE OR REPLACE PROCEDURE oug_p (ID NUMBER)
IS
tax NUMBER;
BEGIN SELECT salary * .2
INTO tax FROM employees
WHERE employee_id = ID;
END;
/
------------------------------
EXEC oug_p(122)
--- the Result here without output.
-------------------------------
CREATE OR REPLACE PROCEDURE oug_p (ID NUMBER)
IS
tax NUMBER;
BEGIN SELECT salary * .2
INTO tax FROM employees
WHERE employee_id = ID;

DBMS_OUTPUT.put_line ('The TAX Value is : ' || tax);
END;
/
------------------------------------
EXEC oug_p(122)
--- the Result here with output.
------------------------------------------------------------
CREATE OR REPLACE FUNCTION oug_f (ID NUMBER)
RETURN NUMBER
IS
tax NUMBER;
BEGIN SELECT salary * .2
INTO tax FROM employees
WHERE employee_id = ID;

RETURN tax;
END;
/
--------------------------------
EXEC DBMS_OUTPUT.put_line (oug_f (122))
--OR
EXEC DBMS_OUTPUT.put_line ('The TAX Value is : '||oug_f (122))
--------------------------------------------------------------------
الـ function تعني دالة يمكن أن تستقبل متغير أو أكثر أو لا تستقبل شي ولكن يجب أن تعيد قيمة واحد فقط
الـ procedure تعني أجراء يمكن أن تستقبل متغير أو أكثر أو لا تستقبل شي ولا تعيد قيمة 
الـ package مثل الحقيبة التي تحتوي على عدد من الـ function والـ procedure 
------------------------------------------------------------------------------------------

انواع الـ Cursor 
-----------------
هناك نوعين من الـ Cursors 
1- implicit Cursor وهذا النوع يعرف مباشرة دون ان نقوم نحن بتعريفه فهو يعرف مباشره عندما نقوم بتنفيذ اي استعلام حيث يتم حجز منقطه في الذاكره تحت تسميه معينه وتوضع فيها كل البيانات الناتجه من الاستعلام ثم يتم عرضها في الشاشه او التقرير .
فعندما تقوم بفتح ال Sql * plus ثم تقوم بكتابه الاستعلام الاتي (select empno, ename , sal from emp ) فانه يتم تعريف Cursor ضمني توضع فيه كل السجلات الناتجه من الاستعلام ثم يتم بعد ذلك عرض السجلات سجل سجل باستخدام الـIndex .

2- Explicit Cursor وهذا النوع يتم تعريفه من قبل المستخدم عندما يحتاج الى التعامل مع اكثر من سجل . وهذا النوع سوف نتطرق لشرحه بصوره مفصله عندما نحتاج للتعامل مع اكثر من سجل من البيانات من داخل برنامج PL/SQL .

Declare 
 Cursor C1 is
  select empno, ename , sal 
  from emp;

 v_empno emp.empno%type;
 v_ename emp.ename%type;
 v_sal   emp.sal%type;
Begin
Open C1;
	 Dbms_Output.put_line ('empno ' || ' ename ' || ' salary ');
Loop 
	 Fetch C1 into v_empno, v_ename , v_sal;
	 Exit when C1%notfound;
	 Dbms_Output.put_line (  v_empno ||  '  ' ||  v_ename || '   '  ||  v_sal );
End loop;
Close C1;
End;
/




CURSOR 4 LOOP:

declare 
begin
Dbms_Output.put_line ('empno ' || ' ename ' || ' salary ');
For i in (select empno, ename , sal from emp )
loop
	 Dbms_Output.put_line (  i.empno ||  '  ' ||  i.ename || '   '  ||  i.sal );
end loop;
end;
/

PARAMETER CURSOR:
Declare	
 Cursor C1(v_dept in number) is	   
   select empno, ename , sal		from emp	   
   where deptno=v_dept;   

v_empno emp.empno%type;   
v_ename emp.ename%type;   
v_sal   emp.sal%type;
Begin	 
Open C1(10);		  
Dbms_Output.put_line ('empno ' || ' ename ' || ' salary ');	 
Loop		   
		Fetch C1 into v_empno, v_ename , v_sal;		  
	   Exit when C1%notfound;		  
	   Dbms_Output.put_line (  v_empno ||  '  ' ||  v_ename || '   '  ||  v_sal );	 
End loop;
  Close C1;
End;
/








Declare	
Cursor C1(v_dept in number , s  in number) is		
select empno, ename , sal		
from emp	   
where deptno=v_dept AND sal >= s;   
v_empno emp.empno%type;   
v_ename emp.ename%type;   
v_sal   emp.sal%type;
Begin	 
Open C1(10,3000);		  
Dbms_Output.put_line ('empno ' || ' ename ' || ' salary ');	 
Loop		   
Fetch C1 into v_empno, v_ename , v_sal;		  
Exit when C1%notfound;		  
Dbms_Output.put_line (  v_empno ||  '  ' ||  v_ename || '   '  ||  v_sal );	 
End loop;
Close C1;
End;
/

CURSOR FOR UPDATE:
Declare	
Cursor C1 is	   
select empno, ename , sal		 
from emp for update of emp.sal;	
v_empno emp.empno%type;	
v_ename emp.ename%type;	
v_sal   emp.sal%type; 
Begin	  
Open C1;		   
Dbms_Output.put_line ('empno ' || ' ename ' || ' salary ');	  
Loop			
Fetch C1 into v_empno, v_ename , v_sal;		   
if v_sal < 1000 then	
update emp set sal =v_sal + v_sal * 0.5	 
where current of  c1; 
end if;	 
Exit when C1%notfound;		   
Dbms_Output.put_line (  v_empno ||  '  ' ||  v_ename || '   '  ||  v_sal );
End loop;
Close C1;
End;
/
LPAD(salary,10,'*') = *****24000

RPAD(salary, 10, '*') =24000******

---------------------------------------------------------
Procedure :- 

هو مكان نكتب به كود معين و نخزنه بالذاكرة و عند الحاجة اليه نقوم بإستدعائه بإسمه مباشرة .


Trigger : -

هو مكان نكتب به كود معين و نخزنه بالذاكرة لكن هذا الكود ينطلق نتيجة حدث معين 
مثلا ً (After insert,After Delete, After Update, Before Delete, Before insert,before update) و هذا على مستوى قاعدة البيانات
أما على مستوى الـ Form فهنالك أمثلة كثيرة منها (pre-insert, poset-insert,when-button-pressed).
-----------------------------------------------------------
EXCEPTION:
create or replace function Get_emp_name(p_empno emp.empno%type) return  varchar2
is
l_ename emp.ename%type;
l_result emp.ename%type;
begin
if p_empno is null Then 
  raise_application_error(-20001,'لم يتم العثور على رقم الموظف');
end if;
select ename into l_ename 
from emp 
where empno =p_empno;
return l_ename;
Exception
  when others then 
	raise_application_error(-20001,'Error in get_Emp_name  :' || sqlerrm); 
end;
---------------------
declare
l_name varchar2(200);
begin
	l_name := get_emp_name(null);
	message(l_name);
exception
  when others Then 
message(sqlerrm);
end;
create or replace function my_calc(p_num1 number, p_num2 number , p_opr char :='+') return  number
is
l_Result number(4);
l_num1 number(2);
l_num2 number(2);
begin
 l_num1:=p_num1;
 l_num2:=p_num2;
 if p_opr ='-' Then 
l_Result := l_num1 - l_num2;
 elsif p_opr='*' Then 
l_Result := l_num1 * l_num2;
 elsif p_opr='/' Then 
l_Result := l_num1 / l_num2;
 else
l_Result := l_num1 + l_num2;
 end if;
 return(l_Result);
 exception 
	   when others then   
			raise_application_error(-20001,'Error in my_calc ' || sqlerrm);
end my_calc;
----------------------------------------
كود للبحث عن الجداول التي تحتوى على حقل معين, اذا كنت عارف الحقل بس مش عارف الجدول

select * from dba_tab_columns T
where COLUMN_NAME like UPPER('&COL%') AND TABLE_NAME LIKE UPPER('&TAB%') AND DATA_TYPE LIKE UPPER('&TYP%') AND OWNER LIKE UPPER('&OWNR%') 
منع الحذف بواسطة Trigger
CREATE OR REPLACE TRIGGER TRG_BEFORE_EMP BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
BEGIN
  IF(DELETING)THEN
 RAISE_APPLICATION_ERROR(-20000,'لا يجوز حذف أي بيانات من جدول الموظفين');
  END IF;
END;
/



CURSOR INSIDE FUNCTION

create or replace function fun_name(in_num number) return <data type>
as
 v_empnum emp_info.emp_num%type;
 v_sal	emp_info.sal%type;
 v_hours  emp_info.no_of_hours%type;
 cursor c is select emp_num ,sal,no_of_hours 
		  into v_empnum ,v_sal,v_hours 
		  from emp_info
		  where emp_num=in_num;
begin
 open c;
 
 loop
dbms_output.put_line(v_empnum || ' ' || v_sal || ' ' || v_hours);
fetch c into v_empnum,v_sal,v_hours;
exit when c%notfound;
 end loop;
 
 close c;
end;

-------------------------------------------
create or replace procedure CONSTRAINTS_MAKER
 ( TBL_NAME in VARCHAR2,ID_NAME in VARCHAR2) IS
BEGIN
 EXECUTE IMMEDIATE 'alter table '||TBL_NAME||' ADD  (constraint '|| TBL_NAME ||'_'||ID_NAME||'_PK' ||
 ' primary key  ('|| ID_NAME ||'))';
 commit;
 DBMS_OUTPUT.PUT_LINE('CONSTRAINT Primary key is ' ||   ID_NAME|| ' IN TABLE ' || TBL_NAME);
end;
/


create or replace procedure FK_CONSTRAINT_MAKER
( TBL_NAME in VARCHAR2,
ID_NAME in VARCHAR2,
PARENT_TBL in VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'alter table '||TBL_NAME||
' ADD(constraint '|| TBL_NAME ||'_'||ID_NAME||'_FK   
 FOREIGN key  ('|| ID_NAME ||')
 References '|| PARENT_TBL ||' ('||ID_NAME ||'))';
DBMS_OUTPUT.PUT_LINE('CONSTRAINT FOREIGN Key is ' || ID_NAME|| ' IN TABLE ' || TBL_NAME);
end;
/
example :
set serveroutput on;
execute FK_CONSTRAINT_MAKER ('INVOICE_DETAILS','INVOICE_NO','INVOICE');

----------------------------
لا تنسو الدعاء لاهلنا في غزة ولا تنسو الردود
التوفيق للجميع

تم تعديل 10 يناير 2009 بواسطة PALI.ORACLE

[Ahmad.Hasan]

لم يعجبكم الموضوع ؟
ههههههههههه
ارجو الاستفاده للجميع

[M.I.M]

يسلمو على المجهود ولي عوده للقرأه بتمعن

[samia]

جزاك الله خيرا
ننظر المزيد

[مبرمجة أوراكل]

شكرا أخي الكريم على الكودات المفيدة

استفدت منها كثيرا

[Ahmad.Hasan]

التوفيق للجميع
والسلام

[mohammad saad]

لم يعجبكم الموضوع ؟
ههههههههههه
ارجو الاستفاده للجميع

يا أخى الكريم ......... مشكور على المجهود وللللللللللللللللللكن ......... فى التوقيع الخاص بحضرتك، انت عاكس الآية القرآنية والتى تصحيحها كالآتى :

بسم الله الرحمن الرحيم "(00سيهديهم ويصلح بالهم 00ويدخلهم الجنة عرفها لهم00 ياآيها الذين أمنوا إن تنصروا الله ينصركم ويثبت أقدامكم 00 والذين كفروا فتعساً لهم وأضل أعمالهم)"

أرجو ان تراجع اللى انت كاتبه

شكراً
محمد سعد