\u0130li\u015fkisel bir veritaban\u0131yla \u00e7al\u0131\u015f\u0131rken, genellikle uygulama kodunuzdan do\u011frudan SELECT, INSERT, UPDATE veya DELETE gibi verileri almak veya i\u015flemek i\u00e7in Yap\u0131land\u0131r\u0131lm\u0131\u015f Sorgu Dili (SQL) ifadeleri kullan\u0131rs\u0131n\u0131z. Bu ifadeler, do\u011frudan temel veritaban\u0131 tablolar\u0131 \u00fczerinde \u00e7al\u0131\u015f\u0131r ve bu tablolar\u0131 i\u015fler. Ayn\u0131 veritaban\u0131na eri\u015fen birden fazla uygulamada ayn\u0131 ifadeler veya ifade gruplar\u0131 kullan\u0131ld\u0131\u011f\u0131nda, genellikle farkl\u0131 uygulamalar aras\u0131nda tekrarlan\u0131rlar.<\/p>\n
MySQL, di\u011fer bir\u00e7ok ili\u015fkisel veritaban\u0131 y\u00f6netim sistemi gibi, sakl\u0131 yordamlar\u0131n kullan\u0131m\u0131n\u0131 destekler. Sakl\u0131 yordamlar, bir veya daha fazla SQL ifadesinin ortak bir ad alt\u0131nda yeniden kullan\u0131lmak \u00fczere gruplanmas\u0131na yard\u0131mc\u0131 olur ve ortak i\u015f mant\u0131\u011f\u0131n\u0131 veritaban\u0131n\u0131n kendisinde kaps\u00fcller. B\u00f6yle bir yordam, verileri tutarl\u0131 bir \u015fekilde almak veya i\u015flemek i\u00e7in veritaban\u0131na eri\u015fen bir uygulamadan \u00e7a\u011fr\u0131labilir.<\/p>\n
Sakl\u0131 yordamlar\u0131 kullanarak, birden fazla uygulamada kullan\u0131lacak ortak g\u00f6revler i\u00e7in yeniden kullan\u0131labilir rutinler olu\u015fturabilir, verileri do\u011frulayabilir veya veritaban\u0131 kullan\u0131c\u0131lar\u0131n\u0131n temel tablolara do\u011frudan eri\u015fmesini ve keyfi sorgular g\u00f6ndermesini k\u0131s\u0131tlayarak ek bir veri eri\u015fim g\u00fcvenli\u011fi katman\u0131 sa\u011flayabilirsiniz.<\/p>\n
Bu e\u011fitimde sakl\u0131 yordamlar\u0131n ne oldu\u011funu ve veri d\u00f6nd\u00fcren, giri\u015f ve \u00e7\u0131k\u0131\u015f parametrelerini kullanan temel sakl\u0131 yordamlar\u0131n nas\u0131l olu\u015fturulaca\u011f\u0131n\u0131 \u00f6\u011freneceksiniz.<\/p>\n
Bu k\u0131lavuzu takip etmek i\u00e7in, SQL tabanl\u0131 bir ili\u015fkisel veritaban\u0131 y\u00f6netim sistemi (RDBMS) \u00e7al\u0131\u015ft\u0131ran bir bilgisayara ihtiyac\u0131n\u0131z var. Bu k\u0131lavuzdaki talimatlar ve \u00f6rnekler, a\u015fa\u011f\u0131daki ortam kullan\u0131larak do\u011frulanm\u0131\u015ft\u0131r:<\/p>\n
Not: Bir\u00e7ok RDBMS'nin kendi benzersiz SQL uygulamas\u0131n\u0131 kulland\u0131\u011f\u0131n\u0131 ve sakl\u0131 yordamlar\u0131n s\u00f6zdiziminin resmi SQL standard\u0131n\u0131n bir par\u00e7as\u0131 olmad\u0131\u011f\u0131n\u0131 l\u00fctfen unutmay\u0131n. Bu e\u011fitimde belirtilen komutlar di\u011fer RDBMS'lerde \u00e7al\u0131\u015fabilse de, sakl\u0131 yordamlar veritaban\u0131na \u00f6zg\u00fcd\u00fcr ve bu nedenle MySQL d\u0131\u015f\u0131nda bir sistemde test ederseniz kesin s\u00f6zdizimi veya \u00e7\u0131kt\u0131 farkl\u0131 olabilir.<\/span><\/p>\n Ayr\u0131ca, sakl\u0131 yordamlar\u0131 kullanarak tablolar olu\u015fturabilece\u011finiz bo\u015f bir veritaban\u0131na da ihtiyac\u0131n\u0131z olacak. Bu k\u0131lavuzdaki \u00f6rneklerde kullan\u0131lan MySQL sunucusuna ba\u011flanma ve test veritaban\u0131n\u0131 olu\u015fturma hakk\u0131nda ayr\u0131nt\u0131l\u0131 bilgi i\u00e7in a\u015fa\u011f\u0131daki MySQL'e Ba\u011flanma ve \u00d6rnek Veritaban\u0131 Olu\u015fturma b\u00f6l\u00fcm\u00fcn\u00fc takip etmenizi \u00f6neririz.<\/p>\n Bu b\u00f6l\u00fcmde MySQL sunucusuna ba\u011flanacak ve bu k\u0131lavuzdaki \u00f6rnekleri kullanabilmeniz i\u00e7in \u00f6rnek bir veritaban\u0131 olu\u015fturacaks\u0131n\u0131z.<\/p>\n Bu rehberde kurgusal bir araba koleksiyonu kullanacaks\u0131n\u0131z. \u015eu anda sahip oldu\u011funuz arabalar\u0131n marka, model, \u00fcretim y\u0131l\u0131 ve de\u011feri gibi bilgilerini saklayacaks\u0131n\u0131z.<\/p>\n SQL veritaban\u0131 sisteminiz uzak bir sunucuda \u00e7al\u0131\u015f\u0131yorsa, yerel makinenizden sunucunuza SSH ile ba\u011flan\u0131n:<\/p>\n Daha sonra MySQL sunucusu istemini a\u00e7\u0131n ve Sami'yi MySQL kullan\u0131c\u0131 ad\u0131n\u0131zla de\u011fi\u015ftirin:<\/p>\n Procedures ad\u0131nda bir veritaban\u0131 olu\u015fturun:<\/p>\n Veritaban\u0131 ba\u015far\u0131yla olu\u015fturulduysa a\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 alacaks\u0131n\u0131z:<\/p>\n Prosed\u00fcr veritaban\u0131n\u0131 se\u00e7mek i\u00e7in a\u015fa\u011f\u0131daki USE ifadesini \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 alacaks\u0131n\u0131z:<\/p>\n Bir veritaban\u0131 se\u00e7tikten sonra, i\u00e7inde \u00f6rnek tablolar olu\u015fturabilirsiniz. "Makineler" tablosu, veritaban\u0131ndaki makineler hakk\u0131nda basitle\u015ftirilmi\u015f veriler i\u00e7erecektir. A\u015fa\u011f\u0131daki s\u00fctunlar\u0131 i\u00e7erir:<\/p>\n A\u015fa\u011f\u0131daki komutla \u00f6rnek tabloyu olu\u015fturun:<\/p>\n A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 al\u0131rsan\u0131z tablo olu\u015fmu\u015f demektir:<\/p>\n Daha sonra a\u015fa\u011f\u0131daki INSERT INTO i\u015flemini y\u00fcr\u00fcterek Cars tablosunu \u00f6rnek verilerle y\u00fckleyin:<\/p>\n INSERT INTO i\u015flemi, tabloya be\u015f Porsche ve be\u015f Ferrari modeli olmak \u00fczere on \u00f6rnek spor otomobil ekler. A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131, be\u015f sat\u0131r\u0131n da eklendi\u011fini g\u00f6stermektedir:<\/p>\n Art\u0131k k\u0131lavuzun geri kalan\u0131n\u0131 takip etmeye ve SQL'de sakl\u0131 yordamlar\u0131 kullanmaya ba\u015flamaya haz\u0131rs\u0131n\u0131z.<\/p>\n MySQL ve di\u011fer bir\u00e7ok ili\u015fkisel veritaban\u0131 sistemindeki sakl\u0131 yordamlar, veritaban\u0131 taraf\u0131ndan \u00e7a\u011fr\u0131ld\u0131\u011f\u0131nda s\u0131rayla y\u00fcr\u00fct\u00fclen bir veya daha fazla talimat i\u00e7eren nesneler olarak adland\u0131r\u0131l\u0131r. En temel \u00f6rnekte, bir sakl\u0131 yordam, s\u0131k kullan\u0131lan filtreler i\u00e7eren bir veritaban\u0131ndan veri almak gibi, yeniden kullan\u0131labilir bir rutin alt\u0131nda ortak bir ifadeyi depolayabilir. \u00d6rne\u011fin, bir \u00e7evrimi\u00e7i ma\u011fazan\u0131n son birka\u00e7 ay i\u00e7inde sipari\u015f veren m\u00fc\u015fterilerini almak i\u00e7in bir sakl\u0131 yordam olu\u015fturabilirsiniz. En karma\u015f\u0131k senaryolarda, sakl\u0131 yordamlar, g\u00fc\u00e7l\u00fc uygulamalar i\u00e7in karma\u015f\u0131k i\u015f mant\u0131\u011f\u0131n\u0131 tan\u0131mlayan kapsaml\u0131 programlar\u0131 temsil edebilir.<\/p>\n Sakl\u0131 yordamdaki talimat k\u00fcmesi, verileri d\u00f6nd\u00fcren veya i\u015fleyen SELECT veya INSERT sorgular\u0131 gibi yayg\u0131n SQL ifadelerini i\u00e7erebilir. Ayr\u0131ca, sakl\u0131 yordamlar \u015funlar\u0131 kullanabilir:<\/p>\n Not: MySQL taraf\u0131ndan desteklenen kapsaml\u0131 s\u00f6zdizimi, g\u00fc\u00e7l\u00fc programlar yazmay\u0131 ve sakl\u0131 yordamlarla karma\u015f\u0131k sorunlar\u0131 \u00e7\u00f6zmeyi m\u00fcmk\u00fcn k\u0131lar. Bu k\u0131lavuz, sakl\u0131 yordam g\u00f6vdesinde, giri\u015f ve \u00e7\u0131k\u0131\u015f parametrelerinde SQL ifadeleri bulunan sakl\u0131 yordamlar\u0131n yaln\u0131zca temel kullan\u0131m\u0131n\u0131 ele almaktad\u0131r. Ko\u015fullu kod y\u00fcr\u00fctme, de\u011fi\u015fken kullan\u0131m\u0131, d\u00f6ng\u00fcler ve \u00f6zel hata y\u00f6netimi bu k\u0131lavuzun kapsam\u0131 d\u0131\u015f\u0131ndad\u0131r. Resmi MySQL belgelerinde sakl\u0131 yordamlar hakk\u0131nda daha fazla bilgi edinmenizi \u00f6neririz.<\/span><\/p>\n <\/p>\n Prosed\u00fcr ismiyle \u00e7a\u011fr\u0131ld\u0131\u011f\u0131nda, veritaban\u0131 motoru onu tan\u0131mland\u0131\u011f\u0131 \u015fekilde, talimat talimat y\u00fcr\u00fct\u00fcr.<\/p>\n Veritaban\u0131 kullan\u0131c\u0131s\u0131, belirtilen prosed\u00fcr\u00fc y\u00fcr\u00fctmek i\u00e7in uygun izinlere sahip olmal\u0131d\u0131r. Bu izin gereksinimi, veritaban\u0131na do\u011frudan eri\u015fimi engellerken, kullan\u0131c\u0131lar\u0131n g\u00fcvenli bir \u015fekilde y\u00fcr\u00fct\u00fclebilecek bireysel prosed\u00fcrlere eri\u015fmesine izin veren bir g\u00fcvenlik katman\u0131 sa\u011flar.<\/p>\n Sakl\u0131 yordamlar do\u011frudan veritaban\u0131 sunucusunda \u00e7al\u0131\u015f\u0131r, t\u00fcm hesaplamalar\u0131 yerel olarak ger\u00e7ekle\u015ftirir ve sonu\u00e7lar\u0131 yaln\u0131zca tamamland\u0131ktan sonra \u00e7a\u011f\u0131ran kullan\u0131c\u0131ya d\u00f6nd\u00fcr\u00fcr.<\/p>\n Bir prosed\u00fcr\u00fcn davran\u0131\u015f\u0131n\u0131 de\u011fi\u015ftirmek isterseniz, prosed\u00fcr\u00fc veritaban\u0131nda g\u00fcncelleyebilirsiniz ve onu kullanan uygulamalar yeni s\u00fcr\u00fcm\u00fc otomatik olarak al\u0131r. T\u00fcm kullan\u0131c\u0131lar, uygulamalar\u0131nda herhangi bir de\u011fi\u015fiklik yapmak zorunda kalmadan yeni prosed\u00fcr kodunu hemen kullanmaya ba\u015flar.<\/p>\n Sakl\u0131 yordam olu\u015fturmak i\u00e7in kullan\u0131lan SQL kodunun genel yap\u0131s\u0131 \u015f\u00f6yledir:<\/p>\n Bu kod par\u00e7ac\u0131\u011f\u0131ndaki ilk ve son talimatlar DELIMITER \/\/ ve DELIMITER ;'dir. MySQL, ifadeleri ay\u0131rmak ve ne zaman ba\u015flay\u0131p ne zaman bittiklerini belirtmek i\u00e7in genellikle noktal\u0131 virg\u00fcl (;) karakterini kullan\u0131r. MySQL konsolunda noktal\u0131 virg\u00fclle ayr\u0131lm\u0131\u015f birden fazla komut \u00e7al\u0131\u015ft\u0131r\u0131rsan\u0131z, bunlar ayr\u0131 komutlar olarak ele al\u0131n\u0131r ve birbiri ard\u0131na ba\u011f\u0131ms\u0131z olarak \u00e7al\u0131\u015f\u0131r. Ancak, bir sakl\u0131 yordam, \u00e7a\u011fr\u0131ld\u0131\u011f\u0131nda ard\u0131\u015f\u0131k olarak y\u00fcr\u00fct\u00fclen birden fazla komut i\u00e7erebilir. Bu durum, MySQL'e yeni bir prosed\u00fcr olu\u015fturmas\u0131n\u0131 s\u00f6ylemeye \u00e7al\u0131\u015ft\u0131\u011f\u0131n\u0131zda bir soruna neden olur. Veritaban\u0131 motoru, sakl\u0131 yordam\u0131n g\u00f6vdesinde noktal\u0131 virg\u00fcl karakteriyle kar\u015f\u0131la\u015f\u0131r ve komutu y\u00fcr\u00fctmeyi durdurmas\u0131 gerekti\u011fini d\u00fc\u015f\u00fcn\u00fcr. Bu durumda, s\u00f6z konusu komut, prosed\u00fcr\u00fcn kendisindeki tek bir talimat de\u011fil, t\u00fcm prosed\u00fcr olu\u015fturma kodudur ve MySQL amac\u0131n\u0131z\u0131 yanl\u0131\u015f yorumlar.<\/p>\n Bu s\u0131n\u0131rlamay\u0131 a\u015fmak i\u00e7in, CREATE PROCEDURE \u00e7a\u011fr\u0131s\u0131 s\u00fcresince ay\u0131r\u0131c\u0131y\u0131 ge\u00e7ici olarak ; yerine \/\/ olarak de\u011fi\u015ftirmek i\u00e7in DELIMITER ifadesini kullan\u0131n. Ard\u0131ndan, sakl\u0131 yordam g\u00f6vdesindeki t\u00fcm noktal\u0131 virg\u00fcller sunucuya oldu\u011fu gibi g\u00f6nderilir. T\u00fcm yordam tamamland\u0131ktan sonra, ay\u0131r\u0131c\u0131 ; olarak de\u011fi\u015ftirilir ve son DELIMITER ; olur.<\/p>\n Yeni bir prosed\u00fcr olu\u015fturma kodunun kalbi, CREATE PROCEDURE \u00e7a\u011fr\u0131s\u0131d\u0131r ve ard\u0131ndan prosed\u00fcr ad\u0131 gelir: \u00f6rnekte procedure_name. Prosed\u00fcr ad\u0131n\u0131n ard\u0131ndan, prosed\u00fcr\u00fcn kabul etti\u011fi iste\u011fe ba\u011fl\u0131 bir parametre listesi gelir. Son k\u0131s\u0131m ise BEGIN ve END ifadeleri aras\u0131nda yer alan prosed\u00fcr g\u00f6vdesidir. Prosed\u00fcr kodunun i\u00e7inde, SELECT sorgusu gibi bir SQL ifadesi veya daha karma\u015f\u0131k bir kod i\u00e7erebilen bir \u015fey bulunur.<\/p>\n END komutu, normal noktal\u0131 virg\u00fcl yerine ge\u00e7ici bir ay\u0131r\u0131c\u0131 olan \/\/ ile biter.<\/p>\n Sonraki b\u00f6l\u00fcmde, bir sorguyu kaps\u00fclleyen temel bir parametresiz sakl\u0131 yordam olu\u015fturacaks\u0131n\u0131z.<\/p>\n Bu b\u00f6l\u00fcmde, markaya g\u00f6re s\u0131ralanm\u0131\u015f arabalar\u0131n listesini ve de\u011ferlerini azalan d\u00fczende d\u00f6nd\u00fcren bir SQL SELECT ifadesi i\u00e7eren ilk sakl\u0131 yordam\u0131n\u0131z\u0131 olu\u015fturacaks\u0131n\u0131z.<\/p>\n Kullanmak istedi\u011finiz SELECT ifadesini \u00e7al\u0131\u015ft\u0131rarak ba\u015flay\u0131n:<\/p>\n Veritaban\u0131, Arabalar tablosundan, \u00f6nce markaya g\u00f6re, sonra da bir markan\u0131n i\u00e7indeki arabalar\u0131n de\u011ferlerini azalan s\u0131rada g\u00f6steren bir liste d\u00f6nd\u00fcr\u00fcr:<\/p>\n En de\u011ferli Ferrari listenin en \u00fcst\u00fcnde, en de\u011fersiz Porsche ise en altta yer al\u0131yor.<\/p>\n Diyelim ki bu sorgu birden fazla uygulamada veya birden fazla kullan\u0131c\u0131 taraf\u0131ndan tekrar tekrar kullan\u0131l\u0131yor ve herkesin sonu\u00e7lar\u0131 s\u0131ralamak i\u00e7in ayn\u0131 y\u00f6ntemi kulland\u0131\u011f\u0131ndan emin olmak istiyorsunuz. Bunu yapmak i\u00e7in, bu ifadeyi yeniden kullan\u0131labilir bir adland\u0131r\u0131lm\u0131\u015f prosed\u00fcr alt\u0131nda depolayan bir sakl\u0131 prosed\u00fcr olu\u015fturmak istiyorsunuz.<\/p>\n Bu sakl\u0131 yordam\u0131 olu\u015fturmak i\u00e7in a\u015fa\u011f\u0131daki kod par\u00e7ac\u0131\u011f\u0131n\u0131 \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n \u00d6nceki b\u00f6l\u00fcmde a\u00e7\u0131kland\u0131\u011f\u0131 gibi, ilk ve son ifadeler (DELIMITER \/\/ ve DELIMITER \ud83d\ude09), MySQL'e prosed\u00fcr olu\u015fturma s\u00fcresince ifade ay\u0131r\u0131c\u0131 olarak noktal\u0131 virg\u00fcl karakterini yok saymas\u0131n\u0131 s\u00f6yler.<\/p>\n CREATE PROCEDURE SQL ifadesinin ard\u0131ndan, i\u015flemin ne yapt\u0131\u011f\u0131n\u0131 en iyi \u015fekilde tan\u0131mlamak i\u00e7in tan\u0131mlayabilece\u011finiz get_all_cars i\u015flem ad\u0131 gelir. \u0130\u015flem ad\u0131ndan sonra, parametreler ekleyebilece\u011finiz bir \u00e7ift parantez () bulunur. Bu \u00f6rnekte, i\u015flem parametre kullanmad\u0131\u011f\u0131 i\u00e7in parantezler bo\u015ftur. Ard\u0131ndan, i\u015flem kod blo\u011funun ba\u015flang\u0131c\u0131n\u0131 ve sonunu tan\u0131mlayan BEGIN ve END ifadeleri aras\u0131na, daha \u00f6nce kullan\u0131lan SELECT ifadesi aynen yaz\u0131l\u0131r.<\/p>\n Veritaban\u0131 bir ba\u015far\u0131 mesaj\u0131yla yan\u0131t verecektir:<\/p>\n get_all_cars prosed\u00fcr\u00fc art\u0131k veritaban\u0131nda saklan\u0131yor ve \u00e7a\u011fr\u0131ld\u0131\u011f\u0131nda saklanan ifadeyi oldu\u011fu gibi y\u00fcr\u00fct\u00fcyor.<\/p>\n Sakl\u0131 bir prosed\u00fcr\u00fc \u00e7al\u0131\u015ft\u0131rmak i\u00e7in, CALL SQL komutunu ve ard\u0131ndan prosed\u00fcr ad\u0131n\u0131 kullanabilirsiniz. Yeni olu\u015fturulan prosed\u00fcr\u00fc \u015fu \u015fekilde \u00e7al\u0131\u015ft\u0131rmay\u0131 deneyin:<\/p>\n Prosed\u00fcr\u00fc kullanmak i\u00e7in ihtiyac\u0131n\u0131z olan tek \u015fey get_all_cars prosed\u00fcr ad\u0131d\u0131r. Art\u0131k daha \u00f6nce kulland\u0131\u011f\u0131n\u0131z SELECT ifadesinin hi\u00e7bir b\u00f6l\u00fcm\u00fcn\u00fc manuel olarak yazman\u0131za gerek yok. Veritaban\u0131, sonu\u00e7lar\u0131 t\u0131pk\u0131 SELECT ifadesinin \u00e7\u0131kt\u0131s\u0131 gibi y\u00fcr\u00fct\u00fcr:<\/p>\n Art\u0131k Cars tablosundaki t\u00fcm arabalar\u0131 belirli bir \u015fekilde s\u0131ralanm\u0131\u015f \u015fekilde d\u00f6nd\u00fcren, parametresiz bir sakl\u0131 yordam ba\u015far\u0131yla olu\u015fturdunuz. Bu y\u00f6ntemi birden fazla uygulamada kullanabilirsiniz.<\/p>\n Sonraki b\u00f6l\u00fcmde, kullan\u0131c\u0131 girdisine ba\u011fl\u0131 olarak prosed\u00fcr\u00fcn davran\u0131\u015f\u0131n\u0131 de\u011fi\u015ftirmek i\u00e7in parametreler kabul eden bir prosed\u00fcr olu\u015fturacaks\u0131n\u0131z.<\/p>\n Bu b\u00f6l\u00fcmde, sakl\u0131 yordam tan\u0131m\u0131na, yordam\u0131 \u00e7al\u0131\u015ft\u0131ran kullan\u0131c\u0131lar\u0131n yordama veri aktarmas\u0131na olanak tan\u0131yan giri\u015f parametreleri eklersiniz. \u00d6rne\u011fin, kullan\u0131c\u0131lar sorgu filtreleri sa\u011flayabilir.<\/p>\n \u00d6nceki sakl\u0131 yordam get_all_cars, cars tablosundaki t\u00fcm arabalar\u0131 her zaman al\u0131r. Belirli bir \u00fcretim y\u0131l\u0131na ait arabalar\u0131 bulmak i\u00e7in ba\u015fka bir yordam olu\u015ftural\u0131m. Bunu sa\u011flamak i\u00e7in, yordam tan\u0131m\u0131nda get_all_cars adl\u0131 bir parametre tan\u0131mlay\u0131n.<\/p>\n A\u015fa\u011f\u0131daki kodu \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n \u00d6nceki b\u00f6l\u00fcmden prosed\u00fcr olu\u015fturma kodunda birka\u00e7 de\u011fi\u015fiklik var.<\/p>\n \u0130lk olarak get_cars_by_year ismi, \u00fcretim y\u0131l\u0131na g\u00f6re arabalar\u0131 getiren y\u00f6ntemi anlat\u0131yor.<\/p>\n \u00d6nceki bo\u015f parantezler art\u0131k bir parametre tan\u0131m\u0131 i\u00e7eriyor: IN y\u0131l_filtresi int. IN anahtar s\u00f6zc\u00fc\u011f\u00fc, veritaban\u0131na parametrenin \u00e7a\u011f\u0131ran kullan\u0131c\u0131 taraf\u0131ndan prosed\u00fcre iletildi\u011fini bildirir. y\u0131l_filtresi, parametre i\u00e7in rastgele bir add\u0131r. Prosed\u00fcr kodunda parametreye at\u0131fta bulunmak i\u00e7in bunu kullanacaks\u0131n\u0131z. Son olarak, int veri t\u00fcr\u00fcd\u00fcr. Bu durumda, \u00fcretim y\u0131l\u0131 say\u0131sal bir de\u011fer olarak ifade edilir.<\/p>\n Prosed\u00fcr ad\u0131ndan sonra tan\u0131mlanan year_filter parametresi WHERE year = year_filter ifadesindeki SELECT ifadesinde g\u00f6r\u00fcn\u00fcr ve otomobil tablosunu \u00fcretim y\u0131llar\u0131na g\u00f6re filtreler.<\/p>\n Veritaban\u0131 bir kez daha ba\u015far\u0131 mesaj\u0131yla yan\u0131t verecektir:<\/p>\n Prosed\u00fcr\u00fc daha \u00f6nce yapt\u0131\u011f\u0131n\u0131z gibi herhangi bir parametre vermeden \u00e7al\u0131\u015ft\u0131rmay\u0131 deneyin:<\/p>\n MySQL veritaban\u0131 bir hata mesaj\u0131 d\u00f6nd\u00fcr\u00fcyor:<\/p>\n Bu sefer, sakl\u0131 yordam bir parametre sa\u011flanmas\u0131n\u0131 bekliyor, ancak hi\u00e7bir parametre sa\u011flanmad\u0131. Parametreli bir sakl\u0131 yordam\u0131 \u00e7a\u011f\u0131rmak i\u00e7in, parametre de\u011ferlerini parantez i\u00e7inde, yordam\u0131n bekledi\u011fi s\u0131rayla sa\u011flayabilirsiniz. 2017 y\u0131l\u0131nda \u00fcretilen arabalar\u0131 almak i\u00e7in a\u015fa\u011f\u0131dakileri \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n Art\u0131k \u00e7a\u011fr\u0131lan prosed\u00fcr do\u011fru \u015fekilde y\u00fcr\u00fct\u00fcl\u00fcr ve o y\u0131la ait arabalar\u0131n listesini d\u00f6nd\u00fcr\u00fcr:<\/p>\n Bu \u00f6rnekte, sakl\u0131 yordamlara giri\u015f parametrelerinin nas\u0131l ge\u00e7irilece\u011fini ve bunlar\u0131 bir yordam i\u00e7indeki sorgularda kullanarak filtreleme se\u00e7enekleri sa\u011flamay\u0131 \u00f6\u011frendiniz.<\/p>\n Bir sonraki b\u00f6l\u00fcmde, tek bir y\u00fcr\u00fctmede birden fazla farkl\u0131 de\u011fer d\u00f6nd\u00fcren prosed\u00fcrler olu\u015fturmak i\u00e7in \u00e7\u0131k\u0131\u015f parametrelerini kullanacaks\u0131n\u0131z.<\/p>\n \u00d6nceki iki \u00f6rnekte de, olu\u015fturdu\u011funuz sakl\u0131 yordamlar bir dizi sonu\u00e7 d\u00f6nd\u00fcrmek i\u00e7in SELECT ifadesini \u00e7a\u011f\u0131r\u0131yordu. Ancak baz\u0131 durumlarda, bir sorgu i\u00e7in tek bir sonu\u00e7 k\u00fcmesi yerine birka\u00e7 farkl\u0131 de\u011feri bir arada d\u00f6nd\u00fcren bir sakl\u0131 yordama ihtiya\u00e7 duyabilirsiniz.<\/p>\n Belirli bir y\u0131ldaki otomobiller hakk\u0131nda, koleksiyondaki otomobil say\u0131s\u0131 ve piyasa de\u011ferleri (minimum, maksimum ve ortalama) dahil olmak \u00fczere \u00f6zet bilgiler sa\u011flayan bir prosed\u00fcr olu\u015fturmak istedi\u011finizi varsayal\u0131m.<\/p>\n Bunu yapmak i\u00e7in, yeni bir sakl\u0131 yordam olu\u015ftururken OUT parametrelerini kullanabilirsiniz. IN parametrelerine benzer \u015fekilde, OUT parametrelerinin de kendilerine ait bir ad\u0131 ve veri t\u00fcr\u00fc vard\u0131r. Ancak, sakl\u0131 yordama veri aktarmak yerine, \u00e7a\u011f\u0131ran kullan\u0131c\u0131ya de\u011fer d\u00f6nd\u00fcrmek i\u00e7in sakl\u0131 yordamdaki verilerle doldurulabilirler.<\/p>\n \u00c7\u0131k\u0131\u015f parametrelerini kullanarak, belirli bir \u00fcretim y\u0131l\u0131na ait otomobiller i\u00e7in \u00f6zet verileri d\u00f6nd\u00fcren get_car_stats_by_year prosed\u00fcr\u00fcn\u00fc olu\u015fturun:<\/p>\n Bu sefer, arabalar\u0131 \u00fcretim y\u0131l\u0131na g\u00f6re filtrelemek i\u00e7in kullan\u0131lan IN parametresi year_filter ile birlikte, parantez blo\u011funda tan\u0131mlanm\u0131\u015f d\u00f6rt OUT parametresi bulunmaktad\u0131r. cars_number parametresi, int veri t\u00fcr\u00fcyle temsil edilir ve k\u00fcmedeki araba say\u0131s\u0131n\u0131 d\u00f6nd\u00fcrmek i\u00e7in kullan\u0131l\u0131r. min_value, avg_value ve max_value parametreleri piyasa de\u011ferini temsil eder ve ondal\u0131k t\u00fcr (10, 2) ile tan\u0131mlan\u0131r (cars tablosundaki de\u011fer s\u00fctununa benzer). Bunlar, k\u00fcmedeki en ucuz ve en pahal\u0131 arabalar ile e\u015fle\u015fen t\u00fcm arabalar\u0131n ortalama fiyat\u0131 hakk\u0131nda bilgi d\u00f6nd\u00fcrmek i\u00e7in kullan\u0131l\u0131r.<\/p>\n SELECT ifadesi, SQL matematiksel fonksiyonlar\u0131n\u0131 kullanarak arabalar tablosundan d\u00f6rt de\u011feri arar: Toplam araba say\u0131s\u0131n\u0131 elde etmek i\u00e7in COUNT ve de\u011fer s\u00fctunundan minimum, ortalama ve maksimum de\u011ferleri elde etmek i\u00e7in MIN, AVG ve MAX.<\/p>\n Veritaban\u0131na, bu sorgunun sonu\u00e7lar\u0131n\u0131n sakl\u0131 yordam\u0131n \u00e7\u0131kt\u0131 parametrelerinde saklanmas\u0131 gerekti\u011fini bildirmek i\u00e7in INTO adl\u0131 yeni bir anahtar s\u00f6zc\u00fck eklendi. INTO anahtar s\u00f6zc\u00fc\u011f\u00fcnden sonra, al\u0131nan verilerle ilgili d\u00f6rt yordam parametresinin adlar\u0131 listelenir. Bu, MySQL'in cars_number parametresinde COUNT(*) de\u011ferini, min_value parametresinde MIN(value) sonucunu vb. saklamas\u0131na neden olur.<\/p>\n Veritaban\u0131, prosed\u00fcr\u00fcn ba\u015far\u0131yla olu\u015fturuldu\u011funu do\u011fruluyor:<\/p>\n \u015eimdi a\u015fa\u011f\u0131dakileri \u00e7al\u0131\u015ft\u0131rarak yeni rutini \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n D\u00f6rt yeni parametre @ simgesiyle ba\u015flar. Bunlar, MySQL konsolunda verileri ge\u00e7ici olarak depolamak i\u00e7in kullanabilece\u011finiz yerel de\u011fi\u015fkenlerin adlar\u0131d\u0131r. Bunlar\u0131 olu\u015fturdu\u011funuz sakl\u0131 yordama ge\u00e7irdi\u011finizde, yordam bu de\u011fi\u015fkenlere de\u011ferler ekler.<\/p>\n Veritaban\u0131 a\u015fa\u011f\u0131daki \u015fekilde yan\u0131t verecektir:<\/p>\n Bu, sonu\u00e7lar\u0131n an\u0131nda ekranda g\u00f6r\u00fcnt\u00fclendi\u011fi \u00f6nceki davran\u0131\u015ftan farkl\u0131d\u0131r. Bunun nedeni, sakl\u0131 yordam\u0131n sonu\u00e7lar\u0131n\u0131n \u00e7\u0131kt\u0131 parametrelerinde depolanmas\u0131 ve sorgu sonu\u00e7lar\u0131 olarak d\u00f6nd\u00fcr\u00fclmemesidir. Sonu\u00e7lara eri\u015fmek i\u00e7in, bunlar\u0131 MySQL kabu\u011funda a\u015fa\u011f\u0131daki gibi do\u011frudan se\u00e7ebilirsiniz:<\/p>\n Bu sorgu ile, prosed\u00fcr\u00fc tekrar \u00e7a\u011f\u0131rmak yerine yerel de\u011fi\u015fkenlerden de\u011ferler se\u00e7ersiniz. Sakl\u0131 prosed\u00fcr, sonu\u00e7lar\u0131n\u0131 bu de\u011fi\u015fkenlerde depolar ve siz kabuktan \u00e7\u0131kana kadar veriler kullan\u0131labilir.<\/p>\n Not: MySQL'de kullan\u0131c\u0131 tan\u0131ml\u0131 de\u011fi\u015fkenlerin kullan\u0131m\u0131 hakk\u0131nda daha fazla bilgi i\u00e7in dok\u00fcmantasyonun Kullan\u0131c\u0131 Tan\u0131ml\u0131 De\u011fi\u015fkenler b\u00f6l\u00fcm\u00fcne bak\u0131n. Uygulama geli\u015ftirmede kullan\u0131ld\u0131\u011f\u0131nda, sakl\u0131 yordamlardan d\u00f6nd\u00fcr\u00fclen verilere eri\u015fim yollar\u0131 farkl\u0131 programlama dilleri ve \u00e7er\u00e7eveleri aras\u0131nda farkl\u0131l\u0131k g\u00f6sterecektir. \u015e\u00fcpheniz varsa, se\u00e7ti\u011finiz dil ve \u00e7er\u00e7evenin dok\u00fcmantasyonuna bak\u0131n.<\/span><\/p>\n \u00c7\u0131kt\u0131da sorgu de\u011fi\u015fkenlerinin de\u011ferleri g\u00f6r\u00fcnt\u00fclenir:<\/p>\n De\u011ferler, 2017 y\u0131l\u0131nda \u00fcretilen otomobil say\u0131s\u0131n\u0131 ve bu \u00fcretim y\u0131l\u0131na ait otomobillerin minimum, ortalama ve maksimum piyasa de\u011ferini g\u00f6stermektedir.<\/p>\n Bu \u00f6rnekte, daha sonra kullan\u0131lmak \u00fczere bir sakl\u0131 yordam i\u00e7inden birden fazla farkl\u0131 de\u011fer d\u00f6nd\u00fcrmek i\u00e7in \u00e7\u0131k\u0131\u015f parametrelerinin nas\u0131l kullan\u0131laca\u011f\u0131n\u0131 \u00f6\u011frendiniz. Bir sonraki b\u00f6l\u00fcmde, olu\u015fturulan yordamlar\u0131n nas\u0131l silinece\u011fini \u00f6\u011freneceksiniz.<\/p>\n Bu b\u00f6l\u00fcmde veritaban\u0131ndaki sakl\u0131 yordamlar\u0131 sileceksiniz.<\/p>\n Bazen olu\u015fturdu\u011funuz bir prosed\u00fcre art\u0131k ihtiya\u00e7 duyulmayabilir. Baz\u0131 durumlarda ise, prosed\u00fcr\u00fcn \u00e7al\u0131\u015fma \u015feklini de\u011fi\u015ftirmek isteyebilirsiniz. MySQL, bir prosed\u00fcr\u00fcn tan\u0131m\u0131n\u0131 olu\u015fturulduktan sonra de\u011fi\u015ftirmenize izin vermez, bu nedenle tek yol \u00f6nce prosed\u00fcr\u00fc silmek ve ard\u0131ndan istenen de\u011fi\u015fikliklerle yeniden olu\u015fturmakt\u0131r.<\/p>\n Son prosed\u00fcr olan get_car_stats_by_year'\u0131 b\u0131rakal\u0131m. Bunu yapmak i\u00e7in DROP PROCEDURE ifadesini kullanabilirsiniz:<\/p>\n Veritaban\u0131, prosed\u00fcr\u00fcn ba\u015far\u0131yla silindi\u011fini bir ba\u015far\u0131 mesaj\u0131yla do\u011frular:<\/p>\n Prosed\u00fcr\u00fcn silindi\u011fini, onu \u00e7a\u011f\u0131rarak do\u011frulayabilirsiniz. \u00c7al\u0131\u015ft\u0131r\u0131n:<\/p>\n Bu sefer prosed\u00fcr\u00fcn veritaban\u0131nda bulunmad\u0131\u011f\u0131na dair bir hata mesaj\u0131 g\u00f6receksiniz:<\/p>\n Bu b\u00f6l\u00fcmde, bir veritaban\u0131ndan sakl\u0131 yordamlar\u0131n nas\u0131l silinece\u011fini \u00f6\u011frendiniz.<\/p>\n Bu k\u0131lavuzu izleyerek, sakl\u0131 yordamlar\u0131n ne oldu\u011funu ve MySQL'de yeniden kullan\u0131labilir ifadeleri adland\u0131r\u0131lm\u0131\u015f yordamlarda depolamak ve daha sonra \u00e7al\u0131\u015ft\u0131rmak i\u00e7in nas\u0131l kullan\u0131laca\u011f\u0131n\u0131 \u00f6\u011frendiniz. Parametresiz sakl\u0131 yordamlar ve giri\u015f ve \u00e7\u0131k\u0131\u015f parametreleri kullanarak daha esnek hale getiren yordamlar olu\u015fturdunuz.<\/p>","protected":false},"excerpt":{"rendered":"Giri\u015f Tipik olarak, ili\u015fkisel bir veritaban\u0131yla \u00e7al\u0131\u015f\u0131rken, Yap\u0131land\u0131r\u0131lm\u0131\u015f Sorgu Dili (SQL) sorgular\u0131\u2026","protected":false},"author":1,"featured_media":15989,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_yoast_wpseo_focuskw":"\u0646\u062d\u0648\u0647 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 \u0631\u0648\u06cc\u0647 \u0647\u0627\u06cc \u0630\u062e\u06cc\u0631\u0647 \u0634\u062f\u0647 \u062f\u0631 MySQL","_yoast_wpseo_title":"","_yoast_wpseo_metadesc":"","_yoast_wpseo_canonical":"","_yoast_wpseo_opengraph-description":"","_yoast_wpseo_opengraph-image":"","_yoast_wpseo_twitter-description":"","_yoast_wpseo_twitter-image":"","_yoast_wpseo_focuskeywords":"","_yoast_wpseo_primary_category":"193","footnotes":""},"categories":[193,349],"tags":[403,391],"class_list":{"0":"post-15988","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-tutorials","8":"category-database","9":"tag-data-base","10":"tag-mysql"},"yoast_head":"\nMySQL'e ba\u011flanma ve \u00f6rnek bir veritaban\u0131 kurma<\/h2>\n
ssh sammy@your_server_ip\r\n<\/code><\/pre>\n<\/div>\nmysql -u sammy -p\r\n<\/code><\/pre>\n<\/div>\nCREATE DATABASE procedures;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 1 row affected (0.01 sec)<\/code><\/pre>\n<\/div>\nUSE procedures;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\nDatabase changed<\/code><\/pre>\n<\/div>\n\n
CREATE TABLE cars (\r\nmake varchar(100),\r\nmodel varchar(100),\r\nyear int,\r\nvalue decimal(10, 2)\r\n);<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/pre>\n<\/div>\nINSERT INTO cars\r\nVALUES\r\n('Porsche', '911 GT3', 2020, 169700),\r\n('Porsche', 'Cayman GT4', 2018, 118000),\r\n('Porsche', 'Panamera', 2022, 113200),\r\n('Porsche', 'Macan', 2019, 27400),\r\n('Porsche', '718 Boxster', 2017, 48880),\r\n('Ferrari', '488 GTB', 2015, 254750),\r\n('Ferrari', 'F8 Tributo', 2019, 375000),\r\n('Ferrari', 'SF90 Stradale', 2020, 627000),\r\n('Ferrari', '812 Superfast', 2017, 335300),\r\n('Ferrari', 'GTC4Lusso', 2016, 268000);<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 10 rows affected (0.00 sec)\r\nRecords: 10 Duplicates: 0 Warnings: 0<\/code><\/pre>\n<\/div>\nSakl\u0131 Yordamlara Giri\u015f<\/h2>\n
\n
DELIMITER \/\/\r\nCREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)\r\nBEGIN\r\ninstruction_1;\r\ninstruction_2;\r\n. . .\r\ninstruction_n;\r\nEND \/\/\r\nDELIMITER ;<\/code><\/pre>\n<\/div>\nParametresiz sakl\u0131 yordam olu\u015fturma<\/h2>\n
SELECT * FROM cars ORDER BY make, value DESC;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+---------+---------------+------+-----------+\r\n| make | model | year | value |\r\n+---------+---------------+------+-----------+\r\n| Ferrari | SF90 Stradale | 2020 | 627000.00 |\r\n| Ferrari | F8 Tributo | 2019 | 375000.00 |\r\n| Ferrari | 812 Superfast | 2017 | 335300.00 |\r\n| Ferrari | GTC4Lusso | 2016 | 268000.00 |\r\n| Ferrari | 488 GTB | 2015 | 254750.00 |\r\n| Porsche | 911 GT3 | 2020 | 169700.00 |\r\n| Porsche | Cayman GT4 | 2018 | 118000.00 |\r\n| Porsche | Panamera | 2022 | 113200.00 |\r\n| Porsche | 718 Boxster | 2017 | 48880.00 |\r\n| Porsche | Macan | 2019 | 27400.00 |\r\n+---------+---------------+------+-----------+\r\n10 rows in set (0.00 sec)<\/code><\/pre>\n<\/div>\nDELIMITER \/\/\r\nCREATE PROCEDURE get_all_cars()\r\nBEGIN\r\nSELECT * FROM cars ORDER BY make, value DESC;\r\nEND \/\/\r\nDELIMITER ;<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 0 rows affected (0.02 sec)<\/code><\/pre>\n<\/div>\nCALL get_all_cars;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+---------+---------------+------+-----------+\r\n| make | model | year | value |\r\n+---------+---------------+------+-----------+\r\n| Ferrari | SF90 Stradale | 2020 | 627000.00 |\r\n| Ferrari | F8 Tributo | 2019 | 375000.00 |\r\n| Ferrari | 812 Superfast | 2017 | 335300.00 |\r\n| Ferrari | GTC4Lusso | 2016 | 268000.00 |\r\n| Ferrari | 488 GTB | 2015 | 254750.00 |\r\n| Porsche | 911 GT3 | 2020 | 169700.00 |\r\n| Porsche | Cayman GT4 | 2018 | 118000.00 |\r\n| Porsche | Panamera | 2022 | 113200.00 |\r\n| Porsche | 718 Boxster | 2017 | 48880.00 |\r\n| Porsche | Macan | 2019 | 27400.00 |\r\n+---------+---------------+------+-----------+\r\n10 rows in set (0.00 sec)\r\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/pre>\n<\/div>\nGiri\u015f parametreleriyle sakl\u0131 yordam olu\u015fturma<\/h2>\n
DELIMITER \/\/\r\nCREATE PROCEDURE get_cars_by_year(\r\nIN year_filter int\r\n)\r\nBEGIN\r\nSELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;\r\nEND \/\/\r\nDELIMITER ;<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 0 rows affected (0.02 sec)<\/code><\/pre>\n<\/div>\nCALL get_cars_by_year;\r\n<\/code><\/pre>\n<\/div>\nError message\r\nERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0<\/code><\/pre>\n<\/div>\nCALL get_cars_by_year(2017);\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+---------+---------------+------+-----------+\r\n| make | model | year | value |\r\n+---------+---------------+------+-----------+\r\n| Ferrari | 812 Superfast | 2017 | 335300.00 |\r\n| Porsche | 718 Boxster | 2017 | 48880.00 |\r\n+---------+---------------+------+-----------+\r\n2 rows in set (0.00 sec)\r\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/pre>\n<\/div>\nGiri\u015f ve \u00e7\u0131k\u0131\u015f parametreleriyle sakl\u0131 yordam olu\u015fturma<\/h2>\n
DELIMITER \/\/\r\nCREATE PROCEDURE get_car_stats_by_year(\r\nIN year_filter int,\r\nOUT cars_number int,\r\nOUT min_value decimal(10, 2),\r\nOUT avg_value decimal(10, 2),\r\nOUT max_value decimal(10, 2)\r\n)\r\nBEGIN\r\nSELECT COUNT(*), MIN(value), AVG(value), MAX(value)\r\nINTO cars_number, min_value, avg_value, max_value\r\nFROM cars\r\nWHERE year = year_filter ORDER BY make, value DESC;\r\nEND \/\/\r\nDELIMITER ;<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 0 rows affected (0.02 sec)<\/code><\/pre>\n<\/div>\nCALL get_car_stats_by_year(2017, @number, @min, @avg, @max);\r\n<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 1 row affected (0.00 sec)<\/code><\/pre>\n<\/div>\nSELECT @number, @min, @avg, @max;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+---------+----------+-----------+-----------+\r\n| @number | @min | @avg | @max |\r\n+---------+----------+-----------+-----------+\r\n| 2 | 48880.00 | 192090.00 | 335300.00 |\r\n+---------+----------+-----------+-----------+\r\n1 row in set (0.00 sec)<\/code><\/pre>\n<\/div>\nSakl\u0131 prosed\u00fcrleri sil<\/h2>\n
DROP PROCEDURE get_car_stats_by_year;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 0 rows affected (0.02 sec)<\/code><\/pre>\n<\/div>\nCALL get_car_stats_by_year(2017, @number, @min, @avg, @max);\r\n<\/code><\/pre>\n<\/div>\nError message\r\nERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist<\/code><\/pre>\n<\/div>\nSonu\u00e7<\/h2>\n