Lecture Notes Unit5 chapter17 Stored procedures and functions

Lecture Notes Unit5 chapter17 Stored procedures and functions

• 1.
  RDBMS - UnitV Chapter 17 Stored Procedures and Functions Prepared By Dr. S.Murugan, Associate Professor Department of Computer Science, Alagappa Government Arts College, Karaikudi. (Affiliated by Alagappa University) Mailid: muruganjit@gmail.com Reference Book: LEARN ORACLE 8i, JOSE A RAMALHO
• 2.
  Stored Procedure -Definition ➢ A stored procedure is a group of SQL and PL/SQL commands that execute certain tasks. Procedure Trigger The user must call a procedure either from a program or manually. A trigger is automatically executed when a trigger event occurs.
• 3.
  TRIGGER - Definition
• 4.
  Creating a StoredProcedure ➢ A stored procedure has two parts: a specification section and the procedure body. The CREATE PROCEDURE command is responsible for the creation of procedures. ➢ Syntax: CREATE [OR REPLACE] PROCEDURE [schema.] procedure [ (argument [IN | OUT | IN OUT] datatype [, argument [IN | OUT | IN OUT] datatype] ...)] {IS|AS} {pl/sql_subprogram_body | external_body}
• 5.
  Creating a StoredProcedure Arguments: OR REPLACE ➢ Re-creates the procedure if it already exists. schema ➢ The schema that will contain the procedure. If schema is omitted, Oracle creates the procedure in the current schema. procedure ➢ The name of the procedure to be created. argument ➢ The name of a procedure argument. If the procedure does not accept arguments, you can omit the parentheses after its name. IN ➢ Specifies that a value is determined for the argument when the procedure is called.
• 6.
  Creating a StoredProcedure OUT ➢ Specifies that the procedure returns a value for this argument in its calling environment after the execution. IN OUT ➢ Specifies that a value must be determined for the argument when the procedure is called and that the procedure returns a value in its calling environment after the execution. datatype ➢ An argument can be any data type supported by PL/SQL. IS pl/sql_subprogram_body ➢ The procedure definition. Procedure definitions are written in PL/SQL.
• 7.
  Creating a StoredProcedure – Example Program No Argument 1. Create table emp2 (name varchar2(25), salary number(7), eno number(3)); 2. Insert few records. 3. View the records 4. Type the following program proc1.sql (This procedure increases the employees’ salaries 10 percent) create or replace procedure increase1 is begin update system.emp2 set salary=salary*1.1; end; / 5. Run the program (@ filepath) 6. Execute the procedure by the following statement exec increase1 7. Verify the modified output (salary field) in the emp2 database with step 3. (select * from emp2;)
• 8.
  Creating a StoredProcedure – Example Program with Arguments ➢ This procedure increases the employees’ salaries but this time the pay raise is only for those in a particular employee number and the increase is a different percentage. ➢ CREATE PROCEDURE increase2 (veno IN emp2.eno%type, percent number) IS BEGIN update emp2 set salary=salary*(1+percent/100) where eno=veno; END; / ➢ Execute the procedure by the following statement exec increase2(105,20) ➢ Here 105 is the employee number and 20 is the percentage to be increase.
• 9.
  Deleting a StoredProcedure ➢ To delete a stored procedure, use the DROP PROCEDURE command: ➢ Syntax : DROP PROCEDURE name ➢ Example: SQL> drop procedure increase1; Procedure dropped.
• 10.
  Function ➢ A Functionis a group of SQL and PL/SQL commands that execute certain tasks. ➢ Difference between Procedure and Function Procedure Function It does not return a value It returns a value The procedure invoked by the procedure name. The function invoked by the function name with select statement.
• 11.
  Creating a Function ➢The CREATE FUNCTION command creates a function as an isolated schema object. You can also create a function as part of a package. Syntax: CREATE [OR REPLACE] FUNCTION [schema.]function [(argument [IN | OUT | IN OUT] datatype [, argument [IN | OUT | IN OUT] datatype] ...)] RETURN datatype {IS | AS} pl/sql_subprogram_body
• 12.
  Creating a Function– Example Program for counting number of records 1. Create the empl table with the field as name, salary and dno (department number) and insert few records . 2. Type the following program create or replace function countemp (vdno in empl.dno%type) return number is emptot number; begin select count(*) into emptot from empl where dno=vdno; return emptot; end; / 3. Run the program 4. Execute the function by using the following statement select countemp(101) from dual