数据库-过程 PL/SQL 的运用

过程是指用来完成特定任务的子程序其语法规则如下：

create or replace procedure <procedure_name> [<parameter_list>]is|as       <local> variable declarationbegin       <executable statement>exception       <exception handlers>end;

过程的参数类型
in：入参 默认是入参的
out：出参
in out ：出入参

应用一：查找数据库信息

create or replace procedure pro_ts1(v_empno number)as       empname emp.ename%type;    --[default '张三']begin       select ename into empname from emp where empno = v_empno;       dbms_output.put_line('雇员名为'||empname);exception       when no_data_found then            dbms_output.put_line('没有找到您要的数据......');            empname:='匿名';            dbms_output.put_line('雇员名为'||empname);end;

应用二：根据员工编号，查选员工信息，要求将查到的员工姓名返回给调用者

create or replace procedure pro_ts2(v_empno in number,v_ename out varchar2)isbegin       select ename into v_ename from emp where empno=v_empno;exception       when no_data_found then            v_ename:='匿名';end;declare        v_ename varchar2(100);begin       pro_ts2(77888,v_ename);        dbms_output.put_line(v_ename);end;

应用三：实现两个数之间的交换，并返回交换后得结果

create or replace procedure pro_ts3(v_num1 in out number,v_num2 in out number)as        v_temp number(10);begin       v_temp:=v_num1;       v_num1:=v_num2;       v_num2:=v_temp;end;declare       v_num1 number(10):=10;       v_num2 number(10):=20;begin        pro_ts3(v_num1,v_num2);       dbms_output.put_line('num1:'||v_num1||'num2:'||v_num2);end;

应用四：创建一个存储过程，用来向dept表中添加数据

数据库代码如下：

create or replace procedure pro_addDept(v_deptno number,v_dname varchar2,v_loc varchar2,v_result out number)as       v_count number;begin       select count(deptno) into v_count from dept where deptno=v_deptno;       if v_count >0 then         v_result:=1;       else         insert into dept values(v_deptno,v_dname,v_loc);         v_result:=3;       end if;exception       when others then         v_result:=2;end;

java代码如下：

public class sqlTest {    public static void main(String[] args) throws Exception {        Class.forName("oracle.jdbc.driver.OracleDriver");        Connection con=null;        CallableStatement cs;        con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","a");        cs = con.prepareCall("call pro_addDept(?,?,?,?)");        //给入参占位符赋值        cs.setString(1,"20");        cs.setString(2, "人事部");        cs.setString(3, "湖南衡阳");        //给出参占位符设置类型        cs.registerOutParameter(4, OracleTypes.NUMBER);        cs.execute();  //指定过程        //获得出参值        int result=cs.getInt(4);        if(result==1){            System.out.println("部门编号存在");        }else if(result==2){            System.out.println("数据添加失败。。。");        }else if(result==3){            System.out.println("数据添加成功。。。");        }else{            System.out.println("未知错误");        }        cs.close();        con.close();    }}

应用五：传出数据库的数据
数据库代码：

create or replace package navy_package as       type cur is ref cursor;end;create or replace procedure pro_findEmpByDeptNo(v_deptno number,v_cur out navy_package.cur)isbegin  if v_deptno=0 then    open v_cur for select * from emp;  else    open v_cur for select * from emp where deptno=v_deptno;  end if;end;

需要将数据进行打包

java代码：

public class sqlTest2 {    public static void main(String[] args) throws Exception {        Class.forName("oracle.jdbc.driver.OracleDriver");        Connection con=null;        CallableStatement cs;        con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","a");        cs = con.prepareCall("call pro_findEmpByDeptNo(?,?)");        //给入参占位符赋值        cs.setInt(1, 60);        //给出参占位符设置类型        cs.registerOutParameter(2, OracleTypes.CURSOR);        cs.execute();  //指定过程        //获得出参值        ResultSet rs=(ResultSet) cs.getObject(2);        while(rs.next()){            System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));        }        cs.close();        con.close();    }}