oracle Dbeaver存储过程语法详解_oracle_

 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 

 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;