A local subprogram within a stored procedure : Procedure Definition « Stored Procedure Function « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Stored Procedure Function
  3. Procedure Definition

A local subprogram within a stored procedure

    

SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE StoredProc AS
  2    CURSOR c_Someemp IS SELECT fname, lname FROM emp WHERE lname > 'L' ORDER BY lname;
  3
  4    v_FormattedName VARCHAR2(50);
  5
  6    FUNCTION FormatName(p_FirstName IN VARCHAR2, p_LastName IN VARCHAR2)
  7      RETURN VARCHAR2 IS
  8    BEGIN
  9      RETURN p_FirstName || ' ' || p_LastName;
 10    END FormatName;
 11
 12  BEGIN
 13    FOR v_empRecord IN c_Someemp LOOP
 14      v_FormattedName := FormatName(v_empRecord.fname,v_empRecord.lname);
 15      DBMS_OUTPUT.PUT_LINE(v_FormattedName);
 16    END LOOP;
 17  END StoredProc;
 18  /

Procedure created.

SQL>
SQL> BEGIN
  2    StoredProc;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> drop table emp;

Table dropped.








Related examples in the same category

1.Creat an empty procedure
2.replace a procedure
3.Create a stored procedure and how to call it.
4.Define procedure to insert data
5.Define and call procedure
6.Create procedure with authid
7.Create a stored procedure with authid
8.Mutually exclusive local subprograms.
9.A forward declaration.
10.Inner procedure
11.Save calculation result to a table in procedure
12.exception throwed out of the procedure
13.AUTHID clause in a CREATE PROCEDURE statement indicates that this procedure is being created with user's or invoker's rights
14.Increase gift price
15.Overloaded local procedures: number and varchar2
16.Using all the default values
17.Forward Referencing
18.Mark procedure with authid current_user
19.Reference package variable in a procedure
20.Only manager can change the password