Oracle notes: system functions, user-defined functions, and keywords

Oracle notes some system functions, user-defined functions, and keyword inscriptions: Collect oracle knowledge points that are commonly used at work, update constantly, and accumulate constantly. Just use it as your own notebook. Some keywords 1 is not null to determine whether the field IS NULL
[SQL] select * from t_user u where u. name is not null 2 case when... THEN... ELSE... END, Condition Statement [SQL] SELECT CASE when' condition IS NOT NULL then' condition IS true 'else' condition IS NOT true 'end AS result FROM t_user 3 EXITS, NOT EXITS, determine whether or not to exist. Generally, some system functions are placed after the WHERE clause. 1. TO_CHAR () [SQL] select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') FROM DUAL display: 10:11:45 www.2cto.com 2. TO_DATE () is converted to a certain format of time [SQL] select to_date ('$ Time Parameter $0: 0', 'yyyy-mm-dd hh24: mi: ss ') from dual time parameter can be: some custom functions 1. write the CHECK_DEMAND_IS_NOTICE FUNCTION used for work. This FUNCTION requires a data of the NUMBER type to be passed IN, and returns a data of the VARCHAR2 type [SQL] CREATE OR REPLACE FUNCTION check_deman_is_notice (V_DE_ID IN NUMBER) RETURN VARCHAR2 -- check whether the request ticket has been notified IS -- NUMBER of unnotified pending orders V_NOT_NOTICE_COUNT NUMBER (12); BEGIN -- Query 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_deman_wait_order z where z. DE_ID = V_DE_ID and w. ID = WAIT_ORDER_ID); -- IF the value is greater than 0, the request ticket is not notified IF V_NOT_NOTICE_COUNT> 0 then return '0'; end if; RETURN '1'; END; author baolong47