关于PL/SQL操作Oracle的入门知识

关于PL/SQL操作Oracle时的一些知识点:

小技巧:

trunc(sysdate) 可以直接表示今天开始的时间

函数:
数据库函数必须要有返回值
存储过程/表/视图等修改:
需要对失效对象进行编译

数据库里自带的属性和一些特殊类型的定义

①rownum 它常用于当做条件来控制查询的结果集 例:rownum<10,取前九行数据
②having 它用于分组查询时做条件判断,需要在group by后接着使用,例:select id from info group by id having count(id)>1;查询info表id分组后重复id值大于1的id
③between 它用于查询时控制查询范围,后面接两个值用and连接表示范围,例:between 1 and 20,取前20行数据
④raise 它用于在存储过程中抛出异常,先通过E_SAME_TT_NUMBER EXCEPTION;声明一个名叫E_SAME_TT_NUMBER的异常,然后需要进行触发时,使用RAISE E_SAME_TT_NUMBER;抛出异常,捕获时在end前面简建立EXCEPTION然后在内部使用WHEN 异常名 THEN来捕获 例

create or replace procedure test(id in varcher,count out varcher) as
	--声明变量(变量名 变量类型)
	E_SAME_TT_NUMBER	EXCEPTION;--声明一个名叫E_SAME_TT_NUMBER的异常变量
begin
	--存储过程的执行体
	RAISE E_SAME_TT_NUMBER;--触发E_SAME_TT_NUMBER异常
EXCEPTION--异常出现后进入该代码块
	WHEN E_SAME_TT_NUMBER THEN--捕获异常
	--执行的业务...
end test;

⑤sysdate 使用表示当前时间,对于它的运算则是以天为单位来计算,1等于一天
⑥chr(10) 表示空格,记录在ASCII表中
⑦varray(10) 定义数组,并声明大小 例type 字段名 is varray(数组长度) of 类型
⑧存储过程中自定义类型(对象)

type 定义的类型名A is record(
	字段名  类型(大小),
	...
	字段名  类型(大小)
);

⑨定义多行类型 type 定义的类型名B is table of 任意数据类型A;
注:此处的"任意数据类型A"可以是上面"定义的类型名A"
⑩集合类型使用方式
任意数据类型B 新定义的变量名 := 新定义的变量名();
注:此处的任意数据类型B可以是上面的"定义的类型名B",该类型生成的对象,每次存储后,换到下一行需要使用 新定义的变量名.extend() 方法,内部数据值操作可以通过 新定义的变量名.字段 调用

数据库里自带函数,以下所有的值/原值(只要是进行函数处理的对象都可以为SQL的查询表达式语句)

1.add_months(时间值,计算值) 对时间值以月为单位进行计算值运算
2.mod(值1,值2) 求值1对值2进行取余
3.upper(值) 对于值进行大写转换
4.instr(查找字符串,值) 显示从查找字符串开始到值出现的位数,类似于java中indexOf()方法,区别为值不是下标而是查找的目标对象,返回结果为查找字符串的下标
4.instr(查找字符串,值,数值分正左负右开始查找,查找第几个) 同上,增加了左右查找和查找的排名条件
5.substr(截取文本对象, 文本下标, 从下标开始截取的字符长度) 字符截取,如果只给两个入参,那么文本下标默认为0
6.rtrim(处理文本对象,处理值默认为空格) 从处理文本对象的右边开始,匹配处理值中的每个字符,当遇到匹配到的时候删除文本对象里的值 例rtrim(‘askjnkcj’,‘kj’),返回结果:asnkcj
7.ltrim(处理文本对象,处理值默认为空格) 功能与rtrim一样,不同点是从左边开始
8.trunc(数值,截取值) 处理数值函数,根据截取值以小数点为中心,正保留小数位x,负则无小数整数置零x位,无则默认无小数,时间类型可以根据截取值定义的yyyy-mm-dd HH24:MI:SS来截取文本
9.round(数值,截取值) 处理数值函数,根据截取值以小数点为中心,正保留小数位x,负则无小数整数置零x位,无则默认无小数,与trunc区别是此函数做四舍五入
10.floor(数值) 数值舍去小数
11.decode(原值,比较值,显示值,比较值,显示值,…default值) 类似Java的switch语句,和数据库里的case when语句也很相似,原值和后面的比较值比较,匹配到了就显示比较值后面挨着的显示值,比较值必须和显示值成对出现,没有成对出现那么最后的值就是default值,也就是没有匹配时的显示值
12.to_date(文本值,‘时间类型模板’) 文本转换时间类型 例to_date(‘2018-04-09’,‘yyyy-mm-dd hh24:mi:ss’)
13.sign(数值) 根据数值正负零返回1,-1,0三个值,说实话没什么用
14.row_number() over(partition by 分组字段 order by 排序字段) 分组排序,产生新的一个列,对分组字段进行分组,再在分组后的顺序上对排序字段排序,展示的是分组后排完序的值,该值为一列,它不像group by进行了聚合,它主要是使用在select与from之间产生新的列
15.count(*) over(partition by 分组字段) 分组求和,产生新的一个列,对分组字段分组,以新的列存储该列所在组的总和,同上,它主要是使用在select与from之间产生新的列
16.cast(数值 as 类型) 将数值转换成相应的类型
17.start with 字段名 = 值 connect by prior 字段名 = 字段名 层次查询条件,其中查询后展示的level作为内部字段可使用,表示层次深度,一般这种查询都是作为内查询,然后在外层再约束,使用方式:以单表里面的两个字段里的数据值相同为条件进行纵列不关联查询,start with 后的字段名 = 值为层级起点条件,然后以前条件为基础,查询connect by prior后面接的字段 = 字段的层次条件,满足的数据显示
18.nvl(字段,值) 空值判断,当字段值为空时,返回值,不为空时返回字段原值,如果函数计算时都为空,就返回NULL
19.order by 排序语法:
(1) nlssort(字段,‘NLS_SORT=SCHINESE_PINYIN_M’) 中文首字母排序
(2) nlssort(字段,‘NLS_SORT=SCHINESE_STROKE_M’) 中文笔画排序
(3) nlssort(字段,‘NLS_SORT=SCHINESE_RADICAL_M’) 中文部首排序 例order by nlssort(字段,‘NLS_SORT=SCHINESE_RADICAL_M’)
20.sample(值) 百分比数据,在查询时接在实表的表名后面可以将原有的表数据转换成百分比值的数据,取出的数据在所有数据里面是平均分散的
21.regexp_substr(值,参数1,参数2,参数3,参数4) 正则分割字符串函数,分割后的数据会以行显示
值 为需要进行处理的字符串
参数1 是进行匹配分割的正则表达式
参数2 是起始位置,从第几个字符开始,默认为1
参数3 表示第几个匹配组,默认为0,表示全部
参数4 表示其它功能项,值(i:不区分大小写,c:区分大小写,x:过滤参数1的空白字符),默认为区分大小写,不过滤空白字符
22.replace(值,参数1,参数2) 替换函数
值 为需要进行处理的字符串
参数1 是进行匹配替换的字符串
参数2 是进行替换的字符串,默认为空格
23.regexp_replace(值,参数1,参数2,参数3,参数4,参数5) 正则替换字符串函数
值 为需要进行处理的字符串
参数1 是进行匹配替换的正则表达式
参数2 是进行替换的字符串,默认为空格
参数3 是起始位置,从第几个字符开始,默认为1
参数4 表示第几个匹配组,默认为0,表示全部
参数5 表示其它功能项,值(i:不区分大小写,c:区分大小写,x:过滤参数1的空白字符),默认为区分大小写,不过滤空白字符
样例

select value from (
	select regexp_substr(
	'102649222;;;F11945-00;;;空港物流园01#顺驰路光交箱-空港物流园02#交接箱南法信城铁站;;;70950038;;;F5143-00;;;德梦澜实业发展公司-顺于路23#;;;87783955;;;F5143-01;;;空港物流园01#顺驰路光交箱-顺驰路#1-光缆光缆疑似中断'
	,'[^;;;]+', 1, level, 'i') value,rownum name from dual connect by level <= 
	(length(
	'102649222;;;F11945-00;;;空港物流园01#顺驰路光交箱-空港物流园02#交接箱南法信城铁站;;;70950038;;;F5143-00;;;德梦澜实业发展公司-顺于路23#;;;87783955;;;F5143-01;;;空港物流园01#顺驰路光交箱-顺驰路#1-光缆光缆疑似中断'
	)-length(regexp_replace(
	'102649222;;;F11945-00;;;空港物流园01#顺驰路光交箱-空港物流园02#交接箱南法信城铁站;;;70950038;;;F5143-00;;;德梦澜实业发展公司-顺于路23#;;;87783955;;;F5143-01;;;空港物流园01#顺驰路光交箱-顺驰路#1-光缆光缆疑似中断'
	, ';;;')))/length(';;;')+1
) where mod(name-1,3) = 0

数据库里运算符

|| 字符串连接符,连接前后字符串
<> 不等于
is null 判断空
not is null 判断非空
in 判断是否在的意思

语法(使用在存储过程里一些知识)

case选择分支语句 例:case when 条件 then 值 else 值 end
例

select
	case when 
		to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') >= to_char(sysdate, 'yyyy-mm-dd') || ' 08:30:00' and to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') <= to_char(sysdate, 'yyyy-mm-dd') || ' 21:30:00' 
	then
		'1'
	else
		'0'
	end
	as istrue
from dual

for (集合对象,不需要声明) in (结果集) loop 循环体内容… end loop; 用在存储过程里面,对查询的结果集做遍历处理,每次取出一行结果集
execute immediate 文本值 into 变量值; 将文本值作文可执行的sql执行后赋值给变量值
select * from table(列表); 嵌套查询出的结果集或者是视图,可以用table()函数包裹后当做表使用

表

dual 它是oracle中内部的虚拟表,当不想查询已有表又不想新建表时可以使用dual来完成一些函数的使用

权限

grant 权限类型 on 对象 to 用户名 将存储过程或者是函数的权限赋给用户(请记住,以整体为单位赋权,比如包里面的一个存储过程是不可行的,只能赋整个包的权限)
权限类型可为execute/执行,select/查询,insert/插入,查询针对序列权限,插入针对表
对象可以是表/存储过程/函数/视图等任意对象

集合运算

minus 对两个相同列的数据进行减集运算,左边的减右边相同的数据,右边不重复数据舍弃
union 对两个相同列的数据进行并集运算,合并所有数据,去重
union all 对两个相同列的数据进行并集运算,合并所有数据,不去重
intersect 对两个相同列的数据进行交集运算,只展示最终都拥有的数据行

数据库常用脚本(操作后请编译失效对象)

–添加表中字段

alter TABLE 表名 ADD product_type varchar(4);

–添加表中字段的备注

comment on column 表名.product_type
  is '(产品类型) 值: FTTH政企标准宽带\商务专线(直接传类型值,不做转换)';

–删除表中字段

ALTER TABLE 表名 DROP COLUMN Is_Reply_Received

–修改表中字段长度

alter TABLE 表名 modify(Is_Reply_Received varchar(255));

–查看数据库里面的用户锁

SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
　　l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
　　FROM v$sqlarea a,v$session s, v$locked_object l
　　WHERE l.session_id = s.sid
　　AND s.prev_sql_addr = a.address
　　ORDER BY sid, s.serial#;

–删除锁(732是sid,24131是serial#)

alter system kill session '732,24131' ;

–查看数据库里面的死锁

SELECT a.sid,d.spid,b.status,e.event,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid 
AND B.PADDR=D.ADDR and A.sid in(select sid from v$Lock where block=1);

–查询跨库的DB_link

select * from dba_db_links

–DB_link的字段意义如下

OWNER –表示权限范围
DB_LINK –表示BDLINK名称
USERNAME –表示连接数据库对象的用户名
HOST –表示连接的数据库对象,可以是在oracle里面声明好了的数据源名称,也可以就是数据源本身定义的名称,还可以是简写方式"地址/数据库名(数据库名不是数据源名,定义数据源时用到的指向名称)"
CREATED –表示创建时间

–使用PL/SQL创建DBlinks注意
鉴定者不需要填写

–查询数据库里面的对象(因为oracle里面的所有对象名都是大写的,所以需要使用upper进行大写转换)

select * from dba_objects t where t.OBJECT_NAME = upper('fyg_product_req_mes_to_erp_prg');

–查询数据库里面的同义词对象

select * from dba_synonyms t where t.synonym_name = upper('fyg_product_req_mes_to_erp_prg');

–创建索引的一般形式PL/SQL方式
从左到右
所有者 这个表能使用该索引的用户
名称 不能重复的命名,后期可以进行强制索引操作
类型 过滤的类型,默认即可
列 对那个字段做索引,增加过滤性
压缩 空即可
前缀长度 空即可
反转 空即可
local 空即可
存储{
从左到右,从上到下
表空间 选择users
初始大小 默认64KB
%空间 10
下一个大小 1MB(越大过滤性越强)
%已用 默认空
%增加 默认空
初始事务数 2
最小数量 1
最大事务 255
最大数量 默认空
不限制 默认勾上
}

–复制跨库数据

insert into tsm_entity_config (select * from tsm_entity_config@ossdb2)--通过DBlink来跨库连接数据
insert into tsm_entity_field_config (select * from tsm_entity_field_config@ossdb2)

–查询跨库数据

select * from tsm_entity_config@ossdb2--通过DBlink来跨库连接数据
select * from tsm_entity_field_config@ossdb2

–删除数据

delete from tsm_entity_config
delete from tsm_entity_field_config

–快速备份表数据

create table 新表名 as(select * from 原表名 where 条件)

–创建备份,和快速备份表数据是一样的,就当做样例吧

create table tsm_entity_config20180623 as(select * from tsm_entity_config)
create table tsm_entity_field_config_180623 as(select * from tsm_entity_config)

–根据类型和用户查找最近创建的表

select * from dba_objects where object_type='TABLE' and owner='FWBZ' order by created desc;--owner是用户,值必须是大写才能查找,object_type是对象类型,TABLE表示表对象