oracle Dbeaver存储过程语法详解

可视化工具 dbeaver

基本语法

增

create or replace  procedure  addstudent
is 
begin
	insert into student values(6,2,5,'小陈',22,0);
end addstudent; 

call addstudent();

drop  procedure getstudent 
select * from student

删

create or replace  procedure  delstudent
is 
begin
	delete from student where id='6';
end delstudent; 

call delstudent();

drop  procedure delstudent 
select * from student

改

create or replace  procedure  updatestudent
is 
begin
	update   student set  age=25 where id='5';
end updatestudent; 

call updatestudent();

drop  procedure updatestudent 
select * from student

单个查询

create or replace  procedure  getstudentcount
(studentcount out number)
is 
begin
	select   count(*) into studentcount from student;
end getstudentcount; 

declare
studentcount number(38);
begin
	getstudentcount(studentcount);
dbms_output.put_line(studentcount);
end;

drop  procedure getstudentcount 
select * from student

多行查询

--定义存储过程，返回游标
create or replace procedure  getallstudent(resule out sys_refcursor) is  --返回游标
begin 
	open resule for select * from student;
end;

--查询存储过程
declare
	cur sys_refcursor;  --游标
	result_row student%rowtype;  
begin
	getallstudent(cur);
	loop
		fetch cur into result_row ;
		exit when cur%notfound;
		dbms_output.put_line('id: '||result_row.id||' tid: '||result_row.tid||'sid: '||result_row.sid||' sname: '||result_row.sname||' age: '||result_row.age||' sex: '||result_row.sex);
	end loop;
	close cur;
end;

drop  procedure getallstudent 
select * from student

springboot中使用

一个student表，一个teacher表

有这样一个业务，删除教师，删除其所有学生

create or replace  procedure  delteacher(mytid in varchar2)is begin	delete from teacher where tid=mytid;end delteacher; 

create or replace  procedure  delstudentofteacher(mytid in varchar2)is begin	delete from student where tid=mytid;end delstudentofteacher; 

<delete id="teacherdelete" parametertype="int">        {call delteacher(#{arg0})}</delete>

<delete id="studentofteacherdelete" >        {call delstudentofteacher(#{arg0}) }</delete>

测试

@test
    void contextloads() {
        teacherservice.deleteteacher(2);
    }

增

create or replace  procedure  addteacher(mytid in varchar2,mytname in varchar2,myage in varchar2)
is 
begin
	insert into teacher values(mytid,mytname,myage);
end addteacher; 

<insert id="teacheradd">
        call addteacher(#{arg0},#{arg1},#{arg2})
</insert>

改

create or replace  procedure  updateteacher(mytname in varchar2,myage in varchar2,mytid in varchar2)
is 
begin
	update   teacher set tname=mytname,age=myage where tid=mytid;
end updateteacher; 

<update id="teacherupdate" >
        call updateteacher(#{arg0},#{arg1},#{arg2});
</update>

学生增删改

增

create or replace  procedure  addstudent(myid in varchar2,mytid in varchar2,mysid in varchar2,mysname in varchar2,myage in number,mysex in varchar2)
is 
begin
	insert into student values(myid,mytid,mysid,mysname,myage,mysex);
end addstudent; 

<insert id="studentadd" >
        call addstudent(#{arg0},#{arg2},#{arg1},#{arg3},#{arg4},#{arg5})
</insert>

删

create or replace  procedure  delstudent(mysid in varchar2)
is 
begin
	delete from student where sid=mysid;
end delstudent; 

<delete id="studentdelete" >
        call delstudent(#{arg0})
</delete>

改

create or replace  procedure  updatestudent(mysid in varchar2,mysname in varchar2,myage in number,mysex in varchar2)
is 
begin
	update   student set  sname=mysname,age=myage,sex=mysex where sid=mysid;
end updatestudent; 

到此这篇关于oracle dbeaver存储过程的文章就介绍到这了,更多相关oracle dbeaver存储过程内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！