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) istype 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) isv_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) isv_test varchar2(40):='aaa';begin  sp_name:='你好';--不允许对输入的参数再次赋值  dbms_output.put_line(v_test);end;/

 

带有返回值的procedure

 

--带有返回的过程--输入一个userid返回usernamecreate 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表示注册时的那个2System.out.println(username);

返回一个数据集合的procedure

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

 

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

 

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

 

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

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

 

create or replace procedure get_emp(in_v_deptid in number,emp_cursor out mypackage.my_cursor) isbegin  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"));}