1.oracle md5加密的方式
1.先创建md5的函数
CREATE OR REPLACE FUNCTION MD5(
passwd IN VARCHAR2)
RETURN VARCHAR2
IS
retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN LOWER(retval);--大小写转换
END;
2.在语法中使用
insert into t_user(oid,account,password,locked)values(2,'shaobin1990',md5('shaobin1990'||888888),'1')2.
2.分页
-- pageSize = 2
-- currentPage = 4
SELECT T.*
FROM (SELECT X.*, ROWNUM AS RN
FROM (SELECT * FROM DEPT ORDER BY DEPTNO) X
WHERE ROWNUM <= (2 * 5)) T
WHERE T.RN > (2 * (5 - 1));
分页查询,必须使用rouwnum这个属性 rownum是oracle表中自带的属性
3. -- MYSQL中获取当前时间
-- SELECT NOW();
-- Oracle 获取当前时间
-- SELECT SYSDATE FROM DUAL;
-- Java 日期转串SimpleDateFormat#format
-- 串转日期 SimpleDateFormat#parse
-- Oralce date类型装varchar2 to_char
-- varchar2类型转date to_date
SELECT to_char(SYSDATE, 'yyyy-MM-dd HH24:MI:SS') FROM DUAL;
SELECT to_date('2012-12-12 12:12:12', 'yyyy-MM-dd HH24:MI:SS') from dual;
daul 是虚拟的表 经常使用
4.根据某个数字显示不同的汉字
select eid,ename,
case sex
when 'm' then '男'
when 'f' then '女'
else sex
end
from emp;
经常使用的一个关键字
5.exists关键字
select m.*
from message m
where exists (select 1
from t_user u
where u.account = 'root'
and u.oid = m.oid)
AA_1, DD_8
不是很好理解
-- 检索子辈,包含自身
select level, x.*
from t_department x
connect by x.pid = prior x.id
start with x.id = 2;
-- 检索子辈,不包含自身
select level, x.*
from t_department x
connect by x.pid = prior x.id
start with x.pid = 2;
-- 检索父辈
select level, x.*
from t_department x
connect by x.id = prior x.pid
start with x.id = 4
--定义视图
CREATEVIEW VIEW_NAME
AS
SELECT* FROM BASE_TABLE
[WITHREAD ONLY]
[WITHCHECK OPTION]