Stored Procedure dan User Defined Function
|
| Stored Procedure |
| |
Ketika anda mendevelop sebuah sistem database
yang berbasis client-server maupun n-tier, dapat dipastikan
bahwa anda banyak menggunakan transact-SQL. Hal ini merupakan
sesuatu yang wajar dikarenakan pemrograman transact-SQL
merupakan interface utama atara program aplikasi dengan SQL
Server. Ketika anda menggunakan transact SQL anda dapat
menggunakan dua metode untuk menyimpan dan meng-eksekusi
transact SQL. Metode yang pertama adalah transact SQL disimpan
pada program aplikasi, untuk mengeksekusi transact-SQL tersebut
maka program aplikasi akan mengirimkannya ke SQL Server, dan
setelah diproses oleh SQL Server maka hasilnya dikembalikan ke
aplikasi dalam bentuk recordset. Metode yang kedua adalah
transact SQL tersebut disimpan pada SQL Server dalam bentuk
stored procedure, untuk dapat mengesekusinya maka aplikasi dapat
memanggil nama dari stored procedure tersebut.
| Syntax Stored Procedure : |
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ {
@parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION |
RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
|
Argument
Procedure_name
Merupakan nama
procedure yang unik (tidak boleh ada nama stored procdure yang
sama dalam satu database).
Number
Ini akan
berfungsi jika anda ingin menghapus stored procedure yang
mempunyai nama sama pada group server yang berbeda.
@parameter
parameter berfungsi sebagai passing variable
antara stored procedure dengan pemanggilnya.
Data_type
Merupakan tipe data dari parameter
VERIYING
Hasil
pengembalian mendukung out parameter, hanya diaplikasikan untuk
parameter bertipe cursor.
OUTPUT
Mengindikasikan
bahwa parameter berupa return parameter.
{ RECOMPILE |
ENCRYPTION | RECOMPILE , ENCRYPTION }
RECOMPILE
mengindikasikan bahwa stored procedure akan selalu dibuat pada
run time
ENCRYPTION mengindikasikan bahwa stored
procedure tersebut di-encrypt
FOR REPLICATION
Mengindikasikan bahwa stored procedure hanya dapat dijalankan
pada replikasi dan tidak diijinkan dijalankan oleh subscriber.
Sql_statement
Merupakan pendeklarasian proses stored
procedure itu sendiri.
| Contoh 1 : |
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
Contoh cara pemanggilan stored procedure
EXECUTE au_info_all
--Atau
EXEC au_info_all
|
| Contoh Stored Procedure dengan Parameter |
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
Untuk menggunakan stored procedure au_info anda dapat
menggunakan dua cara seperti dibawah ini:
EXECUTE au_info 'Dull', 'Ann'
-- Atau
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
|
| Contoh Stored Procedure dengan Wildcard
Parameter |
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
Contoh pemanggilan stored procedure :
EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%' |
| Stored procedure menggunakan output
parameter |
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM
money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
Contoh cara pemanggilan :
DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
Hasil Proses:
Title Name
--------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
|
| Stored procedure menggunakan output cursor
parameter |
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING
OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles
OPEN @titles_cursor
GO
Contoh cara pemanggilan :
USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
|
| Stored procedure menggunakan encryption |
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
Jika anda memanggil stored procedure diatas dengan perintah
EXEC sp_helptext encrypt_this
Maka akan menghasilkan
The object's comments have been encrypted.
Untuk memanggil encrypted storeg procedure tersebut gunakan
perintah seperti dibawah ini
SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'encrypt_this'
Hasil proses :
id text
----------
---------------------------------------------------1413580074
?????????????????????????????????e????????????????????????????
(1 row(s) affected) |
|
| |
|
|
User Defined Function |
| |
User define fuction merupakan fasilitas baru
yang disediakan oleh Microsoft SQL Server 2000, dan fasilitas
ini belum ada pada SQL Server versi sebelumnya.
User define function merupakan subroutine yang digunakan untuk
membuat transact-SQL, dimana kode user define function tersebut
berbentuk encapsulation code, dan kode tersebut dapat digunakan
digunakan untuk membangun transact-SQL lain (reusable). User
define function ini berfungsi hampir sama dengan stored
procedure. Perbedaannya adalah jika stored procedure tidak bisa
digunakan untuk menyimpan hasil proses, sedangkan pada user
define function hasil dari proses dapat dikembalikan berupa
sebuah nilai atau table. Jika anda sudah familiar dengan bahasa
pemrograman Visual Basic, maka perbedaan user define function
dan stored procedure mirip seperti perbedaan antara Function dan
Sub.
User define function dibuat dengan perintah CREATE FUNCTION,
dimodifikasi dengan ALTER FUNCTION atau di hapus dengan perintah
DROP FUNCTION.
Tipe User Define Function
User define function dalam SQL Server 2000 terbagi menjadi dua
tipe yaitu:
1. Scalar function
2 Table valued function
Scalar Function
Scalar function adalah sebuah user define function yang
mengembalikan sebuah nilai dengan satu tipe data scalar. Semua
tipe data yang ada pada SQL Server 2000 dapat digunakan untuk
menampung hasil pengembalian proses, kecuali tipe data text,
ntext, image, timestamp dan cursor. Bagian dari function ini
harus berada dalam blok BEGIN…END. Berikut ini contoh membuat
scalar function yang mengembalikan nilai dengan tipe data
decimal.
| Syntax Scalar Function : |
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ =
default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,] ...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END |
| Syntax Scalar Function : |
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
Function diatas dapat digunakan pada semua ekspresi yang
mempunyai tipe data desimal.
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
) |
Table Value Function
Jika Scalar function dapat mengembalikan sebuah nilai dengan tipe data
tertentu, maka table value function dapat mengembalikan nilai berupa
table. Table valued function ini dibedakan menjadi dua jenis yaitu
Inline table valued function dan multistatement table valued function.
Inline valued function dapat digunakan jika hasil yang akan dikembalikan
merupakan hasil dari perintah SELECT. Bagian dari deklarasi inline
function tidak didalam blok BEGIN…END. Fungsi Inline table valued
function ini mirip seperti view, perbedaanya adalah jika pada view tidak
bisa menggunakan parameter, sedangkan pada function diijinkan
mengggunaan parameter. Untuk lebih jelasnya mengenai perbedaan view dan
inline table value function dapat anda lihat pada pada contoh dibawah
ini.
| Syntax Inline Table-valued Functions : |
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ =
default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ] |
| Syntax Multi-statement Table-valued
Functions |
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ =
default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
< function_option > ::=
{ ENCRYPTION | SCHEMABINDING }
< table_type_definition > :: =
( { column_definition | table_constraint } [ ,...n ] ) |
| Contoh Perbedaan View dan Inline Table
Valued Function |
--Contoh View
CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'
--Contoh Function
CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
GO
-- contoh perbrdaan pemanggilan view dan function
SELECT * FROM vw_CustomerNamesInWA
SELECT *
FROM fn_CustomerNamesInRegion('WA')
GO |
Dari contoh diatas dapat disimpulkan bahwa penggunaan user define
function lebih powerful dibandingkan dengan view.
Jika pada Inline function hanya mengembalikan hasil proses SELECT
menjadi sebuah table, maka pada multistatement table valued function
dapat mengembalikan data berupa table sedangkan isi dari table tersebut
dihasilkan dari perintah INSERT.
| Contoh Multistatement Tabled Valued
Function |
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
-- Contoh pemanggilan function :
SELECT *
FROM LargeOrderShippers( $500 )
------------------------------------------------------------------------------
CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report
to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO
-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO |
|
| |
|
|
Eko Heri Susanto
Patria Computer
Jl.Cikini IV No 18
Jakarta Pusat
Jakarta-Indonesia, 10330
Phone/Fax : +62 21-3910750
HP : +62 815 11254389 |
|
|