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

-----

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

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

create or replace

procedure proc_addproducttype

(tname in producttype.typename%type)

--in是输入参数关键字，tname是参数名，in后面是 --参数类型，这里是引用表producttype中typename字段的类型

is

begin

insert into

producttype(typeid,typename)

values(seq_producttype.nextval,tname);

end;--seq_producttype是创建的序列名，此处实现typeid列自增功能

--调用存储过程proc_addproducttype

begin

proc_addproducttype('生日鲜花');

end;

--2、创建删除商品分类数据，并返回刚刚删除的分类名称的存储过程proc_delproducttype

create or replace procedure

proc_delproducttype

(tid in producttype.typeid%type,tname out

producttype.typename%type)

--out为输出参数标志

is

begin

select typename into tname

from producttype where typeid=tid;

--找到要删除typeid对应的typename并赋给输出参数tname delete from producttype where

typeid=tid;

exception when no_data_found

then tname:='未找到数据！';

--发生数据未找到异常时，给输出参数tname赋值

end;

--调用存储过程proc_delproducttype

declare tn

producttype.typename%type;--定义变量，接收存储过程返回的参数

begin

proc_delproducttype(3,tn);

dbms_output.put_line(tn);--输出打印变量tn

end;

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

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

cs =

conn.prepareCall("{call

pro_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 replace

function

fun_str

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

return

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

is str

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

begin

for

i in (select cid from course) loop

str:=str||i.cid||','; //使用

||

对字符进行拼接

end

loop;

return

str; //返回str

end;

--自定义函数的调用

begin

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

end;

java中使用自定义函数

CallableStatement

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

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

fun_call.execute();

String

funstr=fun_call.getString(1);

System.out.println(funstr);

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

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

create or replace

function

fun_date(td in date) --函数中的参数只能是输入参数(没有输出参数)，用in标示，

return

varchar2

is

str

varchar2(50);

声明变量 str

begin

为str赋值

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

dual;

return str;

end;

--调用日期

select

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

三．触发器：关键字 trigger

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

create or replace

trigger

trig_course_add

before insert [or

update , delete ] //表示在插入之前执行

on

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

for each

row

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

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 str1

dblog.logmsg%type; str2

g.logmsg%type;

begin

if inserting

then

如果是插入

select :new.cname

into str1 from dual;

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

elsif updating then

如果是修改

select :old.cname

into str1 from dual;

select :new.cname

into str2 from dual;

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

--

dbms_output.put_line(str1);

elsif deleting then 如果是删除

select :old.cname

into str1 from dual;

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

endif;

insertinto

dblog

values (seq_dblog.nextval,'course',str1);

插入日志信息

end;