Oracle入门

最新推荐文章于 2024-05-05 11:01:29 发布

一屋不扫何以扫-天下

最新推荐文章于 2024-05-05 11:01:29 发布

阅读量272

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/qq_39544334/article/details/77334576

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Oracle SQL指令

进入oracle控制台

在虚拟机上  sqlplus 输入用户名密码即可

在本机上，安装虚拟机客户端之后

sqlplus scott/tiger@192.168.32.128:1521/orcl

基本查询

展示当前用户
```
show user;
```
当前用户下的表
```
select * from tabs;
```
查看员工表结构
```
desc emp;
```
查询员工表中所有信息
```
select * from emp;
```

设置行宽、列宽

set linesize 120
col ename for a8
col sal for 9999
/

查询员工表中员工号姓名月薪
```
select  empno,ename,sal from emp;
```
查询员工表中员工号姓名月薪，年薪
```
select  empno,ename,sal，sal*12 from emp ;
```
查询员工信息：员工号姓名月薪年薪奖金年收入
```
select empno,ename,sal,sal*12+comm from emp;
```

Orcle中null问题

包含null的表达式都为null
null!=null

Oracle中null的设置

select empno,ename,sal,sal*12+nvl(comm,0) from emp;

查询没有奖金的员工信息

select * from emp where comm=null    无法查询出来

select * from emp where comm is null

查询去重复
```
select distinct job from emp;
```

过滤和排序查询

根据部门编号查询部门员工信息
```
select * from emp where deptno =10;
```
查询姓名叫KING的员工
```
select * from emp where ename='KING';
```

查询某个日期入职的员工信息

select * from emp where hiredate='17-NOV-81';

查询工资在1000到2000之间的用户信息

select * from emp where sal between 1000 and 2000;

查询部门编号在10-20之间

select * from emp where deptno in(10,20);

员工姓名模糊查询

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

排序按照月薪排序
```
select * from emp order by  sal;
```
排序按照月薪倒叙
```
select * from emp order by  sal desc;
```
排序按照奖金正序
```
select * from emp order by comm ;
```
排序按照奖金倒叙
```
select * from emp order by comm desc ;
```

null排序修正

select * from emp order by comm desc nulls last;

分组查询,部门编号，工作

select deptno from emp group by deptno;

常用函数

查询当前日期
```
select sysdate from  emp;
```
查询工资总额 sum
```
select sum(sal) from emp;
```
查询总条目数 count
```
select count(*) from emp;
```
查询总人数，有奖金的总人数
```
select count(*),count(comm) from emp;
```
查询平均工资 avg()
```
select avg(sal) from emp;
```

查询某个部门的平均工资,并进行倒叙排序

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

多表查询

查看dept表，设置列宽
```
desc dept
set linesize 80;
```
笛卡尔积查询
```
select * from emp,dept
```

内连接查询

select e.ename,e.sal,e.comm,d.dname from emp e, dept d where e.deptno=d.deptno;

查询级别表
```
select * from salgrade 
```

查询在低级到高级的员工信息

select e.ename,e.empno,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

外联接

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 fr where e.deptno=d.deptno group by d.deptno,d.dname;

子查询

select * from emp where deptno=(select deptno from dept where loc='NEW YORK')
select * from emp where sal > (select sal from emp where ename='SCOTT');

集合运算

union A-B联合，包含中间

select * from emp where deptno =10
union 
select * from emp where deptno =20

union all 包含中间两次

select * from emp where deptno =10
union all
select * from emp where deptno =20

intersect A-B交集

select * from emp where deptno =10 intersect select * from emp where sal<3000

minus A-B

select * from emp where deptno =10 minus select * from emp where sal<3000

分页

单独分页
select * from (select rownum r,e1.* from emp e1 where rownum<=10) where r>=5;


 分页+排序
 select *
 from   (select rownum r,e1.*
     from (select ename,sal from emp order by sal) e1
     where rownum <14
    )
 where r >8;






select * from(select rownum r,e1.* from (select * from emp order by sal) e1 where rownum<10) where r>=5;

表创建及表操作

创建表

create table t_user(
userid number(2) primary key,
password varchar2(14),
sex varchar2(13)) ;
添加数据

insert into t_user (userid,password,sex) values(1,’sss’,’man’);
删除表

drop table t_user;

PLSQL

PL/SQL Developer是一个集成开发环境，专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。

程序结构

PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分三部分：
声明部分（用declare开头）、执行部分（以 begin开头）和异常处理部分
（以exception开头）。其中执行部分是必须的，其他两个部分可选。
无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成

PLSQL模板

declare
　　/* 声明区(可选)：定义类型和变量、声明变量、声明函数、游标 */
　　begin
　　/* 执行区(必须的)：执行pl/sql语句或者sql语句 */
　　exception
　　/* 异常处理区(可选)：处理错误的 */
　　end;

Hello PLSQL

set serveroutput on

declare
    --声明变量
begin
    dbms_output.put_line('hello plsql');

end;
/

引用型变量

应用数据库中某个字段的类型，通过单查字段进行设置

--查询7839的姓名和薪水
set serveroutput on

declare
  --定义变量保存姓名和薪水
  --pename varchar2(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;
/

记录型变量

设置拥有数据库中一行的所有数据类型内容

set serveroutput on
declare
 emp_rec emp%rowtype;
begin

  select * into emp_rec from emp where empno=7839;

  SYS.DBMS_OUTPUT.PUT_LINE(emp_rec.ename||'薪资是'||emp_rec.sal);

end;
/

if语句

--判断用户从键盘输入的数字
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;
/

循环语句

--打印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;
/

光标

光标属性
%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;
/

涨工资案例

涨工资，总裁1000 经理800 其他400

    set serveroutput on

    declare
          --定义光标
          cursor cemp is select empno,job from emp;
          pempno emp.empno%type;
          pjob       emp.job%type;
        begin
          rollback;

          open cemp;
          loop
            --取一个员工
            fetch cemp into pempno,pjob;
            exit when cemp%notfound;

            --判断职位
            if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
              elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
              else  update emp set sal=sal+400 where empno=pempno;
            end if;
          end loop;
          close cemp;

          --提交 ---> ACID
          commit;
          dbms_output.put_line('涨工资完成');
    end;
    /

Exception

系统异常

--被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;
/

自定义异常

--查询并打印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;
/

存储过程

第一个存储过程函数

create or replace procedure sayhelloworld
as
  --说明部分
begin
  dbms_output.put_line('Hello World');

end;
/

调用存储过程

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

带参数的存储过程

给指定的员工涨500，并且打印涨钱和涨后的薪水

create or replace procedure raisesalary(eno in number)
as
–定义变量保存涨前的薪水
psal emp.sal%type;
begin
–得到涨前的薪水
select sal into psal from emp where empno=eno;

–涨100
update emp set sal=sal+100 where empno=eno;

–要不要commit?
–一般不在存储过程和存储函数中commit和rollback

–打印
dbms_output.put_line(‘涨前:’||psal||’ 涨后:’||(psal+100));
end;

/