随笔分类 -  Oracle

• oracle case when
  摘要：参考 https://blog.csdn.net/yangzjchn/article/details/81019449 select case when t.content = '1' then '满意' when t.content = '2' then '一般' else '不满意' end s 阅读全文
  posted @ 2019-03-12 14:52 white knight 阅读(85) 评论(0) 推荐(0)
• oracle 分页
  摘要：select a1.* (select a.*, rownum rn from (select * from table1 where field1 = ? order by field2) a where rownum > ???) a1 where a1.rn <= ??? 阅读全文
  posted @ 2018-08-07 17:21 white knight 阅读(68) 评论(0) 推荐(0)
• oracle 存储过程模板
  摘要：CREATE OR REPLACEPROCEDURE PROCE_NAME(V_IN varchar2, V_OUT out varchar2) ASBEGIN --... commit; V_OUT := 'aaa';exception when others then V_OUT := '999 阅读全文
  posted @ 2018-08-03 16:29 white knight 阅读(132) 评论(0) 推荐(0)
• oracle wm_concat() 返回空
  摘要：参考 https://www.cnblogs.com/zengweiming/archive/2013/11/20/3433642.html select wm_concat(to_char(str)) from xxx 阅读全文
  posted @ 2018-06-23 13:50 white knight 阅读(519) 评论(0) 推荐(0)
• oracle取某字符串字段的后4位
  摘要：参考 https://zhidao.baidu.com/question/2142799026528780468.html select substr('str1234', -4) from dual 阅读全文
  posted @ 2018-06-22 15:20 white knight 阅读(2248) 评论(0) 推荐(0)
• oracle授予调用存储过程权限
  摘要：参考 https://blog.csdn.net/h254532693/article/details/45364317 grant execute on PROCEDURENAME to USERNAME; 阅读全文
  posted @ 2018-04-21 13:09 white knight 阅读(945) 评论(0) 推荐(0)
• oracle改变表中列的编码
  摘要：ALTER TABLE table_name CHANGE `name` `name` VARCHAR(255) CHARACTER SET utf8; 阅读全文
  posted @ 2018-03-26 11:29 white knight 阅读(356) 评论(0) 推荐(0)
• oracle创建存储过程
  摘要：CREATE OR REPLACE PROCEDURE "PROCUDURE_NAME" (task_id NUMBER, deal_result out NUMBER, result_info out VARCHAR) AS BEGIN update TABLE_NAME set zzz ='z1' where ID = task_id; deal_result := 0; res... 阅读全文
  posted @ 2018-03-26 11:11 white knight 阅读(141) 评论(0) 推荐(0)
• ORACLE用户解锁
  摘要：alter user wms_user ACCOUNT UNLOCK 阅读全文
  posted @ 2018-03-26 11:08 white knight 阅读(91) 评论(0) 推荐(0)
• oracle查询锁表
  摘要：select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time; alter system kill se 阅读全文
  posted @ 2018-03-26 11:07 white knight 阅读(148) 评论(0) 推荐(0)
• oracle杀掉执行的死循环存储过程
  摘要：select * from v$db_object_cache where locks > 0 and pins > 0 and type='PROCEDURE'; select b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session ' || '' 阅读全文
  posted @ 2018-03-26 11:06 white knight 阅读(3038) 评论(0) 推荐(0)
• 查询oracle的session数
  摘要：select * from v$resource_limit; 阅读全文
  posted @ 2018-03-26 11:05 white knight 阅读(259) 评论(0) 推荐(0)
• oracle以逗号分隔查询结果列表
  摘要：select wmsys.wm_concat(id) from table_name where id >= 5000 and id < 6000 阅读全文
  posted @ 2018-03-26 11:05 white knight 阅读(518) 评论(0) 推荐(0)
• oracle存储过程-获取错误信息
  摘要：dbms_output.put_line('code:' || sqlcode); dbms_output.put_line('errm:' || sqlerrm); dbms_output.put_line('lineno:' || dbms_utility.format_error_backtr 阅读全文
  posted @ 2018-03-26 11:04 white knight 阅读(377) 评论(0) 推荐(0)