oracle测试小例子[你会几个?]

1.创建一个名称为test的用户，并授权其连接数据库和创建表的权限，同时授权访问另一个用户scott的表emp的权限

create user test identified by 111;
grant connect to test;
grant create table to test;
grant select on scott.emp to test; 

2. 针对scott用户emp表编写语句，只有当最低工资少于2000而且最高工资超过4000时，才显示部门ID以及该部门的最低工资和最高工资

select deptno,min,max from(select min(sal) min,max(sal) max,deptno from emp group by deptno)
where min<2000 and max >4000;

3. 公司决定发年终奖，根据职员的所在部门来计算，如果部门为10的，奖金为2000，部门为20的，奖金为1500，部门为30的奖金为1000，其他的奖金为500。编写一个过程来完成

create or replace procedure pro_addAward
as
begin
update emp set comm=0 where comm is null;
update emp set comm=comm+decode(deptno,10,2000,20,1500,30,1000);
end;

4. 编写代码，提示用户输入姓名。代码应检查用户输入的姓名的长度，如果长度小于2，则代码应引发异常并显示消息“请输入正确的姓名”。否则应显示消息“欢迎”。
create or replace function fun_login
(

--参数不能指定长度!!!!!!!!!
userName varchar2
)
return varchar2--必须有return
as
begin
if(length(userName)>2) then--必须有then
return '欢迎';
else
raise_application_error(-20000,'用户名输入不正确!');--必须有第一个参数
end if;
end;

declare
name varchar2(50):='&name';
begin
Dbms_output.put_line(fun_login(name));--调用函数
end;

5.编写代码，提示用户输入要查看的哪个表的记录，如果用户输入“E”，则显示emp表中empno，ename，job和sal列的值；如果用户输入“D”，则显示dept表中的

deptno，dname和loc列的值
create or replace procedure pro_showInfo
(
word varchar2,
cur_result out sys_refcursor
)
as
v_sql varchar2(500);
begin
case word
when 'E' then
open cur_result for select empno,ename,sal,job from emp;
when 'D' then
open cur_result for select deptno,dname,loc from dept;
else
select * from emp;
end case;
end;


declare
Type emp_record IS RECORD
(
empno emp.empno%type,
ename emp.ename%type,
sal emp.sal%type,
job emp.job%type
);
Type dept_record IS RECORD
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
cur_word sys_refcursor;
dept_rec dept_record;
emp_rec emp_record;
word varchar2(10):='&word';
begin
pro_showInfo(word,cur_word);
case word
when 'E' then
Dbms_output.put_line('empno          ename         sal          job');
loop
fetch cur_word into emp_rec;
exit when cur_word%notfound;
Dbms_output.put_line(emp_rec.empno||'          '||emp_rec.ename||'          '||emp_rec.sal||'           '||emp_rec.job);
end loop;
when 'D' then
Dbms_output.put_line('deptno          dname          loc');
loop
fetch cur_word into dept_rec;
exit when cur_word%notfound;
Dbms_output.put_line(dept_rec.deptno||'          '||dept_rec.dname||'          '||dept_rec.loc);
end loop;
else
Dbms_output.put_line('不存在!');
end case;
close cur_word;
end;

6.编写一个程序包，包有两个过程和一个函数，第一个过程根据职员编号打印职员姓名，第二个过程根据职员编号打印职员薪水，函数根据职员编号返回职员的入职时间。(此处只完成：第一个过程根据职员编号打印职员姓名，其它例似)

create or replace package emp_package
is
procedure proc_printNamebyEmpNo
(
param_empNo number
);--分号不能掉！！！
end emp_package;

create or replace package body emp_package
as
procedure proc_printNamebyEmpNo
(
param_empNo number
)
as--这里不是;了。
ename emp.ename%type;
empno emp.empno%type;
begin
select a.ename into ename from emp a where a.empno=param_empno;
Dbms_output.put_line('编号为：'||param_empno||'的员工姓名为：'||ename);
exception--捕获异常
when no_data_found then
Dbms_output.put_line('不存在编号为'||param_empno||'的员工!');
end;
end emp_package;

declare
empno varchar2(50):='&empno';
begin
emp_package.proc_printNamebyEmpNo(empno);--调用函数
end;

7.创建一个触发器，无论用户插入新记录，还是修改EMP表的job列，都将用户指定的job列的值转换成大写

create or replace trigger tr_upper_job
before insert or update on emp
for each row
begin
:NEW.job:= upper(:NEW.job);--此处用select into 和set会出错。直接赋值更为简单。
end;

8.将用户scott的所有对象导入到用户test下。

exp scott/111@ORCL file=d:\daochu.dmp full=y
imp scce/111@ORCL file=d:\daochu.dmp fromuser=scott touser=scce ignore=y constraints=y grants=y