CODEWITHSUNDEEP
oracle-databaseplsql
user9816779
user9816779

Reputation:

How to create PL/SQL function in package

How to create PL/SQL function get_amount which returns acc_amount for acc_id, and function get_date which returns acc_date for acc_id.

Here you have package and package body, how I make last two functions.

This I work like exercise.

I think my other code is fine.

I'm beginner in Oracle and pl/sql, maybe my question is stupid

I am sorry if i did not explain well, My english is not very good.

Thank you all for the answer!

CREATE OR REPLACE PACKAGE account_api AS

PROCEDURE add_new_account
    (
      p_acc_id     accounts.acc_id%type
    , p_acc_name   accounts.acc_name%type
    , p_acc_amount accounts.acc_amount%type
    , p_acc_date   accounts.acc_date%type
);

PROCEDURE upd_account   
    (
      p_acc_name   accounts.acc_name%type
    , p_acc_amount accounts.acc_amount%type
    , p_acc_date   accounts.acc_date%type
);

PROCEDURE del_accounts
    (
      p_acc_id     accounts.acc_id%type
);

FUNCTION get_amount 
    (p_acc_id      accounts.acc_id%type)
    RETURN number;

FUNCTION get_date 
    (p_acc_date    accounts.acc_date%type)
    RETURN date;  

end account_api;
/

CREATE OR REPLACE PACKAGE BODY account_api AS

PROCEDURE add_new_account
    (
      p_acc_id     accounts.acc_id%type
    , p_acc_name   accounts.acc_name%type
    , p_acc_amount accounts.acc_amount%type
    , p_acc_date   accounts.acc_date%type
)
IS
BEGIN
    INSERT INTO account (acc_id, acc_name, acc_amount, acc_date)
    VALUES (acc_seq.nextval, p_acc_id, p_acc_name, p_acc_amount, p_acc_date)
END;

PROCEDURE upd_account
    (
      p_acc_name   accounts.acc_name%type
    , p_acc_amount accounts.acc_name%type
    , p_acc_date   accounts.acc_name%type
)
IS
BEGIN
    UPDATE accounts
        SET acc_naziv  = p_acc_naziv
          , acc_amount = p_acc_amount
          , acc_date   = p_acc_date
    WHERE ---------------------------------
COMMIT;
END;

PROCEDURE del_accounts
    (
      p_acc_id     accounts.acc_id%type
)

DELETE FROM accounts WHERE acc_id = p_acc_id;
COMMIT;


FUNCTION get_amount
    (
      p_acc_id    accounts.acc_id%type
)



FUNCTION get_date
    (
       p_acc_id    accounts.acc_id%type
)

Upvotes: 2

Views: 3015

Answers (2)

skarakas
skarakas

Reputation: 154

If you want to return a cursor try this one.

function get_amount(p_acc_id in number) return t_cursor is
    v_cursor t_cursor
Begin
open v_cursor for 
    select * from  account 
    where acc_id =p_acc_id; 
    return v_cursor;
End get_amount;

But you should define type t_cursor IS REF Cursor; in your package header.

Upvotes: 0

PKey
PKey

Reputation: 3841

Are you looking for something like this? 

FUNCTION get_amount(p_acc_id accounts.acc_id%type) return Number is
 res number;
begin 
 select acc_amount into res 
 from  account where acc_id =p_acc_id; 
 return res;
end;

You select the value into a variable and then return that variable.

Also, have in mind that the above code could cause an exception if no data is found (wrong p_acc_id is given)

I leave the second implementation to you.

Upvotes: 3

Related Questions