Oracle system functions, user-defined functions, and keywords

Question: Collect Oracle knowledge points that are commonly used at work, constantly update and accumulate, and use them as your own notebook.

Some keywords

1Is not null. Check whether the field is null.

1. Select*FromT_user uWhereU.Name Is Not Null

2 Case when... THEN... ELSE... END, Condition Statement

1. SELECTCASE WHEN 'Condition' IS NOT NULL THEN 'Condition exist' ELSE 'The condition is invalid' END ASResultFROMT_user

3 EXITS, not exits, to determine whether it exists, usually placed behind the WHERE

Some system functions

1. TO_CHAR ()

1. SelectTo_char (sysdate,'Yyyy-MM-dd HH24: mi: ss')FROMDUAL

Display: 10:11:45

2. Convert TO_DATE () to a certain format of time

1. SelectTo_date ('$ Time Parameter $0: 0','Yyyy-mm-dd hh24: mi: ss')FromDual

The time parameter can be:

Some customFunction

1. Write the CHECK_DEMAND_IS_NOTICE function used by a job. The function needs to input data of the NUMBER type, and a data of the VARCHAR2 type is returned.

1. CREATE OR REPLACE FUNCTIONCHECK_DEMAND_IS_NOTICE (V_DE_IDINNUMBER)RETURNVARCHAR2
2. -- Check whether the request form has been notified
3. IS
4. -- Number of pending orders not notified
5. V_NOT_NOTICE_COUNT NUMBER (12 );
6. BEGIN
7. -- Query
8. SELECT COUNT(1)INTOV_NOT_NOTICE_COUNTFROMZY_POC_WAIT_ORDER WWHEREIS_NOTICE ='0'
9. ANDEXISTS (
10. SELECT1FROMZY_MAP_DEMAND_WAIT_ORDER ZWHEREZ. DE_ID = V_DE_IDANDW. ID = WAIT_ORDER_ID
11. );
12. -- If the value is greater than 0, the request ticket is not notified.
13. IF V_NOT_NOTICE_COUNT> 0THEN
14. RETURN '0';
15. ENDIF;
16. RETURN '1';
17. END;