| ); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger"); conn.setAutoCommit(false); conn=DBConnection.getDBConnection().getConnection(); String spName="{call sp_add_emp1(?,?,?,?)}"; CallableStatement cstmt=conn.prepareCall(spName); cstmt.setInt(1, 2); cstmt.setString(2, "wwww"); cstmt.setInt(3, 1); cstmt.setString(4, "qwqwq"); cstmt.executeUpdate(); conn.close(); Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger"); conn.setAutoCommit(false); conn=DBConnection.getDBConnection().getConnection(); String spName="{call sp_add_emp1(?,?,?,?)}"; CallableStatement cstmt=conn.prepareCall(spName); cstmt.setInt(1, 2); cstmt.setString(2, "wwww"); cstmt.setInt(3, 1); cstmt.setString(4, "qwqwq"); cstmt.executeUpdate(); conn.close();
2.需求同上, 只是返回该部门的员工总数。（有一个返回值）
--创建存储过程如下
- CREATE OR REPLACE PROCEDURE sp_add_emp2(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE,
-
- num out number
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
- SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
- IF(num1=0) THEN
- INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
- END IF;
- SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
- IF(num2=0)THEN
- INSERT INTO emp(empno,ename,deptno)
本新闻共 6页,当前在第 2页 1 2 3 4 5 6
|
