oracle存储过程学习

/*set serveroutput on
declare
  type emp_record_type is record(
    name emp.ename%TYPE,
    salary emp.sal%TYPE,
    dno emp.deptno%TYPE
    );
  emp_record emp_record_type;
begin
  select ename,sal,deptno into emp_record --放到记录
  from emp where empno=&no;
  dbms_output.put_line(emp_record.name);
end;*/

--select * from dept;
/*  记录类型
declare
  dept_record dept%ROWTYPE;
begin
dept_record.deptno:=50;
  dept_record.loc:='BEIJING';
  dept_record.dname:='ADMINISTRATOR';
  insert into dept values dept_record;
  dept_record.deptno:=30;
  dept_record.dname:='SALES';
  dept_record.loc:='ShangHai';
  update dept set row=dept_record where deptno=30;
  dept_record.deptno:=50;
  delete from dept where deptno=dept_record.deptno;
 
end;    */   

--索引表       
/*set serveroutput on
declare
    type ename_table_type is table of emp.ename%TYPE  --建立索引表
         index by BINARY_INTEGER;
    ename_table ename_table_type;
begin
  select ename into ename_table(-1) from emp  --下标取行值
  where empno=&no;
  dbms_output.put_line('雇员名:'||ename_table(-1));
end;   */ 
--使用varchar2
/*set serveroutput on
declare
   type area_table_type is table of number
        index by varchar2(10);
   area_table area_table_type;
begin
   area_table('北京'):=1;  --建立索引表
   area_table('上海'):=2;
   area_table('广州'):=3;
   dbms_output.put_line('第一个元素:'||area_table.first);--获取index ,以拼音排序
   dbms_output.put_line('最后一个元素:'||area_table.last);
end;  */                                    
                                         
--嵌套表
/*declare
   type ename_table_type is table of emp.ename%type;
   ename_table ename_table_type;
begin
   ename_table:=ename_table_type('MARY','MARY','MARY');
   select ename into ename_table(2) from emp
   where empno=&no;
   dbms_output.put_line('雇员名1:'||ename_table(1));
   dbms_output.put_line('雇员名2:'||ename_table(2));
   dbms_output.put_line('雇员名3:'||ename_table(3));
end;  */                    

--表列中使用嵌套表
/*create type phone_type is table of varchar2(20);--create type 创建嵌套表,自动生成构造方法
/
create table employee(
       id number(4),
       name varchar2(10),
       sal number(6,2),
       phone phone_type
)nested table phone store as phone_table;    
--指定专门存储表

begin
  insert into employee values(1,'scott',800,phone_type('0471-888888','13400000000'));--使用构造方法
end;*/
--检索嵌套列数据
/*set serveroutput on
declare
    phone_table phone_type;
begin
  select phone into phone_table from employee where id=1;--设置嵌套列变量
  for i in 1..phone_table.count loop
      dbms_output.put_line('电话号码:'||phone_table(i));
  end loop;
end;*/
--更新
/*declare
  phone_table phone_type:=phone_type('010-123456','12322222222');
begin
  update employee set phone=phone_table where id=1;
end;*/

--变长数组 varray
/*declare
  type ename_table_type is varray(20) of emp.ename%type; --设置容量
  ename_table ename_table_type:=ename_table_type('mary');--初始化
begin
  for i in 1..ename_table.count loop
    select ename into ename_table(i) from emp where empno=&no;--更改
  end loop;
  for i in 1..ename_table.count loop
    dbms_output.put_line('雇员名'||i||':'||ename_table(i));
  end loop;
end;*/
--表列中使用 varray
/*create type phone_type is varray(20) of varchar2(20);
/
create table employee(
       id number(4),
       name varchar2(10),
       sal number(6,2),
       phone phone_type
);*/

--pl/sql 记录表 处理多行多列数据
/*declare
  type emp_table_type is table of emp%rowtype  --索引表 多行多列
       index by binary_integer;
   emp_table emp_table_type;
begin
  select * into emp_table(1) from emp
  where empno=&no;
  dbms_output.put_line('雇员姓名:' ||emp_table(1).ename);
  dbms_output.put_line('雇员工资:'||emp_table(1).sal);
end;   */ 

--多级集合 多维数组 嵌套varray
/*declare
  type al_varray_type is varray(10) of int;
  type nal_varray_type is varray(10) of al_varray_type;
  nv  nal_varray_type:=nal_varray_type(
      al_varray_type(58,100,102),
      al_varray_type(55,6,73),
      al_varray_type(2,4));
begin
  dbms_output.put_line('显示元素:');
  for i in 1..nv.count loop
    for j in 1.. nv(i).count loop
      dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;   
/*/
--嵌套表
/*declare
  type al_varray_type is table of int;
  type nal_varray_type is table of al_varray_type;
  nv  nal_varray_type:=nal_varray_type(
      al_varray_type(58,100,102),
      al_varray_type(55,6,73),
      al_varray_type(2,4,6));
begin
  dbms_output.put_line('显示元素:');
  for i in 1..nv.count loop
    for j in 1.. nv(i).count loop
      dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;   
/*/

--多级索引表
/*declare
  type al_varray_type is table of int
       index by binary_integer;
  type nal_varray_type is table of al_varray_type
       index by binary_integer;
  nv  nal_varray_type;
begin
  nv(1)(1):=10;
  nv(1)(2):=20;
  nv(2)(1):=30;
  nv(2)(2):=40;
  dbms_output.put_line('显示元素:');
  for i in 1..nv.count loop
    for j in 1.. nv(i).count loop
      dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;   
/*/

/*declare
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;--:=ename_table_type('kevin','lee'); --索引表无需初始化
begin
  if ename_table.exists(1) then --用于pl/sql
    ename_table(1):='scott';
    dbms_output.put_line('更改后:'||ename_table(1));
  else
    dbms_output.put_line('要初始化表元素');
  end if;
end;
/*/

--批量绑定测试

/*drop table demo;
commit;
create table demo(
       id number(6) primary key,
       name varchar2(10)
       );
commit;      
declare
  type id_table_type is table of demo.id%type
       index by binary_integer;
  type name_table_type is table of demo.name%type
       index by binary_integer;
  id_table id_table_type;
  name_table name_table_type;
  start_time number(10);
  end_time number(10);
 
begin
  for i in 1..50000 loop
    id_table(i):=i;
    name_table(i):='Name'||i; --to_char?
  end loop;
  start_time:=dbms_utility.get_time;
  --for i in 1..id_table.count loop
  forall i in 1..id_table.count    --使用批量绑定特征
    insert into demo values(id_table(i),name_table(i));
  --end loop;
  end_time:=dbms_utility.get_time;
  dbms_output.put_line('总计时间(秒):'||(end_time-start_time)/100);--时间??
  commit;
end;
/*/
/*
create table new_demo as  --空表
select * from demo where 1=0;
commit;

declare
  type id_table_type is table of demo.id%type;
  type name_table_type is table of demo.name%type;
  id_table id_table_type;
  name_table name_table_type;
  type index_pointer_type is table of pls_integer; --创建指针表
  index_pointer index_pointer_type;
begin
  select * bulk collect into id_table,name_table  --??
  from demo;
  index_pointer:=index_pointer_type(6,8,10);
  forall i in values of index_pointer
    insert into new_demo values(id_table(i),name_table(i));
  commit;
end;
/*/

--显示游标
/*declare
  cursor emp_cursor is
    select ename,sal from emp where deptno=10;  --create
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  open emp_cursor;   --open
  loop
    fetch emp_cursor into v_ename,v_sal;    --fetch(loop)
    exit when emp_cursor%NOTFOUND;
    dbms_output.put_line(v_ename||':'||v_sal);
  end loop;
  close emp_cursor; --close
end;
/*/
--显示游标 提取到collect
/*declare
  cursor emp_cursor is
    select ename from emp where deptno=10;  --create
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;
begin
  open emp_cursor;   --open
  fetch emp_cursor bulk collect into ename_table;    --fetch
  for i in 1..ename_table.count loop
    dbms_output.put_line(ename_table(i));
  end loop;
  close emp_cursor; --close
end;
/*/
/*
declare
  type name_array_type is varray(5) of varchar2(10);
  name_array name_array_type;
  cursor emp_cursor is select ename from emp;
  lim int:=5;
  v_count int:=0;
begin
  open emp_cursor;
  loop
    fetch emp_cursor bulk collect into name_array limit lim;
    dbms_output.put('雇员名:');
    for i in 1..(emp_cursor%rowcount-v_count) loop
      dbms_output.put(name_array(i)||'  ');
    end loop;
   
    dbms_output.put_line(to_char(v_count));
    v_count:=emp_cursor%rowcount; --上一次的游标位置
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
end;
/*/
--游标变量  建议  可带参数   当用于 update  delete
/*declare
  cursor emp_cursor(no number) is select ename,sal from emp where deptno=no  --for update 加锁
  for update;
  emp_record emp_cursor%rowtype;
begin
  open emp_cursor(10);
  loop
    fetch emp_cursor into emp_record;
    exit when emp_cursor%notfound; 
    update emp set sal=sal-200 where current of emp_cursor;  --current of 当前行
    dbms_output.put_line('雇员姓名:'||emp_record.ename||',雇员工资:'||emp_record.sal);
  end loop;
  close emp_cursor;
end;
/*/

--使用for语句 简化开发
declare
  cursor emp_cursor(no number) is select ename,sal from emp where deptno=no; 
begin
  for emp_record in emp_cursor(10) loop  --不用声明  也可不用游标 替换为子查询 (select * from emp)
    dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||emp_record.ename);
  end loop;
end;
/