- 博客(15)
- 资源 (1)
- 收藏
- 关注
RSS订阅原创 MYSQL序号
直接执行以下查询语句即可，第一列就是序号。SELECT @R:=@R+1 R,T.* FROM INFORMATION_SCHEMA.COLUMNS T,(SELECT @R:=0) R ;
2021-04-07 09:22:17
669
原创 ORACLE读取XML
第一个例子：SELECT * FROM XMLTABLE('$T/DEAL_BASIC/USER_DEAL_INFO' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?> <DEAL_BASIC> <USER_DEAL_INFO&g...
2020-02-21 15:54:09
1474
原创 ORACLE正在执行的SQL
select SQL_TEXT, -- SQL文本的前1000个字符SHARABLE_MEM/1024, -- 占用的共享内存大小(单位, -- byte)PERSISTENT_MEM/1024, -- 生命期内的固定内存大小(单位, -- byte)RUNTIME_MEM/1024, -- 执行期内的固定内存大小SORTS, -- 完成的排序数LOADED_VERSIONS, -- 显...
2020-02-21 15:02:33
252
原创 ORACLE系统输出所有视图的COUNT时间
DECLARE str VARCHAR2(10000); va VARCHAR2(10000); d1 DATE; d2 DATE; i NUMBER; userrow user_views%rowtype; CURSOR userrows IS SEL...
2019-11-18 17:47:40
273
原创 ORACLE判断字符串是否为数字
WITHT AS (SELECT '0123.01' S FROM DUAL UNION ALLSELECT '-0123' S FROM DUAL UNION ALLSELECT '-0123.01' S FROM DUAL UNION ALLSELECT '012345' S FROM DUAL UNION ALLSELECT '01234567 A' S FROM DUAL UN...
2019-11-18 17:46:42
412
原创 ORACLE百分百
WITH T AS (SELECT 'V' STATUS,41 SHULIANG FROM DUAL UNION ALLSELECT 'C' STATUS,699 SHULIANG FROM DUAL UNION ALLSELECT 'D' STATUS,19 SHULIANG FROM DUAL UNION ALLSELECT 'J' STATUS,172 SHULIANG FROM D...
2019-11-18 17:45:43
604
原创 ORACLE获取数据库对象生成脚本
获取数据库对象生成脚本期中OBJECT_TYPE是对象类型，OBJECT_NAME是对象名称。SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME) FROM DUAL;获取索引创建脚本：SELECT DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME') FROM DUAL;获取表创...
2019-11-18 17:44:34
420
原创 ORACLE管道函数
DROP TYPE ty_str_split ;DROP FUNCTION fn_split ;CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)RETU...
2019-11-18 17:40:59
246
原创 ORACLE按照汉字拼音、笔划、部首排序。
WITHT AS (SELECT '一' S FROM DUAL UNION ALLSELECT '二' S FROM DUAL UNION ALLSELECT '三' S FROM DUAL UNION ALLSELECT '四' S FROM DUAL UNION ALLSELECT '五' S FROM DUAL UNION ALLSELECT '六' S FROM DUAL ...
2019-11-18 17:39:57
653
原创 ORACLE单表随机获取N条记录
drop table t ;create table T AS SELECT LEVEL L,SYS_GUID() S FROM DUAL CONNECT BY LEVEL <= 100 ORDER BY 2;select * from (select * from t sample(50) order by trunc(dbms_random.value(0, 1000))) ...
2019-11-18 17:38:11
278
原创 ORACLE数学统计函数
WITHT1 AS (SELECT 1 A FROM DUAL UNION ALLSELECT 2 A FROM DUAL UNION ALLSELECT 3 A FROM DUAL UNION ALLSELECT 4 A FROM DUAL UNION ALLSELECT 5 A FROM DUAL )SELECTSTDDEV(A) 标准偏离,STDDEV_POP(A) 总体标...
2019-11-18 17:37:14
254
原创 ORACLE动态语句
declare str varchar2(500); c_1 varchar2(100); r_1 varchar2(100);begin c_1:='测试人员'; str:='select :c from dual'; execute immediate str into r_1 using c_1; DBMS...
2019-11-18 17:36:18
168
原创 ORACLE阶层
方法1：DECLARE o NUMBER; str VARCHAR(1000);BEGIN str := 'WITHNT AS (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 10)SELECT ' || chr(39) || 'SELECT ' || chr(39)...
2019-11-18 17:34:53
257
原创 ORACLE数字类型和科学记数法互转
SELECTTO_CHAR(0.000000000000097, '9.9EEEE') 数字转科学记数法,TRUNC(9.7E+14,128) 科学记数法转数字FROM DUAL;
2019-07-10 10:23:38
3253
原创 ORACLE代码块、常见循环、系统输出实例，任意库直接执行即可。
DECLARE a VARCHAR2(64) := 'Aa'; b VARCHAR2(64) := 'Bb'; c VARCHAR2(64) := 'Cc'; i NUMBER; userrow dual%rowtype; CURSOR userrows IS SEL...
2019-07-01 15:12:57
1133
1
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人