Oracle SQL

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

  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;

  1. SUBSTR 从1开始而不是0   
    SUBSTR ('shidong', 2) return idong

  2. 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:

  1. The producer function must use the PIPELINED keyword in its declaration.
  2. The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.
  3. Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword
  4. The producer function must end with a RETURN statement that does not specify any return value.
  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.
    定义返回类型:
1
2
3
4
CREATE OR REPLACE TYPE myObjectFormat AS OBJECT ( A INT, B DATE, C VARCHAR2(25) ) 
/   
CREATE OR REPLACE TYPE myTableType AS TABLE OF myObjectFormat 
/  

定义包/包体: 

1
2
3
4
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; 
/

客户端调用:

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