/* 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;
oracle的几个练习题
最新推荐文章于 2021-02-04 17:25:49 发布