oracle Dbeaver存儲過程語法詳解

更新時間：2021年10月18日 10:49:54 作者：_凌晨兩點(diǎn)半_

這篇文章主要介紹了oracle Dbeaver存儲過程語法詳解,本文通過實例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下

可視化工具 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

多行查詢

--定義存儲過程，返回游標(biāo)
CREATE OR REPLACE PROCEDURE  getAllStudent(resule OUT sys_refcursor) IS  --返回游標(biāo)
BEGIN 
	OPEN resule FOR SELECT * FROM student;
END;

--查詢存儲過程
DECLARE
	cur SYS_REFCURSOR;  --游標(biāo)
	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表

有這樣一個業(yè)務(wù)，刪除教師，刪除其所有學(xué)生

在這里插入圖片描述

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>

在這里插入圖片描述

學(xué)生增刪改

增

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;

到此這篇關(guān)于oracle Dbeaver存儲過程的文章就介紹到這了,更多相關(guān)oracle Dbeaver存儲過程內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: