As far as I am aware there is no disadvantage to using cursors inside oracle stored procedures or functions. In fact, the cursors can be cached. I don't use mysql so cannot comment on that.

You can use dynamic sql in oracle stored procedures but it is generally not such a good idea as oracle cannot know the sql you will be running so optimization of it prior to running it is less likely. Our project uses no dynamic sql at all (we have gone out of our way to avoid it). If a procedure/function which generates and returns a cursor can take optional arguments (e.g., ones we we use in the SQL) then we default them to NULL and ensure the SQL does not perform the comparison on the column if it is NULL e.g., (where (param is NULL) or (param is not null and param = column).

I don't really see any cons to using procedures in oracle and for a) speed and optimization, b) keeping logic in the database near the actual schema and data it is unmatched. However, if your only option is building dynamic sql then this is easily done in Perl.

In reply to Re^4: arranging code that works with SQL databases in Perl by mje
in thread arranging code that works with SQL databases in Perl by metaperl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.