3 Y\u0131ll\u0131k Deneyime Sahip PL\/SQL Geli\u015ftiricileri \u0130\u00e7in M\u00fclakat Sorular\u0131<\/p>\n
PL\/SQL, Oracle veritaban\u0131 sistemleri i\u00e7in kullan\u0131lan g\u00fc\u00e7l\u00fc bir programlama dilidir. Bir PL\/SQL geli\u015ftiricisi olarak, veritaban\u0131 nesneleri olu\u015fturma, verileri manip\u00fcle etme ve karma\u015f\u0131k i\u015f mant\u0131\u011f\u0131 uygulamak gibi g\u00f6revler \u00fcstlenirsiniz. 3 y\u0131ll\u0131k deneyime sahip bir PL\/SQL geli\u015ftiricisi olarak, m\u00fclakatlarda kar\u015f\u0131la\u015fabilece\u011finiz baz\u0131 sorular\u0131 ve bunlara nas\u0131l yan\u0131t verebilece\u011finizi inceleyelim.<\/p>\n
PL\/SQL ile \u00e7al\u0131\u015fmaya ba\u015flamak i\u00e7in baz\u0131 temel kavramlar\u0131 bilmeniz gerekir. Bu kavramlar\u0131 anlamak, m\u00fclakat sorular\u0131n\u0131 cevaplaman\u0131za ve PL\/SQL’nin yeteneklerini tam olarak kullanman\u0131za yard\u0131mc\u0131 olacakt\u0131r.<\/p>\n
PL\/SQL, Oracle veritaban\u0131 i\u00e7in yap\u0131land\u0131r\u0131lm\u0131\u015f bir programlama dilidir. Bu dilin temel \u00f6zellikleri aras\u0131nda:<\/p>\n
PL\/SQL’in avantajlar\u0131 aras\u0131nda:<\/p>\n
PL\/SQL, say\u0131lar, metin, tarih ve di\u011fer verileri depolamak i\u00e7in farkl\u0131 veri tipleri sunar. Bu veri tiplerini anlayarak do\u011fru veri t\u00fcr\u00fcn\u00fc se\u00e7ip kullanabilirsiniz.<\/p>\n
De\u011fi\u015fkenler, PL\/SQL bloklar\u0131nda de\u011ferleri depolamak i\u00e7in kullan\u0131l\u0131r. De\u011fi\u015fkenler, veri t\u00fcrleri ve isimlerini belirterek tan\u0131mlan\u0131r ve de\u011ferleri atama operat\u00f6r\u00fc (=) ile atanabilir.<\/p>\n
“`sql
\nDECLARE
\n — Say\u0131sal de\u011fi\u015fken tan\u0131mlama
\n my_number NUMBER := 10;
\n — Metin de\u011fi\u015fkeni tan\u0131mlama
\n my_string VARCHAR2(20) := ‘Merhaba D\u00fcnya’;
\nBEGIN
\n — De\u011fi\u015fkenlere de\u011fer atama
\n my_number := my_number + 5;
\n — De\u011fi\u015fkenleri ekrana yazd\u0131rma
\n DBMS_OUTPUT.PUT_LINE(‘Say\u0131: ‘ || my_number);
\n DBMS_OUTPUT.PUT_LINE(‘Metin: ‘ || my_string);
\nEND;
\n\/
\n“`<\/p>\n
Ko\u015fullu ifadeler, belirli ko\u015fullar alt\u0131nda kod bloklar\u0131n\u0131n \u00e7al\u0131\u015ft\u0131r\u0131lmas\u0131n\u0131 sa\u011flar. PL\/SQL’de IF-THEN-ELSE ve CASE ifadeleri kullan\u0131l\u0131r.<\/p>\n
“`sql
\nDECLARE
\n age NUMBER := 18;
\nBEGIN
\n — IF-THEN-ELSE ifadesi
\n IF age >= 18 THEN
\n DBMS_OUTPUT.PUT_LINE(‘Re\u015fit’);
\n ELSE
\n DBMS_OUTPUT.PUT_LINE(‘Re\u015fit de\u011fil’);
\n END IF;<\/p>\n
— CASE ifadesi
\n CASE age
\n WHEN 18 THEN DBMS_OUTPUT.PUT_LINE(‘Tam re\u015fit’);
\n WHEN 19 THEN DBMS_OUTPUT.PUT_LINE(‘Yeni re\u015fit’);
\n ELSE DBMS_OUTPUT.PUT_LINE(‘Belirtilmemi\u015f’);
\n END CASE;
\nEND;
\n\/
\n“`<\/p>\n
D\u00f6ng\u00fcler, belirli bir kod blo\u011funun tekrar tekrar \u00e7al\u0131\u015ft\u0131r\u0131lmas\u0131n\u0131 sa\u011flar. PL\/SQL’de LOOP, WHILE ve FOR d\u00f6ng\u00fcleri kullan\u0131l\u0131r.<\/p>\n
“`sql
\nDECLARE
\n i NUMBER := 1;
\nBEGIN
\n — LOOP d\u00f6ng\u00fcs\u00fc
\n LOOP
\n DBMS_OUTPUT.PUT_LINE(‘D\u00f6ng\u00fc ad\u0131m: ‘ || i);
\n i := i + 1;
\n EXIT WHEN i > 5;
\n END LOOP;<\/p>\n
— WHILE d\u00f6ng\u00fcs\u00fc
\n WHILE i <= 10 LOOP\n DBMS_OUTPUT.PUT_LINE('WHILE d\u00f6ng\u00fcs\u00fc: ' || i);\n i := i + 1;\n END LOOP;\n\n -- FOR d\u00f6ng\u00fcs\u00fc\n FOR j IN 1..10 LOOP\n DBMS_OUTPUT.PUT_LINE('FOR d\u00f6ng\u00fcs\u00fc: ' || j);\n END LOOP;\nEND;\n\/\n```\n\n\n\n
Prosed\u00fcrler ve fonksiyonlar, tekrar tekrar kullan\u0131lan kod par\u00e7ac\u0131klar\u0131n\u0131 d\u00fczenlemek ve kodun tekrar\u0131n\u0131 azaltmak i\u00e7in kullan\u0131l\u0131r. Prosed\u00fcrler sonu\u00e7 d\u00f6nd\u00fcrmezken, fonksiyonlar sonu\u00e7 d\u00f6nd\u00fcr\u00fcr.<\/p>\n
“`sql
\n— Prosed\u00fcr tan\u0131mlama
\nCREATE OR REPLACE PROCEDURE sayilari_topla(p_sayi1 NUMBER, p_sayi2 NUMBER)
\nAS
\n toplam NUMBER;
\nBEGIN
\n toplam := p_sayi1 + p_sayi2;
\n DBMS_OUTPUT.PUT_LINE(‘Toplam: ‘ || toplam);
\nEND;
\n\/<\/p>\n
— Prosed\u00fcr\u00fc \u00e7a\u011f\u0131rma
\nEXECUTE sayilari_topla(10, 20);<\/p>\n
— Fonksiyon tan\u0131mlama
\nCREATE OR REPLACE FUNCTION sayilari_carp(p_sayi1 NUMBER, p_sayi2 NUMBER)
\nRETURN NUMBER
\nAS
\n carpim NUMBER;
\nBEGIN
\n carpim := p_sayi1 * p_sayi2;
\n RETURN carpim;
\nEND;
\n\/<\/p>\n
— Fonksiyonu \u00e7a\u011f\u0131rma
\nDECLARE
\n sonuc NUMBER;
\nBEGIN
\n sonuc := sayilari_carp(5, 10);
\n DBMS_OUTPUT.PUT_LINE(‘\u00c7arp\u0131m: ‘ || sonuc);
\nEND;
\n\/
\n“`<\/p>\n
Hata y\u00f6netimi, kodun hatalar\u0131 ele almas\u0131n\u0131 ve beklenmedik durumlarla ba\u015fa \u00e7\u0131kmas\u0131n\u0131 sa\u011flar. PL\/SQL, hata yakalama ve i\u015fleme i\u00e7in EXCEPTION blo\u011funu kullan\u0131r.<\/p>\n
“`sql
\nDECLARE
\n my_number NUMBER;
\nBEGIN
\n — Hatal\u0131 kod
\n my_number := 10 \/ 0;
\nEXCEPTION
\n WHEN ZERO_DIVIDE THEN
\n DBMS_OUTPUT.PUT_LINE(‘S\u0131f\u0131ra b\u00f6lme hatas\u0131!’);
\nEND;
\n\/
\n“`<\/p>\n
PL\/SQL, veritaban\u0131 nesneleri olu\u015fturmak, verileri manip\u00fcle etmek ve veritaban\u0131 i\u015flemlerini y\u00f6netmek i\u00e7in kullan\u0131l\u0131r. Bu alanlardaki deneyiminizi g\u00f6stermek i\u00e7in a\u015fa\u011f\u0131daki sorular\u0131 cevaplamaya haz\u0131r olun.<\/p>\n
INSERT, UPDATE ve DELETE SQL ifadelerini kullanarak veritaban\u0131na veri ekleyebilir, mevcut verileri g\u00fcncelleyebilir ve verileri silebilirsiniz. PL\/SQL, bu i\u015flemleri kodunuzun i\u00e7inde ger\u00e7ekle\u015ftirmenizi sa\u011flar.<\/p>\n
“`sql
\n— Veri ekleme
\nINSERT INTO employees (employee_id, first_name, last_name, salary)
\nVALUES (100, ‘Fatih’, ‘Soysal’, 5000);<\/p>\n
— Veri g\u00fcncelleme
\nUPDATE employees
\nSET salary = 6000
\nWHERE employee_id = 100;<\/p>\n
— Veri silme
\nDELETE FROM employees
\nWHERE employee_id = 100;
\n“`<\/p>\n
\u0130\u015flemler, bir veya daha fazla DML i\u015flemini tek bir atomik i\u015flem olarak y\u00f6netmek i\u00e7in kullan\u0131l\u0131r. Bu, i\u015flemlerin tamamen ba\u015far\u0131l\u0131 veya tamamen ba\u015far\u0131s\u0131z olmas\u0131n\u0131 sa\u011flar. PL\/SQL’de i\u015flemler, TRANSACTION anahtar s\u00f6zc\u00fc\u011f\u00fc kullan\u0131larak y\u00f6netilir.<\/p>\n
“`sql
\nDECLARE
\n v_employee_id NUMBER;
\nBEGIN
\n — \u0130\u015flem ba\u015flatma
\n BEGIN TRANSACTION;
\n — Yeni bir \u00e7al\u0131\u015fan ekleme
\n INSERT INTO employees (employee_id, first_name, last_name, salary)
\n VALUES (101, ‘Ali’, ‘Y\u0131lmaz’, 4500);
\n — \u00c7al\u0131\u015fan\u0131n maa\u015f\u0131n\u0131 g\u00fcncelleme
\n UPDATE employees
\n SET salary = 5000
\n WHERE employee_id = 101;
\n — \u0130\u015flem sonland\u0131rma
\n COMMIT;
\nEXCEPTION
\n WHEN OTHERS THEN
\n — Hatal\u0131 i\u015flem durumunda geri alma
\n ROLLBACK;
\n RAISE;
\nEND;
\n\/
\n“`<\/p>\n
Triggerlar, veritaban\u0131 tablolar\u0131nda olaylara tepki olarak \u00e7al\u0131\u015ft\u0131r\u0131lan \u00f6zel PL\/SQL kod bloklar\u0131d\u0131r. INSERT, UPDATE veya DELETE i\u015flemleri gibi belirli olaylar oldu\u011funda otomatik olarak \u00e7al\u0131\u015ft\u0131r\u0131labilirler.<\/p>\n
“`sql
\nCREATE OR REPLACE TRIGGER employee_salary_update
\n BEFORE UPDATE OF salary ON employees
\n FOR EACH ROW
\nBEGIN
\n — Maa\u015f art\u0131\u015f oran\u0131 hesaplama
\n IF :new.salary > :old.salary THEN
\n DBMS_OUTPUT.PUT_LINE(‘\u00c7al\u0131\u015fan maa\u015f\u0131 art\u0131r\u0131ld\u0131.’);
\n ELSIF :new.salary < :old.salary THEN\n DBMS_OUTPUT.PUT_LINE('\u00c7al\u0131\u015fan maa\u015f\u0131 azalt\u0131ld\u0131.');\n END IF;\nEND;\n\/\n```\n\n\n\n
Stored procedures ve functions, tekrar kullan\u0131labilir kod bloklar\u0131n\u0131n veritaban\u0131nda depolanmas\u0131n\u0131 sa\u011flar. Bunlar, karma\u015f\u0131k i\u015f mant\u0131\u011f\u0131n\u0131 paketlemek, veritaban\u0131 i\u015flemlerini optimize etmek ve veri eri\u015fimini kontrol etmek i\u00e7in kullan\u0131l\u0131r. Stored procedures ve functions, daha \u00f6nceki \u00f6rneklerde oldu\u011fu gibi CREATE OR REPLACE PROCEDURE ve CREATE OR REPLACE FUNCTION komutlar\u0131 ile olu\u015fturulur ve \u00e7a\u011f\u0131r\u0131l\u0131r.<\/p>\n
Cursorlar, veritaban\u0131ndan al\u0131nan veri k\u00fcmelerine sat\u0131r sat\u0131r eri\u015fim sa\u011flayan \u00f6zel PL\/SQL nesneleridir. Bu, veritaban\u0131 sorgular\u0131n\u0131n sonu\u00e7lar\u0131n\u0131 i\u015flerken her bir sat\u0131r \u00fczerinde i\u015flem yapman\u0131za olanak tan\u0131r.<\/p>\n
“`sql
\nDECLARE
\n — Cursor tan\u0131mlama
\n CURSOR employee_cursor IS
\n SELECT employee_id, first_name, last_name, salary
\n FROM employees
\n WHERE salary > 5000;
\n — Cursor de\u011fi\u015fkeni tan\u0131mlama
\n employee_record employee_cursor%ROWTYPE;
\nBEGIN
\n — Cursor a\u00e7ma
\n OPEN employee_cursor;
\n — Sat\u0131r sat\u0131r veri okuma
\n LOOP
\n FETCH employee_cursor INTO employee_record;
\n EXIT WHEN employee_cursor%NOTFOUND;
\n — Verileri i\u015fleme
\n DBMS_OUTPUT.PUT_LINE(‘\u00c7al\u0131\u015fan ID: ‘ || employee_record.employee_id);
\n DBMS_OUTPUT.PUT_LINE(‘\u00c7al\u0131\u015fan Ad\u0131: ‘ || employee_record.first_name);
\n END LOOP;
\n — Cursor kapatma
\n CLOSE employee_cursor;
\nEND;
\n\/
\n“`<\/p>\n
Paketler, PL\/SQL prosed\u00fcrleri, fonksiyonlar\u0131, de\u011fi\u015fkenleri, tipleri ve cursorlar gibi ilgili nesneleri bir araya getirmek i\u00e7in kullan\u0131l\u0131r. Paketler, kodun organizasyonunu ve tekrar kullan\u0131labilirli\u011fini art\u0131r\u0131r.<\/p>\n
“`sql
\nCREATE OR REPLACE PACKAGE employee_package
\nAS
\n — Fonksiyon tan\u0131mlama
\n FUNCTION get_employee_count RETURN NUMBER;
\n — Prosed\u00fcr tan\u0131mlama
\n PROCEDURE update_employee_salary(p_employee_id NUMBER, p_new_salary NUMBER);
\nEND;
\n\/<\/p>\n
CREATE OR REPLACE PACKAGE BODY employee_package
\nAS
\n FUNCTION get_employee_count RETURN NUMBER
\n IS
\n v_count NUMBER;
\n BEGIN
\n SELECT COUNT(*) INTO v_count FROM employees;
\n RETURN v_count;
\n END;<\/p>\n
PROCEDURE update_employee_salary(p_employee_id NUMBER, p_new_salary NUMBER)
\n IS
\n BEGIN
\n UPDATE employees
\n SET salary = p_new_salary
\n WHERE employee_id = p_employee_id;
\n END;
\nEND;
\n\/
\n“`<\/p>\n
3 y\u0131ll\u0131k deneyime sahip bir PL\/SQL geli\u015ftiricisi olarak, ileri d\u00fczey konularda da bilgi sahibi olman\u0131z beklenir. A\u015fa\u011f\u0131daki konularla ilgili sorulara haz\u0131r olun.<\/p>\n
\u0130\u015flemsel g\u00f6r\u00fcn\u00fcm (stored procedure) ve fonksiyon (function) aras\u0131nda birka\u00e7 \u00f6nemli fark vard\u0131r:<\/p>\n
PL\/SQL kodunuzun performans\u0131n\u0131 iyile\u015ftirmek i\u00e7in \u00e7e\u015fitli teknikler kullan\u0131labilir:<\/p>\n
PL\/SQL, farkl\u0131 veri yap\u0131lar\u0131na ihtiya\u00e7 duydu\u011funuzda kullanabilece\u011finiz farkl\u0131 tipler sunar:<\/p>\n
PL\/SQL, farkl\u0131 hata durumlar\u0131n\u0131 ele almak i\u00e7in kullanabilece\u011finiz bir hata y\u00f6netimi mekanizmas\u0131 sa\u011flar. Hatalar\u0131 ele almak i\u00e7in EXCEPTION blo\u011funu ve \u00e7e\u015fitli hata t\u00fcrlerini kullanabilirsiniz. <\/p>\n
PL\/SQL’de, belirli zamanlarda veya belirli aral\u0131klarda otomatik olarak \u00e7al\u0131\u015facak kod bloklar\u0131 tan\u0131mlamak i\u00e7in zamanlama kullanabilirsiniz. Zamanlama, DBMS_SCHEDULER paketi arac\u0131l\u0131\u011f\u0131yla ger\u00e7ekle\u015ftirilir.<\/p>\n
“`sql
\nBEGIN
\n — Yeni bir zamanlama i\u015fi olu\u015fturma
\n DBMS_SCHEDULER.CREATE_JOB (
\n job_name => ‘my_job’,
\n job_type => ‘PLSQL_BLOCK’,
\n job_action => ‘BEGIN DBMS_OUTPUT.PUT_LINE(”Zamanlama \u00e7al\u0131\u015f\u0131yor!”); END;’,
\n start_date => SYSDATE,
\n repeat_interval => ‘FREQ=MINUTELY; INTERVAL=5’,
\n enabled => TRUE
\n );
\nEND;
\n\/
\n“`<\/p>\n
PL\/SQL kodunun g\u00fcvenli\u011fini sa\u011flamak i\u00e7in birka\u00e7 \u00f6nemli ad\u0131m atman\u0131z gerekir:<\/p>\n
Oracle, her yeni s\u00fcr\u00fcmle PL\/SQL’ye yeni \u00f6zellikler ekliyor. Yeni \u00f6zelliklerden haberdar olmak ve bunlar\u0131 kullanmak i\u00e7in Oracle belgelerini takip etmeniz ve yeni s\u00fcr\u00fcmlerin \u00f6zelliklerinden haberdar olman\u0131z gerekir. <\/p>\n
PL\/SQL geli\u015ftiricisi olarak m\u00fclakatlara haz\u0131rlanmak i\u00e7in \u015funlar\u0131 yapabilirsiniz:<\/p>\n
Umar\u0131m bu makale, 3 y\u0131ll\u0131k deneyime sahip PL\/SQL geli\u015ftiricileri i\u00e7in m\u00fclakat sorular\u0131n\u0131 anlaman\u0131za yard\u0131mc\u0131 olur. Kendinize g\u00fcvenin, \u00e7al\u0131\u015f\u0131n ve ba\u015far\u0131ya ula\u015f\u0131n!<\/p>\n
\u0130lave Bilgiler \u0130\u00e7in:<\/strong><\/p>\n #PL\/SQL #M\u00fclakat #Geli\u015ftirici #Oracle #Veritaban\u0131 #Programlama #SQL #Stored Procedures #Functions #Triggerlar #Paketler #Performans #Optimizasyon #Hata Y\u00f6netimi #Zamanlama #G\u00fcvenlik #Deneyim #\u0130pu\u00e7lar\u0131 #Haz\u0131rl\u0131k #E\u011fitim #Kaynaklar <\/p>\n","protected":false},"excerpt":{"rendered":"3 Y\u0131ll\u0131k Deneyime Sahip PL\/SQL Geli\u015ftiricileri \u0130\u00e7in M\u00fclakat Sorular\u0131 PL\/SQL, Oracle veritaban\u0131 sistemleri i\u00e7in kullan\u0131lan g\u00fc\u00e7l\u00fc bir programlama…","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"csco_page_header_type":"","csco_page_load_nextpost":"","csco_page_subscribe_form":"","csco_page_contact_form":"","footnotes":""},"categories":[1],"tags":[],"class_list":{"0":"post-3515","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-genel","7":"cs-entry","8":"cs-video-wrap"},"yoast_head":"\n\n
#Etiketler<\/h2>\n