1

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.

Barbaros Özhan
  • 39,060
  • 9
  • 21
  • 45
programming_amazing
  • 357
  • 8
  • 1
    Regarding the first example, we can use views for stored queries. You can have procedures that return cursors but you'll need to write code to handle the cursor until Oracle 12.1 (see https://stackoverflow.com/a/40360471/230471). It's not worth using a procedure for this kind of thing unless the query is complex and dynamic. – William Robertson Feb 20 '21 at 10:01

2 Answers2

2

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

variable rc refcursor;
exec GetAllRows( :rc );
print rc;
Hiren Jasani
  • 327
  • 1
  • 10
  • Hey pal, thanks for replying I do not understand. Are my SPs well written? – programming_amazing Feb 19 '21 at 17:41
  • Yes, Both SP are ok – Hiren Jasani Feb 19 '21 at 17:44
  • Could you please tell me how to execute the SP within the insert statement? – programming_amazing Feb 19 '21 at 17:45
  • Please refer to below link for the stored procedure execution EXECUTE procedure_name( arguments); https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/ – Hiren Jasani Feb 19 '21 at 17:49
  • Just an FYI note, not on the SPs themselves, but on the data types. ID number - ok. Amount varchar2 - appears to be some thing you might want to do math with so better as number (with or without precision, scale. Date varchar2 - this is a bug. You can get away with it, but it will eventually cause problems, 3 issues. First off Oracle has data types for holding date information. . Dates should always be date or timestamp never varchar2. Second "date" is a reserved word so not a good name. – Belayer Feb 19 '21 at 18:49
  • 1
    @Belayer - you beat me to it. I'd also add that starting or ending a name with underscore ('cursor_') is probably not a great idea either. Technically it will work, but I'd be concerned that some human maintaining the code would mis-read/mis-type it. Save the underscore for joining two words. – EdStevens Feb 19 '21 at 19:44
  • Even for the column/variable names that "work" for oracle, I'd suggest the OP think carefully about a coherent naming standard. _ALL_ of my column names are in the format _ ie: invoice_id, purchase_amount, billing_date. All procedure parms begin with 'p_' ie: p_emp_name. All variables begin with v_ ie: v_emp_name. – EdStevens Feb 19 '21 at 19:49
  • @EdStevens Yep you are absolutely correct. I do not use the p_ conversion but a different convention doing the same.But the important part is "It is **always** the same. – Belayer Feb 19 '21 at 19:52
  • @Belayer Yeah, it was just an example :) (I was not using the correct data type). Guys, would you change anything related to the structure of my SPs? – programming_amazing Feb 19 '21 at 20:02
  • @EdStevens what about you, pal? – programming_amazing Feb 19 '21 at 20:02
  • 1
    Nit picking. Naming format consistency. Never use CamelCase (oracle will spit back CAMELCASE) making it hard to read. And purely personal preference I like specific routine names so maybe "get_mytable_rows" and "insert_mytable_row". And I write everything in lower case (many will disagree with that). The important thing is not how you write (within reason). But **3 very important** concepts: 1, Names are important choose them well and with thought. 2. Code must be easy to read, if it is not it cannot be understand. 3. Choose your style, and then ALWAYS be consistent with it. – Belayer Feb 19 '21 at 20:26
  • Just another thought that applies to all above but especially #3. if your client or employer has a specified standards then you are professionally obligated to follow those standards, whether you like them or not. – Belayer Feb 19 '21 at 20:33
  • Re: naming standards and coding style - don't think of _just_ yourself. Think about the guy who has to maintain your code 2 years after you've moved. Or yourself 6months from now! I'm a huge fan of shop-wide standards. Have served on committees to develop such. Another point - standardize common abbreviations, and always use them. If the standard is 'nbr' for 'number', then _always_ use 'nbr', never 'num' or 'number'. Doesn't matter which, just pick one and _always_ use it. Another example: 'amt' or 'amnt' or 'amount'. PIck on and stick with it. – EdStevens Feb 20 '21 at 00:34
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.
EdStevens
  • 2,850
  • 2
  • 7
  • 15
  • Thank you very much for replying, it means a look, pal. Can i ask you something? What would you do to SELECT all tuples or some columns in your SP? Would it be similar to the one i created?. is there exist a different way to create it in Oracle DATABASE (11g version)? – programming_amazing Feb 20 '21 at 00:55
  • Depends on what you are going to do with them. One thing for sure, except for ad hoc queries, you should _not_ simply 'select * from my_table'. Such a statement could break your code if the table structure were to change. Presumably, in production code you know which columns you need -- so list them: 'select emp_id, first_name, last_name from employees'. – EdStevens Feb 20 '21 at 13:47