1 / 40

Oracle stored procedures functions

Oracle stored procedures functions. Table of Contents. Stored Procedures Stored Procedures and Functions Creating or Changing, Executing, and Deleting a Stored Procedures Stored Procedure : The Header Section Stored Procedure : Header Parameters

illias
Télécharger la présentation

Oracle stored procedures functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oraclestored proceduresfunctions

  2. Table of Contents • Stored Procedures • Stored Procedures and Functions • Creating or Changing, Executing, and Deleting a Stored Procedures • Stored Procedure : The Header Section • Stored Procedure : Header Parameters • Stored Procedure : Header Parameters with IN mode • Stored Procedure : Header Parameters with OUT mode • Defining a Stored Procedure within a Procedure • Stored Procedure - Overloading • Functions • Function Structure • Creating and Using a Function • Creating and Using a Function in a SELECT Clause • Using a Function in a SELECT Clause

  3. Stored Procedures • The code block that have been illustrated thus far have been anonymous procedures. • Code block may also be defined as database objects, meaning that code resides within the database in a compiled form; therefore, performance will be greater using these types of procedures.

  4. Stored Procedures and Functions • There are two types of database code blocks: Stored Procedures (named) procedures and functions. • Difference between the two is that the named procedures can be executed as a statement of its own and function is always used as an argument within a statement • Another difference is that the function always returns a value; the procedure does not have to return a value.

  5. Creating or Changing, Executing, and Deleting a Stored Procedures The next example illustrates the creation, execution, and deletion of a stored procedure. The procedure contains one statement (named firstproc) that outputs the words 'Hello to you from Stored Procedure' to you. The CREATE or REPLACE option is used to create the procedure, which is then executed from the SQL prompt in SQLPlus. Finally, the procedure is deleted with the DROP PROCEDURE command.

  6. Stored Procedure - Example Set serveroutput on create or replace procedure firstproc is begin dbms_output.put_line ('Hello to you from Stored Procedure'); end; / execute firstproc ; drop procedure firstproc ;   Procedure created. SQL> execute firstproc ; Hello to you from Stored Procedure  PL/SQL procedure successfully completed. SQL> drop procedure firstproc ; Procedure dropped.

  7. Stored Procedure – Practice SQL Code Practice 1: Modify the previous example to produce the following answer ********************************** *** Named Procedure *** *** Today date is 04-FEB-03 ***** ********************************** PL/SQL procedure successfully completed.

  8. Stored Procedure – Practice SQL Code -- **************** Stored Procedures ****************** -- 1. Creating a named procedure -- ***************************************************** create or replace procedure secondproc is begin dbms_output.put_line ('**********************************'); dbms_output.put_line ('*** Named Procedure ***'); dbms_output.put_line('*** Today date is '||sysdate||' *****'); dbms_output.put_line ('**********************************'); end; / execute secondproc ; drop procedure secondproc ;

  9. Stored Procedure : The Header Section • Stored procedures need to have a header section, which is the first section of the code block and contains important information . • The header can contain information: ·The type of PL/SQL object (i.e., function or procedure) ·The name of the object ·Parameters (optional for procedures) The keyword IS The following is a syntax template of a header section;Procedure_function objectname (parameter1, parameter2) is The following are examples of procedure header sections: Procedure example1 is Procedure example2 (lname in employee.last_name) is

  10. Stored Procedure : Header Parameters The header section can optionally contain parameters, which are used to input values into the procedure and to obtain values from the procedure. Parameter declarations follow the PL/SQL object name and are enclosed by parentheses. The declaration consists of the formal parameter name, the mode, and the parameter data type. As many parameters as necessary can be included in the declaration. Each declaration must be separated from other declarations by a comma. The following is a syntax template: (parameter_name1 mode datatype, parameter_name2 mode datatype)

  11. Stored Procedure : Header Parameters • The formal parameter name is the name that is placed in the parameter list during the declaration. These are the names that the procedure will use. • The actual parameter names are the names of variables that will supply values to the declared parameters from the calling procedure. • The mode represents the manner in which the procedure will use the parameter Modes are: IN OUT In Out

  12. Stored Procedure : Header Parameters with IN mode • The IN mode creates a variable that is similar to a constant. • The value may not be changed after initialization at execution time. • Several IN mode characteristics are as follows: • When a mode is not defined, the variable will use the IN mode. • When the procedure is called, an IN parameter value can be a variable or a text literal. • The literal can be assigned as a permanent default value at the procedure definition time. Place the keyword DEFAULT after the parameter data type, followed by the default value. If you use the default, do not populate the parameter when calling the procedure.

  13. Stored Procedure : Header Parameters with OUT mode • The OUT mode is used to return a value to the calling procedure. • A parameter defined with this mode may not be assigned a value by the calling procedure. This means that when the procedure is invoked, an OUT parameter should contain a variable that will be used in the calling procedure. • This variable will be assigned a value. The following are rules concerning OUT parameters: • The value cannot be assigned to another variable. • A default value cannot be assigned to the parameter. • The value must be assigned within the procedure. • When the procedure is called, OUT parameters must be a variable. They cannot be a text literal because they will receive a value.

  14. Stored Procedure : Header Parameters with OUT mode • IN OUT parameters can be assigned a value from the calling procedure. The procedure may modify the value, and the parameter value can be passed to the calling procedure. The only restriction concerning IN OUT parameters is this: • When the procedure is called, the parameter must be a variable. • The data type parameter definition describes the type of variable. Rules concerning the parameter data are as follows: • The parameter data type can be any of the normal data types, such as VARCHAR2, CHAR, NUMBER, or BOOLEAN. • %TYPE and %ROWTYPE can be used in the declaration even if they reference constrained columns.

  15. Stored Procedure Using the IN Parameter- Example • Inputting Values by Using the IN Parameter Mode The procedure lists the staffs of a specific branch. • When the procedure is defined, the formal parameter name CITY is placed within the parameter section. • The parameter mode is set to IN. This declaration tells Oracle that the procedure needs a value from an external source. • This parameter or variable is used in the WHERE clause to retrieve the proper records from the database.

  16. Stored Procedure Using the IN Parameter- Example set serveroutput on create or replace procedure list_branch (city1 in varchar2) is begin for a in (select s.lname, s.fname, s.dob from staff s,branch b where city = city1 And s.branchno=b.branchno) loop dbms_output.put_line(a.lname||', '||a.fname||' '||a.dob); end loop; end; / exec list_branch('London'); drop procedure list_branch; /

  17. Stored Procedure Using the IN Parameter- Example • The name of a procedure can be used as a calling statement in another procedure. • The called procedure can also supply a value to the calling procedure by using the OUT parameter mode. • Next program illustrates this concept. • The first section of the listing displays the script to create a named procedure to determine the oldest employee in a department. • The CITY parameter is used to tell the procedure what branch to use in determining the oldest staff. • The employee first and last name are returned to the calling procedure. The FNAME and LNAME parameters that are defined by using the OUT parameter mode are used to perform this function. These parameters are declared by using the %TYPE attribute. • The second part of the listing illustrates an anonymous procedure that retrieves each of the records in the staff table. This procedure calls the stored procedure and assigns the current value of the city to the CITY parameter. The returned values are then used in an output statement in the calling procedure.

  18. Stored Procedure Using the OUT Parameter- Example • The first section of the listing displays the script to create a Stored procedure to determine the oldest employee in a department. • The branchno parameter is used to tell the procedure what branch to use in determining the oldest employee. • The staff first and last name are returned to the calling procedure. The FNAME and LNAME parameters that are defined by using the OUT parameter mode are used to perform this function. • The second part of the listing illustrates an anonymous procedure that retrieves each of the records in the Employee table. This procedure calls the named procedure and assigns the current value of the department to the DEPARTMENT parameter. The returned values are then used in an output statement in the calling procedure.

  19. Stored Procedure Using the OUT Parameter- Example create or replace procedure find_oldest_staff (branchno in varchar2, fname out staff.fname%type, lname out staff.lname%type) is cursor b is select fname, lname from staff where dob = (select max(dob) from staff where lower(branchno) = lower(branchno)); begin open b; fetch b into fname, lname; close b; end find_oldest_staff; / declare cursor a is select * from branch; a_var a%rowtype; lname staff.lname%type; fname staff.fname%type; begin open a; fetch a into a_var; while a%found loop find_oldest_staff (a_var.branchno, fname,lname); dbms_output.put_line (rpad(a_var.branchno, 8)||' '||rpad(fname, 8)|| ' '||rpad(lname, 10)); fetch a into a_var; end loop; close a; end;

  20. Defining a Stored Procedure within a Procedure • In our previous examples, the stored procedures were created as database objects. • It is possible to define a stored procedure within the declaration section of the anonymous code block. • When this is done, the procedure acts similarly to a macro. There is one rule that pertains to this technique: The named procedure must be the last object listed in the declaration section.

  21. Stored Procedure - Example -- Calling stored procedures from anonymous procedure declare procedure Hello is begin dbms_output.put_line ('Hello to you from Stored Procedure'); end; procedure list_position (position in varchar2) is begin for a in (select * from staff where position=position) loop dbms_output.put_line (a.position); end loop; end; begin dbms_output.put_line ('*** Results of the first procedure'); hello; dbms_output.put_line ('*'); dbms_output.put_line ('*** Results of the second procedure'); dbms_output.put_line ('position '); dbms_output.put_line ('---------'); list_position('Assistant'); end;

  22. Stored Procedure - Example *** Results of the first procedure Hello to you from Stored Procedure * *** Results of the second procedure position --------- Assistant Supervisor Assistant Manager Assistant manager PL/SQL procedure successfully completed.

  23. Stored Procedure - Overloading • Overloading is a technique that allows the developer to have objects with the same name that have different behaviors. • PL/SQL allows the developer to define two or more PL/SQL objects (e.g., procedures, functions) with the same name within a procedure. Developers can create another object with the same name by simply changing a parameter definition. • The combination of procedure and name and parameters determines uniqueness. • When Oracle encounters the name of a PL/SQL object, it evaluates the parameters for all objects with the same name. The PL/SQL object with the same defined parameters as the calling statement will be used.

  24. Stored Procedure - Overloading • To illustrate overloading, next program contains two procedures with the same name (LIST_STAFF). • The first procedure lists the staff for a given department. The value for the department is input through the parameter section. • The second procedure lists the staff in a given department who were born after a specific date. This is the overloaded procedure. It differs from the original procedure in that it requires two parameters to be input.

  25. Stored Procedure: Overloading - Example -- This is an anonymous procedure using an overloading procedure name -- Both procedures are using the same name. declare -- The first procedure that lists the staff in a specified branch procedure list_staff (branch in varchar2) is begin for a in (select lname, dob from staff where branchno = branch) loop dbms_output.put_line (a.lname||' '||to_char(a.dob)); end loop; end;

  26. Stored Procedure: Overloading - Example -- The overloaded procedure that lists the staff in a -- specified branch born after a specified date procedure list_staff (branch in varchar2, born in date) is begin for a in (select lname, dob from staff where branchno = branch and dob > born) loop dbms_output.put_line (a.lname||' '||to_char(a.dob)); end loop; end; begin dbms_output.put_line ('Results of the first procedure'); list_staff('B003'); dbms_output.put_line ('----'); dbms_output.put_line ('Results of the second procedure'); list_staff('B003', '01-JAN-36'); end; /

  27. Functions • Functions are another important PL/SQL object. • Functions are similar to procedures except that functions cannot be a statement. They must be the argument on the right side of an assignment or evaluation. • In addition, a function must always return a value to a calling statement.

  28. Functions As a business analyst, functions can make your life easier by eliminating repeated calculations and formatting. For example, it is common to format multiple name values into one expression, such as Johnson, Alice. It is much easier to create and use a function that performs the formatting, rather than continually having to format the data. Calculations are an even better use of a function. It is somewhat common for different business analysts to code and perform a calculation but get different results. Differences in the calculations (and mistakes) are the causes. Placing the calculation in a function will allow you to test once and to compute the same value every time. When the calculation is sophisticated, placing the calculation into a function will save you a great deal of time because you do not have to recode the calculation continually. Adding functions to your database will also increase the amount of information that is available to your end users.

  29. Function Structure The structure of a function is similar to that of a procedure. Functions must have a header. • The header begins with the keyword FUNCTION, followed by an optional parameter section. • The mandatory keyword RETURN and the return data type follow the parameter section. The return data type describes the type of value that the function will return to the calling statement. • The keyword IS terminates the header. The rest of the function structure is similar to a standard code block. • Declarations are placed after the IS keyword and before the executable section, which starts with the keyword BEGIN. • The executable section must have a RETURN variable statement, which returns the value to the calling assignment statement. • The function code block can also contain an exception section.

  30. Creating and Using a Function The following program illustrates the creation and use of a simple function. The function is created by using the CREATE command. The return data type is VARCHAR2. It must be unconstrained (i.e., does not have a precision). The function contained in the listing returns the text literal “Hello from Function” to the calling statement. This listing also contains an anonymous procedure that uses the function to assign the value to a variable. The function is deleted from the database by using the DROP command.

  31. Creating and Using a Function - Example --1. Assignment of a function in an anonymous block create or replace function Hello return varchar2 is begin return 'Hello from Function'; end; / declare a varchar2(30); begin a := Hello; dbms_output.put_line (a); end; / drop function Hello;

  32. Creating and Using a Function - Example SQL> declare 2 a varchar2(30); 3 begin 4 a := Hello; 5 dbms_output.put_line (a); 6 end; 7 / Hello from Function PL/SQL procedure successfully completed. SQL> drop function Hello;

  33. Creating and Using a Function in a SELECT Clause • User-defined functions can also be included in the SELECT clause of a SELECT statement. • When the function is stored within the database, it can be used in the same manner as other functions such as SUBSTR or UPPER. • The next program illustrates this concept. staff age is a constantly changing value. A function called AGE is created to compute the staff current ages. The function requires the calling program or SQL statement to supply the staff’ birth date values. The function then determines the staff’ ages. The function also uses the TRUNC built-in function to create a formatted age. By creating this function, the developer does not have to keep coding the more complex age calculation.

  34. Creating and Using a Function in a SELECT Clause create or replace function age (dob in date) return number is age number; begin age := trunc(months_between(sysdate, dob)/12, 0); return age; end; / select lname, age(dob) as AGE from staff where branchno = 'B003'; LNAME AGE --------------- --------- Beech 42 Ford 44 Brand -37

  35. Creating and Using a Function in a SELECT Clause - Practice • Modify the previous AGE FUNCTION example to produce the following result STAFF LNAME Current D Month Between DOB ----- --------------- --------- ------------- --------- SG37 Beech 06-FEB-03 506 10-NOV-60 SG14 Ford 06-FEB-03 538 24-MAR-58 SA9 Howe Brand 06-FEB-03 395 19-FEB-70 SG5 Brand 06-FEB-03 -447 03-JUN-40 SL41 Lee 06-FEB-03 451 13-JUN-65 SL21 white 06-FEB-03 -511 01-OCT-45

  36. Creating and Using a Function in a SELECT Clause – Practice Code create or replace function months (DOB in date) return number is months number; begin months := trunc(months_between(sysdate, DOB), 0); return months; end; / select staffno, lname, sysdate "Current Date", months(dob) as "Month Between",DOB from staff;

  37. Using a Function in a SELECT Clause calling several functions in the Select statement create or replace function Hello return varchar2 is begin return 'Hello from Function'; end; / create or replace function months (dob in date) return number is months number; begin months := trunc(months_between(sysdate, dob), 0); return months; end; / Column lname format a10 Column function format a20 select staffno, lname, sysdate "Cur Date", months(dob) as "Month Between", dob, Hello as function from staff;

  38. Using a Function in a SELECT Clause --****************** User Defined Function ********** -- calling several functions in the Select statement --*************************************************** STAFF LNAME Cur Date Month Between DOB FUNCTION ----- ---------- --------- ------------- --------- -------------------- SG37 Beech 06-FEB-03 506 10-NOV-60 Hello from Function SG14 Ford 06-FEB-03 538 24-MAR-58 Hello from Function SA9 Howe Brand 06-FEB-03 395 19-FEB-70 Hello from Function SG5 Brand 06-FEB-03 -447 03-JUN-40 Hello from Function SL41 Lee 06-FEB-03 451 13-JUN-65 Hello from Function SL21 white 06-FEB-03 -511 01-OCT-45 Hello from Function

  39. Using a Function with cursor create or replace function count_staff (branch in varchar2) return number is cursor a is select count(staffno) tot from staff where branchno=branch; a_var a%rowtype; begin open a; fetch a into a_var; if a%notfound then return 'No such Branch'; else return a_var.tot; end if; end; /

  40. Using a Function within a Procedure select branchno, city, count_staff(branchno) "# of Staff " from branch order by 3 desc; BRANC CITY # of Staff ----- -------------------- ----------- B003 Glasgow 3 B005 London 1 B007 Aberdeen 1 B004 Bristol 0 B002 London 0

More Related