PL/SQL在取行、取列、取行列以及分页中的应用

PL/SQL：过程SQL语言（Procedural Language/SQL）。是Oracle数据库在标准的sql语言上的拓展，不仅允许嵌入sql语言，还可以定义变量和常量，允许利用条件语句和循环语句，允许利用例外处理各种错误，从而使功能变得更加强大。

①. 过程，函数，触发器是由PL/SQL编写的
②. 过程，函数，触发器实现在Oracle中
③. 过程，函数可以在Java中使用

一、PL/SQL的优点：
1). 提高应用程序的运行性能
2). 模块化的设计思想（分页，订单，转账）
3). 减少网络传输量
4). 提高安全性

二、缺点：移植性不太好

三、标示符号的命名规范：

定义变量的时候，建议用 v_ 作为前缀
定义常量的时候，建议用 c_ 作为前缀
定义一个游标的时候，建议用 _cursor 作为后缀
定义一个例外时候，建议用e_ 作为前缀

四、PL/SQL的取行操作：记录的定义（取行）

–记录的定义
type 记录名类型 is record (变量名 变量类型(长度),…);

–定义了一个变量，变量的类型就是 记录名类型；
变量名 记录名类型;

--例如，输入员工号，输出姓名，工资，部门号
declare
  --记录类型
  type record_emp_type is record(
     v_name varchar2(12),
     v_sal s_emp.salary%type,
     v_deptid s_emp.dept_id%type 
  );
  --定义一个记录名类型变量
  emp_record record_emp_type;

  begin
    select first_name,salary,dept_id into emp_record from s_emp where id=&ch;
    dbms_output.put_line(emp_record.vname||'  '||emp_record.v_sal||'  '||emp_record.v_deptid);   
  end;

五、PL/SQL的取列操作：表的定义（取列）

–PL/SQL表的定义
type 表名类型 is table of 表名.字段名%type index by binary_integer;

①. index by binary_integer 表示下标是整数
②. 定义变量: 变量名 表名类型;
③. 取数据的时候：变量名(index)

--定义一个表类型table_emp_fn，该类型用于存放s_emp.first_name%type的数组
--index by binary_integer 表示下标是整数
declare
  type table_emp_fn is table of s_emp.first_name%type index by binary_integer;
  --定义一个表名类型变量
  sp_table table_emp_fn;
  begin
    select first_name into sp_table(0) from s_emp where id=&x;
    dbms_output.put_line(sp_table(0));
  end;

六、PL/SQL的取行列操作：游标的定义和使用（取行列）

--游标与迭代器差不多，其实就相当于一个指针
declare
  cursor 游标名 is 查询语句;

    变量名 游标名%rowtype;

  begin
    open 游标名;
    loop
     fetch 游标名 into 变量名;--或for 变量名 in 游标名 loop
     dbms_output.put_line(变量名.名称);
     close 游标名;
    end loop;
  end;

–例如

declare
  --定义一个游标
  cursor emp_cursor is select first_name,salary,dept_id from s_emp;
  --定义变量
  vcur_emp emp_cursor%rowtype;
  begin
    --打开游标
    open emp_cursor;
    --循环遍历
    loop
      --取值
      fetch emp_cursor into vcur_emp;
      --结束循环
      exit when emp_cursor%notfound; 
      --打印
      dbms_output.put_line(vcur_emp.first_name||'=='||vcur_emp.salary||'=='||vcur_emp.dept_id);    
    end loop;
  --关闭游标
  close emp_cursor;
  end;

七、PL/SQL的分页操作：分页的编写

–模板
select * from (
select a.* rownum rn from (
select * from s_emp) a where rownum<=20)
where rn>=5;

--创建一个包，在包中定义一个test_cursor游标
create or replace package tespackage as type test_cursor is ref cursor;
  end tespackage;

--分页编写的过程**********************************************
create or replace procedure fenye(
   tablename in varchar2,--输入的表的名称
   pagesize in number,--输入每页要显示的数据条数
   pagenow in number,--输入当前页
   myrows out number,--输出总记录数
   mypagecount out number,--输出总页数
   p_cursor out tespackage.test_cursor--返回记录集
) is
--定义SQL语句字符串
  v_sql varchar2(1000);--用来存储SQL语句
  v_begin number:=(pagenow-1)*pagesize+1;--开始的数据条数
  v_end number:=pagenow*pagesize;--结束的数据条数

  begin
    --取出满足记录的数据
    v_sql:='select * from(select a.*,rownum rn from(
                        select * from '||tablename||') a
                    where rownum<='||v_end||')
                where rn>='||v_begin;
    --把游标与sql语句关联起来
    open p_cursor for v_sql;
    v_sql:='select count(*) from '||tablename;
    execute immediate v_sql into myrows;
    --获得总的记录数
   -- select count(*) into myrows from tablename;
    --取总页数
    if mod(myrows,pagesize)=0 then
      mypagecount:=myrows/pagesize;
      else
        mypagecount:=myrows/pagesize+1;
    end if;
  end;