CARA MENGGUNAKAN SQL LOADER ORACLE DENGAN BAT FILE

 Ringkasan : dalam tutorial ini, Anda akan mempelajari cara menggunakan alat Oracle SQL*Loader untuk memuat dari file datar ke dalam tabel di database.

Pengantar alat SQL*Loader

SQL*Loader memungkinkan Anda memuat data dari file eksternal ke dalam tabel di database. Itu dapat mengurai banyak format file yang dibatasi seperti CSV, tab-delimited, dan pipe-delimited.

SQL*Loader menyediakan metode berikut untuk memuat data:

• Pemuatan jalur konvensional – membuat INSERTpernyataan dari konten file data input berdasarkan spesifikasi yang telah ditentukan sebelumnya dan menjalankan penyisipan.
• Beban jalur langsung – membuat blok data dalam format blok database Oracle dari file data dan langsung menulis blok data ke database. Cara ini jauh lebih cepat daripada jalur konvensional tetapi tunduk pada beberapa batasan.
• Beban tabel eksternal – buat tabel eksternal untuk data yang disimpan dalam file data dan jalankan INSERTpernyataan untuk memasukkan data dari file data ke tabel target. Beban tabel eksternal mendukung pemuatan paralel jika file data cukup besar.

Untuk menjalankan alat SQL*Load, Anda memerlukan setidaknya tiga file:

• File data input menyimpan data yang dibatasi atau mentah
• File parameter menyimpan lokasi file input/output
• File kontrol berisi spesifikasi tentang bagaimana data dimuat.

Setelah itu, Anda menjalankan perintah sqlldrdari baris perintah di Windows atau Terminal di GNU/Linux:

>sqlldr parfile=parameter_file.par
Bahasa kode:  SQL (Structured Query Language)  ( sql )

Gambar berikut mengilustrasikan proses SQL*Loader:

Mari kita ambil contoh penggunaan alat SQL*Load.

Contoh SQL*Loader

Kami akan memuat data email format file CSV ke dalam emailstabel di database.

Siapkan file input

Berikut isi dari  email.dat file tersebut:

1,john.doe@example.com
2,jane.doe@example.com
3,peter.doe@example.com
Bahasa kode:  CSS  ( css )

Isi dari file kontrol ( email.ctl) adalah sebagai berikut:

load data into table emails
insert
fields terminated by ","
(
email_id,
email
)
Bahasa kode:  JavaScript  ( javascript )

Dalam file kontrol:

• Perintah load data into table emails insertSQL*Loader untuk memuat data ke dalam tabel email menggunakan pernyataan INSERT.
• Menentukan fields terminated by "," (email_id,email)bahwa setiap baris dalam file memiliki dua kolom email_iddan emaildipisahkan oleh koma (,).

Berikut adalah isi dari file parameter ( email.par):

userid=ot@pdborc/Abcd1234
control=email.ctl
log=email.log
bad=email.bad
data=email.dat
direct=true
Bahasa kode:  JavaScript  ( javascript )

Dalam file parameter ini, kami menentukan pengguna ( userid) yang akan terhubung ke database Oracle, file kontrol ( email.ctl), file log ( email.log), dan file data ( email.dat).

File email.badmenyimpan data yang tidak valid. Dan baris terakhir direct=truemenginstruksikan SQL*Loader untuk menggunakan metode pemuatan jalur langsung.

Perhatikan bahwa tidak ada spasi antara parameter dan nilai, misalnya:

control=email.ctrl
Bahasa kode:  SQL (Structured Query Language)  ( sql )

Setelah memiliki tiga file, Anda dapat meletakkannya di direktori misalnya C:\loader.

Berikut ini tautan untuk mengunduh ketiga file dalam format file zip:

Unduh file data, kontrol, dan parameter

Muat data dari file datar ke dalam tabel

Pertama, buat tabel baru untuk menyimpan data email dari input datafile:

CREATE TABLE emails(
    email_id NUMBER PRIMARY KEY,
    email VARCHAR2(150) NOT NULL
);
Bahasa kode:  SQL (Structured Query Language)  ( sql )

Kedua, luncurkan program SQL*Loader dari direktori tempat Anda menyimpan file menggunakan sqlldrperintah:

C:\loader> sqlldr parfile=dept_loader.par
Bahasa kode:  SQL (Structured Query Language)  ( sql )

Berikut adalah outputnya:

Path used:      Direct

Load completed - logical record count 3.

Table EMAILS:
3 Rows successfully loaded.

Check the log file:
email.log
for more information about the load.
Bahasa kode:  CSS  ( css )

Ketiga, masuk ke Oracle dan verifikasi isi emailstabel:

SELECT * FROM emails;
Bahasa kode:  SQL (Structured Query Language)  ( sql )

Berikut adalah hasil yang ditetapkan:

Terakhir, tinjau file log:

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Jul 23 08:04:46 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   email.ctl
Data File:      email.dat
  Bad File:     email.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table EMAILS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL_ID                            FIRST     *   ,       CHARACTER            
EMAIL                                NEXT     *   ,       CHARACTER            

The following index(es) on table EMAILS were processed:
index OT.SYS_C0010446 loaded successfully with 3 keys

Table EMAILS:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        2
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Tue Jul 23 08:04:46 2019
Run ended on Tue Jul 23 08:04:48 2019

Elapsed time was:     00:00:01.47
CPU time was:         00:00:00.07
Bahasa kode:  JavaScript  ( javascript )

Dalam tutorial ini, Anda telah mempelajari cara menggunakan SQL*Loader untuk memuat data dari flat-file ke dalam tabel di database.

Read More

CARA MEMBUAT STORE PROCEDURE DI SQL SERVER

 

Pada artikel ini, kita akan belajar cara membuat STORE PROCEDURE di SQL Server dengan berbagai contoh.

STORE PROCEDURE SQL Server adalah kumpulan pernyataan yang dikelompokkan sebagai unit logis dan disimpan dalam database. STORE PROCEDURE menerima parameter dan mengeksekusi pernyataan T-SQL dalam prosedur, mengembalikan set hasil jika ada.

Untuk memahami perbedaan antara fungsi dan prosedur tersimpan di SQL Server, Anda dapat merujuk ke artikel ini, Fungsi vs prosedur tersimpan di SQL Server dan untuk mempelajari tentang prosedur tersimpan sebagian di SQL Server, klik Prosedur tersimpan sebagian di SQL Server .

Manfaat menggunakan Store procedure

Dapat dengan mudah dimodifikasi : Kita dapat dengan mudah memodifikasi kode di dalam stored procedure tanpa perlu me-restart atau menjalankan aplikasi. Misalnya, jika kueri T-SQL ditulis dalam aplikasi dan jika kita perlu mengubah logika, kita harus mengubah kode dalam aplikasi dan menerapkannya kembali. Prosedur SQL Server Stored menghilangkan tantangan tersebut dengan menyimpan kode dalam database. jadi, ketika kita ingin mengubah logika di dalam prosedur kita bisa melakukannya dengan pernyataan ALTER PROCEDURE sederhana.

Mengurangi lalu lintas jaringan: Saat kami menggunakan prosedur tersimpan alih-alih menulis kueri T-SQL di tingkat aplikasi, hanya nama prosedur yang dilewatkan melalui jaringan alih-alih seluruh kode T-SQL.

Dapat digunakan kembali : Prosedur tersimpan dapat dijalankan oleh banyak pengguna atau beberapa aplikasi klien tanpa perlu menulis kode lagi.

Keamanan: Prosedur tersimpan mengurangi ancaman dengan menghilangkan akses langsung ke tabel. kami juga dapat mengenkripsi prosedur tersimpan saat membuatnya sehingga kode sumber di dalam prosedur tersimpan tidak terlihat. Gunakan alat pihak ketiga seperti ApexSQL Decrypt untuk mendekripsi prosedur tersimpan terenkripsi.

Kinerja: Prosedur tersimpan SQL Server saat dijalankan untuk pertama kali membuat rencana dan menyimpannya di buffer pool sehingga rencana dapat digunakan kembali saat dijalankan di lain waktu.

Saya membuat tabel sampel yang akan digunakan dalam contoh di artikel ini.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE TABLE Product (ProductID INT, ProductName VARCHAR(100) ) GO   CREATE TABLE ProductDescription (ProductID INT, ProductDescription VARCHAR(800) ) GO   INSERT INTO Product VALUES (680,'HL Road Frame - Black, 58') ,(706,'HL Road Frame - Red, 58') ,(707,'Sport-100 Helmet, Red') GO   INSERT INTO ProductDescription VALUES (680,'Replacement mountain wheel for entry-level rider.') ,(706,'Sturdy alloy features a quick-release hub.') ,(707,'Aerodynamic rims for smooth riding.') GO

Membuat prosedur tersimpan sederhana

Kami akan membuat prosedur tersimpan sederhana yang menggabungkan dua tabel dan mengembalikan set hasil seperti yang ditunjukkan pada contoh berikut.

1 2 3 4 5 6 7 8 9 10

CREATE PROCEDURE GetProductDesc AS BEGIN SET NOCOUNT ON   SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID   END

Kita dapat menggunakan 'EXEC ProcedureName' untuk menjalankan prosedur tersimpan. Ketika kita menjalankan prosedur GetProductDesc, kumpulan hasil terlihat seperti di bawah ini.

Membuat prosedur tersimpan dengan parameter

Mari kita buat prosedur tersimpan SQL Server yang menerima parameter input dan memproses catatan berdasarkan parameter input.

Berikut adalah contoh prosedur tersimpan yang menerima parameter.

1 2 3 4 5 6 7 8 9 10 11 12

CREATE PROCEDURE GetProductDesc_withparameters (@PID INT) AS BEGIN SET NOCOUNT ON   SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID WHERE P.ProductID=@PID   END

1	EXEC GetProductDesc_withparameters 706

Saat menjalankan prosedur tersimpan, kita harus melewati parameter input. Silakan lihat gambar di bawah untuk set hasil.

Membuat prosedur tersimpan dengan nilai parameter default

Berikut adalah contoh prosedur tersimpan dengan nilai parameter default.

1 2 3 4 5 6 7 8 9 10 11 12

CREATE PROCEDURE GetProductDesc_withDefaultparameters (@PID INT =706) AS BEGIN SET NOCOUNT ON   SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID WHERE P.ProductID=@PID   END

Ketika kita menjalankan prosedur di atas tanpa melewati nilai parameter, nilai default 706 akan digunakan. Tetapi ketika dieksekusi melewati nilai, nilai default akan diabaikan dan nilai yang diteruskan akan dianggap sebagai parameter.

Membuat prosedur tersimpan dengan parameter keluaran

Di bawah ini adalah contoh prosedur tersimpan dengan parameter keluaran. Contoh berikut mengambil EmpID yang merupakan kolom identitas otomatis ketika karyawan baru dimasukkan.

1	CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))

1 2 3 4 5 6 7 8 9 10 11 12

CREATE PROCEDURE ins_NewEmp_with_outputparamaters (@Ename varchar(50), @EId int output) AS BEGIN SET NOCOUNT ON   INSERT INTO Employee (EmpName) VALUES (@Ename)   SELECT @EId= SCOPE_IDENTITY()   END

Menjalankan prosedur tersimpan dengan parameter keluaran sedikit berbeda. Kita harus mendeklarasikan variabel untuk menyimpan nilai yang dikembalikan oleh parameter output.

1 2 3 4 5

declare @EmpID INT   EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT   SELECT @EmpID

Membuat prosedur tersimpan terenkripsi

Kita dapat menyembunyikan kode sumber dalam prosedur tersimpan dengan membuat prosedur dengan opsi "ENKRIPSI".

Berikut adalah contoh prosedur tersimpan terenkripsi.

1 2 3 4 5 6 7 8

CREATE PROCEDURE GetEmployees WITH ENCRYPTION AS BEGIN SET NOCOUNT ON   SELECT EmpID,EmpName from Employee END

Ketika kami mencoba untuk melihat kode prosedur tersimpan SQL Server menggunakan sp_helptext, ia mengembalikan "Teks untuk objek 'GetEmployees' dienkripsi."

Saat Anda mencoba membuat skrip prosedur tersimpan terenkripsi dari studio manajemen SQL Server, kesalahan seperti di bawah ini muncul.

Membuat prosedur sementara

Seperti tabel sementara, kita juga dapat membuat prosedur sementara. Ada dua jenis prosedur sementara, satu adalah prosedur penyimpanan sementara lokal dan yang lainnya adalah prosedur sementara global.

Prosedur ini dibuat dalam database tempdb .

Prosedur tersimpan SQL Server lokal sementara : Ini dibuat dengan # sebagai awalan dan hanya dapat diakses di sesi tempat prosedur tersebut dibuat. Prosedur ini secara otomatis dibatalkan ketika koneksi ditutup.

Berikut ini adalah contoh membuat prosedur sementara lokal.

1 2 3 4 5

CREATE PROCEDURE #Temp AS BEGIN PRINT 'Local temp procedure' END

Prosedur tersimpan SQL Server sementara global: Prosedur ini dibuat dengan ## sebagai awalan dan dapat diakses di sesi lain juga. Prosedur ini secara otomatis terputus ketika koneksi yang digunakan untuk membuat prosedur ditutup.

Di bawah ini adalah contoh membuat prosedur sementara global.

1 2 3 4 5

CREATE PROCEDURE ##TEMP AS BEGIN PRINT 'Global temp procedure' END

Memodifikasi prosedur tersimpan

Gunakan pernyataan ALTER PROCEDURE untuk memodifikasi prosedur tersimpan yang ada. Berikut adalah contoh modifikasi prosedur yang ada.

1 2 3 4 5 6 7 8 9 10

ALTER PROCEDURE GetProductDesc AS BEGIN SET NOCOUNT ON   SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID   END

Mengganti nama prosedur tersimpan

Untuk mengganti nama prosedur tersimpan menggunakan T-SQL, gunakan prosedur tersimpan sistem sp_rename. Berikut adalah contoh yang mengganti nama prosedur "GetProductDesc" menjadi nama baru "GetProductDesc_new".

1	sp_rename 'GetProductDesc','GetProductDesc_new'

Kesimpulan

Pada artikel ini, kami menjelajahi prosedur tersimpan SQL Server dengan contoh yang berbeda

Read More