Some system functions, custom functions, and keywords in oracle notes

Some system functions, custom functions, and keywords in oracle notes
the
Inscription: Collect some oracle knowledge points commonly used in work, constantly update and accumulate, and use it as your own notepad.
the
some keywords
the
1 IS NOT NULL, to determine whether the field is empty
the
[sql]
select * from t_user u where u.name is not null
the
2 CASE WHEN. ． ． THEN . ． ． ELSE. ． ． END , conditional statement
[sql]
SELECT CASE WHEN & #39; condition & #39; IS NOT NULL THEN & #39; condition is true & #39; ELSE & #39; condition is not true & #39; END AS result FROM t_user
the
3 EXITS , NOT EXITS, judge whether it exists, usually put it behind WHERE
the
some system functions
the
1. TO_CHAR()
[sql]
select to_char(sysdate,’yyyy-MM-dd HH24:mi:ss’) FROM DUAL
Display: 2012-06-15 10:11:45
www.2cto.com
2. TO_DATE() Convert to a time in a certain format
[sql]
select to_date('$time parameter $0:0:0','yyyy-mm-dd hh24:mi:ss') from dual
The time parameter can be: 2012-06-15
the
some custom functions
the
1. Write a function CHECK_DEMAND_IS_NOTICE that is used in work. This function needs to pass in a NUMBER type of data, and the result returns a VARCHAR2 type of data
[sql]
CREATE OR REPLACE FUNCTION CHECK_DEMAND_IS_NOTICE(V_DE_ID IN NUMBER) RETURN VARCHAR2
— Check whether the demand list has been notified
IS
— The number of orders to be loaded that has not been notified
V_NOT_NOTICE_COUNT NUMBER(12);
BEGIN
–Inquire
SELECT COUNT(1) INTO V_NOT_NOTICE_COUNT FROM ZY_POC_WAIT_ORDER W WHERE IS_NOTICE = '0'
AND EXISTS( www.2cto.com
SELECT 1 FROM ZY_MAP_DEMAND_WAIT_ORDER Z WHERE Z.DE_ID =V_DE_ID AND W.ID = WAIT_ORDER_ID
);
–If it is greater than 0, the demand order has not been notified.
IF V_NOT_NOTICE_COUNT >0 THEN
RETURN '0';
END IF;
RETURN '1';
END;
the
the
Author baolong47