| ); conn.setAutoCommit(false); conn=DBConnection.getDBConnection().getConnection(); String spName="{call sp_add_emp2(?,?,?,?,?)}"; CallableStatement cstmt=conn.prepareCall(spName); cstmt.setInt(1,1111); cstmt.setString(2, "qqqq"); cstmt.setInt(3, 50); cstmt.setString(4, "pppp"); cstmt.registerOutParameter(5, java.sql.Types.INTEGER); cstmt.executeUpdate(); int i = cstmt.getInt(5); System.out.println(i); cstmt.close(); conn.close();
3.需求同上, 并返回该部门的员工信息(工号和姓名)。（返回一个游标）
--创建存储过程如下
Sql代码
--1.建包 - CREATE OR REPLACE PACKAGE my_pak AS
- TYPE my_cus IS REF CURSOR ;
- END my_pak;
--2.写存储返回过程
- CREATE OR REPLACE PROCEDURE sp_add_emp3(
- v_empno emp.empno%TYPE,
- v_ename emp.ename%TYPE,
- v_deptno dept.deptno%TYPE,
- v_dname dept.dname%TYPE,
-
- p_cus OUT my_pak.my_cus
- )AS
- num1 NUMBER;
- num2 NUMBER;
- BEGIN
-
- OPEN p_cus FOR select empno,ename into v_empno,v_ename from emp where deptno =v_deptno
- 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) VALUES(v_empno,v_ename,v_deptno);
本新闻共 6页,当前在第 4页 1 2 3 4 5 6
|
