Author Topic: Oracle/PLSQL: Creating Procedures  (Read 124 times)

0 Members and 1 Guest are viewing this topic.

Offline MrSpecialist

  • Sr. Member
  • *
  • Posts: 342
  • Karma: +1/-0
  • I'm an expert!
    • View Profile
Oracle/PLSQL: Creating Procedures
« on: October 19, 2008, 02:40:48 PM »
In Oracle, you can create your own procedures.

The syntax for a procedure is:

Code: [Select]
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];


When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a procedure:

Code: [Select]
CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )
IS
    cnumber number;

    cursor c1 is
    select course_number
      from courses_tbl
      where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
     cnumber := 9999;
end if;

insert into student_courses
( course_name,
  course_number)
values ( name_in,
                cnumber );

commit;

close c1;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,\'An error was encountered - \'||SQLCODE||\' -ERROR- \'||SQLERRM);
END;


This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.

Techronnati | where technology never sleeps

Oracle/PLSQL: Creating Procedures
« on: October 19, 2008, 02:40:48 PM »

Mountain View

Offline Corps

  • Sr. Member
  • *
  • Posts: 273
  • Karma: +0/-0
    • View Profile
Re: Oracle/PLSQL: Creating Procedures
« Reply #1 on: November 01, 2008, 07:44:53 AM »
I have sample code here for the benefit of others learning about Oracle/PLSQL

Code: [Select]
CREATE OR REPLACE PROCEDURE MIS.UPLOAD_YEARLY_LOANS_GRANTS  
   IS
   v_approval_no VARCHAR2(20);
   v_start_yr    int;
   v_orig_yr    int;
   v_rev_yr    int;
   n_curr_yr    int;
   n_end_yr    int;
       
   CURSOR c_portfolio IS
    SELECT approval_no, to_char(approval_dt,\'YYYY\'), to_char(closing_orig_dt,\'YYYY\'),
    to_char(closing_rev_dt, \'YYYY\')
    from VW_LOANS_GRANTS_PORTFOLIO;
   
BEGIN
 
   Open c_portfolio;
   LOOP  
      FETCH c_portfolio INTO v_approval_no, v_start_yr, v_orig_yr, v_rev_yr;
         EXIT WHEN c_portfolio%NOTFOUND;      

      DELETE FROM REP_YEARLY_LOANS_GRANTS
      WHERE APPROVAL_NO = v_approval_no;

      IF (v_rev_yr is null) THEN
        n_end_yr := v_orig_yr;
      ELSE
        n_end_yr := v_rev_yr;
      END IF;

      n_curr_yr := v_start_yr;

      WHILE n_curr_yr <= n_end_yr
      LOOP
         INSERT INTO REP_YEARLY_LOANS_GRANTS(APPROVAL_NO, APPROVAL_YR, DOWNLOAD_DT)
         VALUES(v_approval_no, n_curr_yr, sysdate);
         n_curr_yr := n_curr_yr + 1;
      END LOOP;
   END LOOP;
  CLOSE c_portfolio;
   
END;
/


 

Related Topics

  Subject / Started by Replies Last post
0 Replies
92 Views
Last post November 01, 2008, 07:50:30 AM
by Corps
1 Replies
114 Views
Last post November 08, 2008, 06:24:28 PM
by MrSpecialist
0 Replies
70 Views
Last post April 02, 2009, 05:43:44 AM
by MrSpecialist
2 Replies
77 Views
Last post January 26, 2017, 12:46:38 AM
by LadyProgrammer
3 Replies
68 Views
Last post August 23, 2016, 02:14:07 AM
by arpee

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.