Oracle PL/SQL/Stored Procedure Function/PIPELINED

Материал из SQL эксперт
Перейти к: навигация, поиск

An example of a pipelined table function.

   <source lang="sql">
 

SQL> SQL> SQL> CREATE TYPE MyType AS OBJECT (

 2    field1 NUMBER,
 3    field2 VARCHAR2(50));
 4  /

SQL> SQL> CREATE or replace TYPE MyTypeList AS TABLE OF MyType;

 2  /

Type created. SQL> SQL> CREATE OR REPLACE FUNCTION PipelineMe

 2    RETURN MyTypeList PIPELINED AS
 3    v_MyType MyType;
 4  BEGIN
 5    FOR v_Count IN 1..20 LOOP
 6      v_MyType := MyType(v_Count, "Row " || v_Count);
 7      PIPE ROW(v_MyType);
 8    END LOOP;
 9    RETURN;
10  END PipelineMe;
11  /

Function created. SQL> SQL> SELECT *

 2    FROM TABLE(PipelineMe);
   FIELD1 FIELD2

--------------------------------------------------
        1 Row 1
        2 Row 2
        3 Row 3
        4 Row 4
        5 Row 5
        6 Row 6
        7 Row 7
        8 Row 8
        9 Row 9
       10 Row 10
       11 Row 11
       12 Row 12
       13 Row 13
       14 Row 14
       15 Row 15
       16 Row 16
       17 Row 17
       18 Row 18
       19 Row 19
       20 Row 20

20 rows selected. SQL> SQL> DROP TYPE MyType;

SQL> SQL>

 </source>
   
  


pipelined returning value and table() function

   <source lang="sql">
  

SQL> SQL> SQL> create table myTable as

 2   select trunc(created) created
 3   from all_objects;

Table created. SQL> SQL> create or replace

 2   type date_list is table of Date;
 3  /

Type created. SQL> create or replace function pipe_date(p_start date, p_limit number)

 2   return date_list pipelined is
 3   begin
 4       for i in 0 .. p_limit-1 loop
 5           pipe row (p_start + i);
 6       end loop;
 7   return;
 8   end;
 9  /

Function created. SQL> SQL> select column_value, count(created) no_of_obj

 2   from myTable, table(pipe_date(trunc(sysdate)-14,14))
 3   where column_value = myTable.created(+)
 4   group by column_value
 5  /

COLUMN_VA NO_OF_OBJ


----------

12-OCT-09 0 13-OCT-09 0 14-OCT-09 0 15-OCT-09 45 16-OCT-09 55 17-OCT-09 243 18-OCT-09 177 19-OCT-09 53 20-OCT-09 0 21-OCT-09 24 22-OCT-09 0 COLUMN_VA NO_OF_OBJ


----------

23-OCT-09 0 24-OCT-09 0 25-OCT-09 0 14 rows selected. SQL> SQL> drop table myTable; Table dropped.


 </source>
   
  


Using a pipelined table function.

   <source lang="sql">
  

SQL> SQL> SQL> CREATE OR REPLACE TYPE OracleError AS OBJECT (

 2    ErrNumber INTEGER,
 3    Message VARCHAR2(4000));
 4  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE OracleErrors AS TABLE OF OracleError;

 2  /

Type created. SQL> SQL> CREATE OR REPLACE FUNCTION OracleErrorTable

 2    RETURN OracleErrors DETERMINISTIC PIPELINED
 3  AS
 4    v_Low PLS_INTEGER := -65535;
 5    v_High PLS_INTEGER := 100;
 6    v_Message VARCHAR2(4000);
 7  BEGIN
 8    FOR i IN v_Low..v_High LOOP
 9      v_Message := SQLERRM(i);
10
11      IF v_Message != " -" || TO_CHAR(i) || ": non-ORACLE exception "
12      AND v_Message != "ORA" || TO_CHAR(i, "00000") || ": Message " ||
13          TO_CHAR(-i) || " not found;  product=RDBMS; facility=ORA"
14      THEN
15        PIPE ROW(OracleError(i, v_Message));
16      END IF;
17    END LOOP;
18    RETURN;
19  END;
20  /

Function created. SQL> SQL> CREATE OR REPLACE VIEW all_oracle_errors

 2    AS SELECT * FROM TABLE(OracleErrorTable());

View created. SQL> SQL> desc all_oracle_errors

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ERRNUMBER                                          NUMBER
MESSAGE                                            VARCHAR2(4000)

SQL> SELECT MIN(errnumber), MAX(errnumber), COUNT(*) FROM all_oracle_errors; MIN(ERRNUMBER) MAX(ERRNUMBER) COUNT(*)


-------------- ----------
       -44412            100      15430

1 row selected. SQL> SQL> SQL> COLUMN message FORMAT a60 SQL> SQL> SELECT *

 2    FROM all_oracle_errors
 3    WHERE errnumber BETWEEN -115 AND 100;
ERRNUMBER MESSAGE

------------------------------------------------------------
     -115 ORA-00115: connection refused; dispatcher connection table i
          s full
     -114 ORA-00114: missing value for system parameter SERVICE_NAMES
     -113 ORA-00113: protocol name  is too long
     -112 ORA-00112: value of  is null
     -111 ORA-00111: invalid attribute
     -110 ORA-00110: invalid value  for attribute , must be between  a
          nd
     -109 ORA-00109: invalid value for attribute :
     -108 ORA-00108: failed to set up dispatcher to accept connection
          asynchronously
     -107 ORA-00107: failed to connect to ORACLE listener process
     -106 ORA-00106: cannot startup/shutdown database when connected t
          o a dispatcher
     -105 ORA-00105: too many dispatcher configurations
     -104 ORA-00104: deadlock detected; all public servers blocked wai
          ting for resources
     -103 ORA-00103: invalid network protocol; reserved for use by dis
          patchers
     -102 ORA-00102: network protocol  cannot be used by dispatchers
     -101 ORA-00101: invalid specification for system parameter DISPAT
          CHERS
     -100 ORA-00100: no data found
      -97 ORA-00097: use of Oracle SQL feature not in SQL92  Level
      -96 ORA-00096: invalid value  for parameter , must be from among
      -94 ORA-00094:  requires an integer value
      -93 ORA-00093:  must be between  and
      -92 ORA-00092: LARGE_POOL_SIZE must be greater than LARGE_POOL_M
          IN_ALLOC
      -91 ORA-00091: LARGE_POOL_SIZE must be at least
      -90 ORA-00090: failed to allocate memory for cluster database OR
          ADEBUG command
      -89 ORA-00089: invalid instance number in ORADEBUG command
      -88 ORA-00088: command cannot be executed by shared server
      -87 ORA-00087: command cannot be executed on remote instance
      -86 ORA-00086: user call does not exist
      -85 ORA-00085: current call does not exist
      -84 ORA-00084: global area must be PGA, SGA, or UGA
      -83 ORA-00083: warning: possibly corrupt SGA mapped
      -82 ORA-00082: memory size of  is not in valid set of [1], [2],
          [4]
      -81 ORA-00081: address range [, ) is not readable
      -80 ORA-00080: invalid global area specified by level
      -79 ORA-00079: variable  not found
      -78 ORA-00078: cannot dump variables by name
      -77 ORA-00077: dump  is not valid
      -76 ORA-00076: dump  not found
      -75 ORA-00075: process "" not found in this instance
      -74 ORA-00074: no process has been specified
      -73 ORA-00073: command  takes between  and  argument(s)
      -72 ORA-00072: process "" is not active
      -71 ORA-00071: process number must be between 1 and
      -70 ORA-00070: command  is not valid
      -69 ORA-00069: cannot acquire lock -- table locks disabled for
      -68 ORA-00068: invalid value  for parameter , must be between  a
          nd
      -67 ORA-00067: invalid value  for parameter ; must be at least
      -65 ORA-00065: initialization of FIXED_DATE failed
      -64 ORA-00064: object is too large to allocate on this O/S (,)
      -63 ORA-00063: maximum number of log files exceeded
      -62 ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS
           is 0
      -61 ORA-00061: another instance has a different DML_LOCKS settin
          g
      -60 ORA-00060: deadlock detected while waiting for resource
      -59 ORA-00059: maximum number of DB_FILES exceeded
      -58 ORA-00058: DB_BLOCK_SIZE must be  to mount this database (no
          t )
      -57 ORA-00057: maximum number of temporary table locks exceeded
      -56 ORA-00056: DDL lock on object "." is already held in an inco
          mpatible mode
      -55 ORA-00055: maximum number of DML locks exceeded
      -54 ORA-00054: resource busy and acquire with NOWAIT specified
      -53 ORA-00053: maximum number of enqueues exceeded
      -52 ORA-00052: maximum number of enqueue resources () exceeded
      -51 ORA-00051: timeout occurred while waiting for a resource
      -50 ORA-00050: operating system error occurred while obtaining a
          n enqueue
      -42 ORA-00042: Unknown Service name
      -41 ORA-00041: active time limit exceeded - session terminated
      -40 ORA-00040: active time limit exceeded - call aborted
      -38 ORA-00038: Cannot create session: server group belongs to an
          other user
      -37 ORA-00037: cannot switch to a session belonging to a differe
          nt server group
      -36 ORA-00036: maximum number of recursive SQL levels () exceede
          d
      -35 ORA-00035: LICENSE_MAX_USERS cannot be less than current num
          ber of users
      -34 ORA-00034: cannot  in current PL/SQL session
      -33 ORA-00033: current session has empty migration password
      -32 ORA-00032: invalid session migration password
      -31 ORA-00031: session marked for kill
      -30 ORA-00030: User session ID does not exist.
      -29 ORA-00029: session is not a user session
      -28 ORA-00028: your session has been killed
      -27 ORA-00027: cannot kill current session
      -26 ORA-00026: missing or invalid session ID
      -25 ORA-00025: failed to allocate
      -24 ORA-00024: logins from more than one process not allowed in
          single-process mode
      -23 ORA-00023: session references process private memory; cannot
           detach session
      -22 ORA-00022: invalid session ID; access denied
      -21 ORA-00021: session attached to some other process; cannot sw
          itch session
      -20 ORA-00020: maximum number of processes () exceeded
      -19 ORA-00019: maximum number of session licenses exceeded
      -18 ORA-00018: maximum number of sessions exceeded
      -17 ORA-00017: session requested to set trace event
       -1 ORA-00001: unique constraint (.) violated
        0 ORA-0000: normal, successful completion
        1 User-Defined Exception
      100 ORA-01403: no data found

91 rows selected. SQL> SQL> SQL> DROP TYPE OracleErrors; Type dropped. SQL>


 </source>