Mega Code Archive
A local subprogram within a stored procedure
<![CDATA[
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.
]]>