تستطيع استخدام native Dynamic SQL لإنشاء إجرائيات وتوابع متعددة الاستخدام و تنفيذ تعليمة DDL ضمن PL-SQL , كما تستطيع استخدام Dynamic SQL لتنفيذ تعليمات SQL المبنية أثناء زمن التنفيذ (run time) ,تستطيع استخدام Dynamic SQL ضمن الحزم (packages) وكذلك ضمن anonymous PL/SQL Block
فمثلا تستطيع إنشاء إجرائية تقوم بإنشاء جدول والذي يأخذ اسمه كبارامتر دخل أو التي تأخذ عبارة where كبارامتر دخل .
ملاحظة : المستخدم الذي استخدمه في هذا الموضوع هو HR
Using Execute Immediate
الكود في المثال التالي ينشأ إجرائية تسمى ANYSTRING حيث تملك هذه الاجرائية بارامتر واحد وهو عبارة عن SQL Command , عندما تنفذ هذه الاجرائية سوف تقوم بتمرير SQL command , قد يكون هذا الأمر DML مثل (insert , update, delete) أو DDL مثل (create table)
Create or replace procedure ANYSTRING(String IN varchar2) as
Begin
Execute immediate (String);
End;
/
ملاحظة هامة : إذا تريد تنفيذ DDL Command من خلال dynamic SQL يجب أن تمنح صلاحيات النظام الضرورية صراحة وليس عبر Role , بمعنى إذا لم تمتلك صلاحية النظام Create table صراحة فأن الأمر سوف يفشل وستحصل على الرسالة التالية :
“ORA-01031 : insufficient privileges ” error :
إذن كيف تعرف أنك تمتلك هذه الصلاحيات ؟
نفذ الاستعلام التالي :
SELECT PRIVILEGE FROM USER_SYS_PRIVS;
إذا وجدت create table ضمن النتائج فأنك تستطيع تنفيذ الاجرائية السابقة
أما إذا لم تكن هذه الصلاحية موجودة ستتعلم كيف تقوم بمنحها .
قم بالاتصال بالمستخدم SYS بحيث تكتب في sqlplus في حال كنت متصل مسبقا بمستخدم سابق
Conn SYS / كلمة السر as sysdba
إذا لم تكن متصل بمستخدم قم بفتح sqlplus واكتب التالي في اسم المستخدم ومن ثم Enter أو موافق :
Sys/كلمة السر as sysdba
ولكي تعطي هذه الصلاحية create table صراحة للمستخدم hr اكتب الكود التالي :
Grant create table to hr;
بهذا تكون قد منحت الصلاحية صراحة للمستخدم HR .
الآن قم بالاتصال ب HR وقم بترجمة الإجرائية السابقة , في حال تم الترجمة من دون أخطاء تستطيع تنفيذ الاجرائية كالتالي :
Execute ANYSTRING(‘create table CD (Artist varchar2(25) , title varchar2(25))’);
هنا قمن بإنشاء جدول باسم CD ويحتوي عمودين , وكما تلاحظون أن تعليمة Create التي قمنا بتمريرها لا تحوي (;) في نهايتها ولا بد أن توضع ضمن علامتي تنصيص كما في المثال بالأعلى .
لتنفيذها مره أخرى :
Exec ANYSTRING(‘insert into CD values (‘’NEIL FINN’’,’’TRY WHISTLING THIS’’) ’);
ولا ننسى أن ننفذ commit حتى يتم تثبيت البيانات وكما تلاحظزن في المثال السابق اننا قمنا بإدخال القيم المحرفية بإضافة (single quotation) مرتين وذلك حتى أضمن أن تكون بالصيغة ‘NEIL FINN’
للتأكد جرب التالي :
select 'insert into CD values (''NEIL FINN'',''TRY WHISTLING THIS'') ' from dual
ستلاحظ الخرج كالتالي :
insert into CD values ('NEIL FINN','TRY WHISTLING THIS')
وهذا ما نريده .
وعندما تنفذ هذا الاستعلام لابد أن تحصل على سجل :
Select * from CD;
مثال آخر :
Declare
dname varchar2(20) :='Payroll';
Begin
Execute immediate 'delete from departments where department_name = :dname'
Using dname;
End;
قم بتنفيذ هذه الإجرائية ومن ثم أصدر الأمر التالي :
SELECT * FROM departments
where department_name='Payroll';
ستجد أنه لا يوجد سجل بهذا الاسم , ومن ثم قم بعمل rollback وجرب الأمر مره أخرى ستلاحظ وجود سجل مطابق.
مثال آخر :
سوف نستخدم اسم البارامتر الممرر للاجرائية كاسم للجدول الذي نريد انشاؤة من الجدول employees :
CREATE OR REPLACE procedure CreateTable(table_name varchar2) as
begin
execute immediate ('create table '||table_name ||' as select * from employees');
end;
/
قم بتنفيذ الإجرائية كالتالي :
Exec createtable(‘EMP09’);
ومن ثم نفذ الأمر التالي للتحقق :
SELECT * FROM EMP09
ستلاحظ أن الجدول قد تم إنشاؤه ويحتوي نفس بيانات الجدول employees .
Using DBMS_SQL
في النسخ السابقة ل oracle9i , لا بد من استخدام حزمة DBMS_SQL عند استخدامنا لـ Dynamic SQL , وتستطيع استخدامها كذلك في oracle9i كما ستلاحظ هما , إن إستخدام هذه الحزمة يعطيك تحكم كبير في تدفق المعالجة ضمن dynamic SQL , ولكن بشكل عام هي أكثر تعقيدا من native dynamic SQL والذي رأيناه سابقا .
الكود الموضح في المثال الآتي ينشأ إجرائية ANYSTRING التي رأيناها سابقا ولكن هنا باستخدام DBMS_SQL وسوف نسميها ANYSTRING1 حيث تمتلك هذه الاجرائية بارامتر دخل وحيد وهو عبارة عن SQL command :
CREATE OR REPLACE procedure ANYSTRING1(Str IN varchar2) as
cursor_name integer;
Ret Integer;
begin
cursor_name :=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.parse(cursor_name,Str,dbms_sql.native);
Ret :=DBMS_SQL.Execute (cursor_name);
DBMS_SQL.Close_cursor(cursor_name);
end;
/
لتنفيذ هذه الإجرائية :
نقوم أول بحذف الجدول السابق باستخدام الاجرائية كالتالي :
Exec ANYSTRING1(‘drop table CD’);
ومن ثم نقوم بإنشاؤه كالتالي :
Execute ANYSTRING1(‘create table CD (Artist varchar2(25) , title varchar2(25))’);
ومن ثم بنقوم بإدخال البيانات بإستخدام الاجرائية كذلك :
Exec ANYSTRING1(‘insert into CD values (‘’NEIL FINN’’,’’TRY WHISTLING THIS’’) ’);
شرح الاجرائية :
1- Open cursor : قمنا باستخدام هذا التابع الموجود في الحزمة DBMS_SQL لفتح Cursor ويعيد هذا التابع قيمة صحيحة وهي عبارة عن Cursor ID Number لذلك عرفنا المتحول cursor_name كمتحول صحيح ليحوي هذه القيمة .
2- Parse تقوم هذه الإجرائية بفحص ال syntax الخاص بالتعليمة المخزنة لدينا بالمتحول str وتربطه مع رقم المؤشر والذي حصلنا عليه في التعليمة السابقة .
تمتلك هذه الإجرائية ثلاثة بارامترات رقم المؤشر , الأمر الذي سوف نفحصه و راية اللغة ويحدد هذا الأخير السلوك للتعليمة وفي حالتنا اخترنا native .
3- Execute : هذا التابع يقوم بتنفيذ تعليمة SQL الديناميكية ولا بد من تمرير رقم المؤشر والذي ربطناه سابقا بالتعليمة , وكما تلاحظون أن هذا التابع يعيد رقم أو يعيد عدد الأسطر التي تم معالجتها (في حال استخدمنا تعليمة DML) .
4- Close_cursor تقوم هذه الاجرائية بإغلاق المؤشر المفتوح ويملك بارامتر وهو عبارة عن رقم المؤشر , إن إغلاق المؤشر يحرر الذاكرة المستخدمة ويزيد من عدد ال cursor المستخدمين في هذه الجلسة .
ملاحظة : إن الحزمة DBMS_SQL تحتوي على العديد من التوابع والاجرائيات الأخرى التي لم أتطرق لها هنا ولمزيد من المعلومات تستطيع ان تبحث على شبكة الانترنت .
ليست هناك تعليقات:
إرسال تعليق