Re: PL/SQL

Daniel,

   Oracle Press has a pretty good book on the subject, I think the title is 'PL/SQL Programming Handbook'. In PL/SQL a "Hello World" program and its output would look like this:

SQL> CREATE OR REPLACE PROCEDURE HWLD AS   2 BEGIN
  3 dbms_output.put_line('Hello World');   4 END;
  5 /

Procedure created.

SQL> set serveroutput on
SQL> exec hwld
Hello World

PL/SQL procedure successfully completed.

One of the uses of PL/SQL is to write procedures and functions not included by Oracle. For example, there is no easy way in SQL*PLUS to format a number with decimal places to print out as 000123.4500, the function below makes this reasonably simple:

CREATE OR REPLACE FUNCTION PAD_NUM(v_input NUMBER, v_length NUMBER, v_precision NUMBER)
  RETURN varchar2 IS v_output VARCHAR2(50);

  v_tot_len      NUMBER(3);
  v_whole_len    NUMBER(3);
  v_whole_num    NUMBER(20);
  v_decimal      NUMBER(3);
  v_len_test     NUMBER(3);

• precision must be a positive integer value
  --
  v_decimal := trunc(v_precision); IF v_decimal != v_precision THEN v_output := 'Precision MUST be an INTEGER'; RETURN v_output; ELSE v_decimal := abs(v_precision); IF v_decimal != v_precision THEN v_output := 'Precision MUST be POSITIVE'; RETURN v_output; END IF; END IF;
  --
  
• length must be a positive integer value
  --
  v_len_test := trunc(v_length); IF v_len_test != v_length THEN v_output := 'Length MUST be an INTEGER'; RETURN v_output; ELSE v_len_test := abs(v_length); IF v_len_test != v_length THEN v_output := 'Length MUST be POSITIVE'; RETURN v_output; END IF; END IF;
  --
  
• length must be greater than or equal to precision
  --
  IF v_precision > v_length THEN v_output := 'Length MUST be >= Precision'; RETURN v_output; END IF;
  --
  
• whole and decimal portions of inputted number
• should each be less than or equal to respective
• portions of outputted string
  --
  IF v_precision > 0 THEN
  --
  
• return has decimal places
  --
  IF length(trunc(v_input)) > (v_length - v_precision) THEN v_output := 'Whole part of Length MUST be >= whole part of value'; RETURN v_output; ELSIF (length(v_input) - length(trunc(v_input)) - 1) > v_precision THEN v_output := 'Decimal part of Length MUST be >= decimal part of value'; RETURN v_output; END IF; ELSE
  --
  
• return has no decimals
  --
  IF length(trunc(v_input)) < length(v_input) THEN v_output := 'Decimal part of Length MUST be >= decimal part of value'; RETURN v_output; ELSIF length(v_input) > v_length THEN v_output := 'Whole part of Length MUST be >= whole part of value'; RETURN v_output; END IF; END IF;
  --
  
• if all tests have been passed - then process the number and return the string
• the processing used is conditional upon the precision supplied by the user
  --
  v_tot_len := v_length + 2; v_whole_len := v_length - v_decimal; v_whole_num := to_number(rpad(to_char(1),(v_whole_len + 1),'0')); IF v_precision = 0 THEN SELECT substr(rpad(to_char(v_input + v_whole_num),v_tot_len,'0'),2,v_whole_len) INTO v_output FROM DUAL; ELSE SELECT substr(rpad(to_char(v_input + v_whole_num),v_tot_len,'0'),2,v_whole_len) || '.' || substr(rpad(to_char(v_input + v_whole_num),v_tot_len,'0'), (v_whole_len + 3),v_decimal) INTO v_output FROM DUAL; END IF; RETURN v_output; END; /

Once you have created this function, you can use it in SQL*PLUS or in other PL/SQL procedures, functions, or scripts as in:

SQL> SELECT PAD_NUM(pnum,10,4) FROM padnum;

PAD_NUM(PNUM,10,4)

[Quoted] 001234.5600
000123.4560
012345.6780
000123.0000

Hope this info is of some help to you in getting started, you can also find a bunch of other PL/SQL scripts to look at on the web - Oracle itself has quite a few, www.orafaq.org and www.orafans.com have others, plus links to other sites with even more. You can gain some more insight into how the language works by looking at them even if they are doing something you may never have use for. Good luck,
Paul

In article <38449EEF.7C6F2E2D_at_ingress.com>,   Daniel Morgan <danmorg_at_ingress.com> wrote: [Quoted] > I may not be new relational database management systems, SQL, nor Oracle

> RDMS; but I am a complete newbie when it comes to PL/SQL.
>
> I have a couple of Oracle books, but none of them seems to provide the
> 'Hello World' of a PL/SQL block.
>
> Where do I begin?
>
> Thanks in advance,
> dan
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 01 1999 - 12:13:24 CET