orcl一些基础

修改密码：

输入命令: sqlplus /nolog ,进入oracle控制台，并输入 conn /as sysdba;以DBA角色进入

连接成功后，输入“select username from dba_users”查看用户列表。

若修改某一个用户密码， 修改用户口令 格式为：

alter user 用户名 identified by 新密码；

以 apps 为例，密码修改为 123456. 可输入

alter user apps identified by 123456;

成功后马上登陆发现登陆不了提示用户名被锁所以从新进入运行下面代码解锁 

alter user myuser account unlock;

修改用户名：

select user#, name from user$ where name = 'ARWEN';     --先瞧下arwen的信息,其中user#就是一个序列号,相当于身份证号吧,假如这里是250

 update user$ set name = 'WEIWENHP' where user# = 250;     --于是用户名就改好了啊.当然是update语句就记得再commit提交下啊.

实际上不用重启数据库拉,你这样强制更新下.

 alter system checkpoint;

 alter system flush shared_pool;    

 你再次登陆就发现可以登陆了啊.而之前的arwen用户就登不了

修改字符级：

以DBA身份进入SQLPLUS SQL sqlplus sys/sys as sysdba; …… SQL shutdown immediate; SQL startup mount; SQL alter system enable restricted session; SQL alter system set job_queue_processes=0; SQL alter system set aq_tm_processes=0; SQL alter database open; SQL alter database character set internal_use AL32UTF8;(ZHS16GBK) SQL shutdown immediate; SQL startup; 这样就可以讲ORACLE的字符集修改为UTF8，如果需要修改为GBK只需将alter database character set internal_use AL32UTF8;(ZHS16GBK)这句最后的AL32UTF8修改为ZHS16GBK即可

基础sql语句：

SQL>--进入orcl的命令是
SQL> --sqlplus scott/tiger@192.168.56.101:1521/orcl
SQL> --scott是用户名tiger是密码1521是端口号orcl是数据库名
SQL> --安装orcl在本机的时候要注意找到setup.exe点右键属性设置一下兼容性和以管理员省份运行安装 安装的中需要打开scott和hr用户并设置密码    最后在配置把环境变量指向I:\OracleSQL课件\工具\oracleInstanceClient\32bit\instantclient_12_1
安装虚拟机：运行I:\OracleSQL课件\虚拟机\virtualbox\4.3.8文件夹下VirtualBox-4.3.8-92456-Win.exe
然后再virtualbox里面新建一个虚拟机用户  把WinXP.vdi系统映射文件给它   安装好了启动系统
在虚拟机系统里面查看设备共享文件夹  添加你原电脑上面需要共享的资源给虚拟机  然后就可以安装orcl了
如果用户名密码忘记了   可以通过以下操作重新设置密码  以系统管理员cmd操作数据库
sqlplus / as sysdba
alter user sys identified by admin;

SQL> --清屏
SQL> host cls
SQL> --当前用户
SQL> show user
SQL> --查询所有员工的所有信息
SQL> select * from emp;
 SQL> --设置行宽
SQL> show linesize
linesize 80
SQL> set linesize 150
SQL> --设置列宽
SQL> col ename for a8
SQL> col sal for 9999
SQL> SQL优化的原则:
SQL> 1. 使用列名代替*
SQL> --查询员工信息：员工号 姓名 月薪
SQL> select empno,ename,sal
  2  from emp;
SQL> --查询员工信息：员工号 姓名 月薪 年薪 奖金 年收入
SQL> select empno,ename,sal,sal*12,comm,sal*12+comm
  2  from emp;
SQL> /*
SQL> SQL中null值:
SQL> 1. 包含null的表达式都为null
SQL> 2. null永远!=null
SQL> */
SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)
  2  from emp;
SQL> --2. null永远!=null
SQL> --查询奖金为null的员工
SQL> select *
  2  from emp
  3  where comm=null;
SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)
  2  from emp;
  已写入 file afiedt.buf
  1  select empno as "员工号",ename "姓名",sal 月    薪,sal*12,comm,sal*12+nvl(comm,0)
  2* from emp
SQL> --distinct  去掉重复记录
SQL> select distinct deptno from emp;                     
SQL> --distinct作用于后面所有的列
SQL> --连接符
SQL> --concat
SQL> select concat('Hello','  World') from dual;
SQL> select 3+2 from dual;


SQL> --dual表：伪表
SQL> select 'Hello'||' Word' 字符串 from dual;


字符串                                                                                                                                                
----------                                                                                                                                            
Hello Word                                                                                             
SQL> --查询员工信息：***的薪水是****
SQL> select ename||'的薪水是'||sal 信息 from emp;
 SQL> spool off
SQL> select e.empno,e.ename,e.sal,d.dname
  2  from emp e,dept d
  3  where e.deptno=d.deptno;
SQL> --不等值连接
SQL> --查询员工信息：员工号 姓名 月薪 工资级别
SQL> select e.empno,e.ename,e.sal,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;
SQL> --外连接
SQL> --按部门统计员工人数：部门号  部门名称  人数
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  2  from emp e,dept d
  3  where e.deptno=d.deptno
  4  group by d.deptno,d.dname;
SQL> /*
SQL> 外连接：某些不成立的记录，通过外连接依然可以包含在最后的结果中
SQL> 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然包含在最后的结果中
SQL>     写法：where e.deptno=d.deptno(+)
SQL> 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然包含在最后的结果中
SQL>     写法： where e.deptno(+)=d.deptno
SQL> */
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno
  4  group by d.deptno,d.dname;
SQL> --查询员工信息： 员工的名字 老板的名字
SQL> --自连接：通过表的别名，将同一张表视为多张表
SQL> select e.ename 员工的名字,b.ename 老板名字
  2  from emp e,emp b
  3  where e.mgr=b.empno;
SQL> --自连接：不适合操作大表
SQL> --层次查询
SQL> select level,empno,ename,mgr
  2  from emp
  3  connect by prior empno=mgr
  4  start with mgr is null
  5  order by 1;
SQL> /*
SQL>  第一层: start with mgr is null
SQL> 第二层: where mgr= 7839---> (7566,****,***)
SQL> 第三层: where mgr in (7566,****,***)
SQL> */
SQL> --字符函数
SQL> select lower('Hello World') 小写,upper('Hello World') 大写,initcap('hello world') 首字母大写
  2  from dual;
SQL> --substr(a,b) 从a中，第b位开始取
SQL> select substr('Hello World',3) from dual;
SQL> --substr(a,b,c) 从a中，第b位开始取,取c位
SQL> select substr('Hello World',3,4) from dual;
SQL> --length 字符数 lengthb 字节数
SQL> select length('Hello World') 字符,lengthb('Hello World') 字节
  2  from dual;
SQL> --trim 去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;
SQL> --replace 替换
SQL> select replace('Hello World','l','*') from dual;
SQL> --四舍五入
SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,
  2         round(45.926,-1) 四,round(45.926,-2) 五
  3  from dual;
SQL> --截断
  1  select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,
  2         trunc(45.926,-1) 四,trunc(45.926,-2) 五
  3* from dual
SQL> --日期
SQL> --当前时间
SQL> select sysdate from dual;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
SQL> --昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
SQL> --计算员工的工龄：天  星期 月 年
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
  2                        (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
  3  from emp;
SQL> select hiredate+sysdate from emp;
select hiredate+sysdate from emp
SQL> --months_between
SQL> select ename,hiredate,(sysdate-hiredate)/30  一,months_between(sysdate,hiredate) 二
  2  from emp;
SQL> --78个月后-
SQL> --add_months
SQL> select add_months(sysdate,78) from dual;
SQL> --last_day 月的最后一天
SQL> select last_day(sysdate) from dual;
SQL> --next_day
SQL> --下一个星期二
SQL> select next_day(sysdate,'星期二') from dual;
SQL> /*
SQL> next_day的应用：每个星期一自动备份数据
SQL> --对日期四舍五入
SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;
SQL> --to_char
SQL> --2015-03-10 15:23:12今天是星期二
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
SQL> --查询员工薪水：两位小数 千位符 货币代码
SQL> select to_char(sal,'L9,999.99') from emp;
SQL> -- 通用函数
SQL> --nvl2(a,b,c) 当a=null时候，返回c，否则返回b
SQL> select sal*12+nvl2(comm,comm,0) from emp;
SQL> --nullif(a,b) 当a=b时候，返回null，否则返回a
SQL> select nullif('abc','abc') 值 from dual;
SQL> --coalesce 从左到右找到第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值"
  2  from emp;
SQL> --涨工资，总裁1000 经理800 其他400
SQL> select ename,job,sal 涨前,
  2         decode(job,'PRESIDENT',sal+1000,
  3                    'MANAGER',sal+800,
  4                              sal+400) 涨后
  5  from emp;
SQL> --工资总额
SQL> select sum(sal) from emp;
SQL> --平均工资
SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;
SQL> --平均奖金
SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三
  2  from emp;
SQL> select count(*),count(comm) from emp;
SQL> --null值 5. 组函数会自动滤空；
SQL> select count(*),count(nvl(comm,0)) from emp;
SQL> --null值 5. 组函数会自动滤空；可以嵌套滤空函数来屏蔽他的滤空功能
SQL> --求部门的平均工资
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno;
SQL> --多个列的分组
SQL> select deptno,job,sum(sal)
  2  from emp
  3  group by deptno,job
  4  order by 1;
SQL> --多个列的分组: 先按照第一个列分组，如果相同，再按照第二列分组，以此类推
SQL> --求平均工资大于2000的部门
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having avg(sal) > 2000;
 SQL> --where和having最大的区别：where后面不能使用组函数
SQL> --查询10号部门的平均工资
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having deptno=10;
SQL> ed
  1  select deptno,avg(sal)
  2  from emp
  3  where deptno=10
  4* group by deptno
SQL> --SQL优化 3. 尽量使用where
SQL> group by 的增强
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
SQL> break no deptno skip 2
SP2-0016: 中断说明必须以 ON/BY 或 ACROSS 关键字开始
SP2-0158: 未知的 BREAK 选项 "deptno"
SQL> break on deptno skip 2
SQL> break on null


 --oracle分页
 select *
 from (select rownum r,e1.*
from (select * from emp order by sal) e1
  where rownum <=8
)
 where r >=5;


SQL> 查询10和20号部门的员工
SQL> 1. select * from emp where deptno in (10,20);
SQL> 2. select * from emp where deptno=10 or deptno=20;
SQL> 3. 集合运算
SQL> select * from emp where deptno=10
  2  union --union表示+
  3  select * from emp where deptno=20;
SQL> /*
SQL> 注意的问题
SQL> 1. 参与运算的各个集合必须列数相同，且类型一致
SQL> 2. 采用第一个集合作为最后的表头
SQL> 3. order by永远在最后
SQL> 4. 括号
SQL> */
SQL>  select deptno,job,sum(sal) from emp group by deptno,job
  2   union
  3   select deptno,to_char(null),sum(sal) from emp group by deptno
  4   union
  5   select to_number(null),to_char(null),sum(sal) from emp;
SQL> --打开SQL执行时间的开关
SQL> set timing on
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
已用时间:  00: 00: 00.02
SQL>  select deptno,job,sum(sal) from emp group by deptno,job
  2   union
  3   select deptno,to_char(null),sum(sal) from emp group by deptno
  4   union
  5   select to_number(null),to_char(null),sum(sal) from emp;
已用时间:  00: 00: 00.08
SQL> --SQL 优化原则 5：尽量不要使用集合运算
SQL> set timing off
SQL> spool off


SQL> --rownum 伪列 行号
SQL> select rownum,empno,ename,sal from emp;
SQL> select rownum,empno,ename,sal
  2  from emp
  3  where rownum<=3
  4  order by sal desc;
 SQL> /*
SQL> 关于rownum
SQL> 1. rownum永远按照默认的顺序生成
SQL> 2. rownum只能使用< <=;不能使用> >=
SQL> */
SQL>  select rownum,empno,ename,sal from emp order by sal desc;
SQL> select rownum,empno,ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum<=3;
SQL> -- 2. rownum只能使用< <=;不能使用> >=
SQL> select rownum,empno,ename,sal from emp where rownum<=8;
SQL>  select *
  2   from (select rownum r,e1.*
  3   from (select * from emp order by sal) e1
  4   where rownum <=8
  5   )
  6   where r >=5;
SQL> --临时表
SQL> --create global temporary table ******
SQL> --特点：当事务或者会话结束的时候，表中的数据自动删除
SQL> create global temporary table test1
  2  (tid number,
  3   tname varchar2(20))
  4  on commit delete rows;
SQL> insert into test1 values(1,'Tom');
SQL> select * from test1;
SQL> commit;
SQL> --第二题
SQL> select e.empno,e.ename,e.sal,d.avgsal
  2  from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d
  3  where e.deptno=d.deptno and e.sal> d.avgsal;
SQL> --相关子查询：将主查询的值作为参数传递给子查询
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
  2  from emp e
  3  where sal > (select avg(sal) from emp where deptno=e.deptno);
SQL> --第三题
SQL> select count(*) Total,
SQL> 
SQL>        sum(if 是81年 then +1 else +0) "1981",
SQL> from emp;
SQL> --行转列
SQL> -- wm_concat(字符串)  组函数
SQL> select deptno,wm_concat(ename) nameslist
  2  from emp
  3  group by deptno;
SQL> --查询工资比SCOTT高的员工信息
SQL> select * from emp where sal > select sal from emp where ename='SCOTT';
SQL> --子查询所要解决的问题：不能一步求解
SQL> select *
  2  from emp
  3  where sal > (select sal
  4               from emp
  5               where ename='SCOTT');
SQL> /*
SQL> 注意的问题：
SQL> 1. 括号
SQL> 2. 合理的书写风格
SQL> 3. 可以在主查询的where  select having from后面都可以放置子查询
SQL> 4. 不可以在group by放置子查询
SQL> 5. 强调from后面的子查询
SQL> 6. 主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用即可
SQL> 7. 一般不在子查询排序；但在top-n分析问题中，必须对子查询排序
SQL> 8. 一般先执行子查询，再执行主查询；但相关子查询例外
SQL> 9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10. 子查询中的null
SQL> */
SQL> --3. 可以在主查询的where  select having from后面都可以放置子查询
SQL> select empno,ename,sal,(select job from emp where empno=7839) 第四列
  2  from emp;
SQL> --5. 强调from后面的子查询
SQL> --查询员工信息：员工号 姓名 月薪
SQL> select *
  2  from (select empno,ename,sal from emp);
SQL> --查询员工信息：员工号 姓名 月薪 年薪
  1  select *
  2* from (select empno,ename,sal,sal*12 annsal from emp)
SQL> --6. 主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用即可
SQL> --查询部门名称是SALES的员工信息
SQL> select *
  2  from emp
  3  where deptno=(select deptno
  4                from dept
  5                where dname='SALES');
 SQL> select e.*
  2  from emp e,dept d
  3  where e.deptno=d.deptno and d.dname='SALES';
SQL> --SQL优化 4： 理论上，尽量使用多表查询
SQL> --in 在集合中
SQL> --查询部门名称是SALES和ACCOUNTING的员工
  1  select *
  2  from emp
  3* where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING')
SQL> select e.*
  2  from emp e,dept d
  3  where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
SQL> --any: 和集合中任意一个值比较
SQL> --查询工资比30号部门任意一个员工高的员工信息
SQL> select *
  2  from emp
  3  where sal > any (select sal from emp where deptno=30);


  1  select *
  2  from emp
  3* where sal > (select min(sal) from emp where deptno=30)
SQL> --all: 和集合中的所有值比较
SQL>  --查询工资比30号部门所有员工高的员工信息
SQL> select *
  2  from emp
  3  where sal > all (select sal from emp where deptno=30);


  1  select *
  2  from emp
  3* where sal > (select max(sal) from emp where deptno=30)
SQL> --多行子查询中的null
SQL> --not in (10,20,null)
SQL> --查询不是老板的员工
SQL> select *
  2  from emp
  3  where empno not in (select mgr from emp);
SQL> --查询是老板的员工
  1  select *
  2  from emp
  3* where empno in (select mgr from emp)
SQL> select *
  2  from emp
  3  where empno not in (select mgr from emp where mgr is not null);
SQL> create table test3
  2  (tid number,
  3   tname varchar2(20),
  4   hiredate date default sysdate);
SQL> insert into test3(tid,tname) values(1,'Tom');
SQL> select * from test3;
SQL> --rowid 行地址 伪列
SQL> select rowid,empno,ename,sal
  2  from emp;
SQL> --创建表：员工号  姓名 月薪 年薪 部门名称
SQL> create table empinfo
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
  4  from emp e,dept d
  5  where e.deptno=d.deptno;
SQL> -- 修改表：追加新列  修改列  删除列 重命名列  重命名表
SQL> alter table test3 add photo blob;
SQL> alter table test3 modify tname varchar2(40);
SQL> alter table test3 drop column photo;
SQL> alter table test3 rename column tname to username;
SQL> rename test3 to test4;
SQL> drop table test4;
SQL> --Oracle的回收站
SQL> --才查看回收站
SQL> show recyclebin;
SQL> --清空回收站
SQL> purge  recyclebin;
SQL> drop table TESTSAVEPOINT;
SQL> --Oracle回收站：参考——》闪回（闪回删除）
SQL> create table test5
  2  (tid number,
  3   tname varchar2(20),
  4   gender varchar2(2) check (gender in ('男','女')),
  5   sal number check (sal > 0)
  6  );
SQL> insert into test5 values(1,'Tom','男',1000);
SQL> insert into test5 values(2,'Mike','啊',1000);
insert into test5 values(2,'Mike','啊',1000)
SQL> create table student
  2  (
  3     sid number constraint student_pk primary key,
  4     sname varchar2(20) constraint student_name_notnull not null,
  5     gender varchar2(2) constraint student_gender check (gender in ('男','女')),
  6     email varchar2(40) constraint student_email_unique unique
  7                        constraint student_email_notnull not null,
  8     deptno number constraint student_fk references dept(deptno) on delete set null
  9  );
SQL> insert into student values(1,'Tom','男','tom@126.com',10);
SQL> insert into student values(2,'Mike','男','tom@126.com',10);
insert into student values(2,'Mike','男','tom@126.com',10)


SQL> --视图
SQL> create or replace view empinfoview
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
  4  from emp e,dept d
  5  where e.deptno=d.deptno
  6  with read only;
视图已创建。
SQL> select * from empinfoview;
SQL> --序列
SQL> --sequence
SQL> create sequence myseq;
序列已创建。
SQL> create table testseq
  2  (tid number,
  3   tname varchar2(20));
表已创建。
SQL> select myseq.nextval from dual;
SQL> select myseq.currval from dual;
SQL> -- insert into testseq(tid,tname) values(?,?);
SQL> insert into testseq(tid,tname) values(myseq.nextval,'aaa');
SQL> commit;
SQL> insert into testseq(tid,tname) values(myseq.nextval,'aaa');
SQL> rollback;
SQL> insert into testseq(tid,tname) values(myseq.nextval,'aaa');
SQL> --索引 index
SQL> create index myindex
  2  on emp(deptno);
索引已创建。
SQL> --同义词(别名)：代表：表，视图，存储过程*****
SQL> show user
USER 为 "SCOTT"
SQL> select count(*) from hr.employees;
SQL> --为hr.employees起别名 ---> 同义词
SQL> create synonym hremp for hr.employees;
create synonym hremp for hr.employees
同义词已创建。


--查询并打印员工的姓名和薪水
1.  光标的属性
      %isopen      %rowcount (影响的行数)
      %found       %notfound
set serveroutput on
declare
  -- 定义光标
  cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal       emp.sal%type;
begin
  --打开光标
  open cemp;
    loop
    --取一条记录
    fetch cemp into pename,psal;
    --exit when 没有取到记录;
    exit when cemp%notfound;
    dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
    --关闭光标
  close cemp;
end;
/
--查询某个部门的员工姓名
set serveroutput on
declare
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin
  open cemp(20);
  loop
    fetch cemp into pename;
    exit when cemp%notfound;
    
    dbms_output.put_line(pename);


  end loop;
  close cemp;
end;
/
--查询并打印7839的姓名和薪水
set serveroutput on
declare
  --定义记录型变量：代表一行
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=7839;
  
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
--被0除
set serveroutput on


declare 
  pnum number;
begin
  pnum := 1/0;


exception
  when zero_divide then dbms_output.put_line('1:0不能做被除数');
                                         dbms_output.put_line('2:0不能做被除数');
  when value_error then dbms_output.put_line('算术或者转换错误');
  when others then dbms_output.put_line('其他例外');
end;
/


--打印1~10
set serveroutput on
declare
  pnum number := 1;
begin
  loop
    --退出条件
    exit when pnum > 10;
    
    dbms_output.put_line(pnum);


    --加一
    pnum := pnum + 1;
  end loop;
end;
/
--查询并打印7839的姓名和薪水
set serveroutput on


declare
  --定义变量保存姓名和薪水
  --pename varchar(20);
  --psal       number;
  pename emp.ename%type;
  psal        emp.sal%type;
begin
  --得到姓名和薪水
  select ename,sal into pename,psal from emp where empno=7839;
  dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
-查询并打印50号部门的员工姓名
set serveroutput on


declare
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;
  
  --自定义例外
  no_emp_found exception;
begin
  open cemp;
  
  --取一条记录
  fetch cemp into pename;
  
  if cemp%notfound then 
    --抛出例外
    raise no_emp_found;
  end if;  
  
  --自动启动进程：pmon(process monitor) 清理现场
  close cemp;


exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外');
end;
/
set serveroutput on


declare
   --说明部分
begin
   --程序
   dbms_output.put_line('Hello World');
end;
/
--判断用户从键盘上输入的数字
set serveroutput on


--接收键盘输入 
--num: 地址值，在地址上保存了输入的数字
accept num prompt '请输入一个数字';


declare
  --定义变量，保存输入的数字
  pnum number := &num;
begin
  if pnum = 0 then dbms_output.put_line('您输入的是0');
    elsif pnum = 1 then dbms_output.put_line('您输入的是1');
    elsif pnum = 2 then dbms_output.put_line('您输入的是2');
    else dbms_output.put_line('其他数字');
  end if;    
end;
/
/*
数据的 确认： 涨后的工资不能少于涨前的工资
*/
create or replace trigger checksalary
before update
on emp
for each row 
begin


  --if 涨后的薪水  < 涨前的薪水  then
  if :new.sal < :old.sal then
      raise_application_error(-20002,'涨后的工资不能少于涨前的工资。涨前:'||:old.sal||'  涨后:'||:new.sal);
  end if;


end;
/
/*
触发器应用一：实施复杂的安全性检查
禁止在非工作时间插入新员工


1. 周末：to_char(sysdate,'day') in ('星期六','星期日')
2. 上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 18
*/
create or replace trigger securityemp
before insert
on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日','星期五') or 
     to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
     --禁止insert
     raise_application_error(-20001,'禁止在非工作时间插入新员工');
     
  end if;
end;
/
--查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as
  --月薪和奖金
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=eno;


  --返回年收入
  return psal*12+nvl(pcomm,0);
end;
/
--给指定的员工涨100，并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
as
  --定义变量保存涨前的薪水
  psal emp.sal%type;
begin
  select sal into psal from emp where empno=eno;


  update emp set sal = sal+100 where empno=eno;
  
  --要不要commit？ 一般不要
  
  dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));


end;
/
--成功插入新员工后，自动打印“成功插入新员工”


create or replace trigger saynewemp
after insert
on emp
declare
begin
  dbms_output.put_line('成功插入新员工');
end;
/
--打印Hello World
/*
调用存储过程:
1. exec sayhelloworld();


2. begin
       sayhelloworld();
       sayhelloworld();
    end;
    /


*/
create or replace procedure sayhelloworld
as
   --说明部分
begin
   dbms_output.put_line('Hello World');
end;
/
--查询某个员工的姓名 月薪和职位


/*
1. 查询某个员工的所有信息  --> out参数太多
2. 查询某个部门中所有员工的所有信息  ---> 集合
*/
create or replace procedure queryempinfo(eno in number,
                                                                         pename out varchar2,
                                                                         psal       out number,
                                                                         pjob      out varchar2)
as                                                                  
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;


end;
/


635067530