存储过程
它是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;
orcal的存储过程加上触发器,源码
最新推荐文章于 2023-05-16 23:38:37 发布