oracle数据库函数（国外英文资料）.doc

oracle数据库函数（国外英文资料） The function in oracle database: The cartesian set: the record of two tables multiplied Check the tree for several layers The select count (distinct level) from emp start with empno = 7839 connect by prior empno = MGR Select level, lpad (, 2 * level-1) | | ename from emp start with empno = 7839 connect by prior empno = MGR Use the set operator The set operator can combine two rows or rows returned by multiple queries. 1, union all: returns all rows retrieved from each query, including duplicate rows Union: returns all rows retrieved from each query, not including duplicate rows Intersect: returns the common rows retrieved by two queries Minus: returns the row that retrieves the second query from the row that was retrieved from the first query minus the remaining record The difference between UNION and UNION ALL: 1. In the query will encounter UNION ALL, its usage, and the UNION, the UNION only contains distinct features, it can remove the two form duplicate records, and UNION ALL cant, so, from the efficiency of UNION ALL will be a little higher. The union all is arranged in order, and the union resorts the duplicate data in the two tables MINUS: The instructions are applied to two SQL statements. It first finds the results of the first SQL statement, and then see if the results are in the results of the second SQL statement. If so, the data is removed and wont appear in the final results. If the result of the second SQL statement does not exist in the result of the first SQL statement, the data is discarded. Commonly used functions: (1) NVL (string, t_number) When string is NULL (empty) value, take the t_number value Converting empty values to a value that has real meaning, which affects the value of a single step Total annual income of all employees in the emp table: Select ename, sal * 12 * (1 + NVL (comm / 100001)) from emp; Nvl2 (c1, c2, c3) - c1 is empty, and c3, if c1, c3 is both empty and c2 (2) distinct: prevent data repeatability Select