最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
oracle Dbeaver存储过程语法代码示例解析
时间:2022-06-29 09:22:49 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下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;
{call delTeacher(#{arg0})}
{call delStudentOfTeacher(#{arg0}) }
测试
@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;
call addTeacher(#{arg0},#{arg1},#{arg2})
改
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;
call updateTeacher(#{arg0},#{arg1},#{arg2});
学生增删改
增
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;
call addStudent(#{arg0},#{arg2},#{arg1},#{arg3},#{arg4},#{arg5})
删
CREATE OR REPLACE PROCEDURE delStudent(mySID IN varchar2) IS BEGIN DELETE FROM student WHERE SID=mySID; END delStudent;
call delStudent(#{arg0})
改
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;
相关文章
- 蚂蚁庄园答题今日答案2024年10月26日 11-02
- 以闪亮之名时装染色收藏怎么样 时装染色收藏功能介绍 11-02
- 以闪亮之名梦洲笙歌复刻怎么玩 梦洲笙歌复刻活动介绍 11-02
- 以闪亮之名黯影分解商店怎么样 以闪亮之名无烬之誓分解商店介绍 11-02
- 以闪亮之名黯影分解商店怎么样 11-02
- 以闪亮之名无烬之誓有什么更新 11-02