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存储过程内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!


数据运维技术 » oracle Dbeaver存储过程语法详解