1 動(dòng)態(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 在動(dòng)態(tài)sql 中使用bulk語句
?? 1) 在 execute immediate 語句中使用動(dòng)態(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 的實(shí)現(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 的實(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;???????????????????????????????????????????
????????????????????????????????????????????????
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存儲(chǔ)過程。我闡述了使用存儲(chǔ)過程的基本的和高級(jí)特性，比如返回ResultSet。本文假設(shè)你對(duì)DBMS和JDBC已經(jīng)非常熟悉，也假設(shè)你能夠毫無障礙地閱讀其它語言寫成的代碼（即不是Java的語言），但是，并不要求你有任何存儲(chǔ)過程的編程經(jīng)歷。
存儲(chǔ)過程是指保存在數(shù)據(jù)庫并在數(shù)據(jù)庫端執(zhí)行的程序。你可以使用特殊的語法在Java類中調(diào)用存儲(chǔ)過程。在調(diào)用時(shí)，存儲(chǔ)過程的名稱及指定的參數(shù)通過JDBC連接發(fā)送給DBMS，執(zhí)行存儲(chǔ)過程并通過連接（如果有）返回結(jié)果。
使用存儲(chǔ)過程擁有和使用基于EJB或CORBA這樣的應(yīng)用服務(wù)器一樣的好處。區(qū)別是存儲(chǔ)過程可以從很多流行的DBMS中免費(fèi)使用，而應(yīng)用服務(wù)器大都非常昂貴。這并不只是許可證費(fèi)用的問題。使用應(yīng)用服務(wù)器所需要花費(fèi)的管理、編寫代碼的費(fèi)用，以及客戶程序所增加的復(fù)雜性，都可以通過DBMS中的存儲(chǔ)過程所整個(gè)地替代。
你可以使用Java，Python，Perl或C編寫存儲(chǔ)過程，但是通常使用你的DBMS所指定的特定語言。Oracle使用PL/SQL，PostgreSQL使用pl/pgsql，DB2使用Procedural SQL。這些語言都非常相似。在它們之間移植存儲(chǔ)過程并不比在Sun的EJB規(guī)范不同實(shí)現(xiàn)版本之間移植Session Bean困難。并且，存儲(chǔ)過程是為嵌入SQL所設(shè)計(jì)，這使得它們比Java或C等語言更加友好地方式表達(dá)數(shù)據(jù)庫的機(jī)制。
因?yàn)榇鎯?chǔ)過程運(yùn)行在DBMS自身，這可以幫助減少應(yīng)用程序中的等待時(shí)間。不是在Java代碼中執(zhí)行4個(gè)或5個(gè)SQL語句，而只需要在服務(wù)器端執(zhí)行1個(gè)存儲(chǔ)過程。網(wǎng)絡(luò)上的數(shù)據(jù)往返次數(shù)的減少可以戲劇性地優(yōu)化性能。
使用存儲(chǔ)過程
簡單的老的JDBC通過CallableStatement類支持存儲(chǔ)過程的調(diào)用。該類實(shí)際上是PreparedStatement的一個(gè)子類。假設(shè)我們有一個(gè)poets數(shù)據(jù)庫。數(shù)據(jù)庫中有一個(gè)設(shè)置詩人逝世年齡的存儲(chǔ)過程。下面是對(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ǔ)過程調(diào)用的書寫規(guī)范。它指定了存儲(chǔ)過程的名稱，？代表了你需要指定的參數(shù)。
和JDBC集成是存儲(chǔ)過程的一個(gè)很大的便利：為了從應(yīng)用中調(diào)用存儲(chǔ)過程，不需要存根（stub）類或者配置文件，除了你的DBMS的JDBC驅(qū)動(dòng)程序外什么也不需要。
當(dāng)這段代碼執(zhí)行時(shí)，數(shù)據(jù)庫的存儲(chǔ)過程就被調(diào)用。我們沒有去獲取結(jié)果，因?yàn)樵摯鎯?chǔ)過程并不返回結(jié)果。執(zhí)行成功或失敗將通過例外得知。失敗可能意味著調(diào)用存儲(chǔ)過程時(shí)的失敗（比如提供的一個(gè)參數(shù)的類型不正確），或者一個(gè)應(yīng)用程序的失敗（比如拋出一個(gè)例外指示在poets數(shù)據(jù)庫中并不存在“Dylan Thomas”）
結(jié)合SQL操作與存儲(chǔ)過程
映射Java對(duì)象到SQL表中的行相當(dāng)簡單，但是通常需要執(zhí)行幾個(gè)SQL語句；可能是一個(gè)SELECT查找ID，然后一個(gè)INSERT插入指定ID的數(shù)據(jù)。在高度規(guī)格化（符合更高的范式，譯注）的數(shù)據(jù)庫模式中，可能需要多個(gè)表的更新，因此需要更多的語句。Java代碼會(huì)很快地膨脹，每一個(gè)語句的網(wǎng)絡(luò)開銷也迅速增加。
將這些SQL語句轉(zhuǎn)移到一個(gè)存儲(chǔ)過程中將大大簡化代碼，僅涉及一次網(wǎng)絡(luò)調(diào)用。所有關(guān)聯(lián)的SQL操作都可以在數(shù)據(jù)庫內(nèi)部發(fā)生。并且，存儲(chǔ)過程語言，例如PL/SQL，允許使用SQL語法，這比Java代碼更加自然。下面是我們?cè)缙诘拇鎯?chǔ)過程，使用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;
很獨(dú)特？不。我打賭你一定期待看到一個(gè)poets表上的UPDATE。這也暗示了使用存儲(chǔ)過程實(shí)現(xiàn)是多么容易的一件事情。set_death_age幾乎可以肯定是一個(gè)很爛的實(shí)現(xiàn)。我們應(yīng)該在poets表中添加一列來存儲(chǔ)逝世年齡。Java代碼中并不關(guān)心數(shù)據(jù)庫模式是怎么實(shí)現(xiàn)的，因?yàn)樗鼉H調(diào)用存儲(chǔ)過程。我們以后可以改變數(shù)據(jù)庫模式以提高性能，但是我們不必修改我們代碼。
下面是調(diào)用上面存儲(chǔ)過程的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();
}
}
為了確保可維護(hù)性，建議使用像這兒這樣的static方法。這也使得調(diào)用存儲(chǔ)過程的代碼集中在一個(gè)簡單的模版代碼中。如果你用到許多存儲(chǔ)過程，就會(huì)發(fā)現(xiàn)僅需要拷貝、粘貼就可以創(chuàng)建新的方法。因?yàn)榇a的模版化，甚至也可以通過腳本自動(dòng)生產(chǎn)調(diào)用存儲(chǔ)過程的代碼。
Functions
存儲(chǔ)過程可以有返回值，所以CallableStatement類有類似getResultSet這樣的方法來獲取返回值。當(dāng)存儲(chǔ)過程返回一個(gè)值時(shí)，你必須使用registerOutParameter方法告訴JDBC驅(qū)動(dòng)器該值的SQL類型是什么。你也必須調(diào)整存儲(chǔ)過程調(diào)用來指示該過程返回一個(gè)值。
下面接著上面的例子。這次我們查詢Dylan Thomas逝世時(shí)的年齡。這次的存儲(chǔ)過程使用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語法引用。同時(shí)，也注意嵌入的注釋，這是和Java代碼相比的另一個(gè)優(yōu)越性。在Java中寫這樣的注釋當(dāng)然是可以的，但是看起來很凌亂，并且和SQL語句脫節(jié)，必須嵌入到Java String中。
下面是調(diào)用這個(gè)存儲(chǔ)過程的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ǔ)過程時(shí)將拋出一個(gè)RuntimeException，正如你在ResultSet操作中使用了一個(gè)錯(cuò)誤的類型所碰到的一樣。
復(fù)雜的返回值
關(guān)于存儲(chǔ)過程的知識(shí)，很多人好像就熟悉我們所討論的這些。如果這是存儲(chǔ)過程的全部功能，那么存儲(chǔ)過程就不是其它遠(yuǎn)程執(zhí)行機(jī)制的替換方案了。存儲(chǔ)過程的功能比這強(qiáng)大得多。
當(dāng)你執(zhí)行一個(gè)SQL查詢時(shí)，DBMS創(chuàng)建一個(gè)叫做cursor（游標(biāo)）的數(shù)據(jù)庫對(duì)象，用于在返回結(jié)果中迭代每一行。ResultSet是當(dāng)前時(shí)間點(diǎn)的游標(biāo)的一個(gè)表示。這就是為什么沒有緩存或者特定數(shù)據(jù)庫的支持，你只能在ResultSet中向前移動(dòng)。
某些DBMS允許從存儲(chǔ)過程中返回游標(biāo)的一個(gè)引用。JDBC并不支持這個(gè)功能，但是Oracle、PostgreSQL和DB2的JDBC驅(qū)動(dòng)器都支持在ResultSet上打開到游標(biāo)的指針（pointer）。
設(shè)想列出所有沒有活到退休年齡的詩人，下面是完成這個(gè)功能的存儲(chǔ)過程，返回一個(gè)打開的游標(biāo)，同樣也使用PostgreSQL的pl/pgsql語言：
create procedure list_early_deaths () return refcursor as ''declare
toesup refcursor;
begin
open toesup for SELECT poets.name, deaths.age FROM poets, deaths -- all entries in deaths are for poets. -- but the table might become generic.
WHERE poets.id = deaths.mort_id AND deaths.age < 60;
return toesup;
end;'' language ''plpgsql'';
下面是調(diào)用該存儲(chǔ)過程的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ǔ)過程中返回游標(biāo)，我們使用Types.OTHER來指示存儲(chǔ)過程的返回類型，然后調(diào)用getObject()方法并對(duì)返回值進(jìn)行強(qiáng)制類型轉(zhuǎn)換。
這個(gè)調(diào)用存儲(chǔ)過程的Java方法是mapping的一個(gè)好例子。Mapping是對(duì)一個(gè)集上的操作進(jìn)行抽象的方法。不是在這個(gè)過程上返回一個(gè)集，我們可以把操作傳送進(jìn)去執(zhí)行。本例中，操作就是把ResultSet打印到一個(gè)輸出流。這是一個(gè)值得舉例的很常用的例子，下面是調(diào)用同一個(gè)存儲(chǔ)過程的另外一個(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é)果寫入到輸出流。當(dāng)然，這個(gè)技巧并不是存儲(chǔ)過程特有的，但是和存儲(chǔ)過程中返回的ResultSet結(jié)合使用，是一個(gè)非常強(qiáng)大的工具。
結(jié)論
存儲(chǔ)過程可以幫助你在代碼中分離邏輯，這基本上總是有益的。這個(gè)分離的好處有：
• 快速創(chuàng)建應(yīng)用，使用和應(yīng)用一起改變和改善的數(shù)據(jù)庫模式。
• 數(shù)據(jù)庫模式可以在以后改變而不影響Java對(duì)象，當(dāng)我們完成應(yīng)用后，可以重新設(shè)計(jì)更好的模式。
• 存儲(chǔ)過程通過更好的SQL嵌入使得復(fù)雜的SQL更容易理解。
• 編寫存儲(chǔ)過程比在Java中編寫嵌入的SQL擁有更好的工具－－大部分編輯器都提供語法高亮！
• 存儲(chǔ)過程可以在任何SQL命令行中測(cè)試，這使得調(diào)試更加容易。
并不是所有的數(shù)據(jù)庫都支持存儲(chǔ)過程，但是存在許多很棒的實(shí)現(xiàn)，包括免費(fèi)/開源的和非免費(fèi)的，所以移植并不是一個(gè)問題。Oracle、PostgreSQL和DB2都有類似的存儲(chǔ)過程語言，并且有在線的社區(qū)很好地支持。
存儲(chǔ)過程工具很多，有像TOAD或TORA這樣的編輯器、調(diào)試器和IDE，提供了編寫、維護(hù)PL/SQL或pl/pgsql的強(qiáng)大的環(huán)境。
存儲(chǔ)過程確實(shí)增加了你的代碼的開銷，但是它們和大多數(shù)的應(yīng)用服務(wù)器相比，開銷小得多。如果你的代碼復(fù)雜到需要使用DBMS，我建議整個(gè)采用存儲(chǔ)過程的方式。
1 不是使用了spring ，hibernate 等企業(yè)級(jí)產(chǎn)品的框架，我們就是企業(yè)級(jí)產(chǎn)品了。不是我們采用了新瓶裝舊酒的web 2.0 我們就走在技術(shù)的前沿了。我門所需要的是一個(gè)高性能的，健壯的 產(chǎn)品，是一個(gè)可以降低我們實(shí)施成本，一個(gè)可以樹立我們企業(yè)品牌的產(chǎn)品。在這里我不得不對(duì)我們產(chǎn)品的所謂的架構(gòu)們產(chǎn)品疑問，Archetectures,what are you doing？
2 在實(shí)現(xiàn)框架代碼的時(shí)候，當(dāng)你對(duì)采用那種實(shí)現(xiàn)方式猶豫不決的時(shí)，換個(gè)角度，想一想如果你是程序員，喜歡怎么這些框架。在實(shí)現(xiàn)框架的時(shí)候一定要考慮程序員是否能夠理解你寫框架的思路，除非萬不得已不要用一些自以為很高明很巧妙，然而卻很晦澀難懂的方法，那樣的框架，程序員至少合格的程序員是不愿意使用的。我想程序員和編碼工人最大的區(qū)別就是程序員不僅要知其然，還要知其所以然。
3 只有在不斷實(shí)踐中，才能激發(fā)你不斷的求知欲。只有把學(xué)到的知識(shí)不斷的應(yīng)用道實(shí)踐中，你才能在學(xué)習(xí)中得到滿足。不要為了學(xué)習(xí)而學(xué)習(xí)（學(xué)院派，不好聽點(diǎn)就是紙上談兵），而是要從實(shí)際問題出發(fā)，在解決問題的過程中不斷深入，不斷總結(jié)，所以說，當(dāng)你離開了編程的第一線，你將失去學(xué)習(xí)編程知識(shí)的欲望。當(dāng)然如果你愿意，在別的領(lǐng)域還有更廣闊的天空，但是請(qǐng)不要總是說自己原來編程怎么怎么，其實(shí)你已經(jīng)被三振出局了。
4 想外行一樣思考，想專家一樣實(shí)踐，一本書的名字，雖然書沒有看過，但她的名子就已經(jīng)非常有意思了。這豈不就是我們作需求，和作架構(gòu)時(shí)的座右銘嗎？既能象“外行”一樣的站在客戶的角度思考問題，又能象“專家”一樣參與到整個(gè)產(chǎn)品的開發(fā)和實(shí)施當(dāng)中，在實(shí)踐中不斷提高自我。然而，不幸的是許許多多的所謂的架構(gòu)師，系統(tǒng)分析員們卻正向著相反的方向邁進(jìn)。“真正”的做到了，象“專家”一樣思考，象“外行”一樣實(shí)踐，可悲呀可悲。
5設(shè)計(jì)做到什么樣才叫做到位呢。我想只有真正的開發(fā)者才有權(quán)利發(fā)言。只有有它們才是設(shè)計(jì)的真正使用者和受害者。因?yàn)榫臀宜退姡^大多數(shù)設(shè)計(jì)都是設(shè)計(jì)者自己的游戲（當(dāng)然，我可能是井底之蛙了沒有見過什么好的設(shè)計(jì)），程序員所開發(fā)往往還是對(duì)著原形自己再進(jìn)行一遍設(shè)計(jì)，且不說額外增加了多少工作量，浪費(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ù)用，和整體性能方面的考慮也更完整一些。但怎么做才能熊掌和魚兼得呢？下面我發(fā)表一下我個(gè)人的看法：
? 1 代碼就是最好的設(shè)計(jì)，這句話不是我說的，是 xp開發(fā)屆 中的一位大牛說的。之所以在這里引用別人的觀點(diǎn)，并不是自己是一個(gè)xp 的fans，也并不時(shí)完全贊同xp 的理論，我只是覺得這句話得太對(duì)了，對(duì)程序員來說什么設(shè)計(jì)比代碼讀起來更親切呢？。其實(shí)設(shè)計(jì)無非是向開發(fā)所著傳達(dá)設(shè)計(jì)者的思想，告訴開發(fā)者系統(tǒng)需要開什么個(gè)對(duì)象，具有什么屬性和行為，它們之間的調(diào)用關(guān)系又如何。我們?cè)谠O(shè)計(jì)文檔中經(jīng)常使用的方法就是有class 圖，協(xié)作圖，和順序圖對(duì)上面所提到的進(jìn)行描述。然而結(jié)果呢，面對(duì)這大量的令人畏懼的抽象圖表，開發(fā)者可選擇的也只有是“重整江河待后生了”。想想，這樣的設(shè)計(jì)和代碼能夠同步嗎，這樣的設(shè)計(jì)文檔還有什么用呢？所以說與其是這樣還不如把設(shè)計(jì)變成代碼，如對(duì)象屬性可以這直接在代碼中體現(xiàn)，方法可以只定義接口，實(shí)現(xiàn)方式可以作為代碼的注釋，向?qū)懶枨蠓治鲇美频膩硪徊揭徊秸f明程序是需要怎樣調(diào)用。當(dāng)客戶要求設(shè)文檔的時(shí)候，只需要提出javadoc就可以了，而其保證和代碼同步。而開發(fā)者呢，在開發(fā)前需要閱讀用例，了解需求，然后在設(shè)計(jì)者已經(jīng)搭好的代碼框架中進(jìn)行開發(fā)就可以了。如果需要修改的話，不用在去設(shè)計(jì)文檔中更改，只需要修改一下代碼注釋就可以了，（程序員是比較懶的，不怎么愿意寫寫文檔的）。當(dāng)然了，讓懶惰的程序員能夠自覺地寫好文檔也不是一件容易事，下面也許能給你提供一個(gè)好的方法
? 2 交差開發(fā)能夠幫助完成最好的設(shè)計(jì)文檔。
? 3 設(shè)計(jì)者在開發(fā)階段還作什么呢？?????????????????
待續(xù)???????????????????????????????????????????????????????????????