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'中無(wú)權(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) | 評(píng)論 (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) | 評(píng)論 (0) | 編輯 收藏

開(kāi)發(fā)子過(guò)程

?1? 開(kāi)發(fā)過(guò)程
?1) 建立過(guò)程：不帶參數(shù)
? create or replace procedure out_time
? is
? begin
??? dbms_output.put_line(systimestamp);
? end;
? a 使用execute 命令調(diào)用過(guò)程
?? set? serveroutput on
?? exec out_time;
? b 使用call 命令調(diào)用過(guò)程
?? set serveroutput on
?? call out_time();
?2) 建立過(guò)程：帶有in參數(shù) 默認(rèn)為輸入?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,'雇員號(hào)不能重復(fù)');
????? when e_integrity then
??????? raise_application_error(-20001,'部門(mén)號(hào)不存在');
???
調(diào)用
?? exec add_employee(111,'clark',200,'manager',10);
?? 3 建立過(guò)程，帶有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 建立過(guò)程，帶有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) 刪除過(guò)程
???? drop procedure add_dept;
2 開(kāi)發(fā)函數(shù)
? 可以在sql語(yǔ)句和其他子過(guò)程中執(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 語(yǔ)句中直接調(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 語(yǔ)句中只能調(diào)用存儲(chǔ)函數(shù)
? -- 在sql 語(yǔ)句中只能調(diào)用帶有輸入?yún)?shù)in ，而不能有輸出參數(shù)out 和in out 參數(shù)的函數(shù)
? -- 在sql 語(yǔ)句中調(diào)用的函數(shù)的參數(shù)必須是標(biāo)準(zhǔn)的sql 數(shù)據(jù)類型，不能是pl/sql 所特有的數(shù)據(jù)類型
? -- 在sql 語(yǔ)句中調(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)類出子程序編譯錯(cuò)誤
??? -- show errors
??? --使用數(shù)據(jù)字典user_errors? 確定錯(cuò)誤原因和位置
???? select line||'/'||position as "line/col",text error
???? from user_errors where name='raise_salary';
?? 4) 列出對(duì)象的依賴關(guān)系
??? -- 使用 user_dependenciess 確定直接依賴關(guān)系
??? select name,type from user_dependencies
??? where referenced_name='emp'
??? -- 使用 deptree 和 ideptree 確定依賴和見(jiàn)解依賴關(guān)系
??? select nested_level,name,type from deptree;
?? 5) 重新編譯子程序
???? 當(dāng)被應(yīng)用對(duì)象的結(jié)構(gòu)被修改以后，就會(huì)將相關(guān)依賴對(duì)象轉(zhuǎn)變?yōu)闊o(wú)效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) | 評(píng)論 (0) | 編輯 收藏

處理例外

1例外簡(jiǎn)介
?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('首先初始化對(duì)象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 命令打開(kāi)了顯示游標(biāo)，或執(zhí)行for循環(huán)(隱式的打開(kāi)游標(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)打開(kāi)");
?????? 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)沒(méi)有打開(kāi)");
???? 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('沒(méi)有返回結(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 錯(cuò)誤沒(méi)有任何聯(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 使用錯(cuò)誤例外函數(shù)
? 使用例外函數(shù)可以取得錯(cuò)誤號(hào)以及相關(guān)的錯(cuò)誤消息，sqlcode 用于取得oracle 的錯(cuò)誤號(hào)，而sqlerrm
則用于取得與之相關(guān)的錯(cuò)誤信息。
? 1 sqlcode 和 sqlerrm
? 為了在pl/sql 應(yīng)用程序中處理其他為預(yù)料的到的oracle 錯(cuò)誤，用戶可以在例外處理部分的
when others 自句后，引用兩個(gè)函數(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
?? 只能在子程序中使用（過(guò)程，函數(shù)，包，觸發(fā)器）
?? raise_application_error(error_number,message,[true|false]);
?? error_number 錯(cuò)誤號(hào)，在－20000到－20999 之間
?? message 指定錯(cuò)誤消息，不能超過(guò)2048?
?? if v_comm is null then
?????? raise_application_error(-20001,'該雇員無(wú)補(bǔ)助');
?? end if;

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

開(kāi)發(fā)包

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

including Constraints

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

create view

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

使用觸發(fā)器

1 觸發(fā)器簡(jiǎn)介
? 1) 觸發(fā)事件
? 2) 觸發(fā)條件
? 3) 觸發(fā)操作
???? . 觸發(fā)器代碼的大小不能超過(guò)32k，如果使用大量代碼建立觸發(fā)器，應(yīng)該先建立存儲(chǔ)過(guò)程，然后再觸發(fā)器中使用call語(yǔ)句調(diào)用存儲(chǔ)過(guò)程。
???? . 觸發(fā)器中正能含有select ，insert，update 和delete 語(yǔ)句，而不能含有ddl 語(yǔ)句，和事物控制語(yǔ)句。
2 建立dml 觸發(fā)器
?1) 觸發(fā)時(shí)機(jī)
? before，after 表示在執(zhí)行dml操作之后觸發(fā)器
?2)觸發(fā)事件
? insert ，update 和delete 操作。也可以使用書(shū)法事件
?3) dml 觸發(fā)器是針對(duì)特定表進(jìn)行的 因此必須制定dml 操作所對(duì)應(yīng)的表
?4) 觸發(fā)器類型 用于指定當(dāng)觸發(fā)器事件之后，需要執(zhí)行幾次觸發(fā)器操作。如果指定語(yǔ)句觸發(fā)器類型
那么會(huì)執(zhí)行一次觸發(fā)器代碼：如果指定行觸發(fā)器類型，則會(huì)在每個(gè)被作用行上執(zhí)行一次觸發(fā)器代碼。
?5) 觸發(fā)條件
?用于指定執(zhí)行行觸發(fā)器代碼的條件，只有為ture時(shí)，才會(huì)執(zhí)行行觸發(fā)器代碼。
? 6) 如果使用pl/sql 存儲(chǔ)過(guò)程，java 存儲(chǔ)過(guò)程，或外部處處過(guò)程需要在觸發(fā)器操作部分直接使用call
? 7) dml 觸發(fā)器觸發(fā)順序
?? (1)dml 觸發(fā)器在單行數(shù)據(jù)上的觸發(fā)順序。
??? 對(duì)于單行數(shù)據(jù)而言，無(wú)論是語(yǔ)句此觸發(fā)器，還是行觸發(fā)器，觸發(fā)器代碼實(shí)際只執(zhí)行一次，并且執(zhí)行
順序?yàn)閎efore 語(yǔ)句觸發(fā)器，before 行觸發(fā)器，dml 操作，after 行觸發(fā)器，after 語(yǔ)句觸發(fā)器
?? (2) dml 觸發(fā)器在多行數(shù)據(jù)上的觸發(fā)順序
??? before 語(yǔ)句觸發(fā)器
??? before 行觸發(fā)器
??? after 行觸發(fā)器
??? before行觸發(fā)器
??? after 行觸發(fā)器
??? after語(yǔ)句觸發(fā)器
?? 語(yǔ)句觸發(fā)器只被執(zhí)行一次，而行觸發(fā)器在每個(gè)行上都執(zhí)行一次。
? 2) 語(yǔ)句觸發(fā)器
? 當(dāng)審計(jì)dml 操作，或確保dml操作安全執(zhí)行時(shí)，可以使用語(yǔ)句觸發(fā)器
? 1 建立before 語(yǔ)句觸發(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 語(yǔ)句觸發(fā)器
??? 為了dml 操作，或者dml 操作后執(zhí)行匯總運(yùn)算
?? 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ā)器
?? 審計(jì)數(shù)據(jù)變化可以使用行觸發(fā)器
?? 1 建立不before 行觸發(fā)器
??? 為了取保數(shù)據(jù)符合商業(yè)邏輯或企業(yè)規(guī)則，對(duì)輸入的數(shù)據(jù)進(jìn)行復(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ā)器
???? 為了審計(jì)dml 操作，可以使用語(yǔ)句觸發(fā)器或oracle 系統(tǒng)提供的審計(jì)功能，而為了審計(jì)數(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ā)器，默認(rèn)情況下會(huì)咱每個(gè)被作用行上七星一次觸發(fā)器代碼，為了時(shí)得再特定條件下執(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ā)器使用注意事項(xiàng)
? 觸發(fā)器代碼不能從觸發(fā)器所對(duì)應(yīng)的基表中讀取數(shù)據(jù)
3 dml 觸發(fā)器
? 為了保證數(shù)據(jù)庫(kù)滿足特定的商業(yè)規(guī)則或企業(yè)邏輯，可以使用約束，觸發(fā)器和子程序。約束性能最好，實(shí)現(xiàn)最簡(jiǎn)單，所以為售選，如果觸發(fā)器不盟實(shí)現(xiàn)，可以選擇觸發(fā)器。
? dml 觸發(fā)器可以用于實(shí)現(xiàn)數(shù)據(jù)安全保護(hù)，數(shù)據(jù)審計(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) 實(shí)現(xiàn)數(shù)據(jù)審計(jì)
? 使用數(shù)據(jù)審計(jì)只能審計(jì)sql 操作，而不會(huì)記載數(shù)據(jù)變化
? audit insert,update,delete on emp by access
? 3)實(shí)現(xiàn)數(shù)據(jù)完整性
? 首選約束 alter table emp add constraint ck_sal check (sal>=800),但是在有些情況下只能使用觸發(fā)器來(lái)實(shí)現(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 可以進(jìn)行集聯(lián)刪除，但是卻不能進(jìn)行集聯(lián)更新。采用觸發(fā)器實(shí)現(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ā)器
? 對(duì)于簡(jiǎn)單視圖可以直接進(jìn)行insert update 和delete 等操作，但是對(duì)于復(fù)雜視圖不允許進(jìn)行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 語(yǔ)句。
?? instead of 選項(xiàng)只使用于視圖
?? 基于視圖建立觸發(fā)器時(shí)，不能定義before 和 after
?? 在建立視圖時(shí)不能指定 with check option
?? 當(dāng)建立instead of 觸發(fā)器時(shí)，必須指定for each row 選項(xiàng)
? 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;
??
?? 可以對(duì)視圖執(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) | 評(píng)論 (0) | 編輯 收藏

使用復(fù)合變量

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

advanced subquery

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

開(kāi)發(fā)動(dòng)態(tài)sql

1 動(dòng)態(tài)sql 簡(jiǎn)介
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) 處理無(wú)占位符和returning 子句的dml 語(yǔ)句
???? 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語(yǔ)句
?????? 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語(yǔ)句
?????? 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 處理多行查詢語(yǔ)句
?? 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 在動(dòng)態(tài)sql 中使用bulk語(yǔ)句
?? 1) 在 execute immediate 語(yǔ)句中使用動(dòng)態(tài)bulk 語(yǔ)句
???? 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 語(yǔ)句中使用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 語(yǔ)句中使用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) | 評(píng)論 (0) | 編輯 收藏

managing an oracle instance

posted @ 2006-10-11 14:42 康文 閱讀(360) | 評(píng)論 (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) | 評(píng)論 (0) | 編輯 收藏

extension to dml in oracle

posted @ 2006-10-11 14:41 康文 閱讀(200) | 評(píng)論 (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) | 評(píng)論 (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) | 評(píng)論 (0) | 編輯 收藏

翻頁(yè)的實(shí)現(xiàn)

1 oracle 的實(shí)現(xiàn)
?語(yǔ)句一
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;
語(yǔ)句二：
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 的實(shí)現(xiàn)
3 mysql 的實(shí)現(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) | 評(píng)論 (0) | 編輯 收藏

jdbc-prepare sql

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

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

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

使用存儲(chǔ)過(guò)程

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

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方法的字串是存儲(chǔ)過(guò)程調(diào)用的書(shū)寫(xiě)規(guī)范。它指定了存儲(chǔ)過(guò)程的名稱，？代表了你需要指定的參數(shù)。
和JDBC集成是存儲(chǔ)過(guò)程的一個(gè)很大的便利：為了從應(yīng)用中調(diào)用存儲(chǔ)過(guò)程，不需要存根（stub）類或者配置文件，除了你的DBMS的JDBC驅(qū)動(dòng)程序外什么也不需要。
當(dāng)這段代碼執(zhí)行時(shí)，數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程就被調(diào)用。我們沒(méi)有去獲取結(jié)果，因?yàn)樵摯鎯?chǔ)過(guò)程并不返回結(jié)果。執(zhí)行成功或失敗將通過(guò)例外得知。失敗可能意味著調(diào)用存儲(chǔ)過(guò)程時(shí)的失?。ū热缣峁┑囊粋€(gè)參數(shù)的類型不正確），或者一個(gè)應(yīng)用程序的失敗（比如拋出一個(gè)例外指示在poets數(shù)據(jù)庫(kù)中并不存在“Dylan Thomas”）

結(jié)合SQL操作與存儲(chǔ)過(guò)程

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

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;

很獨(dú)特？不。我打賭你一定期待看到一個(gè)poets表上的UPDATE。這也暗示了使用存儲(chǔ)過(guò)程實(shí)現(xiàn)是多么容易的一件事情。set_death_age幾乎可以肯定是一個(gè)很爛的實(shí)現(xiàn)。我們應(yīng)該在poets表中添加一列來(lái)存儲(chǔ)逝世年齡。Java代碼中并不關(guān)心數(shù)據(jù)庫(kù)模式是怎么實(shí)現(xiàn)的，因?yàn)樗鼉H調(diào)用存儲(chǔ)過(guò)程。我們以后可以改變數(shù)據(jù)庫(kù)模式以提高性能，但是我們不必修改我們代碼。
下面是調(diào)用上面存儲(chǔ)過(guò)程的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();

}

}

為了確?？删S護(hù)性，建議使用像這兒這樣的static方法。這也使得調(diào)用存儲(chǔ)過(guò)程的代碼集中在一個(gè)簡(jiǎn)單的模版代碼中。如果你用到許多存儲(chǔ)過(guò)程，就會(huì)發(fā)現(xiàn)僅需要拷貝、粘貼就可以創(chuàng)建新的方法。因?yàn)榇a的模版化，甚至也可以通過(guò)腳本自動(dòng)生產(chǎn)調(diào)用存儲(chǔ)過(guò)程的代碼。

Functions

存儲(chǔ)過(guò)程可以有返回值，所以CallableStatement類有類似getResultSet這樣的方法來(lái)獲取返回值。當(dāng)存儲(chǔ)過(guò)程返回一個(gè)值時(shí)，你必須使用registerOutParameter方法告訴JDBC驅(qū)動(dòng)器該值的SQL類型是什么。你也必須調(diào)整存儲(chǔ)過(guò)程調(diào)用來(lái)指示該過(guò)程返回一個(gè)值。
下面接著上面的例子。這次我們查詢Dylan Thomas逝世時(shí)的年齡。這次的存儲(chǔ)過(guò)程使用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ù)名通過(guò)Unix和DOS腳本的$n語(yǔ)法引用。同時(shí)，也注意嵌入的注釋，這是和Java代碼相比的另一個(gè)優(yōu)越性。在Java中寫(xiě)這樣的注釋當(dāng)然是可以的，但是看起來(lái)很凌亂，并且和SQL語(yǔ)句脫節(jié)，必須嵌入到Java String中。
下面是調(diào)用這個(gè)存儲(chǔ)過(guò)程的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);

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

復(fù)雜的返回值

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

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)用該存儲(chǔ)過(guò)程的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();

}

}

因?yàn)镴DBC并不直接支持從存儲(chǔ)過(guò)程中返回游標(biāo)，我們使用Types.OTHER來(lái)指示存儲(chǔ)過(guò)程的返回類型，然后調(diào)用getObject()方法并對(duì)返回值進(jìn)行強(qiáng)制類型轉(zhuǎn)換。
這個(gè)調(diào)用存儲(chǔ)過(guò)程的Java方法是mapping的一個(gè)好例子。Mapping是對(duì)一個(gè)集上的操作進(jìn)行抽象的方法。不是在這個(gè)過(guò)程上返回一個(gè)集，我們可以把操作傳送進(jìn)去執(zhí)行。本例中，操作就是把ResultSet打印到一個(gè)輸出流。這是一個(gè)值得舉例的很常用的例子，下面是調(diào)用同一個(gè)存儲(chǔ)過(guò)程的另外一個(gè)方法實(shí)現(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);

}

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

結(jié)論

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

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

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

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

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

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

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

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

posted @ 2006-10-11 14:36 康文 閱讀(242) | 評(píng)論 (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) | 評(píng)論 (0) | 編輯 收藏

database link 使用 轉(zhuǎn)載

鏈接到遠(yuǎn)程數(shù)據(jù)庫(kù)

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

要獲得數(shù)據(jù)庫(kù)鏈接的更深?yuàn)W的知識(shí)，查看Oracle8i SQL Reference（Oracle8i SQL參考）和Oracle8i Concepts （Oracle8i概念手冊(cè)）。詳細(xì)資料的另一個(gè)極好的來(lái)源是Oracle8i Distributed Database Systems（Oracle8i分布式數(shù)據(jù)庫(kù)系統(tǒng)手冊(cè)）。

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

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

簡(jiǎn)單案例

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

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

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

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

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

鏈接名稱GNIS緊隨LINK關(guān)鍵字。當(dāng)連接到遠(yuǎn)程事例時(shí)，CONNECT TO...IDENTIFIED子句指定UEST/WELCOME作為用戶名和口令使用 。USING子句指定通過(guò)網(wǎng)絡(luò)服務(wù)名稱GNIS建立連接。使用這一鏈接，現(xiàn)在你可以在遠(yuǎn)程數(shù)據(jù)庫(kù)上查詢數(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語(yǔ)句中@GNIS緊隨表名稱，說(shuō)明GNIS.FEATURE_NAMES表是在遠(yuǎn)程數(shù)據(jù)庫(kù)，應(yīng)該通過(guò)GNIS鏈接訪問(wèn)，鏈接類型Oracle支持幾種不同類型的鏈接。這些類型相互重疊，有時(shí)難以通過(guò)選項(xiàng)進(jìn)行分類。當(dāng)你建立數(shù)據(jù)庫(kù)鏈接時(shí)，你需要從下面選?。?

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

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

公用鏈接與私有鏈接相對(duì)比

公用數(shù)據(jù)庫(kù)鏈接對(duì)所有的數(shù)據(jù)庫(kù)用戶開(kāi)放訪問(wèn)權(quán)。前面顯示的是私有數(shù)據(jù)庫(kù)鏈接，它只對(duì)建立它的用戶授權(quán)。公用數(shù)據(jù)庫(kù)鏈接更為有用，因?yàn)樗鼓悴槐貫槊恳粋€(gè)潛在用戶創(chuàng)建單獨(dú)的鏈接。為了建立一個(gè)公用數(shù)據(jù)庫(kù)鏈接，使用如下顯示的PUBLIC關(guān)鍵字：

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

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

使用數(shù)據(jù)庫(kù)鏈接訪問(wèn)遠(yuǎn)程表

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

權(quán)限類

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

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

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

如果你不想在數(shù)據(jù)庫(kù)鏈接中嵌入用戶名和口令，Oracle提供給你另一個(gè)非常有用的選擇。你可以建立一個(gè)連接用戶鏈接。連接用戶鏈接是這樣的鏈接，它通過(guò)任一個(gè)正在使用該鏈接的本地?cái)?shù)據(jù)庫(kù)的用戶的用戶名和口令登陸到遠(yuǎn)程數(shù)據(jù)庫(kù)。你可以通過(guò)簡(jiǎn)單地空出用戶名和口令來(lái)建立一個(gè)連接用戶鏈接?？紤]如下定義：

CREATE PUBLIC DATABASE LINK GNIS

USING 'GNIS';

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

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

公用連接用戶數(shù)據(jù)庫(kù)鏈接尤其有用，因?yàn)槟憧梢越⒁粋€(gè)可被所有用戶訪問(wèn)的鏈接，并且所有用戶被分別使用他或她自己的用戶名和口令授權(quán)。你獲得責(zé)任方面的利益，沒(méi)有將遠(yuǎn)程數(shù)據(jù)庫(kù)向你的本地?cái)?shù)據(jù)庫(kù)上的每一位用戶開(kāi)放。代價(jià)是你必須在兩個(gè)數(shù)據(jù)庫(kù)上建立用戶帳戶，并且你必需確信口令保持一致。

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

共享鏈接

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

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

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

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

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

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

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

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

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

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
_______________
GNIS.GENNICK.ORG

由于歷史的原因，默認(rèn)情況下，全局性名稱與數(shù)據(jù)庫(kù)鏈接名稱的之間的鏈接不是強(qiáng)制性的。不過(guò)，你可以通過(guò)設(shè)置GLOBAL_NAMES的初始化參數(shù)為T(mén)RUE來(lái)改變這一行為。例如：

SQL> SHOW PARAMETER GLOBAL_NAMES

NAME TYPE VALUE
________________________________________________________
global_names boolean TRUE

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

posted @ 2006-09-29 15:35 康文 閱讀(2147) | 評(píng)論 (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) | 評(píng)論 (0) | 編輯 收藏

使用游標(biāo)

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

使用復(fù)合變量.

1 pl/sql 集合 處理單列多行數(shù)據(jù)庫(kù)，使用的類型為標(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)使用嵌套表元素時(shí)，必須先用其構(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) 變長(zhǎng)數(shù)組
????? 在使用varray 時(shí)必須指定最大個(gè)數(shù)，和數(shù)據(jù)類型,在使用其元素時(shí)必須進(jìn)行初始化
????? 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)點(diǎn)
??????? 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 多級(jí)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 使用多級(jí)嵌套表
??????? 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)前集合變量中的元素總個(gè)數(shù)
??? ename_table.count
?? 3) limit 返回集合元素的最大個(gè)數(shù)? 只有varray 有
?? 4)first and last
?????? ename_table.first
?????? ename_table.last
?? 5) prior 和next
??? ename_table.prior(5); －－返回元素5的前一個(gè)
??? ename_table.next(5);? --? 后一個(gè)
?? 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)將一個(gè)集合的數(shù)據(jù)賦值給另一個(gè)集合.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 先略過(guò)。
4 批量綁定
? 執(zhí)行單詞sql 操作能傳遞所有集合元素的數(shù)據(jù)。
? 1 forall 語(yǔ)句
? 用于insert update 和delete操作。在oracle9i 中forall 語(yǔ)句必須具有連續(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) | 評(píng)論 (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) | 評(píng)論 (0) | 編輯 收藏

OpenSessionInViewFilter解決Web應(yīng)用程序的問(wèn)題 轉(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) | 評(píng)論 (0) | 編輯 收藏

creating and manipulation table

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

Data manipulation

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

sequence 的用法

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