Path: news.easynews.com!easynews!priapus.visi.com!news-out.visi.com!hermes.visi.com!eusc.inter.net!newsfeed.stueberl.de!news2.euro.net!newsfeed.wxs.nl!news.wxs.nl!not-for-mail From: "Richard Spee" Newsgroups: comp.databases.oracle.misc Subject: Re: pl/sql variables Date: Thu, 6 Jun 2002 18:58:26 +0200 Organization: Planet Internet Lines: 170 Message-ID: References: <3cff7bdd.271435646@news.dal.ca> NNTP-Posting-Host: ipc3797615.dial.wxs.nl Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0030_01C20D8C.23BF0920" X-Trace: reader07.wxs.nl 1023383136 17700 195.121.118.21 (6 Jun 2002 17:05:36 GMT) X-Complaints-To: abuse@planet.nl NNTP-Posting-Date: 6 Jun 2002 17:05:36 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Xref: easynews comp.databases.oracle.misc:82586 X-Received-Date: Thu, 06 Jun 2002 10:03:28 MST (news.easynews.com) ------=_NextPart_000_0030_01C20D8C.23BF0920 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 :=3D anInputVariable; end if; -- aLocalVariable :=3D aPublicVariable; -- END; -- END aPackage; / execute aPackage.aPublicProcedure('Testing.. 1.2.3'); execute aPackage.aPublicProcedure('Testing.. 1.2.3'); "tony" wrote in message = news:3cff7bdd.271435646@news.dal.ca... > I would like to learn more about variables in pl/sql. =20 >=20 > What I am tryng to do is write dynamic sql like this: >=20 > 'select part_no, part_desc from parts > order by ' || v_orderby || ' ' || v_asc_desc; >=20 > I would like to be able to do something like: >=20 > IF v_orderby =3D part_no AND v_asc_desc =3D asc THEN > v_asc_desc =3D desc > END IF >=20 > Every time I call the procedure the variables get initialized. How > can I store their previous values in memory? >=20 > thanks >=20 > cheers, > tony > 902.494.1021 w > 902.461.4550 h ------=_NextPart_000_0030_01C20D8C.23BF0920 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
This examples makes use of package=20 variables.
Just run the script and see what=20 happens.
 
--
set serveroutput on size=20 1000000
--
CREATE OR REPLACE PACKAGE aPackage
IS
--
-- = public=20 procedures
--
PROCEDURE aPublicProcedure
(anInputVariable  = IN=20 VARCHAR2
);
--
-- public package = variables
--
aPublicVariable=20 varchar2(20);
--
END aPackage;
/
 
CREATE OR REPLACE PACKAGE BODY=20 aPackage
IS
--
PROCEDURE = aLocalProcedure
(anInputVariable  IN=20 VARCHAR2
)
IS
BEGIN
  =20 dbms_output.put_line(anInputVariable);
END;
--
--
PROCEDURE=20 aPublicProcedure
(anInputVariable  IN=20 VARCHAR2
)
IS
--
aLocalVariable =20 VARCHAR2(20);
--
BEGIN
   = aLocalProcedure('aPublicVariable:=20 '||aPublicVariable);
   aLocalProcedure('aLocalVariable:=20 '||aLocalVariable);
   --
   if = aPublicVariable is=20 null
   then
      = aPublicVariable :=3D=20 anInputVariable;
   end if;
   = --
  =20 aLocalVariable :=3D aPublicVariable;
   = --
END;
--
END=20 aPackage;
/
execute aPackage.aPublicProcedure('Testing..=20 1.2.3');
execute aPackage.aPublicProcedure('Testing..=20 1.2.3');
 
"tony" <ttigger4321@hotmail.com> wrote in=20 message news:3cff7bdd.271435646@news.dal.ca...
> I would like = to learn more=20 about variables in pl/sql. 
>
> What I am tryng to do = is=20 write dynamic sql like this:
>
> 'select part_no, part_desc = from=20 parts
> order by ' || v_orderby || ' ' || v_asc_desc;
> =
> I=20 would like to be able to do something like:
>
> IF = v_orderby =3D=20 part_no AND v_asc_desc =3D asc THEN
>    v_asc_desc =3D = desc
>=20 END IF
>
> Every time I call the procedure the variables = get=20 initialized.  How
> can I store their previous values in=20 memory?
>
> thanks
>
> cheers,
> = tony
>=20 902.494.1021 w
> 902.461.4550 h ------=_NextPart_000_0030_01C20D8C.23BF0920--