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 的记录
- ? http://www.jb51.net/article/116017.htm#
- /* 存储过程 */
- 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 调用该存储过程
- ? http://www.jb51.net/article/116017.htm#
- /* 存储过程 */
- 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 调用该存储过程
- ? http: //www.jb51.net/article/116017.htm#
- /* 存储过程 */
- 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 调用该存储过程
- ? http://www.jb51.net/article/116017.htm#
- /* 定义游标 */
- 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 号部门所有员工的信息, 这里为方便我们只打印了编号姓名和入职时间
- 运行结果, 控制台打印:*/
- ? http://www.jb51.net/article/116017.htm#
- 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 调用存储过程代码中关闭资源方法的代码
- ? http://www.jb51.net/article/116017.htm#
- 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();
- }
- }
- }
最后给个应用, 分页的存储过程
分页存储过程:
- ? http://www.jb51.net/article/116017.htm#
- /* 定义游标 */
- 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 中调用了
- ? http://www.jb51.net/article/116017.htm#
- /* 调用分页存储过程 */
- 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 存储过程及调用, 希望对大家有所帮助, 如果大家有任何疑问请给我留言, 小编会及时回复大家的在此也非常感谢大家对脚本之家网站的支持!
来源: http://www.bubuko.com/infodetail-2545991.html