oracle数据库知识点总结

1.数据库脚本：
在SQLPlus中默认的"&"表示替代变量，也就是说，只要在命令中出现该符号，SQLPlus就会要你输入替代值。
–set define off关闭替代变量功能
默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功，命令行会返回类似：Table
–当载脚本中运行很多语句的时候，一般设为set feedback off
–set echo off;关闭显示当前正在执行的语句
在sqlplus中运行的脚本，如包含空行，sqlplus会不认的。
–set sqlblanklines on，sqlplus就会正确处理空行

2.存储过程：
–存储过程中 '<>‘表示不等于，’=‘表示等于，’:='表示赋值.
–学会使用replace函数替换，replace(原字段,“被替换内容”,“新内容”)
–chr(39)替换 ',chr(38)替换 " ，sql语句使用 || 连接。
–trunc函数：对 时间类型或者数字进行截取。trunc(sysdate,‘yyyy’);//返回当年第一天、trunc(sysdate,‘mm’);//返回当月第一天、trunc(sysdate,‘dd’);//返回当前时间年月日、
trunc(sysdate,‘d’);//返回当前星期第一天、trunc(sysdate,‘hh’);//返回当前小时、trunc(55.5,-1);值为50，从小数点左边第一位截取后面全置为0、trunc(55.55,1);//值为55.5，小数点后面保留一位
trunc(55.55);//值为55，截取整数部分。
–学会使用dual数据表测试语句是否正确。
–存储过程中加入循环限制提交次数，一般设置为200此以上提交。存储过程中添加异常处理包括空值异常、其他异常。
–regexp_substr函数：使用正则表达式从字符串抽取字符串。
语法：regexp_substr(expression,regular-expression,startoff,endoff)；expression：被分割的字符串、regular-expression：截取字符、startoff：起始值，相当于expression偏移、endoff:获取第几个指定字符截取后的值。

3.配置本地操作数据库
–配置loc_common_sqltxt_def、loc_commonsql_variable两表时确认在哪个数据库，查营业库时在营业库配置，查订单库时在订单库配置。

4.tx_int_dispatcher配置参考。
select * from tx_int_dispatcher t where t.opcode = ‘ChangeOrProd’;

5.count(1)和count()比较：
1、如果你的数据表没有主键，那么count(1)比count()快
2、如果有主键的话，那主键（联合主键）作为count的条件也比count()要快
3、如果你的表只有一个字段的话那count()就是最快的啦
4、count() count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
5、如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 因为count(),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

6.内连接inner join、左连接left join、右连接right join
–内连接 把匹配的信息全部查出来
–左连接 包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐
–右连接 包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐

7./+use_hash(表1 表2)/可以加强表之间的关联，在查询是增加查询效率。

NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

9.查询已创建序列：select * from user_sequences;

10.oracle中的日期格式为:yyyy-MM-dd HH24:mi:ss和 yyyy-MM-dd HH:mi:ss,分别代表oracle中的24小时制和12小时制
java中的的日期格式为:
yyyy-MM-dd HH:mm:ss:代表将时间转换为24小时制,例: 2018-06-27 15:24:21
yyyy-MM-dd hh:mm:ss:代表将时间转换为12小时制,例: 2018-06-27 03:24:21
ps：oracle是不区分大小写的,所以java中根据大小写来代表24小时和12小时的表达式在oracle中就会出问题,oracle中将24小时的小时和分钟做了特殊处理.如上所示,在hh后面加上了24,将mm改为了mi

11.查看是否有同义词：select * from dba_synonyms t where t.SYNONYM_NAME = ‘PC_JT_UNICODE_PRODSTATUSCHGHIS’;
12.删除同义词，例如：drop synonym COMMON.PC_JT_UNICODE_PRODSTATUSCH

13.修改表字段：alter table tbcs.LOC_RECOMMENDBUSI_REC modify(busidesc VARCHAR2(256));

14.新增表字段：alter table tbcs.LOC_RECOMMENDBUSI_REC_HIS add(busidesc VARCHAR2(256));
新增表字段不能为空（表中有数据）：alter table tbcs.LOC_RECOMMENDBUSI_REC add(busidesc VARCHAR2(256) default 1 not null);

15.删除表字段：alter table tbcs.LOC_RECOMMENDBUSI_REC drop column 表列字段;

-----------------------------触发器详解--------------------------------------------------------------
1.触发器在数据库中以独立的对象存储，与存储过程和函数不同的是存储过程和函数需要用户显示调用才能执行，触发器由一个事件来启动运行，即触发器由某个事件发生时自动隐式运行。
2.触发器不能接收参数，所以运行触发器又叫触发或者点火。
3.oracle事件是指对数据库表进行insert、update、delete操作或视图进行类似操作
4.oracle将触发器扩展到触发oracle如数据库启动或者关闭，所以触发器常用于完成由数据库完整性约束难以完成的复杂业务规则约束，或监视数据库的各种操作，实现审计功能。
5.oracle可以在dml语句进行触发，也可以在dml操作前或操作后进行触发，并且对每行或语句操作进行触发。
6.替代触发器是oracle8针对两个以上表建立的视图进行操作
7.oracle8i 提供了第三种类型触发器叫系统触发器，可以在oracle数据库系统事件中进行触发，如oracle系统的启动和关闭等。
8.触发器组成：触发事件（引起触发器被触发的事件）、触发时间（该trigger在触发事件之前before还是之后after触发）、触发操作（触发器触发后的目的）、触发对象（包括表、视图、模式、数据库）、触发条件（由when指定一个逻辑表达式）、触发频率（触发器内定义的动作被执行的次数）
9.触发器注意点：触发器不接受参数、一个表最多12个触发器，但同一时间、事件、类型的触发器只能一个，各个触发器间不能有矛盾、一个表触发器越多，对该表的dml操作性能影响越大、触发器最大为32kb、触发器执行部分只能dml语句（select、delete、update、insert），不能使用ddl、触发器不能包含事务控制语句、触发器主体中不能声明任何long和blob变量
10.创建触发器一般格式：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}…]
ON [schema.]table_name | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

-------------------------------------常用数据表---------------------------------------------------------------------
1.配置短信指令表
select * from sm_code_extend t where t.nodeid = ‘88312443721947’;
select * from sm_code_define t where t.nodeid = ‘88312443721947’;
–从sm_code_define找到businessid
select * from int_business_define t where t.businessid = ‘SMYDLDProdOrder’;
–从int_flow_define找到flowid
select t.* from int_flow_define t where t.flowid = ‘SMYDLDProdOrder’;

2.短信模板
select t.* from tbcs.sm_retcode_templateno t where t.templateno = ‘SMYDLDProd_100’; --模板声明
select t.* from tbcs.sm_template_define t where t.template_no = ‘SMYDLDProd_100’; --短信模板定义
select t.*,rowid from smtemplate@lnk_sms_a1.hebei.mobile.com t where t.template_no = ‘LocRecommendSms’; --测试环境短信模板配置 （上线的时候单独提一个短信库脚本，tbcs用户内容和营业库一样）

3.command接口配置
select t.,rowid from common.int_command_define t where t.commandid = ‘LocQryRecommendRecMsg’;
select t.,rowid from common.int_command_param t where t.commandid = ‘LocQryRecommendRecMsg’;

4.本地操作数据库sql语句配置
select t.,rowid from tbcs.loc_common_sqltxt_def t where t.opersqlid = ‘loc_RadarSendSMS’;
select t. from tbcs.loc_commonsql_variable t where t.opersqlid = ‘LocQryRecommendRecMsg’;

5.查看短信发送结果
select * from smnotify@srp1.hebei.mobile.com t where t.telnum = ‘13903166688’ order by intime desc;
select * from smnotify_para@srp1.hebei.mobile.com t where t.formnum = ‘10000002719338’;
select * from smnotify_history@srp1.hebei.mobile.com t where t.telnum = ‘13582667709’;

6.模拟给10086发送短信
insert into sm_job (area,TELNUM, PORT, MOMSG, MSGID, INTIME, STATUSTIME, STATUS)
values (‘314’,‘15075492749’, ‘10658478’, ‘TJYW#ldtx#13903140908’, SEQ_SMS_OID.Nextval, sysdate, sysdate, 0);
commit;
–有一个进程会不断扫描sm_job这个表，最终结果会进入历史表
select * from sm_job_history;

7.配置opcode
select * from tp_opcode_dict t where t.opcode = ‘LocActiveSubsChk’;

select * from tx_int_dispatcher t where t.opcode = ‘LocActiveSubsChk’;