Oracle-ing Myself – Lesson 1 Özeti

hr user login olamaması sorununu user’ı alter ederek çözebiliriz.

cmd > sqlplus / as sysdba
alter user hr identified by hr account unlock;
exit;
lsnrctl start

sqlplus hr/hr

bağlı bulunduğumuz şemadaki tabloları göster
select * from tab
select * from user_tables

tablonun kolon bilgilerini getirmek
describe employees;
desc employees;

Oracle/PLSQL’de, NVL fonksiyonu null olan bir değeri başka bir değerle değiştirebilmemizi sağlar.
NVL( string1, replace_with )

NVL(COMMISSION_PCT,0) COMMISSION_PCT null ise 0 getir

Concat Operatorü: || ya da concat( string1, string2 ), string birleştirme
alias operatörü : as, kolon adını yeniden verme
q’['in maaşı]‘ : kullanılamayan karakterleri kullanabilmek için

SELECT EMPLOYEES.FIRST_NAME || ' ' || EMPLOYEES.LAST_NAME || q'['IN maaşı]' 
        as ADSOYAD, EMPLOYEES.SALARY as MAAS, 
        NVL(EMPLOYEES.COMMISSION_PCT,0) as KOMISYON,
        (SALARY*12)+(SALARY*12*NVL(EMPLOYEES.COMMISSION_PCT,0)) 
        as YILLIKMAAS, EMPLOYEES.DEPARTMENT_ID as DEPARTMAN
FROM EMPLOYEES
WHERE EMPLOYEES.DEPARTMENT_ID = 90
        and EMPLOYEES.LAST_NAME = 'King'
        and EMPLOYEES.SALARY between 2000 and 25000
        or EMPLOYEES.LAST_NAME between 'Abel' and 'Austin'
        or EMPLOYEES.LAST_NAME like 'B%l_'
        and EMPLOYEES.COMMISSION_PCT is null
        and EMPLOYEES.DEPARTMENT_ID in (90,50)
        and EMPLOYEES.DEPARTMENT_ID not in (80)
        and EMPLOYEES.JOB_ID not in ('IT_PROG','AD_ASST')
ORDER BY MAAS desc

çift amp. (&&) kullanırsak aynı addaki iki parametreyi tekrar girme zahmetinden kurtuluyoruz
(performansa biraz katkısı var)

DEFINE kriter = 'King'
 
SELECT &&kolonadi
FROM &tabloadi
WHERE last_name = '&kriter'
ORDER BY &kolonadi
  • Facebook
  • Twitter
  • StumbleUpon
  • del.icio.us
  • Digg

Category: Sql - Comment »


Leave a Reply



↑ Back to top