Dbms_transaction dbmsutil.sql Dbms_utility dbmsutil.sql
Chapter 15 manipulation large objects
After completing this lesson, you should be able to do the following: ? Compare and contrast LONG and large object(LOB) datatypes ? Create and maintain LOB data types
? Differentiate between internal and external LOBS ? USE THE DBMS_LOB PL/SQL package
? Describe the use of temporary LOBs
1. BLOB represents a binary large object, such as a video clip. 2. CLOB represents a character large object.
3. NCLOB represents a multibyte character large object.
4. BFILE represents a binary file stored in an operating system binary file outside the database.
The BFILE column or attribute stores a file locator that points to the external file. 5. LOBs are characterized in two ways, according to their interpretation by the oracle server
(binary or character) and their storage aspect. LOBs can be stored internally(inside the database) or in host files. There are two categories of LOBs:
1) Internal LOBs(CLOB,NCLOB,BLOB) are stored in the database 2) External files(BFILE) are stored outside the database
Loading BFILES(讀取照片過程)
CREATE OR REPLACE PROCEDURE load_emp_bfile (p_file_loc in varchar2) is v_file BFILE;
v_filename VARCHAR2(16);
CURSOR emp_cursor IS SELECT ename FROM emp WHERE deportno =1 For UPDATE; BEGIN
FOR emp_record in emp_cursor LOOP
v_filename :=emp_record.ename ||’.bmp’; v_file:=bfilename(p_file_loc,v_filename); --if (dbms_lob.fileexists(v_file)=1) then dbms_lob.fileopen(v_file); end loop;
end load_emp_bfile.
另外，dbms_lob.fileexists(v_file)判斷文件存在
The DBMS_LOB Package
? Working with LOB often requires the use of the oracle-supplied package DBMS_LOB
? DBMS_LOB provides routines to access and manipulate internale and external LOBs. ? Oracle 9i enables retrievine LOB data directly using SQL, without using any special LOB API. ? In PL/SQL you can define a VARCHAR2 for a CLOB and a RAW for BLOB. THE DBMS_LOB PACKAGE(包中的函數) 1. APPEND 2. COPY 3. ERASE 4. LOADFROMFILE 5. TRIM 6. WRITE 7. GETLENGTH 8. INSTR 9. READ 10. SUBSTR 11. FILECLOSE 12. FILECLOSEALL 13. FILEEXISTS 14. FILEGETNAME 15. FILEISOPEN 16. FILEOPEN
Procedure read(
lobstr in bfile|BLOB|CLOB,
amount IN OUT BINARY_INTEGER, offset IN INTEGER,
buffer OUT RAW|VARCHAR2)
PROCEDURE WRITE(
amount IN OUT BINARY_INTEGER, offset IN INTEGER,
buffer OUT RAW|VARCHAR2)
Adding LOB Columns to a Table
Alter table employees ADD (RESUME CLOB, picture BLOB); 讀取CLOB字段： Declare Rlob clob;
Text varchar2(4001); Amt number :=4001; Offset number:=1;
Begin
Select resume into rlob from emp where employ_id =110; Dbms_lob.read(rlob,amt,offset,text); Dbms_output.put_line(‘text is: ’ || text); end;
創建臨時BLOB.
DBMS_LOB.CREATETEMPORARY(BLOB類型，TRUE OR FALSE DBMS_LOB.ISOPEN(BLOB類型)
DBMS_LOB.FREETEMPORARY(BLOB類型)
Chapter 16 creating database triggers
After completing this lesson, you should be able to do the following: ? Describe different type of triggers
? Describe database triggers and their use ? Create database triggers
? Describe database trigger firing rules ? Remove database triggers Guidelines for Designing Triggers ? Design trigger to :
? Perform related actions
? Centralize global operations
? Do not design triggers: 不能和其它功能或觸發器關沖突。
? Where functionality is already built into the oracle server
? That duplicate other triggers
? Create stored procedures and invoke them in a trigger, If the PL/SQL code is very lengthy. 如果觸發器需要代碼太多，則需要寫成過程，在觸發器中調用。
? The excessive use of triggers can result in complex interdependencies, which maybe difficult
to maintain in large applications. 不能過多的使用觸發器。
Creating DML Triggers
A triggering statement contains: ? Tringger timeing
? For table: BEFORE,AFTER ? For view: INSTEAD OF ? ? ? ?
Triggering event: INSERT,UPDATE, OR DELETE Table name: On table,view Trigger type: row or statement WHEN clause: Restricting condition
? Trigger body:PL/SQL block
Instead triggers 用來修改DML命令無法修改的視圖。
This type of trigger is used to provide a transparent way of modifying views that cannot be modified directly through SQL DML statements because the view is not inherently modifiable. You can write INSERT, UPDATE, and DELETEE statements against the view. The INSTEAD OF trigger works invisibly in the background performing the action code in the trigger body directly on the underlying tables.
當對一個視圖進行操作時，trigger在后臺修改對應的表。
Statement triggers and row triggers
Statement triggers 不依靠行的變化觸發。比如：對當前用戶的安全性檢查。
跟表的行插入，修改無關。可以是是在修改表之前的一個檢查。 Row triggers 沒有行變化事件進，觸發器不會被觸發。 ：old 代表事件之前的數據
：new 代表事件之后的數據
1. statement trigger:Create or replace trigger tri_name before INSERT ON tab_name 2. row trigger: create or replace trigger tri_name before INSERT ON tab_name
FOR EACH ROW WHEN(condition…)
Differentiating BETWEEN database triggers and stored procedures
Commit, rollback, savepoint 不能用在trigger中。Trigger不需明確調用。 Differences between a database trigger and a from builder trigger 170頁
Managing triggers
1. ALTER TRIGGER trig_name DISABLE| ENABLE
2. ALTER TABLE tab_name DISABLE| ENABLE ALL TRIGGERS 3. ALTER TRIGGER trig_name COMPILE. 4. DROP TRIGGER trig_name
Note: All triggers on a table are dropped when the table is dropped. 觸發器的測試 樣本：
員工表，審計表，包，過程，觸發器
1. 向員工表中添加數據時，觸發器1開始調用包中的過程函數，為包中的公有變量賦值。 2. 寫一過程對公有變量的值進行處理。
3. 在對審計表進行操作時，調用過程，把對應的公有變量值輸入。并將公有變量返回初始
值。
例： CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp
BEGIN
IF (TO_CHAR(SYSDATE,’DY’) IN (‘SAT’,’SUN’)) OR
(TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ’08:00’ AND 18:00) THEN
RAISE_APPLICATEION_ERROR(-20500,’You may insert into emp table only during busisness
hous.’) end if; end;（只有正常上班時間才能輸入表，其它時間輸入會報錯）
Chapter 17 more trigger concepts
After completing this lesson, you should be able to do the following: ? Create additional database triggers ? Explain the rules governing triggers
? Implement triggers
A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table. Triggering events that can cause a trigger to fire:
? A data definition statement on an object in the database or schema ? A specific user(or any user) logging on or off ? A database shutdown or startup ? A specific or any error that occurs
Creating Triggers on DDL statements
Create [OR REPLACE] TRIGGER trig_name Timing
[ddl_event1 [OR ddl_enent2 OR….]] ON {DATABASE|SCHEMA} Trigger_body
Creating trigger on System Events
CREATE [OR REPLACE] TRIGGER trig_name Timing
[database_enent1 [OR ddl_enent2 OR….]] ON {DATABASE|SCHEMA} Trigger_body
Create trigger syntax Database_event AFTER SERVERERRO AFTER LOGON BEFORE LOGOFF AFTER STARTUP BEFORE SHUTDOWN Possible values Cause the oracle server to fire the trigger whenever a server error message is logged Causes the oracle server to fire the trigger whenever a user logs on to the database Causes the oracle server to fire the trigger whenever a user logs off the database Causes the oracle server to fire the trigger whenever the database is opend Causes the oracle server to fire the trigger whenever the database is shut down