procedureBody End A parameter in the paramList is specified as name mode type

Procedurebody end a parameter in the paramlist is

This preview shows page 15 - 23 out of 28 pages.

<procedureBody>; End; / A parameter in the paramList is specified as: <name> <mode> <type> Mode: IN input parameter (default) OUT output parameter INOUT input and output parameter If exists, then drop it and create it again ‘IS’ or ‘AS’ both are valid 15
Image of page 15
General Structure CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] [IS | AS] [declaration_section] BEGIN executable_section [ EXCEPTION exception_section] END [procedure_name]; Optional section for exception handling 16
Image of page 16
Example 1 17 Create Procedure remove_emp (employee_id NUMBER) Is tot_emps NUMBER; Begin DELETE FROM employees WHERE employees.employee_id = remove_emp.employee_id; tot_emps := tot_emps – 1; End remove_emp; / Define a variable. Note: no DECLARE statement Execute the command and create the procedure In PL/SQL a ‘;’ ends a line without execution You can use the procedure name before the parameter name By default, it is IN
Image of page 17
Calling A Stored Procedure 18 SQL> exec <procedureName> [(<paramList>)]; SQL> exec remove_emp (10); Create Procedure remove_emp (employee_id NUMBER) Is tot_emps NUMBER; Begin DELETE FROM employees WHERE employees.employee_id = remove_emp.employee_id; tot_emps := tot_emps – 1; End; /
Image of page 18
Printing From Stored Procedures 19 SQL> SQL> create or replace 2 procedure three_parms( 3 p_p1 number, 4 p_p2 number, 5 p_p3 number ) IS 6 begin 7 dbms_output.put_line( ‘p_p1 = ‘ || p_p1 ); 8 dbms_output.put_line( ‘p_p2 = ‘ || p_p2 ); 9 dbms_output.put_line( ‘p_p3 = ‘ || p_p3 ); 10 end; 11 / Procedure created. For the output to appear on screen, make sure to run: SQL> set serveroutput on; Taking three parameters Printing lines to output screen
Image of page 19
More Features: CURSOR & FOR Statement Create Procedure OpeningBal (p_type IN string) AS cursor C1 Is Select productId, name, price From products where type = p_type; Begin For rec in C1 Loop Insert into Temp values (rec.productId, rec.name, rec.price); End Loop; End; / 20
Image of page 20
Return Value Stored procedures can set output variables Stored procedures do not return values Stored functions differ from procedure in that they return values 21
Image of page 21
Creating A Stored Function 22 CREATE [OR REPLACE] FUNCTION
Image of page 22
Image of page 23

You've reached the end of your free preview.

Want to read all 28 pages?

  • Spring '12
  • Elke
  • MYSQL , Control flow

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture