这里有新鲜出炉的精品教程,程序狗速度看过来!
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
这篇文章主要介绍了 Oracle 存储过程及调用, 需要的朋友可以参考下
Oracle 存储过程语法
Oracle 的存储过程语法如下:
- create procedure存储过程名称(随便取)is在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量begin执行部分end;
(2)带参数的存储过程语法:
- create procedure存储过程名称(随便取) (变量1数据类型, 变量2数据类型, ..., 变量n数据类型) is在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量begin执行部分end;
(3)带输入、输出参数的存储过程语法:
- create procedure存储过程名称(随便取) (变量1 in (或out)数据类型, 变量2 in (或out)数据类型, ..., 变量n in (或out)数据类型) is在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量begin执行部分end;
注意:用上面的语法创建存储过程时可能会碰到数据库中已经有了同名的存储过程,这样 Oracle 就会弹框报错,说名字已被现有对象使用。解决方法有两种:
方法一:换个存储过程名
方法二:在最开头的 create procedure 之间加上 or replace 关键字,例如:create or replace procedure 存储过程名称。但是这种方法不建议使用,因为这种方法会把之前同名的存储过程替换为你当前写的这个
存储过程案例一:没参数的存储过程
- create replace procedure procedure_1 is begin dbms_output.put_line('procedure_1.......');
- end;
存储过程案例二:带参数的的存储过程
- create procedure procedure_2(v_i number, v_j number) is v_m number(5);
- begin dbms_output.put_line('procedure_2.......');
- v_m: =v_i + v_j;
- dbms_output.put_line(v_i || ' + ' || v_j || ' = ' || v_m);
- end;
存储过程案例三:带输入、输出参数的存储过程
存储过程的参数分为输入参数和输出参数,
输入参数:输入参数一般会在变量名和数据类型之间加 in 来表示该参数是输入参数
输出参数:输出参数一般会在变量名和数据类型之间加 out 来表示该变量是输出参数
不写 in 和 out 的话,默认为输入参数
- create procedure procedure_3(v_i in number, v_j in number, v_m out number) is begin dbms_output.put_line('procedure_3.......');
- v_m: =v_i - v_j;
- dbms_output.put_line(v_i || ' - ' || v_j || ' = ' || v_m);
- end;
PL/SQL 块中调用存储过程
下面以调用上面三个存储过程为例
- declare v_param1 number(5) : =2;
- v_param2 number(5) : =8;
- v_result number(5);
- begin--调用上面案例一的存储过程procedure_1(); --调用上面案例二的存储过程procedure_2(v_param1, v_param2); --调用上面案例三的存储过程procedure_3(v_param1, v_param2, v_result);
- dbms_output.put_line(v_result);
- end;
- /*执行结果:*/
- procedure_1.......procedure_2.......2 + 8 = 10 procedure_3.......2 - 8 = -6 10
java 调用存储过程
案例一:java 调用没有返回值的存储过程
要求:编写一个像数据库 emp 表插入一条编号为 6666,姓名为张三,职位为 MANAGER 的记录
- /*存储过程*/
- create procedure procedure_4(v_empno emp.empno % type, v_ename emp.ename % type, v_job emp.job % type) is begin insert into emp(empno, ename, job) values(v_empno, v_ename, v_job);
- end;
- //java调用存储过程
- public static void main(String[] args) {
- Connection conn = null;
- CallableStatement cs = null;
- ResultSet rs = null;
- //java调用存储过程
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");
- cs = conn.prepareCall("{call procedure_4(?,?,?)}");
- //给输入参数赋值
- cs.setInt(1, 6666);
- cs.setString(2, "张三");
- cs.setString(3, "MANAGER");
- cs.execute(); //执行
- } catch(Exception e) {
- e.printStackTrace();
- } finally {
- closeResource(conn, cs, rs); //关闭资源
- }
- }
- //执行后就会向数据库的emp表中插入一条编号为6666,姓名为张三,职位为MANAGER的记录
案例二:java 调用返回单列单行的存储过程
要求:编写一个根据员工编号查找员工姓名的存储过程,并用 java 调用该存储过程
- /*存储过程*/
- create procedure procedure_5(v_empno in emp.empno % type, v_ename out emp.ename % type) is begin select ename into v_ename from emp where empno = v_empno;
- end;
- //java调用存储过程
- public static void main(String[] args) {
- Connection conn = null;
- CallableStatement cs = null;
- ResultSet rs = null;
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");
- cs = conn.prepareCall("{call procedure_5(?,?)}");
- cs.setInt(1, 6666); //给输入参数赋值
- /*指定输出参数的数据类型
- 语法:oracle.jdbc.OracleTypes.输出参数的数据类型
- 此例输出参数的数据类型是varchar,所以是oracle.jdbc.OracleTypes.VARCHAR*/
- cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
- cs.execute(); //执行
- //获取输出参数的值,位置要和输出参数对应?的位置对应起来,该例输出参数对应第2个问号,而且输出参数的数据类型为字符型,所以是cs.getString(2)
- String a = cs.getString(2);
- System.out.println("员工姓名:" + a);
- } catch(Exception e) {
- e.printStackTrace();
- } finally {
- closeResource(conn, cs, rs); //关闭资源
- }
- }
- /*执行结果,控制台打印:*/
- 结果:员工姓名:张三
案例三:java 调用返回单行多列的存储过程
要求:编写一个根据员工编号查找员工姓名、职位和工资的存储过程,并用 java 调用该存储过程
- /*存储过程*/
- create procedure procedure_6(v_empno in emp.empno % type, v_ename out emp.ename % type, v_job out emp.job % type, v_sal out emp.sal % type) is begin select ename,
- job,
- sal into v_ename,
- v_job,
- v_sal from emp where empno = v_empno;
- end;
- //java调用存储过程
- public static void main(String[] args) {
- Connection conn = null;
- CallableStatement cs = null;
- ResultSet rs = null;
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");
- cs = conn.prepareCall("{call procedure_6(?,?,?,?)}");
- cs.setInt(1, 7788);
- //指定输出参数的数据类型,注意:顺序要对应起来
- cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
- cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
- cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE);
- cs.execute(); //执行
- //获取返回值
- String ename = cs.getString(2); //获取姓名
- String job = cs.getString(3); //获取职位
- double sal = cs.getDouble(4); //获取薪水
- System.out.println("员工编号为7788的姓名为:" + ename + " 职位是:" + job + " 薪水是:" + sal);
- } catch(Exception e) {
- e.printStackTrace();
- } finally {
- closeResource(conn, cs, rs); //关闭资源
- }
- }
- /*执行结果,控制台打印:*/
- 员工编号为7788的姓名为:SCOTT职位是:ANALYST薪水是:3000.0
案例四:java 调用返回多行多列(返回列表)的存储过程
要求:编写一个根据部门编号查找部门所有员工信息的存储过程,并用 java 调用该存储过程
- /*定义游标*/
- create package my_package as type emp_cursor is ref cursor;
- end my_package;
- /*存储过程*/
- create procedure procedure_7(v_deptno in emp.deptno % type, emp_cursor out my_package.emp_cursor) is begin open emp_cursor
- for select * from emp where deptno = v_deptno;
- end;
- //java调用存储过程
- public static void main(String[] args) {
- Connection conn = null;
- CallableStatement cs = null;
- ResultSet rs = null;
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");
- cs = conn.prepareCall("{call procedure_7(?,?)}");
- cs.setInt(1, 20); //给输入参数赋值
- cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); //指定输出参数的数据类型
- cs.execute();
- rs = (ResultSet) cs.getObject(2); //获取输出参数的值
- while (rs.next()) {
- //顺序为数据库中字段前后顺序,例如数据库emp表中第5列为hiredate,数据类型为Date,所以获取第5列值时就应该用rs.getDate(5)
- System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDate(5));
- }
- } catch(Exception e) {
- e.printStackTrace();
- } finally {
- closeResource(conn, cs, rs); //关闭资源
- }
- }
/* 以下就是 20 号部门所有员工的信息,这里为方便我们只打印了编号、姓名和入职时间
运行结果,控制台打印:*/
- 7369 SMITH 1980 - 12 - 17 7566 JONES 1981 - 04 - 02 7788 SCOTT 1987 - 04 - 19 7876 ADAMS 1987 - 05 - 23 7902 FORD 1981 - 12 - 03
这是上面 java 调用存储过程代码中关闭资源方法的代码
- public static void closeResource(Connection conn, CallableStatement cs, ResultSet rs) {
- if (rs != null) {
- try {
- rs.close();
- } catch(SQLException e) {
- e.printStackTrace();
- }
- }
- if (cs != null) {
- try {
- cs.close();
- } catch(SQLException e) {
- e.printStackTrace();
- }
- }
- if (conn != null) {
- try {
- conn.close();
- } catch(SQLException e) {
- e.printStackTrace();
- }
- }
- }
最后给个应用,分页的存储过程
分页存储过程:
- /*定义游标*/
- create package page_package as type page_cursor is ref cursor;
- end page_package;
- /*存储过程*/
- create procedure pro_paging(v_page_size in number, --每页显示多少条v_page_count out number, --总页数v_current_page in number, --当前页v_total_count out number, --记录总条数emp_cursor out page_package.page_cursor--返回查询结果集的游标) is v_begin number(5) : =v_page_size * (v_current_page - 1) + 1; --查询起始位置v_end number(5) : =v_page_size * v_current_page; --查询结束位置v_sql varchar2(1000) : ='select empno,ename from
- (select a.empno,a.ename,rownum rn from
- (select empno,ename from emp) a
- where rownum<=' || v_end || ') b
- where b.rn>=' || v_begin;
- /*不能像下面这么写,不然调用该存储过程时会报类型不一致的错,因为最里面查的只有empno,ename,因此外面也要和里面保持一致
- v_sql varchar2(1000):=\'select * from
- (select a.*,rownum rn from
- (select empno,ename from emp) a
- where rownum<=\'|| v_end ||\') b
- where b.rn>='||v_begin;*/
- v_ename varchar2(10);
- v_empno number(4);
- begin open emp_cursor
- for v_sql;
- loop fetch emp_cursor into v_empno,
- v_ename;
- exit when emp_cursor % notfound;
- dbms_output.put_line(v_empno || ' ' || v_ename);
- end loop;
- v_sql: ='select count(empno) from emp';
- execute immediate v_sql into v_total_count;
- if (mod(v_total_count, v_page_size) = 0) then v_page_count: =v_total_count / v_page_size;
- else v_page_count: =trunc(v_total_count / v_page_size) + 1;
- end
- if;
- dbms_output.put_line('共 ' || v_total_count || ' 条记录');
- dbms_output.put_line('共 ' || v_page_count || ' 页');
- dbms_output.put_line('当前页: ' || v_current_page);
- dbms_output.put_line('每页显示 ' || v_page_size || ' 条');
- end;
Java 调用的话和上面 java 调用存储过程的例子一样。这里为了方便 ,就直接在 pl/sql 中调用了
- /*调用分页存储过程*/
- declare v_page_count number(5);
- v_cursor page_package.page_cursor;
- v_total_count number(5);
- begin dbms_output.put_line('第一页数据。。。。。。。。。');
- pro_paging(5, --每页显示5条v_page_count, --总页数1, --当前页v_total_count, --记录总条数v_cursor--游标);
- dbms_output.put_line('--------------------------');
- dbms_output.put_line('第二页数据。。。。。。。。。'); --显示第二页数据pro_paging(5, --每页显示5条v_page_count, --总页数2, --当前页v_total_count, --记录总条数v_cursor--游标);
- end;
- /*运行结果:*/
- 第一页数据。。。。。。。。。6666张三20 empSu2 19 empSave2 7369 SMITH 7499 ALLEN共17条记录共4页当前页:1每页显示5条--------------------------第二页数据。。。。。。。。。7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK共17条记录共4页当前页:2每页显示5条
以上所述是小编给大家介绍的 Oracle 存储过程及调用,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 PHPERZ 网站的支持!
来源: http://www.phperz.com/article/17/0718/336675.html