精华内容
下载资源
问答
  • Oracle存储过程实例;过程;存储过程特点;存储过程特点;过程的语法结构;创建过程实例;参数;参数过程实例;仅有输入参数的过程 create or replace procedure stu_proc1(pno in student.sno%type) as pname varchar2(25; ...
  • Oracle存储过程实例

    千次阅读 2019-05-14 14:10:11
    Oracle存储过程 存储过程 存储过程语法 例子 计算信用积分的存储过程: --记录存储过程log的存储过程(方法函数) sp_exception_log create or replace procedure sp_exception_log(v_date in date, v_proc_nam...

    Oracle存储过程

    存储过程

    存储过程语法

    例子

    计算信用积分的存储过程:

    --记录存储过程log的存储过程(方法函数) sp_exception_log
    create or replace procedure sp_exception_log(v_date      in date,
                                                 v_proc_name in varchar2,
                                                 v_state     in varchar2,
                                                 v_msg       in varchar2,
                                                 v_num       number) is
    ---------------------------------------------------------------------
    
    begin
      insert into check_exception_day
      values
        (v_date, v_proc_name, v_state, v_msg, v_num);
      commit;
    exception
      when others then
        null;
    end;
    
    
    CREATE OR REPLACE PROCEDURE SP_GR_GHF_PFLC(C_DATE VARCHAR2) IS
    
     -- V_FLAG     NUMBER;
      L_DATE     VARCHAR2(8);
      V_ERR_FLAG INT := 0;
      V_ERROR    VARCHAR2(200);
      V_NOW_YEAR varchar2(8);
      v_count    number(12);
    BEGIN
    
      L_DATE := TO_CHAR(TO_DATE(C_DATE, 'YYYYMMDD') - 1, 'YYYYMMDD');
      V_NOW_YEAR  := TO_CHAR(TO_DATE(C_DATE, 'YYYYMMDD'), 'YYYY');
    -- 开始执行
      BEGIN
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         0);
      END;
      
    -- 个人基本信息表按照身份证去重,创建临时表
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop table yw_p_grjbxx_qc';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        EXECUTE IMMEDIATE 'CREATE TABLE yw_p_grjbxx_qc parallel 8 as
        SELECT *
          FROM yw_p_grjbxx a
         where id = (select max(id) from yw_p_grjbxx b where a.sfzh = b.sfzh)';
    
        EXECUTE IMMEDIATE 'create index IND_yw_p_grjbxx_qc on yw_p_grjbxx_qc (SFZH) parallel 8';
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         1);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           1);
      END;
    
    -- 插入个人基本信息
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop TABLE GR_XYZBJFBZ_'||L_DATE;
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        BEGIN
              EXECUTE IMMEDIATE 'drop index SFZH_1129B';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        BEGIN
              EXECUTE IMMEDIATE 'drop index XM_1129A';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        BEGIN
              EXECUTE IMMEDIATE 'drop index XM_1129C';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        EXECUTE IMMEDIATE 'CREATE TABLE GR_XYZBJFBZ_'||C_DATE||' as SELECT * FROM GR_XYZBJFBZ';
    
        EXECUTE IMMEDIATE 'truncate table GR_XYZBJFBZ';
    
        EXECUTE IMMEDIATE 'insert into GR_XYZBJFBZ (id,xm,sfzh,status,create_time,NIANL)
        SELECT id,
               xm,
               sfzh,
               status,
               create_time,
               case
                  when '||V_NOW_YEAR||' - case when csrq is not null then
                   to_char(csrq, ''yyyy'')
                  else
                   substr(sfzh, 7, 4)
                end < 25 then ''25岁以下''
                  when '||V_NOW_YEAR||' - case when csrq is not null then
                  to_char(csrq, ''yyyy'')
                 else
                  substr(sfzh, 7, 4)
                end >= 25
                    and '||V_NOW_YEAR||' -case when csrq is not null then
                    to_char(csrq, ''yyyy'')
                 else
                  substr(sfzh, 7, 4)
               end <= 40 then ''25-40岁''
                  when '||V_NOW_YEAR||' - case when csrq is not null then
                  to_char(csrq, ''yyyy'')
                 else
                  substr(sfzh, 7, 4)
                end > 40
                    and '||V_NOW_YEAR||' -case when csrq is not null then
                    to_char(csrq, ''yyyy'')
                 else
                  substr(sfzh, 7, 4)
               end <= 60 then ''40-60岁''
                  when '||V_NOW_YEAR||' - case when csrq is not null then
                   to_char(csrq, ''yyyy'')
                  else
                   substr(sfzh, 7, 4)
                end > 60 then ''60岁以上'' else null end NIANL
          FROM yw_p_grjbxx_qc';
        commit;
    
        EXECUTE IMMEDIATE 'create index SFZH_1129B on GR_XYZBJFBZ (SFZH) parallel 8';
        EXECUTE IMMEDIATE 'create index XM_1129A on GR_XYZBJFBZ (XM) parallel 8';
        EXECUTE IMMEDIATE 'create index XM_1129C on GR_XYZBJFBZ (ID) parallel 8';
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         2);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           2);
      END;
    
    
    
    -- 职业资格
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop table ghf_zyzg';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        EXECUTE IMMEDIATE 'CREATE TABLE ghf_zyzg as
        SELECT SFZH, sum(cnt) cnt
          FROM (SELECT SFZ SFZH, count(*) cnt
                  FROM (SELECT distinct XM, SFZ, ZCZSMC, ZYZGDJ, ZCZSBH
                          FROM BUS_SECOND.YW_P_ZIGEZHUCE@old_ywk)
                 GROUP BY SFZ
                union all
                SELECT SFZH, count(*) cnt
                  FROM (SELECT distinct TGRQ, XM, SFZH, CYZGMC, CYZGDJ, FZRQ, FZDW
                          FROM YW_P_GRCYZG)
                 GROUP BY SFZH)
         GROUP BY SFZH';
    
        EXECUTE IMMEDIATE 'CREATE index i_ghf_zyzg_0 on ghf_zyzg(SFZH)';
    
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.zizzk = (SELECT b.cnt FROM ghf_zyzg b WHERE a.sfzh = b.sfzh)';
        commit;
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.zizzk = 0 WHERE a.zizzk is null';
        commit;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         3);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           3);
      END;
    
    -- 献血信息
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop table ghf_xxxx';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        EXECUTE IMMEDIATE 'CREATE TABLE ghf_xxxx as
        SELECT sum(ZSXXL) ZSXXL,sfzh FROM YW_P_GRXIANXIE GROUP BY sfzh';
    
        EXECUTE IMMEDIATE 'CREATE index i_ghf_xxxx_0 on ghf_xxxx(SFZH)';
    
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.xianxjl = (SELECT b.ZSXXL FROM ghf_xxxx b WHERE a.sfzh = b.sfzh)';
        commit;
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.xianxjl = 0 WHERE a.xianxjl is null';
        commit;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         4);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           4);
      END;
    
    -- 志愿者服务
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop table ghf_zyzfw';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        EXECUTE IMMEDIATE 'CREATE TABLE ghf_zyzfw as
        SELECT sum(fwsc) fwsc,sfzh FROM YW_P_GRZYZFW GROUP BY sfzh';
    
        EXECUTE IMMEDIATE 'CREATE index i_ghf_zyzfw_0 on ghf_zyzfw(SFZH)';
    
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.zhiyzfw = (SELECT b.fwsc FROM ghf_zyzfw b WHERE a.sfzh = b.sfzh)';
        commit;
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.zhiyzfw = 0 WHERE a.zhiyzfw is null';
        commit;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         5);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           5);
      END;
    
    -- 荣誉表彰
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop table ghf_rybz';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        EXECUTE IMMEDIATE 'CREATE TABLE ghf_rybz as
        SELECT count(*) cnt,sfzh FROM YW_P_GRRYXX GROUP BY sfzh';
    
        EXECUTE IMMEDIATE 'create index I_GHF_RYBZ_0 on GHF_RYBZ (SFZH)';
    
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.rongybz = (SELECT b.cnt FROM ghf_rybz b WHERE a.sfzh = b.sfzh)';
        commit;
        EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
           set a.rongybz = 0 WHERE a.rongybz is null';
        commit;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         6);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           6);
      END;
    
    -- 个人评分
      BEGIN
        BEGIN
              EXECUTE IMMEDIATE 'drop TABLE GR_GERENPINGFENG_'||L_DATE;
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
        BEGIN
              EXECUTE IMMEDIATE 'drop index INDEX_GRPF_SCORE';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
        BEGIN
              EXECUTE IMMEDIATE 'drop index IND_GRPF_ID';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
        BEGIN
              EXECUTE IMMEDIATE 'drop index IND_GRPF_SFZH';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
        BEGIN
              EXECUTE IMMEDIATE 'drop index IND_GRPF_XM';
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    
    
        EXECUTE IMMEDIATE 'CREATE TABLE GR_GERENPINGFENG_'||C_DATE||' as SELECT * FROM GR_GERENPINGFENG';
    
        EXECUTE IMMEDIATE 'create index INDEX_GRPF_SCORE_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (SCORE)';
        EXECUTE IMMEDIATE 'create index IND_GRPF_ID_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (ID)';
        EXECUTE IMMEDIATE 'create index IND_GRPF_SFZH_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (SFZH)';
        EXECUTE IMMEDIATE 'create index IND_GRPF_XM_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (XM)';
    
        EXECUTE IMMEDIATE 'truncate table GR_GERENPINGFENG';
    
        EXECUTE IMMEDIATE 'insert into GR_GERENPINGFENG
          SELECT id,
                 XM,
                 SFZH,
                 STATUS,
                 create_time,
                 (case
                   when zizzk >= 2 then
                    5
                   else
                    0
                 end) + (case
                   when xianxjl is not null and xianxjl > 0 and xianxjl <= 1000 then
                    6
                   when xianxjl is not null and xianxjl > 1000 and xianxjl <= 3000 then
                    11
                   when xianxjl is not null and xianxjl > 3000 and xianxjl <= 5000 then
                    17
                   when xianxjl is not null and xianxjl > 5000 then
                    22
                   else
                    0
                 end) + (case
                   when zhiyzfw is not null and zhiyzfw > 0 and zhiyzfw <= 80 then
                    6
                   when zhiyzfw is not null and zhiyzfw > 80 and zhiyzfw <= 240 then
                    11
                   when zhiyzfw is not null and zhiyzfw > 240 and zhiyzfw <= 480 then
                    17
                   when zhiyzfw is not null and zhiyzfw > 480 then
                    23
                   else
                    0
                 end) + (case
                   when rongybz = 1 then
                    11
                   when rongybz > 1 then
                    22
                   else
                    0
                 end) SCORE
            FROM GR_XYZBJFBZ';
        commit;
    
        EXECUTE IMMEDIATE 'create index INDEX_GRPF_SCORE on GR_GERENPINGFENG (SCORE)';
        EXECUTE IMMEDIATE 'create index IND_GRPF_ID on GR_GERENPINGFENG (ID)';
        EXECUTE IMMEDIATE 'create index IND_GRPF_SFZH on GR_GERENPINGFENG (SFZH)';
        EXECUTE IMMEDIATE 'create index IND_GRPF_XM on GR_GERENPINGFENG (XM)';
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         7);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           7);
      END;
    
    -- 个人基本信息增量数据
      BEGIN
        EXECUTE IMMEDIATE 'truncate table GR_XYZBJFBZ_ZL';
        -- 新增人员
        EXECUTE IMMEDIATE 'insert into GR_XYZBJFBZ_ZL
        SELECT * FROM GR_XYZBJFBZ WHERE SFZH not in (SELECT SFZH FROM GR_XYZBJFBZ_'||C_DATE||')';
        commit;
        -- 有数据更新的人员
        EXECUTE IMMEDIATE 'insert into GR_XYZBJFBZ_ZL
        SELECT a.*
          FROM GR_XYZBJFBZ a
          join GR_XYZBJFBZ_'||C_DATE||' b on a.SFZH = b.SFZH
         WHERE nvl(a.ZIZZK, 0) <> nvl(b.ZIZZK, 0)
            or nvl(a.RONGYBZ, 0) <> nvl(b.RONGYBZ, 0)
            or nvl(a.XIANXJL, 0) <> nvl(b.XIANXJL, 0)
            or nvl(a.ZHIYZFW, 0) <> nvl(b.ZHIYZFW, 0)
            or a.NIANL <> b.NIANL';
        commit;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         8);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           8);
      END;
    
    -- 个人评分增量数据
      BEGIN
        EXECUTE IMMEDIATE 'truncate table GR_GERENPINGFENG_ZL';
        -- 新增人员
        EXECUTE IMMEDIATE 'insert into GR_GERENPINGFENG_ZL
        SELECT * FROM GR_GERENPINGFENG WHERE SFZH not in (SELECT SFZH FROM GR_GERENPINGFENG_'||C_DATE||')';
        commit;
        -- 有数据更新的人员
        EXECUTE IMMEDIATE 'insert into GR_GERENPINGFENG_ZL
        SELECT a.*
          FROM GR_GERENPINGFENG a
          join GR_GERENPINGFENG_'||C_DATE||' b on a.SFZH = b.SFZH
         WHERE to_number(a.SCORE) <> to_number(b.SCORE)';
        commit;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         9);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           9);
      END;
      
    
    -- 新增XXX同步流程
    -- 2019.4.8
    -- xc
      BEGIN
    -------------------------------------------------------------- XXX基本信息更新 --------------------------------------------------------------
    -- 判断是否有增量数据,没有直接跳出循环,有则判断是否有新增数据
    SELECT count(*) into v_count FROM GR_XYZBJFBZ_ZL;
    IF v_count = 0 THEN
        null;
    else
        -- 判断是否有新增数据,如果有新增数据则插入新增数据
        SELECT count(*) into v_count FROM GR_XYZBJFBZ_ZL WHERE SFZH not in (SELECT SFZH FROM smxyjf.GR_XYZBJFBZ@dbl_ghf);
        IF v_count > 0 THEN
            EXECUTE IMMEDIATE 'insert into smxyjf.GR_XYZBJFBZ@dbl_ghf
            SELECT * FROM GR_XYZBJFBZ_ZL WHERE SFZH not in (SELECT SFZH FROM smxyjf.GR_XYZBJFBZ@dbl_ghf)';
            commit;
            -- 判断是否除了新增数据外还有更新数据,如果有则更新数据
            SELECT count(*) into v_count FROM GR_XYZBJFBZ_ZL WHERE SFZH in (SELECT SFZH FROM smxyjf.GR_XYZBJFBZ@dbl_ghf);
            IF v_count > 0 THEN
                EXECUTE IMMEDIATE 'update smxyjf.GR_XYZBJFBZ@dbl_ghf a
                 set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.GONGZWDX, a.ZHIC, a.ZHIY, a.JUZWDX, a.ZHUFQL, a.HUJ, a.NIANL, a.WENHCD, a.HUNYZK, a.JIANKZT, a.ZIZZK, a.YUQFKJL, a.BULXJL, a.RONGYBZ, a.TOUSLSJL, a.WEIJNGGSYFJL, a.XIANXJL, a.ZHIYZFW) = (SELECT a.ID,
                                                                                                                                                                                                                                                               a.XM,
                                                                                                                                                                                                                                                               a.STATUS,
                                                                                                                                                                                                                                                               a.CREATE_TIME,
                                                                                                                                                                                                                                                               a.GONGZWDX,
                                                                                                                                                                                                                                                               a.ZHIC,
                                                                                                                                                                                                                                                               a.ZHIY,
                                                                                                                                                                                                                                                               a.JUZWDX,
                                                                                                                                                                                                                                                               a.ZHUFQL,
                                                                                                                                                                                                                                                               a.HUJ,
                                                                                                                                                                                                                                                               a.NIANL,
                                                                                                                                                                                                                                                               a.WENHCD,
                                                                                                                                                                                                                                                               a.HUNYZK,
                                                                                                                                                                                                                                                               a.JIANKZT,
                                                                                                                                                                                                                                                               a.ZIZZK,
                                                                                                                                                                                                                                                               a.YUQFKJL,
                                                                                                                                                                                                                                                               a.BULXJL,
                                                                                                                                                                                                                                                               a.RONGYBZ,
                                                                                                                                                                                                                                                               a.TOUSLSJL,
                                                                                                                                                                                                                                                               a.WEIJNGGSYFJL,
                                                                                                                                                                                                                                                               a.XIANXJL,
                                                                                                                                                                                                                                                               a.ZHIYZFW
                                                                                                                                                                                                                                                          FROM GR_XYZBJFBZ_ZL b
                                                                                                                                                                                                                                                         WHERE a.SFZH =
                                                                                                                                                                                                                                                               b.SFZH)
               WHERE a.sfzh in (SELECT SFZH FROM GR_XYZBJFBZ_ZL)';
            END IF;
        else
            --如果没有新增数据则说明都是更新数据,执行更新流程
            EXECUTE IMMEDIATE 'update smxyjf.GR_XYZBJFBZ@dbl_ghf a
             set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.GONGZWDX, a.ZHIC, a.ZHIY, a.JUZWDX, a.ZHUFQL, a.HUJ, a.NIANL, a.WENHCD, a.HUNYZK, a.JIANKZT, a.ZIZZK, a.YUQFKJL, a.BULXJL, a.RONGYBZ, a.TOUSLSJL, a.WEIJNGGSYFJL, a.XIANXJL, a.ZHIYZFW) = (SELECT a.ID,
                                                                                                                                                                                                                                                           a.XM,
                                                                                                                                                                                                                                                           a.STATUS,
                                                                                                                                                                                                                                                           a.CREATE_TIME,
                                                                                                                                                                                                                                                           a.GONGZWDX,
                                                                                                                                                                                                                                                           a.ZHIC,
                                                                                                                                                                                                                                                           a.ZHIY,
                                                                                                                                                                                                                                                           a.JUZWDX,
                                                                                                                                                                                                                                                           a.ZHUFQL,
                                                                                                                                                                                                                                                           a.HUJ,
                                                                                                                                                                                                                                                           a.NIANL,
                                                                                                                                                                                                                                                           a.WENHCD,
                                                                                                                                                                                                                                                           a.HUNYZK,
                                                                                                                                                                                                                                                           a.JIANKZT,
                                                                                                                                                                                                                                                           a.ZIZZK,
                                                                                                                                                                                                                                                           a.YUQFKJL,
                                                                                                                                                                                                                                                           a.BULXJL,
                                                                                                                                                                                                                                                           a.RONGYBZ,
                                                                                                                                                                                                                                                           a.TOUSLSJL,
                                                                                                                                                                                                                                                           a.WEIJNGGSYFJL,
                                                                                                                                                                                                                                                           a.XIANXJL,
                                                                                                                                                                                                                                                           a.ZHIYZFW
                                                                                                                                                                                                                                                      FROM GR_XYZBJFBZ_ZL b
                                                                                                                                                                                                                                                     WHERE a.SFZH =
                                                                                                                                                                                                                                                           b.SFZH)
           WHERE a.sfzh in (SELECT SFZH FROM GR_XYZBJFBZ_ZL)';
        END IF;
    END IF;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         10);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           10);
      END;
    
      BEGIN
    -------------------------------------------------------------- XXX分值更新 --------------------------------------------------------------
    -- 判断是否有增量数据,没有直接跳出循环,有则判断是否有新增数据
    SELECT count(*) into v_count FROM GR_GERENPINGFENG_ZL;
    IF v_count = 0 THEN
        null;
    else
        -- 判断是否有新增数据,如果有新增数据则插入新增数据
        SELECT count(*) into v_count FROM GR_GERENPINGFENG_ZL WHERE SFZH not in (SELECT SFZH FROM smxyjf.GR_GERENPINGFENG@dbl_ghf);
        IF v_count > 0 THEN
            EXECUTE IMMEDIATE 'insert into smxyjf.GR_GERENPINGFENG@dbl_ghf
            SELECT * FROM GR_GERENPINGFENG_ZL WHERE SFZH not in (SELECT SFZH FROM smxyjf.GR_GERENPINGFENG@dbl_ghf)';
            commit;
            -- 判断是否除了新增数据外还有更新数据,如果有则更新数据
            SELECT count(*) into v_count FROM GR_GERENPINGFENG_ZL WHERE SFZH in (SELECT SFZH FROM smxyjf.GR_GERENPINGFENG@dbl_ghf);
            IF v_count > 0 THEN
                EXECUTE IMMEDIATE 'update smxyjf.GR_GERENPINGFENG@dbl_ghf a
                 set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.SCORE) = (SELECT a.ID,
                                                                              a.XM,
                                                                              a.STATUS,
                                                                              a.CREATE_TIME,
                                                                              a.SCORE
                                                                         FROM GR_GERENPINGFENG_ZL b
                                                                        WHERE a.SFZH =
                                                                              b.SFZH)
               WHERE a.sfzh in (SELECT SFZH FROM GR_GERENPINGFENG_ZL)';
            END IF;
        else
            --如果没有新增数据则说明都是更新数据,执行更新流程
            EXECUTE IMMEDIATE 'update smxyjf.GR_GERENPINGFENG@dbl_ghf a
             set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.SCORE) = (SELECT a.ID,
                                                                          a.XM,
                                                                          a.STATUS,
                                                                          a.CREATE_TIME,
                                                                          a.SCORE
                                                                     FROM GR_GERENPINGFENG_ZL b
                                                                    WHERE a.SFZH =
                                                                          b.SFZH)
           WHERE a.sfzh in (SELECT SFZH FROM GR_GERENPINGFENG_ZL)';
        END IF;
    END IF;
    
        SP_EXCEPTION_LOG(SYSDATE,
                         'SP_GR_GHF_PFLC',
                         'SUCC',
                         NULL,
                         11);
    
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERR_FLAG := V_ERR_FLAG + 1;
          V_ERROR    := SUBSTR(SQLERRM, 1, 200);
          SP_EXCEPTION_LOG(SYSDATE,
                           'SP_GR_GHF_PFLC',
                           'ERROR',
                           V_ERROR,
                           11);
      END;
    -- 执行结束
    END SP_GR_GHF_PFLC;
    

    计算业务展示数据量的存储过程:

    create or replace procedure p_dp_process is
      --处理数据清洗完成后各个类型的数据量,然后记录到dp_process_result表中,供统计分析查询使用
      --有效量:  etl_type=1 and type=1  业务库表中的rwbh字段为关联条件,计算count
      --未关联量:etl_type=1 and type=4  业务库表中的rwbh字段关联条件,计算jgdj_id is null的count
      --疑问量:etl_type=0 and type=2  DP_SUMMARY_INVALID_DATA表中的logic_table_id,task_code未关联条件,计算count
      --更新量:  etl_type=0 and type=3  dp_data_size的总量-dp_data_size疑问数据量-业务库有效量
    
      --为了提高执行效率可以增加一些索引:
      --1、业务库所有YW_开头的表,对字段 RWBH 加索引
      --2、业务库dp_data_size表,对字段 logic_table_id和task_code加联合索引,不过此表数据量应该比较小,不加也可以,影响不大
      --PS:加了索引插入更新速度会相应变慢,可酌情处理
    
      -- 游标
      CURSOR handle_list IS
      select UPPER(a.table_code) table_code,
               UPPER(a.yw_table_name) yw_table_name,
               B.logic_table_id,
               B.dept_id,
               s.code
          from dp_ysyw_relation a
          join view_version_table_dept b
            on UPPER(a.table_code) = UPPER(b.code)
          join sys_department s on b.dept_id=s.sys_department_id
               where b.id<>'82a07a65647eb4d7016492e3773c0b93'
    
            group by table_code,yw_table_name,B.logic_table_id,B.dept_id,s.code;
    
    
      YOUXIAO_COUNT number; --有效量
      JGDJ_ID_COUNT     number; --含有jgdj_id的
      v_sql         varchar2(32767); --用于存放SQL的变量
      BMBM_COUNT     number; --含有jgdj_id的
    
    
    begin
    
      --删除旧数据
      delete from dp_process_result_test;
      commit;
    
      for line in handle_list loop
       YOUXIAO_COUNT := 0;
        --查询数据量(全量的)
        v_sql := 'select count(*)  from ' || line.yw_table_name ||
                 ' yw where rwbh is not null ';
    
        execute immediate v_sql
          into YOUXIAO_COUNT;
    
             BMBM_COUNT := 0;
        --查询数据量(全量的)
        v_sql := 'select COUNT(1) from user_tab_cols where table_name=''' || line.yw_table_name ||'''and
         column_name=''BMBM''';
    
        execute immediate v_sql
         INTO BMBM_COUNT;
    
    
        IF YOUXIAO_COUNT > 0 AND BMBM_COUNT>0 THEN
    
       /*---------------计算有效量并记录----------------*/
         v_sql := ' insert into dp_process_result_test(id,type,process_size,process_time,
             table_code,etl_type,task_code,dept_id )
                select sys_guid(),1,ruku as youxiao,CREATE_TIME,''' || line.table_code || ''',1,
            rwbh,''' || line.dept_id || '''
                from (
            select count(1) as ruku,--入库量
            rwbh,--批次编号
              min(yw.CREATE_TIME) as CREATE_TIME--时间
             from ' || line.yw_table_name ||' yw
             JOIN SYS_DEPARTMENT S ON S.CODE=YW.BMBM
             JOIN dp_data_report_log LOG ON LOG.DEPT_ID=S.SYS_DEPARTMENT_ID AND LOG.TASK_CODE=YW.RWBH
             where rwbh is not null
             and bmbm=''' || line.code || '''
             AND LOG.logic_table_id =''' || line.logic_table_id || '''
            group by rwbh) where ruku > 0';
    
          execute immediate v_sql;
        COMMIT;
    
        /*---------------计算更新量并记录----------------*/
         v_sql := ' insert into dp_process_result_test(id,type,process_size,process_time,
             table_code,etl_type,task_code,dept_id )
                select sys_guid(),3,
                case when gengxinliang>0 then gengxinliang else 0 end gengxinliang,
                CREATE_TIME,''' || line.table_code || ''',1,
            rwbh,''' || line.dept_id || '''
                from (
            select MAX(ds.all_size)-count(1)-MAX(ds.fail_size) as gengxinliang,--更新量
            rwbh,--批次编号
              min(yw.CREATE_TIME) as CREATE_TIME--时间
             from ' || line.yw_table_name ||' yw
             JOIN SYS_DEPARTMENT S ON S.CODE=YW.BMBM
             JOIN dp_data_report_log LOG ON LOG.DEPT_ID=S.SYS_DEPARTMENT_ID AND LOG.TASK_CODE=YW.RWBH
             LEFT JOIN ( SELECT   TASK_CODE,sum(nvl(ds.all_size, 0)) all_size,sum(nvl(ds.fail_size, 0)) fail_size  FROM dp_data_size ds group by TASK_CODE)DS
              on yw.rwbh=ds.task_code
    
             where rwbh is not null and bmbm=''' || line.code || '''
              AND LOG.logic_table_id =''' || line.logic_table_id || '''
            group by rwbh) where gengxinliang > 0';
    
          execute immediate v_sql;
        COMMIT;
    
        JGDJ_ID_COUNT := 0;
        --查询数据量(全量的)
        v_sql := 'select COUNT(1) from user_tab_cols where table_name=''' || line.yw_table_name ||'''and
         column_name=''JGDJ_ID''';
    
        execute immediate v_sql
          into JGDJ_ID_COUNT;
       IF JGDJ_ID_COUNT > 0 THEN
    
         /*---------------计算未关联量并记录----------------*/
        --YW_L_JGSLBGDJ机构基本信息表不需要统计未关联量,自然人表不统计未关联量
        IF line.yw_table_name != 'YW_L_JGSLBGDJ' and
           SUBSTR(line.yw_table_name, 0, 4) = 'YW_L' THEN
    
            v_sql := ' insert into dp_process_result_test(id,type,process_size,process_time,
             table_code,etl_type,task_code,dept_id )
                select sys_guid(),4,weiguanlian,CREATE_TIME,''' || line.table_code || ''',1,
            rwbh,'''|| line.dept_id ||'''
                from (
            select count(1) as weiguanlian,--未关联量
            rwbh,--批次编号
              min(yw.CREATE_TIME)as CREATE_TIME--时间
             from ' || line.yw_table_name ||' yw
             JOIN SYS_DEPARTMENT S ON S.CODE=YW.BMBM
             JOIN dp_data_report_log LOG ON LOG.DEPT_ID=S.SYS_DEPARTMENT_ID AND LOG.TASK_CODE=YW.RWBH
             where rwbh is not null and jgdj_id is null
              and bmbm=''' || line.code || '''
             AND LOG.logic_table_id =''' || line.logic_table_id || '''
            group by rwbh) where weiguanlian > 0';
    
          execute immediate v_sql;
        COMMIT;
    
        END IF;
         END IF;
    
    
    
      end if ;
    
    END LOOP;
      -- 提交循环后不能整除的剩余事务
    
    
     insert into dp_process_result_test
            (id,
             type,
             process_size,
             process_time,
             table_code,
             etl_type,
             task_code,
             dept_id)
          values
            (sys_guid(),
             1,
            281098232,
             to_date('01-01-2018 17:20:34', 'dd-mm-yyyy hh24:mi:ss'),
             'ZRRSBJNXX_P',
             '1',
             '201801010101',
             '4028810a4e6bb410014e6c458e8f0096');
       commit;
    
    end p_dp_process;
    
    展开全文
  • oracle存储过程实例

    万次阅读 2016-07-05 12:19:27
    认识存储过程和函数 存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中...
    认识存储过程和函数 
    
    存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比,存储过程有很多优点,具体归纳如下:
    * 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的,因为代码不保存在本地,用户可以在任何客户机上登录到数据库,并调用或修改代码。
    * 存储过程和函数可由数据库提供安全保证,要想使用存储过程和函数,需要有存储过程和函数的所有者的授权,只有被授权的用户或创建者本身才能执行存储过程或调用函数。
    * 存储过程和函数的信息是写入数据字典的,所以存储过程可以看作是一个公用模块,用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能,可以设计成为存储过程,比如:显示一张工资统计表,可以设计成为存储过程;一个经常调用的计算,可以设计成为存储函数;根据雇员编号返回雇员的姓名,可以设计成存储函数。
    * 像其他高级语言的过程和函数一样,可以传递参数给存储过程或函数,参数的传递也有多种方式。存储过程可以有返回值,也可以没有返回值,存储过程的返回值必须通过参数带回;函数有一定的数据类型,像其他的标准函数一样,我们可以通过对函数名的调用返回函数值。
       存储过程和函数需要进行编译,以排除语法错误,只有编译通过才能调用。
    创建和删除存储过程
    创建存储过程,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下:
    CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
    {AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    [EXCEPTION
    错误处理部分]
    END [过程名];
    其中:
    可选关键字OR REPLACE 表示如果存储过程已经存在,则用新的存储过程覆盖,通常用于存储过程的重建。
    参数部分用于定义多个参数(如果没有参数,就可以省略)。参数有三种形式:IN、OUT和IN OUT。如果没有指明参数的形式,则默认为IN。
    关键字AS也可以写成IS,后跟过程的说明部分,可以在此定义过程的局部变量。
    编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行,编写好的存储过程必须要在SQL*Plus环境下进行编译,生成编译代码,原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。
    一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下:
    DROP PROCEDURE 存储过程名;
    如果要重新编译一个存储过程,则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下:
    ALTER PROCEDURE 存储过程名 COMPILE;
    执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下:
    方法1:
    EXECUTE 模式名.存储过程名[(参数...)];
    方法2:
    BEGIN
    模式名.存储过程名[(参数...)];
    END;
    传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值,则调用时可以省略参数)。参数可以是变量、常量或表达式,用法参见下一节。
    如果是调用本账户下的存储过程,则模式名可以省略。要调用其他账户编写的存储过程,则模式名必须要添加。
    以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。
    【训练1】  创建一个显示雇员总人数的存储过程。
    步骤1:登录SCOTT账户(或学生个人账户)。
    步骤2:在SQL*Plus输入区中,输入以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT   
    2. AS  
    3. V_TOTAL NUMBER(10);   
    4. BEGIN  
    5.  SELECT COUNT(*) INTO V_TOTAL FROM EMP;   
    6.  DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL);   
    7. END;  
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT  
    2. AS  
    3. V_TOTAL NUMBER(10);  
    4. BEGIN  
    5.  SELECT COUNT(*) INTO V_TOTAL FROM EMP;  
    6.  DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL);  
    7. END;  

    步骤3:按“执行”按钮进行编译。
    如果存在错误,就会显示:
    警告: 创建的过程带有编译错误。
    如果存在错误,对脚本进行修改,直到没有错误产生。
    如果编译结果正确,将显示:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤4:调用存储过程,在输入区中输入以下语句并执行:
    Sql代码 复制代码
    1. EXECUTE EMP_COUNT;  
    1. EXECUTE EMP_COUNT;  

    显示结果为:
    Sql代码 复制代码
    1. 雇员总人数为:14   
    2.         PL/SQL 过程已成功完成。  
    1. 雇员总人数为:14  
    2.         PL/SQL 过程已成功完成。  

    说明:在该训练中,V_TOTAL变量是存储过程定义的局部变量,用于接收查询到的雇员总人数。
    注意:在SQL*Plus中输入存储过程,按“执行”按钮是进行编译,不是执行存储过程。
      如果在存储过程中引用了其他用户的对象,比如表,则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功,就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。
    存储过程没有参数,在调用时,直接写过程名即可。
    【训练2】  在PL/SQL程序中调用存储过程。
    步骤1:登录SCOTT账户。
    步骤2:授权STUDENT账户使用该存储过程,即在SQL*Plus输入区中,输入以下的命令:
    Sql代码 复制代码
    1. GRANT EXECUTE ON EMP_COUNT TO STUDENT  
    1. GRANT EXECUTE ON EMP_COUNT TO STUDENT  

    Sql代码 复制代码
    1. 授权成功。  
    1. 授权成功。  

    步骤3:登录STUDENT账户,在SQL*Plus输入区中输入以下程序:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2.         BEGIN  
    3.         SCOTT.EMP_COUNT;   
    4.         END;  
    1. SET SERVEROUTPUT ON  
    2.         BEGIN  
    3.         SCOTT.EMP_COUNT;  
    4.         END;  

    步骤4:执行以上程序,结果为:
    Sql代码 复制代码
    1. 雇员总人数为:14   
    2.         PL/SQL 过程已成功完成。   
    1. 雇员总人数为:14  
    2.         PL/SQL 过程已成功完成。   

      说明:在本例中,存储过程是由SCOTT账户创建的,STUDEN账户获得SCOTT账户的授权后,才能调用该存储过程。
      注意:在程序中调用存储过程,使用了第二种语法。
    【训练3】  编写显示雇员信息的存储过程EMP_LIST,并引用EMP_COUNT存储过程。
    步骤1:在SQL*Plus输入区中输入并编译以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE EMP_LIST   
    2.         AS  
    3.          CURSOR emp_cursor IS    
    4.         SELECT empno,ename FROM emp;   
    5.         BEGIN  
    6. FOR Emp_record IN emp_cursor LOOP      
    7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);   
    8.         END LOOP;   
    9.         EMP_COUNT;   
    10.         END;  
    1. CREATE OR REPLACE PROCEDURE EMP_LIST  
    2.         AS  
    3.          CURSOR emp_cursor IS   
    4.         SELECT empno,ename FROM emp;  
    5.         BEGIN  
    6. FOR Emp_record IN emp_cursor LOOP     
    7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);  
    8.         END LOOP;  
    9.         EMP_COUNT;  
    10.         END;  

    执行结果:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤2:调用存储过程,在输入区中输入以下语句并执行:
    Sql代码 复制代码
    1. EXECUTE EMP_LIST  
    1. EXECUTE EMP_LIST  

    显示结果为:
    Sql代码 复制代码
    1. 7369SMITH   
    2. 7499ALLEN   
    3. 7521WARD   
    4. 7566JONES   
    5.             执行结果:   
    6.         雇员总人数为:14   
    7.         PL/SQL 过程已成功完成。  
    1. 7369SMITH  
    2. 7499ALLEN  
    3. 7521WARD  
    4. 7566JONES  
    5.             执行结果:  
    6.         雇员总人数为:14  
    7.         PL/SQL 过程已成功完成。  

    说明:以上的EMP_LIST存储过程中定义并使用了游标,用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程EMP_COUNT,用来附加显示雇员总人数。通过EXECUTE命令来执行EMP_LIST存储过程。
    【练习1】编写显示部门信息的存储过程DEPT_LIST,要求统计出部门个数。
    参数传递
    参数的作用是向存储过程传递数据,或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。
    参数的类型有三种,如下所示。
    Sql代码 复制代码
    1. IN  定义一个输入参数变量,用于传递参数给存储过程   
    2. OUT 定义一个输出参数变量,用于从存储过程获取数据   
    3. IN OUT  定义一个输入、输出参数变量,兼有以上两者的功能  
    1. IN  定义一个输入参数变量,用于传递参数给存储过程  
    2. OUT 定义一个输出参数变量,用于从存储过程获取数据  
    3. IN OUT  定义一个输入、输出参数变量,兼有以上两者的功能  

    参数的定义形式和作用如下:
    参数名 IN 数据类型 DEFAULT 值;
    定义一个输入参数变量,用于传递参数给存储过程。在调用存储过程时,主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项,用来设定参数的默认值。如果在调用存储过程时不指明参数,则参数变量取默认值。在存储过程中,输入变量接收主程序传递的值,但不能对其进行赋值。
    参数名 OUT 数据类型;
    定义一个输出参数变量,用于从存储过程获取数据,即变量从存储过程中返回值给主程序。
    在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。在存储过程中,参数变量只能被赋值而不能将其用于赋值,在存储过程中必须给输出变量至少赋值一次。
    参数名 IN OUT 数据类型 DEFAULT 值;
    定义一个输入、输出参数变量,兼有以上两者的功能。在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。DEFAULT 关键字为可选项,用来设定参数的默认值。在存储过程中,变量接收主程序传递的值,同时可以参加赋值运算,也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。
    如果省略IN、OUT或IN OUT,则默认模式是IN。
    【训练1】  编写给雇员增加工资的存储过程CHANGE_SALARY,通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
    步骤1:登录SCOTT账户。
      步骤2:在SQL*Plus输入区中输入以下存储过程并执行:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)   
    2.         AS  
    3.          V_ENAME VARCHAR2(10);   
    4. V_SAL NUMBER(5);   
    5.         BEGIN  
    6.         SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;   
    7.          UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;   
    8.          DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));   
    9. COMMIT;   
    10.         EXCEPTION   
    11.          WHEN OTHERS THEN  
    12.         DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');   
    13.         ROLLBACK;   
    14.         END;  
    1. CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)  
    2.         AS  
    3.          V_ENAME VARCHAR2(10);  
    4. V_SAL NUMBER(5);  
    5.         BEGIN  
    6.         SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;  
    7.          UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;  
    8.          DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));  
    9. COMMIT;  
    10.         EXCEPTION  
    11.          WHEN OTHERS THEN  
    12.         DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');  
    13.         ROLLBACK;  
    14.         END;  

    执行结果为:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤3:调用存储过程,在输入区中输入以下语句并执行:
    Sql代码 复制代码
    1. EXECUTE CHANGE_SALARY(7788,80)  
    1. EXECUTE CHANGE_SALARY(7788,80)  

    显示结果为:
    Sql代码 复制代码
    1. 雇员SCOTT的工资被改为3080   
    1. 雇员SCOTT的工资被改为3080   

    说明:从执行结果可以看到,雇员SCOTT的工资已由原来的3000改为3080。
    参数的值由调用者传递,传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致,可以采用以下调用方法。如上例,执行语句可以改为:
     EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);
      可以看出传递参数的顺序发生了变化,并且明确指出了参数名和要传递的值,=>运算符左侧是参数名,右侧是参数表达式,这种赋值方法的意义较清楚。
    【练习1】创建插入雇员的存储过程INSERT_EMP,并将雇员编号等作为参数。
    在设计存储过程的时候,也可以为参数设定默认值,这样调用者就可以不传递或少传递参数了。
    【训练2】  调用存储过程CHANGE_SALARY,不传递参数,使用默认参数值。
    在SQL*Plus输入区中输入以下命令并执行:
    Sql代码 复制代码
    1. EXECUTE CHANGE_SALARY  
    1. EXECUTE CHANGE_SALARY  

    显示结果为:
    Sql代码 复制代码
    1. 雇员SCOTT的工资被改为3090   
    1. 雇员SCOTT的工资被改为3090   

    说明:在存储过程的调用中没有传递参数,而是采用了默认值7788和10,即默认雇员号为7788,增加的工资为10。
    【训练3】  使用OUT类型的参数返回存储过程的结果。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入并编译以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)   
    2.         AS  
    3.         BEGIN  
    4.         SELECT COUNT(*) INTO P_TOTAL FROM EMP;   
    5.         END;  
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)  
    2.         AS  
    3.         BEGIN  
    4.         SELECT COUNT(*) INTO P_TOTAL FROM EMP;  
    5.         END;  

    执行结果为:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤3:输入以下程序并执行:
    Sql代码 复制代码
    1. DECLARE  
    2.         V_EMPCOUNT NUMBER;   
    3.         BEGIN  
    4.         EMP_COUNT(V_EMPCOUNT);   
    5.         DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);   
    6.         END;  
    1. DECLARE  
    2.         V_EMPCOUNT NUMBER;  
    3.         BEGIN  
    4.         EMP_COUNT(V_EMPCOUNT);  
    5.         DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);  
    6.         END;  

    显示结果为:
    Sql代码 复制代码
    1. 雇员总人数为:14   
    2.         PL/SQL 过程已成功完成。  
    1. 雇员总人数为:14  
    2.         PL/SQL 过程已成功完成。  

        说明:在存储过程中定义了OUT类型的参数P_TOTAL,在主程序调用该存储过程时,传递了参数V_EMPCOUNT。在存储过程中的SELECT...INTO...语句中对P_TOTAL进行赋值,赋值结果由V_EMPCOUNT变量带回给主程序并显示。
    以上程序要覆盖同名的EMP_COUNT存储过程,如果不使用OR REPLACE选项,就会出现以下错误:
    Sql代码 复制代码
    1. ERROR 位于第 1 行:   
    2.         ORA-00955: 名称已由现有对象使用。  
    1. ERROR 位于第 1 行:  
    2.         ORA-00955: 名称已由现有对象使用。  

    【练习2】创建存储过程,使用OUT类型参数获得雇员经理名。
    【训练4】  使用IN OUT类型的参数,给电话号码增加区码。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入并编译以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)   
    2.         AS  
    3.         BEGIN  
    4.          P_HPONE_NUM:='0755-'||P_HPONE_NUM;   
    5.         END;  
    1. CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)  
    2.         AS  
    3.         BEGIN  
    4.          P_HPONE_NUM:='0755-'||P_HPONE_NUM;  
    5.         END;  

    执行结果为:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤3:输入以下程序并执行:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2. DECLARE  
    3. V_PHONE_NUM VARCHAR2(15);   
    4. BEGIN  
    5. V_PHONE_NUM:='26731092';   
    6. ADD_REGION(V_PHONE_NUM);   
    7. DBMS_OUTPUT.PUT_LINE('新的电话号码:'||V_PHONE_NUM);   
    8. END;  
    1. SET SERVEROUTPUT ON  
    2. DECLARE  
    3. V_PHONE_NUM VARCHAR2(15);  
    4. BEGIN  
    5. V_PHONE_NUM:='26731092';  
    6. ADD_REGION(V_PHONE_NUM);  
    7. DBMS_OUTPUT.PUT_LINE('新的电话号码:'||V_PHONE_NUM);  
    8. END;  

    显示结果为:
    Sql代码 复制代码
    1. 新的电话号码:0755-26731092   
    2.         PL/SQL 过程已成功完成。  
    1. 新的电话号码:0755-26731092  
    2.         PL/SQL 过程已成功完成。  

    说明:变量V_HPONE_NUM既用来向存储过程传递旧电话号码,也用来向主程序返回新号码。新的号码在原来基础上增加了区号0755和-。
    创建和删除存储函数
      创建函数,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建存储函数的语法和创建存储过程的类似,即
    CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)]
    RETURN 数据类型
    {AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    RETURN (表达式)
    [EXCEPTION
        错误处理部分]
    END [函数名];
    其中,参数是可选的,但只能是IN类型(IN关键字可以省略)。
    在定义部分的RETURN 数据类型,用来表示函数的数据类型,也就是返回值的类型,此部分不可省略。
    在可执行部分的RETURN(表达式),用来生成函数的返回值,其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句,但只有一个RETURN语句会被执行,一旦执行了RETURN语句,则函数结束并返回调用环境。
    一个存储函数在不需要时可以删除,但删除的人应是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人。其语法如下:
    DROP FUNCTION 函数名;
    重新编译一个存储函数时,编译的人应是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。重新编译一个存储函数的语法如下:
    ALTER PROCEDURE 函数名 COMPILE;
    函数的调用者应是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人,或是被函数的拥有者授予了函数执行权限的账户。函数的引用和存储过程不同,函数要出现在程序体中,可以参加表达式的运算或单独出现在表达式中,其形式如下:
    变量名:=函数名(...)
    【训练1】  创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入以下存储函数并编译:
    Sql代码 复制代码
    1. CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)   
    2.         RETURN VARCHAR2   
    3.         AS  
    4.          V_ENAME VARCHAR2(10);   
    5.         BEGIN  
    6.         ELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;   
    7. RETURN(V_ENAME);   
    8. EXCEPTION   
    9.  WHEN NO_DATA_FOUND THEN  
    10.   DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');   
    11.   RETURN (NULL);   
    12.  WHEN TOO_MANY_ROWS THEN  
    13.   DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');   
    14.   RETURN (NULL);   
    15.  WHEN OTHERS THEN  
    16.   DBMS_OUTPUT.PUT_LINE('发生其他错误!');   
    17.   RETURN (NULL);   
    18. END;  
    1. CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)  
    2.         RETURN VARCHAR2  
    3.         AS  
    4.          V_ENAME VARCHAR2(10);  
    5.         BEGIN  
    6.         ELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;  
    7. RETURN(V_ENAME);  
    8. EXCEPTION  
    9.  WHEN NO_DATA_FOUND THEN  
    10.   DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');  
    11.   RETURN (NULL);  
    12.  WHEN TOO_MANY_ROWS THEN  
    13.   DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');  
    14.   RETURN (NULL);  
    15.  WHEN OTHERS THEN  
    16.   DBMS_OUTPUT.PUT_LINE('发生其他错误!');  
    17.   RETURN (NULL);  
    18. END;  

    步骤3:调用该存储函数,输入并执行以下程序:
    Sql代码 复制代码
    1. BEGIN  
    2.         DBMS_OUTPUT.PUT_LINE('雇员7369的名称是:'|| GET_EMP_NAME(7369));   
    3.          DBMS_OUTPUT.PUT_LINE('雇员7839的名称是:'|| GET_EMP_NAME(7839));   
    4.         END;  
    1. BEGIN  
    2.         DBMS_OUTPUT.PUT_LINE('雇员7369的名称是:'|| GET_EMP_NAME(7369));  
    3.          DBMS_OUTPUT.PUT_LINE('雇员7839的名称是:'|| GET_EMP_NAME(7839));  
    4.         END;  

    显示结果为:
    Sql代码 复制代码
    1. 雇员7369的名称是:SMITH   
    2.         雇员7839的名称是:KING   
    3.         PL/SQL 过程已成功完成。  
    1. 雇员7369的名称是:SMITH  
    2.         雇员7839的名称是:KING  
    3.         PL/SQL 过程已成功完成。  

    说明:函数的调用直接出现在程序的DBMS_OUTPUT.PUT_LINE语句中,作为字符串表达式的一部分。如果输入了错误的雇员编号,就会在函数的错误处理部分输出错误信息。试修改雇员编号,重新运行调用部分。
    【练习1】创建一个通过部门编号返回部门名称的存储函数GET_DEPT_NAME。
       【练习2】将函数的执行权限授予STUDENT账户,然后登录STUDENT账户调用。
    存储过程和函数的查看
    可以通过对数据字典的访问来查询存储过程或函数的有关信息,如果要查询当前用户的存储过程或函数的源代码,可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下:
    Sql代码 复制代码
    1. DESCRIBE USER_SOURCE  
    1. DESCRIBE USER_SOURCE  

    结果为:
    Sql代码 复制代码
    1. 名称                                      是否为空? 类型   
    2.         ------------------------------------------------------------- ------------- -----------------------  
    3.  NAME                                               VARCHAR2(30)   
    4.  TYPE                                               VARCHAR2(12)   
    5.  LINE                                               NUMBER   
    6.  TEXT                                               VARCHAR2(4000)  
    1. 名称                                      是否为空? 类型  
    2.         ------------------------------------------------------------- ------------- -----------------------  
    3.  NAME                                               VARCHAR2(30)  
    4.  TYPE                                               VARCHAR2(12)  
    5.  LINE                                               NUMBER  
    6.  TEXT                                               VARCHAR2(4000)  

      说明:里面按行存放着过程或函数的脚本,NAME是过程或函数名,TYPE 代表类型(PROCEDURE或FUNCTION),LINE是行号,TEXT 为脚本。
    【训练1】  查询过程EMP_COUNT的脚本。
    在SQL*Plus中输入并执行如下查询:
    Sql代码 复制代码
    1. select TEXT  from user_source WHERE NAME='EMP_COUNT';  
    1. select TEXT  from user_source WHERE NAME='EMP_COUNT';  

    结果为:
    Sql代码 复制代码
    1. TEXT   
    2. --------------------------------------------------------------------------------  
    3. PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)   
    4. AS  
    5. BEGIN  
    6.  SELECT COUNT(*) INTO P_TOTAL FROM EMP;   
    7. END;  
    1. TEXT  
    2. --------------------------------------------------------------------------------  
    3. PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)  
    4. AS  
    5. BEGIN  
    6.  SELECT COUNT(*) INTO P_TOTAL FROM EMP;  
    7. END;  

    【训练2】  查询过程GET_EMP_NAME的参数。
    在SQL*Plus中输入并执行如下查询:
    Sql代码 复制代码
    1. DESCRIBE GET_EMP_NAME  
    1. DESCRIBE GET_EMP_NAME  

    结果为:
    Sql代码 复制代码
    1. FUNCTION GET_EMP_NAME RETURNS VARCHAR2   
    2.         参数名称            类型          输入/输出默认值?   
    3.         ----------------------------------------- ----------------------------------- ----------------- -------------  
    4.         P_EMPNO             NUMBER(4) IN     DEFAULT  
    1. FUNCTION GET_EMP_NAME RETURNS VARCHAR2  
    2.         参数名称            类型          输入/输出默认值?  
    3.         ----------------------------------------- ----------------------------------- ----------------- -------------  
    4.         P_EMPNO             NUMBER(4) IN     DEFAULT  

    【训练3】  在发生编译错误时,显示错误。
    Sql代码 复制代码
    1. SHOW ERRORS  
    1. SHOW ERRORS  

    以下是一段编译错误显示:
    Sql代码 复制代码
    1. LINE/COL ERROR   
    2.         ------------- -----------------------------------------------------------------  
    3.         4/2       PL/SQL: SQL Statement ignored   
    4.         4/36      PLS-00201: 必须说明标识符 'EMPP'  
    1. LINE/COL ERROR  
    2.         ------------- -----------------------------------------------------------------  
    3.         4/2       PL/SQL: SQL Statement ignored  
    4.         4/36      PLS-00201: 必须说明标识符 'EMPP'  

      说明:查询一个存储过程或函数是否是有效状态(即编译成功),可以使用数据字典USER_OBJECTS的STATUS列。
    【训练4】  查询EMP_LIST存储过程是否可用:
    Sql代码 复制代码
    1. SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';  
    1. SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';  

    结果为:
    Sql代码 复制代码
    1. STATUS   
    2.         ------------  
    3.         VALID  
    1. STATUS  
    2.         ------------  
    3.         VALID  

    说明:VALID表示该存储过程有效(即通过编译),INVALID表示存储过程无效或需要重新编译。当Oracle调用一个无效的存储过程或函数时,首先试图对其进行编译,如果编译成功则将状态置成VALID并执行,否则给出错误信息。
    当一个存储过程编译成功,状态变为VALID,会不会在某些情况下变成INVALID。结论是完全可能的。比如一个存储过程中包含对表的查询,如果表被修改或删除,存储过程就会变成无效INVALID。所以要注意存储过程和函数对其他对象的依赖关系。
    如果要检查存储过程或函数的依赖性,可以通过查询数据字典USER_DENPENDENCIES来确定,该表结构如下:
    Sql代码 复制代码
    1. DESCRIBE USER_DEPENDENCIES;  
    1. DESCRIBE USER_DEPENDENCIES;  

    结果:
    Sql代码 复制代码
    1. 名称                     是否为空? 类型   
    2.         -------------------------------------------------------------- ------------- ----------------------------  
    3.          NAME            NOT NULL   VARCHAR2(30)   
    4.          TYPE                       VARCHAR2(12)   
    5.         REFERENCED_OWNER                                VARCHAR2(30)   
    6.  REFERENCED_NAME                                VARCHAR2(64)   
    7.  REFERENCED_TYPE                                VARCHAR2(12)   
    8. REFERENCED_LINK_NAME                            VARCHAR2(128)   
    9.         SCHEMAID                                        NUMBER   
    10.          DEPENDENCY_TYPE                                VARCHAR2(4)  
    1. 名称                     是否为空? 类型  
    2.         -------------------------------------------------------------- ------------- ----------------------------  
    3.          NAME            NOT NULL   VARCHAR2(30)  
    4.          TYPE                       VARCHAR2(12)  
    5.         REFERENCED_OWNER                                VARCHAR2(30)  
    6.  REFERENCED_NAME                                VARCHAR2(64)  
    7.  REFERENCED_TYPE                                VARCHAR2(12)  
    8. REFERENCED_LINK_NAME                            VARCHAR2(128)  
    9.         SCHEMAID                                        NUMBER  
    10.          DEPENDENCY_TYPE                                VARCHAR2(4)  

      说明:NAME为实体名,TYPE为实体类型,REFERENCED_OWNER为涉及到的实体拥有者账户,REFERENCED_NAME为涉及到的实体名,REFERENCED_TYPE 为涉及到的实体类型。
    【训练5】  查询EMP_LIST存储过程的依赖性。
    Sql代码 复制代码
    1. SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST';  
    1. SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST';  

    执行结果:
    Sql代码 复制代码
    1. REFERENCED_NAME                                         REFERENCED_TYPE   
    2.         ------------------------------------------------------------------------------------------ ----------------------------  
    3. STANDARD                                                PACKAGE   
    4.         SYS_STUB_FOR_PURITY_ANALYSIS                            PACKAGE   
    5.         DBMS_OUTPUT                                                 PACKAGE   
    6.         DBMS_OUTPUT                                             SYNONYM   
    7. DBMS_OUTPUT                      NON-EXISTENT   
    8.         EMP                                                         TABLE  
    9.         EMP_COUNT                                                   PROCEDURE  
    1. REFERENCED_NAME                                         REFERENCED_TYPE  
    2.         ------------------------------------------------------------------------------------------ ----------------------------  
    3. STANDARD                                                PACKAGE  
    4.         SYS_STUB_FOR_PURITY_ANALYSIS                            PACKAGE  
    5.         DBMS_OUTPUT                                                 PACKAGE  
    6.         DBMS_OUTPUT                                             SYNONYM  
    7. DBMS_OUTPUT                      NON-EXISTENT  
    8.         EMP                                                         TABLE  
    9.         EMP_COUNT                                                   PROCEDURE  

      说明:可以看出存储过程EMP_LIST依赖一些系统包、EMP表和EMP_COUNT存储过程。如果删除了EMP表或EMP_COUNT存储过程,EMP_LIST将变成无效。
    还有一种情况需要我们注意:如果一个用户A被授予执行属于用户B的一个存储过程的权限,在用户B的存储过程中,访问到用户C的表,用户B被授予访问用户C的表的权限,但用户A没有被授予访问用户C表的权限,那么用户A调用用户B的存储过程是失败的还是成功的呢?答案是成功的。如果读者有兴趣,不妨进行一下实际测试。


    包的概念和组成
    包是用来存储相关程序结构的对象,它存储于数据字典中。包由两个分离的部分组成:包头(PACKAGE)和包体(PACKAGE BODY)。包头是包的说明部分,是对外的操作接口,对应用是可见的;包体是包的代码和实现部分,对应用来说是不可见的黑盒。
    包中可以包含的程序结构如下所示。
    Sql代码 复制代码
    1. 过程(PROCUDURE)   带参数的命名的程序模块   
    2. 函数(FUNCTION)    带参数、具有返回值的命名的程序模块   
    3. 变量(VARIABLE)    存储变化的量的存储单元   
    4. 常量(CONSTANT)    存储不变的量的存储单元   
    5. 游标(CURSOR)  用户定义的数据操作缓存区,在可执行部分使用   
    6. 类型(TYPE)    用户定义的新的结构类型   
    7. 异常(EXCEPTION)   在标准包中定义或由用户自定义,用于处理程序错误  
    1. 过程(PROCUDURE)   带参数的命名的程序模块  
    2. 函数(FUNCTION)    带参数、具有返回值的命名的程序模块  
    3. 变量(VARIABLE)    存储变化的量的存储单元  
    4. 常量(CONSTANT)    存储不变的量的存储单元  
    5. 游标(CURSOR)  用户定义的数据操作缓存区,在可执行部分使用  
    6. 类型(TYPE)    用户定义的新的结构类型  
    7. 异常(EXCEPTION)   在标准包中定义或由用户自定义,用于处理程序错误  

    说明部分可以出现在包的三个不同的部分:出现在包头中的称为公有元素,出现在包体中的称为私有元素,出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同,如下所示。
    Sql代码 复制代码
    1. 公有元素(PUBLIC)    在包头中说明,在包体中具体定义 在包外可见并可以访问,对整个应用的全过程有效   
    2. 私有元素(PRIVATE)   在包体的说明部分说明  只能被包内部的其他部分访问   
    3. 局部变量(LOCAL) 在过程或函数的说明部分说明   只能在定义变量的过程或函数中使用  
    1. 公有元素(PUBLIC)    在包头中说明,在包体中具体定义 在包外可见并可以访问,对整个应用的全过程有效  
    2. 私有元素(PRIVATE)   在包体的说明部分说明  只能被包内部的其他部分访问  
    3. 局部变量(LOCAL) 在过程或函数的说明部分说明   只能在定义变量的过程或函数中使用  

    在包体中出现的过程或函数,如果需要对外公用,就必须在包头中说明,包头中的说明应该和包体中的说明一致。
    包有以下优点:
    * 包可以方便地将存储过程和函数组织到一起,每个包又是相互独立的。在不同的包中,过程、函数都可以重名,这解决了在同一个用户环境中命名的冲突问题。
    * 包增强了对存储过程和函数的安全管理,对整个包的访问权只需一次授予。
      * 在同一个会话中,公用变量的值将被保留,直到会话结束。
    * 区分了公有过程和私有过程,包体的私有过程增加了过程和函数的保密性。
    * 包在被首次调用时,就作为一个整体被全部调入内存,减少了多次访问过程或函数的I/O次数。
    创建包和包体
    包由包头和包体两部分组成,包的创建应该先创建包头部分,然后创建包体部分。创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。
    创建包头的简要语句如下:
    CREATE [OR REPLACE] PACKAGE 包名
    {IS|AS}
    公有变量定义
    公有类型定义
    公有游标定义
    公有异常定义
    函数说明
    过程说明
    END;
    创建包体的简要语法如下:
    CREATE [OR REPLACE] PACKAGE BODY 包名
    {IS|AS}
    私有变量定义
    私有类型定义
    私有游标定义
    私有异常定义
    函数定义
    过程定义
    END;
    包的其他操作命令包括:
    删除包头:
    DROP PACKAGE 包头名
    删除包体:
    DROP PACKAGE BODY 包体名
    重新编译包头:
    ALTER PACKAGE 包名 COMPILE PACKAGE
    重新编译包体:
    ALTER PACKAGE 包名 COMPILE PACKAGE BODY
    在包头中说明的对象可以在包外调用,调用的方法和调用单独的过程或函数的方法基本相同,惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意,不同的会话将单独对包的公用变量进行初始化,所以不同的会话对包的调用属于不同的应用。
    系统包
    Oracle预定义了很多标准的系统包,这些包可以在应用中直接使用,比如在训练中我们使用的DBMS_OUTPUT包,就是系统包。PUT_LINE是该包的一个函数。常用系统包下所示。
    Sql代码 复制代码
    1. DBMS_OUTPUT 在SQL*Plus环境下输出信息   
    2. DBMS_DDL    编译过程函数和包   
    3. DBMS_SESSION    改变用户的会话,初始化包等   
    4. DBMS_TRANSACTION    控制数据库事务   
    5. DBMS_MAIL   连接Oracle*Mail   
    6. DBMS_LOCK   进行复杂的锁机制管理   
    7. DBMS_ALERT  识别数据库事件告警   
    8. DBMS_PIPE   通过管道在会话间传递信息   
    9. DBMS_JOB    管理Oracle的作业   
    10. DBMS_LOB    操纵大对象   
    11. DBMS_SQL    执行动态SQL语句  
    1. DBMS_OUTPUT 在SQL*Plus环境下输出信息  
    2. DBMS_DDL    编译过程函数和包  
    3. DBMS_SESSION    改变用户的会话,初始化包等  
    4. DBMS_TRANSACTION    控制数据库事务  
    5. DBMS_MAIL   连接Oracle*Mail  
    6. DBMS_LOCK   进行复杂的锁机制管理  
    7. DBMS_ALERT  识别数据库事件告警  
    8. DBMS_PIPE   通过管道在会话间传递信息  
    9. DBMS_JOB    管理Oracle的作业  
    10. DBMS_LOB    操纵大对象  
    11. DBMS_SQL    执行动态SQL语句  

    包的应用
    在SQL*Plus环境下,包和包体可以分别编译,也可以一起编译。如果分别编译,则要先编译包头,后编译包体。如果在一起编译,则包头写在前,包体在后,中间用“/”分隔。
    可以将已经存在的存储过程或函数添加到包中,方法是去掉过程或函数创建语句的CREATE OR REPLACE部分,将存储过程或函数复制到包体中 ,然后重新编译即可。
       如果需要将私有过程或函数变成共有过程或函数的话,将过程或函数说明部分复制到包头说明部分,然后重新编译就可以了。
    【训练1】  创建管理雇员信息的包EMPLOYE,它具有从EMP表获得雇员信息,修改雇员名称,修改雇员工资和写回EMP表的功能。
    步骤1:登录SCOTT账户,输入以下代码并编译:
    Sql代码 复制代码
    1. CREATE OR REPLACE PACKAGE EMPLOYE --包头部分   
    2.         IS  
    3.  PROCEDURE SHOW_DETAIL;    
    4.  PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);    
    5.  PROCEDURE SAVE_EMPLOYE;    
    6.  PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);    
    7. PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);    
    8.         END EMPLOYE;   
    9.         /   
    10.         CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分   
    11.         IS  
    12.  EMPLOYE EMP%ROWTYPE;   
    13.         -------------- 显示雇员信息 ---------------  
    14.         PROCEDURE SHOW_DETAIL   
    15.         AS  
    16.         BEGIN  
    17. DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);     
    18.         DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);   
    19.         DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);   
    20.           DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);   
    21.          DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);   
    22.          DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);   
    23.         END SHOW_DETAIL;   
    24. ----------------- 从EMP表取得一个雇员 --------------------  
    25.          PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)   
    26.         AS  
    27.         BEGIN  
    28.         SELECT * INTO EMPLOYE FROM EMP WHERE    EMPNO=P_EMPNO;   
    29.         DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');   
    30.          EXCEPTION   
    31.          WHEN OTHERS THEN  
    32.            DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');   
    33.         END GET_EMPLOYE;   
    34. ---------------------- 保存雇员到EMP表 --------------------------  
    35.         PROCEDURE SAVE_EMPLOYE   
    36.         AS  
    37.         BEGIN  
    38.         UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=   
    39.     EMPLOYE.EMPNO;   
    40.      DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');   
    41.         END SAVE_EMPLOYE;   
    42. ---------------------------- 修改雇员名称 ------------------------------  
    43.         PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)   
    44.          AS  
    45.         BEGIN  
    46.          EMPLOYE.ENAME:=P_NEWNAME;   
    47.          DBMS_OUTPUT.PUT_LINE('修改名称完成!');   
    48.         END CHANGE_NAME;   
    49. ---------------------------- 修改雇员工资 --------------------------  
    50.         PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)   
    51.         AS  
    52.         BEGIN  
    53.          EMPLOYE.SAL:=P_NEWSAL;   
    54.          DBMS_OUTPUT.PUT_LINE('修改工资完成!');   
    55.         END CHANGE_SAL;   
    56.         END EMPLOYE;  
    1. CREATE OR REPLACE PACKAGE EMPLOYE --包头部分   
    2.         IS  
    3.  PROCEDURE SHOW_DETAIL;   
    4.  PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);   
    5.  PROCEDURE SAVE_EMPLOYE;   
    6.  PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);   
    7. PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);   
    8.         END EMPLOYE;  
    9.         /  
    10.         CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分   
    11.         IS  
    12.  EMPLOYE EMP%ROWTYPE;  
    13.         -------------- 显示雇员信息 ---------------  
    14.         PROCEDURE SHOW_DETAIL  
    15.         AS  
    16.         BEGIN  
    17. DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);     
    18.         DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);  
    19.         DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);  
    20.           DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);  
    21.          DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);  
    22.          DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);  
    23.         END SHOW_DETAIL;  
    24. ----------------- 从EMP表取得一个雇员 --------------------  
    25.          PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)  
    26.         AS  
    27.         BEGIN  
    28.         SELECT * INTO EMPLOYE FROM EMP WHERE    EMPNO=P_EMPNO;  
    29.         DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');  
    30.          EXCEPTION  
    31.          WHEN OTHERS THEN  
    32.            DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');  
    33.         END GET_EMPLOYE;  
    34. ---------------------- 保存雇员到EMP表 --------------------------  
    35.         PROCEDURE SAVE_EMPLOYE  
    36.         AS  
    37.         BEGIN  
    38.         UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=  
    39.     EMPLOYE.EMPNO;  
    40.      DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');  
    41.         END SAVE_EMPLOYE;  
    42. ---------------------------- 修改雇员名称 ------------------------------  
    43.         PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)  
    44.          AS  
    45.         BEGIN  
    46.          EMPLOYE.ENAME:=P_NEWNAME;  
    47.          DBMS_OUTPUT.PUT_LINE('修改名称完成!');  
    48.         END CHANGE_NAME;  
    49. ---------------------------- 修改雇员工资 --------------------------  
    50.         PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)  
    51.         AS  
    52.         BEGIN  
    53.          EMPLOYE.SAL:=P_NEWSAL;  
    54.          DBMS_OUTPUT.PUT_LINE('修改工资完成!');  
    55.         END CHANGE_SAL;  
    56.         END EMPLOYE;  

    步骤2:获取雇员7788的信息:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE EMPLOYE.GET_EMPLOYE(7788);  
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE EMPLOYE.GET_EMPLOYE(7788);  

    结果为:
    Sql代码 复制代码
    1. 获取雇员SCOTT信息成功   
    2.         PL/SQL 过程已成功完成。  
    1. 获取雇员SCOTT信息成功  
    2.         PL/SQL 过程已成功完成。  

    步骤3:显示雇员信息:
    Sql代码 复制代码
    1. EXECUTE EMPLOYE.SHOW_DETAIL;  
    1. EXECUTE EMPLOYE.SHOW_DETAIL;  

    结果为:
    Sql代码 复制代码
    1. ------------------ 雇员信息 ------------------  
    2.         雇员编号:7788   
    3.         雇员名称:SCOTT   
    4.         雇员职务:ANALYST   
    5.         雇员工资:3000   
    6.         部门编号:20   
    7.         PL/SQL 过程已成功完成。  
    1. ------------------ 雇员信息 ------------------  
    2.         雇员编号:7788  
    3.         雇员名称:SCOTT  
    4.         雇员职务:ANALYST  
    5.         雇员工资:3000  
    6.         部门编号:20  
    7.         PL/SQL 过程已成功完成。  

    步骤4:修改雇员工资:
    Sql代码 复制代码
    1. EXECUTE EMPLOYE.CHANGE_SAL(3800);  
    1. EXECUTE EMPLOYE.CHANGE_SAL(3800);  

    结果为:
    Sql代码 复制代码
    1. 修改工资完成!   
    2.         PL/SQL 过程已成功完成。  
    1. 修改工资完成!  
    2.         PL/SQL 过程已成功完成。  

    步骤5:将修改的雇员信息存入EMP表
    Sql代码 复制代码
    1. EXECUTE EMPLOYE.SAVE_EMPLOYE;  
    1. EXECUTE EMPLOYE.SAVE_EMPLOYE;  

    结果为:
    Sql代码 复制代码
    1. 雇员信息保存完成!   
    2.         PL/SQL 过程已成功完成。  
    1. 雇员信息保存完成!  
    2.         PL/SQL 过程已成功完成。  

    说明:该包完成将EMP表中的某个雇员的信息取入内存记录变量,在记录变量中进行修改编辑,在确认显示信息正确后写回EMP表的功能。记录变量EMPLOYE用来存储取得的雇员信息,定义为私有变量,只能被包的内部模块访问。
      【练习1】为包增加修改雇员职务和部门编号的功能。

    阶段训练
    下面的训练通过定义和创建完整的包EMP_PK并综合运用本章的知识,完成对雇员表的插入、删除等功能,包中的主要元素解释如下所示。
    Sql代码 复制代码
    1. 程序结构    类  型    说    明   
    2. V_EMP_COUNT 公有变量    跟踪雇员的总人数变化,插入、删除雇员的同时修改该变量的值   
    3. INIT    公有过程    对包进行初始化,初始化雇员人数和工资修改的上、下限   
    4. LIST_EMP    公有过程    显示雇员列表   
    5. INSERT_EMP  公有过程    通过编号插入新雇员   
    6. DELETE_EMP  公有过程    通过编号删除雇员   
    7. CHANGE_EMP_SAL  公有过程    通过编号修改雇员工资   
    8. V_MESSAGE   私有变量    存放准备输出的信息   
    9. C_MAX_SAL   私有变量    对工资修改的上限   
    10. C_MIN_SAL   私有变量    对工资修改的下限   
    11. SHOW_MESSAGE    私有过程    显示私有变量V_MESSAGE中的信息   
    12. EXIST_EMP   私有函数    判断某个编号的雇员是否存在,该函数被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等过程调用  
    1. 程序结构    类  型    说    明  
    2. V_EMP_COUNT 公有变量    跟踪雇员的总人数变化,插入、删除雇员的同时修改该变量的值  
    3. INIT    公有过程    对包进行初始化,初始化雇员人数和工资修改的上、下限  
    4. LIST_EMP    公有过程    显示雇员列表  
    5. INSERT_EMP  公有过程    通过编号插入新雇员  
    6. DELETE_EMP  公有过程    通过编号删除雇员  
    7. CHANGE_EMP_SAL  公有过程    通过编号修改雇员工资  
    8. V_MESSAGE   私有变量    存放准备输出的信息  
    9. C_MAX_SAL   私有变量    对工资修改的上限  
    10. C_MIN_SAL   私有变量    对工资修改的下限  
    11. SHOW_MESSAGE    私有过程    显示私有变量V_MESSAGE中的信息  
    12. EXIST_EMP   私有函数    判断某个编号的雇员是否存在,该函数被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等过程调用  

    【训练1】  完整的雇员包EMP_PK的创建和应用。
    步骤1:在SQL*Plus中登录SCOTT账户,输入以下包头和包体部分,按“执行”按钮编译:
    Sql代码 复制代码
    1. CREATE OR REPLACE PACKAGE EMP_PK    
    2.         --包头部分   
    3.         IS  
    4.         V_EMP_COUNT NUMBER(5);                 
    5.         --雇员人数  
    6.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER);  --初始化  
    7.         PROCEDURE LIST_EMP;                        
    8.         --显示雇员列表  
    9. PROCEDURE INSERT_EMP(P_EMPNO        NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,   
    10.         P_SAL NUMBER);                         
    11.         --插入雇员  
    12.         PROCEDURE DELETE_EMP(P_EMPNO NUMBER);       --删除雇员  
    13.          PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);    
    14.         --修改雇员工资  
    15.         END EMP_PK;   
    16.         /CREATE OR REPLACE PACKAGE BODY EMP_PK   
    17.          --包体部分   
    18.         IS  
    19.         V_MESSAGE VARCHAR2(50); --显示信息  
    20. V_MAX_SAL NUMBER(7); --工资上限  
    21.         V_MIN_SAL NUMBER(7); --工资下限  
    22.         FUNCTION EXIST_EMP(P_EMPNO NUMBER)  RETURN  BOOLEAN; --判断雇员是否存在函数  
    23.         PROCEDURE SHOW_MESSAGE; --显示信息过程  
    24.         ------------------------------- 初始化过程 ----------------------------  
    25.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)    
    26.         IS    
    27.         BEGIN  
    28.          SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;   
    29. V_MAX_SAL:=P_MAX;   
    30.          V_MIN_SAL:=P_MIN;   
    31.          V_MESSAGE:='初始化过程已经完成!';   
    32.          SHOW_MESSAGE;    
    33.         END INIT;   
    34. ---------------------------- 显示雇员列表过程 ---------------------  
    35.         PROCEDURE LIST_EMP    
    36.          IS    
    37.         BEGIN  
    38. DBMS_OUTPUT.PUT_LINE('姓名       职务      工资');   
    39.         FOR emp_rec IN (SELECT * FROM EMP)   
    40.         LOOP   
    41.     DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));   
    42.          END LOOP;   
    43.          DBMS_OUTPUT.PUT_LINE('雇员总人数'||V_EMP_COUNT);   
    44.         END LIST_EMP;   
    45. ----------------------------- 插入雇员过程 -----------------------------  
    46.         PROCEDUREINSERT_EMP(P_EMPNO     NUMBER,P_ENAMEVARCHAR2,P_JOB    VARCHAR2,P_SAL NUMBER)   
    47.          IS    
    48.         BEGIN  
    49.         IF NOT EXIST_EMP(P_EMPNO) THEN  
    50.         INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)        VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);   
    51.         COMMIT;    
    52.         V_EMP_COUNT:=V_EMP_COUNT+1;   
    53.         V_MESSAGE:='雇员'||P_EMPNO||'已插入!';   
    54.         ELSE  
    55. V_MESSAGE:='雇员'||P_EMPNO||'已存在,不能插入!';   
    56.       END IF;   
    57.      SHOW_MESSAGE;    
    58.      EXCEPTION   
    59.     WHEN OTHERS THEN  
    60.      V_MESSAGE:='雇员'||P_EMPNO||'插入失败!';   
    61.      SHOW_MESSAGE;   
    62.      END INSERT_EMP;   
    63. --------------------------- 删除雇员过程 --------------------  
    64.          PROCEDURE DELETE_EMP(P_EMPNO NUMBER)    
    65.         IS    
    66.         BEGIN    
    67.         IF EXIST_EMP(P_EMPNO) THEN  
    68.         DELETE FROM EMP WHERE EMPNO=P_EMPNO;   
    69.         COMMIT;   
    70.          V_EMP_COUNT:=V_EMP_COUNT-1;   
    71.          V_MESSAGE:='雇员'||P_EMPNO||'已删除!';   
    72.          ELSE  
    73. V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能删除!';   
    74.     END IF;   
    75.     SHOW_MESSAGE;   
    76.      EXCEPTION   
    77.      WHEN OTHERS THEN  
    78.      V_MESSAGE:='雇员'||P_EMPNO||'删除失败!';   
    79.      SHOW_MESSAGE;   
    80.     END DELETE_EMP;   
    81. --------------------------------------- 修改雇员工资过程 ------------------------------------  
    82.         PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)    
    83.          IS    
    84.          BEGIN    
    85.          IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN  
    86.          V_MESSAGE:='工资超出修改范围!';   
    87.         ELSIF NOT EXIST_EMP(P_EMPNO) THEN  
    88.         V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能修改工资!';   
    89. ELSE  
    90.          UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;   
    91.         COMMIT;   
    92.         V_MESSAGE:='雇员'||P_EMPNO||'工资已经修改!';   
    93.         END IF;   
    94.         SHOW_MESSAGE;   
    95.         EXCEPTION   
    96.          WHEN OTHERS THEN  
    97.          V_MESSAGE:='雇员'||P_EMPNO||'工资修改失败!';   
    98.          SHOW_MESSAGE;   
    99.          END CHANGE_EMP_SAL;   
    100. ---------------------------- 显示信息过程 ----------------------------  
    101.          PROCEDURE SHOW_MESSAGE    
    102.         IS    
    103.         BEGIN  
    104.          DBMS_OUTPUT.PUT_LINE('提示信息:'||V_MESSAGE);   
    105.         END SHOW_MESSAGE;   
    106. ------------------------ 判断雇员是否存在函数 -------------------  
    107.          FUNCTION EXIST_EMP(P_EMPNO NUMBER)   
    108.          RETURN BOOLEAN    
    109.          IS  
    110.         V_NUM NUMBER; --局部变量  
    111.         BEGIN  
    112.         SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;   
    113. IF V_NUM=1 THEN    
    114.            RETURN TRUE;   
    115.          ELSE  
    116.          RETURN FALSE;   
    117.         END IF;    
    118.         END EXIST_EMP;   
    119.         -----------------------------  
    120.         END EMP_PK;  
    1. CREATE OR REPLACE PACKAGE EMP_PK   
    2.         --包头部分   
    3.         IS  
    4.         V_EMP_COUNT NUMBER(5);                
    5.         --雇员人数  
    6.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER);  --初始化  
    7.         PROCEDURE LIST_EMP;                       
    8.         --显示雇员列表  
    9. PROCEDURE INSERT_EMP(P_EMPNO        NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,  
    10.         P_SAL NUMBER);                        
    11.         --插入雇员  
    12.         PROCEDURE DELETE_EMP(P_EMPNO NUMBER);       --删除雇员  
    13.          PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);   
    14.         --修改雇员工资  
    15.         END EMP_PK;  
    16.         /CREATE OR REPLACE PACKAGE BODY EMP_PK  
    17.          --包体部分   
    18.         IS  
    19.         V_MESSAGE VARCHAR2(50); --显示信息  
    20. V_MAX_SAL NUMBER(7); --工资上限  
    21.         V_MIN_SAL NUMBER(7); --工资下限  
    22.         FUNCTION EXIST_EMP(P_EMPNO NUMBER)  RETURN  BOOLEAN; --判断雇员是否存在函数  
    23.         PROCEDURE SHOW_MESSAGE; --显示信息过程  
    24.         ------------------------------- 初始化过程 ----------------------------  
    25.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)   
    26.         IS   
    27.         BEGIN  
    28.          SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;  
    29. V_MAX_SAL:=P_MAX;  
    30.          V_MIN_SAL:=P_MIN;  
    31.          V_MESSAGE:='初始化过程已经完成!';  
    32.          SHOW_MESSAGE;   
    33.         END INIT;  
    34. ---------------------------- 显示雇员列表过程 ---------------------  
    35.         PROCEDURE LIST_EMP   
    36.          IS   
    37.         BEGIN  
    38. DBMS_OUTPUT.PUT_LINE('姓名       职务      工资');  
    39.         FOR emp_rec IN (SELECT * FROM EMP)  
    40.         LOOP  
    41.     DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));  
    42.          END LOOP;  
    43.          DBMS_OUTPUT.PUT_LINE('雇员总人数'||V_EMP_COUNT);  
    44.         END LIST_EMP;  
    45. ----------------------------- 插入雇员过程 -----------------------------  
    46.         PROCEDUREINSERT_EMP(P_EMPNO     NUMBER,P_ENAMEVARCHAR2,P_JOB    VARCHAR2,P_SAL NUMBER)  
    47.          IS   
    48.         BEGIN  
    49.         IF NOT EXIST_EMP(P_EMPNO) THEN  
    50.         INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)        VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);  
    51.         COMMIT;   
    52.         V_EMP_COUNT:=V_EMP_COUNT+1;  
    53.         V_MESSAGE:='雇员'||P_EMPNO||'已插入!';  
    54.         ELSE  
    55. V_MESSAGE:='雇员'||P_EMPNO||'已存在,不能插入!';  
    56.       END IF;  
    57.      SHOW_MESSAGE;   
    58.      EXCEPTION  
    59.     WHEN OTHERS THEN  
    60.      V_MESSAGE:='雇员'||P_EMPNO||'插入失败!';  
    61.      SHOW_MESSAGE;  
    62.      END INSERT_EMP;  
    63. --------------------------- 删除雇员过程 --------------------  
    64.          PROCEDURE DELETE_EMP(P_EMPNO NUMBER)   
    65.         IS   
    66.         BEGIN   
    67.         IF EXIST_EMP(P_EMPNO) THEN  
    68.         DELETE FROM EMP WHERE EMPNO=P_EMPNO;  
    69.         COMMIT;  
    70.          V_EMP_COUNT:=V_EMP_COUNT-1;  
    71.          V_MESSAGE:='雇员'||P_EMPNO||'已删除!';  
    72.          ELSE  
    73. V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能删除!';  
    74.     END IF;  
    75.     SHOW_MESSAGE;  
    76.      EXCEPTION  
    77.      WHEN OTHERS THEN  
    78.      V_MESSAGE:='雇员'||P_EMPNO||'删除失败!';  
    79.      SHOW_MESSAGE;  
    80.     END DELETE_EMP;  
    81. --------------------------------------- 修改雇员工资过程 ------------------------------------  
    82.         PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)   
    83.          IS   
    84.          BEGIN   
    85.          IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN  
    86.          V_MESSAGE:='工资超出修改范围!';  
    87.         ELSIF NOT EXIST_EMP(P_EMPNO) THEN  
    88.         V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能修改工资!';  
    89. ELSE  
    90.          UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;  
    91.         COMMIT;  
    92.         V_MESSAGE:='雇员'||P_EMPNO||'工资已经修改!';  
    93.         END IF;  
    94.         SHOW_MESSAGE;  
    95.         EXCEPTION  
    96.          WHEN OTHERS THEN  
    97.          V_MESSAGE:='雇员'||P_EMPNO||'工资修改失败!';  
    98.          SHOW_MESSAGE;  
    99.          END CHANGE_EMP_SAL;  
    100. ---------------------------- 显示信息过程 ----------------------------  
    101.          PROCEDURE SHOW_MESSAGE   
    102.         IS   
    103.         BEGIN  
    104.          DBMS_OUTPUT.PUT_LINE('提示信息:'||V_MESSAGE);  
    105.         END SHOW_MESSAGE;  
    106. ------------------------ 判断雇员是否存在函数 -------------------  
    107.          FUNCTION EXIST_EMP(P_EMPNO NUMBER)  
    108.          RETURN BOOLEAN   
    109.          IS  
    110.         V_NUM NUMBER; --局部变量  
    111.         BEGIN  
    112.         SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;  
    113. IF V_NUM=1 THEN   
    114.            RETURN TRUE;  
    115.          ELSE  
    116.          RETURN FALSE;  
    117.         END IF;   
    118.         END EXIST_EMP;  
    119.         -----------------------------  
    120.         END EMP_PK;  
    结果为:
    Sql代码 复制代码
    1. 程序包已创建。   
    2.         程序包主体已创建。  
    1. 程序包已创建。  
    2.         程序包主体已创建。  

    步骤2:初始化包:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2. EXECUTE EMP_PK.INIT(6000,600);  
    1. SET SERVEROUTPUT ON  
    2. EXECUTE EMP_PK.INIT(6000,600);  

    显示为:
    Sql代码 复制代码
    1. 提示信息:初始化过程已经完成!  
    1. 提示信息:初始化过程已经完成!  

    步骤3:显示雇员列表:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.LIST_EMP;  
    1. EXECUTE EMP_PK.LIST_EMP;  

    显示为:
    Sql代码 复制代码
    1. 姓名          职务          工资   
    2.         SMITH       CLERK       1560   
    3.         ALLEN       SALESMAN    1936   
    4.         WARD        SALESMAN    1830   
    5.         JONES       MANAGER     2975   
    6.         ...   
    7.         雇员总人数:14   
    8.         PL/SQL 过程已成功完成。  
    1. 姓名          职务          工资  
    2.         SMITH       CLERK       1560  
    3.         ALLEN       SALESMAN    1936  
    4.         WARD        SALESMAN    1830  
    5.         JONES       MANAGER     2975  
    6.         ...  
    7.         雇员总人数:14  
    8.         PL/SQL 过程已成功完成。  

    步骤4:插入一个新记录:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000);  
    1. EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000);  

    显示结果为:
    Sql代码 复制代码
    1. 提示信息:雇员8001已插入!   
    2. PL/SQL 过程已成功完成。  
    1. 提示信息:雇员8001已插入!  
    2. PL/SQL 过程已成功完成。  

    步骤5:通过全局变量V_EMP_COUNT查看雇员人数:
    Sql代码 复制代码
    1. BEGIN  
    2. DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);   
    3. END;  
    1. BEGIN  
    2. DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);  
    3. END;  

    显示结果为:
    Sql代码 复制代码
    1. 15   
    2. PL/SQL 过程已成功完成。  
    1. 15  
    2. PL/SQL 过程已成功完成。  

    步骤6:删除新插入记录:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  

    显示结果为:
    Sql代码 复制代码
    1. 提示信息:雇员8001已删除!   
    2.         PL/SQL 过程已成功完成。  
    1. 提示信息:雇员8001已删除!  
    2.         PL/SQL 过程已成功完成。  

    再次删除该雇员:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  

    结果为:
    Sql代码 复制代码
    1. 提示信息:雇员8001不存在,不能删除!  
    1. 提示信息:雇员8001不存在,不能删除!  

    步骤7:修改雇员工资:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000);  
    1. EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000);  

    显示结果为:
    Sql代码 复制代码
    1. 提示信息:工资超出修改范围!   
    2.         PL/SQL 过程已成功完成。  
    1. 提示信息:工资超出修改范围!  
    2.         PL/SQL 过程已成功完成。  

    步骤8:授权其他用户调用包:
    如果是另外一个用户要使用该包,必须由包的所有者授权,下面授予STUDEN账户对该包的使用权:
    Sql代码 复制代码
    1. GRANT EXECUTE ON EMP_PK TO STUDENT;  
    1. GRANT EXECUTE ON EMP_PK TO STUDENT;  

    每一个新的会话要为包中的公用变量开辟新的存储空间,所以需要重新执行初始化过程。两个会话的进程互不影响。
    步骤9:其他用户调用包。
    启动另外一个SQL*Plus,登录STUDENT账户,执行以下过程:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700);  
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700);  

    结果为:
    Sql代码 复制代码
    1. 提示信息:初始化过程已经完成!   
    2.         PL/SQL 过程已成功完成。  
    1. 提示信息:初始化过程已经完成!  
    2.         PL/SQL 过程已成功完成。  

    说明:在初始化中设置雇员的总人数和修改工资的上、下限,初始化后V_EMP_COUNT为14人,插入雇员后V_EMP_COUNT为15人。V_EMP_COUNT为公有变量,所以可以在外部程序中使用DBMS_OUTPUT.PUT_LINE输出,引用时用EMP_PK.V_EMP_COUNT的形式,说明所属的包。而私有变量V_MAX_SAL和V_MIN_SAL不能被外部访问,只能通过内部过程来修改。同样,EXIST_EMP和SHOW_MESSAGE也是私有过程,也只能在过程体内被其他模块引用。
    注意:在最后一个步骤中,因为STUDENT模式调用了SCOTT模式的包,所以包名前要增加模式名SCOTT。不同的会话对包的调用属于不同的应用,所以需要重新进行初始化。
    练习
    1.如果存储过程的参数类型为OUT,那么调用时传递的参数应该为:
         A.常量 B.表达式                C.变量 D.都可以
    2.下列有关存储过程的特点说法错误的是:
         A.存储过程不能将值传回调用的主程序
         B.存储过程是一个命名的模块
         C.编译的存储过程存放在数据库中
         D.一个存储过程可以调用另一个存储过程
    3.下列有关函数的特点说法错误的是:
         A.函数必须定义返回类型
         B.函数参数的类型只能是IN
         C.在函数体内可以多次使用RETURN语句
         D.函数的调用应使用EXECUTE命令
    4.包中不能包含的元素为:
         A.存储过程 B.存储函数
         C.游标    D.表
    5.下列有关包的使用说法错误的是:
         A.在不同的包内模块可以重名
         B.包的私有过程不能被外部程序调用
         C.包体中的过程和函数必须在包头部分说明
         D.必须先创建包头,然后创建包体

    oracle存储过程实例

    分类: 数据(仓)库及处理 1055人阅读 评论(2) 收藏 举报
    认识存储过程和函数
    存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比,存储过程有很多优点,具体归纳如下:
    * 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的,因为代码不保存在本地,用户可以在任何客户机上登录到数据库,并调用或修改代码。
    * 存储过程和函数可由数据库提供安全保证,要想使用存储过程和函数,需要有存储过程和函数的所有者的授权,只有被授权的用户或创建者本身才能执行存储过程或调用函数。
    * 存储过程和函数的信息是写入数据字典的,所以存储过程可以看作是一个公用模块,用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能,可以设计成为存储过程,比如:显示一张工资统计表,可以设计成为存储过程;一个经常调用的计算,可以设计成为存储函数;根据雇员编号返回雇员的姓名,可以设计成存储函数。
    * 像其他高级语言的过程和函数一样,可以传递参数给存储过程或函数,参数的传递也有多种方式。存储过程可以有返回值,也可以没有返回值,存储过程的返回值必须通过参数带回;函数有一定的数据类型,像其他的标准函数一样,我们可以通过对函数名的调用返回函数值。
       存储过程和函数需要进行编译,以排除语法错误,只有编译通过才能调用。
    创建和删除存储过程
    创建存储过程,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下:
    CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
    {AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    [EXCEPTION
    错误处理部分]
    END [过程名];
    其中:
    可选关键字OR REPLACE 表示如果存储过程已经存在,则用新的存储过程覆盖,通常用于存储过程的重建。
    参数部分用于定义多个参数(如果没有参数,就可以省略)。参数有三种形式:IN、OUT和IN OUT。如果没有指明参数的形式,则默认为IN。
    关键字AS也可以写成IS,后跟过程的说明部分,可以在此定义过程的局部变量。
    编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行,编写好的存储过程必须要在SQL*Plus环境下进行编译,生成编译代码,原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。
    一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下:
    DROP PROCEDURE 存储过程名;
    如果要重新编译一个存储过程,则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下:
    ALTER PROCEDURE 存储过程名 COMPILE;
    执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下:
    方法1:
    EXECUTE 模式名.存储过程名[(参数...)];
    方法2:
    BEGIN
    模式名.存储过程名[(参数...)];
    END;
    传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值,则调用时可以省略参数)。参数可以是变量、常量或表达式,用法参见下一节。
    如果是调用本账户下的存储过程,则模式名可以省略。要调用其他账户编写的存储过程,则模式名必须要添加。
    以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。
    【训练1】  创建一个显示雇员总人数的存储过程。
    步骤1:登录SCOTT账户(或学生个人账户)。
    步骤2:在SQL*Plus输入区中,输入以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT   
    2. AS  
    3. V_TOTAL NUMBER(10);   
    4. BEGIN  
    5.  SELECT COUNT(*) INTO V_TOTAL FROM EMP;   
    6.  DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL);   
    7. END;  
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT  
    2. AS  
    3. V_TOTAL NUMBER(10);  
    4. BEGIN  
    5.  SELECT COUNT(*) INTO V_TOTAL FROM EMP;  
    6.  DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL);  
    7. END;  

    步骤3:按“执行”按钮进行编译。
    如果存在错误,就会显示:
    警告: 创建的过程带有编译错误。
    如果存在错误,对脚本进行修改,直到没有错误产生。
    如果编译结果正确,将显示:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤4:调用存储过程,在输入区中输入以下语句并执行:
    Sql代码 复制代码
    1. EXECUTE EMP_COUNT;  
    1. EXECUTE EMP_COUNT;  

    显示结果为:
    Sql代码 复制代码
    1. 雇员总人数为:14   
    2.         PL/SQL 过程已成功完成。  
    1. 雇员总人数为:14  
    2.         PL/SQL 过程已成功完成。  

    说明:在该训练中,V_TOTAL变量是存储过程定义的局部变量,用于接收查询到的雇员总人数。
    注意:在SQL*Plus中输入存储过程,按“执行”按钮是进行编译,不是执行存储过程。
      如果在存储过程中引用了其他用户的对象,比如表,则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功,就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。
    存储过程没有参数,在调用时,直接写过程名即可。
    【训练2】  在PL/SQL程序中调用存储过程。
    步骤1:登录SCOTT账户。
    步骤2:授权STUDENT账户使用该存储过程,即在SQL*Plus输入区中,输入以下的命令:
    Sql代码 复制代码
    1. GRANT EXECUTE ON EMP_COUNT TO STUDENT  
    1. GRANT EXECUTE ON EMP_COUNT TO STUDENT  

    Sql代码 复制代码
    1. 授权成功。  
    1. 授权成功。  

    步骤3:登录STUDENT账户,在SQL*Plus输入区中输入以下程序:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2.         BEGIN  
    3.         SCOTT.EMP_COUNT;   
    4.         END;  
    1. SET SERVEROUTPUT ON  
    2.         BEGIN  
    3.         SCOTT.EMP_COUNT;  
    4.         END;  

    步骤4:执行以上程序,结果为:
    Sql代码 复制代码
    1. 雇员总人数为:14   
    2.         PL/SQL 过程已成功完成。   
    1. 雇员总人数为:14  
    2.         PL/SQL 过程已成功完成。   

      说明:在本例中,存储过程是由SCOTT账户创建的,STUDEN账户获得SCOTT账户的授权后,才能调用该存储过程。
      注意:在程序中调用存储过程,使用了第二种语法。
    【训练3】  编写显示雇员信息的存储过程EMP_LIST,并引用EMP_COUNT存储过程。
    步骤1:在SQL*Plus输入区中输入并编译以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE EMP_LIST   
    2.         AS  
    3.          CURSOR emp_cursor IS    
    4.         SELECT empno,ename FROM emp;   
    5.         BEGIN  
    6. FOR Emp_record IN emp_cursor LOOP      
    7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);   
    8.         END LOOP;   
    9.         EMP_COUNT;   
    10.         END;  
    1. CREATE OR REPLACE PROCEDURE EMP_LIST  
    2.         AS  
    3.          CURSOR emp_cursor IS   
    4.         SELECT empno,ename FROM emp;  
    5.         BEGIN  
    6. FOR Emp_record IN emp_cursor LOOP     
    7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);  
    8.         END LOOP;  
    9.         EMP_COUNT;  
    10.         END;  

    执行结果:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤2:调用存储过程,在输入区中输入以下语句并执行:
    Sql代码 复制代码
    1. EXECUTE EMP_LIST  
    1. EXECUTE EMP_LIST  

    显示结果为:
    Sql代码 复制代码
    1. 7369SMITH   
    2. 7499ALLEN   
    3. 7521WARD   
    4. 7566JONES   
    5.             执行结果:   
    6.         雇员总人数为:14   
    7.         PL/SQL 过程已成功完成。  
    1. 7369SMITH  
    2. 7499ALLEN  
    3. 7521WARD  
    4. 7566JONES  
    5.             执行结果:  
    6.         雇员总人数为:14  
    7.         PL/SQL 过程已成功完成。  

    说明:以上的EMP_LIST存储过程中定义并使用了游标,用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程EMP_COUNT,用来附加显示雇员总人数。通过EXECUTE命令来执行EMP_LIST存储过程。
    【练习1】编写显示部门信息的存储过程DEPT_LIST,要求统计出部门个数。
    参数传递
    参数的作用是向存储过程传递数据,或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。
    参数的类型有三种,如下所示。
    Sql代码 复制代码
    1. IN  定义一个输入参数变量,用于传递参数给存储过程   
    2. OUT 定义一个输出参数变量,用于从存储过程获取数据   
    3. IN OUT  定义一个输入、输出参数变量,兼有以上两者的功能  
    1. IN  定义一个输入参数变量,用于传递参数给存储过程  
    2. OUT 定义一个输出参数变量,用于从存储过程获取数据  
    3. IN OUT  定义一个输入、输出参数变量,兼有以上两者的功能  

    参数的定义形式和作用如下:
    参数名 IN 数据类型 DEFAULT 值;
    定义一个输入参数变量,用于传递参数给存储过程。在调用存储过程时,主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项,用来设定参数的默认值。如果在调用存储过程时不指明参数,则参数变量取默认值。在存储过程中,输入变量接收主程序传递的值,但不能对其进行赋值。
    参数名 OUT 数据类型;
    定义一个输出参数变量,用于从存储过程获取数据,即变量从存储过程中返回值给主程序。
    在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。在存储过程中,参数变量只能被赋值而不能将其用于赋值,在存储过程中必须给输出变量至少赋值一次。
    参数名 IN OUT 数据类型 DEFAULT 值;
    定义一个输入、输出参数变量,兼有以上两者的功能。在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。DEFAULT 关键字为可选项,用来设定参数的默认值。在存储过程中,变量接收主程序传递的值,同时可以参加赋值运算,也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。
    如果省略IN、OUT或IN OUT,则默认模式是IN。
    【训练1】  编写给雇员增加工资的存储过程CHANGE_SALARY,通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
    步骤1:登录SCOTT账户。
      步骤2:在SQL*Plus输入区中输入以下存储过程并执行:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)   
    2.         AS  
    3.          V_ENAME VARCHAR2(10);   
    4. V_SAL NUMBER(5);   
    5.         BEGIN  
    6.         SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;   
    7.          UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;   
    8.          DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));   
    9. COMMIT;   
    10.         EXCEPTION   
    11.          WHEN OTHERS THEN  
    12.         DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');   
    13.         ROLLBACK;   
    14.         END;  
    1. CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)  
    2.         AS  
    3.          V_ENAME VARCHAR2(10);  
    4. V_SAL NUMBER(5);  
    5.         BEGIN  
    6.         SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;  
    7.          UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;  
    8.          DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));  
    9. COMMIT;  
    10.         EXCEPTION  
    11.          WHEN OTHERS THEN  
    12.         DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');  
    13.         ROLLBACK;  
    14.         END;  

    执行结果为:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤3:调用存储过程,在输入区中输入以下语句并执行:
    Sql代码 复制代码
    1. EXECUTE CHANGE_SALARY(7788,80)  
    1. EXECUTE CHANGE_SALARY(7788,80)  

    显示结果为:
    Sql代码 复制代码
    1. 雇员SCOTT的工资被改为3080   
    1. 雇员SCOTT的工资被改为3080   

    说明:从执行结果可以看到,雇员SCOTT的工资已由原来的3000改为3080。
    参数的值由调用者传递,传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致,可以采用以下调用方法。如上例,执行语句可以改为:
     EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);
      可以看出传递参数的顺序发生了变化,并且明确指出了参数名和要传递的值,=>运算符左侧是参数名,右侧是参数表达式,这种赋值方法的意义较清楚。
    【练习1】创建插入雇员的存储过程INSERT_EMP,并将雇员编号等作为参数。
    在设计存储过程的时候,也可以为参数设定默认值,这样调用者就可以不传递或少传递参数了。
    【训练2】  调用存储过程CHANGE_SALARY,不传递参数,使用默认参数值。
    在SQL*Plus输入区中输入以下命令并执行:
    Sql代码 复制代码
    1. EXECUTE CHANGE_SALARY  
    1. EXECUTE CHANGE_SALARY  

    显示结果为:
    Sql代码 复制代码
    1. 雇员SCOTT的工资被改为3090   
    1. 雇员SCOTT的工资被改为3090   

    说明:在存储过程的调用中没有传递参数,而是采用了默认值7788和10,即默认雇员号为7788,增加的工资为10。
    【训练3】  使用OUT类型的参数返回存储过程的结果。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入并编译以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)   
    2.         AS  
    3.         BEGIN  
    4.         SELECT COUNT(*) INTO P_TOTAL FROM EMP;   
    5.         END;  
    1. CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)  
    2.         AS  
    3.         BEGIN  
    4.         SELECT COUNT(*) INTO P_TOTAL FROM EMP;  
    5.         END;  

    执行结果为:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤3:输入以下程序并执行:
    Sql代码 复制代码
    1. DECLARE  
    2.         V_EMPCOUNT NUMBER;   
    3.         BEGIN  
    4.         EMP_COUNT(V_EMPCOUNT);   
    5.         DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);   
    6.         END;  
    1. DECLARE  
    2.         V_EMPCOUNT NUMBER;  
    3.         BEGIN  
    4.         EMP_COUNT(V_EMPCOUNT);  
    5.         DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);  
    6.         END;  

    显示结果为:
    Sql代码 复制代码
    1. 雇员总人数为:14   
    2.         PL/SQL 过程已成功完成。  
    1. 雇员总人数为:14  
    2.         PL/SQL 过程已成功完成。  

        说明:在存储过程中定义了OUT类型的参数P_TOTAL,在主程序调用该存储过程时,传递了参数V_EMPCOUNT。在存储过程中的SELECT...INTO...语句中对P_TOTAL进行赋值,赋值结果由V_EMPCOUNT变量带回给主程序并显示。
    以上程序要覆盖同名的EMP_COUNT存储过程,如果不使用OR REPLACE选项,就会出现以下错误:
    Sql代码 复制代码
    1. ERROR 位于第 1 行:   
    2.         ORA-00955: 名称已由现有对象使用。  
    1. ERROR 位于第 1 行:  
    2.         ORA-00955: 名称已由现有对象使用。  

    【练习2】创建存储过程,使用OUT类型参数获得雇员经理名。
    【训练4】  使用IN OUT类型的参数,给电话号码增加区码。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入并编译以下存储过程:
    Sql代码 复制代码
    1. CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)   
    2.         AS  
    3.         BEGIN  
    4.          P_HPONE_NUM:='0755-'||P_HPONE_NUM;   
    5.         END;  
    1. CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)  
    2.         AS  
    3.         BEGIN  
    4.          P_HPONE_NUM:='0755-'||P_HPONE_NUM;  
    5.         END;  

    执行结果为:
    Sql代码 复制代码
    1. 过程已创建。  
    1. 过程已创建。  

    步骤3:输入以下程序并执行:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2. DECLARE  
    3. V_PHONE_NUM VARCHAR2(15);   
    4. BEGIN  
    5. V_PHONE_NUM:='26731092';   
    6. ADD_REGION(V_PHONE_NUM);   
    7. DBMS_OUTPUT.PUT_LINE('新的电话号码:'||V_PHONE_NUM);   
    8. END;  
    1. SET SERVEROUTPUT ON  
    2. DECLARE  
    3. V_PHONE_NUM VARCHAR2(15);  
    4. BEGIN  
    5. V_PHONE_NUM:='26731092';  
    6. ADD_REGION(V_PHONE_NUM);  
    7. DBMS_OUTPUT.PUT_LINE('新的电话号码:'||V_PHONE_NUM);  
    8. END;  

    显示结果为:
    Sql代码 复制代码
    1. 新的电话号码:0755-26731092   
    2.         PL/SQL 过程已成功完成。  
    1. 新的电话号码:0755-26731092  
    2.         PL/SQL 过程已成功完成。  

    说明:变量V_HPONE_NUM既用来向存储过程传递旧电话号码,也用来向主程序返回新号码。新的号码在原来基础上增加了区号0755和-。
    创建和删除存储函数
      创建函数,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建存储函数的语法和创建存储过程的类似,即
    CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)]
    RETURN 数据类型
    {AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    RETURN (表达式)
    [EXCEPTION
        错误处理部分]
    END [函数名];
    其中,参数是可选的,但只能是IN类型(IN关键字可以省略)。
    在定义部分的RETURN 数据类型,用来表示函数的数据类型,也就是返回值的类型,此部分不可省略。
    在可执行部分的RETURN(表达式),用来生成函数的返回值,其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句,但只有一个RETURN语句会被执行,一旦执行了RETURN语句,则函数结束并返回调用环境。
    一个存储函数在不需要时可以删除,但删除的人应是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人。其语法如下:
    DROP FUNCTION 函数名;
    重新编译一个存储函数时,编译的人应是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。重新编译一个存储函数的语法如下:
    ALTER PROCEDURE 函数名 COMPILE;
    函数的调用者应是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人,或是被函数的拥有者授予了函数执行权限的账户。函数的引用和存储过程不同,函数要出现在程序体中,可以参加表达式的运算或单独出现在表达式中,其形式如下:
    变量名:=函数名(...)
    【训练1】  创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入以下存储函数并编译:
    Sql代码 复制代码
    1. CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)   
    2.         RETURN VARCHAR2   
    3.         AS  
    4.          V_ENAME VARCHAR2(10);   
    5.         BEGIN  
    6.         ELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;   
    7. RETURN(V_ENAME);   
    8. EXCEPTION   
    9.  WHEN NO_DATA_FOUND THEN  
    10.   DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');   
    11.   RETURN (NULL);   
    12.  WHEN TOO_MANY_ROWS THEN  
    13.   DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');   
    14.   RETURN (NULL);   
    15.  WHEN OTHERS THEN  
    16.   DBMS_OUTPUT.PUT_LINE('发生其他错误!');   
    17.   RETURN (NULL);   
    18. END;  
    1. CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)  
    2.         RETURN VARCHAR2  
    3.         AS  
    4.          V_ENAME VARCHAR2(10);  
    5.         BEGIN  
    6.         ELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;  
    7. RETURN(V_ENAME);  
    8. EXCEPTION  
    9.  WHEN NO_DATA_FOUND THEN  
    10.   DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');  
    11.   RETURN (NULL);  
    12.  WHEN TOO_MANY_ROWS THEN  
    13.   DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');  
    14.   RETURN (NULL);  
    15.  WHEN OTHERS THEN  
    16.   DBMS_OUTPUT.PUT_LINE('发生其他错误!');  
    17.   RETURN (NULL);  
    18. END;  

    步骤3:调用该存储函数,输入并执行以下程序:
    Sql代码 复制代码
    1. BEGIN  
    2.         DBMS_OUTPUT.PUT_LINE('雇员7369的名称是:'|| GET_EMP_NAME(7369));   
    3.          DBMS_OUTPUT.PUT_LINE('雇员7839的名称是:'|| GET_EMP_NAME(7839));   
    4.         END;  
    1. BEGIN  
    2.         DBMS_OUTPUT.PUT_LINE('雇员7369的名称是:'|| GET_EMP_NAME(7369));  
    3.          DBMS_OUTPUT.PUT_LINE('雇员7839的名称是:'|| GET_EMP_NAME(7839));  
    4.         END;  

    显示结果为:
    Sql代码 复制代码
    1. 雇员7369的名称是:SMITH   
    2.         雇员7839的名称是:KING   
    3.         PL/SQL 过程已成功完成。  
    1. 雇员7369的名称是:SMITH  
    2.         雇员7839的名称是:KING  
    3.         PL/SQL 过程已成功完成。  

    说明:函数的调用直接出现在程序的DBMS_OUTPUT.PUT_LINE语句中,作为字符串表达式的一部分。如果输入了错误的雇员编号,就会在函数的错误处理部分输出错误信息。试修改雇员编号,重新运行调用部分。
    【练习1】创建一个通过部门编号返回部门名称的存储函数GET_DEPT_NAME。
       【练习2】将函数的执行权限授予STUDENT账户,然后登录STUDENT账户调用。
    存储过程和函数的查看
    可以通过对数据字典的访问来查询存储过程或函数的有关信息,如果要查询当前用户的存储过程或函数的源代码,可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下:
    Sql代码 复制代码
    1. DESCRIBE USER_SOURCE  
    1. DESCRIBE USER_SOURCE  

    结果为:
    Sql代码 复制代码
    1. 名称                                      是否为空? 类型   
    2.         ------------------------------------------------------------- ------------- -----------------------  
    3.  NAME                                               VARCHAR2(30)   
    4.  TYPE                                               VARCHAR2(12)   
    5.  LINE                                               NUMBER   
    6.  TEXT                                               VARCHAR2(4000)  
    1. 名称                                      是否为空? 类型  
    2.         ------------------------------------------------------------- ------------- -----------------------  
    3.  NAME                                               VARCHAR2(30)  
    4.  TYPE                                               VARCHAR2(12)  
    5.  LINE                                               NUMBER  
    6.  TEXT                                               VARCHAR2(4000)  

      说明:里面按行存放着过程或函数的脚本,NAME是过程或函数名,TYPE 代表类型(PROCEDURE或FUNCTION),LINE是行号,TEXT 为脚本。
    【训练1】  查询过程EMP_COUNT的脚本。
    在SQL*Plus中输入并执行如下查询:
    Sql代码 复制代码
    1. select TEXT  from user_source WHERE NAME='EMP_COUNT';  
    1. select TEXT  from user_source WHERE NAME='EMP_COUNT';  

    结果为:
    Sql代码 复制代码
    1. TEXT   
    2. --------------------------------------------------------------------------------  
    3. PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)   
    4. AS  
    5. BEGIN  
    6.  SELECT COUNT(*) INTO P_TOTAL FROM EMP;   
    7. END;  
    1. TEXT  
    2. --------------------------------------------------------------------------------  
    3. PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)  
    4. AS  
    5. BEGIN  
    6.  SELECT COUNT(*) INTO P_TOTAL FROM EMP;  
    7. END;  

    【训练2】  查询过程GET_EMP_NAME的参数。
    在SQL*Plus中输入并执行如下查询:
    Sql代码 复制代码
    1. DESCRIBE GET_EMP_NAME  
    1. DESCRIBE GET_EMP_NAME  

    结果为:
    Sql代码 复制代码
    1. FUNCTION GET_EMP_NAME RETURNS VARCHAR2   
    2.         参数名称            类型          输入/输出默认值?   
    3.         ----------------------------------------- ----------------------------------- ----------------- -------------  
    4.         P_EMPNO             NUMBER(4) IN     DEFAULT  
    1. FUNCTION GET_EMP_NAME RETURNS VARCHAR2  
    2.         参数名称            类型          输入/输出默认值?  
    3.         ----------------------------------------- ----------------------------------- ----------------- -------------  
    4.         P_EMPNO             NUMBER(4) IN     DEFAULT  

    【训练3】  在发生编译错误时,显示错误。
    Sql代码 复制代码
    1. SHOW ERRORS  
    1. SHOW ERRORS  

    以下是一段编译错误显示:
    Sql代码 复制代码
    1. LINE/COL ERROR   
    2.         ------------- -----------------------------------------------------------------  
    3.         4/2       PL/SQL: SQL Statement ignored   
    4.         4/36      PLS-00201: 必须说明标识符 'EMPP'  
    1. LINE/COL ERROR  
    2.         ------------- -----------------------------------------------------------------  
    3.         4/2       PL/SQL: SQL Statement ignored  
    4.         4/36      PLS-00201: 必须说明标识符 'EMPP'  

      说明:查询一个存储过程或函数是否是有效状态(即编译成功),可以使用数据字典USER_OBJECTS的STATUS列。
    【训练4】  查询EMP_LIST存储过程是否可用:
    Sql代码 复制代码
    1. SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';  
    1. SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';  

    结果为:
    Sql代码 复制代码
    1. STATUS   
    2.         ------------  
    3.         VALID  
    1. STATUS  
    2.         ------------  
    3.         VALID  

    说明:VALID表示该存储过程有效(即通过编译),INVALID表示存储过程无效或需要重新编译。当Oracle调用一个无效的存储过程或函数时,首先试图对其进行编译,如果编译成功则将状态置成VALID并执行,否则给出错误信息。
    当一个存储过程编译成功,状态变为VALID,会不会在某些情况下变成INVALID。结论是完全可能的。比如一个存储过程中包含对表的查询,如果表被修改或删除,存储过程就会变成无效INVALID。所以要注意存储过程和函数对其他对象的依赖关系。
    如果要检查存储过程或函数的依赖性,可以通过查询数据字典USER_DENPENDENCIES来确定,该表结构如下:
    Sql代码 复制代码
    1. DESCRIBE USER_DEPENDENCIES;  
    1. DESCRIBE USER_DEPENDENCIES;  

    结果:
    Sql代码 复制代码
    1. 名称                     是否为空? 类型   
    2.         -------------------------------------------------------------- ------------- ----------------------------  
    3.          NAME            NOT NULL   VARCHAR2(30)   
    4.          TYPE                       VARCHAR2(12)   
    5.         REFERENCED_OWNER                                VARCHAR2(30)   
    6.  REFERENCED_NAME                                VARCHAR2(64)   
    7.  REFERENCED_TYPE                                VARCHAR2(12)   
    8. REFERENCED_LINK_NAME                            VARCHAR2(128)   
    9.         SCHEMAID                                        NUMBER   
    10.          DEPENDENCY_TYPE                                VARCHAR2(4)  
    1. 名称                     是否为空? 类型  
    2.         -------------------------------------------------------------- ------------- ----------------------------  
    3.          NAME            NOT NULL   VARCHAR2(30)  
    4.          TYPE                       VARCHAR2(12)  
    5.         REFERENCED_OWNER                                VARCHAR2(30)  
    6.  REFERENCED_NAME                                VARCHAR2(64)  
    7.  REFERENCED_TYPE                                VARCHAR2(12)  
    8. REFERENCED_LINK_NAME                            VARCHAR2(128)  
    9.         SCHEMAID                                        NUMBER  
    10.          DEPENDENCY_TYPE                                VARCHAR2(4)  

      说明:NAME为实体名,TYPE为实体类型,REFERENCED_OWNER为涉及到的实体拥有者账户,REFERENCED_NAME为涉及到的实体名,REFERENCED_TYPE 为涉及到的实体类型。
    【训练5】  查询EMP_LIST存储过程的依赖性。
    Sql代码 复制代码
    1. SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST';  
    1. SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST';  

    执行结果:
    Sql代码 复制代码
    1. REFERENCED_NAME                                         REFERENCED_TYPE   
    2.         ------------------------------------------------------------------------------------------ ----------------------------  
    3. STANDARD                                                PACKAGE   
    4.         SYS_STUB_FOR_PURITY_ANALYSIS                            PACKAGE   
    5.         DBMS_OUTPUT                                                 PACKAGE   
    6.         DBMS_OUTPUT                                             SYNONYM   
    7. DBMS_OUTPUT                      NON-EXISTENT   
    8.         EMP                                                         TABLE  
    9.         EMP_COUNT                                                   PROCEDURE  
    1. REFERENCED_NAME                                         REFERENCED_TYPE  
    2.         ------------------------------------------------------------------------------------------ ----------------------------  
    3. STANDARD                                                PACKAGE  
    4.         SYS_STUB_FOR_PURITY_ANALYSIS                            PACKAGE  
    5.         DBMS_OUTPUT                                                 PACKAGE  
    6.         DBMS_OUTPUT                                             SYNONYM  
    7. DBMS_OUTPUT                      NON-EXISTENT  
    8.         EMP                                                         TABLE  
    9.         EMP_COUNT                                                   PROCEDURE  

      说明:可以看出存储过程EMP_LIST依赖一些系统包、EMP表和EMP_COUNT存储过程。如果删除了EMP表或EMP_COUNT存储过程,EMP_LIST将变成无效。
    还有一种情况需要我们注意:如果一个用户A被授予执行属于用户B的一个存储过程的权限,在用户B的存储过程中,访问到用户C的表,用户B被授予访问用户C的表的权限,但用户A没有被授予访问用户C表的权限,那么用户A调用用户B的存储过程是失败的还是成功的呢?答案是成功的。如果读者有兴趣,不妨进行一下实际测试。


    包的概念和组成
    包是用来存储相关程序结构的对象,它存储于数据字典中。包由两个分离的部分组成:包头(PACKAGE)和包体(PACKAGE BODY)。包头是包的说明部分,是对外的操作接口,对应用是可见的;包体是包的代码和实现部分,对应用来说是不可见的黑盒。
    包中可以包含的程序结构如下所示。
    Sql代码 复制代码
    1. 过程(PROCUDURE)   带参数的命名的程序模块   
    2. 函数(FUNCTION)    带参数、具有返回值的命名的程序模块   
    3. 变量(VARIABLE)    存储变化的量的存储单元   
    4. 常量(CONSTANT)    存储不变的量的存储单元   
    5. 游标(CURSOR)  用户定义的数据操作缓存区,在可执行部分使用   
    6. 类型(TYPE)    用户定义的新的结构类型   
    7. 异常(EXCEPTION)   在标准包中定义或由用户自定义,用于处理程序错误  
    1. 过程(PROCUDURE)   带参数的命名的程序模块  
    2. 函数(FUNCTION)    带参数、具有返回值的命名的程序模块  
    3. 变量(VARIABLE)    存储变化的量的存储单元  
    4. 常量(CONSTANT)    存储不变的量的存储单元  
    5. 游标(CURSOR)  用户定义的数据操作缓存区,在可执行部分使用  
    6. 类型(TYPE)    用户定义的新的结构类型  
    7. 异常(EXCEPTION)   在标准包中定义或由用户自定义,用于处理程序错误  

    说明部分可以出现在包的三个不同的部分:出现在包头中的称为公有元素,出现在包体中的称为私有元素,出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同,如下所示。
    Sql代码 复制代码
    1. 公有元素(PUBLIC)    在包头中说明,在包体中具体定义 在包外可见并可以访问,对整个应用的全过程有效   
    2. 私有元素(PRIVATE)   在包体的说明部分说明  只能被包内部的其他部分访问   
    3. 局部变量(LOCAL) 在过程或函数的说明部分说明   只能在定义变量的过程或函数中使用  
    1. 公有元素(PUBLIC)    在包头中说明,在包体中具体定义 在包外可见并可以访问,对整个应用的全过程有效  
    2. 私有元素(PRIVATE)   在包体的说明部分说明  只能被包内部的其他部分访问  
    3. 局部变量(LOCAL) 在过程或函数的说明部分说明   只能在定义变量的过程或函数中使用  

    在包体中出现的过程或函数,如果需要对外公用,就必须在包头中说明,包头中的说明应该和包体中的说明一致。
    包有以下优点:
    * 包可以方便地将存储过程和函数组织到一起,每个包又是相互独立的。在不同的包中,过程、函数都可以重名,这解决了在同一个用户环境中命名的冲突问题。
    * 包增强了对存储过程和函数的安全管理,对整个包的访问权只需一次授予。
      * 在同一个会话中,公用变量的值将被保留,直到会话结束。
    * 区分了公有过程和私有过程,包体的私有过程增加了过程和函数的保密性。
    * 包在被首次调用时,就作为一个整体被全部调入内存,减少了多次访问过程或函数的I/O次数。
    创建包和包体
    包由包头和包体两部分组成,包的创建应该先创建包头部分,然后创建包体部分。创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。
    创建包头的简要语句如下:
    CREATE [OR REPLACE] PACKAGE 包名
    {IS|AS}
    公有变量定义
    公有类型定义
    公有游标定义
    公有异常定义
    函数说明
    过程说明
    END;
    创建包体的简要语法如下:
    CREATE [OR REPLACE] PACKAGE BODY 包名
    {IS|AS}
    私有变量定义
    私有类型定义
    私有游标定义
    私有异常定义
    函数定义
    过程定义
    END;
    包的其他操作命令包括:
    删除包头:
    DROP PACKAGE 包头名
    删除包体:
    DROP PACKAGE BODY 包体名
    重新编译包头:
    ALTER PACKAGE 包名 COMPILE PACKAGE
    重新编译包体:
    ALTER PACKAGE 包名 COMPILE PACKAGE BODY
    在包头中说明的对象可以在包外调用,调用的方法和调用单独的过程或函数的方法基本相同,惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意,不同的会话将单独对包的公用变量进行初始化,所以不同的会话对包的调用属于不同的应用。
    系统包
    Oracle预定义了很多标准的系统包,这些包可以在应用中直接使用,比如在训练中我们使用的DBMS_OUTPUT包,就是系统包。PUT_LINE是该包的一个函数。常用系统包下所示。
    Sql代码 复制代码
    1. DBMS_OUTPUT 在SQL*Plus环境下输出信息   
    2. DBMS_DDL    编译过程函数和包   
    3. DBMS_SESSION    改变用户的会话,初始化包等   
    4. DBMS_TRANSACTION    控制数据库事务   
    5. DBMS_MAIL   连接Oracle*Mail   
    6. DBMS_LOCK   进行复杂的锁机制管理   
    7. DBMS_ALERT  识别数据库事件告警   
    8. DBMS_PIPE   通过管道在会话间传递信息   
    9. DBMS_JOB    管理Oracle的作业   
    10. DBMS_LOB    操纵大对象   
    11. DBMS_SQL    执行动态SQL语句  
    1. DBMS_OUTPUT 在SQL*Plus环境下输出信息  
    2. DBMS_DDL    编译过程函数和包  
    3. DBMS_SESSION    改变用户的会话,初始化包等  
    4. DBMS_TRANSACTION    控制数据库事务  
    5. DBMS_MAIL   连接Oracle*Mail  
    6. DBMS_LOCK   进行复杂的锁机制管理  
    7. DBMS_ALERT  识别数据库事件告警  
    8. DBMS_PIPE   通过管道在会话间传递信息  
    9. DBMS_JOB    管理Oracle的作业  
    10. DBMS_LOB    操纵大对象  
    11. DBMS_SQL    执行动态SQL语句  

    包的应用
    在SQL*Plus环境下,包和包体可以分别编译,也可以一起编译。如果分别编译,则要先编译包头,后编译包体。如果在一起编译,则包头写在前,包体在后,中间用“/”分隔。
    可以将已经存在的存储过程或函数添加到包中,方法是去掉过程或函数创建语句的CREATE OR REPLACE部分,将存储过程或函数复制到包体中 ,然后重新编译即可。
       如果需要将私有过程或函数变成共有过程或函数的话,将过程或函数说明部分复制到包头说明部分,然后重新编译就可以了。
    【训练1】  创建管理雇员信息的包EMPLOYE,它具有从EMP表获得雇员信息,修改雇员名称,修改雇员工资和写回EMP表的功能。
    步骤1:登录SCOTT账户,输入以下代码并编译:
    Sql代码 复制代码
    1. CREATE OR REPLACE PACKAGE EMPLOYE --包头部分   
    2.         IS  
    3.  PROCEDURE SHOW_DETAIL;    
    4.  PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);    
    5.  PROCEDURE SAVE_EMPLOYE;    
    6.  PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);    
    7. PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);    
    8.         END EMPLOYE;   
    9.         /   
    10.         CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分   
    11.         IS  
    12.  EMPLOYE EMP%ROWTYPE;   
    13.         -------------- 显示雇员信息 ---------------  
    14.         PROCEDURE SHOW_DETAIL   
    15.         AS  
    16.         BEGIN  
    17. DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);     
    18.         DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);   
    19.         DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);   
    20.           DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);   
    21.          DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);   
    22.          DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);   
    23.         END SHOW_DETAIL;   
    24. ----------------- 从EMP表取得一个雇员 --------------------  
    25.          PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)   
    26.         AS  
    27.         BEGIN  
    28.         SELECT * INTO EMPLOYE FROM EMP WHERE    EMPNO=P_EMPNO;   
    29.         DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');   
    30.          EXCEPTION   
    31.          WHEN OTHERS THEN  
    32.            DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');   
    33.         END GET_EMPLOYE;   
    34. ---------------------- 保存雇员到EMP表 --------------------------  
    35.         PROCEDURE SAVE_EMPLOYE   
    36.         AS  
    37.         BEGIN  
    38.         UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=   
    39.     EMPLOYE.EMPNO;   
    40.      DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');   
    41.         END SAVE_EMPLOYE;   
    42. ---------------------------- 修改雇员名称 ------------------------------  
    43.         PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)   
    44.          AS  
    45.         BEGIN  
    46.          EMPLOYE.ENAME:=P_NEWNAME;   
    47.          DBMS_OUTPUT.PUT_LINE('修改名称完成!');   
    48.         END CHANGE_NAME;   
    49. ---------------------------- 修改雇员工资 --------------------------  
    50.         PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)   
    51.         AS  
    52.         BEGIN  
    53.          EMPLOYE.SAL:=P_NEWSAL;   
    54.          DBMS_OUTPUT.PUT_LINE('修改工资完成!');   
    55.         END CHANGE_SAL;   
    56.         END EMPLOYE;  
    1. CREATE OR REPLACE PACKAGE EMPLOYE --包头部分   
    2.         IS  
    3.  PROCEDURE SHOW_DETAIL;   
    4.  PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);   
    5.  PROCEDURE SAVE_EMPLOYE;   
    6.  PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);   
    7. PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);   
    8.         END EMPLOYE;  
    9.         /  
    10.         CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分   
    11.         IS  
    12.  EMPLOYE EMP%ROWTYPE;  
    13.         -------------- 显示雇员信息 ---------------  
    14.         PROCEDURE SHOW_DETAIL  
    15.         AS  
    16.         BEGIN  
    17. DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----’);     
    18.         DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);  
    19.         DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);  
    20.           DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);  
    21.          DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);  
    22.          DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);  
    23.         END SHOW_DETAIL;  
    24. ----------------- 从EMP表取得一个雇员 --------------------  
    25.          PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)  
    26.         AS  
    27.         BEGIN  
    28.         SELECT * INTO EMPLOYE FROM EMP WHERE    EMPNO=P_EMPNO;  
    29.         DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');  
    30.          EXCEPTION  
    31.          WHEN OTHERS THEN  
    32.            DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');  
    33.         END GET_EMPLOYE;  
    34. ---------------------- 保存雇员到EMP表 --------------------------  
    35.         PROCEDURE SAVE_EMPLOYE  
    36.         AS  
    37.         BEGIN  
    38.         UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=  
    39.     EMPLOYE.EMPNO;  
    40.      DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');  
    41.         END SAVE_EMPLOYE;  
    42. ---------------------------- 修改雇员名称 ------------------------------  
    43.         PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)  
    44.          AS  
    45.         BEGIN  
    46.          EMPLOYE.ENAME:=P_NEWNAME;  
    47.          DBMS_OUTPUT.PUT_LINE('修改名称完成!');  
    48.         END CHANGE_NAME;  
    49. ---------------------------- 修改雇员工资 --------------------------  
    50.         PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)  
    51.         AS  
    52.         BEGIN  
    53.          EMPLOYE.SAL:=P_NEWSAL;  
    54.          DBMS_OUTPUT.PUT_LINE('修改工资完成!');  
    55.         END CHANGE_SAL;  
    56.         END EMPLOYE;  

    步骤2:获取雇员7788的信息:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE EMPLOYE.GET_EMPLOYE(7788);  
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE EMPLOYE.GET_EMPLOYE(7788);  

    结果为:
    Sql代码 复制代码
    1. 获取雇员SCOTT信息成功   
    2.         PL/SQL 过程已成功完成。  
    1. 获取雇员SCOTT信息成功  
    2.         PL/SQL 过程已成功完成。  

    步骤3:显示雇员信息:
    Sql代码 复制代码
    1. EXECUTE EMPLOYE.SHOW_DETAIL;  
    1. EXECUTE EMPLOYE.SHOW_DETAIL;  

    结果为:
    Sql代码 复制代码
    1. ------------------ 雇员信息 ------------------  
    2.         雇员编号:7788   
    3.         雇员名称:SCOTT   
    4.         雇员职务:ANALYST   
    5.         雇员工资:3000   
    6.         部门编号:20   
    7.         PL/SQL 过程已成功完成。  
    1. ------------------ 雇员信息 ------------------  
    2.         雇员编号:7788  
    3.         雇员名称:SCOTT  
    4.         雇员职务:ANALYST  
    5.         雇员工资:3000  
    6.         部门编号:20  
    7.         PL/SQL 过程已成功完成。  

    步骤4:修改雇员工资:
    Sql代码 复制代码
    1. EXECUTE EMPLOYE.CHANGE_SAL(3800);  
    1. EXECUTE EMPLOYE.CHANGE_SAL(3800);  

    结果为:
    Sql代码 复制代码
    1. 修改工资完成!   
    2.         PL/SQL 过程已成功完成。  
    1. 修改工资完成!  
    2.         PL/SQL 过程已成功完成。  

    步骤5:将修改的雇员信息存入EMP表
    Sql代码 复制代码
    1. EXECUTE EMPLOYE.SAVE_EMPLOYE;  
    1. EXECUTE EMPLOYE.SAVE_EMPLOYE;  

    结果为:
    Sql代码 复制代码
    1. 雇员信息保存完成!   
    2.         PL/SQL 过程已成功完成。  
    1. 雇员信息保存完成!  
    2.         PL/SQL 过程已成功完成。  

    说明:该包完成将EMP表中的某个雇员的信息取入内存记录变量,在记录变量中进行修改编辑,在确认显示信息正确后写回EMP表的功能。记录变量EMPLOYE用来存储取得的雇员信息,定义为私有变量,只能被包的内部模块访问。
      【练习1】为包增加修改雇员职务和部门编号的功能。

    阶段训练
    下面的训练通过定义和创建完整的包EMP_PK并综合运用本章的知识,完成对雇员表的插入、删除等功能,包中的主要元素解释如下所示。
    Sql代码 复制代码
    1. 程序结构    类  型    说    明   
    2. V_EMP_COUNT 公有变量    跟踪雇员的总人数变化,插入、删除雇员的同时修改该变量的值   
    3. INIT    公有过程    对包进行初始化,初始化雇员人数和工资修改的上、下限   
    4. LIST_EMP    公有过程    显示雇员列表   
    5. INSERT_EMP  公有过程    通过编号插入新雇员   
    6. DELETE_EMP  公有过程    通过编号删除雇员   
    7. CHANGE_EMP_SAL  公有过程    通过编号修改雇员工资   
    8. V_MESSAGE   私有变量    存放准备输出的信息   
    9. C_MAX_SAL   私有变量    对工资修改的上限   
    10. C_MIN_SAL   私有变量    对工资修改的下限   
    11. SHOW_MESSAGE    私有过程    显示私有变量V_MESSAGE中的信息   
    12. EXIST_EMP   私有函数    判断某个编号的雇员是否存在,该函数被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等过程调用  
    1. 程序结构    类  型    说    明  
    2. V_EMP_COUNT 公有变量    跟踪雇员的总人数变化,插入、删除雇员的同时修改该变量的值  
    3. INIT    公有过程    对包进行初始化,初始化雇员人数和工资修改的上、下限  
    4. LIST_EMP    公有过程    显示雇员列表  
    5. INSERT_EMP  公有过程    通过编号插入新雇员  
    6. DELETE_EMP  公有过程    通过编号删除雇员  
    7. CHANGE_EMP_SAL  公有过程    通过编号修改雇员工资  
    8. V_MESSAGE   私有变量    存放准备输出的信息  
    9. C_MAX_SAL   私有变量    对工资修改的上限  
    10. C_MIN_SAL   私有变量    对工资修改的下限  
    11. SHOW_MESSAGE    私有过程    显示私有变量V_MESSAGE中的信息  
    12. EXIST_EMP   私有函数    判断某个编号的雇员是否存在,该函数被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等过程调用  

    【训练1】  完整的雇员包EMP_PK的创建和应用。
    步骤1:在SQL*Plus中登录SCOTT账户,输入以下包头和包体部分,按“执行”按钮编译:
    Sql代码 复制代码
    1. CREATE OR REPLACE PACKAGE EMP_PK    
    2.         --包头部分   
    3.         IS  
    4.         V_EMP_COUNT NUMBER(5);                 
    5.         --雇员人数  
    6.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER);  --初始化  
    7.         PROCEDURE LIST_EMP;                        
    8.         --显示雇员列表  
    9. PROCEDURE INSERT_EMP(P_EMPNO        NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,   
    10.         P_SAL NUMBER);                         
    11.         --插入雇员  
    12.         PROCEDURE DELETE_EMP(P_EMPNO NUMBER);       --删除雇员  
    13.          PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);    
    14.         --修改雇员工资  
    15.         END EMP_PK;   
    16.         /CREATE OR REPLACE PACKAGE BODY EMP_PK   
    17.          --包体部分   
    18.         IS  
    19.         V_MESSAGE VARCHAR2(50); --显示信息  
    20. V_MAX_SAL NUMBER(7); --工资上限  
    21.         V_MIN_SAL NUMBER(7); --工资下限  
    22.         FUNCTION EXIST_EMP(P_EMPNO NUMBER)  RETURN  BOOLEAN; --判断雇员是否存在函数  
    23.         PROCEDURE SHOW_MESSAGE; --显示信息过程  
    24.         ------------------------------- 初始化过程 ----------------------------  
    25.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)    
    26.         IS    
    27.         BEGIN  
    28.          SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;   
    29. V_MAX_SAL:=P_MAX;   
    30.          V_MIN_SAL:=P_MIN;   
    31.          V_MESSAGE:='初始化过程已经完成!';   
    32.          SHOW_MESSAGE;    
    33.         END INIT;   
    34. ---------------------------- 显示雇员列表过程 ---------------------  
    35.         PROCEDURE LIST_EMP    
    36.          IS    
    37.         BEGIN  
    38. DBMS_OUTPUT.PUT_LINE('姓名       职务      工资');   
    39.         FOR emp_rec IN (SELECT * FROM EMP)   
    40.         LOOP   
    41.     DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));   
    42.          END LOOP;   
    43.          DBMS_OUTPUT.PUT_LINE('雇员总人数'||V_EMP_COUNT);   
    44.         END LIST_EMP;   
    45. ----------------------------- 插入雇员过程 -----------------------------  
    46.         PROCEDUREINSERT_EMP(P_EMPNO     NUMBER,P_ENAMEVARCHAR2,P_JOB    VARCHAR2,P_SAL NUMBER)   
    47.          IS    
    48.         BEGIN  
    49.         IF NOT EXIST_EMP(P_EMPNO) THEN  
    50.         INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)        VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);   
    51.         COMMIT;    
    52.         V_EMP_COUNT:=V_EMP_COUNT+1;   
    53.         V_MESSAGE:='雇员'||P_EMPNO||'已插入!';   
    54.         ELSE  
    55. V_MESSAGE:='雇员'||P_EMPNO||'已存在,不能插入!';   
    56.       END IF;   
    57.      SHOW_MESSAGE;    
    58.      EXCEPTION   
    59.     WHEN OTHERS THEN  
    60.      V_MESSAGE:='雇员'||P_EMPNO||'插入失败!';   
    61.      SHOW_MESSAGE;   
    62.      END INSERT_EMP;   
    63. --------------------------- 删除雇员过程 --------------------  
    64.          PROCEDURE DELETE_EMP(P_EMPNO NUMBER)    
    65.         IS    
    66.         BEGIN    
    67.         IF EXIST_EMP(P_EMPNO) THEN  
    68.         DELETE FROM EMP WHERE EMPNO=P_EMPNO;   
    69.         COMMIT;   
    70.          V_EMP_COUNT:=V_EMP_COUNT-1;   
    71.          V_MESSAGE:='雇员'||P_EMPNO||'已删除!';   
    72.          ELSE  
    73. V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能删除!';   
    74.     END IF;   
    75.     SHOW_MESSAGE;   
    76.      EXCEPTION   
    77.      WHEN OTHERS THEN  
    78.      V_MESSAGE:='雇员'||P_EMPNO||'删除失败!';   
    79.      SHOW_MESSAGE;   
    80.     END DELETE_EMP;   
    81. --------------------------------------- 修改雇员工资过程 ------------------------------------  
    82.         PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)    
    83.          IS    
    84.          BEGIN    
    85.          IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN  
    86.          V_MESSAGE:='工资超出修改范围!';   
    87.         ELSIF NOT EXIST_EMP(P_EMPNO) THEN  
    88.         V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能修改工资!';   
    89. ELSE  
    90.          UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;   
    91.         COMMIT;   
    92.         V_MESSAGE:='雇员'||P_EMPNO||'工资已经修改!';   
    93.         END IF;   
    94.         SHOW_MESSAGE;   
    95.         EXCEPTION   
    96.          WHEN OTHERS THEN  
    97.          V_MESSAGE:='雇员'||P_EMPNO||'工资修改失败!';   
    98.          SHOW_MESSAGE;   
    99.          END CHANGE_EMP_SAL;   
    100. ---------------------------- 显示信息过程 ----------------------------  
    101.          PROCEDURE SHOW_MESSAGE    
    102.         IS    
    103.         BEGIN  
    104.          DBMS_OUTPUT.PUT_LINE('提示信息:'||V_MESSAGE);   
    105.         END SHOW_MESSAGE;   
    106. ------------------------ 判断雇员是否存在函数 -------------------  
    107.          FUNCTION EXIST_EMP(P_EMPNO NUMBER)   
    108.          RETURN BOOLEAN    
    109.          IS  
    110.         V_NUM NUMBER; --局部变量  
    111.         BEGIN  
    112.         SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;   
    113. IF V_NUM=1 THEN    
    114.            RETURN TRUE;   
    115.          ELSE  
    116.          RETURN FALSE;   
    117.         END IF;    
    118.         END EXIST_EMP;   
    119.         -----------------------------  
    120.         END EMP_PK;  
    1. CREATE OR REPLACE PACKAGE EMP_PK   
    2.         --包头部分   
    3.         IS  
    4.         V_EMP_COUNT NUMBER(5);                
    5.         --雇员人数  
    6.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER);  --初始化  
    7.         PROCEDURE LIST_EMP;                       
    8.         --显示雇员列表  
    9. PROCEDURE INSERT_EMP(P_EMPNO        NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,  
    10.         P_SAL NUMBER);                        
    11.         --插入雇员  
    12.         PROCEDURE DELETE_EMP(P_EMPNO NUMBER);       --删除雇员  
    13.          PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);   
    14.         --修改雇员工资  
    15.         END EMP_PK;  
    16.         /CREATE OR REPLACE PACKAGE BODY EMP_PK  
    17.          --包体部分   
    18.         IS  
    19.         V_MESSAGE VARCHAR2(50); --显示信息  
    20. V_MAX_SAL NUMBER(7); --工资上限  
    21.         V_MIN_SAL NUMBER(7); --工资下限  
    22.         FUNCTION EXIST_EMP(P_EMPNO NUMBER)  RETURN  BOOLEAN; --判断雇员是否存在函数  
    23.         PROCEDURE SHOW_MESSAGE; --显示信息过程  
    24.         ------------------------------- 初始化过程 ----------------------------  
    25.         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)   
    26.         IS   
    27.         BEGIN  
    28.          SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;  
    29. V_MAX_SAL:=P_MAX;  
    30.          V_MIN_SAL:=P_MIN;  
    31.          V_MESSAGE:='初始化过程已经完成!';  
    32.          SHOW_MESSAGE;   
    33.         END INIT;  
    34. ---------------------------- 显示雇员列表过程 ---------------------  
    35.         PROCEDURE LIST_EMP   
    36.          IS   
    37.         BEGIN  
    38. DBMS_OUTPUT.PUT_LINE('姓名       职务      工资');  
    39.         FOR emp_rec IN (SELECT * FROM EMP)  
    40.         LOOP  
    41.     DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));  
    42.          END LOOP;  
    43.          DBMS_OUTPUT.PUT_LINE('雇员总人数'||V_EMP_COUNT);  
    44.         END LIST_EMP;  
    45. ----------------------------- 插入雇员过程 -----------------------------  
    46.         PROCEDUREINSERT_EMP(P_EMPNO     NUMBER,P_ENAMEVARCHAR2,P_JOB    VARCHAR2,P_SAL NUMBER)  
    47.          IS   
    48.         BEGIN  
    49.         IF NOT EXIST_EMP(P_EMPNO) THEN  
    50.         INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)        VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);  
    51.         COMMIT;   
    52.         V_EMP_COUNT:=V_EMP_COUNT+1;  
    53.         V_MESSAGE:='雇员'||P_EMPNO||'已插入!';  
    54.         ELSE  
    55. V_MESSAGE:='雇员'||P_EMPNO||'已存在,不能插入!';  
    56.       END IF;  
    57.      SHOW_MESSAGE;   
    58.      EXCEPTION  
    59.     WHEN OTHERS THEN  
    60.      V_MESSAGE:='雇员'||P_EMPNO||'插入失败!';  
    61.      SHOW_MESSAGE;  
    62.      END INSERT_EMP;  
    63. --------------------------- 删除雇员过程 --------------------  
    64.          PROCEDURE DELETE_EMP(P_EMPNO NUMBER)   
    65.         IS   
    66.         BEGIN   
    67.         IF EXIST_EMP(P_EMPNO) THEN  
    68.         DELETE FROM EMP WHERE EMPNO=P_EMPNO;  
    69.         COMMIT;  
    70.          V_EMP_COUNT:=V_EMP_COUNT-1;  
    71.          V_MESSAGE:='雇员'||P_EMPNO||'已删除!';  
    72.          ELSE  
    73. V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能删除!';  
    74.     END IF;  
    75.     SHOW_MESSAGE;  
    76.      EXCEPTION  
    77.      WHEN OTHERS THEN  
    78.      V_MESSAGE:='雇员'||P_EMPNO||'删除失败!';  
    79.      SHOW_MESSAGE;  
    80.     END DELETE_EMP;  
    81. --------------------------------------- 修改雇员工资过程 ------------------------------------  
    82.         PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)   
    83.          IS   
    84.          BEGIN   
    85.          IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN  
    86.          V_MESSAGE:='工资超出修改范围!';  
    87.         ELSIF NOT EXIST_EMP(P_EMPNO) THEN  
    88.         V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能修改工资!';  
    89. ELSE  
    90.          UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;  
    91.         COMMIT;  
    92.         V_MESSAGE:='雇员'||P_EMPNO||'工资已经修改!';  
    93.         END IF;  
    94.         SHOW_MESSAGE;  
    95.         EXCEPTION  
    96.          WHEN OTHERS THEN  
    97.          V_MESSAGE:='雇员'||P_EMPNO||'工资修改失败!';  
    98.          SHOW_MESSAGE;  
    99.          END CHANGE_EMP_SAL;  
    100. ---------------------------- 显示信息过程 ----------------------------  
    101.          PROCEDURE SHOW_MESSAGE   
    102.         IS   
    103.         BEGIN  
    104.          DBMS_OUTPUT.PUT_LINE('提示信息:'||V_MESSAGE);  
    105.         END SHOW_MESSAGE;  
    106. ------------------------ 判断雇员是否存在函数 -------------------  
    107.          FUNCTION EXIST_EMP(P_EMPNO NUMBER)  
    108.          RETURN BOOLEAN   
    109.          IS  
    110.         V_NUM NUMBER; --局部变量  
    111.         BEGIN  
    112.         SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;  
    113. IF V_NUM=1 THEN   
    114.            RETURN TRUE;  
    115.          ELSE  
    116.          RETURN FALSE;  
    117.         END IF;   
    118.         END EXIST_EMP;  
    119.         -----------------------------  
    120.         END EMP_PK;  
    结果为:
    Sql代码 复制代码
    1. 程序包已创建。   
    2.         程序包主体已创建。  
    1. 程序包已创建。  
    2.         程序包主体已创建。  

    步骤2:初始化包:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2. EXECUTE EMP_PK.INIT(6000,600);  
    1. SET SERVEROUTPUT ON  
    2. EXECUTE EMP_PK.INIT(6000,600);  

    显示为:
    Sql代码 复制代码
    1. 提示信息:初始化过程已经完成!  
    1. 提示信息:初始化过程已经完成!  

    步骤3:显示雇员列表:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.LIST_EMP;  
    1. EXECUTE EMP_PK.LIST_EMP;  

    显示为:
    Sql代码 复制代码
    1. 姓名          职务          工资   
    2.         SMITH       CLERK       1560   
    3.         ALLEN       SALESMAN    1936   
    4.         WARD        SALESMAN    1830   
    5.         JONES       MANAGER     2975   
    6.         ...   
    7.         雇员总人数:14   
    8.         PL/SQL 过程已成功完成。  
    1. 姓名          职务          工资  
    2.         SMITH       CLERK       1560  
    3.         ALLEN       SALESMAN    1936  
    4.         WARD        SALESMAN    1830  
    5.         JONES       MANAGER     2975  
    6.         ...  
    7.         雇员总人数:14  
    8.         PL/SQL 过程已成功完成。  

    步骤4:插入一个新记录:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000);  
    1. EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000);  

    显示结果为:
    Sql代码 复制代码
    1. 提示信息:雇员8001已插入!   
    2. PL/SQL 过程已成功完成。  
    1. 提示信息:雇员8001已插入!  
    2. PL/SQL 过程已成功完成。  

    步骤5:通过全局变量V_EMP_COUNT查看雇员人数:
    Sql代码 复制代码
    1. BEGIN  
    2. DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);   
    3. END;  
    1. BEGIN  
    2. DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);  
    3. END;  

    显示结果为:
    Sql代码 复制代码
    1. 15   
    2. PL/SQL 过程已成功完成。  
    1. 15  
    2. PL/SQL 过程已成功完成。  

    步骤6:删除新插入记录:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  

    显示结果为:
    Sql代码 复制代码
    1. 提示信息:雇员8001已删除!   
    2.         PL/SQL 过程已成功完成。  
    1. 提示信息:雇员8001已删除!  
    2.         PL/SQL 过程已成功完成。  

    再次删除该雇员:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  
    1. EXECUTE EMP_PK.DELETE_EMP(8001);  

    结果为:
    Sql代码 复制代码
    1. 提示信息:雇员8001不存在,不能删除!  
    1. 提示信息:雇员8001不存在,不能删除!  

    步骤7:修改雇员工资:
    Sql代码 复制代码
    1. EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000);  
    1. EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000);  

    显示结果为:
    Sql代码 复制代码
    1. 提示信息:工资超出修改范围!   
    2.         PL/SQL 过程已成功完成。  
    1. 提示信息:工资超出修改范围!  
    2.         PL/SQL 过程已成功完成。  

    步骤8:授权其他用户调用包:
    如果是另外一个用户要使用该包,必须由包的所有者授权,下面授予STUDEN账户对该包的使用权:
    Sql代码 复制代码
    1. GRANT EXECUTE ON EMP_PK TO STUDENT;  
    1. GRANT EXECUTE ON EMP_PK TO STUDENT;  

    每一个新的会话要为包中的公用变量开辟新的存储空间,所以需要重新执行初始化过程。两个会话的进程互不影响。
    步骤9:其他用户调用包。
    启动另外一个SQL*Plus,登录STUDENT账户,执行以下过程:
    Sql代码 复制代码
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700);  
    1. SET SERVEROUTPUT ON  
    2.         EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700);  

    结果为:
    Sql代码 复制代码
    1. 提示信息:初始化过程已经完成!   
    2.         PL/SQL 过程已成功完成。  
    1. 提示信息:初始化过程已经完成!  
    2.         PL/SQL 过程已成功完成。  

    说明:在初始化中设置雇员的总人数和修改工资的上、下限,初始化后V_EMP_COUNT为14人,插入雇员后V_EMP_COUNT为15人。V_EMP_COUNT为公有变量,所以可以在外部程序中使用DBMS_OUTPUT.PUT_LINE输出,引用时用EMP_PK.V_EMP_COUNT的形式,说明所属的包。而私有变量V_MAX_SAL和V_MIN_SAL不能被外部访问,只能通过内部过程来修改。同样,EXIST_EMP和SHOW_MESSAGE也是私有过程,也只能在过程体内被其他模块引用。
    注意:在最后一个步骤中,因为STUDENT模式调用了SCOTT模式的包,所以包名前要增加模式名SCOTT。不同的会话对包的调用属于不同的应用,所以需要重新进行初始化。
    练习
    1.如果存储过程的参数类型为OUT,那么调用时传递的参数应该为:
         A.常量 B.表达式                C.变量 D.都可以
    2.下列有关存储过程的特点说法错误的是:
         A.存储过程不能将值传回调用的主程序
         B.存储过程是一个命名的模块
         C.编译的存储过程存放在数据库中
         D.一个存储过程可以调用另一个存储过程
    3.下列有关函数的特点说法错误的是:
         A.函数必须定义返回类型
         B.函数参数的类型只能是IN
         C.在函数体内可以多次使用RETURN语句
         D.函数的调用应使用EXECUTE命令
    4.包中不能包含的元素为:
         A.存储过程 B.存储函数
         C.游标    D.表
    5.下列有关包的使用说法错误的是:
         A.在不同的包内模块可以重名
         B.包的私有过程不能被外部程序调用
         C.包体中的过程和函数必须在包头部分说明
         D.必须先创建包头,然后创建包体
    展开全文
  • Oracle 存储过程实例集锦

    千次阅读 2016-07-13 13:23:17
    一、如何创建存储过程procedure 1、创建一个存储过程用于保存已上架商品的数量 CREATE OR REPLACE PROCEDURE getGoodCount IS goodCount int; BEGIN SELECT COUNT(*

    一、如何创建存储过程procedure

    1、创建一个存储过程用于保存已上架商品的数量
    CREATE ORREPLACE PROCEDURE getGoodCount IS
    goodCount int;
    BEGIN
    SELECT COUNT(*)INTO goodCount FROMtable_good where status = '3';
    DBMS_OUTPUT.PUT_LINE('good表共有'||goodCount||'笔上架商品');
    END getGoodCount;
    call getGoodCount();
     
    2、根据商品编号,查询商品信息:
    CREATE ORREPLACE PROCEDURE getgoodinfo(goodid IN NUMBER)IS
    title table_good.good_title%TYPE;
    BEGIN
    SELECT good_titleINTO title  FROMtable_good WHERE table_good.id=goodid;
    DBMS_OUTPUT.PUT_LINE(goodid||'号商品名称为'||title);
    EXCEPTION
    WHEN NO_DATA_FOUNDTHEN
    DBMS_OUTPUT.PUT_LINE('没有找到该商品');
    END;
    call getgoodinfo(2170);
     
    3、创建有输入和输出参数的过程:
    CREATE ORREPLACE PROCEDURE getgoodinforeturn(goodid IN NUMBER,v_re out VARCHAR2)IS
    BEGIN
    SELECT good_titleINTO v_re  FROMtable_good WHERE table_good.id=goodid;
    EXCEPTION
    WHEN NO_DATA_FOUNDTHEN
    DBMS_OUTPUT.PUT_LINE('没有找到该商品');
    END;
    DECLARE
    title VARCHAR2(100);
    BEGIN
    getgoodinforeturn(2170,title);
    DBMS_OUTPUT.PUT_LINE(title);
    END;
     
    4、创建输入输出同类型参数的过程:
    CREATE ORREPLACE PROCEDURE getgoodinforeturn2(d IN OUT NUMBER) IS
    BEGIN
    SELECT table_good.goods_salesINTO FROMtable_good WHERE table_good.id=d;
    EXCEPTION
    WHEN NO_DATA_FOUNDTHEN
    DBMS_OUTPUT.PUT_LINE('没有找到该商品');
    END;
    DECLARE
    sales Number(10);
    BEGIN
    sales:=4003;
    getgoodinforeturn2(sales);
    DBMS_OUTPUT.PUT_LINE(sales);
    END;
     
    5、默认值的过程
    CREATE ORREPLACE PROCEDURE addGood
    (
    id NUMBER,
    title VARCHAR2,
    content  VARCHAR2 :='CLERK',
    mgr  NUMBER,
    hdate DATE DEFAULT SYSDATE,
    sal  NUMBER  DEFAULT1000,
    comm  NUMBER  DEFAULT0,
    deptNo NUMBER  DEFAULT30
    )
    AS
    BEGIN
    INSERT INTOtable_good VALUES(id,title,content,mgr,hdate,sal,comm,deptNo);
    END;
    EXEC addEmp(7776,'zhangsan','CODER',7788,'06-1月-2000',2000,0,10); --没有使用默认值
    EXEC addEmp(7777,'lisi','CODER',7788,'06-1月-2000',2000,NULL,10); --可以使用NULL值
    EXEC addEmp(7778,'wangwu',mgr=>7788); --使用默认值
    EXEC addEmp(mgr=>7788,empNo=>7779,eName=>'sunliu'); --更改参数顺序

    ...... ...... 还可以update,delete等等

    二、常用命令

    1、删除存储过程
    DROP PROCEDURE Proc_Name;
     
    2、查看过程状态
    SELECT object_name,status  FROM USER_OBJECTS WHERE object_type='PROCEDURE';
     
    3、重新编译过程
    ALTER PROCEDURE Proc_Name COMPILE;
     
    4、查看过程代码
    SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';

    三、关于循环:

    1、loop
    declare
    v_count number(2) := 0;
    begin
    loop
    -- 循环开始
    v_count := v_count + 1;
    dbms_output.put_line(v_count);
    exit whenv_count = 10; --当v_count等于10 时退出循环。
    end loop; -- 循环结束
    dbms_output.put_line('game over');
    end;
     
    2、while
    declare
    v_count number(2) := 0;
    begin
    while v_count < 10 loop
    -- 当v_count 小于10 执行循环
    v_count := v_count + 1;
    dbms_output.put_line(v_count);
    end loop;
    dbms_output.put_line('game over');
    end;
     
    3、for
    declare
    v_count number(2) := 0; -- 此值对for 循环执行的次数没有影响
    begin
    for v_count in 1 .. 10 loop
    -- 此v_count 变量不是上面声明的变量,循环10次
    dbms_output.put_line(v_count);
    end loop;
    for v_count in reverse 1 .. 10 loop
    --反序输出
    dbms_output.put_line(v_count);
    end loop;
    dbms_output.put_line('game over');
    end;
     
    4、goto
    declare
    v_count number(2) := 0;
    begin
    for v_count in 1 .. 10 loop
    dbms_output.put_line(v_count);
    end loop;
    for v_count in reverse 1 .. 10 loop
    dbms_output.put_line(v_count);
    if v_count = 5 then
    goto endofloop;-- 跳至循环体外标签处执行,循环结束
    end if;
    end loop;
    <<endofloop>>
    dbms_output.put_line('game over');-- 此处必须要有语句可以执行,若没有也要写 'null;'
    end;

    四、关于异常 Exception

    预定义异常:

     declare
    v_id    t_12580_o2o_good.id%type := &id;
    v_sales t_12580_o2o_good.goods_sales%type;
    begin
    select goods_sales into v_sales from t_12580_o2o_good where id = v_id;
    dbms_output.put_line('the sales is :' || v_sales);
    exception
    when no_data_found then
    dbms_output.put_line('no data found!');
    when too_many_rows then
    dbms_output.put_line('to many rows!');
    when others then
    dbms_output.put_line(sqlcode || ',' || sqlerrm);
    end;
     

    非预定义异常
    01declare
    02v_id t_12580_o2o_good.id%type := &id;
    03no_result exception;
    04begin
    05update t_12580_o2o_goodset goods_sales = 1 where id = v_id;
    06if sql%notfound then
    07raise no_result;
    08end if;
    09exception
    10when no_resultthen
    11dbms_output.put_line('no data be update');
    12when others then
    13dbms_output.put_line(sqlcode || '-----'|| sqlerrm);
    14end;

    五、关于游标:

    --显式游标:
    01declare
    02v_id    table_good.id%type;
    03v_sales table_good.goods_sales%type;
    04cursor c_cursoris
    05select id, goods_salesfrom table_good whereid between 2000 and 3000;
    06begin
    07open c_cursor;-- 打开游标
    08fetch c_cursor
    09into v_id, v_sales;--获取数据
    10while c_cursor%found loop
    11-- 当游标里有数据就执行下面的打印操作
    12dbms_output.put_line(v_id || ' sales is : '|| v_sales);
    13fetch c_cursor
    14into v_id, v_sales;
    15end loop;
    16close c_cursor;
    17end;
     
    ------------------------------------------------------------------------
     
    01declare
    02-- 记录类型变量,在游标中存放所有列的数据。
    03o2o_record_type table_good%rowtype;
    04cursor v_cursor(v_sales table_good.goods_sales%type)is select * from table_good where goods_sales > v_sales;
    05begin
    06if v_cursor%isopen then
    07fetch v_cursorinto o2o_record_type;
    08else  openv_cursor(1000); -- 若没有打开,就先打开,再取数据
    09fetch v_cursorinto o2o_record_type;
    10end if;
    11while v_cursor%found loop
    12dbms_output.put_line(o2o_record_type.id ||' sales is: ' ||
    13o2o_record_type.goods_sales);
    14fetch v_cursor
    15into o2o_record_type;
    16end loop;
    17dbms_output.put_line(v_cursor%rowcount); -- 游标里的数据的行数
    18close v_cursor;
    19end;
     
    --隐式游标
    1declare
    2v_deptno emp.deptno%type := &p_deptno;begin
    3delete fromemp where deptno = v_deptno;   -- 删除 emp 表中对应部门号下的员工信息
    4if sql%notfound then            -- 如果对应部门没有员工,则删除 dept 表中对应的部门号,
    5delete fromdept where deptno = v_deptno;
    6commit;
    7end if;
    8rollback;      -- 如果对应部门下有员工,则回滚至删除前
    9end;
     
    --给销量低于100的商品增加销售基数100
    01declare
    02v_id       table_good.id%type;
    03v_sal      table_good.goods_sales%type;
    04v_sal_base table_good.goods_sales_base%type;
    05cursor c_cursoris
    06select id, goods_salesfrom table_good whereid between 1000 and 2000;
    07begin
    08open c_cursor;
    09loop
    10fetch c_cursor
    11into v_id, v_sal;
    12exit whenc_cursor%notfound;
    13if v_sal <= 100 then
    14v_sal_base := 100;
    15update table_good
    16set goods_sales_base = v_sal_base
    17where id = v_id;
    18dbms_output.put_line(v_id || '''s goods_sales_base has been update! the new goods_sales_base is: '|| v_sal_base);
    19end if;
    20end loop;
    21dbms_output.put_line(c_cursor%rowcount);
    22close c_cursor;
    23end;
     
    -- FOR 循环操作游标:
    01declare
    02cursor c_cursoris
    03select id,good_title,goods_salesfrom table_good whereid between 2000 and 3000;
    04begin
    05for v_recordin c_cursor loop
    06-- 隐式地打开游标,取数据
    07if v_record.goods_sales <= 1200 then
    08update table_goodset goods_sales_base = 100where id = v_record.id;
    09dbms_output.put_line(v_record.good_title ||'''s sales_base has update!');
    10end if;
    11-- 隐式地关闭游标
    12end loop;
    13end;
    14-- 带参数的游标:
    15declare
    16cursor c_cursor(v_status varchar2default '3')is
    17select id, goods_sales, good_title
    18from table_good
    19where status = v_statusand id between2000 and 3000;
    20begin
    21for c_rec in c_cursor(30) loop
    22dbms_output.put_line(c_rec.id || ','|| c_rec.good_title || ','||
    23c_rec.goods_sales);
    24end loop;
    25for c_rec in c_cursor loop
    26-- 此处将会用默认值 20;
    27dbms_output.put_line(c_rec.id || ','|| c_rec.good_title || ','||
    28c_rec.goods_sales);
    29end loop;
    30end;
    展开全文
  • 存储过程和函数的区别就是,函数只能即用即配,适合简短配置的,但不适合批量操作和后台写入等,这是我对存储过程的简单理解,我认为Oracle存储过程和函数就类似于java,python等面向对象语言的类和函数 我们要处理...

    上一篇我们已经介绍了如何通过ODBC导入表数据ODBC导入数据,本文则通过导入的这张表分享一下存储过程入门

    存储过程和函数的区别就是,函数只能即用即配,适合简短配置的,但不适合批量操作和后台写入等,这是我对存储过程的简单理解,我认为Oracle存储过程和函数就类似于java,python等面向对象语言的类和函数

    我们要处理一批表数据,把excel里的数据根据表名写成一个个的sql脚本,实现这个过程其实很简单,用shell,java的io,oracle的函数等等都可以,但本文用一个更能体现逼格的方法:存储过程来写,拿一张表来举例

    这是表的数据,懒得截图了,用的上一个博客的图,但数据还是这个数据

    1.我们要读取这个表TB2实现成这样的脚本

    INSERT OVERWRITE TABLE OWNER1.TABLE1 PARTITION (rfq='#RQ')
    SELECT COL1,COL2,...,GETDATE() COLN
      FROM OWNER2.TABLE1
     WHERE rfq='#RQ';

    2.现在开始写存储过程

    2.1.第一步是声明变量

    DECLARE                       --声明变量的关键字
      colname VARCHAR2(500);        --变量以及数据类型
         bzbm VARCHAR2(300);
            m        number;
           m2        number;
          ybm VARCHAR2(300);
      fhandle utl_file.file_type;  --fhandle是文件自定义名,utl_file.file_type是属性,是关键字
       CURSOR C_SAL IS
       SELECT DISTINCT YBM FROM tb2;   --CURSOR XXX IS  SELECT * FROM TABLE_NAME;是把选择出来的值赋予XXX
    BEGIN                         
    ...
    END    --BEGIN和END是脚本过程,流程关键字必备

    第二步:开始写入脚本正文

    BEGIN
        fhandle := utl_file.fopen('EXP_DIR', 'XXX.sql', 'w',32767);
        --:=是赋值,fopen可理解为java的io
        FOR V_SAL IN C_SAL LOOP
            --每个语言都有的for循环:FOR xxx in yyy LOOP
            SELECT distinct bzbm INTO bzbm FROM tb2 WHERE YBM = V_SAL.YBM; 
            --SELECT XXX INTO Y FROM赋值Y
            utl_file.PUT(fhandle,'INSERT OVERWRITE TABLE OWNER1.table1')
            --目标主题名,PUT是在这个文件按顺序写入东西
            utl_file.PUT(fhandle, bzbm);
            utl_file.PUT(fhandle, 'PARTITION ( rfq=''20170000'')SELECT  ');
            SELECT max(yxh) into m from tb2 WHERE YBM = V_SAL.YBM;
            FOR i IN 1 .. m LOOP
                SELECT yzdm into colname from tb2 WHERE YBM = V_SAL.YBM and yxh = i;
                if i = 1 then
                    utl_file.PUT(fhandle, colname);
                else
                    utl_file.PUT(fhandle, ',' || colname);
                end if;
            end loop;
            --在存储过程中每个if判断和每个loop循环都要end,类似于VB语言
            utl_file.new_line( fhandle );
            --new_line 增加行终止符,也就是换行的意思
            utl_file.PUT(fhandle, '  FROM OWNER2.');    ---源主题名  
            SELECT distinct jcztbm INTO ybm FROM tb2 WHERE YBM = V_SAL.YBM;   
            utl_file.PUT(fhandle, YBM);  --源表名
            utl_file.PUT_LINE(fhandle, ' where rfq=''20170000'';'); --初始化分区
      	END LOOP;
        utl_file.fclose(fhandle);    --记得fclose文件
    END;

    至此一个利用utl_file包写的存储过程就搞定了,

    fhandle := utl_file.fopen('EXP_DIR', 'XXX.sql', 'w',32767);   --存储过程调用

    这个是把文件输出到EXP_DIR文件夹里,所以在运行该存储过程之前记得先创建该文件夹,并且赋予权限,命令如下

    create directory exp_dir as 'home/oracle/directory'; --oracle安装windows环境下可以用d:/directory等
    grant create any directory to public;

     

    展开全文
  • oracle存储过程实例(一)

    千次阅读 2019-04-11 15:07:27
    create or replace procedure P_Problem_stock is /用于处理验收单税金和不含税计算时四舍五入查1分钱或者税金计算不正确的问题问题处理...然后执行该存储过程。此存储过程不含调价内容/ p_pk_mt_stockin varchar2(...
  • oracle存储过程实例(二)

    千次阅读 2019-04-11 15:10:01
    create or replace procedure P_PROBLEM_ZZCJS_gj(pksettleh in varchar2) is begin –更新归集子表,根据周转材主表主键 update bpm_cm_realcost_b b ...(select THISMONEY from bpm_circul_settle where dr = 0 ...
  • CREATE OR REPLACE PROCEDURE p_updete_gs is --仅供参考 i_jdid varchar(32); i_ryid varchar(32); cursor cur is --游标 给查询... select c.jdid jdid, t.ryid rybh from t_zcj_rctj t,t_zj_jd c where t.r...
  • Oracle 存储过程简单实例

    千次阅读 2020-07-24 15:23:01
    Oracle 存储过程简单实例 1.数据表: 2.存储过程的目的 tt_user 表中sex为0的数据插入到tt_user2中 3.存储过程语句 create or replace procedure user1to2 as nuserid number; nusername varchar2(50); npassword ...
  • 最近在接触数据库的有关知识,因为水平有限,对数据库方面的一些知识缺乏了解,这次遇见的主要是 数据库的存储过程,根据公司项目需求,将oracle存储过程切换为mysql的存储过程,首先oracle存储过程与mysql的...
  • Oracle创建存储过程实例

    千次阅读 2018-04-24 23:15:05
    认识存储过程和函数 存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中...
  • 本文实例讲述了oracle 存储过程、函数和触发器用法。分享给大家供大家参考,具体如下: 一、存储过程存储函数 指存储在数据库中供所有用户程序调用的子程序叫存储过程存储函数。 创建存储过程 用CREATE ...
  • 有俩种方法: 一种是声明... –方法1 ) begin open cur_arg for select * from tablename; end 调用 代码如下: declare cur_calling sys_refcursor; begin p_temp_procedure(cur_calling); –这样这个游标就有值了 for
  • oracle存储过程初学实例

    万次阅读 多人点赞 2017-08-04 11:05:00
    认识存储过程和函数  存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中...
  • Oracle存储过程

    万次阅读 多人点赞 2019-07-01 14:52:17
    Oracle存储过程详解 procedure 1.创建Oracle存储过程 prodedure create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end ...
  • 1、创建好mybatis的Mapper文件 在接口文件中声明好接口以及返回的参数类型 Map&...2、Mapper.xml文件中的对应调用存储过程的方式如下 &amp;amp;lt;select id=&amp;quot;getGoodsInf
  • (1)对于表A和表B两个表结构完全相同的话〔字段个数,相应字段的类型等等〕,可以使用 insert INTO B select * FROM A; insert INTO B(field1,field2,field3) select A.field1,A.field2,A.field3 from A; (2) ...
  • 使用Java调用Oracle数据库的存储过程实例时间:2009-08-19 18:02:43来源:网络 作者:未知 点击:984次 一:无返回值的存储过程 1、建立存储过程 CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN ...
  • oracle存储过程代码实例

    万次阅读 2006-06-22 11:57:00
    1、用来插入大量测试数据的存储过程CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST(ST_NUM IN NUMBER,ED_NUM IN NUMBER)ISBEGINdeclare i number;beginFOR i IN ST_NUM..ED_NUM LOOPINSERT INTO tb va
  • oracle存储过程批量插入和批量删除实例
  • } } } oracle存储过程代码 create or replace procedure get_username(v_id in number,v_username outvarchar2) as begin select username into v_username from tab_user where id = v_id; --变量赋值 ...
  • 本文实例讲述了Oracle存储过程游标用法。分享给大家供大家参考,具体如下: 使用游标的5个步骤 1、声明一些变量用于保存select语句返回的指 2、声明游标,并指定select 语句 3、打开游标 4、从游标中获取记录 5、...
  • oracle存储过程语法

    千次阅读 2018-10-29 16:39:33
    前两天无意见看见了一个非常适合学习Oracle附上链接:...Oracle存储过程基本语法 存储过程   1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END;  行1:   CREAT...
  • Oracle 存储过程学习

    万次阅读 2016-05-20 21:57:24
    Oracle存储过程基础知识 1 Oracle存储过程的基本语法 2 关于Oracle存储过程的若干问题备忘 4 1. 在Oracle中,数据表别名不能加as。 4 2. 在存储过程中,select某一字段时,后面必须紧跟into,如果select整个...
  • oracle 存储过程

    千次阅读 2011-07-27 00:33:55
    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。Oracle存储过程可以有无参数存储过程和带参数存储过程。一、无参数存储过程语法: create or replace procedure noPramePro as ...; begin .....
  • 本例的逻辑比较简单,用一句sql就可以实现,这里只是演示存储过程的基本语法。 创建包package: create or replace package max_type as type max_cursor is ref cursor; end; 创建存储过程...
  • 之前对Oracle的接触都未牵涉到这一块的,刚开始确实不知道怎么处理...1、oracle存储过程如下:   CREATE OR REPLACE PROCEDURE UP_dzSURFACE_EBGJSY( TKALA VARCHAR2 , RC1 out PK_Var.RC ) AS  sSQL varch

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 67,213
精华内容 26,885
关键字:

oracle存储过程实例select