Oracle细节，plsql语法大全
一．简单查询
1.Select 列名，列名..from 表名 ;
select e.empno,e.ename,e.job,e.sal from emp e（别名）;
2.带条件查询 select 列名,列名,...,列名 from 表名 where 条件表达式;
--查询名字叫SMITH的员工的信息
select * from emp where ename='SMITH';（字符串类型加单引号）
3.in(后面跟集合或者子查询) not in
Like 模糊匹配
%：表示在这个%出现的地方可以有0个或者多个字符
_: 表示在_出现的地方有且只能一个任意字符
--查询emp表中员工姓名第三个字符是A的员工信息
select * from emp where ename like '__A%';
like中的转义字符：
select * from emp where ename like '%_%' escape '';把后紧跟的字符转换成普通字符_,不然_的意思在这代表只能一个任意字符
all、any：后面都跟一个集合或者子查询
--查询工资比所有人（1600,2000,3000）都高的员工信息
select * from emp where sal > all(1600,2000,3000);
not exists:和exists 不和任何列一起使用，后面跟的是一个子查询（查询语句select语句），如果子查询能查出结果，那么这个条件就是成立的，查不出结果条件不成立
条件连接符 and or
Between value1 and value2 ;value1的值比value2值小,表示值在value1和value2之间
is null:表示这个值是空时条件成立
--select * from emp where comm is null;
is not null：表示值不为空时条件成立
--查询员工的年薪 Select ‘年薪’, sal*12 from emp;
4.排序分组
（1）排序
select 列名,列名,..,列名 from 表名 where 条件表达式 order by 排序列的列名（后可跟多个列，按照紧跟order by关键字的列排序，如果第一个排序字段的值相同，再按照第二个排序） asc（升序可省略）|desc（降序）;
注意：order by 后面除了可以跟表中的列名外，还可以跟select 和from之间查询结果的序号
select empno,sal,job,ename,deptno from emp order by 5,2 desc;（先按照查询出的第五列进行排序，再按照第二列排序）
（2）聚合函数
聚合函数：对一组值执行计算,并返回单个值
count(列名|*|常数):它求记录数（数据条数）
max(列名):取最大值
min(列名):取最小值
avg(列名):取平均值
sum(列名):求和
distinct:去重关键字,跟在列的最前面
- 分组，having
select 列名,列名,...,列名 from 表名where 条件group by 分组列having条件order by 排序列 asc|desc
--查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;
注意：group by 后面跟多个列时，只有当多个列的值同时相等时，它才会分为同一个组;--select job,deptno,count(*) from emp group by job,deptno;
having:它是对分组后的数据进行筛选，条件表达式中可以使用聚合函数
--求平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
where和having的异同
where:条件，where后面跟的条件比having后的条件先执行，条件中不允许使用聚合函数。
having：条件中可以使用聚合函数，一般having和group by联用。
5.集合，联合查询
集合：每一个sql查询的结果就是一个集合
并集：取两个集合中所有元素
Union all；取两个集合的并集，不删除重复数据
Union;去两个集合的并集，删除重复元素
交集：取两个集合重叠部分的元素 关键词：intersect
差集：第一个集合的所有元素减去两个集合重叠部分的元素 关键词Minus::表示差集（卖了思）
联合查询
交叉连接（笛卡尔积连接）select 列名,列名,...from a表 cross join b表;
select e.*,d.deptno dno,d.dname,d.loc from emp e cross join dept d;
select e.*,d.deptno dno,d.dname,d.loc from emp e,dept d;
内连接：把两个表中符合条件的数据连接为一条，如果哪个表中存在不符合连接条件的数据，那么这些数据就会被过滤掉（不显示）
select 列名,...,列名from a表 inner（可省） join b表 on 连接条件
自连接：特殊的内连接，内连接一般是两个表的连接，自连接将一个表和它自已进行连接
--查询员工信息，并显示它的上级姓名
select e1.*,e2.ename 上级姓名 from emp e1 join emp e2 on e1.mgr=e2.empno
不等连接：连接条件是不等条件,（大于、小于、不等于）
select e.*,s.grade,s.losal,s.hisal from emp e join salgrade s on e.sal between s.losal and s.hisal;
全连接：它会查出两个表中的所有数据
select 列名,列名,列名,....
from 表A full join 表B on 连接条件;
如果两个表中数据都符合条件，会将符合条件的数据连接为一条记录，如果第一张表中的数据在第二张表中没有，会显示第一张表中的数据同时第二张表的位置会为空，同理第二张表的数据在第一张表中查不到会显示第二张表的数据第一张表中的位置显示为空
左外连接；
select 列名,列名,...from 表A left join 表B on 连接条件
（lift join）查出左表的所有数据，根据连接条件去右表查询对应的数据
右外连接
select 列名,列名,列名,...from 左表 right join 右表 on 连接条件
自然连接在两张表中找数据类型和列名都相同的字段，自动连接起来，返回符合条件的结果
select * from emp join dept using(deptno); --自然连接using用来指定关系列
子查询
From后
Where后
Having后
--查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资
select deptno,count(*),avg(sal) from emp group by deptno having avg(sal)>1500;
Select 和from之间（子查询结果只能单行单列）
- 分页sql
伪劣：rownum 用来显示表中数字的行号，只连续的不考虑值相等的情况（值相同序号不相同）
--注意如果给伪列加条件，小于一个值是可以，不能让它大于某个值
--查询工资最低的5名员工信息
select t.*,rownum r from (select * from emp order by sal) t where rownum<=5;
dense_rank():可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
select e.*,dense_rank()over(order by sal desc) r from emp e;
- case when语句
Case
When 条件表达式1 then
When 条件表达式2 then
...
Else
默认值
end
- Oracle常用函数
行转列 将表中多行数据转成一行的多个字段输出
列传行
- 创建表空间和用户
System 是系统表空间，system表空间会掉，Oracle将无法启动
Users 是数据库默认表空间
查询当前用户默认表空间
Select default_tablespace from user_users;
创建表空间：必须使用管理员用户（system，sys）
create tablespace tablespace_name datafile '表空间文件路径' size 1G autoextend on next 100M maxsize unlimited;
Create tablespace (创建表空间关键字)tablespace_name（表空间名称） datafile（指定表空间文件） ‘表空间文件路径’ size 1g （指定表空间文件的是初始大小）autoextend （表示表空间大小自动扩展）on next 100M（指定每次表空间满了扩展的大小） maxsize（指定表空间的最大存储值） unlinited（表示不限制表空间的最大值）;
修改表空间
Alter database datafile 'D:appAdministratororadataorcltest1data.dbf' autoexted on next 200M maxsize 2G
创建用户
权限：系统权限grant create table to test1 此系统权限允许用户创建表
对象权限 grant update on scott.emp to test1 允许用户更新emp表中的记录
GRANT ALL ON SCOTT.EMP TO test1; --允许用户插入、删除、更新和查询 EMP 表中的记录
角色管理 一组相关权限的组合，可以将权限授予角色，再把角色授予用户，简化权限管理
Select * from role_sys_privs;--查看角色所包含的权限
Drop tablespace tablespace_name 删除表空间
数据类型 number (5,2)长度是五，有两位小数
Varchar2() 字符串类型
Date日期类型
建表语法
create table person (
id number(11) primary key--主键约束
name varchar2(20) not null--非空约束
age number(3),
sex number(1),
birthday date
Deptno number(7) reference dept(deptno)--外键约束
);
check:检查约束，表示这个列的取值要满足check后面的约束条件
修改表和约束
Alter table 表名 add列名 数据类型[约束][default默认值];给表添加一个列
Alter table 表名 drop column 列名删除表中一个列
alter table 表名 modify 要修改的列名 数据类型 [约束] [default 默认值];修改一个列
alter table 表名 drop constraint 约束名; 删除一个约束
插入语句
insert into table_name(col_name,列名,...col_name) values(value,值,..,value);
修改语句
update 表名 set 列名=值,列=值,...,列值 where 条件;
删除语句
delete [from] 表名 where 条件;
oracle数据库的表中的每一行数据都有一个唯一的标识符,或者称为rowid。使用rowid可以去除重复记录
索引 类似于书的目录提高查询效率
create index index_name on table_name(col_name[,col_name[,col_name,]]);
注意：使用索引列作为查询条件时，不要在索引列上进行数学运算
select * from emp where sal*12>10000; --这样写条件不会使用索引
select * from emp where sal>10000/12; --这样它就会使用索引
删除索引：
drop index 索引名;
视图以经过定制的方式显示来自一个或多个表的数据
视图可以视为“虚拟表”或“存储的查询”
创建视图所依据的表称为“基表”
CREATE [OR REPLACE] [FORCE] VIEW
view_name [(alias[, alias]...)]
AS select_statement
[WITH CHECK OPTION]
[WITH READ ONLY];
create view view_dept_emp as select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
序列 是oracle中自动生成一个序列的对象，一般用来生成表的主键的值
创建一个序列
create sequence sequence_name 序列关键字
start with startValue指定序列的初始值
increment by inrValue指定序列的值每次增加多少
minvalue minValue指定序列的最小值
maxvalue maxValue指定序列的最大值
cache|nocache 指定序列缓存，默认缓存20个
cycle|no cycle指定序列是否循环生成
create sequence my_seq start with（开始） 1 increment by （增加几）1;
序列的两个属性
nextval:取序列的值，并且序列的值增加select my_seq.nextval from dual;
currval:取序列的当前值，序列值不会变select my_seq.currval from dual; --不能直接取当前值
同义词 公有同义词 私有同义词
创建同义词语法：
create [public] synonym synonym_name for object（去痛一次的对象，可以是表，视图等）;
--创建或替换现有的同义词
CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;
事务作为单个逻辑工作单元执行的一系列操作。这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。 事务是一个不可分割的工作逻辑单元。
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
Commit 事务提交
Rollback 事务回滚
锁 锁是数据库用来控制共享资源并发访问的机制。锁用于保护正在被修改的数据
直到提交或回滚了事务之后，其他用户才可以更新数据
行级锁一种排他锁，防止其他事务修改此行
在使用以下语句时，Oracle会自动应用行级锁：
UPDATE、
DELETE、
SELECT … FOR UPDATE
表级锁 锁定整个表，限制其他用户对表的访问
使用命令显示的锁定表，应用表级锁的语法是:
LOCK TABLE table_name IN mode MODE;
PLSQL
1.基础
plsql分为三个部分，声明部分，可执行部分，异常处理部分
Declare
声明部分
Begin
Plsql语句；
Exception handlers
End；
输出语句：dbms_output.put_line(内容)；
数值类型 number 只能存放整数
字符串类型 varchar2 char
日期类型 date
%type 类型 是取数据库表中某一个字段的类型，作为变量类型
写法：表名.列名%type;
Emp.ename%type 等同于 varchar2
Record 类型 存放一组值
例如 type emp_type is record(
Ename varchar2(10),
Job emp.job%type,
Sal emp.sal%type
);
%rowtype类型 是%type类型和record类型的结合
写法： 变量名 表名%type
变量和常量
变量的声明语法 ：变量名 数据类型[NOT NULL] [:=默认值];
:= 在plsql中是赋值符号
常量：一旦赋值他的值就不能改变
常量的声明语法：常量名 constant 数据类型 ：=默认值；
&：输入符号 从键盘输入
例如：从键盘输入一个数字，输出打印出来
Declare
v_m number（10）；
Begin
v_m:=&请输入一个数字
Dbms_output.put_line(‘v_m的值是：’||v_m);
End;
- 流程控制语句
分为：条件语句，循环语句，顺序语句
条件控制
If语句 三种形式：if then，if then else ，if then elsif
If 语句
语法 ：
If 条件表达式 then
Plsql语句；
End if
意思：如果条件表达式成立，那么执行then后边的plsql代码，如果不成立跳过if语句执行其他语句
If else 语句
语法：
If 条件表达式 then
Plsql 语句；
Else
Plsql 语句；
End if；
意思：如果if的条件表达式成立，执行then后边的代码，如果条件不成立执行else后面的代码；
Elsif 语句
If 条件表达式1 then
Plsql 语句1；
Elsif 条件表达式2 then
Plsql语句2；
....elsif 条件表达式n then
Plsql语句n；
Esle
Plsql语句；
End if；
意思：如果条件表达式1成立执行语句1，如果不成立判断第二个条件表达式，如果成立执行语句2，以此类推，如果都不成立执行else后边的语句
Case语句
语法一：
Case
When 条件表达式1 then
Plsql命令 值1；
When 条件表达式2 then
值2
...
Else
默认值;
End case;
语法二
case 条件表达式
When 值1 then
Plsql语句1；
When 值2 then
Plsql语句2；
...
When 值n then
Plsql语句n；
Else
Plsql 语句；
End case；
意思：如果条件表达式的值，等于某个when后面的值，那么就执行相应then后面的plsql语句，如果表达式的值没在when所列的值中，那么执行else后面的语句
循环控制
用于重复执行一系列语句，三种类型：--loop 无条件循环 --whlie 根据条件循环 --for 循环固定的次数
Loop 循环
语法：
Loop
Plsql 语句；
Exit when 退出循环条件；
循环控制语句；
End loop；
End；
exit when:退出循环关键字，如果条件成立立即退出循环
continue:退出本次循环，不会结束循环
return:退出程序，结束整个程序
Whlie 循环
语法：
while 循环条件 loop
循环体语句;
循环控制语句;
end loop;
FOR 循环
语法：
for 循环变量 in [reverse] 集合|查询语句|游标变量 loop
循环体语句;
end loop;
Select into 语句
语法：select 列名，列名,...into 变量1，变量2,...from 表名 where 条件；
注意：select into 语句查询出的结果只能是一条数据
顺序控制
Goto语句 无条件地转到标签指定的语句
Null语句 什么也不做的空语句，占位置
- 游标
游标逐行处理查询结果，以编程的方式访问数据
类型：隐式游标 显示游标 ref游标
游标的属性：%found 返回布尔类型的值，如果游标指向的数据不为返回ture，否则false
%notfound 和%found相反，指向的数据是空，返回ture
%rowcount 表示游标指向的缓冲区的数据条数
%isopen 返回布尔类型的值，判断当前游标是否打开如果打开返回ture 否则false
显示游标
声明：cursor 游标名 is select 语句；
打开游标 open 游标名
Fetch into 当前游标指向下一条数据并把数据保存在一个变量中
关闭游标 close 游标名称
For循环会自动打开游标，关闭游标，并且自动会让游标指向一条记录
带参数的显示游标
语法：cursor 游标名（参数名 数据类型，参数名 数据类型，...）is select 语句；
使用显示游标更新行
声明游标时必须使用select...for update 语句
隐式游标
在使用dml语句时自动创建隐式游标，自动声明打开和关闭，其名为sql
Ref游标
用于处理运行时动态执行的sql查询
语法：type 类型名称 is ref cursor；
声明ref游标类型的变量 变量名称 游标变量类型
集合
存放一组数据类型相同的数据，是相同类型元素的组合
分类：索引表 嵌套表 可变长度数组
索引表
使用整数或字符串作为下标，下标可以不连续，元素个数无限制
声明语法：
Type 类型名称 is table of 数据类型（是集合中值的数据类型） index by 下标的数据类型（varchar2，pls_integer,binary_integer;）
声明一个集合变量
变量名 类型名
集合中数据的存取
集合变量 下标
V（下标）：=值；
嵌套表 使用整数作为下标，下表是连续的，元素个数无限制
声明语法：type 类型名称 is table of 数据类型（存储的数据的数据类型）;
变量声明
变量名 类型名称
注意：嵌套表在赋值之前需要初始化
嵌套表的初始化：
变量名：=类型名（）；--初始化
变量名：=类型名（值，值，值）；
变长数组
使用整数（只能为正）作为下标，下标是连续的，元素个数有限制，可以用在plsql中，也可以存储在数据库中
语法；
Type 类型名称 is varray|varying array（长度）of 数据类型（保存的数据的数据类型）；
使用前也需要初始化
Bulk collect
可以把一组数据取出来存入一个集合类型变量中
Select ...into变量：只能查出一条数据保存在变量中
Select...bulk collect into 集合类型变量：可以查出多条数据存入变量中
动态SQL
动态sql是指在pl/sql程序执行时生成的sq语句
执行语法：
Execute immediate 字符串参数[into] 变量 using 参数
举例：
v_sql:='select * from emp where empno=:a and deptno=:b';
execute immediate v_sql into v_emp using 7369,20;
注意：按照顺序将两个数输入进去一一对应
异常
在运行时出现的错误叫做异常，发生异常后，语句将停止执行控制权转移到 PL/SQL 块的异常处理部分
declare
声明部分;
begin
plsql代码块;
exception
异常处理部分;
end;
ORACLE异常分为两种类型：系统异常、自定义异常。其中系统异常又分为：预定义异常和非预定义异常。
预定义异常常见如下：
INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发
NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发
NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发
非预定义异常
ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，通过伪过程PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。
如：
pragma exception_init(自定义的异常,编号);
insert into emp(empno,deptno) values(7369,10);
自定义异常
程序员从业务角度出发，制定的一些规则和限制。
异常名称 exception;
抛出异常：
raise 异常名称;
exception 捕获异常 处理异常
存储过程和函数
ORACLE 提供 可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。
创建存储过程语法
Create [or replace] procedure 存储过程名[(参数 in|out|in out参数数据类型...))]
Is|as
声明部分；
Begin
plsql代码块;
Exception
异常处理;
End;
存储过程的调用
- 在plsql块中调用
- Call命令调用
- Excute命令调用
Sqlplus :
set serverout on;
Begin
过程名();
end;
参数的三种模式
IN 用于接受调用程序的值。默认的参数模式
OUT 用于向调用程序返回值
IN OUT 用于接受调用程序的值，并向调用程序返回更新的值
函数
创建函数语法
Crate[or replace] function 函数名称[(形式参数 参数类型...)]
Return 返回值类型
Is
声明变量;
Begin
plsql代码块;
Return 返回值
End;
使用 SQL 语句
Select 函数名字() from dual;
使用 PL/SQL 块
declare
v varchar2(30);
begin
v:=f1;
dbms_output.put_line(v);
end;
触发器
触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用
类型：DML触发器（包含 表级触发器 ，行级触发器，instead of 触发器（替换触发器）） 模式（DDL）触发器 数据库技级触发器
创建触发器语法
Creat[or replace] trigger 触发器名字
Befor|after 触发器事件 on table_name
[foe each row]--行级触发器
[when trigger_condition]
trigger_body
语法解释：
trigger_name：触发器名称
before | after | instead of : 指定触发器是在触发事件发生之前触发还暗示发生之后触发。
trigger_event：触发事件，在DML触发器中主要为insert、update [OF column_list]、delete
table_name：表名，表示发生触发器作用的对象
for each row：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器
when trigger_condition：添加的触发条件
trigger_body：触发体，是标准的PL/SQL语句块
Declare begin end;
表级触发器
无论受影响的行数是多少，都只执行一次
没有for each row语句，在begin代码段中不可以使用:new和:old。
语法：
create or replace trigger 触发器名称
before|after
update or |delete or |insert on 表名
declare
声名变量;
begin
触发器代码;
end;
before:表示在sql语句执行前，执行触发器的代码
after:表示在sql语句执行之后，执行触发器的代码
以下三个都是boolean类型的
updating:如果触发这个触发器的是一条update语句，它值就是true
deleting:如果触发它是一条delete语句，它值就是true
inserting:如果触发它是一条insert语句，它值就是true
行级触发器
对DML语句修改的每个行执行一次,有for each row语句，在begin代码段中可以使用:new和:old。
:new --是 一个引用最新的列值
:old --是 一个引用以前的列值
语法：
语法：
create or replace trigger 触发器名称
before|after update or delete or insert on 表名 for each row
begin
触发器代码;
end;
替换触发器
替换触发器使用在使用在视图上，而且是行级的触发器
语法：
create or replace trigger 触发器名
instead of insert or update or delete on 视图名 for each row
begin
end;
7.模式触发器
在模式中执行 DDL 语句时执行
CREATE TABLE dropped_obj (
obj_name VARCHAR2(30),
obj_type VARCHAR2(20),
drop_date DATE);
语法
CREATE OR REPLACE TRIGGER 触发器名字
before ALTER or DROP or CREATE ON SCHEMA
BEGIN
INSERT INTO dropped_obj VALUES(ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_TYPE, SYSDATE);
END;
常用系统变量：
Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_login_user 返回登录用户名
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
数据库级触发器
在发生打开、关闭、登录和退出数据库等系统事件时执行
startup打开 关闭shutdown 登录logon 退出logoff
注：启用、禁用和删除触发器
启用和禁用触发器
ALTER TRIGGER 触发器名字 DISABLE;
ALTER TRIGGER 触发器名字 ENABLE;
删除触发器
DROP TRIGGER aiu_itemfile;
程序包
程序包是对相关过程、函数、变量、游标和异常等对象的封装
创建程序包的语法
包头和包体名字一致
程序包规范语法
CREATE [OR REPLACE] PACKAGE 名字 IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
package_name:包的名称
Public item declarations：公共声明部分
Subprogram specification：声明PL/SQL子程序
程序包主体
CREATE [OR REPLACE] PACKAGE BODY 名字 IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
package_name是包的名称
public type and item declarations::私有声明部分
subprogram specificatons:子程序体
调用:包名.元素名;
2、本资源部分来源其他付费资源平台或互联网收集，如有侵权请联系及时处理。
SEA模板网 » Oracle细节，plsql语法大全
发表评论