Oracle简单操作总结

---- 用到 scott  hr----------------

---------解锁scott 用户

alter user scott account unlock;

---------重置用户密码

alter user scott identified by tiger;

-------------------------------切换到 scott 用户----------------------

-----dept     部门表

---deptno 部门编号

---dname  部门名称

---loc    部门所在城市

-----emp    员工表

---empno--- 员工编号

---ename    员工姓名

---job     职位

---mgr     领导编号

---hiredate   入职时间

---sal      工资

---comm    年终奖

---deptno   部门编号

---------树形结构-   tree ---------------

----salgrade   工资等级

---grade    工资等级;

---losal    等级最低值

---hisal    等级最高值

-----------------查询一下 员工表信息--------------------

select * from emp ;

select empno,ename,job from emp ;

---别名

---双引号只能出现在别名中，其他用单引号

select empno  as  eno ,ename  ee ,job "职位" from emp ;

/**

中文乱码问题解决

1.查看服务器端编码

select userenv('language') from dual;

我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK

2.执行语句 select * from V$NLS_PARAMETERS

查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。

如果不是，需要设置环境变量.

否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.

3.设置环境变量

计算机->属性->高级系统设置->环境变量->新建

设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是 AMERICAN_AMERICA.ZHS16GBK

4.重新启动PLSQL,插入数据正常

*/

-----------查询员工职位-----------

---去重复

select distinct e.job from emp e;

----查询 员工的年薪 

----sql 可以四则运算

select e.sal *10 ,e.sal from emp e;

select e.empno || '==' || e.ename    xxx from emp e;

--条件查询

--查询用户编号为 7369的员工

select *  from emp  e where e.empno=7369;

--查询奖金 comm不为空的员工

-----null  很特殊  ，null 不等于 null ，null 跟谁运算 谁就变成null

select * from emp e where e.comm  is not null;

select e.sal *12 + nvl(e.comm,0) ,e.sal from emp e;

--查询奖金comm为空并且工资大于 1500

select * from emp e where e.comm is null  and  e.sal >1500;

--查询奖金comm为空并且工资不大于 1500

select * from emp e where  e.comm is null and  e.sal <=1500;

select * from emp e where e.comm is null and  not (e.sal >1500);

--查询奖金commm为空或者工资大于 1500

select * from emp e where e.comm is null or e.sal >1500;

--范围查询

--查询工资大于1500 并且小于3000

select * from emp e  where e.sal >1500 and e.sal<3000;

-----between 包含临界点

select * from emp e where e.sal between 1500 and 3000;

--查询员工编号 是  7369  7788  7654 的员工

select * from emp e where e.empno in (7369,7788,7654);

select * from emp e where e.empno= 7369 or  e.empno= 7788 or  e.empno= 7654;

--查询员工姓名是  SMTH   MARTIN   SCOTT

select * from emp e where e.ename in ('SMITH','MARTIN','SCOTT');

--查询员工编号不等于  7369的员工

select * from emp e where e.empno !=7369;

select * from emp e where e.empno <>7369;

--排序 

--按工资排序

---升序

select * from emp e order by e.sal asc;

----降序

select * from emp e order by e.sal desc;

--按奖金排序

select * from emp e order by e.comm desc  nulls last;

select * from emp e order by e.comm desc  nulls first;

--模糊查询

--查询员工姓名中 带M的

select * from emp e where e.ename like  '%M%';

--查询员工姓名第二个字母是M的员工

select * from emp e where e.ename like  '_M%';

select * from emp e where e.ename like  '__M%';

-----查询  员工姓名中 带_的

select * from emp e where e.ename like '%c_%' escape 'c';

--查询每个员工姓名有多少位

select length(ename) from emp ;

------函数-------------------

------------单行函数   调用执行完  几行记录还是几行记录  ----------------

--------字符

-----小写

select lower( ename) from emp ;

-----大写

select upper( ename) from emp ;

select upper('abcdabcd') from emp ;

select upper('abcdabcd') from dept ;

----虚表  伪表    用来完善语法

select upper('abcdabcd') from dual;

---- ||  合并 

select concat(concat(empno,'=='),ename) from emp ;

select replace('   aaa          bbb   ',' ','') from dual;

--------数值

round

-----保留小数  四舍五入

select round(3.141592653589793238462643383279,4) from dual;

trunc

-----保留小数  截断

select trunc(3.141592653589793238462643383279,4) from dual;

mod

----取余  取模

select mod(4,3) from dual;

-------日期

-----获取当前日期

select sysdate from dual;

-----时间可以四则运算

-----获取明天的时间

select sysdate +1  from dual;

-----获取一周后的时间

select sysdate +7  from dual;

-----获取一月后的时间

select add_months(sysdate,1) from dual;

----获取下年后的时间

select add_months(sysdate,12) from dual;

------查询一下员工表入职时间到现在的天数

select round( sysdate -e.hiredate) from emp e;

------查询一下员工表入职时间到现在的周数

select round( (sysdate -e.hiredate)/7) from emp e;

------查询一下员工表入职时间到现在的月数

select trunc(months_between(sysdate,e.hiredate)) from emp e;

------查询一下员工表入职时间到现在的年数

select trunc(months_between(sysdate,e.hiredate))/12 from emp e;

-------转换

------字符转数字-----

select to_number('123'),'123' from dual;

------数字转字符------

select to_char(123) from dual;

select * from emp where empno =7369;

select * from emp where empno ='7369';

------日期转字符----

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

----------数据 区分大小写

--------关键字不区分

select * from emp where ename like '%m%';

SELECT * FROM emp WHERE ename LIKE '%M%';

------字符转日期------

select to_date('2017-08-02 12:14:58','yyyy-MM-dd HH24:mi:ss') from dual;

-------通用

select e.sal *14 + nvl(e.comm,0) from emp e;

-------表达式

----行转列

select case  e.ename

when 'CLARK' then '鲁班7号'

when 'KING' then '安其拉'

when 'MILLER' then '后羿'

end "姓名"

from emp e  where e.deptno=10;

----oracle 特有表达式

select decode(  e.ename

, 'CLARK' , '鲁班7号'

, 'KING' , '安其拉'

, 'MILLER' , '后羿'

) "姓名"

from emp e  where e.deptno=10;

------------多行函数(聚合函数)   调用执行完  几行记录变1行记录 ----------------

-----查询员工数量

count

select count(*) from emp ;

select count(1) from emp ;

select count(0) from emp ;

select count('其他') from emp ;

select count(empno) from emp ;

select count(comm) from emp ;

------查询员工工资的和

sum

select sum(sal) from emp ;

------查询员工工资的平均数

avg

select avg(sal) from emp ;

-----查询最大

------查询员工工资的最大值

max

select max(sal) from emp ;

min

------查询员工工资的最小值

select min(sal) from emp ;

-----------分组--------------

-----按部门查询员工数量

------如果使用 group by   ,select 后面 只能出现  聚合函数或者 group by 条件的列

select count(0),deptno from emp group by deptno;

----------------按部门查询员工数量   查询数量大于5的

select count(0),deptno from emp group by deptno  having count(0) > 5;

----select count(0) a,deptno from emp group by deptno  having  a > 5;

----sql执行顺序

FROM–>JOIN–>WHERE–>GROUP–>HAVING–>DISTINCT–>ORDER–>TOP

----------- where  和having 区别

----------where 出现在 group by 前    having 出现在 group by 后

----------------------------------------------------------

----------员工表   部门表

---emp  14  * dept   4   =  56

select * from emp e,dept d;

-----笛卡尔积   在sql 中没有实际意义 ，如果出现，说明  sql 写的有问题--------

----

----oracle  在做多表查询时 ，是从右忘左查询的 

----等值连接

----隐式内连接

select * from emp e,dept d

where   e.deptno = d.deptno;

select e.empno,e.ename,e.job,d.dname from emp e,dept d

where   e.deptno = d.deptno;

----显式内连接

select * from emp e inner join dept d on e.deptno=d.deptno;

----不等值连接

select * from emp e,dept d

where   e.deptno != d.deptno;

--范例：查询出每个员工的上级领导 (员工编号  员工姓名 ，领导编号 ，领导姓名)

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 ,emp e2

where e1.mgr = e2.empno

;

--范例: 在上一个例子的基础上查询该员工的部门名称

select e1.empno "员工编号" ,e1.ename "员工姓名",e1.deptno "员工部门编号",d.dname "员工部门名称",

e2.empno "领导编号",e2.ename  "领导姓名"

from emp e1 ,emp e2 ,dept d

where e1.mgr = e2.empno

and e1.deptno=d.deptno

;

--范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

select e1.empno "员工编号" ,e1.ename "员工姓名",e1.deptno "员工部门编号",

d.dname "员工部门名称",e1.sal "员工工资",s1.grade "员工工资等级",

e2.empno "领导编号",e2.ename  "领导姓名" ,e2.sal "领导工资" ,s2.grade "领导工资等级"

from emp e1 ,emp e2 ,dept d ,salgrade s1 ,salgrade s2

where e1.mgr = e2.empno

and e1.deptno=d.deptno

and e1.sal between s1.losal and s1.hisal

and e2.sal between s2.losal and s2.hisal

;

--范例：查询出所有员工的上级领导

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 ,emp e2

where e1.mgr = e2.empno

;

--------------外连接---------------------------

-------------全量表---------------------------

-------------左外 -----------

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 left  join emp e2 on e1.mgr=e2.empno;

---错误

select e1.empno,e1.ename,e2.empno,e2.ename from emp e2 left  join emp e1 on e1.mgr=e2.empno;

------------右外 ------------

select e1.empno,e1.ename,e2.empno,e2.ename from emp e2 right join emp e1 on e1.mgr=e2.empno;

------- oracle 特有外连接-------------

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 ,emp e2

where e1.mgr = e2.empno(+);

-------------------

--查询比SCOTT工资高的员工   

---- 子查询    支持  一行一列  ,    一行多列   ,多行多列

select sal from emp where ename='SCOTT';

select * from emp e where e.sal >  3000;

select * from emp e where e.sal >  (select sal from emp where ename='SCOTT');

select e1.* from emp e1 ,emp e2 where e2.ename='SCOTT' and e1.sal > e2.sal;

--查询职位是经理并且工资比7782号员工高的员工

select sal from emp where empno = 7782;

select * from emp e where e.job='MANAGER' and e.sal > (select sal from emp where empno = 7782);

--查询工资最低的员工

select min(sal) from emp ;

select * from emp e where e.sal=(select min(sal) from emp );

--查询部门最低工资大于30号部门最低工资的结果

select min(sal) from emp  where deptno=30;

select min(e.sal) from emp e  group by deptno  having  min(e.sal)> (select min(sal) from emp  where deptno=30);

--查询出和scott同部门并且同职位的员工

select deptno from emp where ename='SCOTT';

select job from emp where ename='SCOTT';

select * from emp e where e.deptno=(select deptno from emp where ename='SCOTT') 

and e.job=(select job from emp where ename='SCOTT');

select * from emp e where (e.deptno,e.job) =  (select deptno ,job from emp where ename='SCOTT');

--查询每个部门的最低工资和最低工资的雇员和部门名称

-----当子查询  返回结果集为 多行多列时   ，可以把结果集当做表来用

select min(e.sal),e.deptno from emp e group by e.deptno;

select e.*,d.dname from emp e , (select min(e.sal) x,e.deptno y from emp e group by e.deptno)  t ,dept d

where e.sal = t.x and e.deptno=t.y

and e.deptno=d.deptno

;

--查询出不是领导的员工？？？？

---查询领导编号

----在做子查询时， 需要先去 null 值

select distinct mgr from emp ;

select * from emp e where e.empno not in(select distinct mgr from emp  where mgr is not null);

select * from emp e where e.empno =7369  or e.empno =7788 or   e.empno =7654  or  e.empno =null;

                          true  or true  or true    or false= true;     

select * from emp e where e.empno in (7369,7788,7654 );

select * from emp e where e.empno not in (7369,7788,7654 ,null);

select * from emp e where e.empno !=7369 and e.empno !=7788 and  e.empno !=7788  and e.empno!=null;

                          true and  true  and true and false  = false;

                         

                         

                         

                         

                                                                                                                                                                                                

-----课堂练习

--查询员工表中工资最高的前三名

----rownum ,rowid 伪列

----数据库是  先查询 在排序

select t.*,rownum,rowid from (select e.*  from emp e  order by sal desc)  t  where rownum <4;

-------分页   取 4-6 条记录

-------rownum  不能做大于判断

select * from (select t.*,rownum rmm from (select e.*  from emp e  order by sal desc)  t )  tt

where  tt.rmm >3and tt.rmm<7

;

------oracle     rownum

------mysql      limit

------sqlserver   top 

 

-------- oracle  不常用分页----

select * from (select row_number() over( order by sal desc) rm ,e.*  from emp e) t where t.rm >3and t.rm<7 ;

--找到员工表中薪水大于本部门平均薪水的员工

select avg(e.sal) , e.deptno from emp  e  group by e.deptno;

select p.* from emp p ,(select avg(e.sal) x, e.deptno y from emp  e  group by e.deptno) t

where p.deptno=t.y

and p.sal > t.x

;

--统计每年入职的员工个数

select  count(*),to_char(e.hiredate,'yyyy') from emp e group by to_char(e.hiredate,'yyyy');

----1.

select sum(t.x) "Total" from (select count(*) x,to_char(e.hiredate,'yyyy') y from emp e group by to_char(e.hiredate,'yyyy')) t ;

----2.

select case t.y

when '1980' then t.x

end "1980"

from (select count(*) x,to_char(e.hiredate,'yyyy') y from emp e group by to_char(e.hiredate,'yyyy')) t;

----3.

select sum( case t.y

when '1980' then t.x

end) "1980"

from (select count(*) x,to_char(e.hiredate,'yyyy') y from emp e group by to_char(e.hiredate,'yyyy')) t;

-----4.

select

sum(t.x) "Total",

sum( case t.y

when '1980' then t.x

end) "1980",

sum( case t.y

when '1981' then t.x

end) "1981",

sum( case t.y

when '1982' then t.x

end) "1982",

sum( case t.y

when '1987' then t.x

end) "1987"

from (select count(*) x,to_char(e.hiredate,'yyyy') y from emp e group by to_char(e.hiredate,'yyyy')) t;

----exists   (了解)

---用法1：

select * from emp   where exists (select * from dept where deptno=10);

---用法2：

select d.* from dept d where exists (select e.* from emp e where d.deptno=e.deptno);

select * from dept d where d.deptno in (select e.deptno from emp e);

----in   or    like   效率一般------------

---如果  左表大 （数据量多）  右表小 （数据量少）   in效率> exists 效率

---如果  左表小 （数据量少）  右表大 （数据量多）   in效率 < exists 效率

--集合运算

--范例：工资大于1500，或者是20号部门下的员工

--并集

select * from emp where sal >1500 or deptno=20;

select * from emp where sal >1500

union   ----合并去重复

select * from emp where deptno=20;

select * from emp where sal >1500

union  all  ----强制合并  不去重复

select * from emp where deptno=20;

--范例：工资大于1500，并且是20号部门下的员工

--交集

select * from emp where sal >1500 and deptno=20;

select * from emp where sal>1500

intersect

select * from emp where deptno=20;

--范例：1981年入职的普通员工（不包括总裁和经理）

----差集

select * from emp e where to_char(e.hiredate,'yyyy')='1981'

MINUS

select * from emp e where e.job in ('MANAGER','PRESIDENT');

--------全连接-----

select * from emp ;

select * from dept;

select d.*,e.* from dept d  left join emp e on  d.deptno=e.deptno

union

select d.*,e.* from dept d  right join emp e on  d.deptno=e.deptno;

select * from dept d full join emp e on d.deptno=e.deptno;

-----集合补充 并集

----只要  列的数量 类型一致就可以做集合运算

select empno ,ename from emp

union

select deptno ,dname  from dept;

--------------------------------------------------------------------

---  mysql                     oracle

---  创建小数据库              创建表空间

---  创建表                    创建用户  关联表空间

---  crud                      给用户赋权限

---                               建表

---                               crud

-------------------切换到 system    dba (3星/5星) ---------------------

---------------------创建表空间----------------------

create  tablespace  itcastspace   ----表空间的名称

datafile 'c:/itcastspace.dbf'     ----默认数据库文件

size 100M                         ----表空间大小

autoextend on                     ----打开空间自动增长

next 10M                          ----每次增长的大小

---------------创建用户---------------

create user crm   ----用户名

identified by crm  ----密码

default tablespace itcastspace  ----挂载表空间

---------查看当前用户权限

select * from session_privs;

-----给用户赋权限

grant dba  to  crm;

-----------切换到crm 用户------

------oracle 数据类型-------------

----数值

number(3)   999

number(3,2) 9.99

---字符

char (10)                    --定长字符    aaa    10

varchar(10)                --可变字符     aaa    3

varchar2   ----用法 和varchar   一模一样   推荐使用

---日期

date       ---日期+时间

timestamp  ----时间戳      秒后9位

---大字段

long    2G

clob    4G

blob    4G

------------创建表(4星) ----------------------

create table person(

     pid number(10),

     pname varchar2(20)

)

select * from person;

-----删除表 (责任)

drop table person;

-----修改表结构（3星）

---增加列

alter table  person  add  sex varchar2(2);

---修改列名

alter table  person  rename column sex to gender;

--0  女  1  男

---删除列

alter table  person  drop  column gender;

-----------------5星 + 

-----增删改

----增

insert into person values (1,'鲁班');

insert into person (pid,pname) values (2,'安其拉');

----修改 (责任)

update person set pname='貂蝉' where  pid=2;

---删除 (责任)

delete from person where pid=2;

----删除表 再创建表 

truncate table person ;

insert into person (pid,pname) values (2,'安其拉');

select * from person;

-------------约束  (4星)-------------------

------主键 (非空 唯一  )   外键   唯一   非空    检查约束---------------

create table person(

     pid number(10) primary key ,

     pname varchar2(20) not null,

     phone varchar2(20) unique,

     gender number(1) check(gender in(0,1))

)

insert into person values (3,'郭德纲','13838383538',1);

-------------自定义约束名称(3星)--------------------

create table person(

     pid number(10)  ,

     pname varchar2(20) not null,

     phone varchar2(20) ,

     gender number(1)  ,

     constraint pk_person primary key(pid),

     constraint uk_phone unique(phone),

     constraint ck_gender check(gender in(0,1))   

)

-----------订单主表-----

create table orders(

       ooid number(10) primary key,

       ootxt varchar2(20)

)

------------订单从表(订单详情表)-----

create table order_items(

       oiid number(10) primary key,

       oitxt varchar2(20),

       ooid number(10),

       constraint fk_order foreign key (ooid)  references  orders(ooid)

)

insert into orders values (1,'618张三买的鼠标');

select * from orders;

insert into order_items values (1,'罗技鼠标max518',1);

insert into order_items values (2,'雷蛇金环蛇',1);

select * from order_items;

delete from order_items where  ooid=1;

delete from orders where ooid=1;

---------在做插入操作时  需要先插入主表信息  再插入从表信息  (5星)

---------在做删除操作时   需要先删除从表信息，在删除主表信息

commit;

-------------事务保存点(3星)----

update person set pname='于谦' where pid=3;

savepoint a;

update person set pname='王老爷子' where pid=3;

savepoint b;

update person set pname='郭小宝' where pid=3;

savepoint c;

rollback to b;

select * from person;

------------------复制表-------------

create table v_emp as select * from scott.emp;

-------------------视图----------------------

-----------------是包装了一段复杂sql 代码的对象----------------

--------1.简化复杂查询

create  view  v1 as  select * from v_emp;

----使用视图

select * from v1 where empno=7369;

-------2. 隐藏敏感信息

create view  v2 as select empno,ename ,job from v_emp;

select * from v2;

create view  v3 as select empno eno,ename ee ,job jj from v_emp;

select * from v3;

update v1 set ename='吕布' where empno=7369;

---------------1.  视图  不存储数据 

------------2.  不要对视图做  增删改

---------只读视图

create  view  v4 as  select * from v_emp  with read only;

---update v4 set ename='吕布1' where empno=7369;

-------------索引  index  ----------------------------

------------大幅提高查询效率的对象----------------------

create table stu (

stuid number(10),

sutname varchar2(20)

)

begin

  for i in 1..5000000

    loop

      insert into  stu values (i,'abcd' || i);

    end loop;

    commit;

end;

-----------查询500万数据

-----2.012 s  

select * from stu  where sutname = 'abcd4000000';

---创建索引

create index  index_stu on stu(sutname);

-----有索引 0.062 s

select * from stu  where sutname = 'abcd5000000';

-----查询时  会优先查询索引-----------

-----索引会影响 增删改的效率------

-----索引创建条件         1.表查询 次数远远大于  增删改次数   2.  where 条件出现概率高的字段  3.唯一性较强的字段

------主键约束   唯一约束  会自动创建索引

---in  or   like   '%M%'   字段 * xxx    不走索引

---like   'M%'

-----单列索引  

----表   列1 列2  列3

select * from 表 where  列1=xxx and  列2=xxx

------复合索引

----语法

create index  索引名称   on 表 （列1 ，列2 。。。。）

-------

create index  索引名称   on 表 （列1 ，列2）

select * from 表 where  列1=xxx and  列2=xxx；  ---走索引    效率高

select * from 表 where  列2=xxx and  列1=xxx；  ---不走索引    效率低

-----------同义词 (3星)-------------------

select * from scott.emp ;

create public synonym  sp1 for scott.emp;

select * from sp1;

------------序列-------------------------

create sequence seq;

-----获取序列的下一个值

select seq.nextval from  dual;

-----获取序列的当前值

select seq.currval from  dual;

select * from person;

insert into person  values (seq.nextval,'岳云鹏1','13333333334',1);

commit;

----sql 执行顺序      rownum
   FROM–>JOIN–>WHERE–> GROUP–>HAVING–>DISTINCT–>ORDER–>TOP   limit
 

-----pl/sql   语言    sql语言补充

-----hello world

---main -----
begin
   --打印输出 函数
   dbms_output.put_line('hello world');
end;

----全语法

declare
  ----变量的声明区
 v_num number(10);
 v_char  varchar2(20) :='鲁班';   ------声明变量并赋值
 v_sal  emp.sal%type;   ----引用列数据类型
 v_obj  emp%rowtype;    ----行引用
 
begin
  ----业务逻辑  通过sql 给变量赋值  用 into
   --select sal into v_sal from v_emp where empno=7788;
 
   --dbms_output.put_line(v_sal);
 
  v_char :='安其拉';
  dbms_output.put_line(v_char);
   select * into v_obj from emp where empno=7369;
  
   dbms_output.put_line(v_obj.empno || '====' || v_obj.ename);
end;

--------流程控制   -----
------if  ------

----1.
if 条件 then
  ----业务逻辑
end if;

----2.
if 条件 then
  ----业务逻辑
  else
  ----业务逻辑 
end if;

----3.
if 条件 then
   ----业务逻辑
  elsif 条件 then
   ----业务逻辑
end if;

------------------输入年龄  打印输出人类型

declare
v_age  number(3):=0;

begin
   v_age :=&aa; ---弹出框

  if v_age<18 then
    dbms_output.put_line('萝莉or正太');
   elsif v_age>=18 and v_age<60 then 
    dbms_output.put_line('大叔or小姐姐');
    else
    dbms_output.put_line('不能惹+扶不起'); 
  end if;
end; 

---------------循环-------------------------
---1. 无限循环 （死循环）
 loop
  
 end loop;

---2.while 循环
while 条件
  loop
   
  end loop;

---3.for 循环
for 变量 in 条件
  loop
   
  end loop;

--------打印输出   1-100个数

declare
 v_num number(3):=0;
begin
  loop
    --跳出条件
    if v_num>=100 then
      exit;---跳出
    end if;
    v_num :=v_num +1;
    dbms_output.put_line(v_num);
  end loop;
end;

declare
 v_num number(3):=0;
begin
  loop
    --跳出条件
    exit when v_num>=100;
    v_num :=v_num +1;
    dbms_output.put_line(v_num);
  end loop;
end;

-----while循环

declare
v_num number(3):=1;

begin
  while v_num<=100
    loop
       dbms_output.put_line(v_num);
       v_num :=v_num+1;
    end loop;

end;

------for 循环
declare
   v_num number(3):=1;
begin
  for v_num in 1..100
    loop
      dbms_output.put_line(v_num);
    end loop;
end;

----for 变量声明可以省略
begin
  for v_num in 1..100
    loop
      dbms_output.put_line(v_num);
    end loop;
end;

----------集合  （游标） cursor-------
----语法

declare
  cursor 游标名称  is sql 赋值语句
begin
  open 游标   ---打开游标
 
  close 游标;
end;

-----------通过遍历游标  输出 部门编号为10的员工信息
declare
 cursor c1 is select * from emp where deptno=10;
 v_obj emp%rowtype;
begin
  open c1;
       loop
         fetch c1 into v_obj;
         exit when c1%notfound;----当游标里没有数据时跳出
         dbms_output.put_line(v_obj.empno || '====' || v_obj.ename);
       end loop;
  close c1;
end;

-------------for  循环  游标-----------------

declare
 cursor c1 is select * from emp where deptno=10;
begin------for 循环帮我们做了   open 操作  和 close；
  for v_obj in c1
    loop
        dbms_output.put_line(v_obj.empno || '====' || v_obj.ename);
    end loop;
end;

-----------带参数的游标
declare  ----变量的类型没有长度
 cursor c1(dno number) is select * from emp where deptno=dno;
 v_obj emp%rowtype;
begin
  open c1(10); ---给变量赋值
       loop
         fetch c1 into v_obj;
         exit when c1%notfound;----当游标里没有数据时跳出
         dbms_output.put_line(v_obj.empno || '====' || v_obj.ename);
       end loop;
  close c1;
end;

--------例外(异常)-----------------------
declare
v_num number(10) :=2;
begin
  v_num := v_num /0;
  exception  ----开始捕获异常
  --when zero_divide  then
  when others  then
    dbms_output.put_line('这是个被0除异常');
end;

----------------自定义例外--------------------

declare
v_age  number(3):=0;
ex_age exception ;----声明自定义异常
begin
   v_age :=&aa; ---弹出框

   if v_age>140 then
     raise ex_age;    ----抛异常
   end if;
  

  if v_age<18 then
    dbms_output.put_line('萝莉or正太');
   elsif v_age>=18 and v_age<60 then 
    dbms_output.put_line('大叔or小姐姐');
    else
    dbms_output.put_line('不能惹+扶不起'); 
  end if;
 
  exception
    when ex_age then
    dbms_output.put_line('未知生物'); 
end; 

--------------------------存储过程 ----存储函数---------------------------

----预编译到数据的sql 代码段

---语法
create [or replace]  procedure   存储过程名称 (参数名 [in]/out 参数类型,参数2,3,4)
as/is
  ---变量声明
begin
  ----业务逻辑
end;

-----------------根据员工编号  输出年薪---------------

create or replace procedure get_year_sal(eno  number)
is
 v_sal number(10);
begin
  select sal*12 + nvl(comm,0) into v_sal from emp where empno=eno;
  dbms_output.put_line(v_sal);
end;

------调用存储过程
---1. 不常用
call get_year_sal(7369);

---2.常用
begin
  get_year_sal(7369);
end;

-----------------------存储过程的out 参数

create or replace procedure get_year_sal1(eno  number,v_sal out number)
is

begin
  select sal*12 + nvl(comm,0) into v_sal from emp where empno=eno;
end;

----调用
declare
v_sal number(10);
begin
  get_year_sal1(7369,v_sal);
  dbms_output.put_line(v_sal);
end;

-----存储函数------------------------------
create [or replace]  function   存储函数名称 (参数名 [in]/out 参数类型,参数2,3,4) return 数据类型
as/is
  ---变量声明
begin
  ----业务逻辑
  return  数据
end;

-----------------根据员工编号  输出年薪---------------
create or replace  function  get_year_sal_fun(eno number) return number
is
   v_sal number(10);   
begin
  select sal*12 + nvl(comm,0) into v_sal from emp where empno=eno;
  return v_sal;
end;

------调用存储函数
declare
  v_sal number(10);
begin
  v_sal := get_year_sal_fun(7788);    ----存储函数的返回值必须强制接收
  dbms_output.put_line(v_sal);
end;

begin  ----存储函数的返回值必须强制接收
  dbms_output.put_line(get_year_sal_fun(7788));
end;

--------------存储过程不能出现在sql 中  存储函数可以----
select e.*,get_year_sal_fun(e.empno) from emp e;

--------返回值是   游标的存储过程  sys_refcursor--专门用来当out 参数的
create or replace procedure get_emp_by_deptno(dno number,emps out sys_refcursor)
is

begin  ---打开游标并赋值
  open emps for  select * from emp where deptno=dno;
end;

----调用返回值是游标的   存储过程
declare
sysref  sys_refcursor;
v_obj emp%rowtype;
begin
   get_emp_by_deptno(10,sysref);
   loop
     fetch sysref into v_obj;
     exit when sysref%notfound;
     dbms_output.put_line(v_obj.ename);
   end loop;
   close sysref;
end;

--------触发器------
----语法

create [or replace]  trigger  触发器的名称
before/after
insert/update /delete
on 表
--for each row   如果写  行级触发器   如果不写   语句级触发器

declare

begin
 
end;

---------------插入员工表信息后，打印输出一下 新员工入职了--------------------

create or replace  trigger tri_addemp
after
insert
on emp
--for each row
declare

begin
  dbms_output.put_line('新员工入职了');
end;

insert into emp (empno,ename) values (7,'吕布');
commit;

select * from emp ;

update emp  set sal = 10 where empno=7;
commit;

----删除触发器----
drop trigger tri_addemp;

---------不能给员工降薪---------------------
create or replace   trigger  tri_sal
before
update
on emp
for each row    ---如果要使用 :old  :new  需要写 for each row
declare

begin
   ---:old  ---更改前的数据
   ---:new  ---要更改的数据
   if :old.sal >:new.sal  then  --- -20001 ---   - 29999
     raise_application_error(-20001,'不能给员工降薪');
   end if;
end;

update emp set sal = sal-1  where empno=7369;
commit;

select * from emp  where empno=7369;

------------通过触发器 自动生成主键-----------------------
create or replace trigger tri_add_id
before
insert
on emp
for each row
 
declare
   v_id number(10);
begin
  select seq.nextval into v_id from dual;
  :new.empno:=v_id;
end;

insert  into emp  (ename) values('鲁班');
commit;

insert  into emp  (empno,ename) values(8,'安其拉');
commit;

select * from emp ;