如何在JDBC中调用存储过程 [英] How to call a stored procedure in JDBC
问题描述
对于家庭作业,我必须创建一个pl/sql存储过程以向数据库中添加常规成员
For homework I have to create a pl/sql stored procedure to add a facutly member to a database
CREATE OR REPLACE PROCEDURE ADDFACULTYDEPTSAL
(facid IN NUMBER,
facname IN VARCHAR,
depID IN NUMBER)
AS
sal NUMBER;
BEGIN
CALCSALDEPT(depID, sal);
IF sal >= 50000
THEN
sal := sal*.9;
ELSE
IF sal >= 30000
THEN
sal := sal*.8;
END IF;
END IF;
INSERT INTO FACULTY(fid, fname, deptid, salary)
VALUES(facid, facname, depID, sal);
END ADDFACULTYDEPTSAL;
要做到这一点,我需要对上述过程进行java调用,我已经很累了:
Having done that, I need to make a java call for said procedure, which I've tired to do with:
Statement stmt = dbConnection.createStatement();
String in;
if(a == 1){
in = "ADDFACULTYDEPTSAL("
+ fid.getText() + "','"
+ fname.getText() + "','"
+ did.getText() + "')";
} else {
in = "ADDFACULTYUNISAL("
+ fid.getText() + "','"
+ fname.getText() + "','"
+ did.getText() + "')";
}
stmt.executeQuery(in);
我在try catch块中有上述内容,该块不断引发错误.我根据在其他网站上看到的内容,对字符串"in"尝试了几种变体: in ="{致电ADDFACULTYDEPSAL ... in =调用ADDFACULTYDEPSAL ...
I have the above in a try catch block that keeps throwing an error. I have tried several variants on the string "in" based on what I saw on other websites: in = "{call ADDFACULTYDEPSAL ... in = "call ADDFACULTYDEPSAL ...
在这里查看: MySQL连接器指南 我还尝试将stmt更改为可调用语句,例如:
looking here: MySQL Connector Guide I also tried changing stmt to a callable statement as such:
CallableStatement stmt;
if(a == 1){
stmt = dbConnection.prepareCall("{call ADDFACULTYDEPTSAL(?,?,?)}");
} else {
stmt = dbConnection.prepareCall("{call ADDFACULTYUNISAL(?,?,?)}");
}
但是,尝试这种方法似乎不起作用,因为我需要向过程中传递两个以上的变量.
However, trying this way doesn't seem to work because I need to pass more than two variables into the procedure.
有人可以告诉我我在做什么错吗?
Can anyone tell me what I'm doing wrong?
推荐答案
您快到了:
String call = (a == 1 ? "{call ADDFACULTYDEPTSAL(?,?,?)}"
: "{call ADDFACULTYUNISAL(?,?,?)}");
try (CallableStatement stmt = dbConnection.prepareCall(call)) {
stmt.setInt(1, Integer.parseInt(fid.getText()));
stmt.setString(2, fname.getText());
stmt.setInt(3, Integer.parseInt(did.getText()));
stmt.execute();
}
这篇关于如何在JDBC中调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!