آموزش Dynamic SQL

🔸 dynamic SQL

آموزش Dynamic SQL : در این مقاله به معرفی مجموعه دستوراتی که با عنوان dynamic SQL شناخته می‌شوند پرداخته و به ارائه توضیحاتی پیرامون تعریف ، معرفی انواع گوناگون و کاربرد dynamic SQL  در برنامه‌های نوشته‌شده با زبان PL/SQL خواهیم پرداخت.

دستور dynamic SQL چیست ؟

• هرگاه در کد نوشته‌شده با زبان PL/SQL از عبارت execute immediate همراه با دستور SQL ، DDL ، TCL که در متغیری از جنس رشته قرار داده‌شده است مواجه شدید از dynamic SQL استفاده‌شده اس
• به این نکته دقت کنید که وقتی از رشته استفاده می‌کنیم می‌توانیم در زمان اجرا عبارت‌های موردنظر را به آن اضافه کرده و دستور SQL یا ساختار بلاک را در زمان اجرا ساخته و آن را اجرا کنیم

در چه مواقعی باید از dynamic SQL استفاده کنیم ؟

• هرگاه همه یا بخشی از دستور SQL تا قبل از زمان اجرا (Runtime) مشخص نیست باید از dynamic SQL استفاده کنیم
• در مواقعی که می‌خواهیم دستور DDL را در برنامه‌ای که با زبان PL/SQL نوشته‌شده است را اجرا کنیم باید از dynamic SQL استفاده کنیم
• اگر می‌خواهیم ار دستورات DCL در زبان PL/SQL استفاده کنیم باید از dynamic SQL استفاده کنیم
• اگر می‌خواهیم برنامه‌ای بنویسم که عمومی‌تر باشد و مانند ابزار بتواند حالت‌های گوناگونی را پشتیبانی کند می‌توانیم از dynamic SQL استفاده کنیم

انواع dynamic SQL کدام هستند ؟
آموزش Dynamic SQL : حالت یک: در مواقعی است که می‌خواهیم از دستور DDL یا DCL در برنامه PL/SQL استفاده کنیم که به شکل ذیل استفاده می‌گردد 👇

execute immediate ‘DDL  یا DCL دستور’

به مثال ذیل دقت کنید 👇👇👇

 

begin
execute immediate ‘
create table EMP_DYN
(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25) not null,
email VARCHAR2(25) not null,
phone_number VARCHAR2(20),
hire_date DATE not null,
job_id VARCHAR2(10) not null,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4) )’;

end;

❇️ در مثال فوق از دستور DDL برای ایجاد جدول در متغیر از جنس رشته استفاده‌شده است ❇️
❇️ همان‌طور که مشاهده می‌کنید داخل عبارت رشته‌ای دستور مربوط به dynamic SQL سمی کولان (;) وجود ندارد ❇️

همان‌طور که در مثال فوق مشاهده کردید از سایر دستورات DDL یا DCL نیز می‌توانیم همانند مثال فوق استفاده کنیم

آموزش Dynamic SQL : حالت دو : در مواقعی است که می‌خواهیم از دستور DML در برنامه PL/SQL استفاده کنیم که تعداد پارامترها یا تعداد ستون‌های دستور فوق تا زمان اجرا (Runtime)  نامشخص است در این حالت باید از نگارش ذیل استفاده گردد 👇👇👇

execute immediate ‘DML Statement ‘ using value_of_parameter1 , value_of_parameter2 , ….

به مثال ذیل دقت کنید 👇👇👇

declare
v_sql clob;
v_dep_id_value number := &p_dep_id_value;
v_dep_name_value varchar2(100) := &p_dep_name_value;
v_mananger_id number := &p_mananger_id;
v_location_id number := &p_location_id;

begin

if v_mananger_id is null and v_location_id is null then
v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value;

elsif v_mananger_id is not null and v_location_id is null then
v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name, manager_id)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name, :manager_id)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value, v_mananger_id;

elsif v_mananger_id is null and v_location_id is not null then
v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name, location_id)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name, :location_id)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value, v_location_id;

else v_mananger_id is not null and v_location_id is not null then
v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name, manager_id, location_id)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name, :manager_id,:location_id)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value, v_mananger_id, v_location_id;
end if;

end;

❇️ در مثال فوق از دستور DML برای درج رکورد جدید در جدول سازمان‌ها استفاده‌شده است ، برنامه فوق بر اساس مقادیر پارامترهای واردشده تصمیم‌گیری می‌کند که از کدام‌یک از حالت‌های فوق باید استفاده شود ❇️

❇️ به کاربرد دستور using در مثال فوق دقت کنید که مقادیر پارامترهای واردشده توسط کاربر را به دستور dynamic SQL فوق تزریق می‌کند ❇️

👈 اگر مقدار پارامترهای p_mananger_id و p_location_id که توسط کاربر وارد می‌شود null  باشد از دستور ذیل استفاده می‌کند که در نام ستون‌ها و مقادیر متناظرشان دو ستون mananger_id و location_id وجود ندارد

v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value;

👈 اگر مقدار پارامترها p_location_id که توسط کاربر وارد می‌شود null باشد از دستور ذیل استفاده می‌کند که در نام ستون و مقادیر متناظر location_id وجود ندارد

v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name, manager_id)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name, :manager_id)’;

using v_dep_id_value, v_dep_name_value, v_mananger_id;

👈 اگر مقدار پارامترها p_mananger_id که توسط کاربر وارد می‌شود null باشد از دستور ذیل استفاده می‌کند که در نام ستون و مقادیر متناظر p_mananger_id وجود ندارد

v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name, location_id)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name, :location_id)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value, v_location_id;

👈 اگر مقدار پارامترها p_mananger_id و p_location_id که توسط کاربر وارد می‌شود null نباشد از دستور ذیل استفاده می‌کند

v_sql := ‘insert into departments’ || chr(10) ||
‘ (department_id, department_name, manager_id, location_id)’ ||
chr(10) || ‘ values’ || chr(10) ||
‘ (:department_id, :department_name, :manager_id,:location_id)’;

execute immediate v_sql
using v_dep_id_value, v_dep_name_value, v_mananger_id, v_location_id;
end if;

آموزش Dynamic SQL : حالت سه : در مواقعی استفاده می‌شود که می‌خواهیم از دستور select در برنامه PL/SQL  استفاده کنیم که نام جدول و ستون‌ها یا کل پرس و تا زمان اجرا مشخص نیست در این مواقع می‌توان از دو نگارش ذیل استفاده کنیم 👇

🅰️execute immediate ‘Select Statment’ into variable1,variable2,.. using parameter_value1, parameter_value2 , …

🅱️open sys_refcursor for ‘Select Statment’;

به دستورات زیر که مثالی از حالت 🅰️ است دقت کنید 👇👇👇

declare
v_dep_id number := &p_dep_id;
v_cnt pls_integer;
begin
execute immediate ‘select count(*) from employees e where
e.department_id = :p_dep_id ‘
into v_cnt
using v_dep_id;

dbms_output.put_line(v_cnt);
end;

❇️ در مثال فوق مقدار پارامتر شناسه سازمان (p_dep_id) از کاربر پرسیده شده و تعداد کارمندانی که در سازمان واردشده مشغول به‎کار هستند را در خروجی نمایش می‌دهد  ❇️

❇️ در مثال فوق به کاربرد عبارت‌های using و into دقت کنید ، برای مقداردهی خروجی پرس‌وجو از into و برای ارسال پارامتر به dynamic SQL از using استفاده‌شده است ❇️

به دستورات زیر که مثالی از حالت 🅱️ است دقت کنید 👇👇👇

declare
v_table_name varchar2(100) := &p_table_name;
v_field_name varchar2(100) := &p_field_name;
v_where_caluse varchar2(100) := &p_where_caluse;
v_field_result varchar2(100);
v_ref sys_refcursor;
begin
open v_ref for ‘select ‘ || v_field_name || ‘ from ‘ || v_table_name ||
case when v_where_caluse is not null then ‘ where ‘ || v_where_caluse else null end;
loop
fetch v_ref
into v_field_result;
exit when v_ref%notfound;
dbms_output.put_line(v_field_result);
end loop;
close v_ref;
end;

❇️ در مثال فوق از جدولی که کاربر وارد کرده است رکوردها موردنظر را بر اساس فیلتر واردشده توسط کاربر استخراج کرده و ستون موردنظر کاربر را در خروجی چاپ می‌کند ❇️

آموزش Dynamic SQL : حالت چهار : در مواقعی استفاده می‌شود که می‌خواهیم کل بلاک PL/SQL را به‌صورت پویا نوشته و اجرا کنیم

به مثال ذیل دقت کنید 👇👇👇

declare
v_emp_id employees.employee_id%type := &p_emp_id;
v_block_plsql clob;
v_result number;
begin

v_block_plsql := ‘ declare’ || chr(10) ||
‘ v_res number; v_emp_row employees%rowtype ;’ ||
chr(10) || ‘ begin’ || chr(10) ||
‘ v_emp_row := get_emp(:p_emp_id);
:v_res := v_emp_row.salary * 12 ;’ || chr(10) ||
‘ end;’;
execute immediate v_block_plsql
using v_emp_id, out v_result;
dbms_output.put_line(v_result);

end;

❇️در مثال فوق کل بلاک در متغیری رشته‌ای قرار داده‌شده است ❇️
❇️شناسه کارمند از کاربر پرسیده شده و در بلاک به تابع get_emp ارسال می‌شود ، خروجی تابع فوق یک سطر از اطلاعات کارمند فوق است که مقدار حقوق کارمند بازگردانده شده را در ۱۲ ضرب کرده و حقوق سالیانه را به‌دست می‌آورد ، درنهایت مقدار حقوق سالیانه در متغیر v_result بازگردانده می‌شود ❇️
❇️ به نوع متغیر v_result که از نوع out تعریف‌شده است دقت کنید ❇️
❇️ در مثال فوق از تابعی بانام get_emp استفاده‌شده که متن تابع فوق به‌صورت ذیل می‌باشد 👇👇👇❇️

create or replace function get_emp(p_emp_id number)
return employees%rowtype is
v_res employees%rowtype;
begin

if p_emp_id is null then
raise_application_error(-20900,’پارامتر وردي خالي است’);
end if;

execute immediate ‘select * from employees e
where e.employee_id = :p_emp_id ‘
into v_res
using p_emp_id;
return v_res;

end get_emp;

 

 

مولف و مدرس اوراکل : جناب آقای مهندس علی ابراهیمی‌فرد

• 
• 
• 
• 
•