تفاوت SQLj و PL/SQL

دید ما در SQLj این است که در اصل جاوا می‌نویسیم، بعد برنامه جاوایی‌مون رو به عنوان procedure یا function به دیتابیس معرفی می‌کنیم. خب با زبانی مانند جاوا دست شما بسیار بازتر از PL/SQL است.

در حقیقت می‌توان تو سطح خود دیتابیس اوراکل میشه procedure یا function نوشت که از spring ioc استفاده کند. پس می‌توان از تمام کتابخانه‌ها، فریم‌ورک‌ها، ابزارهای جاوا در داخل اوراکل استفاده کرد.

حتی می‌توان تو سطح خود اوراکل RMI سرور راه‌اندازی کرد و از بیرون innovaction رو انجام داد و اصلاً از JDBC استفاده نکرد. فقط مدل کانکشن شما عوض میشه و دیگه به صورت jdbc:oracle:thin:@localhost:1521 نیست و باید به صورت Internal Connection به دیتابیس وصل شوید. (jdbc:default)

بعد زیر ساخت خود اوراکل هم SQL-j است یعنی خیلی از functionهای رمزنگاری و Hashingای که خود اوراکل استفاده می‌کنه SQL-j است.

باید توجه داشته باشید که در خیلی از پروژه‌ها PL/SQL راهکار مناسبی ارائه نمی‌دهد و حتماً باید از SQLj استفاده کرد:

مثال راه‌اندازی کلاستر دستی (نه خود دیتابیس اوراکل رو کلاستر کنید) و load balancer ایجاد کنید نمی‌توانید از PL/SQL استفاده کنید و حتماً باید از SQL-j استفاده کنید. 

همچنین به عنوان مثال می‌توان از Hibernate در خود دیتابیس استفاده کرد.

همچنین در DB2 ما Store Procedure و SQL-j را داریم و جالبه بدونید SQL-j محصول شرکت IBM است.

نحوه ایجاد یک database جدید

به صورت پیش‌فرض با نصب و پیکربندی اوراکل با استفاده از محیط نصاب همانند آموزش زیر:

http://mghaffari.blog.ir/post/72

نصاب برای ما یک database پیش‌فرض به نام ORCL ایجاد می‌کند ولی ممکنه سیستم ما آنقدر enterprise باشه که بخواهیم یک repository دیگر هم به سیستم اضافه کنیم.

نکته: با داشتن 2 دیتابیس حافظه RAM شما به شدت اشغال خواهد شد.

در ویندوز به مسیر زیر مراجعه می‌کنیم:

Satrt: All Programs: Oracle - OraDb11g_home1: Configuration and Migration Tools

برنامه Database Configuration Assistant را اجرا می‌کنیم

و یا در run عبارت dbca را نوشته و OK می‌کنیم. همچنین در لینوکس نیز می‌توانید با اجرای dbca در شل برنامه را بالا بیاورید.

با اجرای این برنامه به گزینه‌های زیر بر می‌خورید:

Create Database = ایجاد یک دیتابیس جدید
Configure Database Options = پیکربندی اجزای یک دیتابیس موجود
Delete Database = پاک‌کردن یک دیتابیس به همراه تمام متعلقات آن
Manage Templates = مدیریت و یا ساخت یک قالب
Manage Pluggable Databases = ساخت، پاک‌کردن یا خارج کردن یک دیتابیس از حالت pluggable

بعد از انتخاب Create Database بر روی گزینه‌ی Advanced Mode کلیک می‌کنیم:

قاعده نامگذاری sequence

بهتر است از قاعده نامگذاری به شکل زیر برای sequence استفاده کنیم:

T1_SEQ

اسم جدول که قراره روش sequence باشه + کلمه کلیدی SEQ 

استفاده از یک sequence در چند جدول

نکته: توی distirbuted table می‌توانیم یک sequence‌ مشترک داشته باشیم.

به مثال زیر توجه کنید:

SQL> create table t2(id number, name varchar2(30));
Table created.

SQL> insert into t2(id, name) values(s1.nextval, 'ss');
1 row created.

SQL> select * from t2;

ID         NAME
---------- ------------------------------
 11        ss

در این مثال از sequence ساخته شده در مثال قبل برای یک جدول دیگر استفاده کرده‌ایم. از این خاصیت می‌توان در خیلی جاها که نیاز به ادامه دادن تراکنش‌ها از یک جدول در یک جدول دیگر داریم استفاده کنیم.

استفاده از Sequence در Insert

به مثال زیر دقت کنید:

SQL> select * from t1;

ID         NAME
---------- --------------------
 1         mohammad
 3         mohsen

SQL> insert into t1 values (s1.nextval, 'javad');
1 row created.

SQL> select * from t1;

ID         NAME
---------- --------------------
 8         javad
 1         mohammad
 3         mohsen

SQL> insert into t1 values (s1.nextval, 'javadssssssssssssssssssssssssssssssssss
sssssssssssssssssssss');
insert into t1 values (s1.nextval, 'javadsssssssssssssssssssssssssssssssssssssss
ssssssssssssssss')
 *
ERROR at line 1:
ORA-12899: value too large for column "MAHDI"."T1"."NAME" (actual: 60, maximum:
20)

SQL> insert into t1 values (s1.nextval, 'mahdi');
1 row created.

SQL> select * from t1;

ID         NAME
---------- --------------------
 8         javad
 10        mahdi
 1         mohammad
 3         mohsen

در مثال بالا به این مسئله پی بردیم که چون sequence به صورت transactional نیست ما id شماره ۹ را به خاطر faile شدن تراکنش از دست داده‌ایم.

در حقیقت sequence جلو می‌رود بدون توجه به اینکه تراکنش انجام می‌شود یا نه پس همیشه حرکت رو به جلو رو داره.

Sequence چیست؟

sequence یک شمارنده در اوراکل است که همیشه در حال شمارش است. به مثال زیر توجه کنید:

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 1

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 2

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 3

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 4

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 5

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 6

نکته: SEQUNCE به صورت TRANSACTIONAL نیست.

SQL> ROLLBACK;
Rollback complete.

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 7

پاک‌کردن محفظه سطل‌بازیابی

برای اینکار از دستور زیر استفاده کنید:

SQL> purge table mahdi.t9;
Table purged.

همچنین برای پاک کردن کل محفظه سطل بازیابی می‌توانید از دستور زیر استفاده کنید:

SQL> PURGE recyclebin;
Recyclebin purged.

نکته: دقت کنید که اگر purge کنید دیگر اطلاعات قابل بازیابی نیست.

برگرداندن آبجکت از سطل بازیابی با flashback

برای اینکار به مثال زیر توجه کنید:

SQL> flashback table MAHDI.t9 to before drop;
Flashback complete.

SQL> describ t9;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 ID                                                  NUMBER
 NAME                                               VARCHAR2(20)
 FAMILY                                             VARCHAR2(20)

سطل بازیابی در اوراکل (dba_recyclebin)

به مثال زیر توجه کنید:

SQL> CONNECT MAHDI;
Connected
Connection created by CONNECT script command disconnected
SQL> DESCRI T9;
DESCRI T9
ERROR: 
------------------------------- 
ERROR: object T9 does not exist

SQL> CREATE TABLE T9(ID NUMBER, NAME VARCHAR2(20), FAMILY VARCHAR2(20));
table T9 created.
SQL> DROP TABLE T9;
table T9 dropped.
SQL> SELECT object_name ,droptime ,original_name ,owner from dba_recyclebin ;
OBJECT_NAME                                        DROPTIME            ORIGINAL_NAME           OWNER
-------------------------------------------------- ------------------- ----------------------- ----------
BIN$uPBAMAcxSEKbICnDjzXV1Q==$0                     2015-05-15:09:46:16 T9                      MAHDI 
BIN$d9S76pNMSmiI/fISqrthNQ==$0                     2015-05-15:09:51:55 T9                      MAHDI

OBJECT_NAME = شماره هر آبجکت چون امکان داره شما چندتا جدول با اسم‌های یکسان drop کرده باشید و این فیلد به ما تفاوت هر آبجکت رو نشون میده.

DROPTIME = زمان حذف آبجکت

ORIGINAL_NAME = اسم اصلی آبجکت

OWNER = مالک آبجکت

ساخت Flashback برای مدت ۲ سال

با استفاده از دستور زیر یک Flashback دو ساله ساخته می‌شود و در Tablespace TBS2 نگهداری می‌شود.

CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;

نکته: حتما توجه داشته باشید که Tablespace موردنظر را در اوراکل از قبل ایجاد کرده باشید.

برای تغییر Flashback به دوسال می‌توانید از دستور زیر استفاده کنید:

ALTER FLASHBACK ARCHIVE fla2 MODIFY RETENTION 2 YEAR;