2006年10月11日
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????
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;
?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,'雇員號不能重復');
????? 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) 顯示當前用戶的所有程序及其源代碼
???? 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ù)必須是標準的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) 列出當前用戶的子程序
?? 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) 重新編譯子程序
???? 當被應用對象的結(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;
?????
??
1例外簡介
?1） 例外分類
? 預定義分類，非預定義分類，自定義例外。
2 處理預定義例外
? 1) 常用預定義例外
?? 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 命令打開了顯示游標，或執(zhí)行for循環(huán)(隱式的打開游標)
?????? 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("游標已經(jīng)打開");
?????? end;
???? e dup_val_on_index
???? begin
???? exception
??????? when dup_val_on_index then
??????? dbms_output.put_line("列上不能出現(xiàn)重復值");
???? 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("游標沒有打開");
???? 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('超出下標范圍');
????? end;
????? k subscript_outside_limit
?????? emp_array(-1);
????? l value_error the length of variable cannot contain the actual value;
????? declare
????? begin
????? end;
3 處理非預定義例外
? 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è)務邏輯所定義的例外
? 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 應用程序中處理其他為預料的到的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;
1建立包
?1) 建立包規(guī)范： 用于定義包的公共組建，包括常量，變量，游標，過程和函數(shù)等
?create or replace package emp_package is
? g_deptno number(3):=30;
? procedure add_employee(eno number,name varchar2,salary number dno number default g_deptno);
? procedure fire_empoyee(eno number);
? function get_sal(eno number) return number;
?end emp_package;
? 2) 建立包體
?? 用于實現(xiàn)包規(guī)范所定義的過程和函數(shù)。在包體中也可以單獨定義私有組件，包括變量，常量，過程和函數(shù)等。但在包體中所定義的組件只能在包內(nèi)使用，而不能由其他子程序引用。
?? create or replace package body emp_package is
??? function validate_deptno(v_deptno number)
????? return boolean;
??? is
????? v_temp int;
??? begin
????? select 1 into v_temp from dept where deptno=v_deptno;
????? return true;
??? exception
????? when no_data_found then
?????? return false;
??? end;
??? procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
??? is
??? begin
????? if validate_deptno(dno) then
??????? insert into emp(empno,ename,sal,deptno)
???????? values(eno,name,salary,dno);
????? esle
??????? raise_application_error(-20011,'部門不存在');
????? end if;
??? exception
????? when dup_val_on_index then
??????? raise_application_error(-20011,'該雇員已存在')
??? end;
??? procedure fire_employee(eno number) is
??? begin
????? select from emp where empno=eno;
????? if sql%notfound then
???????? raise application_error(-20012,'');
????? end if;
??? end;
?? function get_sal(eno number) return number
?? is
???? v_sal emp.sal%type;
?? begin
???? select sal into v_sal from emp where empno=eno;
???? return v_sal;
?? exception
???? when no_data_found then
?????? raise_application_error(-200012,'');
?? end;
? end emp_package;
? 3) 調(diào)用包組建
??? －－ 在同一個包內(nèi)調(diào)用組建 不需要加包名前綴，直接調(diào)用
? create or replace paclage body emp_package is
??? procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
??? is
??? begin
????? validate_deptno(dno) then
??? exception
??? end;
?? -- 調(diào)用包公用變量
?? exec emp_package.g_deptno:=20
?? -- 調(diào)用公用過程
?? exec emp_package.add_employee(111,'mary,2000');
?? -- 調(diào)用遠程數(shù)據(jù)庫包的公用組件
?? exec
emp_paclage.add_employee@orasrv(1116,'scott',1200);
?? 4) 查看包源代碼
?? select text from user_source where name='emp_package' and type='package';
?? 5) 刪除包
?? drop packagee emp_package;
2 使用包重載
? 指多個具有相同名字的子程序，在調(diào)用的時候使用不同的參數(shù)傳遞。
? 1) 建立包規(guī)范
?? 同名過程和函數(shù)必須具有不同的輸入?yún)?shù)，但同名函數(shù)返回值的類型數(shù)據(jù)必須相同
??? create or replace package overload is
??? function get_sal(eno number) return number;
??? function get_sal(name varchar2) return number;
??? procedure fire_employee(eno number);
??? procedure fire_employee(name varchar2);
?? 2) 建立包體
??? 。。。
3) 使用包構(gòu)造過程
?初始化全局變量
?1 建立包規(guī)范
?? create or replace packiage emp_package
?? is
?? minsal number(6,2);
?? maxsal number(6,2);
?? procedure upd_sal(eno number,salary number);
?? procedure upd_sal(name varhcar2,salary number);
?? end;-- 定義了兩全局變量和三個公用過程
?? 2 建立包體
??? create or replace package body emp_package is
????? procedure add_employee(cno number,name varchar2,salary number,dno number)
????? is
????? begin
??????? if salary between minsal and maxsal then
????????? insert into emp(empno,ename,sal,deptno)
???????? ........
??? -- 構(gòu)造過程，位于子程序尾部，已begin 開始已end 結(jié)束
???? begin
?????? select min(sal),max(sal) into minsal,maxsal from emp;
???? end;
????? end;
3 調(diào)用包公用組建
?? 在同一次會話中第一次調(diào)用包的公用組建時，會自動執(zhí)行其它構(gòu)造函數(shù)，而將來調(diào)用其他組建時則不會再調(diào)用其構(gòu)造過程。
4 使用純度級別
?1 家里包規(guī)范
? create or replcace package purity is
? minsal number(6,2);
? maxsal number(6,2);
? function max_sal return number;
? function min_sal return number;
? pragma restrict_references(max_sal,wnps);--wnps 不能修改包的變量（不能給包的變量賦值）??????????????????????????????????????????? --wnds 不能執(zhí)行dml
? pragma restrict_references(min_sal,wnps);--rnps 用于限制函數(shù)不能讀取包變量
? end;
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 satisfy
? 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'
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 distinct 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.
? create 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;
?
1 觸發(fā)器簡介
? 1) 觸發(fā)事件
? 2) 觸發(fā)條件
? 3) 觸發(fā)操作
???? . 觸發(fā)器代碼的大小不能超過32k，如果使用大量代碼建立觸發(fā)器，應該先建立存儲過程，然后再觸發(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 操作所對應的表
?4) 觸發(fā)器類型 用于指定當觸發(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ā)器
? 當審計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ù)進行復雜的約束，可以使用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ù)變化
，則應該使用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ā)器
??? 當使用行觸發(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ā)器所對應的基表中讀取數(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ù)復制等功能。
?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 等操作，但是對于復雜視圖不允許進行insert，update 和delete 操作。
? 滿足一下條件的為復雜視圖
??? 具有操作集合符 union，union all ，intersect，minus
??? 具有分組函數(shù) min，max，avg，sum，count
??? 具有g(shù)roup by connect 編譯 或start with
??? 具有distinct
??? 具有連接
? 為了在復雜視圖上執(zhí)行dml 操作，必須要基于instead-of 觸發(fā)器，建立instead－of 觸發(fā)器后，就可以基于復雜視圖執(zhí)行insert，update和delete 語句。
?? instead of 選項只使用于視圖
?? 基于視圖建立觸發(fā)器時，不能定義before 和 after
?? 在建立視圖時不能指定 with check option
?? 當建立instead of 觸發(fā)器時，必須指定for each row 選項
? 1) 建立復雜視圖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;
1 pl/sql 集合 處理單列多行數(shù)據(jù)庫，使用的類型為標量類型
?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ù)類型使用。
當使用嵌套表元素時，必須先用其構(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 serveroutput 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 返回當前集合變量中的元素總個數(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 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;
1 subquery: is a select statement embedded in other sql statement.
? .the subquery execute (inner query) once before the main query
? .the result of the subquery is used by the main query.
2 pairwise comarison subquery
?select * from employee
?where (manager_id,department_id) in
????????? (select manager_id,department_id
?????????? from employees
?????????? where employee_id in (178,174))
?and employee_id not in (178,174);
?nonpairwise comparison subquery
?select employee_id,manager_id,department_id
?from employee
?where manager_id in
????????????????? (select manager_id
?????????????????? from employees
?????????????????? where employee id in (174,141))
?and department_id in
?????????????????? (select department_id
??????????????????? from employees
??????????????????? where employee_id in (174,141))
?and employ_id not in (174,141);
????????????????? )
3 using a subquery in the from clause
? select a.last_name,a,salary,b.slaavg
? from employees a ,(select department_id,
????????????????????? avg(salary) salavg
????????????????????? from?? employees
????????????????????? group by department_id) b
?? where a.department_id=b.department_id
?? and a.salary>b.salavg;
4 scalar subquery expressions
? . a scalar subquery expression is a subquery that return exactly on column value from one row
? . in oracle8i scalar subqueries can be used in condition and expression part and all clause.
?1) sclaar subqueries in casse expression
?? select employee_id ,last_name,
?? (case
??? when department_id=
????????? (select department_id from departments
????????? where location_id=1800)
??? then 'canada'
??? else 'usa'
?? end) location
?from employees
?? 2)scalar subqueries in order by clasue
?? select employee_id,last_name
?? from employees e
?? order by (select department_name
???????????? from departments d
???????????? where e.department_id=d.department);
4 correlated subqueries
?? the wubquery references a column form a table in the parment query
?? select column1,solumn2....
?? from table1 outer
?? where column1 operator
???????????????????????? (select column1,column2
????????????????????????? from table2
????????????????????????? where expr1=out.expr2);
? e.g 1
?? select last_name,salary,department_id
?? from employees outer
?? where salary>
?????????????? (select avg(salary)
??????????????? from employees
??????????????? where department_id=
?????????????????? outer.department_id);
?????????????? )
?? e.g 2
??? display details of those employees who have switched jobs at lease twice
??? select e.employee_id,last_name,e.job_id
??? from employees e
??? where 2<=(select count(*)
????????????? from job_history
????????????? where employee_id=e.employee_id);
6 using the exists operator
? . the exists operator tests for existencee of rows in the results set of the subquery
? . if a subquery row value id found:
???? the search does not continue in the inner query
???? the condition is flagged true
? .if a subquery row value is not fount
???? the condition is flagged fasle
???? the search continues in the inner query
?e.g
??? find employees who have at least one person reporting to them
??? select employee_id,last_name,job_id,department_id
??? from employees outer
??? where exists (select count(*)
???????????????? from employees
???????????????? where manager_id=outer.employee_id);
? not exist.
7 corelated update
??? use a correlated subquery to update rows in on table based on rows from another table
?? e.g
??? --denormalize the employees table by adding a column to store the department name
??? alter table employees
??? add(department_name varchar2(14));
?? --populate the table by using a correlated update
??? update employees e
??? set department_name=
?????????????????????? (select department_name
??????????????????????? from departments d
??????????????????????? where e.departmentid=d.department);
8 correlated delete
??? delete test1 t1
??? where ster_id in(select ster_id form sales t2 where t.ster_id=t2.ster_id);?
9 using the with clause ,you can use the same query block in a a select statement when it cocurs more than once within a complex query
? the with clause retrieves the results of a query block and stores it in the user's the user's templary tablespace
? the with clause improves performance.
?e.g
?? with
?? dept_costs as(
??? select d.department_name,sum(e.salary) as dept_total
??? from employee e,departments d
??? where e,department_id=d.department_id
??? group by d.department_name),
?? avg_cost as(
???? select sum(dept_total)/count(*) as dept_avg
???? from dept_cost)
? select *
? from dept_costs
? where dept_total>(select dept_avg
??????????????????? from afb_cost)
? order by department_name;
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;
1
? 1)system parameter file -- spfile<sid>,spfile (oracle 9i or later) is a binary file ,it cann't been edited by the text editor. you can change it through "alter system XXX";
? in oracle 9.2.0,spfile is stored in the directory of ora92\datatase\spfile<sid>.ora.
? 2)init parameter file(pfile)--init<sid>.ora? (early version) is a text file.it is stored in admin\<sid>\pfile.
?2
?? Entries are specific to the instance being accessed;
?? there are two kinds of parameters:
????? explicit: having an entry in the file
????? implicit: no entry within the file,but assuming the oracle default values;
?? Multiple files can be used for a single datasbase to optimize performance in different situation.
??? static parameter file,pfile (init.ora)
??? persistent parameter file,spfile(spfile<sid>.ora);
3 order being used
?first spfile<sid> then int<sid>.ora
?select name,value from v$system_parameter
4 spfile spfilesid.ora
? 1)Binary file with the abiility to make changes persistent across shutdown and startup
? 2)Maintained by oracle server
? 3)Record parameter value change made with the alter system command
? 4)Can specify wether the change being made is tempory or persistent
? 5)value can be delete or reset to allow an instance to revert to the default value;
??? e.g
??? alter system set timed_statistics=false scope=memory;
?? alter system set timed_statistics=false scope=spfile;
?? startup force-- restart oracle
?? select name,value from v$system_parameter where name like 'time%';
?? alter system set timed_statistics=false scope=both;
? 6) not all entries can be modified.to see more ,you can lool up v$system_parameter.in this table the isses_modifiable and issys_modifiable will tell which one? can be modified in session,and which one can be modified in sys, and which one cann't be modified.
?? e.g
?? select name,isses_modifiable from v$system_parameter where isses_modifiable='true';
?? alter session set timed_statistics=false;
?? e.g
?? select name,issys_modifiable from v$system_parameter where issys_modifiable<>'false';
??
?? .issys_modifiable may have two values immediate or deffered.
??? immediate means alter this entry will take effect on the current session ,while derrered will take effect on a new session.
?
5 pfile initsid.ora
? .the pfile is a text file than can be modigied with an operation system editor.
? .modifications to the file are made manually.
? .changes to the file take effect on the next startup
6 Creating an spfile
?? spfile can be created from initsid.ora file using thee create spfile command,whick can be executed before or after instance startup.
?? create spfile from pfile. -- you must shutdown the instance befofe or you can use the fellow statement:
??? create spfile='d:\oracle\oracle\ora92\database\spfile.ora' from pfile;
?yo caan backup the
7 Oracle Managed files(OMF)
? 1) OMF are created and deleted by the oracle server as directed by sql commands
? 2) OMF are established by parameters:
???? DB_CREATE_DEST: set to give the default location for data files
???? DB__CREATE_OMLINE_LOG_DEST_N: set to gieve the default locations for online redo logs and control files,up to a mazimum of 5 locations.
??? e.g 1
??? show parameter db_create
??? e.g 2
??? create tablespace test1;--report error,Do not konw where create the data file
??? alter system set db_create_file_dest='d:\oracle\oradb';
??? create tablespace test1; -- is ok;
??? e.g 3
??? alter database add logilfe group 6;
??? -- if you do not specify the db_create_online_log_dest_N ,the log file will create at???? --the location which is specified by the db_create_file_dest.
??? e.g 4
??? drop table test1; -- oracle alse remove it's physicial file;
??? alter database drop logfile group 6-- also romove the physical file.
??? e.g 5
??? create tablespace test1
??? datafile 'd:\oracle\oradb\test1.dbf' size 2m;
??? drop tablespace test1 -- if you create tablespace or others object in this way ,you?????????????????????????? --will cann't remove the physical file.
??? drop table test1 including comtents and datafiles;
8 Starting up a database mount
?startup nomomount :instance started for this instance;create db.create controlfule
???? 1) create alert_sid.log? startup log which is stored in <oracle_home>\admin\<sid>\bdump
???? 2) start instance include allocating memory and start bpprocess
??? select * from v$instance;
??? select * from v$bgprocess;
??? select * from V$sga;
? alter database mount;
??? open control files for the instance;
??? select* from v$database;
??? select* from v$tablespace;
??? select* from datafile;
??? select* from log;
?alter database open;
??? all files opened as describled by the control file for this instance;
9 start up
? start pfile=...\initsid.ora;?? (alter session set nls_language=american)
? alter database open read only; you can not change database to read only after the database has been created.
? startup database restrict;
? alter system enable restricted session;-- only people with restricted privilege can????????????????????????????????????????? --access the database;
?? alter restricted session to kkk;
?? alter disable restricted session
10 opening a database in read only mode
?? a databse can be opened as read only database alter database open read only;
?? a read-only database can be used to :
???? execute queries
???? execute disk sorts using locally managed
???? take data files offline and online ,not tableespaces
???? perform recovery of offline data files and tablespace;
11Shutting down teh database
? normal: wait until current sessions end,wait until current trasactions end ,force a checkpoint and close files.
? tansactional:wail until current transaction end.force a checkpoint and close files
? immediate :force a checkpoint and close the files
? abort???? : do nothing ,just close the files. when startup ,need recovery.
12 Managing an instance by monitoring Diagnostic Files
? Diagnostic files contain information about significant events encounted while the instance is operational.
?? .user to resolve problem or to better manager the database.
?? .server types of dignostic files exist:
????? alertsid.log? --which location is specified by the background_dump_dest?????????????????????????????????? --entry in the initsid.ora.tje default value is???????????????????? --<ora_home>\admin\sid\bdump
????? background trace files -- the same as above;
????? user_tace files
13 alert log file
?? the alertsid.log file recored the command and result of major event while the database is operational.
?? .it is userd for day-to-day operational information or dignosing database errors
?? .ench entry has a time stamp associated with it.
?? .the dba manager the alertsid.log file.
?? .its location is defined by background_dump_dest.
14 enabling or disabling user tracing
??? . sessin level using the alter session
????? alter session set sql_trace=true;
??? session level by execcuting dbms
????
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;
1 over of multitable insert statements
?1)the insert...select statement can be userd to insert row into multiple table as part of a single dml statement.
?2) multitable insert statements can be used in data warehousing systems to transfer data from one or more operational sources to source to a set of target table.
?3) they providde significant performance improvement over
??? single dml versuls multiple insert...select statement
??? single dml versus a proceedduree to do mutiple inserts using if ,,, then syntax.
2
? unconditional insert
? insert all
??? into sal_history values (EMPID,HIREDATE,SAL)
??? into mgr_history values (EMPID,MGR,SAL)
? select employee_id EMPID,hire_date JIREDATE,
???????? salary SAL,manager_id MGR
? from employees
? where employee_id>200;
3 Conditional insert all
? insert all
?? when sal>1000 then
???? into sal_history values(empid,hiredate,sal)
?? when mgr>200 then
???? into mgr_history values(empid,mgr,sal)
?? select emp_id empid,hire_date hiredate,salary sal,manager_id mgr,
?? from employees
?? where employee_id>200;
4 Conditional first insert
? insert first
??? when sal >25000? then
????? into special_sal values(deptid,sal)
??? when hiredate like ('%00%') then
????? into hiredate_history_00 values(deptid,hiredate)
??? when hiredate like ('%99%') then
????? insert hiredate_history_99 values(ddeptid,hiredate)
??? else
????? into hiredate_history values(deptid,hiredate)
??? select ddepartmeent_id deptid,sum(salary) sal,
???? max(hire_date) hiredate
??? from employees
??? group by department_id;
5 Pivoting insert
?insert all
? into sales_info values (employee_id,week_id,sales_mon)
? into sales_info values (employee_id,week_id,sales_tue)
? into sales_info values (employee_id,week_id,sales_wed)
? into sales_info values (employee_id,week_id,sales_thur)
? into sales_info values (employee_id,week_id,sales_fri)
? select employee_id,weekid,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri
? from sales_source_data;
6 create index with create table statement
? create table new_emp
? (employee_id number(6)
???????? primary key using index
???????? (create index emp_id_idx on new_emp(employee_id)),
?? first_name varchar2(20),
?? last_name varchar2(25)
? )
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'
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();
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;???????????????????????????????????????????
????????????????????????????????????????????????
1 prepared sql
oracle provide two kinds of prepared SQL prepared statements and store procedures.Prepared SQL provide an advantage over the simple sql statements you have convered so far.if you execute the same prepared sql more than once,the database remains ready for your sql without having to rebuild the query plan.
?1) Prepared Statements
?PreparedStatement statement=conn.preparedStatement(
???? "update account set balance=? where id=?");
?for(int i=0;i<accounts.length;i++){
??? statement.setFloat(1,accounts[i].getBalance());
??? statement.setInt(2,i);
??? statement.execut();
??? stateement.clearParameters();
?}
?commit();
?statement.close;
?2) Stored Procedure
?try {
??? CallableStatement statement;
??? int i;
?
??? statement = c.prepareCall("{call sp_interest[(?,?)]}");
?
??? statement.registerOutParameter(2, java.sql.Types.FLOAT);
??? for(i=1; i<accounts.length; i++) {
??????? statement.setInt(1, accounts[i].getId( ));
??????? statement.execute( );
??????? System.out.println("New balance: " + statement.getFloat(2));
??? }
??? c.commit( );
??? statement.close( );
??? c.close( );
}
本文闡述了怎么使用DBMS存儲過程。我闡述了使用存儲過程的基本的和高級特性，比如返回ResultSet。本文假設你對DBMS和JDBC已經(jīng)非常熟悉，也假設你能夠毫無障礙地閱讀其它語言寫成的代碼（即不是Java的語言），但是，并不要求你有任何存儲過程的編程經(jīng)歷。
存儲過程是指保存在數(shù)據(jù)庫并在數(shù)據(jù)庫端執(zhí)行的程序。你可以使用特殊的語法在Java類中調(diào)用存儲過程。在調(diào)用時，存儲過程的名稱及指定的參數(shù)通過JDBC連接發(fā)送給DBMS，執(zhí)行存儲過程并通過連接（如果有）返回結(jié)果。
使用存儲過程擁有和使用基于EJB或CORBA這樣的應用服務器一樣的好處。區(qū)別是存儲過程可以從很多流行的DBMS中免費使用，而應用服務器大都非常昂貴。這并不只是許可證費用的問題。使用應用服務器所需要花費的管理、編寫代碼的費用，以及客戶程序所增加的復雜性，都可以通過DBMS中的存儲過程所整個地替代。
你可以使用Java，Python，Perl或C編寫存儲過程，但是通常使用你的DBMS所指定的特定語言。Oracle使用PL/SQL，PostgreSQL使用pl/pgsql，DB2使用Procedural SQL。這些語言都非常相似。在它們之間移植存儲過程并不比在Sun的EJB規(guī)范不同實現(xiàn)版本之間移植Session Bean困難。并且，存儲過程是為嵌入SQL所設計，這使得它們比Java或C等語言更加友好地方式表達數(shù)據(jù)庫的機制。
因為存儲過程運行在DBMS自身，這可以幫助減少應用程序中的等待時間。不是在Java代碼中執(zhí)行4個或5個SQL語句，而只需要在服務器端執(zhí)行1個存儲過程。網(wǎng)絡上的數(shù)據(jù)往返次數(shù)的減少可以戲劇性地優(yōu)化性能。
使用存儲過程
簡單的老的JDBC通過CallableStatement類支持存儲過程的調(diào)用。該類實際上是PreparedStatement的一個子類。假設我們有一個poets數(shù)據(jù)庫。數(shù)據(jù)庫中有一個設置詩人逝世年齡的存儲過程。下面是對老酒鬼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集成是存儲過程的一個很大的便利：為了從應用中調(diào)用存儲過程，不需要存根（stub）類或者配置文件，除了你的DBMS的JDBC驅(qū)動程序外什么也不需要。
當這段代碼執(zhí)行時，數(shù)據(jù)庫的存儲過程就被調(diào)用。我們沒有去獲取結(jié)果，因為該存儲過程并不返回結(jié)果。執(zhí)行成功或失敗將通過例外得知。失敗可能意味著調(diào)用存儲過程時的失敗（比如提供的一個參數(shù)的類型不正確），或者一個應用程序的失敗（比如拋出一個例外指示在poets數(shù)據(jù)庫中并不存在“Dylan Thomas”）
結(jié)合SQL操作與存儲過程
映射Java對象到SQL表中的行相當簡單，但是通常需要執(zhí)行幾個SQL語句；可能是一個SELECT查找ID，然后一個INSERT插入指定ID的數(shù)據(jù)。在高度規(guī)格化（符合更高的范式，譯注）的數(shù)據(jù)庫模式中，可能需要多個表的更新，因此需要更多的語句。Java代碼會很快地膨脹，每一個語句的網(wǎng)絡開銷也迅速增加。
將這些SQL語句轉(zhuǎn)移到一個存儲過程中將大大簡化代碼，僅涉及一次網(wǎng)絡調(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)。我們應該在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這樣的方法來獲取返回值。當存儲過程返回一個值時，你必須使用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中寫這樣的注釋當然是可以的，但是看起來很凌亂，并且和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);
如果指定了錯誤的返回值類型會怎樣？那么，當調(diào)用存儲過程時將拋出一個RuntimeException，正如你在ResultSet操作中使用了一個錯誤的類型所碰到的一樣。
復雜的返回值
關(guān)于存儲過程的知識，很多人好像就熟悉我們所討論的這些。如果這是存儲過程的全部功能，那么存儲過程就不是其它遠程執(zhí)行機制的替換方案了。存儲過程的功能比這強大得多。
當你執(zhí)行一個SQL查詢時，DBMS創(chuàng)建一個叫做cursor（游標）的數(shù)據(jù)庫對象，用于在返回結(jié)果中迭代每一行。ResultSet是當前時間點的游標的一個表示。這就是為什么沒有緩存或者特定數(shù)據(jù)庫的支持，你只能在ResultSet中向前移動。
某些DBMS允許從存儲過程中返回游標的一個引用。JDBC并不支持這個功能，但是Oracle、PostgreSQL和DB2的JDBC驅(qū)動器都支持在ResultSet上打開到游標的指針（pointer）。
設想列出所有沒有活到退休年齡的詩人，下面是完成這個功能的存儲過程，返回一個打開的游標，同樣也使用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并不直接支持從存儲過程中返回游標，我們使用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í)行任意的處理，而不需要改變或者復制獲取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é)果寫入到輸出流。當然，這個技巧并不是存儲過程特有的，但是和存儲過程中返回的ResultSet結(jié)合使用，是一個非常強大的工具。
結(jié)論
存儲過程可以幫助你在代碼中分離邏輯，這基本上總是有益的。這個分離的好處有：
• 快速創(chuàng)建應用，使用和應用一起改變和改善的數(shù)據(jù)庫模式。
• 數(shù)據(jù)庫模式可以在以后改變而不影響Java對象，當我們完成應用后，可以重新設計更好的模式。
• 存儲過程通過更好的SQL嵌入使得復雜的SQL更容易理解。
• 編寫存儲過程比在Java中編寫嵌入的SQL擁有更好的工具－－大部分編輯器都提供語法高亮！
• 存儲過程可以在任何SQL命令行中測試，這使得調(diào)試更加容易。
并不是所有的數(shù)據(jù)庫都支持存儲過程，但是存在許多很棒的實現(xiàn)，包括免費/開源的和非免費的，所以移植并不是一個問題。Oracle、PostgreSQL和DB2都有類似的存儲過程語言，并且有在線的社區(qū)很好地支持。
存儲過程工具很多，有像TOAD或TORA這樣的編輯器、調(diào)試器和IDE，提供了編寫、維護PL/SQL或pl/pgsql的強大的環(huán)境。
存儲過程確實增加了你的代碼的開銷，但是它們和大多數(shù)的應用服務器相比，開銷小得多。如果你的代碼復雜到需要使用DBMS，我建議整個采用存儲過程的方式。
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)框架代碼的時候，當你對采用那種實現(xiàn)方式猶豫不決的時，換個角度，想一想如果你是程序員，喜歡怎么這些框架。在實現(xiàn)框架的時候一定要考慮程序員是否能夠理解你寫框架的思路，除非萬不得已不要用一些自以為很高明很巧妙，然而卻很晦澀難懂的方法，那樣的框架，程序員至少合格的程序員是不愿意使用的。我想程序員和編碼工人最大的區(qū)別就是程序員不僅要知其然，還要知其所以然。
3 只有在不斷實踐中，才能激發(fā)你不斷的求知欲。只有把學到的知識不斷的應用道實踐中，你才能在學習中得到滿足。不要為了學習而學習（學院派，不好聽點就是紙上談兵），而是要從實際問題出發(fā)，在解決問題的過程中不斷深入，不斷總結(jié)，所以說，當你離開了編程的第一線，你將失去學習編程知識的欲望。當然如果你愿意，在別的領(lǐng)域還有更廣闊的天空，但是請不要總是說自己原來編程怎么怎么，其實你已經(jīng)被三振出局了。
4 想外行一樣思考，想專家一樣實踐，一本書的名字，雖然書沒有看過，但她的名子就已經(jīng)非常有意思了。這豈不就是我們作需求，和作架構(gòu)時的座右銘嗎？既能象“外行”一樣的站在客戶的角度思考問題，又能象“專家”一樣參與到整個產(chǎn)品的開發(fā)和實施當中，在實踐中不斷提高自我。然而，不幸的是許許多多的所謂的架構(gòu)師，系統(tǒng)分析員們卻正向著相反的方向邁進。“真正”的做到了，象“專家”一樣思考，象“外行”一樣實踐，可悲呀可悲。
5設計做到什么樣才叫做到位呢。我想只有真正的開發(fā)者才有權(quán)利發(fā)言。只有有它們才是設計的真正使用者和受害者。因為就我所知和所見，絕大多數(shù)設計都是設計者自己的游戲（當然，我可能是井底之蛙了沒有見過什么好的設計），程序員所開發(fā)往往還是對著原形自己再進行一遍設計，且不說額外增加了多少工作量，浪費了多少時間，就工作質(zhì)量而言，也是差強人意。畢竟大多數(shù)情況下，設計者或稱為架構(gòu)師的在技術(shù)方面的經(jīng)驗都更為豐富，對業(yè)務的理解也更為深入，另外由一個人進行設計在功能復用，和整體性能方面的考慮也更完整一些。但怎么做才能熊掌和魚兼得呢？下面我發(fā)表一下我個人的看法：
? 1 代碼就是最好的設計，這句話不是我說的，是 xp開發(fā)屆 中的一位大牛說的。之所以在這里引用別人的觀點，并不是自己是一個xp 的fans，也并不時完全贊同xp 的理論，我只是覺得這句話得太對了，對程序員來說什么設計比代碼讀起來更親切呢？。其實設計無非是向開發(fā)所著傳達設計者的思想，告訴開發(fā)者系統(tǒng)需要開什么個對象，具有什么屬性和行為，它們之間的調(diào)用關(guān)系又如何。我們在設計文檔中經(jīng)常使用的方法就是有class 圖，協(xié)作圖，和順序圖對上面所提到的進行描述。然而結(jié)果呢，面對這大量的令人畏懼的抽象圖表，開發(fā)者可選擇的也只有是“重整江河待后生了”。想想，這樣的設計和代碼能夠同步嗎，這樣的設計文檔還有什么用呢？所以說與其是這樣還不如把設計變成代碼，如對象屬性可以這直接在代碼中體現(xiàn)，方法可以只定義接口，實現(xiàn)方式可以作為代碼的注釋，向?qū)懶枨蠓治鲇美频膩硪徊揭徊秸f明程序是需要怎樣調(diào)用。當客戶要求設文檔的時候，只需要提出javadoc就可以了，而其保證和代碼同步。而開發(fā)者呢，在開發(fā)前需要閱讀用例，了解需求，然后在設計者已經(jīng)搭好的代碼框架中進行開發(fā)就可以了。如果需要修改的話，不用在去設計文檔中更改，只需要修改一下代碼注釋就可以了，（程序員是比較懶的，不怎么愿意寫寫文檔的）。當然了，讓懶惰的程序員能夠自覺地寫好文檔也不是一件容易事，下面也許能給你提供一個好的方法
? 2 交差開發(fā)能夠幫助完成最好的設計文檔。
? 3 設計者在開發(fā)階段還作什么呢？?????????????????
待續(xù)???????????????????????????????????????????????????????????????