1.常用函数

1.TO_CHAR ---- 日期转换

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual

转换日期时to_char函数的格式参数应该传递如'YYYY-MM-DD HH24:MI:SS',不区分大小写,之前写的'yyyy-MM-dd HH:mm:ss'有问题: 没有指定24小时制,'HH'应该为'HH24',因为格式不区分大小写,所以'MM'和'mm'会认为是同样的代码,月份应使用'MM',分采用'MI'.

2.SYS_GUID ---- 生成UUID

select sys_guid() from dual; /*默认如 A8F662B86E7413FEE040970A437C6BD7*/
select lower(sys_guid()) from dual; -- 小写

默认是大写没有分隔符,使用lower()函数可转换为小写

2.修改过期密码

1.首先登录 sqlplus / as sysdba

2.修改密码

alter user xxx identified by "22@@xx!";

3.解锁用户

alter user xxx account unclock ;

3.常用SQL语句

1. INSERT INTO SELECT 语句

语句形式为INSERT INTO Table1(field1,field2..) SELECT value1,value2... FROM Table2 ,这里需要注意的是字段需要一致.

insert into TBL_ROLE_MENU(ID, ROLE_ID, MENU_ID, CREATE_TIME, UPDATE_TIME, DELETE_FLAG)
SELECT *
from (
         select b.guid                        as ID,
                (select ID
                 from TBL_ROLE
                 where CODE = 'ADMIN') 		  as ROLE_ID,
                t.ID                          as MENU_ID,
                sysdate                       as CREATE_TIME,
                sysdate                       as UPDATE_TIME,
                (select 0 from dual)          as DELETE_FLAG
         from TBL_MENU t,
              (select lower(sys_guid()) as guid
               from dual) b
         where t.DELETE_FLAG <> 1
           and t.HIDDEN <> 1
     );

2.INSERT ALL 批量插入数据语句

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;