On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, June 23, 2010

11g: Mixed Name + Positional Notations

Changes with 11g:
  • you can mix named with positional parameters in a call.  
  • the SQL code matches the PL/SQL code.
Notes:
  • Exclusionary Notation: one or more can be excluded if optional. (When formal parameters have defaults they are optional.)
  • You still can't call a function from SQL when any of its formal parameters are defined as IN OUT or OUT mode–only variables. 
  • Mixed must list all positional parameters first.
CREATE OR REPLACE FUNCTION Family
     ( Boy Varchar2  := 'Mickey'
     , Girl Varchar2 := 'Minney'
     , Dog Varchar2  := 'Pluto' ) 
                       RETURN Varchar2 IS
BEGIN
     RETURN Boy||'+'||Girl||'+'||Dog;
END;
/

Column Notation Format A15
Column Family   Format A15

Select 'Positional'           As Notation,
       Family('Dick'
             ,'Jane'
             ,'Spot')         As Family
From Dual;

Select 'Named'                As Notation,
       Family(Girl => 'Jane'
             ,Dog  => 'Spot'
             ,Boy  => 'Dick') As Family
From Dual;

Select 'Mixed'                As Notation,
       Family(        'Dick'
             ,Dog  => 'Spot'
             ,Girl => 'Jane') As Family
From Dual;

Select 'Exclusionary'         As Notation,
       Family(        'Dick'
             ,Girl => 'Jane') As Family
From Dual;

No comments:

Post a Comment