Need help creating a procedure / function for repeatedly used SQL

Posted on 2012-08-22
Last Modified: 2012-08-22
I have a practice table called KEYBOARD_LEARNING  that I use as a log for my latest word per minute scores. I use this code so much I figured it's time to make a procedure or function or package. I'm honestly not sure which is best.

What remains static is emplid. All other columns are dynamic and will be different each time I use this insert to make a new row in my table

INSERT INTO keyboard_learning (emplid,wpm, date_completed,exercise,attempt,score_lvl)
VALUES ('000000000','67.66','14-AUG-2012','','28','95.87% accuracy')

Open in new window

What is the best way to go about encapsulating this SQL? What would the code be?
Thank you
Question by:Mark_Co
    LVL 76

    Accepted Solution

    You really only need a function/procedure to mask the business logic.

    Untested but try the following.  You might need to adjust the data types to match your table.

    create or replace procedure insert_keyboard_learning(
    v_wpm in varchar2,
    v_date_completed in varchar2,
    v_exercise in varchar2,
    v_attempt in varchar2,
    v_score_lvl in varchar2)
    INSERT INTO keyboard_learning (emplid,wpm, date_completed,exercise,attempt,score_lvl)
    VALUES ('000000000',v_wpm, v_date_completed,v_exercise,v_attempt,v_score_lvl);

    show errors

    Author Comment

    Thank you slightwv. I will try this out as soon as I get to back to the office

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now