Home » SQL & PL/SQL » SQL & PL/SQL » Question on user defined functions
icon5.gif  Question on user defined functions [message #280810] Wed, 14 November 2007 17:53 Go to next message
jihaes
Messages: 4
Registered: November 2007
Junior Member
Question1)
I tried to learn the syntax for Create function and I'm getting few error here what's different between oracle sql and sql server?

( Country varchar )
return varchar
as
begin
declare Return varchar
select return = case Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
else 'Unknown'
end;
return @return;
end;


Question 2)
If I have multiple functions, in SQL Server, I can look up the system tables like sys.sysobjects to find all names by type=FN. Where can I look up in oracle database to grab all the function names so that when I want to delete all the user defined functions, I can just drop all by those names?


Thanks!
Re: Question on user defined functions [message #280812 is a reply to message #280810] Wed, 14 November 2007 20:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at Chapter 1 of the Oracle PL/SQL manual: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref66

and also the 11g tutorial: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#CIHDFDJG

Query the data dictionary view USER_OBJECTS to see all of the objects owned by you. Filter on OBJECT_TYPE = 'FUNCTION' to get just the functions.

Ross Leishman
Re: Question on user defined functions [message #280872 is a reply to message #280810] Thu, 15 November 2007 01:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your function would probably be:
CREATE OR REPLACE FUNCTION get_country ( p_country IN varchar )
return varchar
as
  v_return  varchar2(<put max size of country field here>);
begin
  v_return := case p_country
                   when 'Argentina' then 'South America'
                   when 'Belgium' then 'Europe'
                   else 'Unknown'
                   end;
return v_return;
end get_country;

or, if you want to simplify it a bit:
CREATE OR REPLACE FUNCTION get_country ( p_country IN varchar )
return varchar
as
begin
  return case p_country
              when 'Argentina' then 'South America'
              when 'Belgium' then 'Europe'
              else 'Unknown'
              end;
end get_country;
Re: Question on user defined functions [message #280935 is a reply to message #280810] Thu, 15 November 2007 04:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

For Question 2 ,

Refer Data Dictionaries USER_OBJECTS ,ALL_OBJECTS with OBJECT_TYPE='FUNCTION'

Thumbs Up
Rajuvan
Re: Question on user defined functions [message #280942 is a reply to message #280935] Thu, 15 November 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what Ross said?

Regards
Michel
Re: Question on user defined functions [message #281061 is a reply to message #280872] Thu, 15 November 2007 10:19 Go to previous messageGo to next message
jihaes
Messages: 4
Registered: November 2007
Junior Member
Thanks you guys. Both my questions were answered.
Re: Question on user defined functions [message #281063 is a reply to message #280810] Thu, 15 November 2007 10:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ michel :.

I didn't notice Ross's Reply for Question 2

Thumbs Up
Rajuvan
Re: Question on user defined functions [message #281126 is a reply to message #280810] Thu, 15 November 2007 16:25 Go to previous messageGo to next message
jihaes
Messages: 4
Registered: November 2007
Junior Member
Another question...

What's the syntax for returning a TABLE?

In sql server, you can say something like...

CREATE FUNCTION LookByFName(@FirstLetter char(1))
RETURNS TABLE
AS
RETURN SELECT *
FROM employee
WHERE LEFT(fname, 1) = @FirstLetter

Re: Question on user defined functions [message #281127 is a reply to message #281126] Thu, 15 November 2007 17:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION LookByFName
  2    (p_FirstLetters IN VARCHAR2)
  3    RETURN SYS_REFCURSOR
  4  AS
  5    v_results  SYS_REFCURSOR;
  6  BEGIN
  7    OPEN v_results FOR
  8    SELECT *
  9    FROM   emp
 10    WHERE  ename LIKE p_FirstLetters || '%';
 11    RETURN v_results;
 12  END LookByFName;
 13  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_results REFCURSOR
SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> EXECUTE :g_results := LookByFName ('M')

PL/SQL procedure successfully completed.


     EMPNO ENAME      JOB              MGR HIREDATE                      SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------------------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28/09/1981 12:00:00 AM       1250       1400         30
      7934 MILLER     CLERK           7782 23/01/1982 12:00:00 AM       1300                    10

SCOTT@orcl_11g> EXECUTE :g_results := LookByFName ('MA')

PL/SQL procedure successfully completed.


     EMPNO ENAME      JOB              MGR HIREDATE                      SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------------------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28/09/1981 12:00:00 AM       1250       1400         30

SCOTT@orcl_11g> spool off

Re: Question on user defined functions [message #281157 is a reply to message #281126] Thu, 15 November 2007 22:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Word of warning here (not related to any of the answers given to your questions). A very common mistake made by SQLServer-coders working in Oracle is to stick to the habit of creating temporary tables at runtime. DON'T do that in Oracle!
In Oracle, you should try to do whatever you do in a single SQL statement. Don't worry about numbers of joins or complexity of the SQL. Oracle can handle it.
If you have DDL-statements in your regular production code in Oracle, 99.99% chance you have a flaw in your design.
Re: Question on user defined functions [message #281425 is a reply to message #281127] Fri, 16 November 2007 16:27 Go to previous message
jihaes
Messages: 4
Registered: November 2007
Junior Member
Thanks. I'll do some research on the SYS_REFCURSOR
Previous Topic: How to eliminate illegal characters in SQL
Next Topic: Username length limitation in Oracle
Goto Forum:
  


Current Time: Sat Dec 03 10:15:11 CST 2016

Total time taken to generate the page: 0.17955 seconds