最近在工作中遇到了一条sql语句,尽管与我的工作没有多大的联系.但是我仍然想搞清楚.只可惜本人学习Oracle时间有限,再加上能力的制约,所以一直不是很明白.所以想请假各位高手帮帮忙,指点一下.下面的sql语句的含义是什么:SELECT
P_USER_LOGIN.LOGIN_NAME_ as loginName,
innerTable.REAL_NAME_ as realName,
innerTable.ADVISER_CODE_ as adviserCode,
innerTable.BRANCH_CODE_ as branchCode,
innerTable.DEALER_CODE_ as dealerCode,
(case PASSWORD_ when fn_md5(#password#) then 'ok'
else 'wrong' end
) check_passwd,
P_USER_LOGIN.IS_FROZEN_ACC_,
P_USER_LOGIN.IS_INIT_PASSWD_,
P_USER_LOGIN.IS_LOGIN_,
P_USER_LOGIN.IS_BANNED_,
P_USER_LOGIN.LOGIN_FAIL_COUNT_,
to_date(to_char(sysdate, 'YYYYMMDD'), 'YYYYMMDD') - to_date(to_char(P_USER_LOGIN.PASSWD_MODIFY_DATE_, 'YYYYMMDD'), 'YYYYMMDD') as passwdInterval
FROM
P_USER_LOGIN inner join
(
select
P_USER.USER_NAME_,
P_USER.REAL_NAME_,
P_USER.ADVISER_CODE_,
P_USER.BRANCH_CODE_,
P_USER.DEALER_CODE_
from
P_USER
where
P_USER.IS_DELETE_='N'
) innerTable on innerTable.USER_NAME_=P_USER_LOGIN.LOGIN_NAME_
WHERE
P_USER_LOGIN.LOGIN_NAME_=#loginName#
不胜感激!!!
P_USER_LOGIN.LOGIN_NAME_ as loginName,
innerTable.REAL_NAME_ as realName,
innerTable.ADVISER_CODE_ as adviserCode,
innerTable.BRANCH_CODE_ as branchCode,
innerTable.DEALER_CODE_ as dealerCode,
(case PASSWORD_ when fn_md5(#password#) then 'ok'
else 'wrong' end
) check_passwd,
P_USER_LOGIN.IS_FROZEN_ACC_,
P_USER_LOGIN.IS_INIT_PASSWD_,
P_USER_LOGIN.IS_LOGIN_,
P_USER_LOGIN.IS_BANNED_,
P_USER_LOGIN.LOGIN_FAIL_COUNT_,
to_date(to_char(sysdate, 'YYYYMMDD'), 'YYYYMMDD') - to_date(to_char(P_USER_LOGIN.PASSWD_MODIFY_DATE_, 'YYYYMMDD'), 'YYYYMMDD') as passwdInterval
FROM
P_USER_LOGIN inner join
(
select
P_USER.USER_NAME_,
P_USER.REAL_NAME_,
P_USER.ADVISER_CODE_,
P_USER.BRANCH_CODE_,
P_USER.DEALER_CODE_
from
P_USER
where
P_USER.IS_DELETE_='N'
) innerTable on innerTable.USER_NAME_=P_USER_LOGIN.LOGIN_NAME_
WHERE
P_USER_LOGIN.LOGIN_NAME_=#loginName#
不胜感激!!!
(
select
P_USER.USER_NAME_,
P_USER.REAL_NAME_,
P_USER.ADVISER_CODE_,
P_USER.BRANCH_CODE_,
P_USER.DEALER_CODE_
from
P_USER
where
P_USER.IS_DELETE_='N'
) innerTable ----这个innerTable 是上面()里select出来的集合的别名 后面的ON 是INNER JOIN 的语法 ON后面是连接的字段
(case PASSWORD_ when fn_md5(#password#) then 'ok'
else 'wrong' end
) check_passwd, 这个是干什么的,返回的结结果是什么.
如果PASSWORD = fn_md5(#password#) 返回的结果 则'ok' 否则'wrong' 返回的结果是显示的是 'ok‘ 还是 'wrong'
判断 fn_md5(#password#) 有返回值则ok,其他情况的话则wrong
innertable.real_name_ AS realname,
innertable.adviser_code_ AS advisercode,
innertable.branch_code_ AS branchcode,
innertable.dealer_code_ AS dealercode,
DECODE (password_, fn_md5 (#password#), 'ok', 'wrong') check_passwd,
p_user_login.is_frozen_acc_, p_user_login.is_init_passwd_,
p_user_login.is_login_, p_user_login.is_banned_,
p_user_login.login_fail_count_,
TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')
- TO_DATE (TO_CHAR (p_user_login.passwd_modify_date_, 'YYYYMMDD'), 'YYYYMMDD') AS passwdinterval
FROM p_user_login,
(SELECT p_user.user_name_, p_user.real_name_, p_user.adviser_code_,
p_user.branch_code_, p_user.dealer_code_
FROM p_user
WHERE p_user.is_delete_ = 'N') innertable
WHERE innertable.user_name_ = p_user_login.login_name_
AND p_user_login.login_name_ = #loginname#