[转]两千字，让你成为Oracle高手！（想成为ORACLE高手者，不容错过）

学Oracle，难！不看书，不知道怎么回事，看书吧，都是四五百页，而且其间废话多多！是不是做梦都希望能有一本书，只有三页，却能囊括存储过程、函数、Trigger、游标......能让自己一夜之间从Oracle新手变成高手？！

　　本文，不能让你一夜之间成为DBA，不能让你从新手变成高手，但是却能让你在转眼间就对Oracle的存储过程、函数、 Trigger、游标等从不会到会，虽然不是高手，但是其实大多数高手也不过是停留在使用的层面的啊！本文从最简单的实例入手，撇开麻雀，让你只看其心脏！所以，如果您对以上问题还不了解，或者不熟悉，那么请看完本文，而且，相信您看完这篇文章，绝对不会后悔！好了，牛就不多吹了，让我们开始吧！

　　１、要用数据库，当然得建个用户吧？

　　CREATE USER user_fengzhi IDENTIFIED BY password_fengzhi

　　　　 DEFAULT TABLESPACE tablespace_fengzhi

 　　　　TEMPORARY TABLESPACE TEMP

　　　　 PROFILE DEFAULT

　　　　 ACCOUNT UNLOCK;

　　GRANT DBA TO user_fengzhi;

　　　　--建立一个用户user_fengzhi密码是password_fengzhi，默认使用的表空间是tablespace_fengzhi，

　　默认的临时表空间是TEMP，使用系统默认的资源文件配置，并授予其DBA权限！

　　２、刚才说到默认的表空间，现在就来看看怎么建立表空间吧：

　　  CREATE TABLESPACE SD0_DRM DATAFILE

　　　　 'D:oracleproduct.2.0oradataopbSD0_DRM.DBF' SIZE 200M AUTOEXTEND ON NEXT 　　　　　　　　　1280K MAXSIZE 1000M

　　　　　　LOGGING　ONLINE　PERMANENT

　　　　　　EXTENT MANAGEMENT LOCAL AUTOALLOCATE

　　　　　　BLOCKSIZE 8K

　　　　　　SEGMENT SPACE MANAGEMENT AUTO;

　　建一个表空间，所用的数据存储文件是D:oracleproduct.2.0oradataopbSD0_DRM.DBF（注意这时候系统中必须有目录：D:oracleproduct.2.0oradataopb），初始容量为２００M，当超出容量是每次以１２８０K的方式递增，但最大为１０００M！允许通过网络访问，使用自动段管理，规定块的大小为８K！

　　３、建表：  create table fz_user(username varchar(10),userpassword varchar(10),Email 　varchar(200));

　　--实现列名的中文化

　　　COMMENT ON COLUMN username IS '用户名';

　　前面都是基础，一般在工程开始时，就已经都OK了，看看后面是经常要用上的！　

　　４、  游标使用。

    DECLARE

     v_username fz_user.username%TYPE;　

　　　　--游标中的变量 v_username数据类型和fz_user表的　username一样！

    　　 v_userpassword fz_user.userpassword%TYPE;

     　　v_email fz_user.email%TYPE;

     

     　　CURSOR c_fz(v_username fz_user.username%TYPE) IS

        　SELECT * FROM fz_user FOR UPDATE;

    BEGIN

　　--打开游标

     OPEN c_fz('tuser');

     

     　　　--从游标中取出结果，并显示。

     

     LOOP　--通过循环把游标中的值存入游标变量

        FETCH c_fz INTO v_username,v_userpassword,v_email;

       

        EXIT WHEN c_fz%NOTFOUND;　　--c_fz%NOTFOUND表示本次循环在游标中没有找到结果

       

        UPDATE fz_user SET email=v_username||'@neusoft.com' WHERE CURRENT OF c_fz;

       

        DBMS_OUTPUT.put_line(v_username||'#'||v_userpassword||'#'||v_email||'#');　　

　　　　　--用Oracle系统函数输出结果

       

     END LOOP;

     CLOSE c_fz;　　--关闭游标

    END;

　　　　５、  存储过程　PROCEDURE

   

    CREATE OR REPLACE PROCEDURE fz_p_user(

      p_username fz_user.username%TYPE,

     P_userpassword fz_user.userpassword%TYPE,

     p_email fz_user.email%TYPE DEFAULT ‘baidu@baidu.com’)　is

    BEGIN

     INSERT INTO fz_user VALUES(p_username,p_userpassword,p_email);

     

     COMMIT;

    END fz_p_user;　--注意结束时也必须要存储过程名！

存储过程调用

    BEGIN

     fz_p_user('tu1','tp1','te1');

    END;

 

　　存储过程看起来和函数十分相似，所以一定要分清他们之间的界线：存储过程调用本身就是一个PLSQL语句，而函数调用只能是表达式的右值！

 

　　６、函数 function

CREATE OR REPLACE FUNCTION fz_f_user(

 f_usernamein fz_user.username%TYPE) --函数中的参数！

RETURN BOOLEAN IS　　--返回类型

 f_username fz_user.username%TYPE;

 f_userpassword fz_user.userpassword%TYPE;

 f_email fz_user.email%TYPE;

 CURSOR incursor IS

      SELECT username,userpassword,email

      FROM fz_user WHERE username=f_usernamein; 　--注意此处将游标也作为一个成员数据

BEGIN

 

 OPEN incursor;

 FETCH incursor INTO f_username,f_userpassword,f_email;

 

 IF(incursor%FOUND) THEN

    RETURN TRUE;

 ELSE RETURN FALSE;

 

 END IF;

 

 CLOSE incursor; 

END fz_f_user; 

　　函数调用

DECLARE

i_username fz_user.username%TYPE:='tu2';

i_userpassword fz_user.userpassword%TYPE:='tp2';

i_email fz_user.email%TYPE:='te2';

BEGIN

 IF fz_f_user(i_username) THEN 　--调用函数，根据返回值的真假进行相应的处理

 UPDATE fz_user SET userpassword=i_userpassword,email=i_email

    WHERE username=i_username;

 --若返回值为假！

 ELSE INSERT INTO fz_user VALUES(i_username,i_userpassword,i_email);

 END IF;

 COMMIT;

END;

　　７、  触发器TRIGGER

CREATE OR REPLACE TRIGGER fz_t_UPDATE

 AFTER UPDATE -- 可以加上”　or insert “等　触发时机

 ON fz_user FOR EACH ROW　　--建触发器的表

 

 DECLARE CURSOR c_update IS

    SELECT * FROM fz_score WHERE username=:old.username;

 

BEGIN

 FOR results IN c_update LOOP

    UPDATE fz_score SET score=results.score+1 WHERE username=results.username;

    IF SQL%NOTFOUND THEN NULL;

    END IF;

 END LOOP;

END fz_t_UPDATE;

　　８、  EXCEPTION 综合示例　--exception 平时用的相对比较少，但是学会了还是对我们的SQL编程能力提高大有帮助！

--该示例要注意蓝色部分，什么时候COMMIT,什么时候OPEN/CLOSE游标

尤其是要注意他们怎么样配对的！

 

DECLARE

 v_username fz_user.username%TYPE:='tu5';

 v_userpassword fz_user.userpassword%TYPE:='t';

 v_email fz_user.email%TYPE:='t';

 v_score fz_score.score%TYPE:=1 ;

 

 v_nodata EXCEPTION;

 v_exception EXCEPTION;

 CURSOR c_fz IS

    SELECT * FROM fz_user WHERE username=v_username;

BEGIN

 OPEN c_fz;

    FETCH c_fz INTO v_username,v_userpassword,v_email;

    --如果FZ_USER没有相应的值则插入

    IF c_fz%NOTFOUND THEN

      INSERT INTO fz_user VALUES(v_username,v_userpassword,v_email);

 --如果已经存在则抛出异常，并把控制权交给异常处理部分！

    ELSE

      RAISE v_exception;

    END IF;

 CLOSE c_fz;

COMMIT;

EXCEPTION

  WHEN v_exception THEN

 --为了让游标指针指向开头，所以先关闭在打开游标

 CLOSE c_fz;　　--必须先关闭再打开

 OPEN c_fz;

 --修改SCORE 的值。

 SELECT score INTO v_score FROM fz_score WHERE username=v_username;

    LOOP

      FETCH c_fz INTO v_username,v_userpassword,v_email;

      EXIT WHEN c_fz%NOTFOUND;

      v_score:=v_score+1;

      --修改fz-score表

      UPDATE fz_score SET score=v_score WHERE username=v_username;

    END LOOP;

COMMIT;

END;

　　啊，OK了！总算写完了！！其实ORACLE的东西我们能经常用到的也就这么多了！而且其他数据库可能在语法上稍微有差异，但是大致还是差不多的！所以，如果您有兴趣多花一点时间把上面的示例吃透的，对于数据库而言，应付平时的使用，应该就没有什么问题了！　如果觉得本文有什么问题，希望不吝赐教！