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