oracle简单编程题,Oracleplsql编程有点难度的动脑题

/*

题目一  在plsql中创建表cc 插入数据如下(列 c1,c2)&＃xff1a;

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(&＃39;1&＃39;,&＃39;西&＃39;);

insert into cc values(&＃39;1&＃39;,&＃39;安&＃39;);

insert into cc values(&＃39;1&＃39;,&＃39;的&＃39;);

insert into cc values(&＃39;2&＃39;,&＃39;天&＃39;);

insert into cc values(&＃39;2&＃39;,&＃39;气&＃39;);

insert into cc values(&＃39;3&＃39;,&＃39;好&＃39;);

commit;

--wm_concat

select c1,replace(wm_concat(c2),&＃39;,&＃39;,&＃39;&＃39;) from cc group by c1;

--plsql语句

declare myStr varchar2(20):&＃61;&＃39;&＃39;;

begin

for i in (select c1 from cc group by c1) loop

myStr:&＃61;&＃39;&＃39;;

for j in (select c2 from cc where c1&＃61;i.c1) loop

myStr:&＃61;myStr||j.c2;

end loop;

syso(i.c1||&＃39; &＃39;||myStr );

end loop;

end;

/

题目二

查找出输入的用户下&＃xff0c;每张表的记录数&＃xff0c;(提示&＃xff1a;使用 tab表)

以scott用户为例&＃xff0c;

结果应如下&＃xff1a;

DEPT...................................4

EMP...................................14

BONUS.................................0

SALGRADE.............................5

select * from tab --有权限查询的表

select * from user_tables  --scott 创建的表

select * from all_tables where owner&＃61;&＃39;SCOTT&＃39;

declare vSql varchar2(200);

rtrNumber number;

begin

for i in (select * from user_tables) loop

-- select count(rowid) from 表名

vSql:&＃61;&＃39;select count(rowid) from &＃39;||i.table_name;

execute immediate vSql into rtrNumber;

dbms_output.put_line(i.table_name || &＃39;...............................&＃39; ||rtrNumber);

end loop;

end;

/*

题目 三

加载在c盘存在一个文件 a.txt

内容如下

100|金融部|深圳

110|财务部|东莞

使用plsql读取该文件 并将 每行的 数据插入到dept表中 (读取一行 按|切割)

读取文件的代码

declare InHandle utl_file.file_type ; --定义文件类型

vNewLine VARCHAR2(250);  --定义变量获取当前行数据

BEGIN

vInHandle :&＃61; utl_file.fopen (&＃39;mydir&＃39;, &＃39;a,txt &＃39;, &＃39;R&＃39;);

utl_file.get_line (vInHandle, vNewLine); --调用一次 读取一行

*/

declare vInHandle utl_file.file_type ; --定义文件类型

vNewLine VARCHAR2(250);  --定义变量获取当前行数据

begin

vInHandle:&＃61; utl_file.fopen (&＃39;MYDIR&＃39;, &＃39;a1.txt&＃39;, &＃39;R&＃39;);

loop

utl_file.get_line (vInHandle, vNewLine); --调用一次 读取一行

--dbms_output.put_line(vNewLine);

--&＃xfeff;100|金融部|深圳

declare a1 varchar2(20);

a2 varchar2(20);

a3 varchar2(20);

beginIdx number:&＃61;1;

begin

beginIdx:&＃61;instr(vNewLine,&＃39;|&＃39;); --4

-- 1号位置开始到 找到一个 |的位置结束的字符串

a1:&＃61;substr(vNewLine,1,instr(vNewLine,&＃39;|&＃39;)-1);

--从第一个 |的下一个位置开始 5

a2:&＃61;substr(vNewLine,beginIdx&＃43;1,instr(vNewLine,&＃39;|&＃39;,beginIdx&＃43;1)-beginIdx-1);

beginIdx:&＃61;instr(vNewLine,&＃39;|&＃39;,beginIdx&＃43;1);

a3:&＃61;substr(vNewLine,beginIdx&＃43;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(&＃39;Cleck&＃39;,null,null);

查询的sql为

select * from emp where ename like &＃39;%Cleck%&＃39;;

query(&＃39;Cleck&＃39;,&＃39;Manager&＃39;,null);

查询的sql为

select * from emp where ename like &＃39;%Cleck%&＃39; and job like &＃39;%Manager%&＃39;

要求输出查询的结果

*/

create or replace procedure queryEmp(ename varchar2,job varchar2,sal number)

as

vSql :&＃61;&＃39;select * from emp where 1&＃61;1 &＃39;;

begin

if(ename is not null) then

vSql&＃xff1a;&＃61;vSql|| &＃39;and ename&＃61;&＃39;&＃39; &＃39;||ename||&＃39;&＃39;&＃39;&＃39;;

end if;

if(job is not null) then

vSql&＃xff1a;&＃61;vSql|| &＃39;and job&＃61;&＃39;&＃39; &＃39;||job||&＃39;&＃39;&＃39;&＃39;;

end if;

if(sal is not null) then

vSql&＃xff1a;&＃61;vSql|| &＃39;and sal&＃61;&＃39;&＃39; &＃39;||sal||&＃39;&＃39;&＃39;&＃39;;

end if;

end;

queryEmp(null,&＃39;a&＃39;,&＃39;b&＃39;)

select * from emp where job&＃61;&＃39;a&＃39; and sal&＃61;&＃39;b&＃39;

select * from emp where 1&＃61;1 and job&＃61;&＃39;a&＃39; and sal&＃61;&＃39;b&＃39;

queryEmp(null,null,&＃39;b&＃39;)

select * from emp and sal&＃61;&＃39;b&＃39;

select * from emp where 1&＃61;1 and sal&＃61;&＃39;b&＃39;

3 写出一个分页的存储过程

定义如下

tablePager(tableName,curPage,pageSize)

调用

tablePager(&＃39;Emp&＃39;,2,10)

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

开始结束

(curPage-1)*pageSize&＃43;1 curPage*pageSize

create or replace procedure tablePager(tableName varchar2,curPage number,pageSize number)

as

starIndex number:&＃61;(curPage-1)*pageSize&＃43;1;

endIndx number:&＃61;curPage*pageSize;

vSql varchar2(2000):&＃61;&＃39;&＃39;;

colSql varchar2(2000):&＃61;&＃39;&＃39;;

mycursor sys_refcursor;

myResult varchar2(2000);

begin

select replace(wm_concat(column_name),&＃39;,&＃39;,&＃39;||&＃39;&＃39; &＃39;&＃39;||&＃39;) into colSql from user_tab_cols where table_name&＃61;tableName;

vSql:&＃61;&＃39;select &＃39;||colSql||&＃39; from (select t.*,rownum rn from &＃39;||tableName||&＃39; t) where rn>&＃61;&＃39;||starIndex||&＃39; and rn<&＃61; &＃39;||endIndx;

open mycursor for vSql;

loop

fetch mycursor into myResult;

exit when mycursor%notfound;--退出条件 下面会提到4个属性

syso(myResult);

end loop;

close mycursor;

end;

测试

begin

tablePager(&＃39;DEPT&＃39;,2,3);

end;

游标的四个属性

%ISOPEN&＃xff1a;是否打开

%ROWCOUNT&＃xff1a;行数

%FOUND&＃xff1a;boolean值&＃xff0c;是否还有数据

%NOTFOUND&＃xff1a;是否已无数据

4 定义一个存储过程 传入表名

删除该表中的重复记录

比如 deleteMul(tableName)

调用 deleteMul(&＃39;emp&＃39;); 必须删除表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):&＃61;&＃39;&＃39;;

begin

select wm_concat(column_name) into colSql from user_tab_cols where table_name&＃61;tableName;

execute immediate &＃39;delete from &＃39;||tableName||&＃39; where rowid not in(select max(rowid) from

&＃39;||tableName||&＃39; group by &＃39;||colSql||&＃39;)&＃39;;

end;

测试

begin

deleteMul(&＃39;CC&＃39;);

end;