Home » SQL & PL/SQL » SQL & PL/SQL » Need Help on Creating FUNCTION
Need Help on Creating FUNCTION [message #670520] Tue, 10 July 2018 09:17 Go to next message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
Hi All,

I'm new to PL/SQL and trying to create a function that will be used for updating some columns.

Please let me know if this correct or should we write it in a different way.

/*
FUNCTION Get_updated_recs(p_class_id)

RETURN SUB_REC_TYPE
IS
p_sub_rec SUB_REC_TYPE;
BEGIN
IF p_class_id = 'Business'
THEN
p_sub_rec.Business_General := 'x';
END IF;

BEGIN
IF p_class_id='The_Arts'
THEN
p_sub_rec.Arts_general='x';
END IF;

BEGIN
IF p_class_id='ProQuest Catalog news'
THEN
p_sub_rec.All Products='x';
END IF;

BEGIN
IF p_class_id='General,K12&Public Library'
THEN
p_sub_rec.K12&Public Library General='x'
END IF;

RETURN p_sub_rec;
END get_updated_recs; */


Thanks for your help.
Re: Need Help on Creating FUNCTION [message #670521 is a reply to message #670520] Tue, 10 July 2018 09:38 Go to previous messageGo to next message
Bill B
Messages: 1752
Registered: December 2004
Senior Member
What is the type SUB_REC_TYPE defined as and p_sub_rec.K12&Public Library General is not a valid column name. Only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). all other characters are illegal. so if we assume the variable is actually K12_Public_Library_General then the following code will work.
CREATE OR REPLACE FUNCTION Get_updated_recs(P_class_id IN VARCHAR2)
    RETURN Sub_rec_type
IS
    P_sub_rec   Sub_rec_type;
BEGIN
    CASE P_class_id
        WHEN 'Business' THEN
            P_sub_rec.Business_general := 'x';
        WHEN 'The_Arts' THEN
            P_sub_rec.Arts_general := 'x';
        WHEN 'ProQuest Catalog news' THEN
            P_sub_rec.All_products := 'x';
        WHEN 'General,K12&Public Library' THEN
            P_sub_rec.K12_public_library_general := 'x';
    END CASE;

    RETURN P_sub_rec;
END Get_updated_recs;

[Updated on: Tue, 10 July 2018 09:54]

Report message to a moderator

Re: Need Help on Creating FUNCTION [message #670522 is a reply to message #670521] Tue, 10 July 2018 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13168
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not something I would generally consider doing, but without knowing what other process is going to make use of this code it's not really possible to suggest alternatives.
Re: Need Help on Creating FUNCTION [message #670523 is a reply to message #670520] Tue, 10 July 2018 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26061
Registered: January 2009
Location: SoCal
Senior Member
vharish006 wrote on Tue, 10 July 2018 07:17
Hi All,

I'm new to PL/SQL and trying to create a function that will be used for updating some columns.

Please let me know if this correct or should we write it in a different way.

/*
FUNCTION Get_updated_recs(p_class_id)

RETURN SUB_REC_TYPE
IS
p_sub_rec SUB_REC_TYPE;
BEGIN
IF p_class_id = 'Business'
THEN
p_sub_rec.Business_General := 'x';
END IF;

BEGIN
IF p_class_id='The_Arts'
THEN
p_sub_rec.Arts_general='x';
END IF;

BEGIN
IF p_class_id='ProQuest Catalog news'
THEN
p_sub_rec.All Products='x';
END IF;

BEGIN
IF p_class_id='General,K12&Public Library'
THEN
p_sub_rec.K12&Public Library General='x'
END IF;

RETURN p_sub_rec;
END get_updated_recs; */


Thanks for your help.
Above is flawed code.
Never do in PL/SQL that which can be done in plain SQL.

>p_sub_rec.K12&Public Library General='x'
not as above but as below
p_sub_rec.K12&Public Library General := 'x';
Re: Need Help on Creating FUNCTION [message #670524 is a reply to message #670521] Tue, 10 July 2018 09:48 Go to previous messageGo to next message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
Hi Bill,

SUB_REC_TYPE is VARCHAR
Re: Need Help on Creating FUNCTION [message #670525 is a reply to message #670523] Tue, 10 July 2018 09:50 Go to previous messageGo to next message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
Hi Black Swan,

Thanks for your response.

You mean to write it in form of Update Statement??Please let know

But this might be a ongoing process so I thought I should create a function and use it

Thanks

[Updated on: Tue, 10 July 2018 09:51]

Report message to a moderator

Re: Need Help on Creating FUNCTION [message #670526 is a reply to message #670522] Tue, 10 July 2018 09:53 Go to previous messageGo to next message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
The other process updates just column the column with x on a different column based on this condition.

e.g: if Business has the value 'x' then it needs to be translated to Business General for these columns
Re: Need Help on Creating FUNCTION [message #670527 is a reply to message #670524] Tue, 10 July 2018 09:58 Go to previous messageGo to next message
cookiemonster
Messages: 13168
Registered: September 2008
Location: Rainy Manchester
Senior Member
vharish006 wrote on Tue, 10 July 2018 15:48
Hi Bill,

SUB_REC_TYPE is VARCHAR
No it's not - it's some form of record.
The record may contain individual items that are varchar (hopefully varchar2) but the record itself isn't varchar.
Re: Need Help on Creating FUNCTION [message #670528 is a reply to message #670525] Tue, 10 July 2018 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 26061
Registered: January 2009
Location: SoCal
Senior Member
only plain SQL can interact with data within the database.
SQL has its own "engine" to process SQL statements.
PL/SQL has a different "engine" to process PL/SQL code.
A context switch is required when going from SQL to PL/SQL and going from PL/SQL to SQL.
This is a resource intensive operation.
https://en.wikipedia.org/wiki/Context_switch
This "design" results in multiple context switch for every row that is processed & likely does not scale well.
Re: Need Help on Creating FUNCTION [message #670529 is a reply to message #670525] Tue, 10 July 2018 10:01 Go to previous messageGo to next message
cookiemonster
Messages: 13168
Registered: September 2008
Location: Rainy Manchester
Senior Member
vharish006 wrote on Tue, 10 July 2018 15:50
Hi Black Swan,

Thanks for your response.

You mean to write it in form of Update Statement??Please let know

But this might be a ongoing process so I thought I should create a function and use it

Thanks
BS means you need to use the PL/SQL assignment operator (:=) to assign values to PL/SQL variables not the equality operator (=).
you got it right in the first IF but wrong in all the others.

That code is an utter uncompilable mess - Bill has pointed out some other issues as well.
Re: Need Help on Creating FUNCTION [message #670530 is a reply to message #670526] Tue, 10 July 2018 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13168
Registered: September 2008
Location: Rainy Manchester
Senior Member
vharish006 wrote on Tue, 10 July 2018 15:53
The other process updates just column the column with x on a different column based on this condition.

e.g: if Business has the value 'x' then it needs to be translated to Business General for these columns
That's vague - are you talking about updating columns in a table?
Where does the class_id come from?
Why are you using record types?

What is the actual code you are trying to plug this in to?
Re: Need Help on Creating FUNCTION [message #670533 is a reply to message #670530] Tue, 10 July 2018 13:34 Go to previous messageGo to next message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
That's vague - are you talking about updating columns in a table?
Well my requirement is to update a column in a table

Where does the class_id come from?
From a file.


Why are you using record types?
Not really sure.I got some example on google and trying to use that in similar fashion Sad
Re: Need Help on Creating FUNCTION [message #670535 is a reply to message #670533] Tue, 10 July 2018 13:55 Go to previous messageGo to next message
BlackSwan
Messages: 26061
Registered: January 2009
Location: SoCal
Senior Member
vharish006 wrote on Tue, 10 July 2018 11:34


Why are you using record types?
Not really sure.I got some example on google and trying to use that in similar fashion Sad
Realize that record type is a PL/SQL object & therefore can NOT be used is any plain SQL statement.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Please post working test case & show us & explain the desired & expected results
Re: Need Help on Creating FUNCTION [message #670545 is a reply to message #670533] Wed, 11 July 2018 03:43 Go to previous message
cookiemonster
Messages: 13168
Registered: September 2008
Location: Rainy Manchester
Senior Member
vharish006 wrote on Tue, 10 July 2018 19:34
That's vague - are you talking about updating columns in a table?
Well my requirement is to update a column in a table
A column? Or multiple columns?
Cause if it's a single column then why is that function setting multiple different items in a record type.

vharish006 wrote on Tue, 10 July 2018 19:34

Where does the class_id come from?
From a file.
And how are you getting the value out of the file?
Previous Topic: SELECT QUERY
Next Topic: ORA-04091: table Table_Name is mutating, trigger/function may not see it
Goto Forum:
  


Current Time: Wed Jul 18 00:19:32 CDT 2018