Help with stored proc 1

[Glowworm27]

Hello gurus,

I am a Microsoft SQL guy, and need to write an App using an Oracle DB, for the most part all has been going well.

my problem:
I need to write a stored proc to either insert a record into the employee_master table if one DOES NOT exist, or update an Existing Record.

I could write this in SQL no problem, so I figured it would be close, but I am having some syntactical errors, that I am not familiar with when it comes to Oracle.

Could someone check this proc and tell me what I am missing or explain it to me? thanks

CREATE OR REPLACE  PROCEDURE "SYSTEM"."SPINSUPEMPMASTREC" (
 oUniversalID IN Number
 , oCompanyCode IN Number
 , oLastName In Varchar2
 , oFirstName In Varchar2
 , oMiddleInit In Varchar2
 , oSSN In Varchar2
 , oHireDate In Date
)
as
begin
 declare UnivID Number
 
 Set UnivID = (Select Count(*) from Employee_Master Where Universal_ID = oUniversalID);
 
 if UnivID > 0 
    Begin
     Update Employee_Master
     Set Company_Code = oCompanyCode
     , Last_Name = oLastName
     , First_Name = oFirstName
     , Middle_Init = oMiddleInit
     , SSN = oSSN
     , Hire_Date = oHireDate
     where Universal_ID = oUniversalID
    End
    Else
    Begin
     Insert Into Employee_Master Values(oUniversalID, OCompanyCode, oLastName, oFirstName
     , oMiddleInit, oSSN, oHireDate)
    End
end;

Thanks again in advance!!!!

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[gunjan14]

Hi George,

Here is the code that will do what you need to do. I have made bold to denote where and how the code is different. Also given some comments as to how it is different. I recommend that you read the Oracle PL/SQL guide for the complete understanding and syntax of PL/SQL

create or replace  procedure system.spinsupempmastrec 
(
    ouniversalid in number, 
    ocompanycode in number, 
    olastname    in varchar2, 
    ofirstname   in varchar2, 
    omiddleinit  in varchar2, 
    ossn         in varchar2, 
    ohiredate    in date
)
as
    -- Need to have semicolon after each statement
    -- No declare for variable declaration

    [B]univid number;[/B]
begin
    
    -- You have to select into a variable
    -- and not set the variable

    select count(*) [B]into univid[/B]
    from   employee_master 
    where  universal_id = ouniversalid;

    -- need to use THEN with IF
    -- Syntax is IF-THEN-ELSE-END IF

    if univid > 0 [B]then[/B]
        update employee_master
        set    company_code = ocompanycode, 
               last_name = olastname, 
               first_name = ofirstname, 
               middle_init = omiddleinit, 
               ssn = ossn, 
               hire_date = ohiredate
        where  universal_id = ouniversalid[B];[/B]
    else
        insert into employee_master values
        (
            ouniversalid, 
            ocompanycode, 
            olastname, 
            ofirstname, 
            omiddleinit, 
            ossn, 
            ohiredate
        )[B];[/B]
     
    -- Need to have END IF to denote the 
    -- end of IF-THEN-ELSE
    [b]end if;[/b]
end;
/

Anyway you are making this procedure in system user. Is there any special reason for this?

Do let me know if this helps.

Regards,
Gunjan

 

[sem]

create or replace  procedure system.spinsupempmastrec 
(
    ouniversalid in number, 
    ocompanycode in number, 
    olastname    in varchar2, 
    ofirstname   in varchar2, 
    omiddleinit  in varchar2, 
    ossn         in varchar2, 
    ohiredate    in date
)
as
begin
  update employee_master
        set    company_code = ocompanycode, 
               last_name = olastname, 
               first_name = ofirstname, 
               middle_init = omiddleinit, 
               ssn = ossn, 
               hire_date = ohiredate
        where  universal_id = ouniversalid;
  if sql%rowcount=0 then
        insert into employee_master values
        (
            ouniversalid, 
            ocompanycode, 
            olastname, 
            ofirstname, 
            omiddleinit, 
            ossn, 
            ohiredate
        );
  end if;
end;

You may also use MERGE command.

Regards, Dima

http://seminihin.narod.ru

 

[Glowworm27]

Gunjan,

Thanks for the help, and the comments are great. will post a star for you in a second.

Next I am using system, just for testing, its not the production environment. As I said I am a Microsoft SQL guy, and not to familiar with Oracle stuff. There will be a DBA that will handle all the production envionment stuff, and will be responsible for moving the code over to the production schema , once it is created.

But until then, its just easy for me to work with what is available, until the DBA, and the production environment are in place.

I don't have access to the DBA on a daily basis which is the reason for my post here in this forum.

In your reply you mentiond a Guide: Oracle PL/SQL guide for the complete understanding and syntax of PL/SQL. IS this something I may have available freely or is this a book we must purchase. I don't mind purchasing books provided they are a good source of reference. Where may I obtain/purchase a copy of this guide?

Thanks for all your help!!!!

Regards
George

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

Thanks Gunjan
It worked like a charm! Star for you.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[gunjan14]

Hi George,

There are plenty of good books and on-line materials for Oracle PL/SQL. Here is one

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm

or you can go to

http://tahiti.oracle.com

and get all the documentation of Oracle.

I do not know about other books but I have read O'Reilly - Oracle PL/SQL

Hope this helps

Regards,
Gunjan