Oracle 數(shù)據(jù)對(duì)象分析

2024-08-29 13:36:16

字體：大中小

供稿：網(wǎng)友

　　過(guò)程和函數(shù)
　　
　　過(guò)程和函數(shù)都以編譯后的形式存放在數(shù)據(jù)庫(kù)中，函數(shù)可以沒(méi)有參數(shù)也可以有多個(gè)參數(shù)并有一個(gè)返回值。過(guò)程有零個(gè)或多個(gè)參數(shù)，沒(méi)有返回值。函數(shù)和過(guò)程都可以通過(guò)參數(shù)列表接收或返回零個(gè)或多個(gè)值，函數(shù)和過(guò)程的主要區(qū)別不在于返回值，而在于他們的調(diào)用方式。過(guò)程是作為一個(gè)獨(dú)立執(zhí)行語(yǔ)句調(diào)用的：
　　
　　pay_involume(invoice_nbr,30,due_date);
　　
　　函數(shù)以合法的表達(dá)式的方式調(diào)用：
　　
　　order_volumn:=open_orders(SYSDATE,30);
　　
　　創(chuàng)建過(guò)程的語(yǔ)法如下：
　　
　　CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
　　[parameter_lister]
　　{ASIS}
　　declaration_section
　　BEGIN
　　executable_section
　　[EXCEPTION
　　exception_section]
　　END [procedure_name]
　　
　　每個(gè)參數(shù)的語(yǔ)法如下：
　　
　　paramter_name mode datatype [(:=DEFAULT) value]
　　
　　mode有三種形式：IN、OUT、INOUT。
　　
　　IN表示在調(diào)用過(guò)程的時(shí)候，實(shí)際參數(shù)的取值被傳遞給該過(guò)程，形式參數(shù)被認(rèn)為是只讀的，當(dāng)過(guò)程結(jié)束時(shí)，控制會(huì)返回控制環(huán)境，實(shí)際參數(shù)的值不會(huì)改變。
　　
　　OUT在調(diào)用過(guò)程時(shí)實(shí)際參數(shù)的取值都將被忽略，在過(guò)程內(nèi)部形式參數(shù)只能是被賦值，而不能從中讀取數(shù)據(jù)，在過(guò)程結(jié)束后形式參數(shù)的內(nèi)容將被賦予實(shí)際參數(shù)。
　　
　　INOUT這種模式是IN和OUT的組合；在過(guò)程內(nèi)部實(shí)際參數(shù)的值會(huì)傳遞給形式參數(shù)，形勢(shì)參數(shù)的值可讀也可寫(xiě)，過(guò)程結(jié)束后，形勢(shì)參數(shù)的值將賦予實(shí)際參數(shù)。
　　
　　創(chuàng)建函數(shù)的語(yǔ)法和過(guò)程的語(yǔ)法基本相同，唯一的區(qū)別在于函數(shù)有RETUREN子句
　　
　　CREATE [ OR REPLACE] FINCTION [schema.]function_name
　　[parameter_list]
　　RETURN returning_datatype
　　{ASIS}
　　declaration_section
　　BEGIN
　　executable_section
　　[EXCEPTION]
　　exception_section
　　END [procedure_name]
　　
　　在執(zhí)行部分函數(shù)必須有喲個(gè)或多個(gè)return語(yǔ)句。
　　
　　在創(chuàng)建函數(shù)中可以調(diào)用單行函數(shù)和組函數(shù)，例如：
　　
　　CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)
　　RETURN NUMBER
　　IS
　　pi NUMBER=ACOS(-1);
　　RadiansPerDegree NUMBER;
　　
　　BEGIN
　　RadiansPerDegree=pi/180;
　　RETURN(SIN(DegreesIn*RadiansPerDegree));
　　END
　　包
　　
　　包是一種將過(guò)程、函數(shù)和數(shù)據(jù)結(jié)構(gòu)捆綁在一起的容器；包由兩個(gè)部分組成：外部可視包規(guī)范，包括函數(shù)頭，過(guò)程頭，和外部可視數(shù)據(jù)結(jié)構(gòu)；另一部分是包主體(package body),包主體包含了所有被捆綁的過(guò)程和函數(shù)的聲明、執(zhí)行、異常處理部分。
　　
　　打包的PL/SQL程序和沒(méi)有打包的有很大的差異，包數(shù)據(jù)在用戶的整個(gè)會(huì)話期間都一直存在，當(dāng)用戶獲得包的執(zhí)行授權(quán)時(shí)，就等于獲得包規(guī)范中的所有程序和數(shù)據(jù)結(jié)構(gòu)的權(quán)限。但不能只對(duì)包中的某一個(gè)函數(shù)或過(guò)程進(jìn)行授權(quán)。包可以重載過(guò)程和函數(shù)，在包內(nèi)可以用同一個(gè)名字聲明多個(gè)程序，在運(yùn)行時(shí)根據(jù)參數(shù)的數(shù)目和數(shù)據(jù)類(lèi)型調(diào)用正確的程序。
　　
　　創(chuàng)建包必須首先創(chuàng)建包規(guī)范，創(chuàng)建包規(guī)范的語(yǔ)法如下：
　　
　　CREATE [OR REPLACE] PACKAGE package_name
　　{ASIS}
　　public_variable_declarations
　　public_type_declarations
　　public_exception_declarations
　　public_cursor_declarations
　　function_declarations
　　procedure_specifications
　　END [package_name]
　　
　　創(chuàng)建包主體使用CREATE PACKAGE BODY語(yǔ)句：
　　
　　CREATE [OR REPLACE] PACKAGE BODY package_name
　　{ASIS}
　　private_variable_declarations
　　private_type_declarations
　　private_exception_declarations
　　private_cursor_declarations
　　function_declarations
　　procedure_specifications
　　END [package_name]
　　
　　私有數(shù)據(jù)結(jié)構(gòu)是那些在包主體內(nèi)部，對(duì)被調(diào)用程序而言是不可見(jiàn)的。
　　
　　觸發(fā)器(Triggers)
　　
　　觸發(fā)器是一種自動(dòng)執(zhí)行響應(yīng)數(shù)據(jù)庫(kù)變化的程序。可以設(shè)置為在觸發(fā)器事件之前或之后觸發(fā)或執(zhí)行。
能夠觸發(fā)觸發(fā)器事件的事件包括下面幾種：
　　
　　DML事件
　　DDL事件
　　數(shù)據(jù)庫(kù)事件
　　
　　DML事件觸發(fā)器可以是語(yǔ)句或行級(jí)觸發(fā)器。DML語(yǔ)句觸發(fā)器在觸發(fā)語(yǔ)句之前或之后觸發(fā)DML行級(jí)觸發(fā)器在語(yǔ)句影響的行變化之前或之后觸發(fā)。用戶可以給單一事件和類(lèi)型定義多個(gè)觸發(fā)器，但沒(méi)有任何方法可以增強(qiáng)多觸發(fā)器觸發(fā)的命令。下表列出了用戶可以利用的觸發(fā)器事件：
　　
　　事件　　觸發(fā)器描述
　　INSERT　當(dāng)向表或視圖插入一行時(shí)觸發(fā)觸發(fā)器
　　UPDATE　更新表或視圖中的某一行時(shí)觸發(fā)觸發(fā)器
　　DELETE　從表或視圖中刪除某一行時(shí)觸發(fā)觸發(fā)器
　　CREATE　當(dāng)使用CREATE語(yǔ)句為數(shù)據(jù)庫(kù)或項(xiàng)目增加一個(gè)對(duì)象時(shí)觸發(fā)觸發(fā)器
　　ALTER　　當(dāng)使用ALTER語(yǔ)句為更改一個(gè)數(shù)據(jù)庫(kù)或項(xiàng)目的對(duì)象時(shí)觸發(fā)觸發(fā)器
　　DROP　　當(dāng)使用DROP語(yǔ)句刪除一個(gè)數(shù)據(jù)庫(kù)或項(xiàng)目的對(duì)象時(shí)觸發(fā)觸發(fā)器
　　START　　打開(kāi)數(shù)據(jù)庫(kù)時(shí)觸發(fā)觸發(fā)器，在事件后觸發(fā)
　　SHUTDOWN 關(guān)閉數(shù)據(jù)庫(kù)時(shí)觸發(fā)，事件前觸發(fā)
　　LOGON　　當(dāng)一個(gè)會(huì)話建立時(shí)觸發(fā)，事件前觸發(fā)
　　LOGOFF　當(dāng)關(guān)閉會(huì)話時(shí)觸發(fā)，事件前觸發(fā)
　　SERVER　服務(wù)器錯(cuò)誤發(fā)生時(shí)觸發(fā)觸發(fā)器，事件后觸發(fā)
　　
　　創(chuàng)建觸發(fā)器的語(yǔ)法如下：
　　
　　CREATE [OR REPLACE] TRIGGER trigger_name
　　{beforeafterinstead of} event
　　ON {table_or_view_nameDATABASE}
　　[FOR EACH ROW[WHEN condition]]
　　trigger_body
　　
　　只有DML觸發(fā)器（INSERT、UPDATE、DELETE)語(yǔ)句可以使用INSTEAD OF觸發(fā)器并且只有表的DML觸發(fā)器可以是BEFORE或AFTER觸發(fā)器。
　　
　　象約束一樣觸發(fā)器可以被設(shè)置為禁用或啟用來(lái)關(guān)閉或打開(kāi)他們的執(zhí)行體(EXECUTE),將觸發(fā)器設(shè)置為禁用或啟用使用ALTER TRIGGER語(yǔ)句：
　　
　　ALTER TRIGGER trigger_name ENABLE;
　　ALTER TRIGGER trigger_name DISABLE;
　　
　　要禁用或啟用表的所有觸發(fā)器，使用ALTER TABLE語(yǔ)句
　　
　　ALTER TRIGGER table_name DISABLE ALL TRIGGER;
　　ALTER TRIGGER table_name ENABLE ALL TRIGGER;
　　
　　刪除觸發(fā)器使用DROP TRIGGER
　　
　　DROP TRIGGER trigger_name;
　　
　　數(shù)據(jù)字典
　　
　　Oracle數(shù)據(jù)字典包含了用戶數(shù)據(jù)庫(kù)的元數(shù)據(jù)。帶下劃線的表名稱中帶OBJ$、UET$、SOURCE$，這些表是在執(zhí)行CREATE DATABASE語(yǔ)句期間由sql.bsq腳本創(chuàng)建的，一般情況下用戶很少訪問(wèn)這些表。腳本catalog.sql（通常位于$oracle_home/rdbms/admin）在CREATE DATABASE語(yǔ)句之后立即運(yùn)行，創(chuàng)建數(shù)據(jù)字典視圖。
　　
　　數(shù)據(jù)字典視圖大致可以分為三類(lèi)：
　　
　　.前綴為USER_的數(shù)據(jù)字典視圖，包含了用戶擁有的對(duì)象的信息。
　　
　　.前綴為ALL_的數(shù)據(jù)字典視圖，包含了用戶當(dāng)前可以訪問(wèn)的全部對(duì)象和權(quán)限的信息。
　　
　　.前綴為DBA_的數(shù)據(jù)字典視圖，包含了數(shù)據(jù)庫(kù)擁有的所有對(duì)象和權(quán)限的信息。
　　
　　在絕大多數(shù)數(shù)據(jù)字典視圖中都有象DBA_TABLES,ALL_TABLES和USER_TABLES這樣的視圖家族。Oracle中有超過(guò)100個(gè)視圖家族，所以要全面介紹這些視圖家族是單調(diào)乏味的而且沒(méi)有多大的意義。在下表中列出了最重要和最常用的視圖家族，需要注重的是每個(gè)視圖家族都有一個(gè)DBA_,一個(gè)ALL_一個(gè)USER_視圖。
　　
　　視圖家族(View Family)　描述
　　COL_PRIVS　　　　　　　包含了表的列權(quán)限，包括授予者、被授予者和權(quán)限
　　EXTENTS　　　　　　　　數(shù)據(jù)范圍信息，比如數(shù)據(jù)文件，數(shù)據(jù)段名(segment_name)和大小
　　INDEXES　　　　　　　　索引信息，比如類(lèi)型、唯一性和被涉及的表
　　IND_COLUMNS　　　　　　索引列信息，比如索引上的列的排序方式
　　OBJECTS　　　　　　　　對(duì)象信息，比如狀態(tài)和DDL time
　　ROLE_PRIVS　　　　　　　角色權(quán)限，比如GRANT和ADMIN選項(xiàng)
　　SEGMENTS　　　　　　　　表和索引的數(shù)據(jù)段信息，比如tablespace和storage
　　SEQUECNCES　　　　　　　序列信息，比如序列的cache、cycle和ast_number
　　SOURCE　　　　　　　　　除觸發(fā)器之外的所有內(nèi)置過(guò)程、函數(shù)、包的源代碼
　　SYNONYMS　　　　　　　　別名信息，比如引用的對(duì)象和數(shù)據(jù)庫(kù)鏈接db_link
　　SYS_PRIVS　　　　　　　系統(tǒng)權(quán)限，比如grantee、privilege、admin選項(xiàng)
　　TAB_COLUMNS　　　　　　表和視圖的列信息，包括列的數(shù)據(jù)類(lèi)型
　　TAB_PRIVS　　　　　　　表權(quán)限，比如授予者、被授予者和權(quán)限
　　TABLES　　　　　　　　　表信息，比如表空間(tablespace),存儲(chǔ)參數(shù)(storage parms)和數(shù)據(jù)行的數(shù)量
　　TRIGGERS　　　　　　　　觸發(fā)器信息，比如類(lèi)型、事件、觸發(fā)體(trigger body)
　　USERS　　　　　　　　　用戶信息，比如臨時(shí)的和缺省的表空間
　　VIEWS　　　　　　　　　視圖信息，包括視圖定義
　　
　　在Oracle中還有一些不常用的數(shù)據(jù)字典表，但這些表不是真正的字典家族，他們都是一些重要的單一的視圖。

　　
　　VIEW NAME　　　　　　描述
　　USER_COL_PRIVS_MADE　用戶授予他人的列權(quán)限
　　USER_COL_PRIVS_RECD　用戶獲得的列權(quán)限
　　USER_TAB_PRIVS_MADE　用戶授予他人的表權(quán)限
　　USER_TAB_PRIVS_RECD　用戶獲得的表權(quán)限
　　
　　其他的字典視圖中主要的是V$視圖，之所以這樣叫是因?yàn)樗麄兌际且訴$或GV$開(kāi)頭的。V$視圖是基于X$虛擬視圖的。V$視圖是SYS用戶所擁有的，在缺省狀況下，只有SYS用戶和擁有DBA系統(tǒng)權(quán)限的用戶可以看到所有的視圖，沒(méi)有DBA權(quán)限的用戶可以看到USER_和ALL_視圖，但不能看到DBA_視圖。與DBA_,ALL,和USER_視圖中面向數(shù)據(jù)庫(kù)信息相反，這些視圖可視的給出了面向?qū)嵗男畔ⅰ?br />　　
　　在大型系統(tǒng)上化幾周時(shí)間手工輸入每一條語(yǔ)句
　　
　　手工輸入帶用戶名變量的語(yǔ)句，然后再輸入每一個(gè)用戶名，這需要花好幾個(gè)小時(shí)的時(shí)間
　　
　　寫(xiě)一條SQL語(yǔ)句，生成需要的ALTER USER語(yǔ)句，然后執(zhí)行他，這只需要幾分鐘時(shí)間
　　
　　很明顯我們將選擇生成SQL的方法：
　　
　　例：
　　
　　SELECT 'ALTER USER'username
　　'TEMPORARY TABLESPACE temp;'
　　FROM DBA_USERS
　　WHERE username<>'SYS'
　　AND temporary_tablespace<>'TEMP';
　　
　　這個(gè)查詢的結(jié)果將被脫機(jī)處理到一個(gè)文件中，然后在執(zhí)行：
　　
　　ALTER USER SYSTEM TEMPORARY TABLESPACE temp;
　　ALTER USER OUTLN TEMPORARY TABLESPACE temp;
　　ALTER USER DBSNMP TEMPORARY TABLESPACE temp;
　　ALTER USER SCOTT TEMPORARY TABLESPACE temp;
　　ALTER USER DEMO TEMPORARY TABLESPACE temp;

上一篇：Oracle常用的OCI函數(shù)

下一篇：Oracle現(xiàn)用數(shù)據(jù)庫(kù)