1:给 scott.dept创建公有同义词syn_dept,使用同义词-查询部门编码、部门名称和地点
CREATE public SYNONYM dept_syn FOR SCOTT.dept
2:创建序列seq_student,初始值是1000.增量为5,最大值为2000,循环,缓存为10个序列号
create sequence seq_student
start with 1000
increment by 5
maxvalue 2000
cache 10
3:创建student表,字段名为sid,number(4),sname,sid的值通过序列seq_student获得
create table student
sid number(4),saname varchar(20)/
insert into student(sid,sname) values(seq_student.nextval,'张')
4:基于emp表创建视图含20号部门的员工编号、姓名、年薪 (view20_emp)
create or replace view view20_emp
as select empno,ename,job,sal from emp
where deptno=20
with check option constraint chk_20
5.采用PL/SQL编程,在 emp表中,如果部门号 (deptno)为30,且工作(job)为’MANAGER’ 的员工,则工资(sal)提高50%,如果部门号(deptno)为30,且工资job不为‘MANAGER ‘,则工资(sal) 提高 10%,其它员工工资提高20%.
declare
v_deptno emp.deptno%type;
v_sal emp.sal%type;
v_job emp.job%type;
v_empno emp.empno%type:=7788;
begin
select deptno,sal,job into v_deptno,v_sal,v_job from emp where empno=7788 ;
if v_job<'MANAGER' then
update emp set sal=sal*1.5;
elsif v_deptno='30' and v_job!='MANAGER' then
update emp set sal=sal*1.1;
else
update emp set sal=sal*1.2;
end if;
end;
6.使用显式游标,根据员工工资(sal)参数,查询员工表emp中工资大于等于该参数的员工信息,显示内容包括员工编码(empno)、姓名(ename)、工资(sal)。
(for循环)
declare
cursor cursor_emp(inputsal number) is
select empno,ename,sal from emp where sal>inputsal;
empno emp.empno%type;
ename emp.ename%type;
sal emp.sal%type;
begin
sal:=&sal;
for rec in cursor_emp(sal) loop
dbms_output.put_line('编号:'||rec.empno||'姓名:'||rec.ename||'工资:'||rec.sal);
end loop;
end;
显示游标
declare
cursor cursor_emp(inputsal number) is
select empno,ename,sal from emp where sal>inputsal;
empno emp.empno%type;
ename emp.ename%type;
sal emp.sal%type;
begin
sal:=&inputsal;
open cursor_emp(sal);
loop
fetch cursor_emp into empno,ename,sal;
exit when cursor_emp%NOTFOUND;
dbms_output.put_line('编号:'||empno||'姓名:'||ename||'工资:'||sal);
end loop;
close cursor_emp;
end;
7.使用for循环游标查询并打印薪水大于2000并且职位是’manager’ 的员工编号(empno)、员工姓名(ename)、员工职位 (job)、员工薪水(sal)。
(for循环)
declare
cursor cursor_emp is
select job,ename,sal from emp where sal>'2000' and job='MANAGER';
job emp.job%type;
ename emp.ename%type;
sal emp.sal%type;
begin
for rec in cursor_emp loop
dbms_output.put_line(rec.job||':'||rec.ename||':'||rec.sal);
end loop;
end;
8创建程序包,包含一个过程和一个函数。
过程:计算每个员工的年薪并输出。
create or replace procedure SALCHANGE is
cursor cursor_salchange is select ename,sal*12 from emp;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open cursor_salchange;
loop
fetch cursor_salchange into v_ename,v_sal;
exit when cursor_salchange%NOTFOUND;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
close cursor_salchange;
end SALCHANGE;
函数:以部门编号为参数,返回该部门的平均工资。
create or replace function avgsal(deptno in number) return integer
As
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename into v_ename from emp where sal=(select avg(sal) from emp where deptno=deptno);
dbms_output.put_line(v_ename||'的平均工资为:'||v_sal);
return v_ename;
end avgsal;