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

9wah32mt17

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_addAwardasbeginupdate 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--必须有returnasbeginif(length(userName)>2) then--必须有thenreturn '欢迎';elseraise_application_error(-20000,'用户名输入不正确!');--必须有第一个参数end if;end;declarename varchar2(50):='&name';beginDbms_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)asv_sql varchar2(500);begincase wordwhen 'E' thenopen cur_result for select empno,ename,sal,job from emp;when 'D' thenopen cur_result for select deptno,dname,loc from dept;elseselect * from emp;end case;end;declareType 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';beginpro_showInfo(word,cur_word);case wordwhen 'E' thenDbms_output.put_line('empno          ename         sal          job');loopfetch 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' thenDbms_output.put_line('deptno          dname          loc');loopfetch 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;elseDbms_output.put_line('不存在!');end case;close cur_word;end;

　　6.编写一个程序包，包有两个过程和一个函数，第一个过程根据职员编号打印职员姓名，第二个过程根据职员编号打印职员薪水，函数根据职员编号返回职员的入职时间。(此处只完成：第一个过程根据职员编号打印职员姓名，其它例似)create or replace package emp_packageisprocedure proc_printNamebyEmpNo(param_empNo number);--分号不能掉！！！end emp_package;create or replace package body emp_packageasprocedure proc_printNamebyEmpNo(param_empNo number)as--这里不是;了。ename emp.ename%type;empno emp.empno%type;beginselect a.ename into ename from emp a where a.empno=param_empno;Dbms_output.put_line('编号为：'||param_empno||'的员工姓名为：'||ename);exception--捕获异常when no_data_found thenDbms_output.put_line('不存在编号为'||param_empno||'的员工!');end;end emp_package;declareempno varchar2(50):='&empno';beginemp_package.proc_printNamebyEmpNo(empno);--调用函数end;

　　7.创建一个触发器，无论用户插入新记录，还是修改EMP表的job列，都将用户指定的job列的值转换成大写
create or replace trigger tr_upper_jobbefore insert or update on empfor each rowbegin:NEW.job:= upper(:NEW.job);--此处用select into 和set会出错。直接赋值更为简单。end;


　　8.将用户scott的所有对象导入到用户test下。
exp scott/111@ORCL file=d:\daochu.dmp full=yimp scce/111@ORCL file=d:\daochu.dmp fromuser=scott touser=scce ignore=y constraints=y grants=y