Oracle存储过程、自定义函数、和触发器 示例

-----一、创建存储过程关键字是：procedure

---1、创建添加商品分类数据的存储过程proc_addproducttype

create or replace procedure proc_addproducttype
 (tname in producttype.typename%type)--in是输入参数关键字，tname是参数名，in后面是 
   --参数类型，这里是引用表producttype中 typename字段的类型
 is
 begin
   insert intoproducttype(typeid,typename)values(seq_producttype.nextval,tname);
 end;--seq_producttype是创建的序列 名，此处实现typeid列自增功能
 --调用存储过程proc_addproducttype
begin
  proc_addproducttype('生日鲜花');
end;

--2、创建删除商品分类数据，并返回刚刚删除的分类名称的存储过程proc_delproducttype
create or replace procedureproc_delproducttype
 (tid in producttype.typeid%type,tname outproducttype.typename%type)
    --out为输出参数标志
 is
 begin
   select typename into tnamefrom producttype where typeid=tid;
    --找到要删除typeid对应的typename并赋给输出参数tname      
   delete from producttype wheretypeid=tid;
   exception when no_data_foundthen tname:='未找到数据！';
    --发生数据未找到异常时，给输出参数tname赋值
 end;
   --调用存储过程proc_delproducttype
 declare tnproducttype.typename%type;--定义变量，接收存储过程返回的参数
 begin
  proc_delproducttype(3,tn);
  dbms_output.put_line(tn);--输出打印变量tn
 end;

 

 --3、Java中使用存储过程的

//调用数据库中的存储过程

                  cs =conn.prepareCall("{callpro_deleteproducttype(?,?)}");//pro_deleteproducttype是//要调用的存储过程的名字,第一个参数是整形输入参数，第二个参数是输出参数
          //设置存储过程的输入参数
         cs.setInt(1, 24);
         //设置存储过程返回值类型，并执行
         cs.registerOutParameter(2,java.sql.Types.VARCHAR);
         cs.execute();
         //接收返回值，并打印
         String str = cs.getString(2);
                  System.out.println(str);

二．自定义函数的关键字:function

--例一 输出拼接课程表course的id

create or replacefunctionfun_str   --无参数的，注意后面不能加括号

returnvarchar2   --  指明返回类型返回值return 显示返回，注意这里这里并没有指明类型的长度

is strvarchar2(50);       --  is 后声明了一个str变量用于函数得返回，注意这里变量没有用 declare声明

begin

  fori in (select cid from course) loop

   str:=str||i.cid||',';   //使用||对字符进行拼接

  endloop;

 returnstr;         //返回str

end;

--自定义函数的调用

begin

 dbms_output.put_line(fun_str);--无参函数后面没有括号

end;

java中使用自定义函数

CallableStatementfun_call=conn.prepareCall("{?=call fun_str}");

fun_call.registerOutParameter(1,java.sql.Types.VARCHAR);

fun_call.execute();

Stringfunstr=fun_call.getString(1);

System.out.println(funstr);

 

--例二转换输出日期数据

带参数的自定义函数也使用in关键字表识，但是没有out关键字

create or replacefunctionfun_date(td in date) --函数中的参数只能是输入参数（没有输出参数），用in标示，

returnvarchar2    

is

strvarchar2(50);    声明变量 str

begin             为str赋值

 select to_char(td,'yyyy"年"mm"月"dd"日" hh24:mi:ss') into str fromdual;

 return str;

end;

--调用日期

selectcid,cname,tid,fun_date(ctime) as 日期 from course;

三．触发器：关键字 trigger

--触发器，为课程表course添加数据触发器

create or replacetriggertrig_course_add

before insert [orupdate , delete ]  //表示在插入之前执行

oncourse        on 后接的是表明，置顶触发的表

for eachrow    每一行改变时都执行触发器

begin

 -- :new.cid 表示新的cid的值将序列的自增值赋给新的cid

 select seq_course.nextval into :new.cid from dual;

 --其实上面一句话，也可用 :new.cid:=seq_course.nextval代替

end;

--在重新插入的时候就不需要在values中指定序列了

insert into course(cname,tid) values('体育',3);

触发器都是被隐式调用

 

--创建触发器，为课程表添加增、删、改操作时记录到日志表的功能

createorreplacetrigger trig_course_log

afterinsertorupdateordelete

on course

foreachrow    dblog为日志表

declare str1dblog.logmsg%type; str2g.logmsg%type;

 

 

begin

   if insertingthen  如果是插入

      select :new.cnameinto str1 from dual;

      str1:='添加了课程:'||str1;

   elsif updating then  如果是修改

      select :old.cnameinto str1 from dual;

      select :new.cnameinto str2 from dual;

      str1:=str1||':课程改为:'||str2;

     --dbms_output.put_line(str1);

   elsif deleting then  如果是删除

      select :old.cnameinto str1 from dual;

      str1:='删除了课程:'||str1;

    endif;

 insertintodblogvalues (seq_dblog.nextval,'course',str1); 插入日志信息

end;