RAW if the measure column is RAW; otherwise the return value is VARCHAR2. Aggregate Examples The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name: SELECT LISTAGG(last_name, ‘; ‘) WITHIN GROUP ...
simple example:1、table()结合数组:*/create or replace type t_testasobject( id integer, rq date, mc varchar2(60) ); create or replace type t_test_tableastable of t_test; create or replace function f_test_array(ninnumberdefaultnull)returnt_test_tableasv_test t_test_table :=t_test_table...
Oracle FIRST_VALUE() function examples We’ll use theproductstable from thesample databaseto demonstrate theFIRST_VALUE()function: The following example returns the product id, product name, list price, and the name of the product with the lowest price: ...
Here is an example: create or replace package types as type cursorType is ref cursor; end; / create or replace function sp_ListEmp return types.cursortype as l_cursor types.cursorType; begin open l_cursor for select ename, empno from emp order by ename; return l_cursor; end; / create...
Example: GET_TABLE_VALUE('WAGE RATES', 'Wage Rate', Rate_Code) GREATEST expr expr expr GREATEST_OF(expr,expr[,expr] . . .) The GREATEST function compares the values of all the text string operands. It returns the value of the operand that is alphabetically last. If there are two or...
For example, to remove the last character from the name of this site, “Database Star”, the function would be: SUBSTR("Database Star",0,LENGTH("Database Star")-1) This would return: “Database Sta” See the Examples section below for more examples. ...
Return value TheEXTRACT()function returns the value of the field of the source. Examples A) Extracting fields from DATE values You can extract YEAR, MONTH, and DAY from a DATE value by using theEXTRACT()function. The following example extracts the value of the YEAR field from a DATE value...
Example 12-29 Creating and Invoking Pipelined Table Function CREATE OR REPLACE PACKAGE pkg1 AS TYPE numset_t IS TABLE OF NUMBER; FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED; END pkg1; / CREATE PACKAGE BODY pkg1 AS -- FUNCTION f1 returns a collection of elements (1,2,3,... x)...
在Oracle PL/SQL中,CASE表达式(CASE Expression)是一个非常强大且常用的工具,可以用于在SQL或PL/SQL中执行基于条件的操作。CASE表达式类似于IF-THEN-ELSE语句,但使用起来更加灵活,易于阅读和编写。 CASE表达式包含两种形式,一种是简单形式,一种是搜索形式。 下面将分别介绍这两种形式的写法、语法以及使用方法。
Function Reference DocumentationOracle.Database Note Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities. Prerequisites Supported Oracle versions: Oracle Database Server 12c (12.1.0.2) and later ...