420-ch15-procsAndfuncs(2).pptx - IS 420 Chapter 15 PL\/SQL Language Stored procedures and functions 1 Objectives After completing this lesson you will

420-ch15-procsAndfuncs(2).pptx - IS 420 Chapter 15 PL/SQL...

This preview shows page 1 - 16 out of 45 pages.

IS 420 Chapter 15 PL/SQL Language Stored procedures and functions 1
Image of page 1
Objectives After completing this lesson you will know how to use: PL/SQL Stored Procedures PL/SQL Stored Functions PL/SQL Exceptions 2
Image of page 2
About Program Units/Blocks Program unit Self-contained group of program statements that can be used within larger program Anonymous PL/SQL programs Stored PL/SQL program units Server-side program units Client-side program units 3
Image of page 3
Types of Stored Program Units 4
Image of page 4
PL/SQL Procedure A PL/SQL procedure is a named PL/SQL block that is stored on the database server A procedure can receive multiple input parameters (values), can return multiple output values, or can return no output values Chapter 3 - PL/SQL 5
Image of page 5
An Example of Procedure 6 create or replace procedure HelloWorld(s varchar) as begin dbms_output.put_line(s); end;
Image of page 6
General Steps Create function or procedure using create function or create procedure command Oracle will compile and store the procedure In case of compilation error, type show errors You will see the line/column of error and the description of error Try to fix errors Otherwise, you will see “procedure compiled” message 7
Image of page 7
Show Errors Suppose you have created a procedure like Now you type (must do it after you click execute) 8 create or replace procedure HelloWorld(s varchar) as begin dbms_output.putline(s); end; show errors;
Image of page 8
Show Errors Output: Meaning: Error occurs on Line 4 putline is not recognized You can also google error number “PLS-00302” to obtain a more detailed description of error 9
Image of page 9
General Steps To see the names of procedures select * from user_procedures; To check the parameters of procedures desc procedure-name; 10
Image of page 10
General Steps Execute the procedure with passed parameterExecute in command: exec procedure-name(parameter values);Execute in PL/SQL(in anonymous program or another procedure or function): procedure-name(parameter values);exec HelloWorld(‘hello world!’);beginHelloWorld(‘hello world!’);end; 11
Image of page 11
Calling a Procedure from another Procedure 12 Proc 1 Proc 2 Create or replace Procedure Proc1 is . . Begin Proc2; DBMS_OUTPUT…. End; Create or replace Procedure Proc2 is . . Begin End; Call Proc2 Return result to Proc1
Image of page 12
Revisit: Example of Procedure Example: s is a parameter You cannot specify size for parameter types (since Oracle does not allow () within parameter type) 15 create or replace procedure HelloWorld(s varchar) as begin dbms_output.put_line(s); end; -- click execute button now exec HelloWorld(‘Hello’);
Image of page 13
Creating a Procedure CREATE OR REPLACE PROCEDURE procedure_name ( parameter1 mode datatype, parameter2 mode datatype , … ) IS | AS local variable declarations BEGIN program statements EXCEPTION exception handlers END; 16 header body Differences with anonymous PL/SQL programs?
Image of page 14
Example: Procedure to update table CREATE OR REPLACE PROCEDURE update_invoices_credit_total ( invoice_number_param
Image of page 15
Image of page 16

You've reached the end of your free preview.

Want to read all 45 pages?

  • Fall '08
  • Bandaru,P
  • Subroutine, Control flow, Eid

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture