access controll

1Privilgeges
?1) Database security:
? --System security
? --Data security
?2)System privileges:Caining access to the database
?3)Object privileges:manipulationg thee content of the database objects
?4)Schemas:Collections of objects ,such as tables,views,and sequences
2System Privileges
? . More than 100 privileges are available;
? . The database administrator has high-levle system privileges for tasks such as:
??? creating new user,removing user,removing tables,backing up tables
3 Creating user
?the dba creates users by using the create user statement
?create user user
?identified by password;
?e.g create user object scott
???? identified by tiger;

?SQL> create user testuser
? 2? identified by test;

User created

SQL> conn testuser/test@orcl2000
Not logged on

SQL> grant access session to testuser;

grant access session to testuser

Not logged on

SQL> conn digit_cc/digit_cc@orcl2000
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as digit_cc

SQL> grant create session to testuser;

Grant succeeded

SQL> conn testuser/test@orcl2000;
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as testuser
4 user System privileges
?once a user is created,the dba can grant specific system privileges to a user
?grant privilege[,privilege...]
?to user [,user|role,public...];

?DBA can grant a user specific system privileges
?grant create session,create table,create sequence,create view? to scott;
5 creating and granting privileges to role
' Create a role
? create role manager;
?.grant privileges to a role
? grant create table,create view to manager
?.Grant a role to user
? grant manager to kochar;
?
SQL> create role testrole;

Role created

SQL> grant create table,create view,create sequence to testrole;

Grant succeeded

SQL> grant testrole to testuser;
6 change your password
?you can change your password by using the alter user statement;
?alter user scott
?indetified by lion;
7 object privileges
?object privileges vary from object to object
?an owner has all the privilege to the object
?an owner can give specific privilege on that owner object
? grant select on auther to testuser;
? grant select on outher to testuser with grant option -- testuser also can grant it to

other user;
? grant update(department_name,location_id)
? on departments
? to scott,manager;
8 how to revoke object privileges
?--you use the revoke statement to revoke privileges granted to other users
?--privileges granted to other users through the with grant option clause are also revoked.
? revoke privilege {[,privilege...]|all} on object
? from {user[,user....]|role|public}
? [cascade constraints]
? revoke select on author from user;
9 Database Links
?Database link allow user to access data in the remote database;
SQL> create database link kjw1
? 2? connect to digit_cc identified by digit_cc
? 3? using 'orcl2000';

Database link created

SQL> select * from digit_cc.table_action@kjw1;

posted @ 2006-09-29 15:40 康文 閱讀(246) | 評論 (0) | 編輯 收藏

database link 使用 轉載

鏈接到遠程數據庫

在一個分布式的環境里，數據庫鏈接是定義到其它數據庫的路徑的一個重要方法，使得遠程處理天衣無縫。

要獲得數據庫鏈接的更深奧的知識，查看Oracle8i SQL Reference（Oracle8i SQL參考）和Oracle8i Concepts （Oracle8i概念手冊）。詳細資料的另一個極好的來源是Oracle8i Distributed Database Systems（Oracle8i分布式數據庫系統手冊）。

今天許多運行Oracle的機構有不止一個Oracle數據庫。有時不管原計劃是否這樣，一個數據庫中的數據可能與另一數據庫中的數據關聯。出現這種情況時，你可以鏈接這兩個數據庫使得用戶或應用程序可以訪問所有數據，就好象它們在一個數據庫中。當你這么做時，你就有了一個分布式數據庫系統。

如何將兩個數據庫鏈接在一起呢？使用一個數據庫鏈接來完成。數據庫鏈接是定義一個數據庫到另一個數據庫的路徑的對象。數據庫鏈接允許你查詢遠程表及執行遠程程序。在任何分布式環境里，數據庫鏈接都是必要的。

簡單案例

數據庫鏈接的目的是定義一條到遠程數據庫的路徑，使你可以通過在本地執行一條SQL語句來使用那個數據庫中的表和其它的對象。例如，你在一個遠程數據庫上有一個稱之為"geographic feature name"的表，而你想在已連接到你本地數據庫的情況下訪問那些數據。數據庫鏈接正是你所需要的。在建立它之前，你必須搜集如下信息：

一個網絡服務名稱，你的本地數據庫事例能夠使用它來與遠程事例相連接遠程數據庫上的有效用戶名和口令網絡服務名稱是每一個數據庫鏈接必需的。每一次你從客戶機PC使用SQL*Plus連接到你的數據庫時都要使用服務名稱。在那些情況下，你提供給SQL*Plus的網絡服務名稱是通過在你的客戶機上的nsnames.ora文件中查找它們來解析的。在數據庫鏈接中使用的網絡服務名稱也是如此，除非是那些名字是使用駐留在服務器上的tnsnames.ora文件來解析。

在你定義數據庫鏈接時指定的用戶名和口令，用于建立與遠程事例的連接。不需硬編碼用戶名和口令，建立數據庫鏈接也是可能的甚至是值得選取的。既然這樣，現在我們注意這個最直接的例子。

下列語句建立了一個數據庫鏈接，它允許訪問客戶帳戶，這個帳戶是事先在GNIS數據庫建好的：

CREATE DATABASE LINK GNIS
CONNECT TO GUEST IDENTIFIED BY WELCOME
USING 'GNIS';

鏈接名稱GNIS緊隨LINK關鍵字。當連接到遠程事例時，CONNECT TO...IDENTIFIED子句指定UEST/WELCOME作為用戶名和口令使用 。USING子句指定通過網絡服務名稱GNIS建立連接。使用這一鏈接，現在你可以在遠程數據庫上查詢數據。例如：

SQL> SELECT GFN_FEATURE_NAME
2 FROM GNIS.FEATURE_NAMES@GNIS
3 WHERE GFN_FEATURE_TYPE='falls'
4 AND GFN_STATE_ABBR='MI'
5 AND GFN_COUNTY_NAME='Alger';

GFN_FEATURE_NAME
_________________
Alger Falls
Au Train Falls
Chapel Falls
Miners Falls
Mosquito Falls
Tannery Falls
..

在SELECT語句中@GNIS緊隨表名稱，說明GNIS.FEATURE_NAMES表是在遠程數據庫，應該通過GNIS鏈接訪問，鏈接類型Oracle支持幾種不同類型的鏈接。這些類型相互重疊，有時難以通過選項進行分類。當你建立數據庫鏈接時，你需要從下面選取：

Public（公用）或Private （私有）鏈接

權限類: Fixed User（固定用戶）, Connected User（連接用戶）或 Current User（當前用戶）
Shared Link（共享鏈接）或 Not Shared Link（非共享鏈接）
每次創建數據庫鏈接時，你要自覺不自覺地做這三種選擇。

公用鏈接與私有鏈接相對比

公用數據庫鏈接對所有的數據庫用戶開放訪問權。前面顯示的是私有數據庫鏈接，它只對建立它的用戶授權。公用數據庫鏈接更為有用，因為它使你不必為每一個潛在用戶創建單獨的鏈接。為了建立一個公用數據庫鏈接，使用如下顯示的PUBLIC關鍵字：

CREATE PUBLIC DATABASE LINK GNIS
CONNECT TO GUEST IDENTIFIED BY WELCOME
USING 'GNIS';

即使這是一個公用鏈接，用戶名仍舊固定。所有使用這個鏈接的用戶都作為用戶GUEST連接到遠程數據庫。

使用數據庫鏈接訪問遠程表

圖1 數據庫鏈接GNIS，指明網絡服務名稱，鏈接PROD事例到GNIS事例中的FEATURE_NAMES表。

權限類

當你建立一個數據庫鏈接時，關于你如何授權對遠程數據庫進行訪問，有三種選擇。這三種選擇代表了數據庫鏈接的另一種分類方法。這三種類別如下：

固定用戶。為遠程數據庫鏈接指定用戶名和口令，作為數據庫鏈接定義的一部分。
連接用戶。在不指定用戶名和口令時創建的數據庫鏈接。
當前用戶。建立數據庫鏈接并指定CURRENT_USER關鍵字。
固定用戶數據庫鏈接是指在創建鏈接時為遠程數據庫指定用戶名和口令。這一鏈接不管什么時候使用，也無論誰使用，都使用相同的用戶名和口令登陸到遠程數據庫。到目前為止你在本文中所看到的都是固定用戶鏈接。

固定用戶鏈接，尤其是公用固定用戶鏈接的一個潛在問提是他們把遠程系統上的同一帳戶給了許多本地用戶。從安全角度來說，如果所有的本地用戶在遠程系統上擁有同一個帳戶，責任就要折中，這取決于用戶的數量 。如果數據丟失，幾乎不可能確定破壞是如何發生的。另一個潛在問題是公用固定用戶鏈接將對遠程數據庫的訪問權給了所有的本地數據庫用戶。

如果你不想在數據庫鏈接中嵌入用戶名和口令，Oracle提供給你另一個非常有用的選擇。你可以建立一個連接用戶鏈接。連接用戶鏈接是這樣的鏈接，它通過任一個正在使用該鏈接的本地數據庫的用戶的用戶名和口令登陸到遠程數據庫。你可以通過簡單地空出用戶名和口令來建立一個連接用戶鏈接。考慮如下定義：

CREATE PUBLIC DATABASE LINK GNIS

USING 'GNIS';

鏈接名是GNIS。它連接到遠程數據庫連接時使用的網絡服務名稱是GNIS，但是沒有指定用戶名和口令。當你在查詢中使用這個鏈接時，它將向遠程數據庫發送你當前的用戶名和口令。例如，如果你使用AHMAD/SECRET 登陸到你的本地數據庫，那么AHMAD/SECRET將是你登陸到遠程數據庫時使用的用戶名和口令。

為了使用一個連接用戶鏈接，你必須在遠程數據庫上有一個帳號，了解這一點是很重要的。不但這樣，而且你在兩個數據庫上應使用同樣的用戶和口令。如果本地登陸使用AHMAD/SECRET，那么登陸到遠程數據庫時也必須使用同樣的用戶名和口令。使用連接用戶鏈接時，如果你的口令不同，你就無權登陸。

公用連接用戶數據庫鏈接尤其有用，因為你可以建立一個可被所有用戶訪問的鏈接，并且所有用戶被分別使用他或她自己的用戶名和口令授權。你獲得責任方面的利益，沒有將遠程數據庫向你的本地數據庫上的每一位用戶開放。代價是你必須在兩個數據庫上建立用戶帳戶，并且你必需確信口令保持一致。

當前用戶鏈接通過使用CURRENT_USER關鍵字建立并且與連接用戶鏈接相似。只有當使用Oracle Advanced Security Option（Oracle高級安全選項）時，你才能使用當前用戶鏈接，這個鏈接只對授權使用X.509認證的用戶有用。

共享鏈接

共享數據庫鏈接是指該鏈接的多個用戶可以共享同一個底層網絡連接。例如，在有四位用戶的MTS（多線程服務器）環境下，每一個共享服務器進程都將與遠程服務器有一個物理鏈接，這四位用戶共享這兩個鏈接。
表面上，共享鏈接乍一聽起來像是一件好事。在某些環境下的確如此，但是，當你考慮使用共享鏈接時，應當意識到這有許多局限性和警告：

如果你使用一個專用的服務器連接來連接到你的本地數據庫，鏈接只能在你從那些連接中創建的多重會話間共享。 在MTS環境里，每一個共享服務器進程潛在地打開一個鏈接。所有的會話被同一共享服務器進程提供并且分享被那個進程打開的任意共享鏈接。因為在MTS環境里的一個共享服務器進程能夠服務于許多用戶連接，共享鏈接的使用可能導致打開的鏈接遠多于所必須的鏈接。用SHARED關鍵字建立共享數據庫鏈接。還必須使用AUTHENTICATED BY 子句在遠程系統上指定一有效的用戶名和口令。如下命令建立一個共享的、公用的、連接用戶數據庫鏈接：

CREATE SHARED PUBLIC DATABASE LINK GNIS
AUTHENTICATED BY DUMMY_USER IDENTIFIED BY SECRET
USING 'GNIS';

要獲得創建鏈接和管理分布式系統的更多資料，請查閱Oracle Technology Network (http://otn.oracle.com/)。
使用AUTHENTICATED BY子句稍微有些困擾，但是由于實現共享鏈接的方式安全性決定它是必須的。這個例子中的用戶名和口令DUMMY_USER/SECRET必須在遠程系統上有效。然而，遠程系統上使用的帳戶仍就是連接用戶的帳戶。如果我以JEFF/SECRET登陸到我的本地數據庫并使用我剛建好的共享鏈接，將會發生以下一系列事件：

為了打開鏈接，Oracle使用DUMMY_USER/SECRET向遠程數據庫授權。 然后，Oracle試圖使用HMAD/SECRET使我登陸到遠程數據庫。共享鏈接的主要目的是減少兩個數據庫服務器之間的底層網絡連接數量。它們最適合于MTS環境，在那你擁有大量的通過這一鏈接訪問遠程數據庫的用戶。觀念上，你想讓用戶數量超過共享服務器進程的數量。那么你可以通過為每一共享服務器進程打開一個鏈接而不是每位用戶打開一個鏈接的方法，節省資源。

查找關于數據庫鏈接的資料

你可以從幾個數據字典視圖中獲得建立好的數據庫鏈接的資料。DBA_DB_LINKS視圖為每一定義的鏈接返回一行。OWNER 列和DB_LINK列分別顯示了這一鏈接的所有者及名稱。對公用數據庫鏈接，OWNER列將包含'PUBLIC'。如果你建立固定用戶鏈接，用戶名應在DBA_DB_LINKS視圖的USERNAME列里，但是口令只能從SYS.LINK$視圖中看到。默認情況下，只有具有SELECT ANY TABLE系統權限的DBA能夠訪問SYS.LINK$視圖查看口令。你應該保護訪問那個視圖的權限。ALL_DB_LINKS 視圖和 USER_DB_LINKS視圖與 DBA_DB_LINKS視圖相類似-它們分別顯示了你能夠訪問的所有鏈接及你所擁有的全部鏈接。最后，V$DBLINK動態性能視圖向你顯示出任意給定時間你-當前用戶，打開的全部數據庫鏈接。

全局性的數據庫名稱

在分布式環境里，Oracle建議你的數據庫鏈接名應與它們連接到的數據庫的全局性名稱相匹配。因此如果你正在連接到名稱為GNIS.GENNICK.ORG的數據庫，你應當將你的數據庫鏈接命名為GNIS.GENNICK.ORG
為確定數據庫的全局性名稱，以SYSTEM登陸并查詢GLOBAL_NAME視圖：

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
_______________
GNIS.GENNICK.ORG

由于歷史的原因，默認情況下，全局性名稱與數據庫鏈接名稱的之間的鏈接不是強制性的。不過，你可以通過設置GLOBAL_NAMES的初始化參數為TRUE來改變這一行為。例如：

SQL> SHOW PARAMETER GLOBAL_NAMES

NAME TYPE VALUE
________________________________________________________
global_names boolean TRUE

用于產生這個范例的事例要求你使用的數據庫鏈接名，必須與目標數據庫的全局性數據庫名稱相匹配。注意與一些Oracle文檔中說的相反，關鍵是你的本地事例的GLOBAL_NAMES設置。如果你的本地事例中GLOBAL_NAMES=FALSE，你就能夠使用數據庫鏈接，而不用管它們是否與遠程數據庫的全局性名稱相匹配。總的來說，如果你設置GLOBAL_NAMES=TRUE，你應該在你的所有事例中一律這么做。

posted @ 2006-09-29 15:35 康文 閱讀(2156) | 評論 (0) | 編輯 收藏

some database object

1 sequence
? 1)?? automatically generatess unique numbers
?? is a sharable object
?? is typically used to create a primary key value
?? replaces applicaition code
?? speeds up the efficiency of accessing sequence
?? create sequence sequence
?? [increment by n]
?? [start with n]
?? [{maxvalue n |nomaxvalue}]
?? [{minvalue n |nominvalue}]
?? [{cycle|nocycle}]
?? [{cache n |nocache}]

?? create sequence dept_deptin_seq
?? increment by 10
?? start with 120
?? maxvalue 9999
?? nocache
?? nocycle
? 2) Confirming Sequences
?? verify your sequence values in the user_sequences data dictionary table
?? select sequence_name,min_value,max_value,increment_by,last_number
?? from user_sequences;
?? the last_number display the next available sequence number if nocache is specified
? 3)nextval and currval Pseudocolumns
??? --nextval return thee next available sequence value,it return a unique value every time
it si referenced,even for different ueer;
??? --currval obtains the current sequence value;
??? --nextval must be issued for that sequence before curval contains a value;
? 4) Using a Sequence
??? -- Caching sequence values in the memory give faster access to these values;
??? -- Gaps in sequence value can occur when
?????? a rollback occurs
?????? b the system crashes
?????? c A sequence us used in another table;
?? 5) alter sequence test increment by 10;
????? you can change all properties of the sequence except the start with .
?? 6) remove sequence
????? drop sequence test;
2 index
? 1) how are indexes created
?? Automatically : a unique index is created automatically when you create primary key or

unique constraint in a table definition,
?? Manually: user can create nounique index on column to speed up access to the rows.
?? create index testindex on autoer(lanme);
? 2) When to Create an index
?? ypu should create an index if:
?? . a column contains a wide range of values
?? . a column contains a large number of null values
?? . one or more columns are frequently used together in where clause or a join condition;
?? . The table is large and most queries are expected to retrieve less than 2 to 4 percent

of the rows;
?? 3) When not to create an index
?? this usually not worth creating an index if:
?? . the table is small
?? . The columns are not often used as a condition in the query.
?? . Most queries are expected to retrieve more than 2 to 4 percent of the rows in the

table
?? . the indexed columns are referenced as part of an expression.
?? 4)Confirming indexes
??? . The user_indexes data dictionary view contains the name of the index and tis uniquess
??? . the user_ind_columns view contains the index name,the table name,and the column name.
??? select ic.index_name,ic_column_name,ic.column_position,ic_col_pos,ix.uniqueness
??? from user_indexed ix,user_ind_columns ic
??? where ic.index_name=ix.index_name
??? and ic.table_name='employees';
? 5)基于函數的索引
? . a function-based index is an index based on expressions
? . The index expression is built form table columns,constraints,SQL functions and user-

defined functions
?? create index testindex2
?? on autors (upper(au_fname));
??
?? select * from authors
?? where upper(au_fname) like 'B%';
? 6) remoe index
?? drop index index_name;
3 synonyms
? Simplify access to objects by creating a synonym
?? . Ease referring to a table ownerd by anther user
?? . Shorten lengthy object names;
?? create [publi] synonym synonym for object;

posted @ 2006-09-29 11:31 康文 閱讀(197) | 評論 (0) | 編輯 收藏

使用游標

posted @ 2006-09-28 15:32 康文 閱讀(160) | 評論 (0) | 編輯 收藏

createing view

1Why Use Views
? to restrict data access
? to make complex query easy
? to provide data independence
? to provide defferent view of the same data
2 Creating a View
? 1)create [or replace] [force|noforce] view view
? as subquery
? force : create view wether the referenced object existed or not
?
? desc view_name;
?2)create a view by using column aliases in the subquery
? create view salv50
? as select employee_idIO_NUMBER,last_name NAME,slaary*12 ANN_SALARY
? from employees
? where department_id=50;
3 Modigy a View
? 1) Modigy the emplvu80 view by using create or replace view clause.Add an alias for each

column name;
?? create or replace view empvu80
?? (id_number,name,sal,department_id)
?? as select employee_id,first_name||" "||last_name,salary.department_id
?? from employees
?? where department_id=80;
?? column aliases in the create view clause are listed in the same order as the columns in

the subquery
?? note : alter view_name is not a valid command.
4 Create a Complex View
? Create a complex view that contains group functions to display values from two tables
? create view dept_sum_vu
?? (name,minsal,maxsal,avgsal)
? as
?? select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
?? from employees e,departments d
?? where e.department_id=d.department_id
?? group by d.department_name;
5 Rules for performs DML operaton on a view
? 1) You can perform DML operation on simple views
? 2) You can not romove a row if the view contains the following:
??? --group functions
??? --a group by clause
??? --the distince keyword
??? -- rownum keyword
??? -- column defined by expressions
6 Using the with check option Clause
? 1) you can ensure that dml operatons performed on the view stay within the domain of the

view by using the with check option clause.
? creaate view test1
? as
? select * from emp where qty>10;
? with check option;
? update testview1 set qty=10
? where ster_id=6830;
? --when you doing the following update operation
? update testview1 set qty=5 where id=10;
? -- an error will report
? --you violate the where clause
? 2)Any attempt to change the department number for any row in the view fails because it

violates the with check option constraint
?? create or replace view empvu20
?? as
?? select * where department_id=20
?? with check option constriant empvu20_ck;
7 Denying DML Operations
? 1 You can ensure that no dml operations occur by adding the with read only option to your

view definition.
? 2)Any attempt to a DML on any row in the view resuls in an oralce server error.
8 remove veiw
? drop view_name
9 inline view
? 1) an inline view is a subquery with an alias that you can use within a sql statement.
? 2) a named subquery in the from clause of the main query is an exqmple of an inline view
? 3) an inline view is not a schema object.
10 Top-N Analysis
?1)Top_N querise ask for the n largest or smallest values of a column.
?2)Both largest values and smallest values sets considered Top-N queries
? select * from (select ster_id,qty from sales);
?example
? To display the top three earner names and salaries from the employees
? select rownum as rank,last_name,salary
? from (select last_anme,slary from employee
??????? order by slary desc)
? where rownum<=3;
?

posted @ 2006-09-27 18:30 康文 閱讀(278) | 評論 (0) | 編輯 收藏

使用復合變量.

1 pl/sql 集合 處理單列多行數據庫，使用的類型為標量類型
?1） 索引表
? type ename_table_type is table of emp.ename%type
??? index by binary_integer;
? ename_table ename_table_type;
? begin
??? select ename into ename_table(-1) from emp
????? where empno=&no;
??? dbms_output.put_line('雇員名：'||ename_table(-1));
? end;
?
?? set serveroutput no
?? declare
???? type area_table_type is table of number
??????? index by varchar2(10);
???? rea_table area_table_type;
??? begin
??????? area_table('beijing'):=1;
??????? area_table('shanghai'):=2;
??????? area_table('guangzhou'):=3;
??????? dbms_output.put_line(area_table.first);
??????? dbms_output.put_line(area_table.last);
??? end;
???? 2) 嵌套表
????? 索引表類型不能作為累得數據類型使用，但是嵌套表可以作為表類的數據類型使用。
當使用嵌套表元素時，必須先用其構造方法初始化其嵌套表：
?????? a? 在pl/sql 塊中使用嵌套表
??????? declare
????????? type ename_table_type is table of emp.ename%type;
????????? ename_table ename_table_type;
??????? begin
?????????? ename_table:=eanme_table_type('2','2','3');
?????????? select ename into ename table(2) from emp where empno=&no;
?????????? dbms_ouput.put_line(ename_table(2));
??????? end;
????? b 在表中使用嵌套表
??????? create type phone_type is table of varchar2(20);
??????? create table employee(
????????? id number (4),name varchar2(10),sal number(6,2),
????????? phone phone_type
??????? )nested table phone store as phone_table;
?????? -- 為嵌套表插入數據
??????? insert into employee values(2,'scott',200,phone_type('2222','333333'));
?????? －－檢索嵌套表累得數據
??????? set erveroutput on
??????? declare
????????? phone_table phone_type;
??????? begin
????????? select phone into phone_table
????????? from employee where id=1;
????????? for i in 1..phone_table.count loop
??????????? dbms_output.put_line(phone_table(i));
????????? end loop;
??????? end;
?????? -- 更新嵌套表列的數據
???????? delcare
??????????? phone_table phone_type:=('44444','555555');
???????? begin
??????????? update employee set phone=phone_table
??????????? where id=1;
???????? end;
??? 3) 變長數組
????? 在使用varray 時必須指定最大個數，和數據類型,在使用其元素時必須進行初始化
????? type ename_table_type is varray(20) of emp.ename%type;
????? ename_table ename_table_type:=ename_table_type('1','2');
?????
????? -- 在快中使用varray
????? declare
???????? type ename_table_type is varray(20) of emp.ename％type；
???????? ename_table ename_table_type:=ename_table_type('mary');
???????? begin
??????????? select ename into ename_table(1) form emp
?????????????? where empno=$no;
????????? end;
????? --在表列中使用varray
?????? create type phone type is varray(20) of varchar2(20);
?????? create table employee(
???????? id number(4),name varchar2(10),
???????? sal number(6,2),phone phone_type);
??????
???? 3)記錄表
????? 記錄表結合了記錄和集合的優點
??????? declare
????????? type emp_table_type is table of emp%rowtype
????????? index by binary_integer;
??????? emp_table emp_table_type;
??????? begin
????????? select * from into emp_table(1) from emp
????????? where empno=&no;
????????? dbms_output.put_line(emp_table(1).ename);
??????? end;
????? 4)多維集合
?????? 1 多級varray
?????? declare
??????? --define 一維集合
????????? type al_array_type is varray(10) of int;
??????? --定義二維集合
????????? type nal_varray_type is varray(10) of a1_varray_type;
??????? --初始化二維集合
????????? nvl nal_varray_type:=nal_varray_type(
??????????? a1_varray_type(1,2),
??????????? a1_varray_type(2,3)
????????? )
???????? beign
?????????? for i in 1..nal_varray_type.count loop
????????????? for j in 1..a1_array_type.count loop
??????????????? dbms_out.putline(nvl(i)(j));
????????????? end loop;
?????????? end loop;
??????? end;
?????? 2 使用多級嵌套表
??????? table a1_table_type is table of int;
??????? table nvl_table_type is table of a1_table_type;
??????? nvl nvl_table_type:=nvl_table_type(
????????? a1_table_type(1,2),
????????? a1_table_type(2,3)
??????? );
2 集合方法
? 1) exist
?? if ename_table.exists(1) then
??? ename_table(1):='scott';
?? 2) count 返回當前集合變量中的元素總個數
??? ename_table.count
?? 3) limit 返回集合元素的最大個數? 只有varray 有
?? 4)first and last
?????? ename_table.first
?????? ename_table.last
?? 5) prior 和next
??? ename_table.prior(5); －－返回元素5的前一個
??? ename_table.next(5);? --? 后一個
?? 6) extend
??? 使用于varray 和 嵌套表。
??? extend add a null value
??? extend (n) add n null value
??? extend (n,i)add n i value
??? declare
????? type ename_table_type is varray(20) of varchar2(20);
????? ename_table ename_table_type;
??? begin
????? ename_table:=ename_table_type('mary');
????? ename_table.extend(5,1);
????? dbms_output.put_line(ename_table.count);
??? end;
?? 7) trim
?? trim remove one element from the tail of the collection.
?? trim(n) remove n element from the tail of the colleciton.
?? 8)delete
??? delete: delete all the elements
??? delete(n) :delete the nth elements
??? delete(m,n): delete the elements from m to n
3 集合賦值
? 1)將一個集合的數據賦值給另一個集合.clear the destination collectins and set the original

collection
?? delcare
??? type name_varray_type is varray(4) of varchar2(10);
??? name_array1 name_varray_type;
??? name_array2 name_varray_type;
?? begin
???? name_array1:=name_varray_type('scott','smith');
???? name_array2:=name_array_type('a','b','c');
???? name_array1:=name_array2;??
?? end;
??
?
? type name_array1_type is varray(4) of varchar2(10);
? type name_array2_type is varray(4) of varchar2(10);
? name_array1 name_array1_type;
? name_array2 name_array2_type;
? 具有相同的數據類型，單具有不同的集合類型不能構賦值
? 2) 給集合賦城null 值
??? 可以使用delete 或 trim
??? 也可以使用 空集合賦給目表集合
??? type name_varray_type is varray(4) of varchar2(10);
??? name_array name_varray_type;
??? name_empty name_varray_type;
???
??? name_array:=name_varray_type('1','2');
??? name_array:=name_empty;
? 3) 使用集合操作賦和比較集合都是10g 的內容，p176 先略過。
4 批量綁定
? 執行單詞sql 操作能傳遞所有集合元素的數據。
? 1 forall 語句
? 用于insert update 和delete操作。在oracle9i 中forall 語句必須具有連續的元素
??? 1) using forall on insert
???? declare
??????? type id_table_type is table of number(6)
??????? index by binary_integer;
??????? type name_table_type is table of varchar2(2)
??????? index by binary integer;
??????? id_table id_table_type;
??????? name_table name_table_type;
????? begin
???????? for i in 1..10 loop
?????????? id_table(i):=i;
?????????? name_table(i):='Name'||to_char(i);
???????? end loop;
???????? forall i in 1..id_table.count
?????????? insert into demo demo values(id_table(i),name_table(i));
????? end;
???? 2)using forall on using update
?????? forall i in 1..id_table.count
?????????? upate demo set name:=name_table(i)
????????????? where id:=id_table(i);
???? 3)using forall on using delete
??????? forall i in 1..id_table.count
??????????? delete from demo where id:=id_table(i);
???? 4) using forall on part of the collection
??????? for i in1..10 loop
????????? id_table(i):=i;
????????? name_table(i):="name"||to_char(i);
??????? end loop;
??????? forall i in 8..10 l
?????????? insert into demo values(id_table(i),name_table(i));
?? 2 bulk collect
???? is fit for select into ,fetch into and dml clause
???? 1) using bulk collect
????? declares??
??????? type emp_table_type is table of emp%rowtype
???????? index by binary_integer;
??????? emp_table emp_table_type;
????? begin
???????? select * bulk collect into emp_table
????????? from emp where deptno=&no;
???????? for i in 1..emp_tablee.count loop
??????????? dbms_output.put_line(emp_table(i).ename);
???????? end loop;
????? 2) 在dml 的返回字句使用bulk collect 字句
???????? declare
????????? type ename_table_type is table of emp.ename%type;
?????????? ename_table ename_table_type;
????????? begin
???????????? deletee from emp where deptno=&no
???????????? returning ename bulk_collect into ename_table;
????????? for i in 1..ename_table.count loop
??????????? dbms_output.put(ename_table(i));
????????? end loop;
??????? end;
????????? end;
????? end;

posted @ 2006-09-27 15:46 康文 閱讀(211) | 評論 (0) | 編輯 收藏

including constraint

1 What are Constrains
? 1) Constrains enforce on the table level
? 2) Constrains the deletion of a table if there are dependencies
2 Constrain Guidelines
? 1) Name a constraint or the oracle generate a name by the sys_cn format
? 2) Create a constraint either
???? --At the same time as the table is created.or
???? --After the table has been created
? 3)Define a constraint at the column or table level
? 4)view constraint in the data dictionary
3 Crete a constraint
? create table test2
? (id int not null,-- column level
?? lname varchar(20),
?? fname varchar(20),
?? constraint uk_test2_1 unique(lname,fname))--table level
4 The not null Constraint
? create table employees(
?? employee_id number(6),
?? last_name?? varchar2(25) not null? --system named
?? hire_date?? DATE
?????????????? constraint emp_hire_date not null --User named
5Foreign key
? create table test3
? (rid int,
?? name varchar(30),
?? constraint fk_test3_1 foreign key(rid) reference test2(id));
? froeign key constraint keywords
??? foreign key :Define the column in thee child table at the table constrain level.
??? references? :Identifies the table and column in the parent table.
??? on delete cascade: Delete the dependent rows in the child table when a row in the????

parent table is deleted
??? on delete set null:Convert the dependent foreign key values to null when a row in the
??? parent table is deleted.
??
??? --parent table referenced table
??? --child table refernce other table
6 The check Constraint
? Define a condition that each row must be satify
? alter table test3
? add constrain ch_test3 check(name like 's%')
7 Dropping a Constraint
? 1) Remove the manager constraint form the employee table
?? alter table test3
?? drop constriant test3_manager_fk
? 2) Remove the primary key constraint on the departments table and drop the associated
?? foreign key constraint on the employees.department_id column
?? alter table departments
?? drop primary key cascade
8 Disabling and enable Constraints
? 1)Execute the disable clause of the alter table statment to deactive an integrity

constraint
? 2)Apply the cascade option to disable dependent integrity constrints
? alter table employees
? disable constraint emp_emp_id_pl cascade
? 3) enabling Constraints
? .Active an integrity constraint currently disabled in the table definition by using the

enable clause.
?? alter table employees
?? enable constraint emp_emp_id_pk;
? a unique? or a primary? index is automatically created if you enable a unique key or a

primary key constraint?
?8 View Constraints
? select constraint_name,constriant_type,serch_condition
? from user_constraints
? where table_name='employees'
?9 view the columns associated with constraints
?select constraint_name,column_name
?from user_cons_columns
?where table_name='employees'

posted @ 2006-09-26 14:44 康文 閱讀(232) | 評論 (0) | 編輯 收藏

OpenSessionInViewFilter解決Web應用程序的問題 轉自：Potain 的BLOG

protected void doFilterInternal(HttpServletRequest request, 
             HttpServletResponse response,
	     FilterChain filterChain) throws ServletException, IOException {
	SessionFactory sessionFactory = lookupSessionFactory();
	logger.debug("Opening Hibernate Session in OpenSessionInViewFilter");
	Session session = getSession(sessionFactory);
	TransactionSynchronizationManager.bindResource(sessionFactory, 
             new SessionHolder(session));
	try {
		filterChain.doFilter(request, response);
	}
	finally {
		TransactionSynchronizationManager.unbindResource(sessionFactory);
		logger.debug("Closing Hibernate Session in OpenSessionInViewFilter");
		closeSession(session, sessionFactory);
	}
}

publicfinal void setSessionFactory(SessionFactory sessionFactory) {
    this.hibernateTemplate = new HibernateTemplate(sessionFactory);
  }
 protectedfinal HibernateTemplate getHibernateTemplate() {
  return hibernateTemplate;
 }

publicabstract class BaseHibernateObjectDao
	extends HibernateDaoSupport
	implements BaseObjectDao {
protected BaseEntityObject getByClassId(finallong id) {
		BaseEntityObject obj =
			(BaseEntityObject) getHibernateTemplate()
				.execute(new HibernateCallback() {
publicObject doInHibernate(Session session)
				throws HibernateException {
				return session.get(getPersistentClass(), 
                                       newLong(id));
			}
		});
		return obj;
	}
public void save(BaseEntityObject entity) {
		getHibernateTemplate().saveOrUpdate(entity);
	}
public void remove(BaseEntityObject entity) {
		try {
			getHibernateTemplate().delete(entity);
		} catch (Exception e) {
			thrownew FlexEnterpriseDataAccessException(e);
		}
	}
public void refresh(final BaseEntityObject entity) {
		getHibernateTemplate().execute(new HibernateCallback() {
publicObject doInHibernate(Session session)
				throws HibernateException {
				session.refresh(entity);
				returnnull;
			}
		});
	}
public void replicate(finalObject entity) {
		getHibernateTemplate().execute(new HibernateCallback() {
publicObject doInHibernate(Session session)
				throws HibernateException {
				session.replicate(entity, 
                                ReplicationMode.OVERWRITE);
				returnnull;
			}
		});
	}

publicObject execute(HibernateCallback action) throws DataAccessException {
	Session session = (!this.allowCreate ?
		SessionFactoryUtils.getSession(getSessionFactory(), 
                  false) :
		SessionFactoryUtils.getSession(getSessionFactory(),
                  getEntityInterceptor(),
                  getJdbcExceptionTranslator()));
	boolean existingTransaction =  
          TransactionSynchronizationManager.hasResource(getSessionFactory());
	if (!existingTransaction && getFlushMode() == FLUSH_NEVER) {
		session.setFlushMode(FlushMode.NEVER);
	}
	try {
		Object result = action.doInHibernate(session);
		flushIfNecessary(session, existingTransaction);
		return result;
	}
	catch (HibernateException ex) {
		throw convertHibernateAccessException(ex);
	}
	catch (SQLException ex) {
		throw convertJdbcAccessException(ex);
	}
	catch (RuntimeException ex) {
		// callback code threw application exception
		throw ex;
	}
	finally {
		SessionFactoryUtils.closeSessionIfNecessary(
                    session, getSessionFactory());
	}
}

publicstatic void closeSessionIfNecessary(Session session, 
    SessionFactory sessionFactory)   
    throws CleanupFailureDataAccessException {
	if (session == null || 
	   TransactionSynchronizationManager.hasResource(sessionFactory)) {
		return;
	}
	logger.debug("Closing Hibernate session");
	try {
		session.close();
	}
	catch (JDBCException ex) {
		// SQLException underneath
		thrownew CleanupFailureDataAccessException(
		"Cannot close Hibernate session", ex.getSQLException());
	}
	catch (HibernateException ex) {
		thrownew CleanupFailureDataAccessException(
		"Cannot close Hibernate session", ex);
	}
}

Category parentCategory  = new Category ();
	parentCategory.setName("parent");
	dao.save(parentCategory);
	Category childCategory  = new Category();
        childCategory.setName("child");
	parentCategory.addChild(childCategory);
	dao.save(childCategory);
	Category savedParent = dao.getCategory("parent");
	Category savedChild = (Category ) savedParent.getChildren().get(0);
	assertEquals(savedChild, childCategory);

posted @ 2006-09-26 11:01 康文 閱讀(560) | 評論 (0) | 編輯 收藏

creating and manipulation table

posted @ 2006-09-25 17:59 康文 閱讀(209) | 評論 (0) | 編輯 收藏

Data manipulation

posted @ 2006-09-25 16:25 康文 閱讀(247) | 評論 (0) | 編輯 收藏