Stored procedures in Oracle databases (11g)

Question

I've been struggling with this for a while.

I want to create 3 different stored procedures but i am not very sure about the syntax and how to execute them in Oracle database (version 11g)

My table contains these columns, this is the definition:

id NUMBER

amount VARCHAR2(30)

date VARCHAR2(30)

I want to create a procedure that allows me to fecth all the rows from a single table, this is what i did:

create or replace PROCEDURE GetAllRows(cursor_ OUT SYS_REFCURSOR) AS
BEGIN
OPEN cursor_ FOR
    SELECT *
      FROM My_Table  
END GetAllRows;   

Is it okay? How should i execute it?

---

Also , i'd like to INSERT some information to that table, so i created this another SP:

CREATE PROCEDURE insert_row(
                    id IN NUMBER,
                    amount IN VARCHAR,
                    date IN VARCHAR

                        )
IS
BEGIN

INSERT INTO My_table(id, amount, id) VALUES (id, amount, date);

END insert_row;

Is it okay? how should i execute it?

I'm kinda lost with Oracle databases.

Answer 1

Please try with the below code to get stored procedure output.

variable rc refcursor;
exec GetAllRows( :rc );
print rc;

Answer 2

It's hard to talk about 'style' or 'well written' without being able to give an example, and the framework of SO only allows that in an 'answer' not a 'comment'. So I'm going to stretch the meaning of 'answer' and show you how I would have coded it:

CREATE PROCEDURE insert_row(p_descriptive_id IN NUMBER,
                            p_descriptive_amount IN VARCHAR,
                            p_descriptive_date IN VARCHAR
                            )
IS
BEGIN

   INSERT INTO My_table(descriptive_id, 
                        descriptive_amount, 
                        descriptive_date) 
                 VALUES (p_descriptive_id, 
                         p_descriptive_amount, 
                         p__descriptive_date);

END insert_row;

Comments:

1. all field and column names are 'descriptive' - adjective_noun format
2. all parms begin with 'p_', to differentiate bet. parms and columns. Some people like a prefix that indicates if it is input or output parm (i_descriptive_input, o_descriptive_output.
3. In your INSERT line the column names (and values) in a column, not strung out on a single line. Basic rule, comma-delimited lists should line-break on the comma so the list is in a column. Easier to read and take in what's there. Especially on an INSERT when you need to make sure your table columns and values match.