欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle数据库练习题

程序员文章站 2022-03-31 10:39:03
sqlplus sys/111111 as sysdba show parameter name; --建立test表空间 create tablespace test da...

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;