orcal的存储过程加上触发器，源码

存储过程
它是oracle对象，相当于java中的方法，是一些sql语句的集合，它能完成复杂的业务过程处理，从而降低客户端的业务处理，提高效率。
set serveroutput on 

//创建存储过程
create or replace procedure stu_proc
is
declare
name varchar2(20);
begin
name:='算算';
dbms_output.put_line(name);
end;
/

begin   调用存储过程
stu_proc();
end;
/

execut stu_proc();调用存储过程


create or replace procedure stu_proc(name varchar2,sex varchar2,result out varchar2)
is
begin
insert into stu values(stu_id.nextval,name,sex);
result:='成功';
exception
when others then 
result:='失败';
end;


declare
re varchar2(30);
begin
stu_proc('aaa','male',re);
dbms_output.put_line(re);
end;


加游标的存储过程
create or replace procedure stu_proc(name varchar2,sex varchar2,result out varchar2)
is
stuinfo student%rowtype;
stucur sys_refcursor;
begin
open stucur for select * from student where stuname=name;
fetch stucur into stuinfo;
if stucur%notfound
then
insert into stu values(stu_id.nextval,name,sex);
result:='成功';
else
result:='已有此人，注册失败';
end if;
exception
when others then 
result:='失败';
end;

把游标返回给java
drop procedure stu_proc; 删除存储过程
create or replace procedure stu_proce(stucur out sys_refcursor)
is
begin
open stucur for select * from stu;
end;
/

declare
sturow stu%rowtype;
stuc sys_refcursor;
begin
stu_proce(stuc);
fetch stuc into sturow;
loop
dbms_output.put_line(sturow.stuname);
fetch stuc into sturow;
exit when stuc%notfound;
end loop;
end;
/


触发器
触发器是一种自动执行的程序，
//语句级触发器
create or replace trigger stu_trigger
after update 
on student 
declare
name varchar2(20);
begin 
name:='aa';
dbms_output.put_line(name);
end stu_trigger;
/
查看触发器
desc user_tables;
select table_name from user_tables;
desc user_sequences;
select sequence_name from user_sequences;
desc user_triggers;
select trigger_name from user_triggers;

//行级触发器
create or replace trigger stu_trigger
after update 
on student 
for each row//每执行一行执行一次
declare
name varchar2(20);
begin 
name:='aa';
dbms_output.put_line(name);
end stu_trigger;


create or replace trigger stu_trigger
after update 
on student 
for each row//每执行一行执行一次
declare
name varchar2(20);
begin 

dbms_output.put_line(:new.stuname||:old.stuname);
end stu_trigger;