Re: pl/sql variables

This examples makes use of package variables. Just run the script and see what happens.

--

set serveroutput on size 1000000
--

CREATE OR REPLACE PACKAGE aPackage
IS
--

• public procedures
  --
  PROCEDURE aPublicProcedure (anInputVariable IN VARCHAR2 );
  --
  
• public package variables
  --
  aPublicVariable varchar2(20);
  --
  END aPackage; /

CREATE OR REPLACE PACKAGE BODY aPackage
IS
--

PROCEDURE aLocalProcedure
(anInputVariable IN VARCHAR2
)
IS
BEGIN
   dbms_output.put_line(anInputVariable); END;
--
--

PROCEDURE aPublicProcedure
(anInputVariable IN VARCHAR2
)
IS
--

aLocalVariable VARCHAR2(20);
--

BEGIN
   aLocalProcedure('aPublicVariable: '||aPublicVariable);    aLocalProcedure('aLocalVariable: '||aLocalVariable);    --
   if aPublicVariable is null
   then

      aPublicVariable := anInputVariable;    end if;
   --
   aLocalVariable := aPublicVariable;
   --
END;
--

END aPackage;
/
execute aPackage.aPublicProcedure('Testing.. 1.2.3'); execute aPackage.aPublicProcedure('Testing.. 1.2.3');

"tony" <ttigger4321_at_hotmail.com> wrote in message news:3cff7bdd.271435646_at_news.dal.ca...

> I would like to learn more about variables in pl/sql.  
> 
> What I am tryng to do is write dynamic sql like this:
> 
> 'select part_no, part_desc from parts
> order by ' || v_orderby || ' ' || v_asc_desc;
> 
> I would like to be able to do something like:
> 
> IF v_orderby = part_no AND v_asc_desc = asc THEN
>    v_asc_desc = desc
> END IF
> 
> Every time I call the procedure the variables get initialized.  How
> can I store their previous values in memory?
> 
> thanks
> 
> cheers,
> tony
> 902.494.1021 w
> 902.461.4550 h