- 浏览: 54790 次
- 性别:
- 来自: 厦门
最新评论
call 语法格式{call 存储过程名(参数列表)}
业务实例:
1.添加员工,如果指定部门不存在,则先添加部门信息,再添加员工(无返回值)
--创建存储过程如下
Sql代码 JDBC调用存储过程
1. CREATE OR REPLACE PROCEDURE sp_add_emp1(
2. v_empno emp.empno%TYPE,
3. v_ename emp.ename%TYPE,
4. v_deptno dept.deptno%TYPE,
5. v_dname dept.dname%TYPE
6. )AS
7. num1 NUMBER;
8. num2 NUMBER;
9. BEGIN
10. SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
11. IF(num1=0) THEN
12. INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
13. END IF;
14. SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
15. IF(num2=0)THEN
16. INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
17. ELSE
18. raise_application_error(-202021,'员工id 重复!!!');
19. END IF;
20. commit;
21.
22. END;
CREATE OR REPLACE PROCEDURE sp_add_emp1( v_empno emp.empno%TYPE, v_ename emp.ename%TYPE, v_deptno dept.deptno%TYPE, v_dname dept.dname%TYPE)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) VALUES(v_empno,v_ename,v_deptno);ELSE raise_application_error(-202021,'员工id 重复!!!');END IF; commit;END;
Java中调用代码1:
Java代码 JDBC调用存储过程
1. Class.forName("oracle.jdbc.driver.OracleDriver");
2. conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
3. conn.setAutoCommit(false);
4.
5. conn=DBConnection.getDBConnection().getConnection();
6. String spName="{call sp_add_emp1(?,?,?,?)}";
7. CallableStatement cstmt=conn.prepareCall(spName);
8. cstmt.setInt(1, 2);
9. cstmt.setString(2, "wwww");
10. cstmt.setInt(3, 1);
11. cstmt.setString(4, "qwqwq");
12. cstmt.executeUpdate();
13.
14. 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.需求同上, 只是返回该部门的员工总数。(有一个返回值)
--创建存储过程如下
Sql代码 JDBC调用存储过程
1. CREATE OR REPLACE PROCEDURE sp_add_emp2(
2. v_empno emp.empno%TYPE,
3. v_ename emp.ename%TYPE,
4. v_deptno dept.deptno%TYPE,
5. v_dname dept.dname%TYPE,
6.
7. num out number
8. )AS
9. num1 NUMBER;
10. num2 NUMBER;
11. BEGIN
12. SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
13. IF(num1=0) THEN
14. INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
15. END IF;
16. SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
17. IF(num2=0)THEN
18. INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
19. ELSE
20. raise_application_error(-202021,'员工id 重复!!!');
21. END IF;
22.
23. num:=num1;
24. commit;
25.
26. END;
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) VALUES(v_empno,v_ename,v_deptno);ELSE raise_application_error(-202021,'员工id 重复!!!');END IF; num:=num1; commit;END;
Java中调用代码2:
Java代码 JDBC调用存储过程
1. Class.forName("oracle.jdbc.driver.OracleDriver");
2. conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
3. conn.setAutoCommit(false);
4.
5. conn=DBConnection.getDBConnection().getConnection();
6.
7. String spName="{call sp_add_emp2(?,?,?,?,?)}";
8. CallableStatement cstmt=conn.prepareCall(spName);
9. cstmt.setInt(1,1111);
10. cstmt.setString(2, "qqqq");
11. cstmt.setInt(3, 50);
12. cstmt.setString(4, "pppp");
13. cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
14. cstmt.executeUpdate();
15. int i = cstmt.getInt(5);
16. System.out.println(i);
17. cstmt.close();
18. 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_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代码 JDBC调用存储过程
1. --1.建包
2.
3. CREATE OR REPLACE PACKAGE my_pak AS
4. TYPE my_cus IS REF CURSOR ;
5. END my_pak;
6.
7. --2.写存储返回过程
8.
9. CREATE OR REPLACE PROCEDURE sp_add_emp3(
10. v_empno emp.empno%TYPE,
11. v_ename emp.ename%TYPE,
12. v_deptno dept.deptno%TYPE,
13. v_dname dept.dname%TYPE,
14.
15. p_cus OUT my_pak.my_cus
16. )AS
17. num1 NUMBER;
18. num2 NUMBER;
19. BEGIN
20.
21. OPEN p_cus FOR select empno,ename into v_empno,v_ename from emp where deptno =v_deptno
22. SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;
23. IF(num1=0) THEN
24. INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);
25. END IF;
26. SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;
27. IF(num2=0)THEN
28. INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);
29. ELSE
30. raise_application_error(-202021,'员工id 重复!!!');
31. END IF;
32. commit;
33.
34. END;
--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;BEGINOPEN 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);ELSE raise_application_error(-202021,'员工id 重复!!!');END IF; commit;END;
Java中调用代码3:
Java代码 JDBC调用存储过程
1. Class.forName("oracle.jdbc.driver.OracleDriver");
2. conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
3. conn.setAutoCommit(false);
4.
5. conn=DBConnection.getDBConnection().getConnection();
6.
7. String spName="{call sp_add_emp2(?,?,?,?,?)}";
8. CallableStatement cstmt=conn.prepareCall(spName);
9. cstmt.setInt(1,1111);
10. cstmt.setString(2, "qqqq");
11. cstmt.setInt(3, 50);
12. cstmt.setString(4, "pppp");
13. cstmt.registerOutParameter(5, java.sql.Types.ORACLETYPE);
14. cstmt.executeUpdate();
15. int i = cstmt.getInt(5);
16. System.out.println(i);
17. cstmt.close();
18. 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_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.ORACLETYPE); cstmt.executeUpdate(); int i = cstmt.getInt(5); System.out.println(i); cstmt.close(); conn.close();
发表评论
-
servlet jsp 的生命周期
2012-02-09 15:20 1442servlet的生命周期分为以下几个步骤, 第一,加载并实例化 ... -
反射内部类
2011-11-25 18:30 920package test; public class ... -
Date和Calendar类
2010-06-23 08:58 8041、具体类(和抽象类相对)java.util.Date 2、 ... -
Maven2和ivy比较
2010-06-22 13:14 1370Maven 2和Ivy常被放在一起对比,但实际上两者是不同 ... -
JAVA连接DB2注意事项
2010-05-15 23:21 1174使用Class.forName("COM.ibm. ... -
JAVA的MD5加密
2010-01-19 20:44 938MD5的全称是Message-Digest ... -
Web.xml的解析
2010-01-18 13:32 1025web.xml元素介绍 web.xml首先是肯定要包含它的 ... -
JAVA多线程
2010-01-18 13:23 692java.lang.Thread 和 java.lang.R ... -
Java常用工具类(字符编码,时间等)
2010-01-09 16:20 1392/** * 将UTF编码的字符串转化为GB2312 ... -
java读取配置文件
2010-01-09 16:11 8321、随机id的产生如何实现 引入如下的包: impo ... -
UNICODE与汉字编码互转
2010-01-09 16:08 1601为了避免在浏览器中传输数据的时候出现中文乱码,我们可以将内 ... -
Eclipse中JVM内存设置
2010-01-09 16:06 631eclipse.ini内存设置 -vmarg ... -
Hibernate 中 执行 原生 SQL 语句
2010-01-09 16:04 3165用hibernate的executeQuery来执行SQL ... -
hibernate完成oracle存储过程(stored procedures)
2010-01-09 15:59 1139import java.sql.CallableState ... -
oracle 下hibernate3.2 sequence generator 生成id的问题
2010-01-09 15:57 1271@SequenceGenerator(name=&q ... -
hibernate三重关联
2009-12-25 12:15 1072------------------------------- ... -
iframe跨域访问cookie、Sessio
2009-12-23 22:20 19401、IE浏览器iframe跨域丢失Session问题 在开发 ... -
拦截系统关闭信号
2009-12-19 21:31 1045在Java窗口程序中按ctrl+c会强行中止Java程序。点击 ...
相关推荐
jdbc 存储过程 java 程序 JDBC 调用存储过程方法
使用jdbc调用存储过程的代码示例 调用数据库连接池
jdbc调用存储过程 函数例子
jdbc调用存储过程?.avi
使用Oracle数据库实现了jdbc调用存储过程进行增加数据、删除数据、修改数据、查询全部数据、查询单个数据
PLSQL jdbc调用存储过程(入门)
JDBC调用存储过程.pdf
Java面试题31.jdbc调用存储过程.mp4
本工程用于JDBC如何实现对存储过程的调用 本工程编码方式:GBK 参考博客网址:http://blog.csdn.net/gaohuanjie/article/details/34422903 须执行的SQL语句: CREATE DATABASE `test`; USE `test`; CREATE TABLE...
jdbc执行存储过程java源码
NULL 博文链接:https://sxpujs.iteye.com/blog/376972
我写的jdbc掉用存储过程的一个例子,还可以大家可以随便看看。
android通过jdbc方式连接mysql,并执行存储过程。连接数据库部分最好新建一个线程来实现和主线程分开。编译之前确定添加了mysql-connector-java-5.0.8-bin库否则会出错
JDBC存储过程的调用
java调用hibernate存储过程详细介绍,各种配置方法,还有与jdbc调用存储过程二者之间的对比。
JDBC调用MySQL5存储过程[文].pdf
JDBC存储过程调用示例,详细描述了存储过程调用的过程。