/*[scott/tiger]账户做如下题目:
把2000当做输入参数,传进来,返回参数是低于这个数值的员工数。
把每个部门中,低于2000工资的员工,增加到2000,如果大于2000增加10%
并且把低于2000的员工数返回。
*/
create or replace
Procedure
ZKJMY_PROC(sa in number,num out number)
as
Cursor Mycur Is Select * From Emp;
Newsal Number;
En Emp%Rowtype;
Begin
/*select*/
Select Count(*) Into Num From Emp Where Sal
/* for */
For En In Mycur Loop
If(en.Sal
Newsal:=sa;
else
Newsal:=en.Sal*1.1;
End If;
/*update*/
update emp set sal =newsal where empno =en.empno;
end loop;
END ZKJMY_PROC;
下面是Java程序调用的方法
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.oracore.OracleType;
public class CallProcedure {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@192.168.40.2:1521:stu";
String user = "scott";
String password = "tiger";
try {
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection(url, user, password);
/* 调用存储过程 */
CallableStatement cs = conn.prepareCall("{call ZKJMy_Proc(?,?)}");
/* test */
int newsal = 2000;
/* 设置输入参数sa */
cs.setInt(1, newsal);
/* 设置输出参数num */
cs.registerOutParameter(2, java.sql.Types.TINYINT);
// 执行,存储过程。
cs.execute();
int res = cs.getInt(2);
System.out.println("salary在" + newsal + "元以下的人数有:" + res);
cs.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}