Transcript FUNCTION & STORED PROCEDURE

Siti Mukaromah,S.Kom, MCP.
1
2



Fungsi mirip dengan operator bahwa mereka
memanipulasi item data dan mengembalikan
hasilnya. Fungsi berbeda dari operator dalam
format argumen mereka. Format ini
memungkinkan mereka untuk beroperasi pada
nol, satu, dua, atau lebih argumen:
fungsi (argumen, argumen, ...)
Fungsi tanpa argumen biasanya mengembalikan
nilai yang sama untuk setiap baris.
3

Fungsi SQL dibangun ke dalam Oracle
Database dan tersedia untuk digunakan
dalam berbagai statement SQL yang sesuai.
Jangan bingung dengan fungsi-fungsi userdefined SQL yang ditulis dalam PL/SQL.
4

Dalam diagram sintaks untuk fungsi-fungsi
SQL, argumen ditandai dengan datatypes
mereka. Ketika parameter fungsi muncul
dalam sintaks SQL, gantikan dengan salah
satu fungsi yang diuraikan dalam bagian ini.
Fungsi dikelompokkan oleh datatypes dari
argumen mereka dan return value-nya.
5
Function
Single Row Function
6

Bagian-bagian yang mengikuti daftar built-in
fungsi SQL pada masing-masing kelompok
diilustrasikan dalam diagram tersebut kecuali
fungsi user-defined. Semua built-in fungsi
SQL ini kemudian dijelaskan dalam urutan
abjad.
7

Single Row Function mengembalikan hasil
tunggal untuk setiap baris dari sebuah query
tabel atau view. Fungsi-fungsi tersebut dapat
muncul dalam daftar pilih, klausa WHERE,
START WITH dan klausa CONNECT BY, serta
klausa HAVING.
8

Numeric Functions menerima input numerik
dan mengembalikan nilai numerik.
Kebanyakan Numeric Functions yang
mengembalikan nilai NUMBER yang akurat
sampai 38 digit desimal. Fungsi
transendental COS, COSH, EXP, LN, LOG, SIN,
SINH, SQRT, TAN, dan TANH akurat sampai
36 digit desimal. Fungsi transendental ACOS,
ASIN, ATAN, dan ATAN2 akurat sampai 30
digit desimal.
9










ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP









FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND
(number)
SIGN







SIN
SINH
SQRT
TAN
TANH
TRUNC
(number)
WIDTH_BUCK
ET
10
Karakter fungsi yang mengembalikan nilai
karakter, nilai pengembaliannya dari
datatypes berikut kecuali dinyatakan
dibuktikan:


Jika argumen input CHAR atau VARCHAR2,
maka nilai yang dikembalikan VARCHAR2.
Jika argumen input NCHAR atau NVARCHAR2,
maka nilai yang dikembalikan NVARCHAR2.
11
Panjang (length) nilai yang dikembalikan oleh fungsi ini
dibatasi oleh panjang (length) maksimum dari
datatype yang dikembalikan.


Untuk fungsi yang mengembalikan CHAR atau
VARCHAR2, jika panjang (length) dari nilai
pengembalian melebihi batas, Oracle Database akan
memotong dan mengembalikan hasilnya tanpa pesan
kesalahan.
Untuk fungsi yang mengembalikan nilai CLOB, jika
panjang melebihi batas nilai-nilai pengembalian,
Oracle akan menampilkan kesalahan dan tidak ada
pengembalian data.
12











CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE










REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER
13




Fungsi karakter NLS mengembalikan
informasi tentang karakter set. Fungsi
Karakter NLS adalah:
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
14






Karakter fungsi yang mengembalikan nilai
angka (number) dapat mengambil sebagai
argumen mereka setiap datatype karakter.
Fungsi karakter yang mengembalikan nilainilai angka (number) :
ASCII
INSTR
LENGTH
REGEXP_INSTR
15



Fungsi datetime (Datetime Functions) beroperasi pada nilai-nilai
tanggal (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME
ZONE, dan TIMESTAMP WITH LOCAL TIME ZONE), dan interval
(INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH).
Beberapa fungsi datetime dirancang untuk datatype Oracle DATE
(ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, dan
NEXT_DAY). Jika Anda memberikan nilai timestamp sebagai
argumen mereka, Oracle Database secara internal mengubah
tipe input menjadi nilai DATE dan mengembalikan nilai DATE.
Pengecualian adalah fungsi MONTHS_BETWEEN, yang
mengembalikan angka, dan fungsi ROUND dan TRUNC, yang
tidak menerima timestamp atau interval nilai sama sekali.
Fungsi datetime lainnya dirancang untuk menerima salah satu
dari tiga jenis data (tanggal, timestamp, dan interval) dan untuk
mengembalikan nilai salah satu jenis.
16













ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL












ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET
17
Fungsi perbandingan umum yang menentukan
nilai terbesar dan atau paling kecil dari satu
set nilai.
Fungsi perbandingan umum adalah:
 GREATEST
 LEAST
18

Conversion Functions (fungsi Konversi)
mengkonversi nilai dari satu datatype ke yang
lain. Secara umum, bentuk nama fungsi
mengikuti konvensi datatype TO datatype.
Datatype yang pertama adalah datatype
masukan (input datatype). Datatype kedua
adalah output datatype.
19

















ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
















TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR
(character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE






TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR
20
Large Object Functions beroperasi pada LOBs.
Fungsi Large Object Functions adalah:
 BFILENAME
 EMPTY_BLOB, EMPTY_CLOB
21
Collection Functions beroperasi pada tabel
nested dan varrays.
Fungsi SQLCollection Functions adalah:
 CARDINALITY
 COLLECT
 POWERMULTISET
 POWERMULTISET_BY_CARDINALITY
 SET
22


Hierarchical Function menerapkan informasi
jalur hirarkis pada set hasil.
SYS_CONNECT_BY_PATH
23
fungsi data mining yang beroperasi pada model yang telah
dibangun dengan menggunakan paket DBMS_DATA_MINING atau
Oracle Data Mining Java API.
Fungsi data mining SQL adalah:
 CLUSTER_ID
 CLUSTER_PROBABILITY
 CLUSTER_SET
 FEATURE_ID
 FEATURE_SET
 FEATURE_VALUE
 PREDICTION
 PREDICTION_COST
 PREDICTION_DETAILS
 PREDICTION_PROBABILITY
 PREDICTION_SET
24

Fungsi XML beroperasi pada dokumen atau
fragmen XML .
25














APPENDCHILDXML
DELETEXML
DEPTH
EXTRACT (XML)
EXISTSNODE
EXTRACTVALUE
INSERTCHILDXML
INSERTXMLBEFORE
PATH
SYS_DBURIGEN
SYS_XMLAGG
SYS_XMLGEN
UPDATEXML
XMLAGG













XMLCDATA
XMLCOLATTVAL
XMLCOMMENT
XMLCONCAT
XMLFOREST
XMLPARSE
XMLPI
XMLQUERY
XMLROOT
XMLSEQUENCE
XMLSERIALIZE
XMLTABLE
XMLTRANSFORM
26





fungsi encoding dan decoding
memungkinkan Anda memeriksa dan decode
(membaca kode) data dalam database.
DECODE
DUMP
ORA_HASH
VSIZE
27
NULL-Related Functions memfasilitasi
penanganan null
NULL-Related Functions adalah:
 COALESCE
 LNNVL
 NULLIF
 NVL
 NVL2
28







Lingkungan dan fungsi pengenal memberikan
informasi tentang instance dan sesi.
SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV
29

Fungsi Aggregate mengembalikan baris tunggal hasil
berdasarkan kelompok baris, bukan pada baris
tunggal. Fungsi Aggregate dapat muncul dalam
daftar select dan dalam klausa ORDER BY dan
HAVING. Mereka umumnya digunakan dengan klausa
GROUP BY dalam perintah SELECT, di mana Oracle
Database membagi baris dari sebuah tabel query atau
view ke dalam grup. Dalam query yang mengandung
klausa GROUP BY, unsur-unsur dari daftar select bisa
berupa Fungsi Aggregate , ekspresi GROUP BY,
konstanta, atau ekspresi yang melibatkan salah
satunya. Oracle menerapkan fungsi aggregate untuk
setiap kelompok baris dan mengembalikan hasil baris
tunggal untuk setiap kelompok.
30

Jika Anda menghilangkan klausa GROUP BY,
maka Oracle menerapkan fungsi aggregate
dalam daftar select untuk semua baris dalam
tabel query atau view. Anda menggunakan
fungsi aggregate pada klausa HAVING untuk
menghilangkan kelompok-kelompok dari
output berdasarkan hasil fungsi aggregate,
bukan pada nilai-nilai individu baris tabel
query atau view.
31
Model Functions dapat digunakan hanya dalam
model_clause dari SELECT.
Fungsi model:
 CV
 ITERATION_NUMBER
 PRESENTNNV
 PRESENTV
 PREVIOUS
32


Di Oracle Anda dapat membuat fungsi sendiri
Syntax untuk membuat Function:
33
Bila Anda membuat prosedur atau fungsi,
Anda dapat menentukan parameter. Ada tiga
jenis parameter yang dapat dideklarasikan:
1. IN - Parameter yang dapat direferensikan
oleh prosedur atau fungsi. Nilai parameter
tersebut tidak dapat ditimpa oleh prosedur
atau fungsi.
2. OUT - Parameter yang tidak dapat
direferensikan oleh prosedur atau fungsi,
tetapi nilai parameter dapat ditimpa oleh
prosedur atau fungsi.
3. IN OUT - Parameter yang dapat
direferensikan oleh prosedur atau fungsi
dan nilai parameter dapat ditimpa oleh
prosedur atau fungsi.

34

Simple Function Creation
35
36
37
38
39
40
41

Fungsi untuk menentukan apakah sebuah
string memiliki format nomor social security
yang valid
42
43
44
45
46
47
48
49
50
51
52
53

Untuk kembali ke PL/SQL tekan tombol
[ALT+F] lanjutkan dengan menekan tombol
[X] jika muncul pertanyaan pilih [Yes].
54

Semua contoh diatas adalah prosedur yang
tidak menggunakan parameter, dimana nilai
yang diproses sudah ditentukan dan berada
di dalam prosedur tersebut. Ada juga
prosedur yang sifatnya dinamis dimana
nilainya bisa kita ubah sesuai dengan kondisi
yang diinginkan. Agar bisa berfungsi seperti
itu prosedur tersebut harus menggunakan
sebuah paramater sebagai penerima inputan
dari user.
55
56

Contoh penerapannya bisa Anda lihat di bawah ini :
57
58
59
60
61
62
63
64
65
1.
2.
3.
Jelaskan perbedaan antara fungsi dan
prosedur!
Buat fungsi/prosedur yang bisa
menampilkan pesan bahwa bilangan yang
diinputkan adalah bilangan genap atau
ganjil!
Buat fungsi/prosedur untuk menghitung
jumlah panjang karakter nama yang diinputkan! Handle kesalahan jika yang
dimasukkan bukan merupakan karakter
66






To: [email protected]
Subject: T2PBD_NIM_Nama Lengkap
Contoh penulisan subject:
T2PBD_08410100001_Agung Firmansyah
Dikumpulkan paling lambat:
Hari Selasa, 7 September 2010 Pukul 24.00
67