oracle数据库练习题
sqlplus sys/111111 as sysdba
show parameter name;
--建立test表空间
create tablespace test datafile 'D:\app/test.dbf' size 50m maxsize 1g autoextend on;
show parameter name; --显示数据库名，实例名等
show parameter db_name; --显示数据库名
show parameter instance;
select *from v$database;
--查看实例信息
select *from v$instance;
--查看实例名
select name from v$database;
select instance_name from v$instance;
show parameter instance
--查看用户
select *from dba_users;
--建用户授权
create user test identified by 111111;
grant connect,resource to test;
conn test/111111;
drop user test cascade;
conn /as sysdba;
drop user test cascade;
--test有connect的管理权限，可将connect 权限赋给另一个人
create user test identified by 111111;
grant connect,resource to test with admin option;
--查询系统权限
select *from sys.dba_sys_privs; //查询所有用户权限
select *from user_sys_privs;//查询sys用户权限
--回收权限
show user;
conn /as sysdba;
revoke connect from test;
--解锁用户
sqlplus sys/111111 as sysdba
show user;
conn scott/tiger;
show user; //显示没有用户
alter user scott account unlock; //退出重新登录,在有用户的时候登
conn scott/tiger; //连接之后输入新密码
--解锁后用plsql登录 scott/tiger 用户
select *from user_tables; //查询表名
select * from dept; //dept部门表
select emp.* from emp; //emp员工表
select *from emp;
select *from bonus; //bonus奖金表
select *from salgrade; //收入等级表
select * from dept;
select distinct dname from dept;
select dname from dept;
select empno,ename from emp ;
select empno,ename from emp where ename = 'SMITH';
select ename from emp where ename = 'SMITH';
select *from emp where empno between 7369 and 7830;
select *from emp where empno like '%99';
select empno,ename from emp where ename = 'SMITH';
select *from emp where empno like '%56%';
select *from emp where empno in(7499,8000);
select *from emp where empno in(7499,7839);
select *from emp where empno is null;
select *from emp where empno is not null;
select *from emp where empno >= '7521';
select *from emp where rownum <= 5;
select empno,ename,sal+300 from emp where rownum <= 10;
select empno as0000 from emp;
select empno www from emp;
select empno as wwww from emp where rownum <= 10;
select *from emp;
select empno ,ename,job,mgr aaaa from emp order by empno desc;
select deptno from emp group by deptno;
select empno,ename||'m' from emp where rownum <= 5;
select empno,ename||'+'||sal from emp;
select deptno,max(sal) from emp group by deptno;
select deptno,substr(ename,5) from emp where rownum <= 5;
select *from emp;
insert into emp(empno,deptno) values(7368,10);
insert into emp(empno,deptno) values(7368,50); //部门表的部门编号只有10,20,30，报错
delete from dept where deptno = 10; //删除父表中的记录，子表中有记录报错
1.查询姓名首字母为A或第二个字符为A的所有员工信息
//错误
select *from emp;
select *from emp where substr(ename,1,1) = 'a' or substr(ename,2,1)='a';
select *from emp where ename like 'A%' or like '_A%';
//修正
select *from emp;
select *from emp where substr(ename,1,1) = 'A' or substr(ename,2,1)='A';
select *from emp where ename like 'A%' or ename like '_A%';
2.查询部门20,30 中的岗位不是"CLERK"或"SALESMAN"的所有员工信息
select *from emp where job not in('CLERK');
select *from emp where job not in('SALESMAN');
select *from emp;
select *
from emp
where job<>'CLERK'
and job<>'SALESMAN'
and deptno in (20, 30);
select *
from emp
where job not in ('CLERK','SALESMAN')
and deptno in (20, 30);
3.查询工资在2500-3500之间,1981年入职,没有奖金的所有员工信息
select *from emp where sal between 2500 and 3500 and to_char(hiredate,'YYYY-MM-DD') like '1981%' and comm is null;
--用了子查询还有extract函数
select *from (select *from emp where sal between 2500 and 3500 and comm is null)where extract(year from hiredate) = '1981';
select *from emp where extract (year from hiredate) = '1981';
4.查询比平均员工工资高的员工信息
//错误
select *from emp where sal > avg(sal);
//正确
select *from emp where sal > (select avg(sal) from emp);
select avg(sal) from emp; //是子查询
5.查询平均工资高于2000的部门信息 //select avg(sal) from emp 是 所有人平均工资
--1.先找到平均工资大于两千的部门编号，即每个部门的平均工资大于两千的先找出来
select deptno,avg(sal) from emp by deptno having avg(sal) >2000
--2.根据部门编号查找部门信息
select deptno,dname,loc from dept where deptno in(select deptno from emp group by deptno having avg(sal) >2000 );
6.查询出WARD的工作所在地
//这边用in不太好,in是个范围,已经确定了姓名，用等号也不太好
select loc from dept where deptno in (select deptno from emp where ename = 'WARD');
--用两个表的自然链接
select ename,dept.loc from dept,emp where emp.deptno = dept.deptno and ename = 'WARD';
7.查询出工资比ADAMS高的所有人的姓名、部门、所在地
select ename,dname,loc from dept,emp where emp.deptno = dept.deptno and emp.sal > (select sal from emp where ename = 'ADAMS' );
8.--查询工资排名第7的员工信息
//将员工工资先降序排列，查出前7条数据，取前7条数据中的最小工资
select sal from emp order by sal desc;
select *from emp where sal = (select min(sal) from (select *from emp order by sal desc)where rownum <= 7 )
//利用minus将两个降序结果集相减,union则是将两个结果集相并
select *from emp order by sal desc;
select sal from (select *from emp order by sal desc )where rownum <= 7;
select sal from (select *from emp order by sal desc )where rownum <= 6;
select *
from emp
where sal = (select sal
from (select * from emp order by sal desc)
where rownum <= 7 minus
select sal
from (select * from emp order by sal desc)
where rownum <= 6);
//oracle函数
select ename,to_char(hiredate,'YYYY_MM_DD HH:MM:SS') from emp;
select ename,hiredate from emp; //显示日历格式
select to_date('2016-11-23','YYYY-MM-DD') from dual;
select sysdate,add_months(sysdate,3),add_months(sysdate,2)from dual;
select sysdate,last_day(sysdate),last_day(sysdate)+1 from dual;
select
months_between('17-9月-2013','17-9月-2013')as 第一个结果是,
months_between('17-10月-2013','17-8月-2013')as 第二个结果是,
months_between('17-8月-2013','17-10月-2013')as 第三个结果是
from dual;
select
next_day(sysdate,'星期一 ')as第一个结果是,
next_day('18-9月-2013','星期一')as第二个结果是
from dual;
1.查询与部门20岗位不同的员工姓名、工资 (岗位是job)
//错误
select ename,sal,deptno from emp where deptno != 20;
select ename,sal,deptno from emp where deptno not in (select deptno from dept where deptno = 20);
//正确
select ename,sal,job from emp where job not in (select job from emp where deptno = 20);
2.查询与SMITH部门、岗位完全相同的员工姓名、工作、工资
select *from emp;
select deptno,job from emp where ename = 'SMITH'
select ename, job, sal
from emp
where deptno = (select deptno from emp where ename = 'SMITH')
and job = (select job from emp where ename = 'SMITH')
and ename <> 'SMITH'
--查询emp表中的所有信息
select *from emp;
--显示emp表的员工姓名和工资
select ename,sal from emp;
--查询emp表中的部门编号为20的并且sal(工资)大于3000的所有员工信息
select *from emp where deptno = 20 and sal > 3000;
--查询emp表中部门编号为20的或者sal(工资)大于3000的所有员工信息
select *from emp where deptno = 20 or sal > 3000;
--使用between and查询工资在2000和4000之间的员工(用and重新实现)
select *from emp where sal between 2000 and 4000;
select *from emp;
select sal from emp where deptno = 20
select *from emp where empno < any (select empno from emp where deptno = 20) and deptno != 20;
select *from emp where empno < all (select empno from emp where deptno = 20) and deptno != 20;
--等值链接 (deptno对于emp表是外键,对于dept表是主键)
select empno,emp.deptno,sal from emp,dept where emp.deptno = dept.deptno;
--内连接 即等值连接//这些连接在照片上
select empno,emp.deptno,dept.deptno from emp,dept where emp.deptno = dept.deptno;
--左外连接
select empno,emp.deptno,loc from emp,dept where emp.deptno = dept.deptno(+);
select empno,e.deptno,loc from emp e left join dept d on e.deptno = d.deptno;
--右外连接
select empno,emp.deptno,loc from emp,dept where emp.deptno(+) = dept.deptno;
select empno,e.deptno,loc from emp e right join dept d on e.deptno = d.deptno;
left join 与 right join多用于生成的视图上
--全连接
--笛卡尔积--不带条件(效率十分低下，行数为几个表行数之积)
select empno,emp.deptno,sal from emp,dept;
--使用in查询部门编号10，20的所有员工
select *from emp where deptno in(10,20);
--使用like查询所有名字中包括w的员工信息
//错误,W是大写的
select *from emp where ename like '%w%'
select *from emp where ename like '%w' or ename like 'w%' or ename like '%w%';
//正确
select *from emp where ename like '%W' ;
--使用like查询所有员工名字中第二个字母为w的员工信息
select *from emp where ename like '_W%';
--查询所有员工信息并按照部门编号和工资进行排序
//错误
select *from emp order by deptno and emp order by sal;
//正确
select *from emp order by deptno ,sal;
select ename,deptno,sal from emp order by 2,3; //先对2进行排序,也就是对deptno进行排序
select ename,deptno,sal from emp order by sal;
select deptno,sal from emp order by 1,2;
--显示所有员工工资上浮%20的结果
select *from emp;
select ename,sal*1.2 new_sal, sal from emp;
--显示emp表的员工姓名以及工资和奖金的和
//错误
select ename ,sal,comm,sal+comm sum from emp;
select ename ,sal,comm,sal||'+'||comm sum from emp;
//正确
select ename ,sal,comm,sal||comm sum from emp;
select ename ,sal,comm,sal+nvl(comm,0) sum from emp; //nvl转换函数
--显示dept表的内容，使用别名将表头转成中文显示 (表头：deptno,dname,loc)
select *from dept;
select deptno 部门编号,dname 部门名字,loc 工作地点 from dept;
--查询员工姓名和工资，并按工资从小到大排序
select ename,sal from emp order by 2;
--查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示
select *from emp;
select ename ,to_char(hiredate,'YYYY-MM-DD') from emp order by 2 desc;
select ename ,hiredate from emp order by 2 desc;
--查询员工信息,先按部门标号从小到大排序，再按雇佣时间的先后排序
select *from emp order by deptno,to_char(hiredate,'YYYY-MM-DD');
select *from emp order by deptno,hiredate;
--11列出薪金高于公司平均工资的所有员工，所在部门,上级领导,公司的工资等级//所有员工是所有员工信息吗
select * from emp;
select e1.ename, dname, e2.ename mgr, grade
from emp e1, dept, emp e2, salgrade
where e1.mgr = e2.empno(+)
and e1.deptno = dept.deptno
and e1.sal > (select avg(sal) from emp)
and e1.sal between losal and hisal;
select * from emp;
--12列出与scott从事相同工作的所有员工及部门名称 //没有用到什么为空,用等值连接即可
select * from dept;
--用等值连接
select distinct ename, dname, job
from emp, dept
where emp.deptno = dept.deptno
and job = (select job from emp where ename = 'SCOTT');
--13列出薪金大于部门30中的任意员工的薪金的所有员工的姓名和薪金
/ / 错误
select ename, sal
from emp
where sal > (select sal from emp where deptno = 30) / / 正确
select ename, sal
from emp
where sal > (select min(sal) from emp where deptno = 30);
/ / 可以用any
--14列出薪金大于部门30中的全部员工的薪金的所有员工的姓名和薪金，部门名称 //用any和all实现
select ename, sal, dname, emp.deptno
from emp, dept
where emp.deptno = dept.deptno
and sal > (select max(sal) from emp where deptno = 30);
--15列出每个部门的员工数量，平均工资
/ / 正确
select dept.deptno, count(ename), trunc(avg(sal + nvl(comm, 0)))
from emp, dept
where dept.deptno = emp.deptno(+)
group by dept.deptno;
/ / 错误
select dept.deptno, count(*), avg(sal)
from emp, dept
where dept.deptno = emp.deptno(+)
group by dept.deptno;
/ / 用left join为什么错
--16列出每个部门的员工数量,平均工资和平均服务期限(月)
select * from emp;
select deptno,
count(*),
trunc(avg(sal + nvl(comm, 0))),
/ / trunc函数截取小数 trunc(avg(sysdate - hiredate) / 30)
from emp
group by deptno;
--17.列出各种工作的最低工资及从事工资最低工作的雇员名称
select job, min(sal)
from emp
group by job
select * from emp;
/ / 如果emp表里有两个数据是800，a表里也有两个数据是800，不加 a.job = emp.job这个条件, 会输出4条数据
select ename, a.*
from (select job, min(sal) sal from emp group by job) a, emp
where a.sal = emp.sal
and a.job = emp.job;
--18求出部门名称带字符's'的部门员工，工资合计，部门人数
/ / 错误
select dept.deptno, count(ename)
from emp, dept
where emp.deptno(+) = dept.deptno
group by dept.deptno;
select ename, sal + nvl(comm, 0), a.count
from emp,
(select dept.deptno, count(ename) count
from emp, dept
where emp.deptno(+) = dept.deptno
group by dept.deptno) a,
dept
where dept.deptno = emp.deptno(+)
and a.deptno = dept.deptno
and dept.dname like '%s%';
/ / 正确
select * from emp;
select * from dept;
select dname,
dept.deptno,
ename,
nvl2(comm, sal + comm, sal) 工资合计,
a.count 部门人数
from emp,
(select deptno, count(ename) count from emp group by deptno) a,
dept
where dept.deptno = emp.deptno(+)
and a.deptno(+) = emp.deptno
and dept.dname like '%S%';
--19.求出部门平均工资以及等级
select * from salgrade;
select avg(sal) sal, deptno
from emp
group by deptno
select grade, a.sal
from salgrade,
(select avg(sal) sal, deptno from emp group by deptno) a
where a.sal between losal and hisal;
--20不使用函数查询工资最高人的信息
/ / 错误
select *
from emp
where sal = (select sal
from (select sal from emp order by sal desc)
where rownum = 2);
select sal from emp order by sal desc;
select sal from (select sal from emp order by sal desc) where rownum <= 5;
/ / 正确
select * from emp where sal >= all (select sal from emp);
--21求出平均工资最高的部门名称
/ / 错误
select avg(sal), e.deptno from emp e group by e.deptno;
select dname
from emp, (select avg(sal) b, e.deptno from emp e group by e.deptno) a
where emp.deptno = a.deptno
and emp.deptno in (select a.deptno from a where a.b = max(a.b));
select dname
from dept
where deptno =
(select deptno
from (select deptno from emp group by deptno order by avg(sal) desc)
where rownum = 1);
--22求平均工资的等级最低的部门名称//学会用with as
select * from salgrade;
--求出部门平均工资以及等级
select a.deptno, grade, a.sal
from salgrade, (select avg(sal) sal, deptno from emp group by deptno) a
where a.sal between losal and hisal;
----等级最低
select min(grade)
from (select a.deptno, grade, a.sal
from salgrade,
(select avg(sal) sal, deptno from emp group by deptno) a
where a.sal between losal and hisal) b;
--等级最低的部门编号
select deptno
from (select a.deptno, grade, a.sal
from salgrade,
(select avg(sal) sal, deptno from emp group by deptno) a
where a.sal between losal and hisal) b
where b.grade =
(select min(grade)
from (select a.deptno, grade, a.sal
from salgrade,
(select avg(sal) sal, deptno from emp group by deptno) a
where a.sal between losal and hisal) b);
--依据部门编号查找部门姓名
/ / 错误
select dname
from dept,
(select a.deptno, grade, a.sal
from salgrade,
(select avg(sal) sal, deptno from emp group by deptno) a
where a.sal between losal and hisal) b
where dept.deptno = b.deptno
and dept.deptno =
(select b.deptno from b where b.grade = (select min(grade) from b));
/ / 正确
select dname
from dept
join (select deptno, grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade on (t.avg_sal between salgrade.losal and
salgrade.hisal)) t on dept.deptno = t.deptno
where t.grade = (select min(grade)
from (select avg(sal) avg_sal from emp group by deptno) t
join salgrade on (t.avg_sal between salgrade.losal and
salgrade.hisal));
--23部门经理人中平均工资最低的部门名称
select dname
from (select deptno, avg(sal) avg_sal
from emp
where empno in (select mgr from emp)
group by deptno) t
join dept on t.deptno = dept.deptno
where avg_sal = (select min(avg_sal)
from (select avg(sal) avg_sal
from emp
where empno in (select mgr from emp)
group by deptno) t);
--创建视图
--如果创建视图的时候出现权限不足,则进行授权操作 show user; //出现sys用户 grant create view to scott;
create or replace view empdetail as
select empno, ename, job, hiredate, emp.deptno, dname
from scott.emp
join scott.dept on emp.deptno = dept.deptno with read only;
select * from empdetail;
select * from empdetail;
drop view empdetail;
--创建序列
create sequence seq_test start with 1 increment by 1 cache 30;
select sql_test.nextval from dual;
select sql_test.nextval from dual;
.. .
--创建序列
select rowid, emp.* from emp;
/ / rowid数据块的唯一标识
-- Create/Recreate indexes
create index uk on TEXT(test_name);
/ / 创建索引(normal索引) create index UK on TEXT(test_name, test_id) ; / / 创建组合索引
--plsql块
declare i integer;
begin
i := 1;
dbms_output.put_line(i);
end;
-- Created on 2017/11/3 by ZHAOXIAOFENG
declare
-- Local variables here
i integer;
begin
-- Test statements here
for i in 1 .. 3 loop
dbms_output.put_line(i);
end loop;
end;
declare
-- Local variables here
sname VARCHAR2(20) := "jerry";
begin
-- Test statements here
sname := sname || "and tom";
dbms_output.put_line(sname);
end;
/ / 在命令窗口输入的话加set serveroutput on set serveroutput on declare sname VARCHAR2(20) DEFAULT "jerry";
-- Local variables here
begin
select ename into sname from scott.emp where empno = 7934;
-- Test statements here
dbms_output.put_line(sname);
end;
/ / 变量初始化时，可以使用DEFAULT关键字对变量进行初始化 / / 使用select .. .into语句对 变量sname赋值, 要求查询的结果必须是一行, 不能是多行或者没有记录，用游标的方式对多行进行处理
-- Created on 2017/11/3 by ZHAOXIAOFENG
declare
-- Local variables here
pi constant number := 3.14;
r number default 3;
area number;
i integer;
begin
-- Test statements here
area := pi * r * r;
dbms_output.put_line(area);
end;
/*常量用constant
数据块常用数据类型
varchar2,number,date,boolean//布尔
引用数据库一行作为数据类型，即record类型(是plsql附加的数据类型)*/
-- %ROWTYPE类型
declare
myemp scott.emp%ROWTYPE; --myemp和soctt.emp这张表的数据类型一样
begin
--select into结果查询必须是一行,不能是多行
select * into myemp from scott.emp where empno = 7934;
--查询过后myemp变成了一条记录即 select * from scott.emp where empno = 7934;
dbms_output.put_line(myemp.ename);
dbms_output.put_line(myemp.deptno);
dbms_output.put_line(myemp.sal);
end;
--%type(某列的类型)
declare
sal scott.emp.sal%type;
mysal number(4) := 3000;
totalsal mysal%type;
begin
select SAL into sal from scott.emp where empno = 7934;
totalsal := sal + mysal;
dbms_output.put_line(totalsal);
end;
--查询JAMES工资,如果大于900,则发奖金800
declare
newsal scott.emp.sal %type;
begin
select sal into newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
if newsal > 900 then
update scott.emp set comm = 800 where ename = 'JAMES';
end if;
select comm INTO newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
commit;
end;
--if-then-else-endif
declare
newsal scott.emp.sal %type;
begin
select sal into newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
if newsal < 900 then
update scott.emp set comm = 800 where ename = 'JAMES';
else
update scott.emp set comm = 400 where ename = 'JAMES';
end if;
select comm INTO newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
commit;
end;
--if-then-elsif-then-else-end if
declare
newsal scott.emp.sal %type;
begin
select sal into newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
if newsal > 1500 then
update scott.emp set comm = 800 where ename = 'JAMES';
elsif newsal > 900 then
update scott.emp set comm = 400 where ename = 'JAMES';
else
update scott.emp set comm = 200 where ename = 'JAMES';
end if;
select comm INTO newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
commit;
end;
--if-then-elsif-then-else-end if
declare
newsal scott.emp.sal %type;
begin
select sal into newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
if newsal >1500 then
update scott.emp set comm = 800
where ename = 'JAMES';
elsif newsal > 900 then
update scott.emp set comm = 400
where ename = 'JAMES';
else
update scott.emp set comm = 200
where ename = 'JAMES';
end if;
select comm INTO newsal from scott.emp where ename = 'JAMES';
dbms_output.put_line(newsal);
commit;
end;
--case 语句
declare
v_grade char(1):= upper('&p_grade'); --upper函数用来输入一个变量
begin
case v_grade
when 'A' then
dbms_output.put_line('Excellent');
when 'B' then
dbms_output.put_line('Good');
when 'C' then
dbms_output.put_line('Very Good');
else
dbms_output.put_line('No such grade');
end case;
end;
--loop循环控制
--1+2+..+100
declare
i number(3) := 0;
sumresult number := 0;
begin
loop
i := i + 1;
sumresult := sumresult + i;
if i >= 100 then
exit;
end if;
end loop;
dbms_output.put_line ('result is :'||to_char(sumresult));
end;
--while 循环
declare
i number(3) := 0;
sumresult number := 0;
begin
while i < 100 loop
i := i + 1;
sumresult := sumresult + i;
end loop;
dbms_output.put_line ('result is :'||to_char(sumresult));
end;
--for 循环
declare
i number(3) := 0;
sumresult number := 0;
begin
for i in 1..100 loop
sumresult := sumresult + i;
end loop;
dbms_output.put_line ('result is :'||to_char(sumresult));
end;
begin
update scott.emp set sal=sal+100 where empno =7876;
if SQL%found then
dbms_output.put_line('成功修改雇员工资');
else
dbms_output.put_line('修改雇员工资失败');
end if;
end;
--动态sql
/*declare
sql_stmt varchar2(200); --动态sql语句
emp_id number(4) :=7566;
salary number(7,2);
dept_id number(2) :=90;
dept_name varchar2(14) := 'personnel';
location varchar2(13) :='DALLAS';
emp_rec scott.emp%rowtype;
begin
--无子句的execute immediate
execute immediate 'create table a(id number,amt number)';
--using子句中的execute immediate
sql_stmt := 'insert into scott.dept values(:1,:2,:3)';
execute immediate sql_stmt
using dept_id,dept_name,location;--用using 进行赋值
commit;
--into子句的execute immediate
sql_stmt := 'select *from scott.emp where empno = :id';
execute immediate sql_stmt
into emp_rec
using emp_id;
--returning into 子句中的execute immediate
sql_stmt := 'update scott.emp set sal = 2000 where empno = :1 returning sal into :2';
execute immediate sql_stmt
using emp_id
returning into salary;
execute immediate 'delete from scott.dept where deptno =: num'
using dept_id;
end;*/
//三种循环输出结果集
--用for 循环的好处不需要显示的open，fetch，close (游标就是指针,去不停地加1)
/*declare
--类型定义
cursor c_job is select empno,ename,job,sal from emp where job='MANAGER';
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;*/
--loop循环,fetch游标
/*declare
cursor cur is select * from emp;--声明游标
userinfo emp%rowtype;
begin
open cur;--打开游标
loop --指针不停地加
exit when cur%notfound; --当找不到的时候就退出
fetch cur into userinfo;--找到游标将游标变量插入到userinfo中
dbms_output.put_line('user id:' || userinfo.empno || '-' ||
'user name:' || userinfo.ename);
end loop;--emp表行数结束,end loop
exception
when others then
dbms_output.put_line(sqlerrm);
close cur;
end;*/
--while 循环
declare
--游标声明
cursor test is
--select语句
select * from emp;
--指定行指针
userinfo emp%rowtype;
begin
--打开游标
open test;
--找到游标将游标变量插入到userinfo中
fetch test into userinfo;
--测试是否有数据，并执行循环
while test%found loop
dbms_output.put_line('部门编号:' || userinfo.empno || '-' ||
'姓名' || userinfo.ename);
--给下一行喂数据
fetch test into userinfo;
end loop;
close test;
end;
--创建存储过程 on //进行输出
set serveroutput on
create or replace procedure proc(i in int) as
a varchar(20);
begin
for j in 1 .. i loop
a := a || '*';
dbms_output.put_line(a);
end loop;
end;
/
set serveroutput on
exec proc(5);
--在命令窗口显示 in
set serveroutput on
create or replace procedure proc(i out int)
as begin
i:=100;
dbms_output.put_line(i);
end;
/
declare a int;
begin
proc(a);
end;
/
--in out 类型
create or replace procedure proc(num1 in out int,num2 in out int)
as
temp int;
begin
temp := num1;
num1 := num2;
num2 := temp;
end;
/
declare
num1 int:=100;
num2 int:=200;
begin
proc(num1,num2);
dbms_output.put_line('num1是'||num1);
dbms_output.put_line('num2是'||num2);
end;
/
--函数
create or replace function fun1 return varchar2
is
begin
return 'hello,world!';
end;
/
set serveroutput on
declare
ss varchar2(20);
begin
ss:= fun1;
dbms_output.put_line(ss);
end;
/
drop function function_name;
--触发器
create or replace trigger tri1
after insert
on emp
for each row --每行都触发触发器
begin
dbms_output.put_line('触发器执行了');
end;
/
insert into emp(empno) values(7379);
--插入值为负,停止插入
create or replace trigger tri2
before insert
on emp
for each row --每行都触发触发器
begin
if :new.empno<0 then--new.empno是个变量，所以前面加:
raise_application_error(-20001,'员工编号为负,不能插到表中!');
end if;
end;
/
insert into emp(empno) values(-1);
dual系统表
--练习
--1.自定义输入任意员工编号,输出该员工编号、姓名、工资、部门、名称、所在地
declare
m_empno number;
m_ename varchar2(20);
m_sal number;
m_dname varchar2(50);
m_loc varchar2(30);
i number;
begin
i :=&i;
select empno,ename,sal,dname,loc into m_empno,m_ename,m_sal,m_dname,m_loc from emp
join dept on dept.deptno = emp.deptno where empno = i;
--dbms_output.put_line('编号'||m_empno||'姓名'||m_ename||'工资'||m_sal||'部门名称'||m_dname||'所在地'||m_loc);
dbms_output.put_line(m_empno || ' ' ||m_ename || ' ' ||m_sal || ' ' ||m_dname || ' ' ||m_loc);
end;
--2.自定义输入任意员工编号，如果该员工入职时间大于10年，则奖金加1W，如果大于5年，奖金加5000,否则奖金不加 //考虑如何正常输出奖金
--最终输出员工的编号、姓名、入职时间、原奖金、现奖金
declare
m_empno number;
m_ename varchar2(20);
m_hiredate date;
m_comm number;--原奖金
m_nowcomm number;--现奖金
m_year int;
i number;
begin
i :=7369;
select empno,ename,hiredate,comm into m_empno,m_ename,m_hiredate,m_comm from emp where empno = i;
select months_between(sysdate, hiredate) into m_year from emp where empno = i;--总的月份
dbms_output.put_line(m_empno||' '||m_ename||' '||m_hiredate||' '||m_comm||' '||m_nowcomm);
if m_year /12 > 10 then
update emp set comm = nvl(comm,0)+10000 where empno = i;--update语句的写法注意
elsif m_year/12 >=5 and m_year/12 <= 10 then
--elsif m_year/12 between 5 and 10 then
update emp set comm = nvl(comm,0)+5000 where empno = i;
end if;
--else null
--commit;
select comm into m_nowcomm from emp where empno = i;
dbms_output.put_line(m_empno||' '||m_ename||' '||m_hiredate||' '||m_comm||' '||m_nowcomm);
end;
--5.对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水,工资减50,显示原工资和现在的工资
--
SQL> set serveroutput on
SQL>
SQL> declare
2 m_empno emp.empno%type :='&i';
3 avgsal number;
4 now_sal number;
5 rt_emp emp%rowtype;--行类型
6 begin
7 select *into rt_emp from emp where empno = m_empno;
8 select avg(sal) into avgsal from emp group by emp.deptno having emp.deptno = rt_emp.deptno;
9 dbms_output.put_line('员工编号'||m_empno||'原工资'||rt_emp.sal||'现工资'||now_sal||'平均薪水'||avgsal);
10 if rt_emp.sal > avgsal then
11 --update emp set sal = sal - 50 where emp.empno =m_empno;
12 update emp set sal = rt_emp.sal - 50 where emp.empno =m_empno;
13 end if;
14 select sal into now_sal from emp where empno = m_empno;
15 commit;
16 --||'平均薪水'||avgsal --加上平均薪水有问题
17 dbms_output.put_line('员工编号'||m_empno||'原工资'||rt_emp.sal||'现工资'||now_sal);
18 end;
--视屏
declare
myempno emp.empno%type := '&ha';
empone emp%rowtype;--员工编号
avgsal number;
salone number;
--deptone dept%rowtype;--dept的行类型
begin
select * into empone from emp where empno = myempno;
--select * into deptone from dept where dept.deptno = empone.deptno;
select avg(sal)
into avgsal
from emp
group by emp.deptno
having emp.deptno=empone.deptno;
if empone.sal > avgsal then salone := empone.sal - 50;
end if;
update emp set emp.sal = salone where emp.empno = myempno;
commit;
dbms_output.put_line('员工编号：' || myempno || '姓名:' || empone.ename ||
'之前工资：' || empone.sal || '现在工资：' || salone||'平均工资'||avgsal);
end;
--6.创建一个存储过程，实现：通过输入员工编号查看员工姓名，工资、奖金
--如果输入的编号不存在，进行异常处理
--如果工资高于4000，进行异常提示处理
--如果奖金没有或为0，进行异常提示处理
set serveroutput on
create or replace procedure proc(i in int) as
a varchar(20);
begin
for j in 1 .. i loop
a := a || '*';
dbms_output.put_line(a);
end loop;
end;
/
set serveroutput on
exec proc(5);
create or replace procedure proc(m_empno in int)
as
rt_emp emp%rowtype;
ifexist number; --变量名写好exist 与exit
begin
select * into rt_emp from emp where emp.empno = m_empno;
select count(1) into ifexist from emp where empno = m_empno; --count(1)是emp表总行数即empno行数和
if ifexist = 0 then
dbms_output.put_line('您输入的编号不存在,异常');
elsif rt_emp.sal > 4000 then
dbms_output.put_line('工资高于4000,异常');
elsif rt_emp.comm is null or rt_emp.comm = 0 then
--elsif nvl(rt_emp.comm, 0) = 0 then
dbms_output.put_line('奖金没有或为0,异常');
else
null;
end if;
dbms_output.put_line('员工编号：' || m_empno || '姓名:' || rt_emp.ename ||
'工资：' || rt_emp.sal || '奖金：' || rt_emp.comm);
end proc;
上一篇： 关于linux权限的实例讲解
下一篇： MYSQL开启远程连接的方法及问题解决