Oracle PLSQL 题目

/*1.使用游标输出 scott中所有的雇员名称，部门名称，年薪*/
--显示游标
declare
     --声明变量
     rowobj emp%rowtype;
     --声明游标       
     cursor mycursor is select * from emp;
begin
     --打开游标
     open mycursor ;
     --抓取数据
     loop
          fetch mycursor into rowobj;
          exit when mycursor%notfound;
          dbms_output.put_line('姓名:'||rowobj.ename||' 部门:'||rowobj.deptno||' 年薪:'||12*(rowobj.sal+nvl(rowobj.comm,0)));
     end loop;
     --关闭游标      
     close mycursor;
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%'
   要求输出查询的结果
*/
--(1)创建存储过程
  create or replace procedure myquery(v_name varchar2, v_job varchar2, v_sal varchar2) as
      --复制表所有行
      rowobj emp%rowtype;
      sqlVar varchar2(2000):= 'select * from emp where 1=1';
      --定义动态游标
      mycursor sys_refcursor;
  begin
      --条件逻辑
      if(v_name is not null) then
          sqlVar:=sqlVar||'and ename like '''||v_name||'''';
      end if;
      if(v_job is not null) then
          sqlVar:=sqlVar||'and job like '''||v_job||'''';
      end if;
      if(v_sal is not null) then
          sqlVar:=sqlVar||'and sal='||v_sal;
      end if;
      --dbms_output.put_line(sqlVar);
      --使用游标
      open mycursor for sqlVar;
      loop
        fetch mycursor into rowobj;
        exit when mycursor%notfound;
        dbms_output.put_line('['||rowobj.ename||']['||rowobj.job||']['||rowobj.sal||']');
      end loop;
      close mycursor;
  end;
--(2)传入参数测试
begin
    dbms_output.put_line('【参数1、2、3：null,null,null】');
    myquery(null,null,null);
    dbms_output.put_line('【参数1、2、3：null,'''||'CLERK'||''',null】');
    myquery(null,'CLERK',null);
    dbms_output.put_line('【参数1、2、3：'''||'SCOTT'||''',null,null】');
    myquery('SCOTT',null,null);
end;
/*
3.查找出输入的用户下，每张表的记录数，（提示：使用 tab表）
以scott用户为例，
结果应如下：?
  DEPT...................................4?
  EMP...................................14?
  BONUS.................................0?
  SALGRADE.............................5
*/
declare 
    v_sql varchar(2000);
    cou number;
    mycursor sys_refcursor ;
begin
    for i in (select * from user_tables) loop
        v_sql:='select count(rowid) from '||i.table_name;
        --游标操作
        open mycursor for v_sql;
        loop
          fetch mycursor into v_sql;
          exit when mycursor%notfound;
          cou:=v_sql;
        end loop;
        close mycursor;
        --打印语句
        dbms_output.put_line(i.table_name||'...........'||cou);
    end loop;
end;     
/*
4.定义一个存储过程 传入表名
    删除该表中的重复记录
      比如 deleteMul(tableName)
          调用 deleteMul('emp'); 必须删除表emp的重复数据  （execute immediate    using )
*/
create or replace procedure deleteMul(tableName varchar2)
as
    mycursor sys_refcursor;
    v_sql varchar2(2000):='select t.column_name from user_col_comments t where t.table_name = ';
    v_colnames user_col_comments.column_name%type;
    leim varchar2(2000);
begin
    v_sql:=v_sql||''''||tableName||'''';
    open mycursor for v_sql;
    loop
        fetch mycursor into v_colnames;
        exit when mycursor%notfound;
        leim:=leim||v_colnames||',';
    end loop;
    close mycursor;
    leim:=substr(leim,1,length(leim)-1);
    execute immediate 'delete from '||tableName||' e1 where  e1.rowid not in (select max(rowid) from '||tableName||' e2 group by '||leim||')';
    commit;
end;
/*
5.写出一个分页的存储过程
  定义如下
     tablePager(tableName,curPage,pageSize)
  调用 
     tablePager('Emp',2,10) 
     查询emp表中 第二页的数据（每页显示10条 第二页就是 10-20条）
*/
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;
       syso(myResult);
    end loop;
    close mycursor;
end;