Oracle PL/SQL编程(2)

%type根据表中的数据类型动态的定义变量

 

复合变量

pl/sql 记录：类似于高级语言中的结构体（javabean），需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)

定义方法

Type 记录的名称 is record (

变量1 变量类型；

变量2 变量类型；

);

 

--输入一个雇员id，显示出该雇员的名字。薪水。邮箱（使用pl/sql记录实现）
create or replace procedure show_info(in_v_empid number) is
type emp_info_record is record (
v_empname employee.empname%type,--%type自适应变量
v_salary employee.salary%type,
v_email employee.email%type
);
v_emp_record emp_info_record; --实例化这个记录
begin
  select empname,salary,email into v_emp_record from employee where id=in_v_empid;
  dbms_output.put_line('姓名：'||v_emp_record.v_empname||'薪水'||v_emp_record.v_salary||'email'||v_emp_record.v_email); -- v_emp_record.v_empname(记录变量.记录成员)
end;
/

统一管理变量，方便注入参数

参照变量——介绍
  参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。

 

游标：

 

--1.编写一个过程，要求输入部门编号，打印出该部门所有员工的姓名，薪水，邮箱；
--2.如果雇员的薪水低于两千块，则给这个员工加1000块
create or replace procedure print_emp_bydeptno(in_v_departmentid number) is
--定义一个游标变量
type wwr_emp_cursor is ref cursor;
--实例化游标
emp_cursor wwr_emp_cursor;
--定义变量接收游标中的数据
v_empname employee.empname%type;
v_salary employee.salary%type;
v_email employee.email%type;
v_empid employee.id%type;
begin
  --打开游标，让游标指向一个结果集
  open emp_cursor for select empname,salary,email,id from employee where department_id=in_v_departmentid;
  
  --循环取出游标指向结果集行中的数据
  loop
    fetch emp_cursor into v_empname,v_salary,v_email,v_empid;
    --判断游标是否到达最后
    exit when emp_cursor%notfound;
    dbms_output.put_line('姓名：'||v_empname||'薪水'||v_salary||'email'||v_email);
    if v_salary<2000 then
      update employee set salary=salary+1000 where id=v_empid;
    end if;
  end loop;
  close emp_cursor;--使用完游标以后要记得关闭游标，避免空间浪费
end;
/

Oracle中的条件控制语句

1.   if 条件判断语句 then

    执行的代码

     End if ;

2.   if 条件判断语句 then

      执行的代码

      Else

      执行的代码

      End if;

 

 

3     if 条件判断语句 then

       执行的代码

        Elsif 条件判断语句    --注意这里是elsif 不是写错！！！

        执行的代码

       Else

        执行的代码

       End if;

4     loop

  执行的代码

      Exit when 条件判断语句

      End loop;

5     while 条件判断语句 loop

       执行的代码

       End loop;

 

 

 

Procedure 的小细节：

 

--判断下面的过程对不对
create or replace procedure sp_pro1(sp_name varchar2) is
v_test varchar2(40);
v_test:='aaa';--在is.....begin 间不允许对变量赋值，需要在begin.....end之间
begin
  dbms_output.put_line(v_test);
end;
/

create or replace procedure sp_pro1(sp_name varchar2) is
v_test varchar2(40):='aaa';

begin
  sp_name:='你好';--不允许对输入的参数再次赋值
  dbms_output.put_line(v_test);
end;
/

 

带有返回值的procedure

 

--带有返回的过程
--输入一个userid返回username
create or replace procedure get_name(in_v_userid in number,out_v_username out varchar2) is--这里不像函数一样需要return，out关键字会自动返回被其定义的变量
begin
  select username into out_v_username from users1 where userid=in_v_userid;
end;
/

在java程序中得到返回值

 

Class.forName(driver);
ct=DriverManager.getConnection(url,username,password);
cs=ct.prepareCall("{call get_name(?,?)}");			
//给第一个？注入值
cs.setString(1, "75");
//此时不是给？注入值，而是注册，用以表示数据类型
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();

String username=cs.getString(2);//这里的2表示注册时的那个2
System.out.println(username);

返回一个数据集合的procedure

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
※游标不能独立创建，只能在过程、包、函数中创建

 

案例：编写一个过程，输入部门号，返回该部门所有雇员信息。

 

1 --创建一个包，包含一个游标类型

 

create or replace package mypackage is
type my_cursor is ref cursor;
end;
/

2 --创建一个过程，输入一个部门编号返回部门中的所有雇员

 

create or replace procedure get_emp(in_v_deptid in number,emp_cursor out mypackage.my_cursor) is
begin
  open emp_cursor for select * from employee where department_id=in_v_deptid;
  --此时不能关闭游标,需要在java程序中关闭
  --close emp_cursor;
end;
/

在java程序中得到返回值

 

Class.forName(driver);
ct=DriverManager.getConnection(url,username,password);
cs=ct.prepareCall("{call get_emp(?,?)}");
cs.setInt(1, 9);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
//得到游标，并将其强转
rs=(ResultSet) cs.getObject(2);
while(rs.next()){
//通过表中的列名的到相应的值
System.out.println(rs.getString("empname")+" "+rs.getString("salary"));
			}