Oracle基本入门及概念

Oracle

1：Oracle 最主要的服务

1：OracleOraDB11g_home1TNSLstener

​ oracle服务器监听器，用来监听客户端的连接请求该服务在使用Oracle是必须开启，否则不发链接Oracle数据库

2：OracleServiceOrcl：Oracle数据库服务。也要开启

2：sqlplus （Oracle自带的数据库操作命令行）

​ 在 win +R 输入 sqlplus 可以打开Oracle自带的命令行，在保证服务开启的情况下可以通过输入用户名 称和 密码登录到Oracle 我的默认用户名密码为（scott 密码为 admin）这是 其中 sysdba 密码 admin 是权限最大的一般 不使用

3：编写sql语句

​ 在Oracle下 每条语句必须以 分号 结尾（比如**select * from 表名；）同时如果是添加，删除，修改等语句的话

注意Oracle 默认是不提交事务的，需要手动commit提交事务 如（update 表名 set 字段 = 值 ； commit；）;

4:Oracle数据类型

1：字符类型

​ char（n）：固定长度的字符串，如果存入的数据长度不够 n 位 就自动补零，如果长度超过就截取

​ varchar2（n）:可变长度的字符串

  long :大文本最多可以存储2GB 一般不使用

2：数值类型（Oracle中所有数值都是Number）

​ number（m，n）：总长度m位，小数部分n为m和n可以省略 如果省略表示是整数

	 date：日期时间类型，精确到秒；

​ timestamp：日期事件类型，精确到微秒；

3：伪列（不存在的列，但可以查询，不能更新，删除和修改）

​ rowid：表中行的存储地址

	rownum：查询返回的结果集中行的序号，可以使用它来限制查询返回的行数，多用来**分页查询**

4：sql语言的分类

1：数据库定义语言（DDL）定义数据库，create，drop，alter

2：数据操作语言（DML）insert，update，delete，select 不会自动提交

3：事务控制语言（TCL）commit，rollback,savepoint 不会自动提交

4：数据控制语言（DCL）crant，revoke，会自动提交

5：常用函数

1：to_date(字符串，‘格式’)

​ 如 insert into 表名 values(to_date(‘2019-07-01’,‘yyyy-MM-dd’)) ; commit;

2：使用sysdate 获取当前日期

​ 查询当前日期：select sysdate from dual;

​ 使用：如：insert into 表名 values(sysdate); commit;

6: 索引

1：创建不唯一索引

​ create index 索引名称 on 表名 （表中字段）

2：创建唯一索引

​ create unique index 索引名称 on 表名（表中字段）

3：删除索引

​ drop index 索引名称；

7：序列（因为Oracle主键不会自动增长所以需要序列）

1：序列就是一个按一定规则产生整数的数子生成器可设置为递增或者递减

2：当序列可以是循环的，也可以是连续增加的，直到一个限制值为止

3：创建一个序列的语法：

• create sequence 序列名称
   increment by 1 -- 每次增加一
     start with 3 -- 从三开始
      minvalue 1  -- 最小值是一 可以省略
       maxvalue 10 --最大值为10 可以省略
        
       
  

  4：如果需要取出序列的值，可以使用一下两个关键字

​ currval:表示序列的当前值，刚刚创建的序列没有当前值，必须使用netval后才能获取到

​ netval：表示序列的下一个值 （一般使用这个）

​ 如：insert into 表名 values（序列名称.netval）; commit;

5：删除一个序列

​ drop sequence 序列名称;

8：Order By

​ 用来指定查询结果的排序，后面可以跟多个字段名称 默认为升序（asc 从低到高）desc （从高到低）

如：select * from emp Order by ename sac

​ select * from emp Order by ename desc

9：连接查询

​ 1：左外连接（left join）：返回左表的所有行

​ 2：右外连接（right join）：返回右表的所有行

​ 3：全连接（full join）：返回两表的所有数据

10：聚合函数

​ 1：avg（）计算平均值

​ 2：min（）计算最小值

​ 3：mav（）计算最大值

​ 4：sum（）计算总和

​ 5：count（）计算查询结果总数

11：取出重复的行

​ 实例：select disinct(字段名称) from 表名；

12：字符串函数

​ 1：lower(char) 大写字符转换为小写

​ 2：upper(char) 小写字符转换为大写

​ 3：length(char)字符长度

​ 4：instr(char,char) 查询指定的字符串，返回参数一中参数二所在的位置

​ 5：substr(char,m,n)返回截取的字符串 m表示开始位置从一 开始，n表示取几位

​ 6：to_char(日期，‘格式’) 将日期装换为字符串

​ 7：to_number(字符串，‘格式’)将字符串装换为数值

​ 8：nvl(m,n) 如果m的值为null，则函数返回 n

​ 如：select ename ，n5al(age,0) from emp;

13：日期函数

​ 1：sysdate获取日期和时间

​ 2：months_betwwn 获取两个日期之间的月份间隔

​ 3：to_date(字符串，‘格式’) 将字符串转换为日期

​

pl/Sql编程

1：简介

​ Pl/Sql是Oracle 公司提供的对sql语句的拓展，属于第四代编程语言，是一种快结构语句，主要有三种 形式：存储过程，函数，无名快

​ 当程序程序开头以 declare 或者 begin 开头的时候，系统会自动将其识别为pl/SQL语句，而非sql语 句当遇到 / 的时候表示结束

2：代码块 语句格式

 set serveroutput on ;  -- pl/sql 允许服务器输出信息 必须的
 
 declare  -- 代码块的开始
 	x varcher2(20); -- 声明变量  必须是Oracle支持的类型
 begin
 	x:='值';		-- 赋值
 dbms_output.put_line("输出");   --表示打印相当于 Systemout.println();
 end;  	--表示结束
 /

into语句

​ 用于在查询时，将查询出来的结果装配到指定变量中去

declare 
	a number;
begin
	select max(age)into a from emp;
end;
/

3：代码块 语句格式

pl/Sql 具有过程化语言的特性，可以使用顺序结构，选择结构，循环结构，和 goto结构等控制结构

​ 1：选择结构

​ if语句

​ 语法

​ if 条件 then

​ 语句组一;

​ elsif 条件二 then --注意

​ 语句组二;

​ else

​ 语句组n；

​ end if; --结束标志

例子：

• declare
  	a number:=95;
  begin
  	if a<60 then
  		dbms_output.put_line("值");
  	elsif a<80 then
      	dbms_output.pit_line("值");
      else 
      	-- 语句
      end if --结束
  end;
  /
  

2:循环结构

​ 循环结构值重复执行一组语句，直到达到指定循环条件的结束要求

​ loop语句

​ 基本格式为：

​ loop

​ 语句组；

​ exit when 结束条件

​ end loop;

​ for 循环

​ 基本格式为

​ for 循环变量 in 起始值…终止值 loop

​ 语句组；

​ end loop;

​ while循环 语法格式为：

​ while 条件 loop

​ 语句组；

​ end loop;

​ goto 结构又称跳转结构，语法格式为：

​ << 标记名称>>

​ goto 标记名称；

4：pl/Sql字段类型

1：%type

在使用**%type声明的变量类型**始终保持和数据库表中字段的数据类型相同，即使表中字段后来发生了变化

例子：

• declare 
  	ea emp.ename%type;//变量为ea 为emp.ename的类型
  begin
  	--操作
  end;	
  

2：%rowtype

声明为%rowtype类型的变量可以完整的存放某表中的一条记录的类型

例子：

• declare
   em emp%rowtype;
  begin
   select * into em from emp where eid = 7;
   dbms_output.put_line(em.ename||em.sex);
   end;
   /
  

5:游标

1：什么是游标?

游标的使用可以让用户像操作数组一样操作查询出来的数据集，简单来说游标就类似于数组。

2：使用游标的四个步骤

1：声明游标

​ 语法格式为 cursor 游标名称 is select 语句

2：打开游标o

​ 声明的游标必须打开才能使用

​ 语法格式为：open 游标名称。

3：使用游标

​ 一般在循环语句中使用fetch语句提取游标的记录来操作

4：关闭游标

​ 语法：close 游标名称；

• declare
  	cursor cur is select * from emp; -- 创建游标
  	em emp%rowtype;		
  begin
  	open cur; --开启游标
  	loop	  -- 开启循环
  	fetch cur into em; --每次将取出游标中数据给 em变量
  	exit when cur%notfound;	--定义循环结束条件
  		dbms_output.put_line(em....);
  	end loop;			-- 结束循环
  	close cur;			-- 关闭游标
  end;
  /
  

3：游标的四个属性

1：%isopen ：测试游标是否打开，返回boolean 值

2：%found：上一次fetch是否取到了记录

3：%notfound：上一次fetch是否没有取到记录（就是到了游标的最后）

4：%rowcount：返回游标中已经提取的记录行数

4：参数化游标

• declare
  	cursor cur(tim,date) is select * from emp where hire>tim; --创建游标
  	em emp%rowtype;
  begin
  	open cur(to_date(sysdate,'yyyy-MM-ddd'));--给游标的参数赋值
  	loop
  		fetch cur into em;		--循环游标
  		exit when cur%notfound;	--结束条件
  		dbms_output.put_line(em.ename);
  	end loop;
      close cur;
  end;
  /
  

6：存储过程

1：什么是存储过程？

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

2：基本语法

create or replace procedure 过程名称(

​ 参数1 【in/out/inout】类型,

​ – in 用来向过程中传一个值，该值在过程体中不能更改。

​ – on 用来从被调用的过程中返回一个值，该值可以被改变，不能有默认值

​ – 用来向过程中传入一个初始值，并返回更新后的值，不能有默认值

​ 参数2 【in/out/inout】类型

)

as

​ i number;

begin

​ – 操作

end;

调用该存储过程的代码为：

​ declare

​ k integer;

​ k integer;

​ begin

​ 存储过程名称(参数1，参数二)；

​ dbms_output.put_line(打印返回参数);

​ end;

存储过程体中不能使用return 返回值，而只能有输出参数带出返回的值，一个存储过程可定义多个输出参数，这意味着存储过程可以返回多个值

例子：

• --创建存储过程
  create or replace procedure pc(
  	a in number:=1,
      b out number
  )
  as 
  c = number:=1;
  begin 
  	b:=1;
  	while c<=a loop
          b:=b*c;
          c:=c+1;
  	end loop;
  end;
  -- 调用存储过程
  declare
  	a number:=10;
  	b number ;
  begin
  	pc(a,b);
  	dbms_output.put_line(b);
  end;
  /
  
  

3：创建函数

create or replace function 函数名称 【

​ (

​ 参数1[in | out | in out] 类型，

​ 参数2[in | out | in out] 类型

​ )

】

return 返回值类型

as

​ 函数局部变量声明

begin

​ 函数体

​ end；

​ 例子：

• -- 创建函数
  create or replace function f1
  (
      a in emp.eid%type
  )
  return emp.ename%type;
  as
  	num emp.ename%type;
  begin
  	select ename into num form emp where id = a;
  	return num;
  end;
  
  --使用函数
  declare
  	eid emp.eid%type;
  	ename emp.ename%type;
  begin
  	ename:=f1(eid);
  	dbms_output_putline(ename);
  end;
  
  

4：触发器

类似过程 和函数，在事件发生的时候被自动隐式触发，而触发器不接受参数。

语法格式

• create [or replace] trigger 触发器名称 [模式] 
  	before| after | instend of,
  	事件类型,  
  	表名,
  	触发条件
  declare 
  	参数声明
  begin
  	触发体
  end;
  
  

实例1：

​ 给emp表添加一个语句级触发器，当添加员工的时候，触发器触发，打印出当前员工人数

• create or replace trigger t1 after insert on emp 
  declare
   	a integer;
  begin
  	select count(*) into a from emp;
  	dbms_output_putline(a);
  end;	
  
  

实例2：

​ 给emp表添加一个行级触发器，当删除员工的时候，将删除员工添加到离职员工表

• create or replace trigger t1 update on emp 
  for each row --表示行级触发器 执行语句会返回多小条， 就执行多少次
  declare 
  begin
  	insert into oidemp(eid,ename)
  	values(:oid.eid,:oid.ename); -- :oid 表示在下面
  end;
  

​ oid：只能用于删除，或者修改触发器，它表示刚刚删除或者修改的一条记录，在使用的时候必须以

” :oid.列名“ 的格式

new：只能用于添加型 或者 更新型触发器，它表示刚刚添加的一条记录，在使用的时候必须以

“ ：new.列名” 的格式

oid 和new 只能用于行级触发器。

实例3：

​ 创建一个触发器当删除emp 表中一个员工 同时删除 工作记录

• create or replace trigger t1 delete on emp
  declare
  	begin
  	delete from jl where eid = :oid.eid;
  end;