oracle Dbeaver存储过程语法详解

可视化工具 Dbeaver

基本语法

增

CREATE OR REPLACE  PROCEDURE  addStudentIS BEgin	INSERT INTO student values(6,2,5,'小陈',22,0);
    END addStudent;
     call addStudent();
    DROP  procedure getStudent SELECT * From student

删

CREATE OR REPLACE  PROCEDURE  delStudentIS BEGIN	DELETE From student WHERE ID='6';
    END delStudent;
     call delStudent();
    DROP  procedure delStudent SELECT * FROM student

改

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

• 使用Dbeaver远程连接Hive的详细方法
• 详解DBeaver连接MySQL8以上版本以及解决可能遇到的问题
• DBeaver操作数据表的拷贝的实现
• DBeaver一款替代Navicat的数据库可视化工具
• 详解关于Dbeaver的常用操作

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！