Transcript Pert 3 – FUNCTION

FUNCTION
1
Siti Mukaromah,S.Kom, MCP.



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.
2

Fungsi SQL dibangun ke dalam Oracle Database
dan tersedia untuk digunakan dalam berbagai
statement SQL yang sesuai. Jangan bingung
dengan fungsi-fungsi user-defined SQL yang
ditulis dalam PL/SQL.
3

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.
4
SINTAKS MENUNJUKKAN KATEGORI DARI
FUNGSI SEBAGAI BERIKUT:
Function
Single Row Function
5

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.
6
SINGLE ROW FUNCTION

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.
7
NUMERIC FUNCTIONS

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.
8
FUNGSI NUMERIK ADALAH:
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_BUC
KET

9
CHARACTER FUNCTIONS RETURNING
CHARACTER VALUES
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.
10
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.
11
FUNGSI KARAKTER YANG MENGEMBALIKAN
NILAI KARAKTER ADALAH:
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

12
NLS CHARACTER FUNCTIONS

Fungsi karakter NLS mengembalikan informasi
tentang karakter set. Fungsi Karakter NLS
adalah:
NLS_CHARSET_DECL_LEN
 NLS_CHARSET_ID
 NLS_CHARSET_NAME

13
CHARACTER FUNCTIONS RETURNING
NUMBER VALUES


Karakter fungsi yang mengembalikan nilai
angka (number) dapat mengambil sebagai
argumen mereka setiap datatype karakter.
Fungsi karakter yang mengembalikan nilai-nilai
angka (number) :
ASCII
 INSTR
 LENGTH
 REGEXP_INSTR

14
DATETIME FUNCTIONS



Fungsi datetime (Datetime Functions) beroperasi pada nilainilai 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.
15
FUNGSI DATETIME ADALAH:














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
16
GENERAL COMPARISON FUNCTIONS
Fungsi perbandingan umum yang menentukan
nilai terbesar dan atau paling kecil dari satu set
nilai.
Fungsi perbandingan umum adalah:
 GREATEST
 LEAST
17
CONVERSION FUNCTIONS

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.
18
FUNGSI KONVERSI SQL ADALAH:















ASCIISTR
BIN_TO_NUM

CAST

CHARTOROWID
COMPOSE

CONVERT
DECOMPOSE

HEXTORAW
NUMTODSINTERV 
AL
NUMTOYMINTERV 
AL

RAWTOHEX

RAWTONHEX

ROWIDTOCHAR

ROWIDTONCHAR 
SCN_TO_TIMESTA 
MP
TIMESTAMP_TO_S
CN
TO_BINARY_DOUB
LE
TO_BINARY_FLOA
T
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_T
Z
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ...
USING
UNISTR
19
LARGE OBJECT FUNCTIONS
Large Object Functions beroperasi pada LOBs.
Fungsi Large Object Functions adalah:
 BFILENAME
 EMPTY_BLOB, EMPTY_CLOB
20
COLLECTION FUNCTIONS
Collection Functions beroperasi pada tabel nested
dan varrays.
Fungsi SQLCollection Functions adalah:
 CARDINALITY
 COLLECT
 POWERMULTISET
 POWERMULTISET_BY_CARDINALITY
 SET
21
HIERARCHICAL FUNCTION


Hierarchical Function menerapkan informasi
jalur hirarkis pada set hasil.
SYS_CONNECT_BY_PATH
22
DATA MINING FUNCTIONS
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
23
XML FUNCTIONS

Fungsi XML beroperasi pada dokumen atau
fragmen XML .
24
XML SQL FUNGSI:















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
25
ENCODING AND DECODING FUNCTIONS

fungsi encoding dan decoding memungkinkan
Anda memeriksa dan decode (membaca kode)
data dalam database.
DECODE
 DUMP
 ORA_HASH
 VSIZE

26
NULL-RELATED FUNCTIONS
NULL-Related Functions memfasilitasi
penanganan null
NULL-Related Functions adalah:
 COALESCE
 LNNVL
 NULLIF
 NVL
 NVL2
27
ENVIRONMENT AND IDENTIFIER
FUNCTIONS

Lingkungan dan fungsi pengenal memberikan
informasi tentang instance dan sesi.
SYS_CONTEXT
 SYS_GUID
 SYS_TYPEID
 UID
 USER
 USERENV

28
AGGREGATE FUNCTIONS

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.
29

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.
30
MODEL FUNCTIONS
Model Functions dapat digunakan hanya dalam
model_clause dari SELECT.
Fungsi model:
 CV
 ITERATION_NUMBER
 PRESENTNNV
 PRESENTV
 PREVIOUS
31
ORACLE/PLSQL: CREATING FUNCTIONS
Di Oracle Anda dapat membuat fungsi sendiri
 Syntax untuk membuat Function:

32
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.

33
FUNCTIONS WITHOUT PARAMETERS

Simple Function Creation
34
35
FUNCTION WITHOUT PARAMETERS USED
IN A SELECT CLAUSE
36
37
SIMPLE FUNCTION USED IN AN INSERT
STATEMENT
38
SIMPLE FUNCTION USED IN A WHERE
CLAUSE
39
SIMPLE FUNCTION USED IN A VIEW
40
FUNCTIONS WITH PARAMETERS

Fungsi untuk menentukan apakah sebuah string
memiliki format nomor social security yang valid
41
42
43
FUNCTION WITH OUT PARAMETER
44
FUNCTION WITH IN OUT PARAMETER
45