分别是应用,而是允许用户成立自个儿的用户定义函数

SQL SEHavalVECR-V中是允许有自定义函数,Microsoft SQL
Server并不将用户限制在概念为
Transact-SQL语言一部分的停放函数上,而是允许用户创立本人的用户定义函数。

0 . 查看系统表空间

select * from dba_tablespaces

函数是由3个或八个Transact-SQL语句组成的子程序,可用于封装代码以便重新行使。固然自定义函数在数据量庞大的时候,功用上有欠缺,可是在中型小型型数据库中,依旧得以应用,现将本身数据库中有所的自定义函数与我们大饱眼福,并采访了CSDN帖子中冒出的局地自定义函数实行整理测试,例如fredrickhu(小F)
dawugui(爱新觉罗.毓华)
happyflystone(无枪狙击掌)、dobear_0922(do熊)
htl258(Tony)
Haiwer(海阔天空)
libin_ftsafe(子陌红尘[I’m
潇湘]) 、cson_cson等等C友们享受或是原创的部分函数。

1 . 查看表结构

desc table_name

01、删去字符串中的html标记及标记中的内容

2 . 查询数据库SID

select name from v$database;

02、除去字符串中老是的分割符

3 . 用 B 表的多少更新 A 表数据

UPDATE A SET name=(SELECT name FROM B WHEREA.id=B.id) WHERE B.id IS NOT NULL ;

03、求第2个字符串中第四个串的个数

4 . 随机数函数 DBMS_RANDOM.RANDOM

SQL> select dbms_random.random from dual

04、综合模糊查询

5 . 怎样在字符串里加回车?

SQL> select 'line1'||chr||'line2' from dual;

05、将十进制转成十六进制

6 . 怎样使 select 语句使查询结果自动生成序号 ?

SQL> select rownum, fieldname from table;

06、求四个字符串中千篇一律的汉字及字母的个数

7 . 怎么能够飞快做二个和原表一样的备份表 ?

create table new_table as (select * from old_table) ;

07、转移n位随机字符串

8 . 怎样独立备份三个或四个表?

exp 用户 / 密码 tables=( 表 1 , … ,表 2)

08、取出字符串中的汉字、字母或是数字

9. 哪些独立备份叁个或八个用户?

exp system/manager owner=( 用户 1 ,用户 2 , … ,用户 n) file= 导出文件

09、依据字符分割字符串的三种写法

10 . 怎样进行脚本 SQL 文件 ?

SQL>@$PATH/filename.sql ;

10、将数字转换千分位分隔情势

11. 怎么着火速清空1个大表 ?

SQL>truncate table table_name ;

11、取汉字首字母的四个函数

12 . 字符串的连年

select concat(col1 , col2) from table ;select col1||col2 from table ;

12、传说身份证得到生日函数

13 . 怎么把 select 出来的结果导到一个文书文件中?

SQL> spool c:\test.txt;SQL> select * from emp;SQL> spool off ;

13、依照身份证总括性别函数

14. 如何测试 SQL 语句执行所用的小时 ?

SQL>set timing on ;SQL>select * from tablename ;

14、将身份证的15个人号码升级为贰10个人

15 . 改变字段大小 ?

-- 改大行,改小不行SQL>alter table table_name modify (field_name varchar2 ;

15、经过身份证得到户口

16 . 如何查询某天的数据 ?

-- datefield为要查询的日期字段SQL>select * from table_name where trunc( datefield ) = to_date('2003-05-02' ,'yyyy-mm-dd') ;

16、几个数据项的字符串取钦命地方字符

17. 怎么着修改表名 ?

SQL> alter table old_table_name rename to new_table_name;

17、中缀算术转后缀算术表明式并盘算的函数

18 . 如何寻找出前 N 条记录?

SQL> SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;-- 搜索一个范围的数据 SQL> SELECT * FROM (SELECT ROWNUM RN,EMPNO,ENAME FROM EMP) WHERE RN > 5 AND RN < 10 ORDER BY empno;

18、人民币小写金额转大写

19 . 如何用 Sql 语句完结查找一列中第 N 大值?

SQL> select * from (select t.* , dense_rank() over (order by sal) rank from employee) where rank = N ;

19、向左填充钦赐字符串

20 . 怎样在给现有的日期加上 2 年?

SQL> select add_months(sysdate , 24) from dual ;

20、将整型数字转换为大写汉字

21 . 重返大于等于 N 的纤维整数值 ?

SQL> SELECT CEIL FROM DUAL ;

21、反省给定串是不是存在于由区间及点集的构成内

22 . 重临小于等于 N 的极小整数值 ?

SQL> SELECT FLOOR FROM DUAL ;

22、依照日期重回星座

23 . 再次回到当前月的最终一天 ?

SQL> SELECT LAST_DAY FROM DUAL ;

23、测算八个日子之间的工作日

24 . 如何差别用户间数据导入 ?

IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y ;

24、听闻年月生成日历函数

25 . 怎么着找数据库表的主键字段的称呼 ?

SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME' ;

25、从第3个汉字起头分割字符串

26 . 八个结果集互加的函数 ?

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW ;SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW ;

26、过滤掉字符串中再度的字符

27 . 七个结实集互减的函数 ?

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW ;

27、依据日期获得星期的函数

28. 怎么着布署 Sequence?

  • 创建sequence

create sequence seq_cust start 1 incrememt by
  • 建表

create table cust { cust_id smallint not null, ...}
  • insert 数据

insert into table cust values( seq_cust.nextval, ... )insert into table cust values( seq_cust.currval, ... )

28、依照年度判断是或不是是闰年

29 . 日期各部分的写法

-- 取时间点的年份的写法:SELECT TO_CHAR(SYSDATE , 'YYYY') FROM DUAL ;-- 取时间点的月份的写法:SELECT TO_CHAR(SYSDATE , 'MM') FROM DUAL ;-- 取时间点的日的写法:SELECT TO_CHAR(SYSDATE , 'DD') FROM DUAL ;-- 取时间点的时的写法:SELECT TO_CHAR(SYSDATE , 'HH24') FROM DUAL ;-- 取时间点的分的写法:SELECT TO_CHAR(SYSDATE , 'MI') FROM DUAL ;-- 取时间点的秒的写法:SELECT TO_CHAR(SYSDATE , 'SS') FROM DUAL ;-- 取时间点的日期的写法:SELECT TRUNC FROM DUAL ;-- 取时间点的时间的写法:SELECT TO_CHAR(SYSDATE , 'HH24 : MI : SS') FROM DUAL ;-- 日期,时间形态变为字符形态:SELECT TO_CHAR FROM DUAL ;-- 将字符串转换成日期或时间形态:SELECT TO_DATE('2003/08/01') FROM DUAL ;-- 返回参数的星期几的写法:SELECT TO_CHAR(SYSDATE , 'D') FROM DUAL ;-- 返回参数一年中的第几天的写法:SELECT TO_CHAR(SYSDATE , 'DDD') FROM DUAL ;-- 返回午夜和参数中指定的时间值之间的秒数的写法:SELECT TO_CHAR(SYSDATE , 'SSSSS') FROM DUAL ;-- 返回参数中一年的第几周的写法:SELECT TO_CHAR(SYSDATE , 'WW') FROM DUAL ;

29、应有尽有SQL农历转换函数

30 . ROWNUM

-- 按设定排序的行的序号SELECT * FROM emp WHERE ROWNUM < 10 ;

30、SQL简繁转换函数

31. 如何寻找重复记录?

SELECT * FROM TABLE_NAME WHERE ROWID!=( SELECT MAX FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2 );

31、自定义函数实现位操作

32 . 怎么样删除重复记录?

DELETE FROM TABLE_NAME WHERE ROWID!=( SELECT MAX FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2 );

32、求某段时光内星期几的气数

33 . 赋予权力

GRANT CONNECT, RESOURCE, --DBA, --unlimited tablespace, CREATE SESSION, CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY VIEW , CREATE ANY INDEX, CREATE ANY PROCEDURE, CREATE ANY DIRECTORY, ALTER SESSION, ALTER ANY SEQUENCE, ALTER ANY TABLE, --ALTER ANY VIEW , --不能修改视图 ALTER ANY INDEX, ALTER ANY PROCEDURE, --ALTER ANY DIRECTORY, --不能修改目录 --DROP SESSION, --不能删除Session DROP ANY SEQUENCE, DROP ANY TABLE, DROP ANY VIEW , DROP ANY INDEX, DROP ANY PROCEDURE, DROP ANY DIRECTORY, SELECT ANY TABLE, SELECT ANY DICTIONARY, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, DEBUG ANY PROCEDURE, DEBUG CONNECT SESSION, exp_full_database, imp_full_database TO user;

33、据说进舍位或四舍五入来求值

34 . 导出视图数据

 create table v_table as( select * from view_table);

34、字符串转成16进制函数

35 . 消除Temp01.dbf不断变大的标题

第一步:alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' drop;第二步:alter tablespace temp add tempfile'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF'size 2048M reuse autoextend on next 100M;第三步:select d.file_name, d.file_id, d.tablespace_name, d.bytes from dba_temp_files d;第四步:alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' autoextend off; 

35、去掉字段中四个带区号电话号码前边的区号

36 . 行转列的多记录合并

id name
1 a
1 b
1 c
2 aa
2 bb

1 . 使用 WM_CONCAT 函数

SQL> select wmsys.wm_concat(distinct name) from t; SQL> select id,wmsys.wm_concat(distinct name) con_name from t group by id; ID CON_NAME -------------- 1 a,b,c 2 aa,bb 

2 . 使用 STRAGG

SQL> SELECT TRIM(',' FROM SYS.STRAGG(NAME||NVL2(NAME,',',''))) CON_NAME FROM T; CON_NAME ---------- a,b,c,aa,bb 

3 . 使用 partition by

select id,sys_connect_by_path(name,'>') con_name from ( select id,name,row_number() over( partition by id order by name) rn from (select id,name from t )) t start with t.rn=1 connect by t.id=prior t.id and t.rn-1=prior t.rn ID CON_NAME ------- -------------------- 1 >a 1 >a>b 1 >a>b>c 2 >aa 2 >aa>bb 2 >aa>bb>china 2 >aa>bb>china>china select id,substr(max(sys_connect_by_path(name,'>')),2) con_name from (select id,name,row_number() over(partition by id order by name) rn from (select id,name from t ))t start with t.rn=1 connect by t.id=prior t.id and t.rn-1=prior t.rn group by id ID CON_NAME ------- -------------------- 1 a>b>c 2 aa>bb>china>china 

4 . 使用 XMLAGG 函数

RTRIM(XMLAGG(XMLPARSE(CONTENT field || ',' WELLFORMED)) .GETCLOBVAL

5 . 自定义类型 VARCHAR2

-- create function of strcat CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type; -- create type define of strcat_typecreate or replace type strcat_type as object ( currentstr varchar2, currentseprator varchar2, static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number, member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number, member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number, member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number ) -- create type body of strcat_typecreate or replace type body strcat_type is static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is begin sctx := strcat_type; return ODCIConst.Success; end; member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is begin if self.currentstr is null then self.currentstr := value; else self.currentstr := self.currentstr ||currentseprator || value; end if; return ODCIConst.Success; end; member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is begin returnValue := self.currentstr; return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is begin if ctx2.currentstr is null then self.currentstr := self.currentstr; elsif self.currentstr is null then self.currentstr := ctx2.currentstr; else self.currentstr := self.currentstr || currentseprator || ctx2.currentstr; end if; return ODCIConst.Success; end; end; 

6 . 自定义类型 clob

-- create function of strcat CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN clob PARALLEL_ENABLE AGGREGATE USING strcat_type;-- create type define of strcat_typecreate or replace type strcat_type as object( CURR_STR clob, STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT strcat_type) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type, P1 IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type, RETURNVALUE OUT clob, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER)-- create type body of strcat_typecreate or replace type body strcat_type is static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is BEGIN SCTX := strcat_type; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type,P1 IN VARCHAR2) RETURN NUMBER IS BEGIN IF (CURR_STR IS NOT NULL) THEN CURR_STR := CURR_STR || ',' || P1; ELSE CURR_STR := P1; END IF; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type, RETURNVALUE OUT clob, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER IS BEGIN IF (SCTX2.CURR_STR IS NOT NULL) THEN SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR; END IF; RETURN ODCICONST.SUCCESS; END;END;

36、SQL3000/2005字符串拆分为列表通用函数

37 . 去掉重复字符串

 /**************************************************** ** Oracle去掉重复字符串 ** 函数名称:RemoveSameStr ** 参 数:  ** oldStr varchar2 要处理的字符串 ** sign varchar2 字符串分隔符 ** 返 回 值: result varchar2 不包含重复子串的记录 ****************************************************/create or replace function rm_same_str(oldStr clob, sign varchar2) return varchar2 is str clob; currentIndex number; startIndex number; endIndex number; type str_type is table of varchar2 index by binary_integer; arr str_type; Result varchar2;begin -- null string if oldStr is null then return; end if; --string is too long if length > 32000 then return; end if; str := oldStr; currentIndex := 0; startIndex := 0; loop currentIndex := currentIndex + 1; endIndex := instr(str, sign, 1, currentIndex); if (endIndex <= 0) then exit; end if; arr(currentIndex) :=substr(str,startIndex + 1, endIndex - startIndex - 1); -- arr(currentIndex) := trim(substr(str,startIndex + 1,endIndex - startIndex - 1)); startIndex := endIndex; end loop; --get the last string arr(currentIndex) := substr(str, startIndex + 1, length; --remove the same string for i in 1 .. currentIndex - 1 loop for j in i + 1 .. currentIndex loop if arr = arr then arr := ''; end if; end loop; end loop; str := ''; for i in 1 .. currentIndex loop if arr is not null then str := str || sign || arr; --set arrar to null: arr := ''; end if; end loop; --remove the prev mark Result := substr(str, 2, length; return;end rm_same_str;

37、求字符串中汉字的个数

38 . 首要函数

  • INITCAP:将字符串 CHA奥迪Q7 的第四个字符为大写,其他为小写。

SELECT INITCAP FROM DUAL ;
  • LENGTH : 取一字符串 CHA昂Cora 的长短。

SELECT LENGTH FROM DUAL ;
  • LOWER :将字符串 CHARAV4 全部变为小写。

SELECT LOWER FROM DUAL ;
  • LPAD(CHAR1 , N , CHAR2) :用字符串 CHA奇骏2 包含的字符左填 CHA帕杰罗1
    ,使其尺寸为 N 。

SELECT LPAD('ABCDEFG' , 10'123') FROM DUAL ;-- 结果: '123ABCDEFG'
  • LTRIM(CHAR , SET) :从字符串 CHARAV4 的左边移去字符串 SET
    中的字符,直到第二个不是 SET 中的字符为

SELECT ('CDEFG' , 'CD') FROM DUAL ;-- 结果: 'EFG'
  • NLS_INITCAP:取字符 CHACR-V 的首先个字符大写,其他字符为小写。

SELECT NLS_INITCAP FROM DUAL ;
  • NLS_LOWER:将字符串 CHASportage 包括的字符全体大写。

SELECT NLS_LOWER FROM DUAL ;
  • NLS_UPPER:将字符串 CHAR 包含的字符全体大写。

SELECT NLS_UPPER FROM DUAL ;
  • REPLACE(CHAR1 , CHAR2 , CHAR3):用字符串 CHA奥迪Q73 代替每二个列值为
    CHAKoleos2 的列,其结果放在 CHARubicon1 中。

SELECT REPLACE(EMP_NO , '123' , '456') FROM DUAL ;
  • RPAD(CHAR1 , N , CHAR2):用字符串 CHA福睿斯2 右填字符串 CHA卡宴1
    ,使其尺寸为 N 。

SELECT RPAD('234' , 8 , '0') FROM DUAL ;
  • RTRIM(CHAR , SET):移去字符串 CHAPRADO 左侧的字符串 SET
    中的字符,直到末了二个不是 SET 中的字符结束。

SELECT RTRIM('ABCDE' , 'DE') FROM DUAL ;
  • SUBST兰德路虎极光(CHA汉兰达 , M , N):获得字符串 CHAXC90 从 M 处开首的 N 个字符 .
    双字节字符,叁在那之中夏族民共和国字为一个字符的。

SELECT SUBSTR('ABCDE' , 2 , 3) FROM DUAL ;
  • SUBSTRB(CHAR , M , N):获得字符串 CHACRUISER 从 M 处起首的 N 个字符 .
    双字节字符,1个汉字为一个字符的。

SELECT SUBSTRB('ABCDE' , 2 , 3) FROM DUAL ;
  • TRANSLATE(CHAR1 , CHAR2 , CHAR3):将 CHA福特Explorer1 中的 CHALAND2 的一部分用
    CHAQX563 代替。

SELECT TRANSLATE('ABCDEFGH' , 'DE' , 'MN') FROM DUAL ;
  • UPPER:将字符串 CHA奔驰M级 全部为大写。
  • ADD_MONTHS:将 N 个月扩展到 D 日期。

SELECT ADD_MONTHS(SYSDATE , 5) FROM DUAL ;
  • LAST_DAY:获得包蕴 D 日期的月度的最终的一天的日期。

SELECT LAST_DAY FROM DUAL ;
  • MONTH_BETWEEN:获得多个日子之间的月数。

SELECT MONTH_BETWEEN FROM DUAL ;
  • NEXT_DAY:获得比日期 D 晚的由 CHA奥迪Q5 命名的率先个周六的日子。

SELECT NEXT_DAY(TO_DATE('2003/09/20') , 'SATDAY') FROM DUAL ;
  • ROUNT:得到按内定的情势 FMT 舍入到的最进的日期。

SELECT ROUNT('2003/09/20' , MONTH) FROM DUAL ;
  • SYSDATE:得到当前系统的日子和岁月。

SELECT SYSDATE FROM DUAL ;
  • TO_CHAR:将日期 D 转换为 FMT 的字符串。

SELECT TO_CHAR(SYSDATE , 'YYYY/MM/DD') FROM DUAL ;
  • TO_DATE(CHAR , FMT):将字符串 CHATucson 按 FMT 的格式转换为日期。

SELECT TO_DATE('2003/09/20' , 'YYYY/MM/DD') FROM DUAL ;
  • ABS:得到 N 的相对值。

SELECT ABS FROM DUAL ;
  • EXP:得到 N 的 E 的 N 次幂。

SELECT EXP FROM DUAL ;
  • MOD:得到 M 除以 N 的余数。

SELECT MOD FROM DUAL ;
  • POWER:得到 M 的 N 幂。

SELECT POWER FROM DUAL ;
  • ROUND:将 N 舍入到小数点后 M 位。

SELECT (78.87653 , 2) FROM DUAL ;
  • SIGN:当 N<0 时,得到 -1 ;当 N>0 时,得到 1 ;当 N=0 时,得到
    0 ;

SELECT SIGN FROM DUAL ;
  • TRUNC:获得在 M 位截断的 N 的值。

SELECT TRUNC(7.7788 , 2) FROM DUAL ;
  • COUNT():总括满意条件的记录数。

SELECT COUNT FROM TABLE1 WHERE COL1='AAA' ;
  • MAX():对点名的列求最大值。

SELECT MAX FROM TABLE1 ;
  • MIN():对点名的列求最小值。

SELECT MIN FROM TABLE1 ;
  • AVG():对点名的列求平均值。

SELECT AVG FROM TABLE1 ;
  • SUM():总结列的和。

SELECT SUM FROM DUAL ;
  • TO_NUMBER:将字符转换为数值。

SELECT TO_NUMBER FROM DUAL ;
  • empty_b|clob():重回3个空的 LOB 定位符 , 用在伊始化 LOB 变量 ,
    或用在 INSEEnclaveT 及 UPDATE 注明去伊始 化LOB 列或将其属性置为空。

INSERT INTO TABLE1 VALUES(EMPTY_BLOB ;UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB() ;
  • NVL(EXPR1 , EXPR2):若 EXPPRADO1 是 NULL ,则赶回 EXPTucson2 ,不然重回EXPSportage1 。

SELECT NAME , NVL(TO_CHAR , 'NOT APPLICATION') FROM TABLE1 ;

38、获取条形码的校验位函数

39. CASE WHEN 和 DECODE

1 . case表达式

--简单Case函数 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END --Case搜索函数 CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END 

2 . CASE WHEN 在言语中差异职位的用法

  • 2.1 SELECT 用法

SELECT grade, COUNT ( CASE WHEN sex = 1 THEN 1 ELSE NULL END) 男生数, COUNT ( CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数FROM studentsGROUP BY grade
  • 2.2 WHERE 用法

SELECT T2.*, T1.*FROM T1, T2WHERE ( CASE WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1 WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1 ELSE 0 END ) = 1
  • 2.3 GROUP BY 用法

SELECT CASE WHEN salary <= 500 THEN '1' WHEN salary > 500 AND salary <= 600 THEN '2' WHEN salary > 600 AND salary <= 800 THEN '3' WHEN salary > 800 AND salary <= 1000 THEN '4' ELSE NULL END salary_class, -- 别名命名 COUNT FROM A GROUP BY CASE WHEN salary <= 500 THEN '1' WHEN salary > 500 AND salary <= 600 THEN '2' WHEN salary > 600 AND salary <= 800 THEN '3' WHEN salary > 800 AND salary <= 1000 THEN '4' ELSE NULL END; 

3 . DECODE() 函数

着力语法:
decode(条件, 值1, 返回值1, 值2, 返回值2, ..., 值n, 返回值n, 缺省值)

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees;

39、依照时间取稳妥月的气数

40. 修改clob字段为varchar2

alter table tablename add (new_column varchar2;update tablename set new_column=dbms_lob.substr(old_column,1000,1);alter table tablename drop column old_column;alter table tablename rename column new_column to old_column;

40、将3个正整数分解为m个2的n次方的和

41. 运用正则表明式查询

--非正整数 select 字段 from 表 where regexp_replace(字段,'\d','') is not null;--非数值类型select 字段 from 表 where regexp_replace(字段,'^[-\+]?\d+?$','') is not null;--自定义函数,判断非值类型create or replace function isnumber(col varchar2) return i number;begin i := to_number; return 1;exception when others then return 0;end;select 字段 from 表 where isnumber=0;

41、SQL位移运算函数

42. 判断某個字段的值是还是不是数字

共有三种艺术,分别是行使to_number()regexp_like()translate()二种函数来进行匹配。假设字段的值是数字,重临1,不然再次来到0,具体的达成如下,三种办法任选壹种即可。运营结果:假使字符串是数字格式则赶回1,不是则再次来到0。

--1、利用 to_numberCREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2) RETURN NUMBERIS v_str FLOAT;BEGIN IF str IS NULL THEN RETURN 0; ELSE BEGIN SELECT TO_NUMBER  INTO v_str FROM DUAL; EXCEPTION WHEN INVALID_NUMBER THEN RETURN 0; END; RETURN 1; END IF;END isnumeric;/--2、利用 regexp_likeCREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2) RETURN NUMBERISBEGIN IF str IS NULL THEN RETURN 0; ELSE IF regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$') THEN RETURN 1; ELSE RETURN 0; END IF; END IF;END isnumeric;/--3、利用 translateCREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2) RETURN NUMBERIS v_str VARCHAR2 ;BEGIN IF str IS NULL THEN RETURN 0; ELSE v_str := translate(str, '.0123456789', '.'); IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL THEN RETURN 1; ELSE RETURN 0; END IF; END IF;END isnumeric;

鉴于 Oracle
数据库自己没有提供检测字段是或不是为数字的艺术,由此大家须要协调创制壹個
isnumeric() 的函数,然后再调用它来展开判定。具体的调用方式如下:

SQL> select isnumeric from dual;ISNUMERIC----------------- 0SQL> select isnumeric('123.509') from dual;ISNUMERIC('123.509')-------------------- 1SQL> select isnumeric('123.205.10.8') from dual;ISNUMERIC('123.205.10.8')------------------------- 0

42、赢得汉字笔画函数

43. 重复记录查询

壹 、查找表中多余的重复记录,重复记录是基于单个字段 xxxId 来判断

select * from xxx_tablewhere xxxId in (select xxxId from xxx_table group by xxxId having count > 1)

二 、删除表中多余的重复记录,重复记录是根据单个字段 xxxId
来判断,只留有rowid最小的笔录

delete from xxx_table where xxxId in (select xxxId from xxx_table group by xxxId having count > 1) and rowid not in (select min from xxx_table group by xxxId having count>1)

叁 、查找表中多余的重复记录

select * from xxx_table awhere (a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count > 1)

4、删除表中多余的重复记录,只留有rowid最小的记录

delete from xxx_table awhere (a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count > 1)and rowid not in (select min from xxx_table group by xxxIdA,xxxIdB having count

五 、查找表中剩下的重复记录,不含有rowid最小的笔录

select * from xxx_table awhere (a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count > 1) and rowid not in (select min from xxx_table group by xxxIdA,xxxIdB having count

43、SQL数字转英文函数

44、全角半角转换函数

45、回去多个时间范围内的多少个任意时间

46、获取成分个数的函数

47、取得钦赐索引的值的函数

48、听他们说年收获全数星期四的日子

49、变迁五个时刻之内的具备日期

50、冬日,冬辰字符相比较函数

51、在SQL
SEKoleosVELX570中完毕TiggoSA加解密函数(第贰版)

52、在SQL
SE君越VER中完结RAV4SA加解密函数(第①版)

53、输出钦点格式的数据列

54、汉字转拼音函数

55、数字转IP地址函数

56、对字符串进行加密解密

57、计量个人所得税函数

--根据部门ID字符串例"0001,0002,0003"获取对应的部门名称
ALTER FUNCTION [dbo].[GetDeptNames]
    (@DeptIdStr nvarchar(2000))
RETURNS nvarchar(4000)
AS
BEGIN
    IF @DeptIdStr IS NULL
        RETURN ''

    DECLARE @DeptNameStr    nvarchar(4000)
    DECLARE @DeptName       nvarchar(50)
    DECLARE @i          int
    SET @i = 0

    --全部部门
    IF UPPER(@DeptIdStr) = 'All_DEPT'
        RETURN '全体部门'
    ELSE
        DECLARE Dept_Cursor CURSOR FOR 
            SELECT
                [DepartmentName]
            FROM
                [dbo].[Department]
            WHERE
                CHARINDEX(',' + CAST(DepartmentId AS nvarchar(20)) + ',',',' + @DeptIdStr + ',') > 0
            ORDER BY
                [Department].[DepartmentId] ASC

    OPEN Dept_Cursor

    FETCH NEXT FROM Dept_Cursor INTO @DeptName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @i = 0
            SET @DeptNameStr = @DeptName
        ELSE
            SET @DeptNameStr = @DeptNameStr + ',' + @DeptName
        SET @i = @i + 1

        FETCH NEXT FROM Dept_Cursor INTO @DeptName
    END

    CLOSE Dept_Cursor
    DEALLOCATE Dept_Cursor

    RETURN @DeptNameStr
END

--测试
--select * ,dbo.GetDeptNames(DepartmentId) from Users

相关文章