oracle 简单编程题,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;