Oracle数据库小知识点整理

database

-- 数据库存储数据

 

-- 市面上主流的数据库有哪些

 

-- 甲骨文  oracle   mysql

--  IBM  db2  金融

--  微软  sqlserver

 

--这些是关系型数据库。

 

--NOSQL  不仅仅是sql,典型 mongodb.

 

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

--数据库的语言有哪些分类

-- **************************************

/***

1.数据定义语言(DDL),create,alter,drop

2.数据操纵语言(DML),select,update,insert,delete

3.数据事务语言, commit,savepoint,rollback

事务特性:原子性,一致性,持久性,隔离型,

 

...........后面补充.............

 

在关系型数据库中数据是以table

(二维数据结构,行和列的形式)

来组织数据

 

table 就是表来组织数据,这个table的设计的规范是什么?

 

--三范式  *****************

 

--第一范式:数据库表中的字段应该最小单位,

是不能够分割(本地化)

--第二范式:数据库表中的一个字段

不能由另外一个字段通过计算得出。

 

age 

birthday

 

工资

养老保险费用

 

***/

 

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

-- 创建表  oracle  列名和列的类型来定义的

--1.数据类型:

--number 数值类型  number(长度),number(10)

--number(长度,小数点位数)   number(7,2)

--2.char类型   char(长度),定长  char(6) 固定长度为6位

--3.varchar2类型  varchar2(20)

--4.date类型  日期类型

--5.int类型,表示一个正整数

-- 大文本类型 blob ,clob

-- 路径

 

-- 首先我们来创建一张表   命名规范 t_

create   table   t_students

(

      sn  number(7)   primary  key ,--学号 -- 主键(唯一不能重复),主键约束

      sname  varchar2(20) , --学生姓名

      spwd   char(6), -- 学生密码

      sbirthday  date,--出生年月

      smoney      number(7,2),     --账户金额

      job      varchar2(20), --学生职位

      saddress  varchar2(20),--学生籍贯

      sphone  char(11)   --学生手机号码

 

)

 

--查询表

select   *   from  t_students

 

 

-- 插入数据

insert  into   t_students  values(1, "赵蓉","12345",

to_date("1992-12-03","yyyy-MM-dd"),500.65,"学生会委员",

"盐城","13913321089");

commit;

 

 

insert  into   t_students  values(2, "秦日霞","123456",

to_date("1992-11-03","yyyy-MM-dd"),1500.65,"学生会委员",

"盐城","13913321085");

commit;

 

 

-- 修改表

 

-- 表增加一个字段

alter table  t_students  add  ssex  char(4);

 

--表字段类型修改

alter  table t_students  modify  saddress  varchar2(15)

 

--删除表的字段

alter  table t_students   drop  column ssex ;

 

-- 删除整个表

drop  table t_students;

 

select  length(s.spwd) 

 from  t_students  s   where  s.sname="赵蓉"

 

 

 

-- 创建表

 

create  table  t_employees

(

    eid   number(6) primary key, -- 员工编号  主键

    ename  varchar2(20),  --员工姓名

    epwd  char(5) , -- 员工密码

    ebirthday  date, --出生年月

    esalary   number(7,2), --工资

    ejob  varchar2(20), --职位

    esex  char(4) --性别

 

)

 

-- oracle序列机制,自增长机制    sequence

create  sequence  seq_employees

start  with 100 --开始编号

increment  by  2  --步长

nocache  --没有缓存,立即生成

 

--查看表结构

desc  t_employees

 

select  *  from  t_employees  for  update

 

 

select  *  from  t_employees  where

 

 

 

--数据操作语言(select ,insert,update,delete)DML

--select 操作 ,不需要commit  70%

--insert,update,delete  都需要commit  30%

 

--查询

select   *   from  t_employees

 

--插入

-- 插入数据

insert  into  t_employees  values(seq_employees.nextval,

"胡瓜","1234",to_date("1992-12-30","yyyy-MM-dd"),10000.88,

"职工","男");

 

insert  into  t_employees  values(seq_employees.nextval,

"李欣","11111",to_date("1992/08/30","yyyy/MM/dd"),12000.88,"组长","男");

commit;

 

--更新数据

update  t_employees e set e.epwd="12345" ;

commit;

 

update t_employees  e  set  e.epwd="00000"  where e.ename="李欣";

commit;

 

--删除数据

delete  from   t_employees

 

delete  from  t_employees  e  where e.eid=100;

commit;

 

 

 

 

--约束

 

--1.主键约束,唯一不能重复,数据唯一性  primary key

--2.非空约束 这个字段的取值不能为空   not null

--3.默认约束  这个字段取值如果不给值,取默认值  default

--4.检查约束  这个字段取值是一定范围内  check(字段取值范围)

--5.外键约束  一个字段取值从属于另外一个的值

 

drop  table  t_stus

 

create  table  t_stus

(

    sid  number(3)  primary key,

    sname  varchar2(20)   not null

   

 

)

 

insert  into  t_stus   values(seq_stus.nextval,null);

commit;

 

insert  into  t_stus   values(seq_stus.nextval,"李欣",to_date("1976-12-30","yyyy-MM-dd"));

commit;

 

insert  into  t_stus   values(seq_stus.nextval,"李欣1",default);

commit;

 

insert  into  t_stus   values(seq_stus.nextval,"李欣1",default,"男");

commit;

 

 

select   *  from   t_stus

 

--default  是默认,sysdate是oracle默认时间的关键字

alter  table  t_stus  add  birthday  date  default sysdate;

 

alter  table  t_stus  add   sex  char(4)

 check(sex  in("男","女"));

 

 

 -- 创建一张表,树状菜单,自身关联表 一对多

 create  table  t_menu

 (

     fid   number(4)  primary  key, --父id

     fmenuname  varchar2(20)  not null, --父菜单名称

     cmenuuname  varchar2(20) not null,-- 子菜单名称

     cid       number(4)   references t_menu(fid)  --子id  外键约束

 

 

 )

 

 select  *  from   t_menu

 

 

 

 

 

 

-- oracle序列机制   自增长机制

create  sequence   seq_stus

start  with  1

increment  by 1

nocache;

 

--查询序列的当前值  dual是个系统表

select   seq_stus.currval  from  dual;

 

drop  sequence  seq_stus

 

 

 

 

insert  into  t_stus  values(seq_stus.nextval,"李欣");

commit;

 

select   *  from   t_stus

 

delete  from  t_stus

 

--mysql自增长    auto_increment 从起始编号为1,每次增加1.

--sqlserver自增长  identity(10,1)

 

 

 

 

-- oracle内置函数

 

--to_date(参数1,参数2),把一个字符串类型的日期转换成date类型,

--参数2,按照什么日期格式

 

--length() 获取结果的长度

 

--to_char()转换成字符串类型

select   to_char(sysdate,"yyyy")

-to_char(e.ebirthday,"yyyy")  age

  from   t_employees  e

 

-- 拼接字符串concat()

-- 输出一段描述"胡瓜的职位是什么"

select concat(concat(e.ename,"职位是:"),e.ejob) 描述

 from  t_employees  e  where e.ename="胡瓜";

 

 

-- ||连接

select  e.ename||",职位是"||e.ejob  from   t_employees e

 

--nvl()函数,取值为null,给默认值

select *   from   t_employees e  for  update

 

--增加奖金字段

alter  table  t_employees  add  ecomm  number(5,2)

 

-- 算出每个员工的收入

select  e.ename,e.esalary+e.ecomm    from   t_employees  e

select  e.ename,e.esalary+nvl(e.ecomm,0)    from   t_employees  e

 

 

--substr()截取

alter  table  t_employees  add  ephone  char(11) 

--号段

select  substr(e.ephone,0,3) from

 t_employees  e  where  e.ename="胡瓜"

 

 --to_number()转换成数值的函数

 

 

--聚合函数 count(),max(),min(),sum(),avg()

select   count(*)  from  t_employees

select   max(e.esalary)  from  t_employees  e

 select   min(e.esalary)  from  t_employees  e

 select   avg(e.esalary)  from  t_employees e

--事务?????

--手动事务,自动事务,可以设置

 

 

-- 3查询技术  70%

 

-- 3.1 条件查询   where关键字

 

-- 查询姓名是李欣的所有信息

select   *  from  t_employees  e  where e.ename="李欣"

 

--查询姓名是李欣,密码是12345的这个用户是不是合法用户

select  count(*) from  t_employees  e  where e.ename="李欣"  

and e.epwd="1111"

 

select  *  from  t_employees  e  where  e.esex="男"

or  e.ename="李欣"

 

--3.2  比较查询 >,<,>=,<=,!= <>

select  * from  t_employees e  where e.esalary!=5000

 

select  * from  t_employees e  where e.esalary<>5000

 

 

--3.3  模糊查询

 

--查询姓李的人员的信息

select   * from  t_employees  e  where  e.ename  like "李%"

 

--查询姓李的人员的,但是是两个字的人员信息

select   * from  t_employees  e  where  e.ename  like "李__"

 

--3.4  排序

 

--asc  desc

 

select   *  from  t_employees  e  order  by e.esalary asc

select   *  from  t_employees  e  order  by e.esalary desc

 

--3.5 分组

 

--group by  统计就是和聚合函数在一起使用。

 

--统计这个单位的男女 性别数量  分组条件是明确

select   e.esex,count(e.esex)

 from   t_employees  e  group  by e.esex

 

 --

 select  *  from    t_employees  for  update

 -- 统计每个年龄员工的数量 select  count(*),   分组条件是计算的

 select  count(*),

 to_char(sysdate,"yyyy")-to_char(e.ebirthday,"yyyy") age

 from   t_employees  e  group by

 to_char(sysdate,"yyyy")-to_char(e.ebirthday,"yyyy")

 

 --统计每个职位的数量

 select  e.ejob,count(*)

 from   t_employees  e  group  by  e.ejob

 

 --统计学历,统计籍贯

 

 --统计每个年龄数量大于1  分组后带有限制条件

  select  count(*),

 to_char(sysdate,"yyyy")-to_char(e.ebirthday,"yyyy") age

 from   t_employees  e  group by

 to_char(sysdate,"yyyy")-to_char(e.ebirthday,"yyyy")

 having count(*)>1

 

 --统计每个年龄数量,数量按升序(先分组,后排序)分组带排序

   select  count(*),

 to_char(sysdate,"yyyy")-to_char(e.ebirthday,"yyyy") age

 from   t_employees  e  group by

 to_char(sysdate,"yyyy")-to_char(e.ebirthday,"yyyy")

  order  by count(*)

 

 

-- 子查询  一个查询的结果作为另外一个查询的条件

 -- 找出工资最高的人的姓名

 select   *  from   t_employees

 

 select  *  from  t_employees  e where e.esalary=

 (select  max(e.esalary)  from   t_employees e)

 

 

--3.6 in ,not in ,between and  ...any  all关键字

 

-- =只能等于一个值

select   * from  t_employees  e  where e.esalary=10000.88

-- in可以是多个值

select   * from  t_employees  e  where e.esalary

in(10000.88,5000.00)

 

select   * from  t_employees  e  where e.esalary

not  in(10000.88,5000.00)

 

select  * from  t_employees e  where  e.esalary   between  4000

and  20000;

 

select  * from   t_employees e  where   e.esalary >=4000

and e.esalary <=20000

 

--any  >是比最小的大的信息,<是比最大的小信息

select  e.esalary,e.ename  from t_employees e  where e.esalary<any

(select  e.esalary    from t_employees  e where e.ejob="职工")

 

--all  >是比最大的大的信息,<是比最小的小信息

select  e.esalary,e.ename  from t_employees e  where e.esalary < all

(select  e.esalary    from t_employees  e where e.ejob="职工")

 

select   *  from  t_employees  for  update

 

 

-- 查询1991年到1999年出生的员工的信息

 

select   *  from  t_employees

 

select   *  from  t_employees  e 

where  to_number(to_char(e.ebirthday,"yyyy"))

  between 1991 and  1999

 

 

 

 

--****

 

 

insert  into  t_stus   values(seq_stus.nextval,

"李二",default,"男")

 

 

select  *  from   t_employees  for  update

 

 

--- oracle高级查询技术

 

-- 1.子查询

 

-- 逻辑,就是从一个条件出发去找关联的条件。

 

-- 一个查询的结果作为另一个查询的条件

 

-- 找出员工工资最高人的信息

select  *   from   t_employees  e  where e.esalary  in

(select  max(e.esalary)   from  t_employees  e)

 

--找出和李欣职位不同的员工的信息

 

select  *   from  t_employees  e  where e.ejob!=

(select  e.ejob  from  t_employees  e  where e.ename="李欣")

 

 

--找出比员工平均工资高的员工的信息

select  *   from  t_employees  e  where e.esalary>

(select  avg(e.esalary)  from   t_employees  e)

 

--找出收入最高的员工的信息

 

select  *  from   t_employees  e where e.esalary+nvl(e.ecomm,0)=

(select   max(e.esalary+nvl(e.ecomm,0))  from   t_employees  e )

 

 

--oracle分页查询 局限性 ,一条sql语句对应一张表,造成SQL冗余

 

--oracle特殊的列,伪列  rownum ,它始终在第一行,不能移动

 

select  rownum, e.*  from  t_employees  e

 

--查询前三条的数据

select rownum, e.*    from  t_employees  e  where rownum<=3

 

select rownum, e.*    from  t_employees  e  where rownum<=2

 

-- 查询第2条到第4条之间的数据,查询出是空的

select rownum, e.*    from  t_employees  e  where rownum>=2

and rownum<=4;

 

select rownum, e.*    from  t_employees  e  where rownum

between 2  and  4

 

--rownum始终在第一行,导致你查询出来的是空的。

 

-- 查询第2条到第4条之间的数据

--通过临时结果集过渡查询

select  *  from  

(select rownum  rm,e.*  from  t_employees  e  where rownum<=4)  tmp

where  tmp.rm>=2

 

select rownum, e.*    from  t_employees  e

 

--分页查询

 

-- 最大的编号:<=第几页*每页条数

--起始编号:>(第几页-1)*每页条数

 

select  *  from  t_employees  e

 

--每页的条数是2条

--第一页数据  第一页,2条  out:结果集

select  *   from  

(select   rownum  rm,e.* from  t_employees  e  where  rownum<=1*2)

tmp  where tmp.rm>(1-1)*2

 

 

--第二页数据

select  *  from  

(select  rownum  rm,e.*   from  t_employees  e  where  rownum<=2*2)

tmp  where tmp.rm>(2-1)*2

--第三页数据

 

select  *  from  

(select  rownum  rm,e.*   from  t_employees  e  where  rownum<=3*2)

tmp  where tmp.rm>(3-1)*2

 

 

-- 在实际的项目中的一个

--局限性 ,一条sql语句对应一张表,造成SQL冗余 ,

--比如;100张表需要100个sql吗?

 

 

 

--2.集合查询

 

--集合查询

-- 多个结果集的查询

-- 工资大于3000的和职位是组长的两个查询结果的合并,过滤重复

-- 合并不过滤重复,查询的两个结果合并

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob="组长"

union all

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

--合并过滤重复

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob="组长"

union

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

--交集,两个查询结果集都有的

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob="组长"

intersect

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

--差集

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob="组长"

minus

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

--3.decode()函数查询  分支查询

 

select  *   from 

t_employees

 

--科长工资加1.2倍,组长工资加1.1倍,其它职位保持原有,

--请输出加薪后的所有的员工的

--信息

select  e.ename,e.ejob,e.esalary,

decode(e.ejob,"科长",e.esalary*1.2,

"组长",e.esalary*1.1,

esalary)  加薪后的工资

from  t_employees  e

 

-- 等同于case..when

select  e.ename,e.ejob,e.esalary,

case e.ejob when "科长" then e.esalary*1.2

            when "组长" then e.esalary*1.1

            else  e.esalary  end

              

加薪后的工资       

from  t_employees  e

 

--4.多表查询(2表查询,第三范式)

 

--之前讲的都是单表查询,进入到多表(>1)查询

 

--第三范式

-- 表中的列只能参照一个主键字段

 

-- 员工表 员工id,员工姓名,部门名称,部门id, 拆分形成两个实体表

 

--实体表之间的关系,主要有三种:

--1.一对一关系  员工表和角色表

--2.一对多关系   部门表和员工表  商品分类表和商品表

--3.多对多关系    学生和课程表(中间关系表,第三方表,成绩表)

          

--实体表之间的关系图,ER图

 

--这个关系的定义是根据现实的业务来决定的。

select   *  from  t_employees

 

--建立一个部门表  部门和员工表之间的关系是1对多

create  table  t_depts

(

    did  int   primary  key,

    dname  varchar2(20) not null,

    daddress  varchar2(50),

    dphone   char(11)  not null

 

)

 

create  sequence  seq_depts

start with  10

increment  by 2

nocache;

 

 

insert  into  t_depts 

values(seq_depts.nextval,"质量部","南京江北新区","13913321089");

insert  into  t_depts 

values(seq_depts.nextval,"技术部","南京江宁区","13913321086");

insert  into  t_depts 

values(seq_depts.nextval,"人力资源部","南京鼓楼区","13913321085");

commit;

 

 

select  *   from   t_depts  for  update

 

select  *  from  t_employees  for  update

 

-- 员工和部门之间是有关系的

 

alter  table t_employees  add  deptid   int;

 

 

--外键约束????????????

alter  table  t_employees  add  constraints  fk_emp_depts

 

 

-- 表的设计,软件核心来自于数据

 

-- 查询这两张表的数据  笛卡尔积 表1*表2

select  *  from  t_employees,t_depts

 

--等值连接

select   *  from    t_employees  e,t_depts  d

where e.deptid=d.did

 

 

--内连接查询inner  join  

select   *  from    t_employees  e  inner  join  t_depts  d

on e.deptid=d.did

 

-- 查询员工李欣的部门名称

select  d.dname,e.ename from  t_employees   e  inner  join 

 t_depts  d

on   e.deptid=d.did  where  e.ename="李欣"

 

--子查询 的效率比内连接查询要低

select  d.dname  from  t_depts  d where d.did=

(select  e.deptid   from  t_employees   e  where e.ename="李欣")

 

--左连接

select  d.dname,e.ename from  t_employees   e  left  join 

 t_depts  d

on   e.deptid=d.did 

 

 

--右连接

select  d.dname,e.ename from  t_employees   e  right  join 

 t_depts  d

on   e.deptid=d.did 

 

 

--全连接

 

select  d.dname,e.ename from  t_employees   e  full  join 

 t_depts  d

on   e.deptid=d.did

 

 

--

select   *  from  t_depts

 

--  统计技术部人员的工资的总和

 

select   sum(e.esalary) 工资总和  from  t_depts  d  inner  join  t_employees e  on d.did

=e.deptid  where d.dname="技术部"

 

--  统计每个部门的人员的数量

select   count(e.ename) 数量, d.dname from  t_depts  d 

left  join  t_employees e  on d.did=e.deptid 

  group  by  d.dname

=e.deptid

 

--统计每个部门的人员的数量大于1的信息  group  by 限制条件  having

 

select   count(e.ename) 数量, d.dname from  t_depts  d 

left  join  t_employees e  on d.did=e.deptid 

group  by  d.dname

having count(e.ename)>1

  

 --统计每个部门的人员的数量按降序排列

select   count(e.ename) 数量, d.dname from  t_depts  d 

left  join  t_employees e  on d.did=e.deptid 

group  by  d.dname   order by   count(e.ename)  desc

 

 

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

 

 

---pl-sql块

--块:一组SQL语句在一起运行,解决复杂的业务逻辑。

-- 是不能够被编程语言所调用  java,python,c#

 

--块的基本结构

/**

 declare

 

            ---定义的变量

 begin

  

 

             -- 一组sql语句

 

 end;

 

 

 

**/

 

 

 

/**

变量v_

变量赋值:=

 

 

**/

 

-- 计算两个数值类型的变量的和并输出

declare

   v_num1 number:=100;

   v_num2 number:=10;

   v_sum number;

begin

 

    

   v_sum:=v_num1+v_num2;

  

   dbms_output.put_line("计算这两个数的和为"||v_sum);

  

end;

 

 

-- 异常处理  计算两个数值类型的变量的商并输出

declare

   v_num1 number:=100;

   v_num2 number:=0;

   v_sum number;

begin

 

    

   v_sum:=v_num1/v_num2;

  

   dbms_output.put_line("计算这两个数的和为"||v_sum);

  

--异常处理块

exception   --捕获异常

    when  others  then

        dbms_output.put_line("v_num2这个数作为除数不能为零");

  

end;

 

-- 流程控制语句

-- 一个数判断是奇数还是偶数,并输出信息

 

declare

v_num  number:=22;

 

begin

 

  -- oracle不支持%取模,取模函数mod()

  if   mod(v_num,2)=0  then

  

       dbms_output.put_line("v_num这个数是偶数");

 

  else

       dbms_output.put_line("v_num这个数是奇数");

  end  if;

 

end;

 

 

--优化

declare

v_num  number:=22;

v_str  varchar2(100);

begin

 

  -- oracle不支持%取模,取模函数mod()

  if   mod(v_num,2)=0  then

   v_str:="v_num这个数是偶数";

     

 

  else

     v_str:="v_num这个数是奇数";

      

  end  if;

 dbms_output.put_line(v_str);

end;

 

-- 查询李欣这个员工的性别,如果是男的,奖金加1000,

--如果是女的,奖金加500,

-- 输出他现在的收入输出

/**

1.赋值:=  直接给变量赋值

2.从sql语句查询的结果进行赋值。2.1查询出来的是一个值,into

2.2 如果是多个值,不能用into,要用游标遍历

 

**/

 

 

declare

v_esex  t_employees.esex%type;  --这个变量的类型参照表中的字段类型

v_usaraly  t_employees.esalary%type;

v_sum  number(10,2);

begin

 

  --1.查询李欣这个员工的性别

  select  e.esex   into   v_esex

   from  t_employees  e  where e.ename="李欣";

  

   --2.判断是男还是女

  

   if   v_esex="男"   then

    

       v_usaraly:=1000;

  

   else

    

      v_usaraly:=500;

  

   end  if;

  

   --执行更新

   update   t_employees  e  set  e.esalary=e.esalary+v_usaraly

   where e.ename="李欣";

  

   commit;

  

  

   -- 查询出现在的收入

  select  e.esalary+nvl(e.ecomm,0)  into  v_sum   from  t_employees  e where e.ename="李欣";

 

 

  dbms_output.put_line("李欣这个员工的性别是:"||v_esex||",他现在的收入为:"||v_sum);

  --select  e.ename from  t_depts  d  inner join  t_employees  e

  -- on d.did=e.deptid  where d.dname="质量部"

 

end;

 

 

-- 查询李欣的部门名称,如果是质量部,加1000,如果是技术部加2000,

--如果是人力资源部加500,其它部门加100

 

declare

v_dname  t_depts.dname%type;

v_ecomm   t_employees.ecomm%type;

 

begin

 

 -- 李欣的部门名称

 select  d.dname   into   v_dname  from  t_employees e   inner  join  t_depts d

 on e.deptid=d.did  where e.ename="李欣";

 

 if v_dname="质量部" then

  

 v_ecomm:=500;

  

 elsif  v_dname="技术部" then

 

   v_ecomm:=600;

 elsif v_dname="人力资源部"  then

 

   v_ecomm:=200;

 else

      v_ecomm:=100;

 

 end   if;

 

 

    update   t_employees  e  set  e.ecomm=e.ecomm+v_ecomm

   where e.ename="李欣";

  

   commit;

 

end;

 

 

-- 循环结构

 

--1.100之和并判断是奇数还是偶数

--  for  loop

declare

 

v_sum  number:=0;

 

begin

 

for v_i  in 0..100  loop

 

    v_sum:=v_sum+v_i;

   

end  loop;

 

  dbms_output.put_line("和为"||v_sum);

 

end;

 

--while loop

 

 

declare

v_i  number:=0;

v_sum  number:=0;

 

begin

 

   while v_i<=100  loop

    

      v_sum:=v_sum+v_i;

     

      v_i:=v_i+2; --改变循环变量的值

   end  loop;

 

dbms_output.put_line("和为"||v_sum);

 

end;

 

 

--循环结构使用

 

create  table   t_users

(

 

   id  int  primary key,

   tname  varchar2(20),

   tsex   char(6)

 

)

 

create  sequence  seq_users

start with  1

increment  by 1

nocache;

 

select  * from  t_users

 

--for循环批量数据

declare

begin

 

for  v_i  in 1..10000  loop

 

   if mod(v_i,2)=0  then

    

      insert  into  t_users  values(seq_users.nextval,"李"||v_i,"男");

     

   else

       insert  into  t_users  values(seq_users.nextval,"王"||v_i,"女");

  

   end  if;

 

end  loop;

 

commit;

end;

 

--while循环批量数据

declare

v_i  number:=1;

begin

 

while  v_i<=1000  loop

   insert  into  t_users  values(seq_users.nextval,"李"||v_i,"男");

 

v_i:=v_i+1;

 

 

end loop;

 

commit;

 

end;

 

---into是只能附一个值

-- 如果是多个值,就是游标,就是结果集,分为;cursor(显示游标),隐式游标

 

-- 查询员工表的中的数据,并输出每个人的姓名和性别

-- while..loop

declare

   --定义一个显示游标

   cursor  v_datas is select  *  from  t_employees;

   -- 定义一行

   v_linedatas  t_employees%rowtype;

 

begin

 

   

   --打开这个游标

   open  v_datas;

  

   --遍历游标

   fetch  v_datas  into v_linedatas; --遍历第一行

  

     while  v_datas%found  loop -- 如果有数据进入循环体

       

        dbms_output.put_line(v_linedatas.ename||",性别是:"||v_linedatas.esex);

      

         fetch  v_datas  into v_linedatas;--移动到下一行

     end  loop;

  

  

   -- 关闭游标

   close  v_datas;

end;

 

-- for..loop

 

declare

   --定义一个显示游标

   cursor  v_datas is select  *  from  t_employees;

 

begin

 

   for v_linedatas in v_datas loop

  

    dbms_output.put_line(v_linedatas.ename||",性别是:"||v_linedatas.esex);

   end  loop;

  

   -- 关闭游标

end;

 

-- 隐式游标

 

-- 查询质量部员工的性别,是男加1000,是女加5000

 

declare

v_sex  t_employees.esex%type;

v_comm  t_employees.ecomm%type;

 

begin

     

   --质量部的员工的信息

  for  v_datas  in ( select  e.esex ,e.ename  from  t_employees  e   right  join  t_depts  d

   on  e.deptid=d.did  where d.dname="质量部")  loop

  

     dbms_output.put_line(v_datas.esex||v_datas.ename);

    

     if v_datas.esex="男"  then

      

       v_comm:=200;

     else

       v_comm:=50;

     end  if;

    

     --执行更新

     update  t_employees e  set e.ecomm=e.ecomm+v_comm

     where e.ename=v_datas.ename;

     commit;

  

   end  loop;

 

end;

 

 

select   *  from   t_employees

 

 

---存储过程

 

-- 存储(是以一个名字来存储)+过程(过程化的语句块)

 

-- pl-sql块是不能够被编程语言直接调用,只能运行在数据库端

-- 以一个名字命名,这个名字被编程语言call,这样形成交互

 

--预编译,编译一次,下次调用的话不需要再次编译,性能好,

--能够处理复杂的业务逻辑,

--可以有传入和输出参数,缺点:占用存储空间

--sql(dml语言,调用一次编译一次),性能没有存储过程好

 

--基本结构

create  or  replace  procedure   存储过程的名字

(

 

      --传入和输出参数

 

)

as

 

 

 

begin

 

 

 

 

end;

 

---检查登录

create  or  replace  procedure   p_checklogin

(

 

      v_uname  in   varchar2, --传入参数不需要长度

      v_pwd   in   varchar2,

      v_msg  out   varchar2

)

as

v_count  int;

 

 

begin

 

   --检查登录

   select count(*) into  v_count  from  t_employees  e  where e.ename=v_uname

  

   and   trim(e.epwd)=v_pwd;

  

   

   --进行判断

   if  v_count>0 then

    

 

 

 

    v_msg:="登录成功";

   else

    

      v_msg:="登录失败";

     

   end  if;

  

   --记录

   insert  into  t_userlog  values(seq_userlog.nextval,v_uname,default,v_msg);

   commit;

  

  

end;

 

-- 变更存储过程

 

create table  t_userlog

(

    ulid   int   primary key,

    uname varchar2(20), -- 登录的人

    logintime  date  default  sysdate, --登录的时间

    loginresult  varchar2(20)   --登录的结果

 

)

 

create  sequence  seq_userlog

start  with 1

increment  by 1

nocache;

 

 

select  *  from  t_userlog

 

-- 直接返回一个结果集(不在存储过程内部遍历)

--**部门的员工的数量和员工的姓名

create  or  replace  procedure  p_queryempDatas

(

 

    v_dname  in  varchar2,  --部门名称

    v_count  out  int,  --部门员工数量

    v_namedatas   out  sys_refcursor  --部门员工姓名

)

as

 

begin

 

  --1.通过部门名称得到部门的员工的数量

  select count(e.ename)  into  v_count   from  t_employees  e

   right  join  t_depts d

  on  e.deptid=d.did  where d.dname=v_dname;

 

 

  --2.员工姓名(不在存储过程内部遍历)

  open  v_namedatas   for select e.ename    from  t_employees  e 

  right  join  t_depts d

  on  e.deptid=d.did  where d.dname=v_dname;

 

 

end;

 

----**部门的员工的数量和属于这个部门的员工的性别,

--如果是男,工资加250;

--女加100;

 

 

create   or  replace   procedure  p_querydeptupdatesalary

(

   v_dname  in   varchar2,

   v_count  out  int

)

as

v_salary  t_employees.esalary%type;

begin

 

    --1.通过部门名称得到部门的员工的数量

  --select count(e.ename)  into  v_count   from  t_employees  e

   --right  join  t_depts d

  --on  e.deptid=d.did  where d.dname=v_dname;

 

  --调用函数,减少冗余代码

  v_count:=f_querydeptempnum(v_dname);

 

  --2.这个部门的所有员工的信息

 

  for  v_linedatas  in (select  *     from  t_employees  e 

  right  join  t_depts d

  on  e.deptid=d.did  where d.dname=v_dname)  loop

 

  if v_linedatas.esex="男" then

   

     v_salary:=250;

   

  else

   

      v_salary:=150;

 

  end  if;

 

 

  --执行更新

  update  t_employees  e  set  e.esalary=e.esalary+v_salary

  where  e.ename=v_linedatas.ename;

 

  commit;

 

 

  end  loop;

 

 

end;

 

 

 

select  *  from  t_employees

 

-- 自定义函数  特殊的存储过程

-- 自定义函数  1.关键字function  2.只能返回一个值

create   or  replace  function  f_querydeptempnum

(

   v_dname  in  varchar2

)

return int --切记这个地方不能加;

as

 

v_count  int;

begin

 

    select  count(e.ename)  into  v_count  from  t_employees e

    right  join  t_depts  d  on  e.deptid=d.did

    where  d.dname=v_dname;

   

   

    return v_count;

 

end;

 

-- 查询一个表t_employees的条数  如果系统中的表很多,这样会造成冗余

create   or  replace  function  f_queryempcount

return  int

as

 

v_count  int;

begin

 

    select  count(*)   into  v_count from  t_employees ;

    return  v_count;

 

end;

 

--动态sql

 

-- 一个值

create   or  replace  function  f_querytablecount

(

 

   v_tableName   in  varchar2

)

return  int

as

 

v_sql varchar2(1000);

 

v_count  int;

begin

 

   v_sql:="select   count(*)  from  " ||v_tableName;

  

  

   -- 执行这个动态sql,是一个值

   execute immediate  v_sql  into v_count;

  

   return v_count;

 

end;

 

---多个值

create   or  replace  function  f_querytabledatas

(

 

   v_tableName   in  varchar2

)

return  sys_refcursor

as

 

v_sql varchar2(1000);

 

v_datas  sys_refcursor;

begin

 

   v_sql:="select   *  from  " ||v_tableName;

  

  

    -- 执行这个动态sql,是一个结果集

    open v_datas  for v_sql;

   

    return v_datas;

   

end;

 

 

--sql语句分页  固定2条

 

select   *  from  t_employees

 

-- sql分页,系统大了,会造成SQL冗余

select   * from (select  rownum  rm, 

e.* from  t_employees  e  where  rownum<=4)  tmp

where tmp.rm>2

 

 

--存储过程分页 一个存储过程可以对系统中的表都可以分页

-- tablename  in  表名

--pagenum   in   每页几条

--currentnum  in  第几页

--datas  out  每页的结果集

--count  out  总条数

--pagesize  out  总页数

create   or   replace    procedure   p_pagemodel

(

 

    v_tablename  in   varchar2,

    v_pagenum   in    int,

    v_currentnum  in  int,

    v_datas  out  sys_refcursor,

    v_count   out   int,

    v_pagesize  out  int

 

)

as

v_endindex  int:= v_currentnum*v_pagenum;

 

v_startindex  int:=(v_currentnum-1)*v_pagenum;

 

v_sql  varchar2(1000);

begin

 

   --1.构建总的条数动态sql

   v_sql:="select   count(*)   from     "||v_tablename;

  

   --2.执行动态sql

   execute  immediate  v_sql  into v_count;

  

   --3.获取总页数

   if   mod(v_count,v_pagenum)=0  then

    

     v_pagesize:=v_count/v_pagenum;

    

   else

      --除不尽

     v_pagesize:=floor(v_count/v_pagenum)+1;

    

   end  if;

  

   --获取分页结果集

   v_sql:="select   *   from  "||"(select  rownum  rm, e.* from  "||v_tablename||

    "   e  where  rownum<="||v_endindex||")   tmp" ||" where tmp.rm>"||v_startindex;

   

   dbms_output.put_line(v_sql);

  

   --执行动态sql,返回的是一个结果集

   open v_datas  for v_sql;

   

end;

 

-- 面试的时候,什么是事务

--jdbc 事务  自动事务

--hiernate事务

--mybatis事务

 

--spring事务

 

--python事务  手动事务 

 

--  rollback回滚事务  commit提交事务  savepoint 设置事务保存点

--数据库事务

--事务就是一个工作单元,所谓的工作单元,就是不可分割的一个或多个SQL

-- (insert ,update,delete)

 

--秦日霞借钱1000给李欣,并记录日志。

select  *  from  t_employees

 

 

--秦日霞借钱1000给李欣

--记录日志

create  or   replace  procedure   p_operatorsalary

(

 

   v_rname  in  varchar2,

   v_tname  in  varchar2,

   v_money  in  number,

   v_msg   out  varchar2

 

)

as

begin

 

    -- 1.先把借钱人的钱扣掉

    update  t_employees  e  set

    e.esalary=e.esalary-v_money  where e.ename=v_rname;

   

    --2.加上给借钱人

     update  t_employees  e  set

    e.esalary=e.esalary+v_money  where e.ename=v_tname;

   

    --设置一个事务保存点  给个名字

   

   

    savepoint  a;

   

    --3.记录日志

    insert  into  t_userlog 

    values(seq_userlog.nextval,v_tname,"1997-12-30","借钱");

   

    commit;

  

exception

 

when  others  then

  rollback to a;  --回滚到事务保存点a,提交

  commit;

  

end;

 

 

select   *   from  t_userlog

 

 

 

 

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

 

--索引,job(定时任务),视图,触发器

 

-- 索引

-- 怎么来提高查询的性能?

 

-- 1.索引   2. SQL优化

 

-- 索引就是来提高查询的性能。

-- 打个比方来说:书的目录 聚集索引 ;

--书的页码:聚集索引

 

-- 70W条的数据以上我们才考虑建立索引

 

-- 建立一张表

create  table  t_stuinfo

(

 

    suid  number(7)  primary  key,

    sname  varchar2(100),

    sbirthday  date  default  sysdate,

    ssex  char(4)

 

)

 

 

 

--加载80W条数据

 

declare

begin

   for v_i  in 1..1200000 loop

    

       if mod(v_i,2)=0  then

        

      

         insert  into  t_stuinfo  

         values(v_i,"李"||v_i,default,"男");

      

        

       else

     

  

         insert  into  t_stuinfo  

           values(v_i,"王华"||v_i,default,"女");

   

       end  if;

   end  loop;

  

   commit;

 

end;

 

delete  from  t_stuinfo

 

select  count(*)  from  t_stuinfo

 

 

select  *  from  t_stuinfo

 

-- 白盒测试 查询  王华67001 查这个人的信息

create  or  replace  procedure  p_queryinfo

(

    v_name  in  varchar2

 

)

as

v_begintime  varchar2(1000);

v_endtime   varchar2(1000);

v_birthday t_stuinfo.sbirthday%type;

begin

 

       v_begintime:=to_char(systimestamp,"yyyy-MM-dd hh24:mi:ss.ff");

 

        select  s.sbirthday   into  v_birthday

        from   t_stuinfo s  where s.sname=v_name;

       

       

       v_endtime:=to_char(systimestamp,"yyyy-MM-dd hh24:mi:ss.ff");

     

     

      dbms_output.put_line("开始时间:"||v_begintime);

      dbms_output.put_line("接受时间:"||v_endtime);

   

end;

 

--

/**

开始时间:2018-03-23 09:29:31.490000000

接受时间:2018-03-23 09:29:31.537000000    --47000000

 

开始时间:2018-03-23 09:32:05.235000000

接受时间:2018-03-23 09:32:05.250000000    --15000000

 

--

开始时间:2018-03-23 09:32:52.942000000

接受时间:2018-03-23 09:32:52.958000000 16000000

 

 

开始时间:2018-03-23 09:33:26.529000000

接受时间:2018-03-23 09:33:26.576000000  47

**/

 

-- 建立索引

create  index    snameindex   on   t_stuinfo(sname);

 

drop  index  snameindex;

 

 

call   p_queryinfo("李966002");

 

-- job定时任务  数据库定时任务

 

--自动化定时任务  对于表的历史数据的一个定时清理

 

-- 1-10

 

-- t_stuinfo ,每隔1分钟自动插入一条数据

delete  from   t_stuinfo;

commit;

 

create  sequence   seq_stuinfo

start  with 1

increment   by  1

nocache;

 

--建立一个存储过程

create  or  replace  procedure  p_timetaskadddata

as

begin

 

  insert  into  t_stuinfo  

         values(seq_stuinfo.nextval,"李欣",default,"男");

    

  commit;

 

end;

 

 

--建立一个任务

variable  job2018  number;

 

-- 命令行窗口   提交这个定时任务

SQL> variable  job2018   number;

SQL> begin

  2  dbms_job.submit(:job2018,"p_timetaskadddata;",

  sysdate,"sysdate+1/1440");

  3  end;

  4  /

 

 

select   *   from   t_stuinfo

 

 

SQL> begin

  2  dbms_job.remove(:job2018);

  3  end;

  4  /

 

**PL/SQL procedure successfully completed

job2018

---------**

以上是 Oracle数据库小知识点整理 的全部内容, 来源链接: utcz.com/z/531333.html

回到顶部