oracle的几个练习题

/* 1、某表存储了某足球联赛最终排名及积分，表结构及数据如下， TeamRank(rk NUMBER,team VARCHAR2(10),cent NUMBER) 排名 球队 积分 ---- ---------- ---------- ---------------------------- 1 甲 100 2 乙 98 3 丙 89 4 丁 72 要求，通过SELECT查询出球队的积分差距，结果如下： 排名 球队 积分 分差 ---- ---------- ---------- ------------------------------- 1 甲 100 2 乙 98 2 3 丙 89 11 4 丁 72 28 其实现的SQL语句应为： */ ----------- create table TeamRank( rk number, team varchar2(10), cent number ); insert into TeamRank values(1,'就',100); insert into TeamRank values(2,'就',98); insert into TeamRank values(3,'就',89); insert into TeamRank values(4,'就',72); / select rk as 排名,team as 球队,cent as 积分, case when cnt!=0 then cnt end as 分差 from( select rk,team,cent, max(cent)over()-cent as cnt from TeamRank ) / -------------------------------------------------- /* 2、查询scott员工表，按部门统计工资的前三位，参考结果如下： DEPTNO SAL1 SAL2 SAL3 ------ ---------- ---------- ---------- 10 5000 2450 1300 20 3000 2975 1100 30 2850 1600 1500 则，该语句为： */ --------------- select deptno, max(case rk when 1 then sal end) as SAL1, max(case rk when 2 then sal end) as SAL2, max(case rk when 3 then sal end) as SAL3 from( select deptno,sal, dense_rank()over(partition by deptno order by sal desc) rk from emp ) group by deptno; / ---------------------- /* 3、创建一个包，内含一个游标一个存储过程及一个函数，过程包括两个参数，分别为 IN及OUT类型,其中，输入参数为字符型，用于输入给定的表，输出参数为包的游标类型，用于输出该表的数据结果，写好后在PLSQL中调用并显示结果。 */ ------------------------ create or replace package test_package is type ref_cur_table is ref cursor; procedure getInfo(tableName in varchar2,v_result out ref_cur_table); function test return varchar2; end test_package; create or replace package body test_package is procedure getInfo(tableName in varchar2,v_result out ref_cur_table) is tt varchar2(100); begin tt:='select * from '||tableName; open v_result for tt; end; function test return varchar2 is begin return '这是个函数'; end; end test_package; declare v_result test_package.ref_cur_table; tt emp%rowtype; begin test_package.getInfo('emp',v_result); loop fetch v_result into tt; dbms_output.put_line(tt.empno||'----'||tt.ename); end loop; exception when others then dbms_output.put_line('输出完毕'); end; ---------------------- /* 4、创建一个函数，用于判断登录用户的帐号及密码是否正确，如果正确返回"欢迎光临本系统"，如果帐号错误，则给出"帐号错误！"提示，如果密码错误，则给出"密码错误！"提示，当连续3次密码错误，则锁定帐号，给出"该账号被锁定！"，锁定的帐号不能再登录，并且在锁定后再次尝试登录时，提示为"该帐号已经锁定，请与管理员联系！"。 */ --------------------- drop table tuser create table tuser( username varchar2(10) primary key, --用户名 pwd varchar2(10), --密码 isLock char(1), --是否锁定 err_cnt number --连续错误登录数 ); / insert into tuser values('wyt','123','n',0); / select login('wyt','13') from dual; select * from tuser; -------------------------------------------------- create or replace function login(v_username varchar2,pwd varchar2) return varchar2 is pragma autonomous_transaction; --设置为自制事务-- v_tuser tuser%rowtype; err_count number; begin select * into v_tuser from tuser where username=v_username; if v_tuser.isLock='y' then return '该帐号已经锁定，请与管理员联系!'; end if; if v_tuser.pwd=pwd then update tuser set err_cnt=0 where username=v_username; commit; return '欢迎光临本系统'; else update tuser set err_cnt=err_cnt+1 where username=v_username; commit; select err_cnt into err_count from tuser where username=v_username; if err_count=3 then update tuser set isLock='y' where username=v_username; commit; return '该账号被锁定！'; end if; return '密码错误！'; end if; exception when no_data_found then return '用户不存在！'; end;