SQL

1. DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

 

2.  to_date('2008-04-23 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')

     SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') FROM dual;

 

3. SUBSTR 从1开始而不是0

    SUBSTR ('shidong', 2) return idong

 

4. Oracle PIPELINED

 Pipelined functions are useful if there is a need for a data source other than a table in a select statement.

Typical Pipelined Example:

4.1 The producer function must use the PIPELINED keyword in its declaration.

4.2 The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.

4.3 Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword

4.4 The producer function must end with a RETURN statement that does not specify any return value.

4.5 The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

定义返回类型:

CREATE OR REPLACE TYPE myObjectFormat
AS OBJECT
(
A INT,
B DATE,
C VARCHAR2(25)
)
/

 

CREATE OR REPLACE TYPE myTableType
AS TABLE OF myObjectFormat
/

 

定义包/包体:

CREATE OR REPLACE PACKAGE myDemoPack
AS
FUNCTION prodFunc RETURN myTableType PIPELINED;
END;
/

 

CREATE OR REPLACE PACKAGE BODY myDemoPack AS
FUNCTION prodFunc RETURN myTableType PIPELINED IS
BEGIN
FOR i in 1 .. 5
LOOP
PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));
END LOOP;
RETURN;
END;
END;
/

客户端调用:

SELECT * FROM TABLE(myDemoPack.prodFunc());

摘自:http://www.itpub.net/608483.html