Manager User

create user test1 identified by test1;
grant connect,create table to test1;
conn cyts_cc/cyts_cc@orcl2000;
create table(
id int)
tablespace user;
ERROR 位于第 1 行:
ORA-01950: 表空間'USERS'中無權(quán)限
conn cyts_cc/cyts_cc@orcl2000;
alter user test1 quota 1M on users;
create tab
(id int);
success
alter user test1 account lock;
conn test1/test1@orcl2000;
ERROR:
ORA-28000: the account is locked
1 Database Schema
?a schema is a named collection of objects
?b user is created and a corresponding schema is created
?c user can be associated only with one schema
?d username and schema are often userd interchangely.
2 Checklist for creating users
? a idntfigy tablespaces in which the usr nedds to store objects
? b decide on quotas for each tablespace
? c assign a default tablespace and temporary tablespace.if you do not specify at the time of create user,system tablespace will be the defalut tablespace and temporary.it will affect the performance of the oralce.
? d create user
? e grant privileges and roles to user
? desc dba_users;
? select * from dba_users;
3 Creating a new user:
Database Authentiacation
?set the initial password
? create user aaron
? identified by soccer
? default tablespace data
? temporary tablespace temp
? guota 15m on data
? password expire;

? alter database default temporary tablespace temp;
4 Creating a new user operating System Authentication
? os_authent_prefix initialllization parameter specifies the format of the username
? defauts to ops$
??? create user arron
??? identified externally
??? default tablespace users
??? temporary tablespace temp
??? quota 15m on data
??? password expire;

??? conn /
??? show parameter os
??? os_authent_prefix??????????????????? string?????????????????????????? OPS$
??? create user ops$test3
????? identified externally
????? default tablespace us
????? temporary tablespace
????? quota 10m on users
??? thee test2 is an user of os ,which the oracle is installed.
5 Changing user quota on tablespace
?alter user test3 quota 4m on users;
?you cann't grant quota on temp and undotbs.
?
?alter quota 0 on uers -- means that no new table space can be allocated and cannot change the exist object in the tablespaces
6 drop user
?you cannot drop user who has connected to oracle
? drop user (cascade)
7 Obtaining User information
?information about uers can be obtained by qerying the data dictionary
? dba_users
? 名稱??????????????????????????
-----------------------------
USERNAME??????????????????????
USER_ID???????????????????????
PASSWORD??????????????????????
ACCOUNT_STATUS????????????????
LOCK_DATE?????????????????????
EXPIRY_DATE???????????????????
DEFAULT_TABLESPACE????????????
TEMPORARY_TABLESPACE??????????
CREATED???????????????????????
PROFILE???????????????????????
INITIAL_RSRC_CONSUMER_GROUP???
EXTERNAL_NAME?????????????????
? dba_ts_quotas
?? 名稱??????????
?---------------
?TABLESPACE_NAME
?USERNAME??????
?BYTES?????????
?MAX_BYTES?????
?BLOCKS????????
?MAX_BLOCKS????

posted @ 2006-10-12 09:49 康文 閱讀(388) | 評論 (0) | 編輯 收藏

Using SET Operators.

1 The Union Operator
?The union operator returns results form both queries after eliminating duplications.\
?select employee_id,job_id
?from employees
?uniion
?select employ_id ,job_id
?from job_history;
2 the all operator
?The union all opertor reutrn result from both queries,including all duplications
3 interset
?select ster_id,qty from sales where qty>20;
?intersect
?select ster_id,qty from sales where ster_id like '7%';
4 minus
?select ster_id,qty from sales where qty>20
?minus
?select ster_id from sales where ster_id like '7%'

5 set operator guidelines
?. teh expressions in the select list must match in number and data type;
?. Parentheses can be used to alter the sequence of the execution
?.The order by clause:
??? can appear only at the very end of the statement
??? will accept the column name,aliases from thee firest select statement ,or thee positional notation
?.Duplicate row are atuomatically eliminated except in union all.
?.Column names from the first query appear in the result
?.The output is sorted in ascending order by default except in union all
6 matching the select statement
?select department_id,to_number(null),location,hire_date
?from employees
?union
?select department_id,location_id,to_date(null)
?from departments;

?select employee_id,job_id,salary
?from employees
?union
?select employee_id,job_id,0
?from job_history;
7 Controlling the order of the rows
?select 'sing' as "my dream" ,3,a_dummy
?from dual
?union
?select 'like''d like to teach',1
?from dual
?union
?select 'the world to',2
?from dual
?order by 2;

posted @ 2006-10-11 14:51 康文 閱讀(225) | 評論 (0) | 編輯 收藏

開發(fā)子過程

?1? 開發(fā)過程
?1) 建立過程：不帶參數(shù)
? create or replace procedure out_time
? is
? begin
??? dbms_output.put_line(systimestamp);
? end;
? a 使用execute 命令調(diào)用過程
?? set? serveroutput on
?? exec out_time;
? b 使用call 命令調(diào)用過程
?? set serveroutput on
?? call out_time();
?2) 建立過程：帶有in參數(shù) 默認為輸入?yún)?shù)，另外也可以使用in 關(guān)鍵子顯示的定義
?? create or replace procedure add_employee
?? (eno number,name varchar2,sal number,
??? job varchar default 'clerk',dno number)
?? is
?? e_integrity exception;
?? pragma exception_init(e_intgegrity,-2291);
?? begin
??? insert into emp(empno,ename,sal,job.deptno)
????? values(eno.name,sal,job,dno);
??? exception
????? when dup_val_on_index then
??????? raise_application_error(-20000,'雇員號不能重復(fù)');
????? when e_integrity then
??????? raise_application_error(-20001,'部門號不存在');
???
調(diào)用
?? exec add_employee(111,'clark',200,'manager',10);
?? 3 建立過程，帶有out 參數(shù)
??? create or replcace procedure query_employee
??? (eno number,name out varchar2,salary out number)
??? is
??? begin
????? select ename,sal,into name,salary from emp where empno=eno;
??? exception
????? when no_data_found then
??????? raise_application_error(-20000,'G該雇員不存在');
??? end;
???
??? 調(diào)用
??? var name varchar2(10)
??? var salary number
??? exec query_employee(7788,:name,:salary);
??? print name,salary;
??? 4 建立過程，帶有in out 參數(shù)
??? create or replace procedure compute
??? (num1,in out number,num2 in out number)
??? is
?????? v1 number;
?????? v2 number;
??? begin
????? v1:=num1/num2;
????? v2:=mod(num1,num2);
????? num1:=v1;
????? num2:=v2;
??? end;?
???
??? 調(diào)用
??? var n1 number
??? var n2 number
??? exec :n1:=100;
??? exec :n2:=30
??? exec compute(:n1,:n2)
??? print n1 n2
??? 5) 為參數(shù)傳遞變量和數(shù)據(jù) 位置傳遞，名稱傳遞，組合傳遞
??? create or replace procedure add_dept
??? (dno number,dname varchar2 default null,loc varchar default null)
??? is
??? begin
????? insert into dept values(dno.dname,loc);
??? end;
??? -- 位置傳遞
???? exec add_dept(50,'sales','new york');
???? exec add_dept(60);
???? exec add_dept(70,'admin');
??? -- 名稱傳遞
???? exec add_dept(50,loc=>'new york');
???? exec add_dept(60,dname=>'sales',loc=>'new york');
?? 6) 顯示當(dāng)前用戶的所有程序及其源代碼
???? select text from user_source where name='add_dept';
?? 7) 刪除過程
???? drop procedure add_dept;
2 開發(fā)函數(shù)
? 可以在sql語句和其他子過程中執(zhí)行。
? 1 建立函數(shù)，不帶任何參數(shù)
? create or replace function get_user
? return varchar2
? is
??? v_user varchar2(100);
? begin
??? select username into v_user from user_users;
??? return v_user;
??? end;
? --使用變量接收函數(shù)返回值
?? var v2 varchar2(100)
?? exec :v1:=get_user
?? print v1;
?-- 在sql 語句中直接調(diào)用函數(shù)
?? select get_user from dual;
?-- 使用dbms_output 調(diào)用函數(shù)
?? set serveroutput on
?? exec dbms_output.put_line('get_user');
?2) 建立函數(shù)，帶有in 參數(shù)
?? create or replace function get_sal(name in varchar2)
?? reutnr number
?? as
?? v_sal emp.sal%type;
?? begin
???? select sal into v_sal from emp where upper(ename)=upper(name);
???? return v_sal;
?? exception
???? when no_data_found then
?????? raise_application_error(-20000,'employee does not exist');
?? end;
?3) 建立函數(shù)，帶有out 參數(shù)
?? create or replace function get_info
?? (name varchar2,title out varchar2)
?? return varchar2
?? as
???? deptname dept.dname%type;
?? begin
???? select a,job,b,dname into title,deptname from emp a,dept b
???? where a.deptno=b.deptno
???? and upper(a.ename)=uppder(name);
???? return deptname;
?? end;
?? var job varchar2(20)
?? var dname varchar2(20)
?? exec :dname:=get_info('scott',job);
?? print dname job

?4) 帶有 in out 參數(shù)
? create or replace function result
? (num1,number,num2 in out nu8mber)
? return number
? as
??? v_result number(6);
??? v_remainder number;
? begin
??? v_result:=num1/num2;
??? v_remainder:=mod(num1,num2);
??? num2:=v_remainder;
??? retrun v_result;
? exception
??? when zero_divide then
?????? raise_application_error(-20000,'zero divied');
? end;
?5) 函數(shù)調(diào)用的限制
? -- 在sql 語句中只能調(diào)用存儲函數(shù)
? -- 在sql 語句中只能調(diào)用帶有輸入?yún)?shù)in ，而不能有輸出參數(shù)out 和in out 參數(shù)的函數(shù)
? -- 在sql 語句中調(diào)用的函數(shù)的參數(shù)必須是標(biāo)準(zhǔn)的sql 數(shù)據(jù)類型，不能是pl/sql 所特有的數(shù)據(jù)類型
? -- 在sql 語句中調(diào)用的函數(shù)不能包含insert，update 和delete
?6) 查看源代碼
? set pagesize 40
? select text form user_source where name='result';
?7) 刪除函數(shù)
? drop function result;
?3 管理子程序
? 1) 列出當(dāng)前用戶的子程序
?? select object_name,created,status form user_objects
?? where object_type in ('procedure','function');
? 2)列出子程序源代碼
?? select text from user_sorce where name='raise_salary'
? 3)類出子程序編譯錯誤
??? -- show errors
??? --使用數(shù)據(jù)字典user_errors? 確定錯誤原因和位置
???? select line||'/'||position as "line/col",text error
???? from user_errors where name='raise_salary';
?? 4) 列出對象的依賴關(guān)系
??? -- 使用 user_dependenciess 確定直接依賴關(guān)系
??? select name,type from user_dependencies
??? where referenced_name='emp'
??? -- 使用 deptree 和 ideptree 確定依賴和見解依賴關(guān)系
??? select nested_level,name,type from deptree;
?? 5) 重新編譯子程序
???? 當(dāng)被應(yīng)用對象的結(jié)構(gòu)被修改以后，就會將相關(guān)依賴對象轉(zhuǎn)變?yōu)闊o效invalid ，需要重新編譯
???? 1
???? alter table emp add remark varchar2(100);
???? 2
???? select object_name,object_type from user_objects
???? where status='invalid'
???? 3
???? alter procedure add_employee compile;
???? alter view dept10 compile;
???? alter function get_info compile;
?????
??

posted @ 2006-10-11 14:51 康文 閱讀(293) | 評論 (0) | 編輯 收藏

處理例外

1例外簡介
?1） 例外分類
? 預(yù)定義分類，非預(yù)定義分類，自定義例外。
2 處理預(yù)定義例外
? 1) 常用預(yù)定義例外
?? a access_into_null;
???? create type emp_type as object
???? (name varchar2(2),sal number(6,2));
????
???? declare
?????? emp emp_type;
???? begin
?????? emp.name:='scott';
???? exception
?????? when access_into_null then
????????? dbms_output.put_line('首先初始化對象emp');
??? b case_not_found
???? undef no
???? declare
?????? v_val emp.sal%type;
???? begin
?????? select sal into v_sal from emp where empno=&no;
?????? case
???????? when v_sal<1000 then
?????????? update emp set sal=sal+100 where empno=&no;
???????? when v_sal<2000 then
?????????? update emp set sal=sal+150 where empno=&no;
???????? when v_sal<3000 then
????????? update emp set sal=sal+200 where empno=&no;
?????? end case;
?????? exception
???????? when case_not_found then
?????????? dbms_output.put_line();
????? end;
??? c collection is null
??? 在給集合元素（嵌套表或array類型）賦值前，必須首先初始化集合元素
??? declare type ename_table_type is table of emp.eanme%type;
??? ename_table ename_table_type;
??? begin
????? select e_name into ename_talbe(2) from emp where empno=$no;
??? exception
????? when collection_is_null then
??????? dbms_output.put_lilne('必須使用構(gòu)造方法初始化集合元素');
??? end;
??? d currsor_already_open
??? reopen curosr 如果用戶已經(jīng)使用了open 命令打開了顯示游標(biāo)，或執(zhí)行for循環(huán)(隱式的打開游標(biāo))
?????? delcare
???????? cursor emp_surosr is select ename,sal,from emp;
?????? begin
???????? open emp_curosr;
???????? for emp_record in emp_cursor loop
????????????? dbms_output.put_line(emp_record.eanme);
???????? end loop;
?????? exception
????????? when cursor_already_open then
??????????? dbms_output.put_line("游標(biāo)已經(jīng)打開");
?????? end;
???? e dup_val_on_index
???? begin
???? exception
??????? when dup_val_on_index then
??????? dbms_output.put_line("列上不能出現(xiàn)重復(fù)值");
???? end;
???? d invalid_curosr
???? delcare
?????? curosr emp_cursor is select ename,sla from emp;
?????? emp_record emp_crusor%rowtype;
???? begin
?????? fetch emp_cursor into emp_record;
?????? close emp_crusro;
???? exception
?????? dbms_output.put_line("游標(biāo)沒有打開");
???? end;
???? f invalid_number? can not convert char to nmuber successfully
?????? begin
???????? update mep set sal=sal+1oo;
?????? exception
???????? when invalid_number then
???? g no_data_found? when select into is executed ,no row is returned?????
?????? declare
???????? v_sal emp.sal%type;
?????? begin
????????? select sal into v_cal form emp where lower(ename)=lower('&name');
?????? exception
???????? when no_data_found then
???????????? dbms_output.put_line('沒有返回結(jié)果');
?????? end;
????? h too_many_row? ora -01422 there are too many are return when "select into" is executed
????? i zero_divide ora-01476
????? g subscript_beyond_count ora-065533
????? declare
??????? type emp_array_type is varray(20) of varchar2(10);
??????? emp_array emp_array_type;
????? begin
???????? emp_array:=emp_array_type('scott','mary');
???????? dbms_output.put_line('emp_array(3)');
????? exception
???????? when subscript_beyone_count then
????????? dbms_out.put_line('超出下標(biāo)范圍');
????? end;
????? k subscript_outside_limit
?????? emp_array(-1);
????? l value_error the length of variable cannot contain the actual value;
????? declare
????? begin
????? end;
3 處理非預(yù)定義例外
? delcare
??? e_integrity exception;
??? pragma exception_init(e_integrity,-2291);
? begin
??? update emp set deptno=dno where empno=&no;
? exception
??? when a_integrity then
? end;
4 處理自定義例外
? 與oracle 錯誤沒有任何聯(lián)系，為業(yè)務(wù)邏輯所定義的例外
? delcare
??? e_no_employee exception;
? begin
??? update emp set deptno=&dno where empno=&eno;
? if sql%notfound then
??? raise e_no_employee;
? end if;
? exception
??? when e_no_emplyee then
???? dbms_output.put_line('該雇員不存在');
5 使用錯誤例外函數(shù)
? 使用例外函數(shù)可以取得錯誤號以及相關(guān)的錯誤消息，sqlcode 用于取得oracle 的錯誤號，而sqlerrm
則用于取得與之相關(guān)的錯誤信息。
? 1 sqlcode 和 sqlerrm
? 為了在pl/sql 應(yīng)用程序中處理其他為預(yù)料的到的oracle 錯誤，用戶可以在例外處理部分的
when others 自句后，引用兩個函數(shù)
? declare
??? v_ename emp.ename%type;
? begin
??? select ename into v_ename form emp where sal='&v_sal';
? exception
??? when no_data_found then
??? when others then
?????? dbms_output.put_line(sqlcode);
?????? dbms_output.put_line(sqlerrm);
? end;
? 2 raise_aaplicaition_error
?? 只能在子程序中使用（過程，函數(shù)，包，觸發(fā)器）
?? raise_application_error(error_number,message,[true|false]);
?? error_number 錯誤號，在－20000到－20999 之間
?? message 指定錯誤消息，不能超過2048?
?? if v_comm is null then
?????? raise_application_error(-20001,'該雇員無補助');
?? end if;

posted @ 2006-10-11 14:46 康文 閱讀(301) | 評論 (0) | 編輯 收藏

開發(fā)包

posted @ 2006-10-11 14:46 康文 閱讀(362) | 評論 (0) | 編輯 收藏

including Constraints

posted @ 2006-10-11 14:45 康文 閱讀(240) | 評論 (0) | 編輯 收藏

create view

posted @ 2006-10-11 14:45 康文 閱讀(304) | 評論 (0) | 編輯 收藏

使用觸發(fā)器

1 觸發(fā)器簡介
? 1) 觸發(fā)事件
? 2) 觸發(fā)條件
? 3) 觸發(fā)操作
???? . 觸發(fā)器代碼的大小不能超過32k，如果使用大量代碼建立觸發(fā)器，應(yīng)該先建立存儲過程，然后再觸發(fā)器中使用call語句調(diào)用存儲過程。
???? . 觸發(fā)器中正能含有select ，insert，update 和delete 語句，而不能含有ddl 語句，和事物控制語句。
2 建立dml 觸發(fā)器
?1) 觸發(fā)時機
? before，after 表示在執(zhí)行dml操作之后觸發(fā)器
?2)觸發(fā)事件
? insert ，update 和delete 操作。也可以使用書法事件
?3) dml 觸發(fā)器是針對特定表進行的 因此必須制定dml 操作所對應(yīng)的表
?4) 觸發(fā)器類型 用于指定當(dāng)觸發(fā)器事件之后，需要執(zhí)行幾次觸發(fā)器操作。如果指定語句觸發(fā)器類型
那么會執(zhí)行一次觸發(fā)器代碼：如果指定行觸發(fā)器類型，則會在每個被作用行上執(zhí)行一次觸發(fā)器代碼。
?5) 觸發(fā)條件
?用于指定執(zhí)行行觸發(fā)器代碼的條件，只有為ture時，才會執(zhí)行行觸發(fā)器代碼。
? 6) 如果使用pl/sql 存儲過程，java 存儲過程，或外部處處過程需要在觸發(fā)器操作部分直接使用call
? 7) dml 觸發(fā)器觸發(fā)順序
?? (1)dml 觸發(fā)器在單行數(shù)據(jù)上的觸發(fā)順序。
??? 對于單行數(shù)據(jù)而言，無論是語句此觸發(fā)器，還是行觸發(fā)器，觸發(fā)器代碼實際只執(zhí)行一次，并且執(zhí)行
順序為before 語句觸發(fā)器，before 行觸發(fā)器，dml 操作，after 行觸發(fā)器，after 語句觸發(fā)器
?? (2) dml 觸發(fā)器在多行數(shù)據(jù)上的觸發(fā)順序
??? before 語句觸發(fā)器
??? before 行觸發(fā)器
??? after 行觸發(fā)器
??? before行觸發(fā)器
??? after 行觸發(fā)器
??? after語句觸發(fā)器
?? 語句觸發(fā)器只被執(zhí)行一次，而行觸發(fā)器在每個行上都執(zhí)行一次。
? 2) 語句觸發(fā)器
? 當(dāng)審計dml 操作，或確保dml操作安全執(zhí)行時，可以使用語句觸發(fā)器
? 1 建立before 語句觸發(fā)器
?? create or replace trigger tr_sec_emp
?? before insert or update or delete on emp
?? begin
???? if to_char(sysdate,'DY','nls_dtate_language=AMERICAN') in ('sat','sun') then
???? railse_application_error(-200001,'不能在休息日改變雇員信息');
???? end if;
?? end;?
?? 2 使用條件謂詞
?? inserting ,updating ,deleting
?? create or replace trigger tr_sec_emp
?? before insert or update or delete on emp
?? begin
???? if to_char(sysdate,'DY','nls_date_language=american')
????? in('sat','sun') then
???? case
?????? when inserting then
???????? raise_application('-20001','inserting');
?????? when updating then
???????? raise_application('-20002','updating');
?????? when deleting then
???????? raise_application('-20003','deleting');
???? end case;
??? end if;
?? end;
?? 3 建立after 語句觸發(fā)器
??? 為了dml 操作，或者dml 操作后執(zhí)行匯總運算
?? create table aduit_table(
???? name varchar2(20),ins int,upd int,del int,
???? starttime date,endtime date
?? );
?? create or replace trigger tr_aduit_emp
?? after insert or update or delete emp
?? declare
???? v_temp int;
?? begin
???? select count(*) into v_temp from aduit_table
?????? where name='emp';
???? if v_temp=0 then
?????? insert into audit_table values
?????? ('emp',0,0,0,sysdate,null);
???? end if;
???? case
?????? when? inserting then
???????? update aduit_table set ins=ins+1,endtime=sysdate where name='emp';
?????? when updating then
???????? update audit_table set upd=upd+1,endtime=sysdate where name='emp';
?????? when deleting then
???????? update aduit_table set del=del+1,endtime=sysdate where name='emp';
?? end;
? 3) 行觸發(fā)器
?? 審計數(shù)據(jù)變化可以使用行觸發(fā)器
?? 1 建立不before 行觸發(fā)器
??? 為了取保數(shù)據(jù)符合商業(yè)邏輯或企業(yè)規(guī)則，對輸入的數(shù)據(jù)進行復(fù)雜的約束，可以使用before行觸發(fā)器
???? create or replace trigger tr_emp_sal
???? before update of sal on emp
???? for each row
???? begin
?????? if :new.sal<:old.sla then
???????? raisse_application_error(-200010,'工資只漲不降');
?????? end if；
???? end;
???? 2) 建立after 行觸發(fā)器
???? 為了審計dml 操作，可以使用語句觸發(fā)器或oracle 系統(tǒng)提供的審計功能，而為了審計數(shù)據(jù)變化
，則應(yīng)該使用after 行觸發(fā)器
???? create table audit_emp_change(
?????? name varchar2(10),odl number(6,2),
?????? newsal number(6,2),time date);
??? create or replace trigger tr_sal_change
??? after update of sal on emp
??? for each row
??? declare
???? v_temp int;
??? begin
???? select count(*) into v_temp from audit_emp_change where name=:old.ename;
??? if v_temp =0 then
????? insert into audit_emp_change
??????? values(:old,ename,:old.sal,:new,sal,sysdate);
??? else
????? update audit_emp_change
??????? set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;?
??? end if;

??? end;
???? )
??? 3) 限制行觸發(fā)器
??? 當(dāng)使用行觸發(fā)器，默認情況下會咱每個被作用行上七星一次觸發(fā)器代碼，為了時得再特定條件下執(zhí)行行觸發(fā)器代碼，需要使用when 子句
??? create or replace trigger tr_sal_change
??? after update of sal on emp
??? for each row
??? when(old.job='salesman')
??? declare
?????? v_temp int..
2 dml 觸發(fā)器使用注意事項
? 觸發(fā)器代碼不能從觸發(fā)器所對應(yīng)的基表中讀取數(shù)據(jù)
3 dml 觸發(fā)器
? 為了保證數(shù)據(jù)庫滿足特定的商業(yè)規(guī)則或企業(yè)邏輯，可以使用約束，觸發(fā)器和子程序。約束性能最好，實現(xiàn)最簡單，所以為售選，如果觸發(fā)器不盟實現(xiàn)，可以選擇觸發(fā)器。
? dml 觸發(fā)器可以用于實現(xiàn)數(shù)據(jù)安全保護，數(shù)據(jù)審計，數(shù)據(jù)完整性，參照完整性，數(shù)據(jù)復(fù)制等功能。
?1) 控制數(shù)據(jù)安全
? create or replace trigger tr_emp_time
? before insert or update or delete on emp
? begin
??? if to_char(sysdate,'hh24') not between '9' and '17' then
????? raise_application_error(-20101,'not work time');
???? end if;
? end;
? 2) 實現(xiàn)數(shù)據(jù)審計
? 使用數(shù)據(jù)審計只能審計sql 操作，而不會記載數(shù)據(jù)變化
? audit insert,update,delete on emp by access
? 3)實現(xiàn)數(shù)據(jù)完整性
? 首選約束 alter table emp add constraint ck_sal check (sal>=800),但是在有些情況下只能使用觸發(fā)器來實現(xiàn)數(shù)據(jù)完整性
?? create or replace trigger tr_check sal
?? before update of sal on emp
?? for each row
?? when (new.sla<old.sal or new.sal>1.2* old.sal)
?? begin
????? raise_application_error(,,,,,,)
?? end;
? 3) 使用引用完整性
? 采用 on delete cascade 可以進行集聯(lián)刪除，但是卻不能進行集聯(lián)更新。采用觸發(fā)器實現(xiàn)集聯(lián)更新
?? create or replace trigger tr_update
?? after update of sal on emp
?? for each row
?? begin
???? update emp set depno=:new.deptno where dentno=:old.deptno;
?? end;
4 建立instead of 觸發(fā)器
? 對于簡單視圖可以直接進行insert update 和delete 等操作，但是對于復(fù)雜視圖不允許進行insert，update 和delete 操作。
? 滿足一下條件的為復(fù)雜視圖
??? 具有操作集合符 union，union all ，intersect，minus
??? 具有分組函數(shù) min，max，avg，sum，count
??? 具有g(shù)roup by connect 編譯 或start with
??? 具有distinct
??? 具有連接
? 為了在復(fù)雜視圖上執(zhí)行dml 操作，必須要基于instead-of 觸發(fā)器，建立instead－of 觸發(fā)器后，就可以基于復(fù)雜視圖執(zhí)行insert，update和delete 語句。
?? instead of 選項只使用于視圖
?? 基于視圖建立觸發(fā)器時，不能定義before 和 after
?? 在建立視圖時不能指定 with check option
?? 當(dāng)建立instead of 觸發(fā)器時，必須指定for each row 選項
? 1) 建立復(fù)雜視圖dept_emp
?? create or replace view dept_emp as
?? select a.deptno,a.dname,b,empno,b,ename
?? from dept a,emp b
?? where a,deptno=b.deptno;
? 2) 建立 instead－of 觸發(fā)器
?? create of replacee trigger tr_instead_of_dept_emp
?? instead of insert on dept_emp
?? for each row
?? declare
???? v_temp int;
?? beegin
????? select count(*) into v_temp from dept where deptno=:new.deptno;
????? if v_temp=0 then
??????? insert into dept(deptno,dname) values(:new.deptno,:new.dname);
????? end if;
????? select count(*)into v_temp from emp where empno=:new.empno;
????? if v_temp=0 then
???????? insert into emp(empno,ename,deptno)
?????????? values(:new.deptno,:new.ename,:new.deptno);
???????? end if;
?? end;
??
?? 可以對視圖執(zhí)行insert 操作了
??? insert into dept_emp values(50,'admin','1223','mary')
5 管理觸發(fā)器
? 1) 顯示觸發(fā)器信息
??? select trigger_name,status from user_triggers
??? where table_name='emp';
?? 2)禁止觸發(fā)器
??? alter trigger tr_check_sal disable;
?? 3) 激活觸發(fā)器
??? alter trigger tr_check_sal enable;
?? 4) 禁止或激活表上的所有觸發(fā)器
??? alter table emp disable all triggers;
??? alter table emo eanble all triggers;?
?? 5)重新編譯觸發(fā)器
??? alter trigger tr_check_sal compile;
?? 6) 刪除觸發(fā)器
??? drop trigger tr_check_sal;

posted @ 2006-10-11 14:44 康文 閱讀(1022) | 評論 (0) | 編輯 收藏

使用復(fù)合變量

posted @ 2006-10-11 14:44 康文 閱讀(346) | 評論 (0) | 編輯 收藏

advanced subquery

posted @ 2006-10-11 14:43 康文 閱讀(264) | 評論 (0) | 編輯 收藏

開發(fā)動態(tài)sql

1 動態(tài)sql 簡介
2
? 1 使用execute immediate 處理ddl 操作
??? create or replacee procedure drop_table(table_name varchar2)
??? is
???? sql_statement varchar2(100);
??? begin
?????? sql_statement:='drop table'||table_name;
?????? execute immediate sql_statement;
?? 調(diào)用
?????? exec drop_table('worker');
??? end;
??? 2) 使用 execute immediate 處理dcl 操作
??? create or replace procedure grant_sys_priv
??? (priv varchar2,username varchar2)
??? is
??? begin
???? sql_stat:='gruant'||priv||'to'||username;
???? execute immediate sql_stat;
??? end;

?? exec grant_sys_priv('create session','scott');
?? 3 使用execute immediate 處理dml 操作
???? 1) 處理無占位符和returning 子句的dml 語句
???? delcare
????? sql_stat varchar2(100);
???? begin
????? sql_stat:='update emp set sal=sal*1.1 where deptno=44';
????? execute immediate sql_stat;
???? end;
????? 2) 處理包含占位符的dml語句
?????? delare
??????? sql_stat varchar2(100);
?????? begin
???????? sql_stat:='update emp set sql=sql*(1+:percent/100)'
???????????????? ||'where deptno=:dno';
???????? execute immediate sql_stat using &1,&2;
?????? end;
????? 3) 處理包含returning 子句的dml語句
?????? declare
???????? salary number(6,2);
???????? sql_stat varchar2(200);
?????? begin
???????? sql_stat:='update emp set sal=sal*(1:percent/100)'
??????????? ||'where empno=:eno returning sal into :salary';
???????? execute immediate sql_stat using &1,&2;
??????????? returning into salary;
?????? end;
?????? 輸入1的值 15
?????? 輸入2的值 2222
?????? 新工資；2223
????? 4) 使用execute immediate 處理單行查詢
??????? declare
????????? sql_stat varcchar2(100);
????????? emp_record emp%rowtype;
??????? begin
????????? sql_stat:='select * from emp where empno=:eno';
????????? execute immediate sql_stat into emp_record using &1;
?????? end;
?3 處理多行查詢語句
?? declare
????? type empcurtyp is ref cursor;
????? emp_cv empcurtyp;
????? emp record emp%rowtype;
????? sql_stat varchar2(100);
?? begin
????? sql_stat:='select * from em where deptno=:dno';
????? open emp_cv for sql_stat using &dno;
????? loop
???????? fetch emp_cu into emp_record;
???????? exit when emp_cv%notfound;
????? end loop;
????? close emp_cv;
?? end;
4 在動態(tài)sql 中使用bulk語句
?? 1) 在 execute immediate 語句中使用動態(tài)bulk 語句
???? declare
?????? type ename_table_type is table of emp.ename%type
??????? index by binary_integer;
?????? type sal_table_type is table of emp.sal%type
??????? index by binary_integer;
?????? ename_table ename_table_type;
?????? sa_table sal_table_type;
?????? sal_stat varchar2(100);
?????? begin
???????? sql_stat:='update emp set sal=sal*(1+:percent/100)'
?????????? || 'where deptno=:dno'
?????????? ||'returning ename,sal into :name,:salary';
?????? execut immediate sql_stat using &percent,&dno
???????? returning bulk collect into ename_table,sal_table;
?????? for i in 1..ename_table.count loop
?????? ....
?????? end loop;
???? end;
??? 2) 使用bulk 子句處理多行查詢
????? sql_stat:='select ename from emp where deptno=:dno';
????? execute immediate sql_stat bulk collect into ename_table using &dno;
??? 3) 在fetch 語句中使用bulk 子句
????? declare
??????? type empcurtyp is ref cursor;
??????? emp_cv empcurtyp;
??????? type ename_table_type is table of emp.ename%type;
???????? index by binary_integer;
??????? ename_table ename_table_type;
??????? sql_stat varchar2(100);
?????? begin
???????? sql_stat:='select ename from emp where job:=title';
???????? open emp_cv for sql_stat using '&job';
???????? fetch emp_cv bulk collect into ename_table;
??? 4) 在forall 語句中使用bulk 子句
????? declare
??????? type ename_table_type is table of emp.ename%type;
??????? type sla_table_type is table of emp.sal%type;
??????? ename_table ename_table_type;
??????? sal_table sal_table_type;
??????? sql_stat varchar2(100);
????? begin
??????? ename_table:=ename_table_type('scott','smith','clark');
??????? sql_stat:='update emp set sal=sal*1.1 where ename=:1'
??????????? ||'returning sal into :2';
??????? forall i in 1..ename_table.count
????????? execite immediate sql_stat using ename_table(i)
??????????? returning bulk collect into sal_table;???????
????? end;

posted @ 2006-10-11 14:43 康文 閱讀(479) | 評論 (0) | 編輯 收藏

managing an oracle instance

posted @ 2006-10-11 14:42 康文 閱讀(360) | 評論 (0) | 編輯 收藏

archetecture query

1 hierachical Queries
?select [level],colun,expr......
?from table
?[where condition(s)]
?[start with condition(s)]
?[connect by prior condition(s)]
?
?where condition
? exprcompparison_operator expr

2 Starting Point
?.Specifies the condition that must be met
?.Accepts any valid condition
3 Waling the Tree
? connect by prior column1=column2
? walk from the top donw ,using the employees table
?? top donw
???? column1=parentkey
???? column2=childkey
?? bottom up
???? column1=child key
???? column2=parent key

? select level,last_name
? from employees
? start with last_name='king'
? connect by prior employee_id=manager_id;

posted @ 2006-10-11 14:42 康文 閱讀(187) | 評論 (0) | 編輯 收藏

extension to dml in oracle

posted @ 2006-10-11 14:41 康文 閱讀(200) | 評論 (0) | 編輯 收藏

Managing Passswordd Security and Resources

1 Profiles
? 1)a profile is a named set of password and resource limits
? 2)Profiles are assigned to user by the create user or alter user command
? 3)can be enable or disable
? 4)can relate to the default profile.
2 Password Management
? Password history,account locking,password expiration and aging ,password verificcation.
3Enabling Password Management
? 1)set up passwordd management by using profiles and assign them to users
? 2)lock unlock and expire accounts usign the create user or alter user
? 3)alwarys enforcing
?? e.g
?? create user test identified by test;
?? alter user test account lock;
?? alter user test account unlock;
?? alteer user test password expire;
4 Passowrd Account locking
? FAIKED_LOGIN_ATTEMPS: number of failed login attemts before lockout of the account
? PASSWORD_LOCK_TIME : number of days the account is locked after the specified number of??????????????????????? failed login attemps
? e.g
? create profile profile1 limit
? password_lock_time 1/1440? -- one muinuts
? failed_login_attempts 3;
?
? alter user test
? profile profile1;

? alter profile profile1 limit
? passowrd_lock_time 1/24? --one hour
5 passowrd expiration and aging
?passwowd_life_time lifetime of the passowrd in days? after which the password expires(有效期)
?password_grace_time grace period in days for changing the password after the first????????????????????? successful login afteer the password has expired(鎖定期)
?e.g
?alter profile profile1 limit
?password_life_time 2
?password_grace_time 3;
6 password history
?password_reuse_time:number of days before a passowrd and be resued
?password _reuse_max:maxum number of times password can bee reused
?e.g
?alter profile profile1 limit
?password_reuse_time 10
?password_reuse_max 3;
7passowrd Verification(study latter)
8drop a profile
?drop profile profile1 (cascade);
the user will use the default profile.it will take effect on the new session.
9 Resource Management
?Resource mangement limits can be enforced at the session level,the call level or both
?limits can be defined by profiles using the create profile command
?enable resource limints with the
? .resource_limit initialization parameter
?? alter system command
?e.g
?alter system set resource_limit=true;
10 setting a Resdource limits at session level
? cup_per_session : total cpu time measured in hundredths of seconds (百分之一秒)
? sessions_per_user: number of coucurrent sessions allowed for each username
? connect_time:elapsed connect time measured in minutes
? idle_time :periods of inactive time measured in minutes
? logical_reads_per_session: number of data blocks
? private_sga :measure in reads
? e.g
? alter profile profile1 limit
? cpu_per_session 100000
? connect_time 60
? idle_time 5;
?
? alter user test profile profile1

11 Setting Resource limits at call level?
?e.g
?alter profile profile1
?cpu_per_call 1000? -- cup time per call in
?logical_reads_per_call --number of data balock that can be read per call
?
? create profile develper_prof limit
?? session_per_user2
?? cpu_per_session 10000
?? idle_time 60
?? connect_time 480
12 Managing Resources using database resource manager
?? 1)Provides the oracle server with more control over resorce management decisions
?? 2)elements of database resorcee manager
??? resource consumer group
??? resourcee plan
??? resource allocation method
??? resource plan directives
?? 3)dbms_resource_manager package is uerd to create and maintain elements
?? 4)requires administer_resource_manager privilege
??? desc dbms_resoource_manager
13 0btaining password and resource limits informaiton
? information about password and resource limits can be obtained by querying the data dictonary
?? dba_users
?? select * from users;
?? dba_profiles
?? select * from dba_profiles where profile='PROFILE1'

posted @ 2006-10-11 14:41 康文 閱讀(212) | 評論 (0) | 編輯 收藏

jdbc-batch processing

the addBatch() method is basically nothing more than a tool fro assigning a bunch of sql statements to a jdbc statement object for execution together

PreparedStatement stmt=conn.prepareStatement(
????? "update account set balance=? where acct_id=?");
int[] rows;
for(int i=0;i<accts.length;i++){
??? stmt.setInt(1,i);
??? stmt.setLong(2,i);
??? stmt.addBatch();
? }
rows=stemt.executeBatch();

posted @ 2006-10-11 14:40 康文 閱讀(210) | 評論 (0) | 編輯 收藏

翻頁的實現(xiàn)

1 oracle 的實現(xiàn)
?語句一
SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 條件1 ORDER BY 條件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 條件3;
語句二：
SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 條件1 ORDER BY 條件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 條件3;

select * from (select rownum as numrow from table_name where numrow>80 and numrow<100 )
不能直接使用 select * from rownum>100 and rownum<200;
in oracle return null;
2 sql server 的實現(xiàn)
3 mysql 的實現(xiàn)

select id from table_name where id in
?????????????????????????????????? select * from (select rownum as numrow ,id from tabl_name)
???????????????????????????????????????????? where numrow>80 and num<100;???????????????????????????????????????????
????????????????????????????????????????????????

posted @ 2006-10-11 14:39 康文 閱讀(188) | 評論 (0) | 編輯 收藏

jdbc-prepare sql

posted @ 2006-10-11 14:38 康文 閱讀(378) | 評論 (0) | 編輯 收藏

java 調(diào)用存儲過程 轉(zhuǎn)載

本文闡述了怎么使用DBMS存儲過程。我闡述了使用存儲過程的基本的和高級特性，比如返回ResultSet。本文假設(shè)你對DBMS和JDBC已經(jīng)非常熟悉，也假設(shè)你能夠毫無障礙地閱讀其它語言寫成的代碼（即不是Java的語言），但是，并不要求你有任何存儲過程的編程經(jīng)歷。
存儲過程是指保存在數(shù)據(jù)庫并在數(shù)據(jù)庫端執(zhí)行的程序。你可以使用特殊的語法在Java類中調(diào)用存儲過程。在調(diào)用時，存儲過程的名稱及指定的參數(shù)通過JDBC連接發(fā)送給DBMS，執(zhí)行存儲過程并通過連接（如果有）返回結(jié)果。
使用存儲過程擁有和使用基于EJB或CORBA這樣的應(yīng)用服務(wù)器一樣的好處。區(qū)別是存儲過程可以從很多流行的DBMS中免費使用，而應(yīng)用服務(wù)器大都非常昂貴。這并不只是許可證費用的問題。使用應(yīng)用服務(wù)器所需要花費的管理、編寫代碼的費用，以及客戶程序所增加的復(fù)雜性，都可以通過DBMS中的存儲過程所整個地替代。
你可以使用Java，Python，Perl或C編寫存儲過程，但是通常使用你的DBMS所指定的特定語言。Oracle使用PL/SQL，PostgreSQL使用pl/pgsql，DB2使用Procedural SQL。這些語言都非常相似。在它們之間移植存儲過程并不比在Sun的EJB規(guī)范不同實現(xiàn)版本之間移植Session Bean困難。并且，存儲過程是為嵌入SQL所設(shè)計，這使得它們比Java或C等語言更加友好地方式表達數(shù)據(jù)庫的機制。
因為存儲過程運行在DBMS自身，這可以幫助減少應(yīng)用程序中的等待時間。不是在Java代碼中執(zhí)行4個或5個SQL語句，而只需要在服務(wù)器端執(zhí)行1個存儲過程。網(wǎng)絡(luò)上的數(shù)據(jù)往返次數(shù)的減少可以戲劇性地優(yōu)化性能。

使用存儲過程

簡單的老的JDBC通過CallableStatement類支持存儲過程的調(diào)用。該類實際上是PreparedStatement的一個子類。假設(shè)我們有一個poets數(shù)據(jù)庫。數(shù)據(jù)庫中有一個設(shè)置詩人逝世年齡的存儲過程。下面是對老酒鬼Dylan Thomas（old soak Dylan Thomas，不指定是否有關(guān)典故、文化，請批評指正。譯注）進行調(diào)用的詳細代碼：

try{
int age = 39;

String poetName = "dylan thomas";

CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");

proc.setString(1, poetName);

proc.setInt(2, age);

cs.execute();

}catch (SQLException e){ // ....}

傳給prepareCall方法的字串是存儲過程調(diào)用的書寫規(guī)范。它指定了存儲過程的名稱，？代表了你需要指定的參數(shù)。
和JDBC集成是存儲過程的一個很大的便利：為了從應(yīng)用中調(diào)用存儲過程，不需要存根（stub）類或者配置文件，除了你的DBMS的JDBC驅(qū)動程序外什么也不需要。
當(dāng)這段代碼執(zhí)行時，數(shù)據(jù)庫的存儲過程就被調(diào)用。我們沒有去獲取結(jié)果，因為該存儲過程并不返回結(jié)果。執(zhí)行成功或失敗將通過例外得知。失敗可能意味著調(diào)用存儲過程時的失敗（比如提供的一個參數(shù)的類型不正確），或者一個應(yīng)用程序的失敗（比如拋出一個例外指示在poets數(shù)據(jù)庫中并不存在“Dylan Thomas”）

結(jié)合SQL操作與存儲過程

映射Java對象到SQL表中的行相當(dāng)簡單，但是通常需要執(zhí)行幾個SQL語句；可能是一個SELECT查找ID，然后一個INSERT插入指定ID的數(shù)據(jù)。在高度規(guī)格化（符合更高的范式，譯注）的數(shù)據(jù)庫模式中，可能需要多個表的更新，因此需要更多的語句。Java代碼會很快地膨脹，每一個語句的網(wǎng)絡(luò)開銷也迅速增加。
將這些SQL語句轉(zhuǎn)移到一個存儲過程中將大大簡化代碼，僅涉及一次網(wǎng)絡(luò)調(diào)用。所有關(guān)聯(lián)的SQL操作都可以在數(shù)據(jù)庫內(nèi)部發(fā)生。并且，存儲過程語言，例如PL/SQL，允許使用SQL語法，這比Java代碼更加自然。下面是我們早期的存儲過程，使用Oracle的PL/SQL語言編寫：

create procedure set_death_age(poet VARCHAR2, poet_age NUMBER)

poet_id NUMBER;

begin SELECT id INTO poet_id FROM poets WHERE name = poet;

INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age);

end set_death_age;

很獨特？不。我打賭你一定期待看到一個poets表上的UPDATE。這也暗示了使用存儲過程實現(xiàn)是多么容易的一件事情。set_death_age幾乎可以肯定是一個很爛的實現(xiàn)。我們應(yīng)該在poets表中添加一列來存儲逝世年齡。Java代碼中并不關(guān)心數(shù)據(jù)庫模式是怎么實現(xiàn)的，因為它僅調(diào)用存儲過程。我們以后可以改變數(shù)據(jù)庫模式以提高性能，但是我們不必修改我們代碼。
下面是調(diào)用上面存儲過程的Java代碼：

public static void setDeathAge(Poet dyingBard, int age) throws SQLException{

Connection con = null;

CallableStatement proc = null;

try {

con = connectionPool.getConnection();

proc = con.prepareCall("{ call set_death_age(?, ?) }");

proc.setString(1, dyingBard.getName());

proc.setInt(2, age);

proc.execute();

}?

finally {

try { proc.close(); }

catch (SQLException e) {}

con.close();

}

}

為了確保可維護性，建議使用像這兒這樣的static方法。這也使得調(diào)用存儲過程的代碼集中在一個簡單的模版代碼中。如果你用到許多存儲過程，就會發(fā)現(xiàn)僅需要拷貝、粘貼就可以創(chuàng)建新的方法。因為代碼的模版化，甚至也可以通過腳本自動生產(chǎn)調(diào)用存儲過程的代碼。

Functions

存儲過程可以有返回值，所以CallableStatement類有類似getResultSet這樣的方法來獲取返回值。當(dāng)存儲過程返回一個值時，你必須使用registerOutParameter方法告訴JDBC驅(qū)動器該值的SQL類型是什么。你也必須調(diào)整存儲過程調(diào)用來指示該過程返回一個值。
下面接著上面的例子。這次我們查詢Dylan Thomas逝世時的年齡。這次的存儲過程使用PostgreSQL的pl/pgsql：

create function snuffed_it_when (VARCHAR) returns integer ''declare

poet_id NUMBER;

poet_age NUMBER;

begin

--first get the id associated with the poet.

SELECT id INTO poet_id FROM poets WHERE name = $1;

--get and return the age.

SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id;

return age;

end;'' language ''pl/pgsql'';

另外，注意pl/pgsql參數(shù)名通過Unix和DOS腳本的$n語法引用。同時，也注意嵌入的注釋，這是和Java代碼相比的另一個優(yōu)越性。在Java中寫這樣的注釋當(dāng)然是可以的，但是看起來很凌亂，并且和SQL語句脫節(jié)，必須嵌入到Java String中。
下面是調(diào)用這個存儲過程的Java代碼：

connection.setAutoCommit(false);

CallableStatement proc = connection.prepareCall("{ ? = call snuffed_it_when(?) }");

proc.registerOutParameter(1, Types.INTEGER);

proc.setString(2, poetName);

cs.execute();

int age = proc.getInt(2);

如果指定了錯誤的返回值類型會怎樣？那么，當(dāng)調(diào)用存儲過程時將拋出一個RuntimeException，正如你在ResultSet操作中使用了一個錯誤的類型所碰到的一樣。

復(fù)雜的返回值

關(guān)于存儲過程的知識，很多人好像就熟悉我們所討論的這些。如果這是存儲過程的全部功能，那么存儲過程就不是其它遠程執(zhí)行機制的替換方案了。存儲過程的功能比這強大得多。
當(dāng)你執(zhí)行一個SQL查詢時，DBMS創(chuàng)建一個叫做cursor（游標(biāo)）的數(shù)據(jù)庫對象，用于在返回結(jié)果中迭代每一行。ResultSet是當(dāng)前時間點的游標(biāo)的一個表示。這就是為什么沒有緩存或者特定數(shù)據(jù)庫的支持，你只能在ResultSet中向前移動。
某些DBMS允許從存儲過程中返回游標(biāo)的一個引用。JDBC并不支持這個功能，但是Oracle、PostgreSQL和DB2的JDBC驅(qū)動器都支持在ResultSet上打開到游標(biāo)的指針（pointer）。
設(shè)想列出所有沒有活到退休年齡的詩人，下面是完成這個功能的存儲過程，返回一個打開的游標(biāo)，同樣也使用PostgreSQL的pl/pgsql語言：

create procedure list_early_deaths () return refcursor as ''declare

toesup refcursor;

begin

open toesup for SELECT poets.name, deaths.age FROM poets, deaths -- all entries in deaths are for poets. -- but the table might become generic.

WHERE poets.id = deaths.mort_id AND deaths.age < 60;

return toesup;

end;'' language ''plpgsql'';

下面是調(diào)用該存儲過程的Java方法，將結(jié)果輸出到PrintWriter：
PrintWriter:

static void sendEarlyDeaths(PrintWriter out){

Connection con = null;

CallableStatement toesUp = null;

try {

con = ConnectionPool.getConnection();

// PostgreSQL needs a transaction to do this... con.

setAutoCommit(false); // Setup the call.

CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }");

toesUp.registerOutParameter(1, Types.OTHER);

toesUp.execute();

ResultSet rs = (ResultSet) toesUp.getObject(1);

while (rs.next()) {

String name = rs.getString(1);

int age = rs.getInt(2);

out.println(name + " was " + age + " years old.");

}

rs.close();

}

catch (SQLException e) { // We should protect these calls. toesUp.close(); con.close();

}

}

因為JDBC并不直接支持從存儲過程中返回游標(biāo)，我們使用Types.OTHER來指示存儲過程的返回類型，然后調(diào)用getObject()方法并對返回值進行強制類型轉(zhuǎn)換。
這個調(diào)用存儲過程的Java方法是mapping的一個好例子。Mapping是對一個集上的操作進行抽象的方法。不是在這個過程上返回一個集，我們可以把操作傳送進去執(zhí)行。本例中，操作就是把ResultSet打印到一個輸出流。這是一個值得舉例的很常用的例子，下面是調(diào)用同一個存儲過程的另外一個方法實現(xiàn)：

public class ProcessPoetDeaths{

public abstract void sendDeath(String name, int age);

}

static void mapEarlyDeaths(ProcessPoetDeaths mapper){

Connection con = null;

CallableStatement toesUp = null;

try {

con = ConnectionPool.getConnection();

con.setAutoCommit(false);

CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }");

toesUp.registerOutParameter(1, Types.OTHER);

toesUp.execute();

ResultSet rs = (ResultSet) toesUp.getObject(1);

while (rs.next()) {

String name = rs.getString(1);

int age = rs.getInt(2);

mapper.sendDeath(name, age);

}

rs.close();

} catch (SQLException e) { // We should protect these calls. toesUp.close();

con.close();

}

}

這允許在ResultSet數(shù)據(jù)上執(zhí)行任意的處理，而不需要改變或者復(fù)制獲取ResultSet的方法：

static void sendEarlyDeaths(final PrintWriter out){

ProcessPoetDeaths myMapper = new ProcessPoetDeaths() {

public void sendDeath(String name, int age) {

out.println(name + " was " + age + " years old.");

}

};

mapEarlyDeaths(myMapper);

}

這個方法使用ProcessPoetDeaths的一個匿名實例調(diào)用mapEarlyDeaths。該實例擁有sendDeath方法的一個實現(xiàn)，和我們上面的例子一樣的方式把結(jié)果寫入到輸出流。當(dāng)然，這個技巧并不是存儲過程特有的，但是和存儲過程中返回的ResultSet結(jié)合使用，是一個非常強大的工具。

結(jié)論

存儲過程可以幫助你在代碼中分離邏輯，這基本上總是有益的。這個分離的好處有：
&#8226; 快速創(chuàng)建應(yīng)用，使用和應(yīng)用一起改變和改善的數(shù)據(jù)庫模式。
&#8226; 數(shù)據(jù)庫模式可以在以后改變而不影響Java對象，當(dāng)我們完成應(yīng)用后，可以重新設(shè)計更好的模式。
&#8226; 存儲過程通過更好的SQL嵌入使得復(fù)雜的SQL更容易理解。
&#8226; 編寫存儲過程比在Java中編寫嵌入的SQL擁有更好的工具－－大部分編輯器都提供語法高亮！
&#8226; 存儲過程可以在任何SQL命令行中測試，這使得調(diào)試更加容易。

并不是所有的數(shù)據(jù)庫都支持存儲過程，但是存在許多很棒的實現(xiàn)，包括免費/開源的和非免費的，所以移植并不是一個問題。Oracle、PostgreSQL和DB2都有類似的存儲過程語言，并且有在線的社區(qū)很好地支持。
存儲過程工具很多，有像TOAD或TORA這樣的編輯器、調(diào)試器和IDE，提供了編寫、維護PL/SQL或pl/pgsql的強大的環(huán)境。
存儲過程確實增加了你的代碼的開銷，但是它們和大多數(shù)的應(yīng)用服務(wù)器相比，開銷小得多。如果你的代碼復(fù)雜到需要使用DBMS，我建議整個采用存儲過程的方式。

posted @ 2006-10-11 14:37 康文 閱讀(194) | 評論 (0) | 編輯 收藏

披著盛裝的稻草人-- 編程中的隨筆

1 不是使用了spring ，hibernate 等企業(yè)級產(chǎn)品的框架，我們就是企業(yè)級產(chǎn)品了。不是我們采用了新瓶裝舊酒的web 2.0 我們就走在技術(shù)的前沿了。我門所需要的是一個高性能的，健壯的 產(chǎn)品，是一個可以降低我們實施成本，一個可以樹立我們企業(yè)品牌的產(chǎn)品。在這里我不得不對我們產(chǎn)品的所謂的架構(gòu)們產(chǎn)品疑問，Archetectures,what are you doing？

2 在實現(xiàn)框架代碼的時候，當(dāng)你對采用那種實現(xiàn)方式猶豫不決的時，換個角度，想一想如果你是程序員，喜歡怎么這些框架。在實現(xiàn)框架的時候一定要考慮程序員是否能夠理解你寫框架的思路，除非萬不得已不要用一些自以為很高明很巧妙，然而卻很晦澀難懂的方法，那樣的框架，程序員至少合格的程序員是不愿意使用的。我想程序員和編碼工人最大的區(qū)別就是程序員不僅要知其然，還要知其所以然。

3 只有在不斷實踐中，才能激發(fā)你不斷的求知欲。只有把學(xué)到的知識不斷的應(yīng)用道實踐中，你才能在學(xué)習(xí)中得到滿足。不要為了學(xué)習(xí)而學(xué)習(xí)（學(xué)院派，不好聽點就是紙上談兵），而是要從實際問題出發(fā)，在解決問題的過程中不斷深入，不斷總結(jié)，所以說，當(dāng)你離開了編程的第一線，你將失去學(xué)習(xí)編程知識的欲望。當(dāng)然如果你愿意，在別的領(lǐng)域還有更廣闊的天空，但是請不要總是說自己原來編程怎么怎么，其實你已經(jīng)被三振出局了。

4 想外行一樣思考，想專家一樣實踐，一本書的名字，雖然書沒有看過，但她的名子就已經(jīng)非常有意思了。這豈不就是我們作需求，和作架構(gòu)時的座右銘嗎？既能象“外行”一樣的站在客戶的角度思考問題，又能象“專家”一樣參與到整個產(chǎn)品的開發(fā)和實施當(dāng)中，在實踐中不斷提高自我。然而，不幸的是許許多多的所謂的架構(gòu)師，系統(tǒng)分析員們卻正向著相反的方向邁進。“真正”的做到了，象“專家”一樣思考，象“外行”一樣實踐，可悲呀可悲。
5設(shè)計做到什么樣才叫做到位呢。我想只有真正的開發(fā)者才有權(quán)利發(fā)言。只有有它們才是設(shè)計的真正使用者和受害者。因為就我所知和所見，絕大多數(shù)設(shè)計都是設(shè)計者自己的游戲（當(dāng)然，我可能是井底之蛙了沒有見過什么好的設(shè)計），程序員所開發(fā)往往還是對著原形自己再進行一遍設(shè)計，且不說額外增加了多少工作量，浪費了多少時間，就工作質(zhì)量而言，也是差強人意。畢竟大多數(shù)情況下，設(shè)計者或稱為架構(gòu)師的在技術(shù)方面的經(jīng)驗都更為豐富，對業(yè)務(wù)的理解也更為深入，另外由一個人進行設(shè)計在功能復(fù)用，和整體性能方面的考慮也更完整一些。但怎么做才能熊掌和魚兼得呢？下面我發(fā)表一下我個人的看法：
? 1 代碼就是最好的設(shè)計，這句話不是我說的，是 xp開發(fā)屆 中的一位大牛說的。之所以在這里引用別人的觀點，并不是自己是一個xp 的fans，也并不時完全贊同xp 的理論，我只是覺得這句話得太對了，對程序員來說什么設(shè)計比代碼讀起來更親切呢？。其實設(shè)計無非是向開發(fā)所著傳達設(shè)計者的思想，告訴開發(fā)者系統(tǒng)需要開什么個對象，具有什么屬性和行為，它們之間的調(diào)用關(guān)系又如何。我們在設(shè)計文檔中經(jīng)常使用的方法就是有class 圖，協(xié)作圖，和順序圖對上面所提到的進行描述。然而結(jié)果呢，面對這大量的令人畏懼的抽象圖表，開發(fā)者可選擇的也只有是“重整江河待后生了”。想想，這樣的設(shè)計和代碼能夠同步嗎，這樣的設(shè)計文檔還有什么用呢？所以說與其是這樣還不如把設(shè)計變成代碼，如對象屬性可以這直接在代碼中體現(xiàn)，方法可以只定義接口，實現(xiàn)方式可以作為代碼的注釋，向?qū)懶枨蠓治鲇美频膩硪徊揭徊秸f明程序是需要怎樣調(diào)用。當(dāng)客戶要求設(shè)文檔的時候，只需要提出javadoc就可以了，而其保證和代碼同步。而開發(fā)者呢，在開發(fā)前需要閱讀用例，了解需求，然后在設(shè)計者已經(jīng)搭好的代碼框架中進行開發(fā)就可以了。如果需要修改的話，不用在去設(shè)計文檔中更改，只需要修改一下代碼注釋就可以了，（程序員是比較懶的，不怎么愿意寫寫文檔的）。當(dāng)然了，讓懶惰的程序員能夠自覺地寫好文檔也不是一件容易事，下面也許能給你提供一個好的方法
? 2 交差開發(fā)能夠幫助完成最好的設(shè)計文檔。
? 3 設(shè)計者在開發(fā)階段還作什么呢？?????????????????
待續(xù)???????????????????????????????????????????????????????????????

posted @ 2006-10-11 14:36 康文 閱讀(242) | 評論 (0) | 編輯 收藏

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 康文 閱讀(239) | 評論 (0) | 編輯 收藏

database link 使用 轉(zhuǎn)載

鏈接到遠程數(shù)據(jù)庫

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

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

今天許多運行Oracle的機構(gòu)有不止一個Oracle數(shù)據(jù)庫。有時不管原計劃是否這樣，一個數(shù)據(jù)庫中的數(shù)據(jù)可能與另一數(shù)據(jù)庫中的數(shù)據(jù)關(guān)聯(lián)。出現(xiàn)這種情況時，你可以鏈接這兩個數(shù)據(jù)庫使得用戶或應(yīng)用程序可以訪問所有數(shù)據(jù)，就好象它們在一個數(shù)據(jù)庫中。當(dāng)你這么做時，你就有了一個分布式數(shù)據(jù)庫系統(tǒng)。

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

簡單案例

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

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

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

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

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

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

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表是在遠程數(shù)據(jù)庫，應(yīng)該通過GNIS鏈接訪問，鏈接類型Oracle支持幾種不同類型的鏈接。這些類型相互重疊，有時難以通過選項進行分類。當(dāng)你建立數(shù)據(jù)庫鏈接時，你需要從下面選取：

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

權(quán)限類: Fixed User（固定用戶）, Connected User（連接用戶）或 Current User（當(dāng)前用戶）
Shared Link（共享鏈接）或 Not Shared Link（非共享鏈接）
每次創(chuàng)建數(shù)據(jù)庫鏈接時，你要自覺不自覺地做這三種選擇。

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

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

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

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

使用數(shù)據(jù)庫鏈接訪問遠程表

圖1 數(shù)據(jù)庫鏈接GNIS，指明網(wǎng)絡(luò)服務(wù)名稱，鏈接PROD事例到GNIS事例中的FEATURE_NAMES表。

權(quán)限類

當(dāng)你建立一個數(shù)據(jù)庫鏈接時，關(guān)于你如何授權(quán)對遠程數(shù)據(jù)庫進行訪問，有三種選擇。這三種選擇代表了數(shù)據(jù)庫鏈接的另一種分類方法。這三種類別如下：

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

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

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

CREATE PUBLIC DATABASE LINK GNIS

USING 'GNIS';

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

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

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

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

共享鏈接

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

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

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

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

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

查找關(guān)于數(shù)據(jù)庫鏈接的資料

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

全局性的數(shù)據(jù)庫名稱

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

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
_______________
GNIS.GENNICK.ORG

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

SQL> SHOW PARAMETER GLOBAL_NAMES

NAME TYPE VALUE
________________________________________________________
global_names boolean TRUE

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

posted @ 2006-09-29 15:35 康文 閱讀(2147) | 評論 (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)基于函數(shù)的索引
? . 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 康文 閱讀(191) | 評論 (0) | 編輯 收藏

使用游標(biāo)

posted @ 2006-09-28 15:32 康文 閱讀(153) | 評論 (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 康文 閱讀(271) | 評論 (0) | 編輯 收藏

使用復(fù)合變量.

1 pl/sql 集合 處理單列多行數(shù)據(jù)庫，使用的類型為標(biāo)量類型
?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) 嵌套表
????? 索引表類型不能作為累得數(shù)據(jù)類型使用，但是嵌套表可以作為表類的數(shù)據(jù)類型使用。
當(dāng)使用嵌套表元素時，必須先用其構(gòu)造方法初始化其嵌套表：
?????? 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;
?????? -- 為嵌套表插入數(shù)據(jù)
??????? insert into employee values(2,'scott',200,phone_type('2222','333333'));
?????? －－檢索嵌套表累得數(shù)據(jù)
??????? 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;
?????? -- 更新嵌套表列的數(shù)據(jù)
???????? delcare
??????????? phone_table phone_type:=('44444','555555');
???????? begin
??????????? update employee set phone=phone_table
??????????? where id=1;
???????? end;
??? 3) 變長數(shù)組
????? 在使用varray 時必須指定最大個數(shù)，和數(shù)據(jù)類型,在使用其元素時必須進行初始化
????? 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)記錄表
????? 記錄表結(jié)合了記錄和集合的優(yōu)點
??????? 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 返回當(dāng)前集合變量中的元素總個數(shù)
??? ename_table.count
?? 3) limit 返回集合元素的最大個數(shù)? 只有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)將一個集合的數(shù)據(jù)賦值給另一個集合.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;
? 具有相同的數(shù)據(jù)類型，單具有不同的集合類型不能構(gòu)賦值
? 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 的內(nèi)容，p176 先略過。
4 批量綁定
? 執(zhí)行單詞sql 操作能傳遞所有集合元素的數(shù)據(jù)。
? 1 forall 語句
? 用于insert update 和delete操作。在oracle9i 中forall 語句必須具有連續(xù)的元素
??? 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 康文 閱讀(202) | 評論 (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 康文 閱讀(224) | 評論 (0) | 編輯 收藏

OpenSessionInViewFilter解決Web應(yīng)用程序的問題 轉(zhuǎn)自：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 康文 閱讀(553) | 評論 (0) | 編輯 收藏

creating and manipulation table

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

Data manipulation

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

sequence 的用法

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

subqueries

1 Guidelines for Using Subqueries
?a Enclose subqueries in parenttheses
?b placce subqueries on the right side of the comparision condition
?c the order by clause in the subquery is not needed
?d using single-row operators with single-row subqueries and use multiple -row operator with multiple-row subqueries .
? single-row subqueries can work as a expression,and muitiple-row subqueries can only be used with in all any ,i will talk it later
? select last_name where job_idd=(select job_id
????????????????????????????????? from employees
????????????????????????????????? where imployee_id=141)
2 The HAVING CLause with Subqueries
?a The Oracle server execute subqueries first
?b The Oracle return result into the HAVING clause of the main query
? select department_id,min(salary)
? from employee
? group by department_id
? having min(salary)>
???????????????????? (select min(salary)
????????????????????? from employees
????????????????????? where department_id=50);
3 Multiple-Row Subqueries
? a Return? more than one row
? Using mutiple-row comparsion operator
? select employee_id
? from employees
? where salary<any
????????????????? (select salary
?????????????????? from employees
?????????????????? where job_id='ddd')

? select employee_id
? from employees
? where salary<all
????????????????? (select salary
?????????????????? from employees
?????????????????? where job_id='ddd')
?? select emp.last_name
?? from employees emp
?? where emp.employee_id not in
?????????????????????????????? (select mgr.manager_id
??????????????????????????????? from employees mgr)

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

jointable

posted @ 2006-09-22 14:55 康文 閱讀(366) | 評論 (0) | 編輯 收藏

sql function

1 "'
2 ||
3 isql*plus? http://127.0.00.1/isqlplus
4 desc author
5 initcap('SQL Course')
? INSERT('JellwWord','W')? 6
?? LPAD (salary,10,'*')? *****24000
?? RPAD (salary,10,'*')? 24000*****
?? TRIM ('H' from 'HolloWorld') olloWord
?? substr('helloword',1,5)? hello
?? substr('helloword',-1,5) oword
6? Number Functions
?? round(45.926,2)?? 45.93
?? round(45.926,-2) 0
?? round(55.926,-2) 100
?? trunc(45.926,2)?? 45.92
?? mod(1600,300)? 100
7 data function
?? systdate
?? (sysdate-hire_date)/7 as weeks
?? months_between? number of months between two dates
?? months_between ('01-sep-95','11,jan-94')? 19.6774194
?? add_months????? add calendar months to date
?? add_months('11-JAN-94',6)? '11-JUL-94'
?? next_day??????? next day of the date specified
?? next_day('01-SEP-95','FRIDAY') '08-SEP-95'
?? last_day??????? last day of the month
?? last_day('01-feb-95')? '28-feb-95'
?? round?????????? round date
?? assume sysdate='25-jul-95'
?? round(sysdate,'month') 01-aug-95
?? round(sysdate,'year')? 01-JAN-96
?? trunc?????????? truncate date
?? trunc(sysdate,'month') 01-Jul-95
?? trunc(sysdate,'month') 01-JAN-95
8? Conversion Functions?
? 1) implicit data typ conversion
?? varchar2 or char? ---number
?? varchar2 or char? ---date
?? numbeer?????????? ---varchar2
?? date????????????? ---varchar2
? 2) to_char(date,'format')
? format:
??? YYYY Full year in numbers
??? YEAR Year spelled out
??? MM?? Two-digit value for month
??? MONTH Full name of the month
??? MON? THree-letter abbreviation of the month
??? DY?? Three-letter abbreviation of the day of the week
??? DAY? Full name of the day of hte week
??? DD?? Numberic day of the month
??? HH24:MI:SS AM? 15:45:32:PM
??? DD "of"? MONTH 12 of october
? 3) to_char function with number?
?? TO_CAHR(number,'format_model')
?? These are some of the format elements you can use with the to_char function to display number as a character.
??? 9 Reqresents a number
??? 0 Forces a zero to be displayed
??? $ Places a flationg dollar sign
??? L Uses the floating local currency symbol
??? . Prints a decimal point
??? , Print a thousand director
? select to_char(qtym,"$999.99")
? 4) Using t_number and to _date functions
?? a converting a character string to a number format using to_number function
?? to_number(char,"format")l
?? b converting a character string to a date format
?? to_date(char,"format")
5 Nesting Functions
.Single-row function can be nested to many level
.Nested function can be evaluated from deepest level
6General Function
These function work with any data type and pertain to using nulls
nvl(expr1,expr2);
nvl2(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2,,,,exprn)
?1) nvl function
?convert a null to an actual function
?a Data type can be used are data character and number
?b Data types must match??????????????????????????????
?(set wrap off
? set line 1000
?)
?2)Using the COALESCE Function
?a The advantage of the coalesce function over nal function is that coalesce function can take multiple alternative value
?b If the first value is not null, it return that expression,otherwise,it does a coalesce of remaining expressions
6 Conditional Expressions
?a Provide the use of if-then-else logic
?b use two methods: case expression decode function
? select last_name,job_id,salary,
???????? case job_id when 'it' then 1*salary
????????????????????? when 'manager' then 1.2*salary
???????? else salary end;
? from employee.

? select last_namek,job_id,salary,
???????? decode(job_id,'it' ,1*salary,
??????????????????????? 'manager',1.2*salary,
??????????????? salary)
?? from employees
??

posted @ 2006-09-22 11:50 康文 閱讀(366) | 評論 (0) | 編輯 收藏

Aggregating Datas Using Group Functionbs.

1 What Are Group Functions
Group functions operatee on sets of rows to give one result per group
?1)agg,count,max,min,stddev,sum,variance
?select avg(salary),max(salary),min(salary),sum(salary)
?from employees
?where job_id like '%REP%'

?select count(*) from
?select count(address) from authors
?count the valid count of the address (exclude the null value)
?2) Using theDISTINCT Keyword
? count(distinct expr) return thee number of the distinct non-null value of the expr
? select count(distincee department_id) from employees
?3)Group functions and null values
? group functions ignore null values in the clumn
?4) Using thee NVL Function with Group Functions
? The nul function force group funtion to include null values
? select avg(nvl(commission_pct,0)) from employees
2 Creating Groups of Data
? 1)
? a Divide rows in a table into smaller groups by using the group by clause
? b All coulmns in the select list that are not in group function must be in the group by clause
? select department_id,avg(salary)
? from employees
? group by department_id;
? 2) Grouping by More Than One Column
? 3) Ilegal Queries Using Group Functions
?? a You cannot use thee where clause to restrict groups
?? b You use thee having clause to restrict groups
?? c you cannot use group functions in the where clause
? 4)Excluding Group Resdults:The Having Clause
?? Use the HAVING clause to restrict groups
?? a Rows are grouped
?? b The group functions is applied
?? c Groups matcching the Having clause are display
? select department_id,max(salary)
? from employees
? group by department_id
? having max(salary)>10000
?5) Nesting Group function
?select max(avg(salary))
?from employees
?group by department_id;

posted @ 2006-09-22 11:49 康文 閱讀(201) | 評論 (0) | 編輯 收藏

window programming --keybord.txt

1 Keyboardd Basics
?1) Ignoring the Keyboard
?Your programm does not need to act on every keyboard message it received,Window handle many keyboard message function itself.
?2)Who's Got thefocus
? Though the keyboard is shared by all the window in the application ,The DispatchMessage send the message to the window procedure associated the window which message is intended.
? The window that receives a particular keyboard event is the window has the input foucs.
? Sometime no window has input foucs,this is the case if all your programs have been minmized,window continue send keyboard to the active window ,but it is send in the deffert form from sending to the input foucs.
? A window procedure can be determine when its window has the input focus by trapping WM_SETFOCUS and WM_KILLFOCUS.
?3) Queues and Synchronization
? As the user presses and releases key on keyborad,Windows and keyboard device driver translate the hardware scan code into formatted message.Howerver the messages are not palced on the application queue right away,Instean Windows store these message in a system message queue .The System message queue is a single message maintained by windows specifically for the prelimary storage of user input from keyboard and the mouse. Window will take the next message from the system message queue and place it on the application message queue only when a window application has finished proecssing the previous user input message.
4) Keystorkes and Character
?The message that an application receives from windows about keyboard events distingush between keystrokes and characters.
?for instance The keysokes has only one key labbed 'A' ,and the character may be 'a' or 'ctr-a' etc.
2 Keystroke Message
?1)Wirtual key Codes
? The virtual key code is stored in the wParam parameter of WM_KEYDOWN,WM_KEYUP,the code identifies the key being pressed or release.
? 2)lParam Information
? the wParam message parameter contain virtual key code and the lparam message parameter contains other information in understanding the keystoke.
? 3) Shift States
? iState = GetKeyState (VK_SHIFT) ;
? iState variable will be negative if the Shift key is donw
? iState = GetKeyState (VK_CAPITAL) ;
? 4)Using Keystroke Messages
???
? 5)
?? case WM_KEYDOWN:
???? switch (wParam)
???? {
???? case VK_HOME:
????????? SendMessage (hwnd, WM_VSCROLL, SB_TOP, 0) ;
????????? break ;

???? case VK_END:
????????? SendMessage (hwnd, WM_VSCROLL, SB_BOTTOM, 0) ;
????????? break ;

???? case VK_PRIOR:
????????? SendMessage (hwnd, WM_VSCROLL, SB_PAGEUP, 0) ;
????????? break ;
3 Character Messages
Message Key or Code
WM_KEYDOWN Virtual key code for `A' (0x41)
WM_CHAR Character code for `a' (0x61)
WM_KEYUP Virtual key code for `A' (0x41)

Message Key or Code
WM_KEYDOWN Virtual key code VK_SHIFT (0x10)
WM_KEYDOWN Virtual key code for `A' (0x41)
WM_CHAR Character code for `A' (0x41)
WM_KEYUP Virtual key code for `A' (0x41)
WM_KEYUP Virtual key code VK_SHIFT (0x10)

Key Character Code Duplicated by ANSI C? Escape
Backspace???? 0x08 Ctrl-H??????????????? \b
Tab?????????? 0x09 Ctrl-I??????????????? \t
Ctrl-Enter??? 0x0A Ctrl-J??????????????? \n
Enter???????? 0x0D Ctrl-M???????????????? \r
Esc 0x1B Ctrl-[

? case WM_CHAR:
???? [other program lines]
???? switch (wParam)
???? {
???? case `\b':????????? // backspace
????????? [other program line
????????? break ;
???? case `\t':????????? // tab
????????? [other program lines]
????????? break ;

???? case `\n':????????? // linefeed
????????? [other program lines]
????????? break ;

???? case `\r':????????? // carriage return
????????? [other program lines]
????????? break ;

???? default:??????????? // character codes
????????? [other program lines]
????????? break ;
???? }
???? return 0 ;

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

windows programming --window and message

1 An Architectural Overview
? 1)Getting a good feel for messages is an import part of learning how to write programs for windows.
? Windows send a message to your porgram means that Windows calls a function in your program .The parameter of this function describe the message that is being send.The function in your program is know as Window Procedure.
? Windows send a message to window by calling window procedure ,The window procedure do some processing based on the message and return control to Windows.
? More precisely , a Window? is always createdd based on a "window class".The window class identifies the window procedure that precesses messages to the windows.The use of window class allow mutiple window to be based the same window class and hence use the same window procedure.
? 2) Requsteriing the Window Class
?typedef struct
?{
???? UINT??????? style ;
???? WNDPROC???? lpfnWndProc ;
???? int???????? cbClsExtra ;
???? int???????? cbWndExtra ;
???? HINSTANCE?? hInstance ;
???? HICON?????? hIcon ;
???? HCURSOR???? hCursor ;
???? HBRUSH????? hbrBackground ;
???? LPCTSTR???? lpszMenuName ;
???? LPCTSTR???? lpszClassName ;
?}
?if (!RegisterClass (&wndclass))
?{
???? MessageBox (NULL, TEXT ("This program requires Windows NT!"),
???????????????? szAppName, MB_ICONERROR) ;
???? return 0 ;
?}
?2)Creating the Window
? Window class define the general Characteristics of the a window.If you want to create window based on the same window class,you can use CreateWindwo which allow you to specify more detail imformation about the window.
? hwnd = CreateWindow (szAppName,????????????????? // window class name
???????????????????? TEXT ("The Hello Program"), // window caption
???????????????????? WS_OVERLAPPEDWINDOW,??????? // window style
???????????????????? CW_USEDEFAULT,????????????? // initial x position
???????????????????? CW_USEDEFAULT,????????????? // initial y position
???????????????????? CW_USEDEFAULT,????????????? // initial x size
???????????????????? CW_USEDEFAULT,????????????? // initial y size
???????????????????? NULL,?????????????????????? // parent window handle
???????????????????? NULL,?????????????????????? // window menu handle
???????????????????? hInstance,????????????????? // program instance handle
???????????????????? NULL) ;???????????????????? // creation parameters

?3)Displaying the window
?? When thee CreateWindow function return ,windows create a window internal.What it means is that windows allocate a block memory to store the imformation about the window.If you want to show that ,you should call
ShowWindow (hwnd, iCmdShow) ;
UpdateWindow (hwnd) ;?
?4) The Message Loop
?while (GetMessage (&msg, NULL, 0, 0))
?{
???? TranslateMessage (&msg) ;
???? DispatchMessage (&msg) ;
?}

?typedef struct tagMSG
{
???? HWND?? hwnd ;
???? UINT?? message ;// message identifier
???? WPARAM wParam ;
???? LPARAM lParam ;
???? DWORD? time ; // the time the message is placed on the message queen
???? POINT? pt ; // the mouse coordiante
?}
?typedef struct tagPOINT
?{
???? LONG? x ;
???? LONG? y ;
?}
POINT, * PPOINT;
the message filed of message retrived from the message queen is anyting except WM_QUITm ,GetMessage return a nonzero value
wM_QUIT cause GetMessage reuturn 0.

TranslateMessage (&msg) ;
passing the msg struct back to window for some keysboard translation

DispatchMessage (&msg) ;
pass the msessageback to window .Windwo then send the message to the appropriate window procedure for processing.After window
procedure processing the message ,it return control to the windows,which is still in serving the Dispatchmessage
?5) The Window Procedure
? a The window procedure determined the how the window display on the client area and how the window respose to user input
? b LRESULT CALLBACK WndProc (HWND hwnd, UINT message, WPARAM wParam, LPARAM lParam),the four parameter is idential to the first four filed of the message struct.
?6) Processing the Message
?switch (iMsg)
{
case WM_CREATE :
???? [process WM_CREATE message]
???? return 0 ;
?????????
case WM_PAINT :
???? [process WM_PAINT message]
???? return 0 ;
?????????
case WM_DESTROY :
???? [process WM_DESTROY message]
???? return 0 ;
}
return DefWindowProc (hwnd, iMsg, wParam, lParam) ;

when a window procedure process the message ,it should return 0;
?7) The WM_PAINT Message
? WU_PAINT message is extremely import in window.it inform a program when part or all of the window 's client area are invalid and must be redraw or repaint.
?WM_PAINT processing almost always begins with a call to BeginPaint:
? hdc = BeginPaint (hwnd, &ps) ; //return a handle to device context
and ends with a call to EndPaint:
?EndPaint (hwnd, &ps) ;
?8)The WM_DESTROY Message
? PostQuitMessage (0) ;
??
?WNDCLASS, * PWNDCLASS ;
?#include <windows.h>
/* the same as long _stdcall WndPro(long,unsign int,unsign int ,long)*/
LRESULT CALLBACK WndProc (HWND, UINT, WPARAM, LPARAM) ;

/*int _stdcall WinMain(long hInstance,long hPrevInstance,char * szComdLine,int iCmdShow)*/
int WINAPI WinMain (HINSTANCE hInstance, HINSTANCE hPrevInstance,???
??????????????????? PSTR szCmdLine, int iCmdShow)
{
???? static TCHAR szAppName[] = TEXT ("HelloWin") ;
???? HWND???????? hwnd ;
???? MSG????????? msg ;
???? WNDCLASS???? wndclass ;
/*
? all window createe based on this window class will completely repaint whenever? horizational window sizd and vertial??
? window size change.
*/
???? wndclass.style???????? = CS_HREDRAW | CS_VREDRAW ;?????????????????
???? wndclass.lpfnWndProc?? = WndProc ; // set the window procedure for the window class
???? wndclass.cbClsExtra??? = 0 ;
???? wndclass.cbWndExtra??? = 0 ;
???? wndclass.hInstance???? = hInstance ; // the handle of this program
???? wndclass.hIcon???????? = LoadIcon (NULL, IDI_APPLICATION) ;
???? wndclass.hCursor?????? = LoadCursor (NULL, IDC_ARROW) ;
???? wndclass.hbrBackground = (HBRUSH) GetStockObject (WHITE_BRUSH) ;
???? wndclass.lpszMenuName? = NULL ;
???? wndclass.lpszClassName = szAppName ;

???? if (!RegisterClass (&wndclass))
???? {
????????? MessageBox (NULL, TEXT ("This program requires Windows NT!"),
????????????????????? szAppName, MB_ICONERROR) ;
????????? return 0 ;
???? }
???? hwnd = CreateWindow (szAppName,????????????????? // window class name
????????????????????????? TEXT ("The Hello Program"), // window caption
????????????????????????? WS_OVERLAPPEDWINDOW,??????? // window style
????????????????????????? CW_USEDEFAULT,????????????? // initial x position
????????????????????????? CW_USEDEFAULT,????????????? // initial y position
????????????????????????? CW_USEDEFAULT,????????????? // initial x size
????????????????????????? CW_USEDEFAULT,????????????? // initial y size
????????????????????????? NULL,?????????????????????? // parent window handle
????????????????????????? NULL,?????????????????????? // window menu handle
????????????????????????? hInstance,????????????????? // program instance handle
????????????????????????? NULL) ;???????????????????? // creation parameters
????
???? ShowWindow (hwnd, iCmdShow) ;// iCmdShow determine how the window is to be initially displayed on the screen.
???? UpdateWindow (hwnd) ; // cause the client area to paint
????
???? while (GetMessage (&msg, NULL, 0, 0))
???? {
????????? TranslateMessage (&msg) ;
????????? DispatchMessage (&msg) ;
???? }
???? return msg.wParam ;
}

LRESULT CALLBACK WndProc (HWND hwnd, UINT message, WPARAM wParam, LPARAM lParam)
{
???? HDC???????? hdc ;
???? PAINTSTRUCT ps ;
???? RECT??????? rect ;
????
???? switch (message)
???? {
???? case WM_CREATE:
????????? PlaySound (TEXT ("hellowin.wav"), NULL, SND_FILENAME | SND_ASYNC) ;
????????? return 0 ;

???? case WM_PAINT:
????????? hdc = BeginPaint (hwnd, &ps) ;//return a handle to device context
?????????
????????? GetClientRect (hwnd, &rect) ;// set the struct rect with the dimensions of the client area
?????????
????????? DrawText (hdc, TEXT ("Hello, Windows 98!"), -1, &rect,
??????????????????? DT_SINGLELINE | DT_CENTER | DT_VCENTER) ;
????????? EndPaint (hwnd, &ps) ;
????????? return 0 ;
?????????
???? case WM_DESTROY:
????????? PostQuitMessage (0) ;// insert a WM_QIUT in the message queue.
????????? return 0 ;
???? }
???? return DefWindowProc (hwnd, message, wParam, lParam) ;
}

2 The Window Programming Hurdles
?1)Queue and noqueue message
? queue message are post the message queue and the noqueue message send to the window procdure directly.
????

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

Spring -transaction

1 Understand Transaction
? 1) Introduce Spring's transaction manager
? a? JDBC transactions?
???? <bean id="transactionManager" class="org.springframework.jdbc.
????? datasource.DataSourceTransactionManager">
???? <property name="dataSource">
???? <ref bean="dataSource"/>
???? </property>
???? </bean>
?? b Hibernate transactions
???? <bean id="transactionManager" class="org.springframework.
?????? orm.hibernate.HibernateTransactionManager">
???? <property name="sessionFactory">
???? <ref bean="sessionFactory"/>
???? </property>
???? </bean>
2 Programing transaction in Spring
?? One approach to adding transaction to your code is to programmly add transactional boundary using transiationTemplate class.
?? Programming is good when you want complete control over transactional boundary.but you have to use spring specific class.In most case ,your tansactional needs will not require such precise control over transactional boundaries.That is why you will typically choolse to declare transaction support
? public void enrollStudentInCourse() {
??? transactionTemplate.execute(
??? new TransactionCallback() {
????? public Object doInTransaction(TransactionStatus ts) {
??????? try {
????????? // do stuff?? Runs within doInTransaction()
??????? } catch (Exception e) {
????????? ts.setRollbackOnly(); //Calls setRollbackOnly() to roll Calls setRollbackOnly()???????????????????????????????? //to roll back
??????? }
????????? return null;?? //If successful, transaction is committed
????? }
??? }
?? );
?}
?<bean id="transactionTemplate" class="org.springframework.
?????? transaction.support.TransactionTemplate">
? <property name="transactionManager">
??? <ref bean="transactionManager"/>
? </property>
?</bean>
?<bean id="courseService"
??? class="com.springinaction.training.service.CourseServiceImpl">
?<property name=" transactionTemplate">
???? <ref bean=" transactionTemplate"/>
?? </property>
?</bean>
3 Declaring transactions
? Spring's support for declarative transaction management is implementedd through Spirng's? AOP framework.
? <bean id="courseService" class="org.springframework.transaction.
?????? interceptor.TransactionProxyFactoryBean">
? <property name="proxyInterfaces">
??? <list>
????? <value>
??????? com.springinaction.training.service.CourseService??
????? </value>
??? </list>
? </property>
? <property name="target">
?? <ref bean="courseServiceTarget"/>?? //Bean being proxied
? </property>
?<property name="transactionManager">
?? <ref bean="transactionManager"/>?? //Transaction manager
?</property>
?<property name="transactionAttributeSource">
?? <ref bean="attributeSource"/>?? //Transaction attribute source
?</property>
?</bean>
?1) Understanding transaction attributes
? In Spring transaction attribute is a description of how transaction policies should be
applied to a methods
?? a? Propagation behavior
??? Propagation behavior???????????????? What it means
??? PROPAGATION_MANDATORY??????????????? indicate that the method must run within a????????????????????????????????????????????????? transaction.If no transaction is in progress
???????????????????????????????????????? an exception will be thrown
??? PROPAGATION_NESTED
??? PROPAGATION_NEVER??????????????????? indicate that the method can not run withi a??????????????????????????????????????????????? transaction. if a transaction exist an exception??????????????????????????????????????????? will be thrown.
??? PROPAGATIOM_NOT_SUPPORT????????????? Indicates that the method should not run within a?????????????????????????????????????????? transaction. If an existing transaction is????????????????????????????????????????????????? in progress, it will be suspended for the
???????????????????????????????????????? duration of the method.
??? PROPAGATION_REQUIRED???????????????? indicate that the current method must run within a????????????????????????????????????????? transaction.if an existing transaction is in??????????????????????????????????????????????? progress,the ,method will run with the transaction
???????????????????????????????????????? otherwise a new transaction will be started
??? PROPAGATION_REQUIRENEW?????????????? indicates that the current must run within its own
???????????????????????????????????????? transaction.A new transaction is started and an???????????????????????????????????????????? existing transaction will be suspend
??? PROPAGATION_SUPPORT????????????????? indicate the current mehtod does not require a????????????????????????????????????????? transaction.but may run if on is already in progress?????
??? b Isolation levels?????
??? Isolation level??????????????????? What it means
??? ISOLATION_DEFAULT????????????????? Using the defaul isolation level of the underlying????????????????????????????????????????? database
??? ISOLATION_READ_UNCOMMITTED???????? Allows you read change that have not yet been commit
?????????????????????????????????????? May result in dirty read,phantom read,nonrepeatable???????????????????????????????????????? read
??? ISOLATION_READ_COMMITTED?????????? Allows reads from concurrent transactions that have
?????????????????????????????????????? bean committed.Dirty read are prevent.but platform????????????????????????????????????????? and norepeatable reads may still occur.
??? ISOLATIOM_REPEATABLE_READ????????? Multiple read the same field will yield the same??????????????????????????????????????????? result ,unless changed by the transaction?????????????????????????????????????????????????? itself.Dirty reads ,nonrepeatable are all prevented
?????????????????????????????????????? phantom may still occur
??? ISOLATION_SERIALIZABLE???????????? This fully ACID-compliant isolation level ensusme??????????????????????????????????????? that dirty read,unrepeatable read ,phantom read are??????????????????????????????????????? all prevented.And this is the most slowest isolation
?????????????????????????????????????? since it is typically accomplished by doing full??????????????????????????????????????? table lock on the tables in the transaction.

?? c Read-Only
?? If a transaction performs only read operation against the underlying datastore.when a transaction begin ,it only make sense to declare a transaction as read only on mehtods with
propagation behavior which start a new transaction.
?? Furthermore ,if you are Hibernate as persistence mechanism,declaring a transaction as read only will reult in Hibernate flush mode being set to FLUST_NEVER.this tell hibernate to avoid synchroniztion of objects with database.
?? d Transaction timeout
?? Suppose that your transaction becomes unexpectedly long-running transaction.Because transaction may invole locks on the underlying database.Instead of waiting it out ,you can delcare a transaction to automaitically roll back.
?? because timeout clock begin ticking when a transaction start. it only make sense to declare a transaction timeout on methods with propagation behavior that start a new transaction.
? 2) Declaring a simple transaction policy
?? <bean id="myTransactionAttribute"
??? class="org.springframework.transaction.interceptor.
??????????? DefaultTransactionAttribute">
? <property name="propagationBehaviorName">
??? <value>PROPAGATION_REQUIRES_NEW</value>
? </property>
? <property name="isolationLevelName">
??? <value>ISOLATION_REPEATABLE_READ</value>
? </property>
?</bean>
?<bean id="transactionAttributeSource"
??? class="org.springframework.transaction.interceptor.
??????????? MatchAlwaysTransactionAttributeSource">
? <property name="transactionAttribute">
??? <ref bean="myTransactionAttribute"/>
? </property>
?</bean>
4 Declaring transactions by method name
? 1) Using NameMatchTransactionAttributeSource
? The properties property of NameMatchTransactionAttributeSource maps mehtod to a transaction property descriptor. the property descriptor takes the following form:
? Propagation,isolation,readOnly,-Exception,+Exception
?
? <bean id="transactionAttributeSource"
??? class="org.springframework.transaction.interceptor.
??????????? NameMatchTransactionAttributeSource">
? <property name="properties">
??? <props>
????? <prop key="enrollStudentInCourse">
????????? PROPAGATION_REQUIRES_NEW
????? </prop>
??? </props>
? </property>
?</bean>
?2) Specifying the transaction Isolation level
? <bean id="transactionAttributeSource"
??? class="org.springframework.transaction.interceptor.
??????????? NameMatchTransactionAttributeSource">
???? <property name="properties">
??? <props>
????? <prop key="enrollStudentInCourse">
??????? PROPAGATION_REQUIRES_NEW,ISOLATION_REPEATABLE_READ
????? </prop>
??? </props>
? </property>
?</bean>
?3) Using real-only transaction
? <bean id="transactionAttributeSource"
??? class="org.springframework.transaction.interceptor.
??????????? NameMatchTransactionAttributeSource">
? <property name="properties">
??? <props>
????? <prop key="getCompletedCourses">
??????? PROPAGATION_REQUIRED,ISOLATION_REPEATABLE_READ,readOnly
????? </prop>
??? </props>
? </property>
</bean>
?4)Specifying? rollback rules
? You can sepcify that a transaction be rollback on specify checked exception
? <bean id="transactionAttributeSource"
??? class="org.springframework.transaction.interceptor.
??????????? NameMatchTransactionAttributeSource">
? <property name="properties">
??? <props>
????? <prop key="enrollStudentInCourse">
??????? PROPAGATION_REQUIRES_NEW,ISOLATION_REPEATABLE_READ,
??????? -CourseException
??????? </prop>
???? </props>
??? </property>
? </bean>
? Exception can be marked as negative(-) or postive(+)
? Negative exception will trigger the roll back if the exception (or sublclass of it) is thrown.Postive exception on the other hand indicate that the transacton should be commit
even if the exception is thrown
?5)Using wildcard matches
?<bean id="transactionAttributeSource"
??? class="org.springframework.transaction.interceptor.
??????????? NameMatchTransactionAttributeSource">
? <property name="properties">
??? <props>
????? <prop key="get*">
??????? PROPAGATION_SUPPORTS
????? </prop>
??? </props>
? </property>
</bean>
?6 Short-cut name match transaction
?<bean id="courseService" class="org.springframework.transaction.
?????? interceptor.TransactionProxyFactoryBean">
?? <property name="transactionProperties">
??? <props>
????? <prop key="enrollStudentInCourse">
??????? PROPAGATION_REQUIRES_NEW
????? </prop>
??? </props>
?? </property>
?</bean>

posted @ 2006-09-15 11:00 康文 閱讀(1805) | 評論 (0) | 編輯 收藏