Oracle----plsql编程-----有点难度的动脑题

/*
题目一  在plsql中创建表cc 插入数据如下（列 c1,c2）： 
c1 c2 
1 西 
1 安 
1 的 
2 天 
2 气 
3 好 
转换为 
1 西安的
2 天气 输出
3 好  输出
*/


create table cc (
   c1 number,
   c2 varchar2(3)
)
delete from cc;
insert into cc values('1','西');
insert into cc values('1','安');
insert into cc values('1','的');
insert into cc values('2','天');
insert into cc values('2','气');
insert into cc values('3','好');
commit;

--wm_concat
select c1,replace(wm_concat(c2),',','') from cc group by c1;
--plsql语句
declare myStr varchar2(20):='';
begin
  for i in (select c1 from cc group by c1) loop
     myStr:='';
     for j in (select c2 from cc where c1=i.c1)  loop
       myStr:=myStr||j.c2;
     end loop; 
     syso(i.c1||' '||myStr );
  end loop;
end;

/
题目二 
  查找出输入的用户下，每张表的记录数，（提示：使用 tab表）
以scott用户为例，
结果应如下： 
  DEPT...................................4 
  EMP...................................14 
  BONUS.................................0 
  SALGRADE.............................5
  
  select * from tab --有权限查询的表
  select * from user_tables  --scott 创建的表
  select * from all_tables where owner='SCOTT'
  

  declare vSql varchar2(200);
    rtrNumber number;
  begin
    for i in (select * from user_tables) loop
      -- select count(rowid) from 表名 
      vSql:='select count(rowid)  from '||i.table_name;
      execute immediate vSql  into rtrNumber;
      dbms_output.put_line(i.table_name || '...............................' ||rtrNumber);
    end loop;
  
  end;

  
/*  
题目 三
加载在c盘存在一个文件 a.txt
内容如下
  100|金融部|深圳
  110|财务部|东莞
  
使用plsql读取该文件 并将 每行的 数据插入到dept表中 （读取一行 按|切割）
读取文件的代码
declare InHandle utl_file.file_type ; --定义文件类型
vNewLine VARCHAR2(250);  --定义变量获取当前行数据
BEGIN
vInHandle := utl_file.fopen ('mydir', 'a,txt ', 'R');
utl_file.get_line (vInHandle, vNewLine); --调用一次 读取一行
*/


declare vInHandle utl_file.file_type ; --定义文件类型
vNewLine VARCHAR2(250);  --定义变量获取当前行数据
begin
vInHandle:= utl_file.fopen ('MYDIR', 'a1.txt', 'R');


 

loop
 utl_file.get_line (vInHandle, vNewLine); --调用一次 读取一行
 --dbms_output.put_line(vNewLine);
 --100|金融部|深圳
 declare a1 varchar2(20);
   a2 varchar2(20);
   a3 varchar2(20);
   beginIdx number:=1;
   begin
      beginIdx:=instr(vNewLine,'|'); --4
      -- 1号位置开始到 找到一个 |的位置结束的字符串
      a1:=substr(vNewLine,1,instr(vNewLine,'|')-1);
      --从第一个 |的下一个位置开始    5  
      a2:=substr(vNewLine,beginIdx+1,instr(vNewLine,'|',beginIdx+1)-beginIdx-1);
      beginIdx:=instr(vNewLine,'|',beginIdx+1);
      a3:=substr(vNewLine,beginIdx+1);
      dbms_output.put_line(a1||a2||a3);
      insert into dept values(a1,a2,a3);
      commit;
   end;
 end loop;
 exception when others then
   utl_file.fclose(vInHandle);
   
end;

/*
  2 定义存储过程 可以传入以下参数
          query(ename,job,sal)
        如果传入了某几个参数 以参数组合的形式查询结果
   比如调用过程如下
  query('Cleck',null,null);
  查询的sql为
  select * from emp where ename like '%Cleck%';
   query('Cleck','Manager',null);
  查询的sql为
  select * from emp where ename like '%Cleck%' and job like '%Manager%'
  要求输出查询的结果
 */

 create or replace procedure queryEmp(ename varchar2,job varchar2,sal number)
 as
  vSql :='select * from emp where 1=1 ';
 begin
   if(ename is not null) then
     vSql：=vSql|| 'and ename='' '||ename||'''';
   end if;
   if(job is not null) then
     vSql：=vSql|| 'and job='' '||job||'''';
   end if;
   if(sal is not null) then
     vSql：=vSql|| 'and sal='' '||sal||'''';
   end if;
 end;
 
 queryEmp(null,'a','b')
 select * from emp where job='a' and sal='b'
 select * from emp where 1=1 and job='a' and sal='b'
  queryEmp(null,null,'b')
 select * from emp  and sal='b'
 select * from emp where 1=1  and sal='b'

 
3 写出一个分页的存储过程
        定义如下
           tablePager(tableName,curPage,pageSize)
        调用 
            tablePager('Emp',2,10) 

            查询emp表中 第二页的数据（每页显示10条 第二页就是 10-20条）

	开始			结束
(curPage-1)*pageSize+1    curPage*pageSize


 create or replace procedure tablePager(tableName varchar2,curPage number,pageSize number)
 as
  starIndex number:=(curPage-1)*pageSize+1;
  endIndx number:=curPage*pageSize;
  vSql varchar2(2000):='';
  colSql varchar2(2000):='';
  mycursor sys_refcursor;
  myResult varchar2(2000);
 begin
    select replace(wm_concat(column_name),',','||'' ''||') into colSql from user_tab_cols where table_name=tableName;
    vSql:='select '||colSql||' from (select t.*,rownum rn from '||tableName||' t) where rn>='||starIndex||' and rn<= '||endIndx;
    open mycursor for vSql;
    loop
       fetch mycursor into myResult;
       exit when mycursor%notfound;--退出条件 下面会提到4个属性
       syso(myResult);
    end loop;
    close mycursor;
 end;
   

测试
begin
   tablePager('DEPT',2,3);
end;

游标的四个属性

%ISOPEN：是否打开
%ROWCOUNT：行数
%FOUND：boolean值，是否还有数据
%NOTFOUND：是否已无数据

 
            
            
            
           
4 定义一个存储过程 传入表名
        删除该表中的重复记录
          比如 deleteMul(tableName)
              调用 deleteMul('emp'); 必须删除表emp的重复数据  （execute immediate    using ）
     
 

  delete from 表名 where rowid not in(select max(rowid) from 表名 group by 所有的列)


 create or replace procedure deleteMul(tableName varchar2)
 as
 colSql varchar2(2000):='';
 begin
   select wm_concat(column_name) into colSql from user_tab_cols where table_name=tableName;
   execute immediate 'delete from '||tableName||' where rowid not in(select max(rowid) from 
   '||tableName||' group by '||colSql||')';
 end;
 
测试
begin
   deleteMul('CC');
end;