استفاده از مکان نما و حلقه ها در Transact-SQL. مکان نماها در MySQL رویه های ذخیره شده مکان نماها در سرور sql

دستور DECLARE CURSOR به شما اجازه می دهد تا رکوردها را از یک جدول ردیف به ردیف برای دستکاری بازیابی کنید. این امکان پردازش ردیف به ردیف را به جای پردازش مجموعه داده های سنتی که SQL انجام می دهد را فراهم می کند.

به عنوان اولین تقریب، مراحل زیر هنگام کار با مکان نما استفاده می شود.

مکان نما با دستور DECLARE ایجاد می شود. مکان نما با دستور OPEN باز می شود.

عملیات با مکان نما با استفاده از دستور FETCH انجام می شود. مکان نما با دستور CLOSE بسته می شود.

دستور DECLARE CURSOR یک عبارت SELECT را مشخص می کند. هر ردیفی که توسط دستور SELECT برگردانده می شود می تواند به صورت جداگانه بازیابی و پردازش شود. مثال زیر Oracle یک مکان نما را در یک بلوک اعلان به همراه چندین متغیر دیگر اعلام می کند. پس از این، در بلوک BEGIN...END بعدی، مکان نما باز می شود، انتخابی از آن انجام می شود و مکان نما بسته می شود.

CURSOR title_price_cursor عنوان انتخاب شده است، قیمت از عناوین

جایی که قیمت تهی نیست. title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);

OPEN title_price_Cursor;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES

(title_price_val.title, new_price) CLOSE title_price_cursor; پایان؛

از آنجایی که این مثال از PL/SQL استفاده می کند، ما در این کتاب به توضیح بیشتر کد نمی پردازیم. با این حال، بلوک DECLARE به وضوح اعلان مکان نما را نشان می دهد. در یک بلوک PL/SQL اجرایی، مکان نما با دستور OPEN مقداردهی اولیه می شود، مقادیر با دستور FETCH بازیابی می شوند و در نهایت مکان نما با دستور CLOSE بسته می شود.

دستور SELECT اساس مکان نما است، بنابراین تمرین خوبی است که قبل از گنجاندن آن در عبارت DECLARE CURSOR، آن را به طور کامل آزمایش کنید. دستور SELECT می تواند روی جدول یا نمای زیرین عمل کند. بنابراین، نشانگرهای فقط خواندنی می توانند با نماهای غیرقابل به روز رسانی کار کنند. یک دستور SELECT می تواند شامل بندهایی مانند ORDER BY، GROUP BY و HAVING باشد تا زمانی که این بندها جدول منبع را به روز نکنند. اگر مکان نما به عنوان FOR UPDATE تعریف شده است، توصیه می شود چنین جملاتی را از دستور SELECT حذف کنید.

نشانگرهای محلی اغلب به عنوان پارامترهای خروجی برای رویه های ذخیره شده استفاده می شوند. بنابراین، می توانید یک مکان نما را در یک رویه ذخیره شده تعریف و پر کنید و آن را به کار دسته ای فراخوانی یا رویه ذخیره شده ارسال کنید.

در مثال ساده DB2 زیر، ما مکان نما را اعلام می کنیم که شماره بخش ها، نام بخش ها و شماره های مدیر را در گروه admin "XO1" جستجو می کند.

نشانگر dept_cursor CURSOR

برای انتخاب dept_nbr، dept_name، mgr_nbr

WHERE admin_group="X01"

سفارش بر اساس d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

مثال زیر مایکروسافت SQL Server یک مکان نما را برای جدول ناشران اعلام و باز می کند. مکان نما اولین رکوردی را که با عبارت SELECT مطابقت دارد از جدول منتشرکنندگان انتخاب می کند و آن را در جدول دیگری قرار می دهد. سپس به رکورد بعدی و سپس رکورد بعدی می رود تا زمانی که همه رکوردها پردازش شوند. در نهایت، مکان نما بسته می شود و حافظه آزاد می شود (دستور DEALLOCATE فقط در Microsoft SQL Server استفاده می شود).

اعلام @publisher_name VARCHAR(20)

اعلام مکاننما pub_cursor برای انتخاب pub_name از ناشران WHERE کشور "USA"

واکشی بعدی از pub_cursor به publisher_name

WHILE @s>FETCH_STATUS=0

INSERT INTO Foreign_publishers VALUES("j>publisher_name)

CLOSE pub_cursor DEALLOCATE pub_cursor

در این مثال، می توانید مکان نما را در حال حرکت در مجموعه ای از رکوردها ببینید. (این مثال فقط برای نشان دادن ایده است، زیرا در واقعیت وجود دارد بهترین راهراه حل این مشکل، یعنی دستورالعمل INSERT، SELECT.)

در موارد زیر کاربرد دارد: SQL Server (از سال 2008) پایه داده های SQLانبار داده موازی Azure SQL Data Warehouse

ویژگی‌های مکان‌نمای سرور Transact-SQL را تعریف می‌کند، مانند ویژگی‌های view و پرس و جوی مورد استفاده برای ساخت مجموعه نتایجی که مکان‌نما بر روی آن کار می‌کند. عبارت DECLARE CURSOR از هر دو نحو استاندارد ISO و نحوی که از مجموعه پسوند زبان Transact-SQL استفاده می کند، پشتیبانی می کند.

نحو ISO اعلام نام مکان نما [ غیر حساس ] [ SCROLL ] CURSOR FOR select_statement [ برای (فقط خواندن | به روز رسانی [ OF column_name [ ,...n ] ] ) ] [;] Transact-SQL Extended Syntax مکان نما را اعلام کنید نام مکان [ محلی | GLOBAL ] [ FORWARD_ONLY | اسکرول ] [ ایستا | کیست | پویا | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | خوش بینانه ] [ TYPE_WARNING ] برای select_statement [ برای به روز رسانی [ OF column_name [ ,...n ] ] ] [;]

مکان نما
مکان نما

غیر حساس
tempdb; بنابراین، تغییرات جداول زیربنایی در داده های بازگردانده شده توسط انتخاب های این مکان نما منعکس نمی شود و این مکان نما قابل تغییر نیست. هنگام استفاده از نحو ISO، مگر اینکه گزینه INSENSITIVE مشخص شده باشد، به روز رسانی های متعهد و حذف های انجام شده در جداول پایه در انتخاب های بعدی ظاهر می شوند.

طومار
نشان می دهد که همه گزینه های نمونه گیری در دسترس هستند (FIRST، LAST، PRIOR، NEXT، RELATIVE، ABSOLUTE). اگر عبارت ISO DECLARE CURSOR گزینه SCROLL را مشخص نکند، فقط گزینه واکشی NEXT پشتیبانی می شود. گزینه SCROLL را نمی توان با گزینه FAST_FORWARD مشخص کرد.

select_statement
یک دستور SELECT استاندارد که مجموعه نتایج یک مکان نما را مشخص می کند. کلمات کلیدی FOR BROWSE و INTO مجاز نیستند select_statementاعلام مکان نما

select_statementدر تضاد با مکان نما از نوع درخواستی.

فقط خواندنی

به روز رسانی ]
نام ستون [, .. .n] مشخص شده است، فقط ستون های فهرست شده اجازه تغییرات را می دهند. اگر دستور UPDATE بدون لیستی از ستون ها استفاده شود، به روز رسانی برای همه ستون ها امکان پذیر است.

مکان نما
نام Transact-SQL مکان نما سرور خاص. مکان نماباید قوانین مربوط به شناسه ها را رعایت کند.

محلی
نشان می دهد که مکان نما محلی برای بسته، رویه ذخیره شده یا تریگری است که در آن ایجاد شده است. نام مکان نما فقط در این ناحیه معتبر است. مکان نما را می توان با متغیرهای محلی بسته، رویه های ذخیره شده، تریگرها یا پارامتر خروجی یک رویه ذخیره شده ارجاع داد. پارامتر OUTPUT برای ارسال مکان‌نمای محلی به بسته فراخوانی، رویه ذخیره‌شده یا ماشه استفاده می‌شود، که سپس می‌تواند پارامتر را به یک متغیر مکان‌نما برای دسترسی بعدی به مکان‌نما پس از تکمیل فرآیند ذخیره‌شده اختصاص دهد. مکان نما به طور ضمنی آزاد می شود زمانی که دسته، رویه ذخیره شده یا تریگر اجرا را کامل می کند، مگر اینکه مکان نما به پارامتر OUTPUT داده شود. اگر مکان نما به پارامتر OUTPUT داده شود، هنگامی که همه متغیرهایی که به آن ارجاع می دهند آزاد می شوند یا زمانی که محدوده خارج می شود، مکان نما آزاد می شود.

جهانی
نشان می دهد که مکان نما برای اتصال سراسری است. نام مکان نما را می توان توسط هر رویه یا بسته ذخیره شده ای که روی اتصال اجرا می شود استفاده کرد. مکان نما تنها در صورت قطع اتصال به طور ضمنی آزاد می شود.

FORWARD_ONLY
مشخص می کند که مکان نما فقط از خط اول تا آخر قابل مشاهده است. فقط گزینه FETCH NEXT fetch پشتیبانی می شود. اگر FORWARD_ONLY بدون کلیدواژه های STATIC، KEYSET یا DYNAMIC مشخص شده باشد، مکان نما مانند مکان نما DYNAMIC رفتار می کند. اگر نه آرگومان FORWARD_ONLY و نه آرگومان SCROLL مشخص نشده باشد، آرگومان پیش فرض FORWARD_ONLY است مگر اینکه کلیدواژه های STATIC، KEYSET یا DYNAMIC وجود داشته باشند. نشانگرهای STATIC، KEYSET و DYNAMIC دارای مقدار پیش فرض SCROLL هستند. برخلاف APIهای پایگاه داده مانند ODBC و ADO، حالت FORWARD_ONLY توسط مکان نماهای Transact-SQL زیر پشتیبانی می شود: STATIC، KEYSET، و DYNAMIC.

ایستا
مکان نما را تعریف می کند که یک کپی موقت از داده ها را برای استفاده توسط مکان نما ایجاد می کند. همه پرس و جوهای مکان نما به جدول موقت مشخص شده در داخل دسترسی دارند tempdb; بنابراین، تغییرات جداول زیربنایی در داده های بازگردانده شده توسط انتخاب های این مکان نما منعکس نمی شود و این مکان نما قابل تغییر نیست.

KEYSET
نشان می دهد که عضویت یا ترتیب ردیف ها در مکان نما با باز شدن آن بدون تغییر است. مجموعه‌ای از کلیدها که ردیف‌ها را به‌طور منحصربه‌فرد شناسایی می‌کنند در جدول تعبیه شده‌اند tempdbتماس گرفت کلیدها.

تغییرات مقادیر غیر کلیدی در جداول پایه که توسط مالک مکان نما انجام شده یا توسط سایر کاربران انجام شده است، زمانی که مالک مکان نما آن را مشاهده می کند، نمایش داده می شود. تغییرات ایجاد شده توسط سایر کاربران منعکس نمی شود (تغییرات را نمی توان با استفاده از مکان نما سرور Transact-SQL ایجاد کرد). اگر یک ردیف حذف شود، تلاش برای واکشی ردیف‌ها @@FETCH_STATUS -2 را برمی‌گرداند. به روز رسانی مقادیر کلیدی در سراسر مرزهای مکان نما مشابه حذف یک ردیف قدیمی و سپس درج یک ردیف جدید است. سطر با مقادیر جدید قابل مشاهده نیست و تلاش برای واکشی ردیف با مقادیر قدیمی @@FETCH_STATUS -2 را برمی‌گرداند. اگر به‌روزرسانی‌ها از طریق مکان‌نما با استفاده از عبارت WHERE CURRENT OF انجام شوند، فوراً قابل مشاهده هستند.

پویا
مکان نما را تعریف می کند که تمام تغییرات داده های ایجاد شده در ردیف های مجموعه نتیجه را هنگام مشاهده این مکان نما نشان می دهد. مقادیر داده، ترتیب، و عضویت ردیف در هر انتخاب ممکن است متفاوت باشد. گزینه انتخاب ABSOLUTE توسط مکان نماهای پویا پشتیبانی نمی شود.

FAST_FORWARD
یک مکان نما FORWARD_ONLY، READ_ONLY را نشان می دهد که بهینه سازی عملکرد را فعال کرده است. گزینه FAST_FORWARD را نمی توان با گزینه های SCROLL یا FOR_UPDATE مشخص کرد.

فقط خواندنی
از تغییرات ایجاد شده از طریق این مکان نما جلوگیری می کند. عبارت WHERE CURRENT OF نمی تواند به مکان نما در عبارت UPDATE یا DELETE اشاره کند. این گزینه بر ویژگی پیش‌فرض به‌روزرسانی مکان‌نما اولویت دارد.

SCROLL_LOCKS
نشان می‌دهد که به‌روزرسانی‌ها یا حذف‌های انجام‌شده با استفاده از مکان‌نما تضمینی برای موفقیت هستند. SQL Server ردیف‌ها را هنگام خواندن در مکان‌نما قفل می‌کند تا اطمینان حاصل کند که آن ردیف‌ها برای تغییرات بعدی در دسترس هستند. گزینه SCROLL_LOCKS را نمی توان با گزینه FAST_FORWARD یا STATIC مشخص کرد.

خوشبین
مشخص می کند که اگر ردیف از زمانی که در مکان نما خوانده شده به روز شده باشد، به روز رسانی ها یا حذف های انجام شده با استفاده از مکان نما ناموفق خواهند بود. SQL Server سطرها را هنگام خواندن در مکان نما قفل نمی کند. به جای آن از مقایسه استفاده می شود مهر زمانیمقادیر ستون یا چک جمع ها، اگر در جدول نباشد مهر زمانیستونی برای تعیین اینکه آیا ردیف از زمانی که در مکان نما خوانده شده تغییر کرده است یا خیر. اگر ردیف اصلاح شده باشد، تلاش‌ها برای به‌روزرسانی یا حذف موقعیت‌یابی ناموفق خواهند بود. گزینه OPTIMISTIC را نمی توان با گزینه FAST_FORWARD مشخص کرد.

TYPE_WARNING
مشخص می کند که اگر مکان نما به طور ضمنی از یک نوع درخواستی به نوع دیگر تبدیل شود، یک هشدار برای مشتری ارسال می شود.

select_statement
یک دستور SELECT استاندارد که مجموعه نتایج یک مکان نما را مشخص می کند. کلمات کلیدی COMPUTE، COMPUTE BY، FOR BROWSE و INTO در select_statementاعلام مکان نما

SQL Server به طور ضمنی مکان نما را در صورت وجود بند به نوع دیگری تبدیل می کند select_statementدر تضاد با مکان نما از نوع درخواستی. برای اطلاعات بیشتر، به تبدیل مکان‌نمای ضمنی مراجعه کنید.

برای به روز رسانی]
ستون هایی را در مکان نما که به روز می شوند را تعریف می کند. اگر OF نام ستون [, ... n] ارائه شده است، فقط ستون های فهرست شده اجازه تغییرات را می دهند. اگر دستور UPDATE بدون فهرست ستونی استفاده شود، به روز رسانی برای همه ستون ها امکان پذیر است مگر اینکه گزینه همزمانی READ_ONLY مشخص شده باشد.

دستور DECLARE CURSOR ویژگی‌های مکان‌نمای سرور Transact-SQL را تعریف می‌کند، مانند ویژگی‌های view و پرس و جوی مورد استفاده برای ساخت مجموعه نتایجی که مکان‌نما بر روی آن کار می‌کند. دستور OPEN مجموعه نتایج را پر می کند و دستور FETCH یک ردیف از آن را برمی گرداند. دستور CLOSE مجموعه نتایج فعلی مرتبط با مکان نما را پاک می کند. دستور DEALLOCATE منابع مورد استفاده مکان نما را آزاد می کند.

شکل اول عبارت DECLARE CURSOR از نحو ISO برای تعیین پارامترهای مکان نما استفاده می کند. شکل دوم عبارت DECLARE CURSOR از پسوندهای زبان Transact-SQL استفاده می کند که به شما امکان می دهد مکان نماها را با استفاده از همان انواع مورد استفاده در توابع مکان نما APIهای پایگاه داده مانند ODBC و ADO تعریف کنید.

این دو شکل را نمی توان با هم مخلوط کرد. اگر قبل از کلمه کلیدی CURSOR SCROLL را مشخص کنید یا کلمات کلیدی را حذف کنید، نمی توانید از کلمات کلیدی بین CURSOR و همچنین برای select_statementکلید واژه ها. هنگام تعیین کلمات کلیدی بین مکان نما، و همچنین برای select_statementکلمات کلیدی، شما نمی توانید SCROLL یا INSENSITIVE را قبل از کلیدواژه CURSOR مشخص کنید.

اگر از دستور Transact-SQL برای عبارت DECLARE CURSOR استفاده می‌کنید و گزینه‌های READ_ONLY، OPTIMISTIC یا SCROLL_LOCKS را مشخص نمی‌کنید، مقدار پیش‌فرض زیر در نظر گرفته می‌شود.

اگر عبارت SELECT از به‌روزرسانی‌ها پشتیبانی نمی‌کند (یا مجوزهای کافی ندارد، یا به جداول راه دوری که به‌روزرسانی‌ها را پشتیبانی نمی‌کنند و غیره دسترسی دارد)، مکان‌نما روی READ_ONLY تنظیم می‌شود.

نشانگر STATIC و FAST_FORWARD به طور پیش فرض روی READ_ONLY است.

نشانگرهای DYNAMIC و KEYSET به طور پیش فرض روی OPTIMISTIC هستند.

نشانگرها را فقط می توان با سایر دستورات Transact-SQL ارجاع داد. توابع API پایگاه داده نمی توانند نشانگرها را ارجاع دهند. به عنوان مثال، هنگامی که یک مکان نما اعلام می شود، توابع و متدهای OLE DB، ODBC یا ADO نمی توانند به نام آن اشاره کنند. ردیف های مکان نما را نمی توان با استفاده از توابع و روش های API مربوطه انتخاب کرد. برای این منظور باید از دستورات Transact-SQL FETCH استفاده شود.

رویه های ذخیره شده زیر را می توان برای تعریف ویژگی های مکان نما پس از اعلام آن استفاده کرد.

متغیرها را می توان به عنوان بخشی استفاده کرد select_statement، که در آن مکان نما اعلام شده است. مقادیر متغیرهای مکان نما پس از اعلام تغییر نمی کند.

به طور پیش فرض، مجوزهای DECLARE CURSOR به همه کاربرانی که مجوز SELECT در نماها، جداول و ستون های مورد استفاده مکان نما دارند، اعطا می شود.

شما نمی توانید از نشانگرها یا تریگرها در جدولی با نمایه فروشگاه ستونی خوشه ای استفاده کنید. این محدودیت برای شاخص های غیر خوشه ای اعمال نمی شود. می‌توانید از نشانگرها و ماشه‌ها در جدولی با فهرست ذخیره‌سازی ستون‌های غیر خوشه‌ای استفاده کنید.

الف. استفاده از مکان نما و نحو ساده

مجموعه نتیجه ای که هنگام باز کردن این مکان نما ایجاد می شود شامل تمام سطرها و ستون های جدول است. این مکان نما را می توان به روز کرد، تمام به روز رسانی ها و حذف ها در انتخاب این مکان نما نشان داده شده است. FETCH``NEXT فقط به این دلیل واکشی می شود که پارامتر SCROLL مشخص نشده است.

اعلام مکان نما vend_cursor برای انتخاب * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor.

ب. استفاده از نشانگرهای تو در تو برای نمایش گزارش

مثال زیر از مکان نماهای تو در تو برای نمایش یک گزارش پیچیده استفاده می کند. برای هر ارائه دهنده یک مکان نما داخلی اعلام می شود.

SET NOCOUNT ON ; اعلام @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 );چاپ" -------- گزارش محصولات فروشنده --------"؛ اعلام مکان نما vendor_cursor برای شناسه فروشنده انتخاب شده، نام از خرید.فروشنده WHERE PreferredVendorStatus = 1 ORDER BY VendorID.باز کردن vendor_cursor واکشی بعدی از vendor_cursor به @vendor_id، @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- محصولات از فروشنده:"+ @vendor_name PRINT @message - یک مکان نما داخلی را بر اساس اعلام کنید -- در vendor_id از مکان نما بیرونی. CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id اعلام کنید - مقدار متغیر از مکان نما بیرونیباز کردن product_cursor واکشی بعدی از product_cursor به @product IF @@FETCH_STATUS<>0 چاپ "<>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- فروشنده بعدی را دریافت کنید. FETCH NEXT FROM vendor @vendor_vendor_ END CLOSE vendor_cursor; DEALLOCATE vendor_cursor;

مکان نما پیوندی به ناحیه حافظه متنی است. در برخی از پیاده سازی های زبان برنامه نویسی SQL (اوراکل، مایکروسافت SQL Server) - مجموعه نتیجه به دست آمده هنگام اجرای یک پرس و جو و اشاره گر رکورد فعلی مرتبط با آن. من می گویم که مکان نما یک جدول مجازی است که نشان دهنده یک ذخیره سازی داده های جایگزین است. در این حالت مکان نما به شما اجازه می دهد تا به داده های آن به گونه ای دسترسی داشته باشید که گویی داده های یک آرایه معمولی است.
نشانگرها در رویه های ذخیره شده استفاده می شوند. نظریه کافی است، بیایید به یک مثال نگاه کنیم:
ما یک پایگاه داده داریم (دیتابیس کمی خوب نیست، این یکی از پایگاه های من است کار آزمایشگاهی، اما معلم پایگاه داده ما بر چنین ساختاری اصرار داشت)

/*اطلاعات بانکی*/
ایجاد جدول «بانک» (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


کلید اولیه ('BankId')

)ENGINE=InnoDB
مجموعه کاراکتر "utf8" COLLATE "utf8_bin" ;
/*اطلاعات مربوط به سپرده ها */
ایجاد جدول «توزیع بانکی» (
"BankId" INTEGER (11) NOT NULL،
عدد صحیح (11) DEFAULT NULL،
"ContributeAmount" اعشاری (10،0) NOT NULL،
"ClientId" INTEGER (11) NOT NULL،
کلید اصلی ('BankId'، 'ClientId')،
KEY `BankId` (`BankId`)،
KEY "ClientId" ("ClientId")،
محدودیت "bankdistribution_fk" کلید خارجی ("BankId") مراجع "bank" ("BankId")،
محدودیت «bankdistribution_fk1» کلید خارجی («ClientId») مراجع «مشتری» («ClientId»)
)ENGINE=InnoDB
/*داده های سرمایه گذاران*/
ایجاد جدول «مشتری» (
«ClientId» INTEGER (3) NOT NULL AUTO_INCREMENT،
"CreditCardId" BIGINT(10) NOT NULL،
`نام خانوادگی` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`تلفن` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`آدرس` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
"SafeId" INTEGER (5) NOT NULL،
کلید اصلی ('ClientId'، 'CreditCardId')،
KEY "ClientId" ("ClientId")

)ENGINE=InnoDB
AUTO_INCREMENT=11 مجموعه کاراکتر "utf8" جمع آوری "utf8_bin"

فرض کنید باید هر بانک را به نوبه خود دریافت کنیم و برخی اقدامات را با آن انجام دهیم، پرس و جو زیر می تواند در این مورد به ما کمک کند.

«بانک» را انتخاب کنید.* از تعداد محدود «بانک» که نیاز داریم، 1

. بنابراین، با استفاده از LIMIT WE NEED_RECORD NUMBER, 1، هر رکورد را در یک حلقه از جدول بانک استخراج می کنیم و اقدامات مورد نیاز خود را با آن انجام می دهیم، در حالی که مقدار WE NEED_RECORD NUMBER را به 1 افزایش می دهیم. اکنون همین کار را انجام می دهیم اما با استفاده از یک مکان نما

شروع
/* متغیرهایی که داده ها را استخراج می کنیم */
vBankId عدد صحیح را اعلام کنید.
اعلام vBankName VARCHAR (50);
اعلان vAddress VARCHAR(50);
اعلام vPhone VARCHAR (50);
/* متغیر هادلر - a*/
اعلان انجام شده عدد صحیح پیش فرض 0;
/*اعلان مکان نما*/
اعلام مکان نما بانک برای انتخاب «bank».`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone, FROM`bank` where 1
/*هدف HANDLER که در ادامه توضیح داده خواهد شد*/
DECLARE CONTINUE Handler FOR SQLSTATE "02000" SET done=1;
/* باز کردن مکان نما */
باز کردن نشانگر بانک؛
/*بازیابی اطلاعات*/
WHILE انجام شد = 0 DO

ما اقدامات مورد نیاز خود را انجام می دهیم
END WHILE ;
/*بستن مکان نما*/
بستن نشانگر بانک؛
پایان ؛

* این کد منبع با برجسته کننده کد منبع برجسته شده است.

خطا: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

پیام: داده ای وجود ندارد - ردیف صفر واکشی، انتخاب یا پردازش شده است

SQLSTATE: 02000 با رسیدن به انتهای مکان نما، یا زمانی که select یا update یک رشته خالی را برمی گرداند، فعال می شود.

خط بعدی ما مکان نما را اعلام کردیم CURSOR FOR select_statement.
مکان نما را باز کنید cursor_name را باز کنید.
سپس تا به انتهای مکان نما (WHILE done = 0 DO) برسیم، داده ها را استخراج کرده و پردازش می کنیم.
قبل از خروج از رویه ذخیره شده باید مکان نما را ببندید. بستن cursor_name.

پیچیده به نظر نمی رسد. اما مشکلات زیادی در ارتباط با SQLSTATE "02000" وجود دارد.

WHILE انجام شد = 0 DO
واکشی نشانگر بانک در vBankId، vBankName، vAddress، vPhone؛

(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution که در آن BankId = vBankId حد 1 انتخاب کنید.
اقداماتی انجام می دهیم
END WHILE ;

* این کد منبع با برجسته کننده کد منبع برجسته شده است.

از نظر نحوی همه چیز خوب و درست است. اما از منظر منطقی خیر. ممکن است اتفاق بیفتد که سپرده گذاران در برخی از بانک ها حساب باز نکرده باشند، سپس برای Select (ContributeAmount) INTO vContributeAmountSUM FROM توزیع بانک که در آن BankId = vBankId حد 1 باشد. SQLSTATE: 02000 فعال می شود، متغیر done روی 1 تنظیم می شود و حلقه while زودتر از آنچه انتظار داشتیم به پایان می رسد. با انجام موارد زیر می توان از این امر جلوگیری کرد

WHILE انجام شد = 0 DO
واکشی نشانگر بانک در vBankId، vBankName، vAddress، vPhone؛
/* استخراج مبلغ هر یک از سپرده های بانک برای بانک */


اگر (vContributeAmountSUM > 0) سپس
/* استخراج مبلغ هر یک از سپرده های بانک برای بانک */

پایان اگر ;
اقداماتی انجام می دهیم
END WHILE ;

* این کد منبع با برجسته کننده کد منبع برجسته شده است.

با اولین درخواست، بررسی کردیم که آیا مشارکتی وجود دارد (اگر هیچ مشارکتی وجود ندارد، vContributeAmountSUM == 0) و فقط در صورت وجود، داده ها را بازیابی می کنیم.

حال فرض کنید باید کل مبلغ موجود در حساب‌های بانک‌های مختلف را برای هر مشتری حذف کنیم
مکان نما ClientSummCursor را برای Select sum اعلام کنید

اعلام مکان نما ClientSummCursor برای Select sum ('bankdistribution'.'ContributeAmount')، 'bankdistribution'.'ClientId' FROM 'bankdistribution' Inner Join Client on (client.ClientId = bankdistribution.`ClientId`) که در آن 1 گروه توسط ``kdistribution. "ClientId"؛

ClientSummCursor را باز کنید.
WHILE انجام شد = 0 DO
واکشی نشانگر بانک در vBankId، vBankName، vAddress، vPhone؛
/* استخراج مبلغ هر یک از سپرده های بانک برای بانک */
تعداد (ContributeAmount) INTO vContributeAmountSUM از توزیع بانک را انتخاب کنید که در آن BankId = vBankId حد 1 است.
/* بررسی کنید که آیا واقعاً در این بانک سپرده وجود دارد */
اگر (vContributeAmountSUM > 0) سپس
/* استخراج مبلغ هر یک از سپرده های بانک برای بانک */
ContributeAmount INTO vContributeAmountSUM FROM bankdistribution که در آن BankId = vBankId حد 1 را انتخاب کنید.
پایان اگر ;


اقداماتی انجام می دهیم
END WHILE ;

* این کد منبع با برجسته کننده کد منبع برجسته شده است.

همین وضعیت ممکن است زمانی رخ دهد که داده‌ها در مکان‌نما ClientSummCursor زودتر از داده‌های موجود در BankCursor به پایان برسد، SQLSTATE: 02000 فعال می‌شود، متغیر done روی 1 تنظیم می‌شود و حلقه while زودتر از آنچه انتظار داشتیم به پایان می‌رسد. با انجام موارد زیر می توان از این امر جلوگیری کرد

ClientSummCursor را باز کنید.
WHILE انجام شد = 0 DO
واکشی نشانگر بانک در vBankId، vBankName، vAddress، vPhone؛
/* استخراج مبلغ هر یک از سپرده های بانک برای بانک */
تعداد (ContributeAmount) INTO vContributeAmountSUM از توزیع بانک را انتخاب کنید که در آن BankId = vBankId حد 1 است.
/* بررسی کنید که آیا واقعاً در این بانک سپرده وجود دارد */
اگر (vContributeAmountSUM > 0) سپس
/* استخراج مبلغ هر یک از سپرده های بانک برای بانک */
ContributeAmount INTO vContributeAmountSUM FROM bankdistribution که در آن BankId = vBankId حد 1 را انتخاب کنید.
پایان اگر ;
/* قبل از استخراج داده ها از مکان نما دوم، حالت sqlstate */ را به خاطر بسپارید.
SET old_status = انجام شد.
/* داده های مورد نیاز را استخراج کنیم */
واکشی ClientSummCursor در vSum,vClientId.
/* بررسی کنید که آیا داده ها بازیابی شده اند و آیا sqlstate 0200 شکست خورده است */
اگر (انجام شد = 0) سپس
اقداماتی انجام می دهیم
پایان اگر ;
/* قبل از پایان while، مقدار متغیر done را بازیابی کنید */
مجموعه انجام شد = old_status;
END WHILE ;

* این کد منبع با برجسته کننده کد منبع برجسته شده است.

از همه کسانی که تا اینجا خوانده اند متشکرم، امیدوارم برای کسی مفید باشد.

پیاده سازی مکان نما در یک پایگاه داده شبیه کلاس جاوا است که دارای مجموعه ای از داده ها و روش های پردازش آن است. که در آن مکان نما sqlاز داده ها به عنوان یک آرایه معمولی استفاده می کند. نشانگرها را می توان در تریگرها، رویه های ذخیره شده و توابع استفاده کرد.

مطابق با استاندارد SQL، هنگام کار با مکان نما، اقدامات اساسی زیر انجام می شود:

• اعلام مکان نما؛
• باز کردن مکان نما با خواندن داده ها؛
• نمونه برداری خط به خط داده ها از مکان نما؛
• تغییر داده های ردیف با استفاده از مکان نما؛
• بستن مکان نما، پس از آن غیر قابل دسترس می شود.
• رها کردن مکان نما، یعنی حذف مکان نما از حافظه زیرا بستن آن لزوماً حافظه مرتبط با آن را آزاد نمی کند.

در پیاده سازی های مختلف تعریف مکان نماممکن است تفاوت هایی داشته باشد به عنوان مثال، گاهی اوقات لازم است که حافظه اختصاص داده شده برای یک مکان نما به صراحت آزاد شود. پس از آزاد شدن مکان نما، حافظه مرتبط با آن نیز آزاد می شود. این امکان استفاده مجدد از نام مکان نما را فراهم می کند. در اجراهای دیگر، وقتی مکان نما بسته می شود، حافظه به طور ضمنی آزاد می شود.

در برخی موارد، بدون استفاده از مکان نما نمی توانید انجام دهید. با این حال، در صورت امکان، باید از استفاده از مکان نما اجتناب کنید و با دستورات استاندارد پردازش داده کار کنید: SELECT، UPDATE، INSERT، DELETE. این به دلیل این واقعیت است که مکان نماها اجازه انجام عملیات تغییر در کل حجم داده را نمی دهند و سرعت انجام عملیات پردازش داده با استفاده از مکان نما به میزان قابل توجهی کمتر از سرعت است. به معنی استاندارد SQL.

اگر برنامه ای بتواند داده های بارگذاری شده در مکان نما را تغییر دهد، آن را قابل تغییر می نامند. وقتی در مورد مکان نما صحبت می کنیم، نباید جداسازی تراکنش ها را فراموش کنیم. یک کاربر یک رکورد را با استفاده از مکان نما تغییر می دهد، در حالی که کاربر دیگری آن رکورد را با استفاده از مکان نما خود می خواند. علاوه بر این، او می تواند همان رکورد را تغییر دهد، که حفظ یکپارچگی داده ها را ضروری می کند.

اعلام مکان نما

نشانگرها باید قبل از استفاده مشخص شوند. استاندارد SQL از دستور زیر برای ایجاد مکان نما استفاده می کند:

نشانگر cursor_name برای select_statement ])]

این عبارت یک مکان نما را اعلام می کند مکان نما را اعلام کنیدبا نام "Cursor_name".

غیر حساسیک مکان نما استاتیک ایجاد می شود که اجازه ایجاد تغییرات را نمی دهد. علاوه بر این، تغییرات ایجاد شده توسط سایر کاربران نمایش داده نمی شود. اگر کلمه کلیدی INSENSITIVE وجود نداشته باشد، یک مکان نما پویا ایجاد می شود.

هنگام استفاده از کلمه کلیدی طومارمکان نما ایجاد شده را می توان در هر جهت پیمایش کرد و به شما امکان می دهد هر دستور انتخابی را اعمال کنید. اگر این آرگومان حذف شود، مکان نما متوالی خواهد بود، یعنی. مشاهده آن تنها در یک جهت امکان پذیر خواهد بود - از ابتدا تا انتها.

اصطلاح select_statementساختاری برای خواندن اطلاعات مانند انتخاب ... از ... را نشان می دهد. نباید شامل اپراتور باشد به، از آنجایی که مکان نما عملگر خود را دارد رفتن و آوردنبرای پر کردن متغیرها با داده های مکان نما.

هنگام مشخص کردن یک آرگومان فقط برای READ_ONLYیک مکان نما فقط خواندنی ایجاد می شود و هیچ تغییری در داده ها مجاز نخواهد بود. یک مکان نما پویا را می توان به عنوان یک مکان نما فقط خواندنی اعلام کرد و اجازه می دهد تغییرات ایجاد شده توسط کاربر دیگر نمایش داده شود.

ایجاد مکان نما با آرگومان برای به روز رسانیبه شما این امکان را می دهد که داده های مکان نما را در ستون های مشخص شده یا در صورت عدم وجود آرگومان تغییر دهید OF column_name، در تمام ستون ها

شما می توانید چندین مکان نما را در یک زیر برنامه اعلام کنید. اما هر مکان نما باید یک نام منحصر به فرد داشته باشد. برای باز کردن مکان نما باید از عملگر استفاده کنید باز کنکه نشانگر قبلاً اعلام شده را باز می کند:

مکان نما باز است

SQL دستور زیر را برای باز کردن مکان نما "مکان نما باز" تعریف می کند:

نام مکان نما را باز کنید.

واکشی داده ها از مکان نما، واکشی مکان نما

نحو برای خواندن داده ها از مکان نما به برخی از متغیرها به شرح زیر است:

واکشی cursor_name در var_name [, var_name] ...;

اپراتور رفتن و آوردنداده های مکان نما را در متغیرهایی که بعد از آن قرار دارند انتخاب می کند بهو مکان نما را به موقعیت بعدی منتقل می کند.

بسته شدن مکان نما

اپراتور بستنمکان نما را می بندد اگر اپراتور به صراحت مشخص نشده باشد، هنگامی که بلوک برنامه مربوطه بسته می شود، مکان نما به طور خودکار بسته می شود.

بستن cursor_name.

پس از بسته شدن، مکان نما غیر قابل دسترس می شود. هنگام بسته شدن، تمام قفل های نصب شده در حین کارکرد مکان نما آزاد می شوند. فقط نشانگرهای باز می توانند بسته شوند. مکان نما بسته اما آزاد نشده را می توان دوباره باز کرد. بستن مکان نما باز نشده مجاز نیست.

هر DBMS ویژگی های خاص خود را در استفاده از مکان نما دارد.

ویژگی های استفاده از مکان نما در اوراکل

چهار ویژگی مکان نما در PL/SQL وجود دارد % FOUND, ٪پیدا نشد, ٪باز استو ٪شمارش ردیف. ویژگی های مکان نما مانند عملگرهای %TYPE و %ROWTYPE در سمت راست نام مکان نما اعلام می شوند.

ویژگی % FOUND

ویژگی %NOTFOUND

ویژگی %NOTFOUND دقیقاً برعکس %FOUND است.

ویژگی %ISOPEN

ویژگی %ISOPEN فقط نشان می دهد که مکان نما باز است یا خیر.

ویژگی %ROWCOUNT

صفت ٪شمارش ردیفیک ویژگی عددی است که تعداد ردیف های خوانده شده توسط مکان نما را در یک نقطه خاص از زمان برمی گرداند.

نمونه ای از مکان نما SQL در DBMS اوراکل

اعلام v_id managers.id %TYPE. v_name managers.name%TYPE; v_comm managers.comm%TYPE; نشانگر crs برای انتخاب شناسه، نام، جمع (comm) به عنوان comm از مدیران که در آن داده‌های بین «01-11-2014» و «2014-11-30» بر اساس شناسه، نام گروه‌بندی می‌شوند. شروع باز کردن crs. حلقه خروج وقتی crs%NOTFOUND; واکشی crs به ​​v_id، v_name، v_comm. در مقادیر پاداش (id، نام، comm) (crs.id، crs.name، crs.comm) قرار دهید. حلقه پایانی؛ مرتکب شدن؛

بستن crs; پایان؛

ویژگی های استفاده از مکان نما در سرور SQL

نشانگرهای مورد استفاده در MSSQL می توانند متوالی یا قابل پیمایش باشند. Sequential به شما امکان می دهد داده ها را فقط در یک جهت انتخاب کنید - از ابتدا تا انتها. مکان نماهای قابل پیمایش حرکت در هر دو جهت را امکان پذیر می کنند و به شما امکان می دهند به یک ردیف دلخواه در مجموعه نتایج مکان نما بپرید.

در طراحی مکان نما استاتیک، اطلاعات به عنوان یک عکس فوری در یک نقطه از زمان ذخیره می شود. بنابراین تغییرات ایجاد شده در پایگاه داده توسط کاربر دیگری قابل مشاهده نیست. در حالی که مکان نما باز می شود، سرور روی تمام ردیف های موجود در مجموعه نتایج کامل خود قفل تنظیم می کند. یک مکان نما ثابت پس از ایجاد تغییر نمی کند و همیشه مجموعه داده ای را که در زمان باز شدن وجود داشته است را نمایش می دهد. اگر سایر کاربران داده های موجود در مکان نما را در جدول منبع تغییر دهند، این روی مکان نما ثابت تأثیری نخواهد داشت. ایجاد تغییرات در مکان نما ثابت غیرممکن است، بنابراین همیشه در حالت فقط خواندنی باز می شود.

یک مکان نما پویا به سربار شبکه و منابع نرم افزاری اضافی نیاز دارد. هنگام استفاده از مکان نماهای پویا، یک کپی کامل از داده ها ایجاد نمی شود، اما انتخاب از جداول منبع تنها زمانی انجام می شود که کاربر به داده های خاصی دسترسی داشته باشد. در حین واکشی، سرور سطرها را قفل می کند و هر تغییری که کاربر در مجموعه نتایج کامل مکان نما ایجاد کند در مکان نما قابل مشاهده خواهد بود. با این حال، هنگامی که مکان نما داده ها را واکشی کرد، تغییرات ایجاد شده توسط کاربر دیگر دیگر در مکان نما منعکس نمی شود.

مکان نما که توسط مجموعه ای از کلیدها کنترل می شود دارای ویژگی های بین ایستا و پویا است. سوابق در زمان نمونه برداری شناسایی می شوند و بنابراین تغییرات ردیابی می شوند. این نوع مکان نما هنگام پیاده سازی اسکرول به عقب مفید است. در این حالت، تا زمانی که اطلاعات به روز نشده و مکان نما انتخاب نشود، افزودن و حذف داده ها قابل مشاهده نیست نسخه جدیداگر تغییراتی در آنها ایجاد شده باشد، ثبت می کند.

نشانگرهای ایستا به بهترین وجه برای سیستم های پردازش اطلاعات استفاده می شوند. برای سیستم های گزارش دهی یا برای اهداف آماری و تحلیلی. مکان نما استاتیک در واکشی حجم زیادی از داده ها بهتر است. در سیستم های خرید الکترونیکی یا رزرو اشیاء (صندلی، بلیط)، لازم است به صورت پویا اطلاعات به روز شده را با ایجاد تغییرات درک کنید. در چنین مواردی از مکان نما پویا استفاده می شود. در این برنامه ها، مقدار داده های منتقل شده معمولاً کم است و در سطح رکورد فردی قابل دسترسی است.

مکان نماهای متوالی اجازه نمی دهند داده ها در جهت معکوس واکشی شوند، فقط از ابتدا تا انتهای مکان نما. یک مکان نما متوالی مجموعه ای از تمام ردیف های داده را ذخیره نمی کند. به محض اینکه انتخابی در مکان نما انجام می شود، آنها از پایگاه داده خوانده می شوند، که اجازه می دهد تا به صورت پویا تمام تغییرات ایجاد شده توسط کاربران در پایگاه داده با استفاده از دستورات INSERT، UPDATE، DELETE منعکس شود. مکان نما آخرین وضعیت داده را می خواند.

اعلامیه مکان نما

اعلام مکان نما cursor_name برای SELECT_statement ]]

هنگام استفاده از کلمه کلیدی محلییک مکان نما محلی ایجاد می شود که فقط در بلوک، ماشه، رویه ذخیره شده یا عملکرد تعریف شده توسط کاربر قابل مشاهده است. کلمه کلیدی جهانی، یک مکان نما جهانی را تعریف می کند که تا زمان بسته شدن اتصال فعلی وجود دارد.

اپراتور FORWARD_ONLYیک مکان نما متوالی را تعریف می کند که به داده ها اجازه می دهد فقط در جهت از ردیف اول تا آخر بازیابی شوند. هنگام استفاده از اپراتور طوماریک مکان نما قابل پیمایش ایجاد می شود که امکان دسترسی به داده ها را در هر ترتیب و در هر جهتی فراهم می کند.

نوع مکان نما توسط عملگرها تعیین می شود:

• STATIC - ایجاد یک مکان نما استاتیک.
• DYNAMIC - ایجاد یک مکان نما پویا.
• KEYSET - ایجاد مکان نما کلید.

اگر برای مکان نما فقط خواندنیآرگومان را مشخص کنید FAST_FORWARD، سپس مکان نما ایجاد شده برای بهینه سازی می شود دسترسی سریعبه داده ها این استدلال را نمی توان همراه با آرگومان ها استفاده کرد FORWARD_ONLYو خوشبین.

اگر مکان نما با عملگر ایجاد شود خوشبین، سپس تغییر یا حذف ردیف هایی که پس از باز شدن مکان نما تغییر کرده اند ممنوع است.

هنگام مشخص کردن یک آرگومان TYPE_WARNINGسرور یک تغییر ضمنی نوع مکان نما را گزارش می دهد اگر با کوئری SELECT ناسازگار باشد.

بازیابی داده ها از مکان نما، واکشی

بلافاصله پس از باز کردن مکان نما، می توانید محتویات آن را با استفاده از دستور زیر دریافت کنید:

هنگام استفاده از اپراتور اولیناولین سطر از مجموعه نتایج مکان نما برگردانده می شود که به ردیف فعلی تبدیل می شود. وقتی مشخص شد آخرآخرین خط مکان نما برگردانده خواهد شد. خط فعلی هم می شود.

هنگام تعیین یک اپراتور بعدسطر بلافاصله بعد از ردیف فعلی در مجموعه نتایج برگردانده می شود. این خط به خط فعلی تبدیل می شود. دستور پیش فرض رفتن و آوردندقیقاً از این روش برای واکشی ردیف ها استفاده می کند.

هنگام تعیین یک اپراتور قبلخط قبل از خط فعلی برگردانده خواهد شد. این خط به خط فعلی تبدیل می شود.

اپراتور ABSOLUTE (خط_شماره | @line_number_variable)یک ردیف را با عدد ترتیبی مطلق آن در مجموعه نتایج کامل مکان نما برمی گرداند. شماره خط را می توان با استفاده از یک ثابت یا به عنوان نام متغیری که شماره خط در آن ذخیره می شود مشخص کرد. متغیر باید یک نوع داده عدد صحیح باشد. هر دو مقدار مثبت و منفی نشان داده شده است. هنگام تعیین یک مقدار مثبت، رشته از ابتدای مجموعه شمارش می شود، در حالی که مقدار منفی از انتهای مجموعه شمارش می شود. خط انتخاب شده به خط فعلی تبدیل می شود. اگر مقدار null مشخص شود، هیچ ردیفی برگردانده نمی شود.

بحث و جدل نسبی (تعداد ردیف | @تغییر تعداد ردیف)تعداد خطوط مشخص شده را بعد از خط فعلی، خط را جبران می کند. اگر تعداد ردیف‌های منفی را مشخص کنید، ردیفی که تعداد ردیف‌های مشخص شده قبل از ردیف فعلی است برگردانده می‌شود. تعیین یک مقدار تهی، ردیف فعلی را برمی گرداند. ردیف برگشتی به ردیف فعلی تبدیل می شود.

برای باز کردن مکان نما جهانی، باید یک کلمه کلیدی را قبل از نام آن مشخص کنید جهانی. نام مکان نما را نیز می توان با استفاده از یک متغیر مشخص کرد.

در بیان INTO @variable_name [,...n]لیستی از متغیرها تعریف شده است که در آن مقادیر ستون مربوط به سطر برگشتی ذخیره می شود. ترتیب متغیرها باید با ترتیب ستون ها در مکان نما و نوع داده متغیر باید با نوع داده در ستون مکان نما مطابقت داشته باشد.

تغییر و حذف داده ها با استفاده از مکان نما

برای تغییر داده ها با استفاده از مکان نما، باید دستور UPDATE را با فرمت زیر صادر کنید:

در یک عملیات، مقادیر چندین ستون از ردیف مکان نما فعلی را می توان تغییر داد، اما همه آنها باید به یک جدول تعلق داشته باشند.

برای حذف داده ها با استفاده از مکان نما، از دستور DELETE در قالب زیر استفاده کنید:

در نتیجه، خط فعلی در مکان نما حذف خواهد شد.

آزاد کردن حافظه، توزیع

برای حذف مکان نما از حافظه، از دستور استفاده کنید

اختصاص نام مکان نما؛

ویژگی @@FETCH_STATUS

برای تعیین وجود ردیف ها در مکان نما، باید از یک متغیر سراسری استفاده کنید @@FETCH_STATUS، که اگر ردیف دیگری در مکان نما وجود نداشته باشد مقدار غیر صفر می گیرد. اگر مجموعه سطرها هنوز تمام نشده باشد، @@FETCH_STATUS برابر با صفر است.

نمونه ای از مکان نما در سرور SQL

اعلام @company varchar(50)، @manager varchar(50)، @message varchar(256); مکان نما crs_clients را محلی برای شرکت منتخب، مدیر مشتریانی که در آن شهر = "مسکو" سفارش توسط شرکت، مدیر اعلام کنید. چاپ "فهرست مشتریان"؛ crs_clients را باز کنید. واکشی بعدی از crs_clients به @company، @manager. در حالی که @@FETCH_STATUS = 0 شروع را انتخاب کنید @message = "Company" + @company + " manager " + @manager; چاپ @message; - به رکورد بعدی واکشی بعدی از crs_clients به @company، @manager بروید. پایان؛ بستن crs_clients; معامله crs_clients;

تعریف مکان نما داده شده است. شرح انواع و رفتار آن داده شده است: مکان نماهای ایستا، پویا، ترتیبی و کلیدی. اصول کنترل مکان نما شرح داده شده است: ایجاد و باز کردن مکان نما، خواندن داده ها، بستن مکان نما. نمونه هایی از برنامه نویسی مکان نما آورده شده است.

مفهوم مکان نما

یک پرس و جو در برابر یک پایگاه داده رابطه ای معمولاً چندین ردیف (سوابق) از داده ها را برمی گرداند، اما برنامه کاربردی فقط یک رکورد را در یک زمان پردازش می کند. حتی اگر همزمان با چندین ردیف سروکار داشته باشد (مثلاً داده ها را به شکل صفحات گسترده نمایش می دهد)، تعداد آنها همچنان محدود است. علاوه بر این، هنگام اصلاح، حذف یا اضافه کردن داده ها، واحد کاری مجموعه است. در این شرایط مفهوم مکان نما مطرح می شود و در این زمینه مکان نما نشانگر یک ردیف است.

مکان نما در SQL ناحیه ای در حافظه پایگاه داده است که برای نگهداری آخرین دستور SQL طراحی شده است. اگر عبارت فعلی یک کوئری پایگاه داده باشد، ردیفی از داده های پرس و جو به نام مقدار فعلی یا خط مکان نما فعلی نیز در حافظه ذخیره می شود. ناحیه مشخص شده در حافظه نامگذاری شده و برای برنامه های کاربردی قابل دسترسی است.

معمولاً از مکان نماها برای انتخاب زیرمجموعه ای از اطلاعات ذخیره شده در یک پایگاه داده استفاده می شود. در هر زمان معین، یک خط مکان نما را می توان توسط برنامه کاربردی بررسی کرد. نشانگرها اغلب در عبارات SQL، در برنامه های کاربردی نوشته شده به زبان های رویه ای تعبیه شده است. برخی از آنها به طور ضمنی توسط سرور پایگاه داده ایجاد می شوند، در حالی که برخی دیگر توسط برنامه نویسان تعریف می شوند.

مطابق با استاندارد SQL، هنگام کار با مکان نما، اقدامات اصلی زیر قابل تشخیص است:

• ایجاد یا اعلام مکان نما;
• باز کردن مکان نما، یعنی پر کردن آن با داده هایی که در حافظه چند سطحی ذخیره می شود.
• انتخاب از مکان نماو تغییر ردیف های داده با آن؛
• بستن مکان نما، پس از آن برای برنامه های کاربر غیر قابل دسترس می شود.
• آزاد کردن مکان نما، یعنی حذف مکان نما به عنوان یک شی، زیرا بستن آن لزوماً حافظه مرتبط با آن را آزاد نمی کند.

تعریف مکان نما ممکن است در بین اجراها کمی متفاوت باشد. به عنوان مثال، گاهی اوقات یک توسعه دهنده باید حافظه اختصاص داده شده برای مکان نما را به صراحت آزاد کند. بعد از مکان نما را رها کنیدحافظه مرتبط آن نیز آزاد می شود. این امکان استفاده مجدد از نام او را فراهم می کند. در اجراهای دیگر وقتی بستن مکان نماآزاد کردن حافظه به طور ضمنی رخ می دهد. بلافاصله پس از بهبودی، برای سایر عملیات ها در دسترس می شود: باز کردن مکان نما دیگرو غیره.

در برخی موارد استفاده از مکان نما اجتناب ناپذیر است. با این حال، در صورت امکان، باید از این کار اجتناب کرد و با دستورات استاندارد پردازش داده کار کرد: SELECT، UPDATE، INSERT، DELETE. علاوه بر این واقعیت که مکان نماها اجازه انجام عملیات تغییر در کل حجم داده را نمی دهند، سرعت انجام عملیات پردازش داده با استفاده از مکان نما به میزان قابل توجهی کمتر از ابزارهای استاندارد SQL است.

پیاده سازی مکان نما در محیط MS SQL Server

SQL Server از سه نوع مکان نما پشتیبانی می کند:

• مکان نماهای SQL عمدتاً در تریگرها، رویه های ذخیره شده و اسکریپت ها استفاده می شوند.
• مکان نماهای سرور روی سرور کار می کنند و رابط برنامه نویسی برنامه را برای ODBC، OLE DB، DB_Library پیاده سازی می کنند.
• نشانگرهای کلاینت بر روی خود کلاینت پیاده سازی می شوند. آنها کل مجموعه نتایج سطرها را از سرور واکشی می کنند و آن را به صورت محلی ذخیره می کنند که با کاهش زمان تلف شده در عملیات شبکه سرعت پردازش داده ها را افزایش می دهد.

انواع مختلف برنامه های کاربردی چند کاربره به انواع مختلفی از دسترسی موازی به داده ها نیاز دارند. برخی از برنامه ها نیاز به دسترسی فوری به اطلاعات مربوط به تغییرات پایگاه داده دارند. این برای سیستم های رزرو بلیط معمولی است. در موارد دیگر، مانند سیستم های گزارش آماری، پایداری داده ها مهم است زیرا اگر دائماً در حال تغییر باشد، برنامه ها نمی توانند اطلاعات را به طور موثر نمایش دهند. برنامه های مختلف به پیاده سازی های متفاوتی از مکان نماها نیاز دارند.

در SQL Server، انواع مکان نما در قابلیت هایی که ارائه می دهند متفاوت است. نوع مکان نما در مرحله ایجاد آن تعیین می شود و قابل تغییر نیست. برخی از انواع مکان نماها می توانند تغییرات ایجاد شده توسط سایر کاربران در ردیف های موجود در مجموعه نتایج را تشخیص دهند. با این حال، SQL Server فقط تغییرات چنین ردیف‌هایی را در زمانی که به سطر در حال دسترسی است ردیابی می‌کند و اجازه نمی‌دهد تغییرات پس از خوانده شدن سطر، اصلاح شوند.

نشانگرها به دو دسته تقسیم می شوند: متوالیو قابل پیمایش متوالیبه شما امکان می دهد داده ها را فقط در یک جهت انتخاب کنید - از ابتدا تا انتها. نشانگرهای قابل پیمایشآزادی عمل بیشتری را فراهم می کند - می توان در هر دو جهت حرکت کرد و به یک ردیف دلخواه از مجموعه نتایج مکان نما رفت، اگر برنامه بتواند داده هایی را که مکان نما به آنها اشاره می کند تغییر دهد، آن را قابل پیمایش و تغییر می نامند. در مورد مکان نماها، نباید جداسازی تراکنش را فراموش کنیم. هنگامی که یک کاربر یک رکورد را تغییر می دهد، دیگری آن را با استفاده از مکان نما خود می خواند و علاوه بر این، می تواند همان رکورد را تغییر دهد که حفظ یکپارچگی داده ها را ضروری می کند.

SQL Server از استاتیک، پویا، متوالیو توسط مجموعه ای از کلیدها کنترل می شود.

در طرح با مکان نما استاتیکاطلاعات یک بار از پایگاه داده خوانده می شود و به عنوان یک عکس فوری ذخیره می شود (در مقطعی از زمان)، بنابراین تغییرات ایجاد شده در پایگاه داده توسط کاربر دیگری قابل مشاهده نیست. در حالی که برای باز کردن مکان نماسرور روی تمام ردیف های موجود در مجموعه نتایج کامل خود قفل تنظیم می کند. مکان نما استاتیکپس از ایجاد تغییر نمی کند و همیشه مجموعه داده ای را که در زمان باز شدن آن وجود داشته را نشان می دهد.

اگر سایر کاربران داده های موجود در مکان نما را در جدول منبع تغییر دهند، این امر بر روی آن تأثیر نمی گذارد مکان نما استاتیک.

که در مکان نما استاتیکامکان ایجاد تغییرات وجود ندارد، بنابراین همیشه در حالت فقط خواندنی باز می شود.

مکان نما پویاداده ها را در حالت "زنده" نگهداری می کند، اما این به منابع شبکه و نرم افزار نیاز دارد. استفاده كردن نشانگرهای پویایک کپی کامل از داده های منبع ایجاد نمی شود، اما یک انتخاب پویا از جداول منبع تنها زمانی انجام می شود که کاربر به داده های خاصی دسترسی داشته باشد. در طول واکشی، سرور سطرها را قفل می کند و هر تغییری که کاربر در مجموعه نتایج کامل مکان نما ایجاد کند در مکان نما قابل مشاهده خواهد بود. با این حال، اگر کاربر دیگری پس از واکشی داده‌ها توسط مکان‌نما، تغییراتی را ایجاد کرده باشد، آن‌ها در مکان‌نما منعکس نمی‌شوند.

مکان نما توسط مجموعه ای از کلیدها کنترل می شود، در وسط بین این افراط قرار دارد. سوابق در زمان نمونه برداری شناسایی می شوند و بنابراین تغییرات ردیابی می شوند. این نوع مکان‌نما هنگام پیاده‌سازی اسکرول به عقب مفید است - سپس اضافه‌ها و حذف‌های ردیف‌ها تا زمانی که اطلاعات به‌روزرسانی نشود قابل مشاهده نیست و درایور نسخه جدیدی از رکورد را انتخاب می‌کند اگر تغییراتی در آن ایجاد شده باشد.

نشانگرهای متوالیمجاز به واکشی داده ها در جهت معکوس نیستند. کاربر فقط می تواند ردیف هایی را از ابتدا تا انتهای مکان نما انتخاب کند. مکان نما سریالمجموعه ای از تمام ردیف ها را ذخیره نمی کند. آنها به محض انتخاب در مکان نما از پایگاه داده خوانده می شوند، که اجازه می دهد تمام تغییرات ایجاد شده توسط کاربران در پایگاه داده با استفاده از دستورات INSERT، UPDATE، DELETE به صورت پویا منعکس شود. مکان نما آخرین وضعیت داده ها را نشان می دهد.

نشانگرهای ثابتیک نمای پایدار از داده ها ارائه دهد. آنها برای سیستم های "انبارداری" اطلاعات خوب هستند: برنامه های کاربردی برای سیستم های گزارش دهی یا برای اهداف آماری و تحلیلی. بعلاوه، مکان نما استاتیکبهتر از سایرین با نمونه برداری از حجم زیادی از داده ها مقابله می کند. در مقابل، سیستم‌های خرید الکترونیکی یا رزرو بلیط نیاز به درک پویا از اطلاعات به‌روز شده با ایجاد تغییرات دارند. در چنین مواردی استفاده می شود مکان نما پویا. در این برنامه ها، مقدار داده های منتقل شده معمولاً کم است و در سطح ردیف ( رکورد فردی) قابل دسترسی است. دسترسی گروهی بسیار نادر است.

مدیریت مکان نما در محیط MS SQL Server

کنترل مکان نمابا اجرای دستورات زیر پیاده سازی می شود:

• اعلام - ایجاد یا اعلام مکان نما;
• باز کن - باز کردن مکان نما، یعنی پر کردن آن با داده ها؛
• رفتن و آوردن انتخاب از مکان نماو تغییر ردیف های داده با استفاده از مکان نما.
• بستن - بستن مکان نما;
• اختصاص دادن - آزاد کردن مکان نما، یعنی حذف مکان نما به عنوان یک شی

اعلامیه مکان نما

استاندارد SQL دستور زیر را برای ایجاد مکان نما ارائه می دهد:

استفاده از کلمه کلیدی INSENSITIVE ایجاد خواهد کرد مکان نما استاتیک. داده ها تغییر می کندمجاز نیستند و تغییرات ایجاد شده توسط سایر کاربران نمایش داده نمی شود. اگر کلمه کلیدی INSENSITIVE وجود نداشته باشد، a مکان نما پویا.

هنگامی که کلمه کلیدی SCROLL را مشخص می کنید، مکان نما ایجاد شده را می توان در هر جهت پیمایش کرد و به شما امکان می دهد از هر دستور انتخابی استفاده کنید. اگر این آرگومان حذف شود، مکان نما خواهد بود استوار، یعنی مشاهده آن تنها در یک جهت امکان پذیر خواهد بود - از ابتدا تا انتها.

دستور SELECT بدنه درخواست SELECT را مشخص می کند که مجموعه ردیف های حاصل را برای مکان نما تعیین می کند.

تعیین FOR READ_ONLY یک مکان نما فقط خواندنی ایجاد می کند و اجازه هیچ گونه تغییری در داده ها را نمی دهد. با استاتیک متفاوت است، اگرچه دومی نیز اجازه تغییر داده را نمی دهد. می تواند به عنوان یک مکان نما فقط خواندنی اعلام شود مکان نما پویا، که اجازه می دهد تغییرات ایجاد شده توسط کاربر دیگر نمایش داده شود.

ایجاد مکان نما با آرگومان FOR UPDATE به شما امکان می دهد در مکان نما اجرا کنید تغییر داده هایا در ستون های مشخص شده یا در غیاب آرگومان OF column_name، در همه ستون ها.

در محیط MS SQL Server، دستور زیر برای دستور ایجاد مکان نما پذیرفته می شود:

<создание_курсора>::= اعلام مکان نما برای SELECT_statement ]]

استفاده از کلمه کلیدی LOCAL یک مکان نما محلی ایجاد می کند که فقط در محدوده بسته، راه اندازی، رویه ذخیره شده یا تابع تعریف شده توسط کاربر که آن را ایجاد کرده است قابل مشاهده است. هنگامی که یک بسته، ماشه، رویه یا تابع خاتمه می یابد، مکان نما به طور ضمنی از بین می رود. برای ارسال محتویات مکان نما به خارج از ساختاری که آن را ایجاد کرده است، باید یک آرگومان OUTPUT به پارامتر آن اختصاص دهید.

اگر کلمه کلیدی GLOBAL مشخص شده باشد، یک مکان نما سراسری ایجاد می شود. تا زمانی که اتصال فعلی بسته نشود وجود دارد.

مشخص کردن FORWARD_ONLY ایجاد می کند مکان نما سریال; داده ها را فقط می توان در جهت از ردیف اول تا آخر نمونه برداری کرد.

مشخص کردن SCROLL ایجاد می کند مکان نما قابل پیمایش; داده ها به هر ترتیب و در هر جهت قابل دسترسی هستند.

مشخص کردن STATIC ایجاد می کند مکان نما استاتیک.

تعیین KEYSET یک مکان نما کلید ایجاد می کند.

مشخص کردن DYNAMIC ایجاد می کند مکان نما پویا.

اگر آرگومان FAST_FORWARD را برای مکان نما READ_ONLY مشخص کنید، مکان نما ایجاد شده برای دسترسی سریع به داده ها بهینه می شود. این آرگومان را نمی توان همراه با آرگومان های FORWARD_ONLY یا OPTIMISTIC استفاده کرد.

مکان نما ایجاد شده با آرگومان OPTIMISTIC از اصلاح یا حذف ردیف هایی که پس از اصلاح شده اند جلوگیری می کند. باز کردن مکان نما.

با تعیین آرگومان TYPE_WARNING، سرور کاربر را از تغییر ضمنی نوع مکان نما در صورتی که با کوئری SELECT ناسازگار باشد، مطلع می کند.

باز کردن مکان نما

برای باز کردن مکان نماو با پر کردن آن با داده های مربوط به پرس و جوی SELECT مشخص شده در هنگام ایجاد مکان نما، از دستور زیر استفاده کنید:

بعد از باز کردن مکان نمادستور SELECT مربوطه اجرا می شود که خروجی آن در حافظه چند سطحی ذخیره می شود.

بازیابی داده ها از مکان نما

درست بعد باز کردن مکان نمامی توانید محتویات آن (نتیجه اجرای کوئری مربوطه) را با استفاده از دستور زیر انتخاب کنید:

مشخص کردن FIRST اولین سطر از مجموعه نتایج کامل مکان نما را برمی گرداند که به ردیف فعلی تبدیل می شود.

تعیین LAST آخرین ردیف مکان نما را برمی گرداند. خط فعلی هم می شود.

تعیین NEXT ردیف را بلافاصله بعد از ردیف فعلی در مجموعه نتایج کامل برمی‌گرداند. حالا جاری می شود. به طور پیش فرض، دستور FETCH از این روش برای واکشی ردیف ها استفاده می کند.

کلمه کلیدی PRIOR ردیف قبل از ردیف فعلی را برمی گرداند. جاری می شود.

بحث و جدل ABSOLUTE (خط_شماره | @line_number_variable)یک ردیف را با عدد ترتیبی مطلق آن در مجموعه نتایج کامل مکان نما برمی گرداند. شماره خط را می توان با استفاده از یک ثابت یا به عنوان نام متغیری که شماره خط در آن ذخیره می شود مشخص کرد. متغیر باید یک نوع داده عدد صحیح باشد. هر دو مقدار مثبت و منفی نشان داده شده است. هنگام تعیین یک مقدار مثبت، رشته از ابتدای مجموعه شمارش می شود، در حالی که مقدار منفی از انتهای مجموعه شمارش می شود. خط انتخاب شده به خط فعلی تبدیل می شود. اگر مقدار null مشخص شود، هیچ ردیفی برگردانده نمی شود.

بحث و جدل نسبی (تعداد ردیف | @تغییر تعداد ردیف)خطی را که تعداد خطوط مشخص شده بعد از خط فعلی است برمی گرداند. اگر تعداد ردیف‌های منفی را مشخص کنید، ردیفی که تعداد ردیف‌های مشخص شده قبل از ردیف فعلی است برگردانده می‌شود. تعیین یک مقدار تهی، ردیف فعلی را برمی گرداند. ردیف برگشتی به ردیف فعلی تبدیل می شود.

به باز کردن مکان نما جهانی، باید کلمه کلیدی GLOBAL را قبل از نام آن مشخص کنید. نام مکان نما را نیز می توان با استفاده از یک متغیر مشخص کرد.

در طراحی INTO @variable_name [,...n]لیستی از متغیرها مشخص می شود که در آن مقادیر ستون مربوط به سطر برگشتی ذخیره می شود. ترتیب تعیین متغیرها باید با ترتیب ستون ها در مکان نما مطابقت داشته باشد و نوع داده متغیر باید با نوع داده در ستون مکان نما مطابقت داشته باشد. اگر ساختار INTO مشخص نشده باشد، رفتار فرمان FETCH شبیه رفتار دستور SELECT خواهد بود - داده ها روی صفحه نمایش داده می شوند.

تغییر و حذف داده ها

برای ایجاد تغییرات با استفاده از مکان نما، باید دستور UPDATE را با فرمت زیر صادر کنید:

چندین ستون از ردیف مکان نما فعلی را می توان در یک عملیات تغییر داد، اما همه آنها باید متعلق به یک جدول باشند.

برای حذف داده ها با استفاده از مکان نما، از دستور DELETE در قالب زیر استفاده کنید:

در نتیجه، جریان تنظیم خط در مکان نما حذف خواهد شد.

بستن مکان نما

پس از بسته شدن، مکان نما برای کاربران برنامه غیر قابل دسترس می شود. در صورت بسته شدن، تمام قفل های نصب شده در طول عملیات آن برداشته می شود. بستن فقط برای نشانگرهای باز قابل اعمال است. بسته است اما نه مکان نما آزاد شدممکن است بازگشایی شود بستن مکان نما باز نشده مجاز نیست.

مکان نما را رها کنید

بستن مکان نمالزوماً حافظه مرتبط با آن را آزاد نمی کند. برخی از پیاده سازی ها باید به صراحت آن را با استفاده از عبارت DEALLOCATE منتشر کنند. بعد از مکان نما را رها کنیدحافظه نیز آزاد می شود و امکان استفاده مجدد از نام مکان نما را فراهم می کند.

برای کنترل اینکه آیا به انتهای مکان نما رسیده است، توصیه می شود از تابع استفاده کنید: @@FETCH_STATUS

تابع @@FETCH_STATUS برمی‌گرداند:

0 اگر واکشی موفقیت آمیز بود.

1 اگر واکشی به دلیل تلاش برای واکشی یک خط خارج از مکان نما ناموفق بود.

2 اگر واکشی به دلیل تلاش برای دسترسی به یک ردیف حذف شده یا اصلاح شده شکست خورد.

DECLARE @id_kl INT، @firm VARCHAR(50)، @fam VARCHAR(50)، @message VARCHAR(80)، @nam VARCHAR(50)، @d DATETIME، @p INT، @s INT SET @s=0 PRINT "فهرست خرید" CURSOR LOCAL FOR SELECT Client Code, Company, Last Name FROM Client WHERE City="Moscow" ORDER BY Company, Last Name OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @STALE, @FAM@FAM =0 BEGIN SELECT @message="Client "+@fam+ "Company"+ @firm PRINT @message SELECT @message="نام محصول تاریخ خرید هزینه" PRINT @message اعلام tovar_cursor نشانگر برای انتخاب محصول.نام، معامله.تاریخ، محصول .قیمت* معامله. مقدار به عنوان هزینه محصول. کد محصول=Transaction.کد محصول WHERE معامله.کد مشتری=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "بدون خرید" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Total cost "+ CAST(@s AS CHAR(6)) PRINT @message -- انتقال به مشتری بعدی-- واکشی بعدی از klient_cursor به @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor مثال 13.6. مکان نما برای نمایش لیستی از کالاهای خریداری شده توسط مشتریان از مسکو و هزینه کل آنها.

مثال 13.7.یک مکان نما قابل پیمایش برای مشتریان از مسکو ایجاد کنید. اگر شماره تلفن با 1 شروع می شود، مشتری را با آن شماره حذف کنید و در اولین ورودی مکان نما، رقم اول شماره تلفن را با 4 جایگزین کنید.

اعلام @firm VARCHAR(50)، @fam VARCHAR(50)، @tel VARCHAR(8)، @message VARCHAR(80) PRINT "List of Client" اعلام klient_cursor کلید اسکرول جهانی مکان نما برای انتخاب شرکت، نام خانوادگی، تلفن از طرف مشتری WHERE City ="Moscow" سفارش توسط شرکت، نام خانوادگی برای به روز رسانی OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ " Company "+ @firm " Phone " + @tel PRINT @message -- اگر شماره تلفن با 1 شروع می شود، -- مشتری را با آن شماره حذف کنید IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- به بعدی بروید client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- در اولین ورودی، اولین رقم شماره تلفن را با 4 به روز رسانی تلفن مجموعه مشتری جایگزین کنید ='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Phone"+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor مثال 13.7. مکان نما قابل پیمایش برای مشتریان از مسکو.

مثال 13.8.استفاده مکان نما به عنوان پارامتر خروجی رویه. این روش یک مجموعه داده - لیستی از محصولات را برمی گرداند.

فراخوانی رویه و چاپ داده ها از مکان نما خروجی به صورت زیر انجام می شود:

اعلام @my_cur مکان‌نما اعلام @n VARCHAR(20) EXEC my_proc @cur=@my_cur واکشی خروجی بعدی از @my_cur به @n انتخاب @n در حالی که (@FETCH_STATUS=0) شروع واکشی بعدی در @_n پایان بستن @my_cur DEALLOCATE @my_cur