storedprocedure
过程（procedure）又叫存储过程（stored procedure），是一个有名称的PL/SQL程序块 。
过程相当于java中的方法， 它注重的是实现某种业务功能 。 函数（function）也相当于java中的方法，它 注重计算并且总是有返回结果 。
过程和函数都是能够永久存储在数据库中的程序代码块，应用时通过调用执行 。
I 过程的基本结构
create [or replace ] procedure 过程名称(形参名称 形参类型,形参名称 形参类型······) is | as 定义变量 begin 过程体 exception 异常 end;
1.1 含有输入参数的过程
输入参数 用in 标识 可省略
--过程
create or replace procedure mypro(p_id in number,p_sal in number)
is
v_count number(3);
v_no number(3);
begin
select count(*) into v_count from emp where empno = p_id;
if v_count > 0 then
update emp set sal = sal+p_sal where empno=p_id;
v_no := sql%rowcount;
commit;
dbms_output.put_line(v_no||'rows updated');
else
dbms_output.put_line('记录不存在');
end if;
end;1.2 无参的过程
--无参的过程 create or replace procedure mypro_noparam is v_count number(3); begin select count(*) into v_count from emp where empno = 7788; dbms_output.put_line(v_count||' 条记录'); end;
1.3 有输出参数的过程
输出参数用 out 标识
--含有输出参数的过程 create or replace procedure mypro(p_id in number, p_sal out number) is begin select sal into p_sal from emp where empno=p_id; end;
II 过程的调用
2.1 通过匿名块调用
输入参数
--通过匿名块调用过程 begin mypro(1234,100); end;
输出参数过程
declare v_sal number(10); begin mypro(7788,v_sal); dbms_output.put_line(v_sal); end;
无参的过程
begin mypro_noparam; end;
2.2 命令行调用
#1. 调用输入参数 SQL> exec mypro(7788,3000); # 2.输出参数 SQL> var v_sal number; # 注册变量 SQL> exec mypro(7788,:v_sal); #:变量名称 使用变量接收输出 # 3.调用无参 SQL> exec mypro; ———————————————— 版权声明：本文为CSDN博主「iOS逆向」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
示例：
写一个过程来封装emp表中的数据
/*写一个过程封装emp表中的数据*/create or replace procedure my_pro is type v_table is table of emp%rowtype index by binary_integer; --定义表类型的变量 v_data v_table; --定义游标类型 type table_cursor is ref cursor return emp%rowtype; --定义游标变量 v_myCursor table_cursor; v_index binary_integer:=0;begin --开启游标 open v_myCursor for select * from emp; --获取数据 loop fetch v_myCursor into v_data(v_index); --打印数据 exit when v_myCursor%notfound; dbms_output.put_line(v_data(v_index).deptno||'____________'||v_data(v_index).empno); --下标的自增 v_index :=v_index+1; end loop; close v_myCursor; end; --调用过程 begin my_pro;end;
写一个过程：输入员工编号，通过游标获取，输出该员工对应下属的信息 。
/*写一个过程输入员工编号，通过游标获取输出该员工对应下属的信息*/create or replace procedure my_pro(p_id in number)is type v_table is table of emp%rowtype index by binary_integer; --定义表类型的变量 v_data v_table; --定义游标类型 type table_cursor is ref cursor return emp%rowtype; --定义游标变量 v_myCursor table_cursor; v_index binary_integer:=0;begin --开启游标 open v_myCursor for select * from emp where mgr=p_id; --获取数据 loop fetch v_myCursor into v_data(v_index); --打印数据 exit when v_myCursor%notfound; dbms_output.put_line(v_data(v_index).deptno||'____________'||v_data(v_index).empno); --下标的自增 v_index :=v_index+1; end loop; close v_myCursor; end; --调用过程 begin my_pro(&no);end;
计算100-200的素数
/*计算100-200的素数*/declare
begin
execute immediate ('create or replace procedure myPro
is
v_flag boolean ;
begin for i in 100..200 loop
v_flag:=true;--默认为是素数 --增加除数 for j in 2..i/2 loop
if i mod j =0 then v_flag:=false;--标记为不是素数
exit;
end if;
end loop;
--打印素数 if v_flag then
dbms_output.put_line(i);
end if;
end loop;
end;
');
end;
begin
myPro; end;2.3 通过java调用过程
package jdbcPlSQL;
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.SQLException;import java.sql.Types;
import oracle.jdbc.driver.OracleTypes;import util.CoonectionUtil;
public class TestPLSQL {
public static void main(String[] args) {
Connection conn=null;
CallableStatement cstmt=null;
//调用无参的过程
//String sql="{call my_Porcedure()}";
//调用有参的过程
//String sql="{call mypro(?,?)}";
//调用有输出参数的过程
String sql="{call myPorcedure(?,?)}";
try {
conn=CoonectionUtil.getConn();
cstmt=conn.prepareCall(sql);
cstmt.setInt(1, 7369);
//住入输出参数oracle.jdbc.driver.OracleTypes//Types
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.execute();
//获取输出参数的值
System.out.println(cstmt.getInt(2));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}}III 自定义函数
/*创建函数*/create or replace function my_fun(p_deptno number)return number is v_num number(10);begin select count(*) into v_num from emp where deptno=p_deptno; return v_num;end; declare v_num number(10);begin v_num :=my_fun(20); dbms_output.put_line(v_num);end; /*含有输出参数的函数*/create or replace function my_fun(p_id number,p_ename out varchar2)return number is v_deptno number(10); begin select deptno,ename into v_deptno,p_ename from emp where empno=p_id; return v_deptno;end; --调用含有输出参数的函数 declare v_deptno number(10); v_ename varchar2(30);begin v_deptno := my_fun(7788,v_ename); dbms_output.put_line(v_deptno||'*************'||v_ename);end;
IV 异常
系统异常分为预定义异常和非预定义异常。
预定义异常：是由数据库定义好，含有异常编码，异常名称，异常信息；
大概有20种，例如
too_many_rouws;no_data_found;zero_divide。
非预定义异常：异常编码，异常信息，但没有异常名称。
4.1非预定义异常
代码示例
/*非预定义的异常*/declare --定义异常名称
e_noFather exception;
--绑定异常名称与异常编码
pragma exception_init(e_noFather,-02291);begin
insert into emp(empno,deptno) values(1111,99);
commit;--处理异常
exception
when e_noFather then
dbms_output.put_line('该部门不存在');end;4.2 自定义异常
/*自定义异常*/declare
v_ename_search varchar2(20);
v_empno number(10):=&no;
v_ename varchar2(20):=upper('&pwd');
--自定义异常
--定义异常名称
e_pwdErr exception;
--绑定异常名称与异常编码
pragma exception_init(e_pwdErr,-20291);begin
select ename into v_ename_search from emp where empno=v_empno;
--产生异常 if v_ename_search=v_ename then
dbms_output.put_line('登陆成功');
else
--raise e_pwdErr;
--抛异常
dbms_standard.raise_application_error(-20291,'密码不正确',false);
end if;exception
when no_data_found then
dbms_output.put_line('该用户不存在');
--when e_pwdErr then --dbms_output.put_line('密码不正确');end;
/*自定义异常根据员工号计算谁工资低于3000，并计算他的入职时间*/create or replace procedure my_pro(v_empno number)is
--员工的的sal
v_sal number(20);
--员工的入职时间
v_time number(10);
--自定义异常
--定义异常名称
e_lowsal exception;
--绑定异常名称与异常编码
pragma exception_init(e_lowsal,-20291);begin
select sal into v_sal from emp where empno=v_empno;
--产生异常 if v_sal<3000 then select (sysdate-hiredate) into v_time from emp where empno=v_empno;
raise e_lowsal; --产生异常
end if;
exception --员工不存在的异常
when no_data_found then
dbms_output.put_line('该员工不存在');
when e_lowsal then
dbms_output.put_line(v_empno||'白吃了'||v_time||'天这么长的时间的饭，还只拿这样的工资'||v_sal);end;V 包
包用于管理过程和函数，包分为包头和包体。
包一定要有包头，包头负责声明函数、过程、变量和常量。包头可单独定义，单独定义的包头只能含有常量。
包体具体来实现包头所声明定义的函数和过程，包体封装实现。
5.1 代码示例
/*声明包头*/ --声明包头 create or replace package package_emp is --声明游标类型 type my_cursor is ref cursor return emp%rowtype; --定义过程 procedure my_pro (p_cursor out my_cursor ); --定义函数 function my_fun return number;end; /*声明包体*/create or replace package body package_emp is --实现过程 procedure my_pro (p_cursor out my_cursor ) is begin open p_cursor for select * from emp; end; --函数的实现 function my_fun return number is v_num number(10); begin select sum(sal) into v_num from emp; return v_num; end; end; /*调用包中的过程与方法*/declare --不要return type my_cursor is ref cursor ; --定义游标类型的变量 v_cursor my_cursor; --总工资 v_num number(10); --记录类型的变量 v_recored emp%rowtype; begin package_emp.my_pro(v_cursor); --打印数据 loop --游标已在包体中开启 fetch v_cursor into v_recored; dbms_output.put_line(v_recored.ename); exit when v_cursor%notfound; end loop; close v_cursor; --获取函数的返回值 v_num:=package_emp.my_fun; dbms_output.put_line(v_num); end;
5.2 jdbc调用含有包的过程体
代码示例
package package_JDBC;
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;
import oracle.jdbc.driver.OracleTypes;
import testlife.ConnectionUtil;/**
*
* @author zhang_kn
* 调用包中的过程
*/public class Package_Procedure {
public static void main(String[] args){
//获取连接
Connection conn=ConnectionUtil.getConn();
//System.out.println(conn);
//调用在包中的过程,该过程含有输出参数
String sql="{call package_emp.my_pro(?)}";
CallableStatement cstmt=null;
ResultSet rs=null;
try {
cstmt=conn.prepareCall(sql);
//注入参数
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
//获取输出参数
rs=(ResultSet) cstmt.getObject(1);
while(rs.next()){
//获取数据
System.out.println(rs.getInt(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
ConnectionUtil.close(conn,cstmt,rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}}
tags:
