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

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

create or replace procedure <procedure_name> [<parameter_list>]
is|as
       <local> variable declaration
begin
       <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)
is
begin
       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)
is
begin
  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();

    }

}