{"id":111,"date":"2020-06-02T21:21:04","date_gmt":"2020-06-02T21:21:04","guid":{"rendered":"http:\/\/bahadirmeric.net\/bahadir\/?p=111"},"modified":"2020-06-02T21:21:04","modified_gmt":"2020-06-02T21:21:04","slug":"mysql-de-sp-yazmak","status":"publish","type":"post","link":"http:\/\/bahadirmeric.net\/bahadir\/2020\/06\/02\/mysql-de-sp-yazmak\/","title":{"rendered":"MySql\u2019 de SP yazmak"},"content":{"rendered":"\n<p>MySql\u2019 in geli\u015fmesiyle birlikte 5.0 versiyonu ile yanl\u0131\u015f hat\u0131rlam\u0131yorsam art\u0131k i\u00e7ine MsSql\u2019 de oldu\u011fu gibi SP(Store Procedure) yazmaya ba\u015flayabildik. Sql yani veritaban\u0131 d\u00fcnyas\u0131nda zaten hep bir yok MsSql daha iyidir yok MySql daha iyidir diye bir yar\u0131\u015f vard\u0131r. Hepsi asl\u0131nda kendi i\u00e7lerinde belli ko\u015ful ve \u015fartlar sa\u011fland\u0131\u011f\u0131nda iyidir ve h\u0131zl\u0131 \u00e7al\u0131\u015f\u0131r. Dikkat ederseniz Oracle\u2019 yi bu kavgaya dahil etmedim \u00e7\u00fcnk\u00fc herkesin kabul etsin yada etmesin ortak g\u00f6r\u00fc\u015flerinden biri Oracle\u2019 nin ger\u00e7ekten hepsinden de iyi oldu\u011fu y\u00f6n\u00fcndedir ki bende buna sonuna kadar kat\u0131l\u0131yorum.<br>Fakat bana soracak olursan\u0131z ben oyumu MySql\u2019 den yana kullan\u0131r\u0131m. MySql, MsSql \u2018den baz\u0131 konularda daha ge\u00e7 geli\u015fme g\u00f6stersede mevcuttaki core \u00e7ekirdek kararl\u0131l\u0131\u011f\u0131 baz\u0131 durumlarda MsSql \u2018e g\u00f6re bir \u00e7ok fayda sa\u011flamakta. Dedi\u011fim gibi biraz daha yava\u015f geli\u015fiyor olmas\u0131 bir dez avantaj\u0131 \u015fimdi gelelim bu veritaban\u0131nda da MsSql \u2018deki gibi bir SP \u2018nin nas\u0131l yaz\u0131laca\u011f\u0131n\u0131 g\u00f6relim.<\/p>\n\n\n\n<p>Ba\u015ftan belirtmeliyim ki MySql \u2018de SP yazarken MsSql \u2018deki al\u0131\u015fkanl\u0131klar\u0131n\u0131z\u0131 bir tarafa b\u0131rakman\u0131z gerekli. \u015eimdi MySql \u2018de \u00f6rnek bir SP tan\u0131mlamas\u0131 yapal\u0131m;<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE PROCEDURE SpCalculateInvoice(IN BeginDate DATETIME,IN EndDate DATETIME,IN UserName VARCHAR(250))\nBEGIN\n \nEND\n<\/pre><\/div>\n\n\n<p>Burada g\u00f6rece\u011finiz gibi d\u0131\u015far\u0131dan de\u011fer almak i\u00e7in kullanaca\u011f\u0131m\u0131z de\u011fi\u015fkenlerimizin ba\u015f\u0131na \u201cIN\u201d ifadesi konmu\u015f. Mysql \u2018de SP tan\u0131mlamas\u0131 yaparken bu bir zorunluluktur ve a\u015fa\u011f\u0131daki taglerden biri mutlaka bulunmal\u0131d\u0131r.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>IN : De\u011fi\u015fken sadece d\u0131\u015far\u0131dan SET edilir ve i\u00e7erideki i\u015flemlerde sadece kullan\u0131l\u0131r de\u011feri ezilemez !!!<\/li><li>OUT : De\u011fi\u015fken sadece i\u00e7eriden SET edilir d\u0131\u015far\u0131dan de\u011fer alamaz !!!<\/li><li>INOUT : De\u011fi\u015fken her iki taraftanda SET edilebilir. Hem d\u0131\u015far\u0131dan SET edilip SP i\u00e7ine de\u011fer aktarmada kullan\u0131l\u0131r hemde SP i\u00e7erisinden SET edilerek d\u0131\u015far\u0131 de\u011fer vermede.<\/li><\/ul>\n\n\n\n<p>\u015eimdi bunlarla ilgili birer \u00f6rnek yapal\u0131m. \u0130lk ba\u015fta zaten ben IN ifadesini kullanm\u0131\u015f ve bir SP tan\u0131mlam\u0131\u015f idim. \u015eimdi bunu nas\u0131l size d\u0131\u015far\u0131dan \u00e7a\u011f\u0131raca\u011f\u0131m\u0131 g\u00f6stereyim;<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCALL SpCalculateInvoice('2013-04-05 12:00:00','2013-04-05 21:00:00','bmeric');\n\n<\/pre><\/div>\n\n\n<p>E\u011fer a\u015fa\u011f\u0131daki gibi bir SP olu\u015fturursak;<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE PROCEDURE ikinciProcedure(OUT toplam INT)\nBEGIN\nselect Count(*) into toplam from users;\nEND\n<\/pre><\/div>\n\n\n<p>Bu olu\u015fturdu\u011fumuz prosed\u00fcrde users tablosundaki kullan\u0131c\u0131lar\u0131n say\u0131lar\u0131n\u0131 say\u0131p elde edilen sonucu toplam de\u011fi\u015fkenine at\u0131yor. Bu SP \u2018nin \u00e7a\u011f\u0131r\u0131\u015f\u0131 yukar\u0131daki gibi olmayacak. OUT \u2018lu SP \u2018leri \u00e7a\u011f\u0131rman\u0131n koduda a\u015fa\u011f\u0131daki gibidir;<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET @sonuc = 0;\nCALL ikinciProcedure(@sonuc);\nSELECT @sonuc;\n<\/pre><\/div>\n\n\n<p>\u015eimdi bir tek INOUT ile yaz\u0131lan SP \u2018mizi tan\u0131mlamak kald\u0131. \u0130\u015fte oda a\u015fa\u011f\u0131daki gibidir;<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE PROCEDURE ucuncuProcedure (INOUT sayi INT)\nBEGIN\nSET sayi = (SELECT COUNT(*) FROM users WHERE id=sayi);\nEND\n<\/pre><\/div>\n\n\n<p>Bu SP \u2018nin \u00e7a\u011f\u0131r\u0131m\u0131da a\u015fa\u011f\u0131daki gibidir;<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET @sonuc = 5;\nCALL ucuncuProcedure(@sonuc);\nSELECT @sonuc;\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>MySql\u2019 in geli\u015fmesiyle birlikte 5.0 versiyonu ile yanl\u0131\u015f hat\u0131rlam\u0131yorsam art\u0131k i\u00e7ine MsSql\u2019 de oldu\u011fu gibi SP(Store Procedure) yazmaya ba\u015flayabildik. Sql yani veritaban\u0131 d\u00fcnyas\u0131nda zaten hep bir yok MsSql daha iyidir yok MySql daha iyidir diye bir yar\u0131\u015f vard\u0131r. Hepsi asl\u0131nda kendi i\u00e7lerinde belli ko\u015ful ve \u015fartlar sa\u011fland\u0131\u011f\u0131nda iyidir ve h\u0131zl\u0131&#8230; <a class=\"continue-reading-link\" href=\"http:\/\/bahadirmeric.net\/bahadir\/2020\/06\/02\/mysql-de-sp-yazmak\/\"> Continue reading <span class=\"meta-nav\">&rarr; <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":10,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[10,2],"class_list":["post-111","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql","tag-sql"],"_links":{"self":[{"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/posts\/111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/comments?post=111"}],"version-history":[{"count":1,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/posts\/111\/revisions"}],"predecessor-version":[{"id":112,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/posts\/111\/revisions\/112"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/media\/10"}],"wp:attachment":[{"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/media?parent=111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/categories?post=111"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bahadirmeric.net\/bahadir\/wp-json\/wp\/v2\/tags?post=111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}