Docstoc

Stored Procedures(2)

Document Sample
Stored Procedures(2) Powered By Docstoc
					                                     Stored Procedures and Functions

A stored procedure or function is a PL/SQL program stored in an Oracle database and invoked by a user, either directly or
indirectly. The benefits of using stored procedures and functions are:
      Improved performance
      Reusability
      Portability
      Maintainability



Creating a Stored Procedure or Function
The syntax for creating a stored procedure is

CREATE [OR REPLACE] PROCEDURE procedure-name
[(argument1 ... [, argumentN) ] IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]
END [procedure-name];

where
         procedure-name is the procedure name subject to Oracle database object-naming restrictions.
         argument1 through argumentN are optional argument declarations that consist of argument-name [IN | OUT] datatype [
         { := | DEFAULT} value]
         local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local
         to procedure-name.
         executable-section is the PL/SQL statements that compose the procedure.
         exception-section is the optional exception handling section of the procedure.

For example, the following stored procedure has a single argument that the DELETE statement uses to determine which products
to remove from the Product table.

SQL> create or replace procedure Delete_Specified_Product
2                 (Description_Phrase varchar2) is
3       begin
4
4       delete from Product
5       where
6       upper(Description) like Description_Phrase;
7
7       end;
8       /

Procedure created.



The syntax for creating a stored function is very similar to the syntax for creating a stored procedure. Of course, a stored
function must also return a value.

CREATE [OR REPLACE] FUNCTION function-name
[(argument1 ... [, argumentN) ]
RETURN function-datatype IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]

                                                                                                                               1
RETURN function-value
END [function-name];



where
         function-name is the function name subject to Oracle database object-naming restrictions.
         argument1 through argumentN are optional argument declarations that consist of argument-name [IN | OUT] datatype [
         { := | DEFAULT} value]
         function-datatype is the datatype of the value returned by the function.
         local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local
         to function-name.
         executable-section is the PL/SQL statements that compose the function.
         exception-section is the optional exception handling section of the function.
         Function-value is the value that the function returns to the caller.

Here is an example of stored function that obtains a new Customer ID and stores the information about a new customer in the
Customer table:

SQL> create or replace function Get_New_Customer_ID
2                 (Salutation      varchar2,
3                 Last_Name        varchar2,
4                 First_Name       varchar2,
5                 Street_Address varchar2,
6                 City             varchar2,
7                 State            varchar2,
8                 Zipcode          varchar2,
9                 Home_Phone       varchar2,
10                Work_Phone       varchar2)
11                return number is
12
12      New_Customer_ID number(4);
13
13      begin
14
14      select Customer_Sequence.nextval
15      into New_Customer_ID
16      from dual;
17
17      insert into Customer
18      Customer_ID, Salutation, Last_Name, First_Name,
19      Street_Address, City, State, Zipcode, Home_Telephone_Number,
20      Work_Telephone_Number)
21       Values
22      (New_Customer_ID, Salutation, Last_Name, First_Name,
23       Street_Address, City, State, Zipcode, Home_Phone, Work_Phone);
24
24      return New_Customer_ID;
25
25      end;
26       /

Function created.



Obtaining Error Messages When Creating Stored Procedures

If Oracle detects errors when you create a stored PL/SQL program, it issues a nondescript message indicting that errors occurred
- without providing any additional details. To view the errors resulting from the attempted compilation of the PL/SQL code,
you can use the SQL*Plus command show errors, which displays the specific PL/SQL complication errors. Here is an example:

SQL> show errors


                                                                                                                               2
Errors for PROCEDURE SHOW_INSERTS:
LINE/COL        ERROR
--------------  -----------------------------------------------------------------------------------------------------
12/5            PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
                One of the following:
                ;
                ; was inserted before "DBMS_OUTPUT" to continue.



When PL/SQL compiles a subprogram, the resulting compilation errors are stored in an Oracle data dictionary table. Instead of
using the show errors command, you can query a data dictionary view name USER_ERRORS, which has this structure:

SQL> desc user_errors

Name                              Null?                   Type
--------------------              -------                 ------
NAME                              NOT NULL                VARCHAR2(30)
TYPE                                                      VARCHAR2(12)
SEQUENCE                          NOT NULL                NUMBER
LINE                              NOT NULL                NUMBER
POSITION                          NOT NULL                NUMBER
TEXT                              NOT NULL                VARCHAR2(2000)



The Name column contains the name of the stored procedure that has compilation errors. The Sequence column is the order in
which the error messages should be retrieved. The Line column contains the line number of the procedure's PL/SQL statement
that caused a compilation error. Position contains the column position where the error was detected.

To query USER_ERRORS to determine the cause of the warning message, use this SELECT statement:

SQL> select line, position, text
2      from user_errors
3      where
4      name = 'SHOW_INSERTS'
5      order by sequence;

LINE        POSITION              TEXT
---------   --------              -------------------------------------------
12          5                     PLS-00103: Encountered the symbol "DBMS_OUTPUT"
                                  when expecting one of the
                                  following:
                                  ;
                                  ; was inserted before "DBMS_OUTPUT" to continue.



Retrieving a Stored Procedure
You can retrieve the source code of a stored procedure by querying an Oracle data dictionary view. The Oracle data dictionary
is a group of tables that contain information about the Oracle database itself. Oracle defines a set of views that provide a
coherent perspective of the data dictionary. One of these views is named USER_SOURCE.

If you DESCRIBE USER_SOURCE, here is what you will see:

SQL> describe USER_SOURCE

Name                   Null?                  Type
--------------------   -------                ------
NAME                   NOT NULL               VARCHAR2(30)
TYPE                                          VARCHAR2(12)
LINE                   NOT NULL               NUMBER
TEXT                   NOT NULL               VARCHAR2(2000)


                                                                                                                                3
The Name column contains the procedure, function, package, or package body name. The Type column indicates whether the
source belongs to a procedure, function, package, or package body. The line number of each PL/SQL source line is stored in
the Line column. Text contains each PL/SQL program line.

For example, suppose that you created a stored procedure named DELETE_AMPS that consists of the following code:

SQL> CREATE OR REPLACE PROCEDURE DELETE_AMPS IS
2
2      BEGIN
3
3      delete from Product
4      where
5      upper(Description) like '%AMP%';
6
6      END;
7      /

Procedure created.



If you want to see the source code of DELETE_AMPS, query the USER_SOURCE data dictionary view.

SQL> select text
2      from User_Source
3      where
4      name = 'DELETE_AMPS'
5      order by line;

TEXT
---------------------------------
PROCEDURE Delete_Amps IS
BEGIN
delete from Product
where
upper(Description) like '%AMP%';
END;



Using Stored Functions in a SQL Statement
Oracle RDBMS enables you to reference a stored function within a SQL statement. Here is a example of how this functionality
is accomplished.

SOL> create or replace function DegF_to_DegC (Deg_F IN number)
2                return number is
3
3       Deg_C number;
4
4       begin
5
5       Deg_C := (5.0/9.O)*(Deg_F - 32);
6
6       return Deg_C;
7
7       end DegF_to_DegC;
8       /

Function created.

SQL> select body_temp, DegF_to_DegC(body_temp)
2      from patient;


                                                                                                                             4
BODY_TEMP             DEGF_TO_DEGC ( BODY_TEMP )
-------------------   -----------------------------------------------
99.2                  37 . 333333
100.2                 37 . 888889
103.8                 39 . 888889



Storing Results to a Table
Although PL/SQL doesn't have any built-in support for communicating with the user, you can still use PL/SQL to provide
results to a user or another program by
 Writing information to an intermediate table that a user or program can query
 Using the procedures and functions available in the Oracle-supplied package DBMS_OUTPUT

For instance, assume that you want to create a stored procedure that adjusts the price of a specified product downward by some
designated percentage.

SQL> create or replace procedure adjust_product_market_value
2                 (Prod_ID              varchar2,
3                  Man_ID               varchar2,
4                  Pct                  number) IS
5
5       Current_Val                     number(7,2);
6       New_Current_Val                 number(7,2);
7       Comments                        varchar2(100);
8
8       begin
9
9       select Current_Used_Value
10      into Current_Val
11      from Product
12      where
13      Product_ID = Prod_ID and
14      Manufacturer_ID = Man_ID;
15
15      New_Current_Val := Current_Val * (1 - Pct);
16
16      update Product
17                set Current_Used_Value = New_Current_Val
18                where
19                Product_ID = Prod_ID and
20                Manufacturer_ID = Man_ID;
21
21      Comments := 'Price adjusted from '               || to_char(Current_Val, 'S99999.99')
22                || ' to ' || to_char(New_Current_Val, '$99999.99');
23
23      insert into market_value_adjustment
24      (Product_ID, Manufacturer_ID, Comments)
25      values
26      (Prod_ID, Man_ID, Comments);
27
27      END;
28      /

Procedure created.

The procedure adds a record to a table named Market_Value_Adjustment and indicates the product, manufacturer, previous
value, and current value.

SQL> execute adjust_product_market_value ('C2002', 'MIT501', 0.08);

PL/SQL procedure successfully completed.


                                                                                                                                 5
SQL> select Product_ID, Manufacturer_ID, Comments
2      from Market_Value_Adjustment;

PRODUCT_ID MANUFA                     COMMENTS
------------------- --------------   --------------------------------------------------------
C2002                MIT501           Price adjusted from $120.00 to $110.40



Displaying Results with DBMS_OUTPUT
To provide output, you need to call two procedures: enable and put_line. Also, before using DBMS_OUTPUT, you need to set
the SQL*Plus system variable SERVEROUTPUT to ON.

SQL> set serveroutput on

SQL> CREATE OR REPLACE PROCEDURE show_inserts IS
2
2     max records CONSTANT int := 100;
3     I         int := 1;
4
4     BEGIN
5
5     dbms_output.enable;
6
6     FOR i IN 1..max_records LOOP
7
7     If (mod(i,10) = 0) then
8               INSERT INTO test_table
9               (record_number, current_date)
10              VALUES
11              (i, SYSDATE);
12              dbms_output.put_line('The value of i is ' || to_char(i));
13
13    else
14              NULL;
15
15    end if;
16
16    END LOOP;
17
17    END;
18    /

procedure created.

SQL> execute show_inserts;
The value of i is 10
The value of i is 20
The value of i is 30
The value of i is 40
The value of i is 50
The value of i is 60
The value of i is 70
The value of i is 80
The value of i is 90
The value of i is 100



Invoking a Stored Procedure
The method for invoking a stored procedure or function depends on the context.

For SQL*Plus, use the execute command (this syntax is for a stored procedure that doesn't have any arguments) in the
following way:

                                                                                                                       6
execute show_inserts;

From a PL/SQL subprogram, simply reference the stored procedure or function with any required arguments.




                                                                                                           7

				
DOCUMENT INFO
Lingjuan Ma Lingjuan Ma
About