| user-defined function in package (IF THEN ELSE) [message #509037] |
Wed, 25 May 2011 12:43  |
 |
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
|
Senior Member |
|
|
My need is to find correct client type by its cid number. If it's individual (ct.officialtype = 3) then function returns 3. If it's not individual (ct.officialtype 3) then function should check ENTERPRISETYPE.id, 910 is small business (function should return 1), else it returns 2. You can find my try below, it returns error during package creating:
ORA-24344: success with compilation error
13/29 PLS-00201: identifier 'US1' must be declared
13/34 PL/SQL: ORA-00904: : invalid identifier
13/1 PL/SQL: SQL Statement ignored
17/4 PLS-00201: identifier 'US1' must be declared
17/1 PL/SQL: Statement ignored
9/13 PLS-00323: subprogram or cursor 'F_CLIENTTYPE' is declared in a package specification
and must be defined in the package body
It should be function, because I need to use it in SELECT statement. Could you please give me some advise? Thanks ahead.
CREATE OR REPLACE PACKAGE creator.marco_function_clienttype
IS
-- 0 - not found
-- 1 - small business
-- 2 - corporate
-- 3 - individual
FUNCTION f_clienttype
(cid number
)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY creator.marco_function_clienttype
IS
FUNCTION f_clienttype
(ccid number)
RETURN NUMBER
IS
Us NUMBER;
Begin
--officialtype = 2 - corporate, officialtype = 3 - individual
select ct.officialtype into Us1
from contragenttype ct, contragent d
where d.id = ccid and ct.cid = d.contragenttypeid;
---
IF Us1 = 3 THEN
select '3' as t into Us from dual;
ELSE
--ENTERPRISETYPE.id = 910 - small business
select dd.enterprisetypeid into Us2
from contragent dd
where dd.id = ccid ;
IF Us2 = 910 THEN
select '1' as t into Us from dual;
ELSE
select '2' as t into Us from dual;
END IF;
END IF;
Return nvl(Us,0);
END;
END;
/
[Updated on: Thu, 26 May 2011 01:06] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: user-defined function in package (IF THEN ELSE) [message #509044 is a reply to message #509038] |
Wed, 25 May 2011 13:28  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And use SQL*Plus then it comes better as there are line numbers and we can see them:
SQL> CREATE OR REPLACE PACKAGE marco_function_clienttype
2 IS
3
4 -- 0 - not found
5 -- 1 - small business
6 -- 2 - corporate
7 -- 3 - individual
8
9 FUNCTION f_clienttype
10 (cid number
11 )
12 RETURN NUMBER;
13 END;
14 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY marco_function_clienttype
2 IS
3
4 FUNCTION f_clienttype
5 (ccid number)
6 RETURN NUMBER
7 IS
8 Us NUMBER;
9
10
11 Begin
12 --officialtype = 2 - corporate, officialtype = 3 - individual
13 select ct.officialtype into Us1
14 from contragenttype ct, contragent d
15 where d.id = ccid and ct.cid = d.contragenttypeid;
16 ---
17 IF Us1 = 3 THEN
18 select '3' as t into Us from dual;
19 ELSE
20 --ENTERPRISETYPE.id = 910 - small business
21 select dd.enterprisetypeid into Us2
22 from contragent dd
23 where dd.id = ccid ;
24 IF Us2 = 910 THEN
25 select '1' as t into Us from dual;
26 ELSE
27 select '2' as t into Us from dual;
28 END IF;
29 END IF;
30
31 Return nvl(Us,0);
32
33 END;
34
35 END;
36 /
Warning: Package Body created with compilation errors.
SQL> sho err
Errors for PACKAGE BODY MARCO_FUNCTION_CLIENTTYPE:
LINE/COL
---------------------------------------------------------------------------------
ERROR
-------------------------------------------------------------------------------------
9/13
PLS-00323: subprogram or cursor 'F_CLIENTTYPE' is declared in a package specification
and must be defined in the package body
13/1
PL/SQL: SQL Statement ignored
14/25
PL/SQL: ORA-00942: table or view does not exist
17/1
PL/SQL: Statement ignored
17/4
PLS-00201: identifier 'US1' must be declared
In addition:
IF Us2 = 910 THEN
select '1' as t into Us from dual;
ELSE
select '2' as t into Us from dual;
END IF;
Why using SQL and DUAL to set a variable? Don't you know ":="?
Regards
Michel
[Updated on: Thu, 26 May 2011 01:08] Report message to a moderator |
|
|
|
|
|
|
|
| Re: user-defined function in package (IF THEN ELSE) [message #509056 is a reply to message #509052] |
Wed, 25 May 2011 14:09 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:used pure SQL before
Why don't you use it here?
SELECT nvl(decode(ct.officialtype,
3, 3,
decode(dd.enterprisetypeid,
910, 2,
3))
,0)
INTO us
FROM contragenttype ct inner join contragent d on d.ID = ccid AND ct.cid = d.contragenttypeid
left outer join on contragent dd on dd.ID = ccid
/
I have no test case to verify the syntax but you see what I mean.
It is not exactly equivalent as if you have no result in the second query your function raises an error when mine return 0.
You can get back your behaviour by removing the NVL and raising the error by yourself if the query returns NULL.
Regards
Michel
|
|
|
|
| Re: user-defined function in package (IF THEN ELSE) [message #509066 is a reply to message #509052] |
Wed, 25 May 2011 15:13 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
ecivgamer wrote on Wed, 25 May 2011 14:42 Thanks Michel, I work with PL/SQL the second month, used pure SQL before. Here is my solution, everything works just fine:
Everything runs fine, or just compiles fine?
Quote:
SELECT ct.officialtype
INTO us1
FROM contragenttype ct, contragent d
WHERE d.ID = ccid
AND ct.cid = d.contragenttypeid;
/
At a minimum (depending on your data), that is a cartesian join, with at least TOO_MANY_ROWS and NO_DATA_FOUND needed.
[Updated on: Wed, 25 May 2011 15:19] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
