Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Column Selection from Table in a Function (10g, HP Unix)
Multiple Column Selection from Table in a Function [message #380112] |
Fri, 09 January 2009 03:33  |
shankhan
Messages: 28 Registered: September 2008 Location: Pakistan
|
Junior Member |
|
|
Dear Guys,
I have a table T1 with the following columns:
phone_number
country_code
country_name
city_name
Now I have a function get_country_code to which I pass a phone number and in return I get the "country_code" information.
I write an UPDATE statement on the table T1 using the function in the following manner.
UPDATE t1
SET country_code = get_country_code (phone_number).
This function has a query that FETCHES country_code information from a table that also has the country_name and city_name infomration as well.
Now what I want to do is to update the country_name and city_name information as well so that i don't have to write three seperate functions( get_country_code, get_country_name, get_city_name) because all this information get be selected only on one criteria.
Also the logic to calculate the information is such that i have to write that in the function.
Is there any way that I can avoid two extra functions and update country_name and city_name as well ?
[Updated on: Fri, 09 January 2009 03:37] Report message to a moderator
|
|
|
|
Re: Multiple Column Selection from Table in a Function [message #380132 is a reply to message #380112] |
Fri, 09 January 2009 04:21   |
shankhan
Messages: 28 Registered: September 2008 Location: Pakistan
|
Junior Member |
|
|
create table t1( phone_number varchar2(15), country_code varchar2(10),
country_name varchar2(100), city_name varchar2(100));
create table country_information(
country_code varchar2(10), country_name varchar2(100), city_name varchar2(100));
Insert into T1
(PHONE_NUMBER)
Values
('92423434343');
Insert into T1
(PHONE_NUMBER)
Values
('92513403074');
COMMIT;
Insert into COUNTRY_INFORMATION
(COUNTRY_CODE, COUNTRY_NAME, CITY_NAME)
Values
('9242', 'Pakistan', 'Lahore');
Insert into COUNTRY_INFORMATION
(COUNTRY_CODE, COUNTRY_NAME, CITY_NAME)
Values
('9251', 'Pakistan', 'Islamabad');
COMMIT;
/* Formatted on 2009/01/09 15:04 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION get_country_code (p_num VARCHAR2)
RETURN VARCHAR2
IS
v_country_code VARCHAR2 (10);
BEGIN
SELECT country_code
INTO v_country_code
FROM (SELECT country_code
FROM country_information,
(SELECT SUBSTR (p_num, 1, LEVEL) codestring,
LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= LENGTH (p_num))
WHERE country_code = codestring
ORDER BY lvl DESC)
WHERE ROWNUM = 1;
RETURN v_country_code;
END;
/
Using the above information i write the UPDATE statement as given below.
UPDATE t1
SET country_code = get_country_code (phone_number)
Now I have to write an UPDATE statement that will update country_name and city_name as well as given below.
UPDATE t1
SET country_code = get_country_code (phone_number),
country_name = get_country_name(phone_number),
city_name = get_city_name(phone_number)
The functions get_country_name and get_city_name are based on the same logic as the function get_country_code except that we select different columns in each function ( country_name, city_name ).
I want to avoid these two extra functions because they are based on the same logic and still update all the columns using single function that returns all the infomration( country_code, country_name, city_name).
I want an UPDATE like
UPDATE t1
SET (country_code, country_name, city_name) = (....)
[Updated on: Fri, 09 January 2009 04:27] Report message to a moderator
|
|
|
Re: Multiple Column Selection from Table in a Function [message #380138 is a reply to message #380132] |
Fri, 09 January 2009 04:39   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> create or replace type mytype as object (
2 country_code varchar2(10), country_name varchar2(100), city_name varchar2(100))
3 /
Type created.
SQL> create or replace type myarray as table of mytype
2 /
Type created.
SQL> CREATE OR REPLACE FUNCTION get_code (p_num VARCHAR2)
2 RETURN myarray
3 IS
4 v_code myarray := myarray();
5 BEGIN
6 v_code.extend;
7 SELECT mytype(country_code, country_name, city_name)
8 INTO v_code(1)
9 FROM (SELECT country_code, country_name, city_name
10 FROM country_information,
11 (SELECT SUBSTR (p_num, 1, LEVEL) codestring,
12 LEVEL lvl
13 FROM DUAL
14 CONNECT BY LEVEL <= LENGTH (p_num))
15 WHERE country_code = codestring
16 ORDER BY lvl DESC)
17 WHERE ROWNUM = 1;
18
19 RETURN v_code;
20 END;
21 /
Function created.
SQL> update t1 set (country_code, country_name, city_name) =
2 (select * from table(get_code(phone_number)))
3 /
2 rows updated.
SQL> select * from t1;
PHONE_NUMBER COUNTRY_CO COUNTRY_NAME CITY_NAME
--------------- ---------- -------------------- --------------------
92423434343 9242 Pakistan Lahore
92513403074 9251 Pakistan Islamabad
2 rows selected.
Regards
Michel
|
|
|
|
Re: Multiple Column Selection from Table in a Function [message #380158 is a reply to message #380140] |
Fri, 09 January 2009 06:02   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
Can try with this
SQL> COLUMN COUNTRY_CODE FORMAT A4
SQL> COLUMN COUNTRY_NAME FORMAT A12
SQL> COLUMN CITY_NAME FORMAT A12
SQL> COLUMN PHONE_NUNMBER FORMAT A12
SQL> SELECT * FROM t11
PHONE_NUMBER COUN COUNTRY_NAME CITY_NAME
--------------- ---- ------------ ------------
92423434343
92513403074
2 rows selected.
SQL> UPDATE t11 a
SET (country_code, country_name, city_name) =
(SELECT country_code, country_name, city_name
FROM country_information b
WHERE b.country_code = REGEXP_SUBSTR (phone_number, country_code))
2 rows updated.
SQL> SELECT * FROM t11
PHONE_NUMBER COUN COUNTRY_NAME CITY_NAME
--------------- ---- ------------ ------------
92423434343 9242 Pakistan Lahore
92513403074 9251 Pakistan Islamabad
2 rows selected.
Thanks
Trivendra
[Updated on: Fri, 09 January 2009 06:03] Report message to a moderator
|
|
|
|
Re: Multiple Column Selection from Table in a Function [message #380166 is a reply to message #380160] |
Fri, 09 January 2009 06:31   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
Thanks Michel,
As soon as I posted, I recognized as country_code can be match in any pattern of the phone_number.
I have done some changes( As I am not good in REGEXP).
may be this is correct
Inserted one more test case
Insert into T11
(PHONE_NUMBER)
Values
('91113449251');
SQL> COLUMN COUNTRY_CODE FORMAT A4
SQL> COLUMN COUNTRY_NAME FORMAT A12
SQL> COLUMN CITY_NAME FORMAT A12
SQL> COLUMN PHONE_NUNMBER FORMAT A12
SQL> SELECT *
FROM t11
PHONE_NUMBER COUN COUNTRY_NAME CITY_NAME
--------------- ---- ------------ ------------
92423434343
92513403074
91113449251
3 rows selected.
SQL> UPDATE t11 a
SET (country_code, country_name, city_name) =
(SELECT country_code, country_name, city_name
FROM country_information b
WHERE b.country_code =
DECODE (INSTR (phone_number, country_code),
1, REGEXP_SUBSTR (phone_number, country_code)
))
3 rows updated.
SQL> SELECT *
FROM t11
PHONE_NUMBER COUN COUNTRY_NAME CITY_NAME
--------------- ---- ------------ ------------
92423434343 9242 Pakistan Lahore
92513403074 9251 Pakistan Islamabad
91113449251
3 rows selected.
I hope Michel you will provide better example for this.
Thanks and Regards
Trivendra
[Updated on: Fri, 09 January 2009 06:34] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 17:33:28 CST 2025
|