oracle的存储过程，视图，触发器小例子

建表BB表

create table BB

(

  id            NUMBER,

  short_name    VARCHAR2(500),

  stantding_code VARCHAR2(500),

  plan_value    NUMBER,

  all_invest    NUMBER,

  base_id       NUMBER,

  tj_base_id    NUMBER

);

插入数据

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (1, '   海阳核废料处置场项目', '2007-033-ZHD-QT-X', 106,10000, 111111606268, 111111606268);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (2, '   江西上犹仙鹅塘二期50兆瓦风电场项目','2015-097-JXF-ZSf-X', 200, 42912, 211113037230, 211113037230);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (3, '   安徽田集二期扩建项目', '2003-011-SHD-HD-K', 3000,468415, 111111198253, 111111198253);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (6, '   广西白龙及桂东核电项目', '2003-019-ZHD-ND-X', 13049,3500000, 111111198049, 111111198049);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (7, '   吉林赤松核电项目', '2003-020-CPI-ND-X', 2100,3500000, 211111638573, 111111198089);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (8, '   贵州白市水电项目', '2003-021-WLD-SD-X', 31000,698800, 111111198274, 111111198274);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (9, '   青海积石峡水电站项目', '2004-013-HHS-SD-X', 18974,721737, 111111198113, 111111198113);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (10, '   青海班多水电站项目', '2004-015-HHS-SD-X', 0, 259699,111111198103, 111111198103);

insert into BB (id, short_name, stantding_code, plan_value,all_invest, base_id, tj_base_id)

values (11, '   重庆涪陵核电项目', '2004-017-CPI-ND-X', 5000, 0,111111198067, 111111198067);

建存储过程

CREATEORREPLACEPROCEDURE EMP

AS

V_TOTALNUMBER(10);

BEGIN

 SELECTCOUNT(*)INTO V_TOTAL FROM BB;

 DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);

END;

运行

Set serveroutputon;

Executeemp();

2存储过程

createorreplaceprocedure zonghechaxun (

                            isal in BB.plan_value%type,

                            sjob invarchar)

  as icount number;

   begin

       selectcount(*)into icount from BB where plan_value > isal and stantding_code =sjob;

       DBMS_OUTPUT.PUT_LINE('符合条件的记录有 '|| icount ||'条');

  exception

      when too_many_rows then

      DBMS_OUTPUT.PUT_LINE('返回值多于1行');

      whenothersthen

      DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');

  end;

运行

Setserveroutput on;

Execute zonghechaxun(1,’2003-011-SHD-HD-K’);

创建视图

CREATEOR REPLACE VIEW SBB_VIEW AS

SELECTplan_value,AVG(ALL_INVEST) AVERAGE_SCORE FROM BB

GROUPBY plan_value;

创建触发器

CREATEOR REPLACE TRIGGER SBB_VIEW_DELETE

INSTEADOF DELETE ON SBB_VIEW

FOREACH ROW

BEGIN

  DELETE FROM BB WHEREPLAN_VALUE=:OLD.PLAN_VALUE;

ENDSBB_VIEW_DELETE;

运行删除视图中的分组

Delete from BB where plan_value=’0’;

原本视图是不能删除的，加了触发器之后就能删除了。