Having Fun with PL/SQL

PL/SQL or Procedural Language SQL is SQL’s extension. It’s a very neat language for writing functions, stored procedures, triggers all helping applications that run on databases. I like playing around with PL/SQL in my free time. Here are some Functions/procedures I have written for the heck of it in PL/SQL some of Which can be quite useful.

1. Factorial
   The following PL/SQL block will take any given number and return it’s factorial value
   _{CREATE OR REPLACE FUNCTION factorial(number_in IN NUMBER)
   RETURN NUMBER IS
   fac number :=1;
   n number := number_in ;
   BEGIN
   WHILE n > 0
   LOOP
   fac := n*fac ;
   n := n-1 ;
   END LOOP ;
   RETURN fac ;
   end factorial ;}
   
   
2. REVERSE_STR
   The below PL/SQL block will take a given string and reverse the order of the letters ie ‘HELLO’ becomes ‘OLLEH’
   _{CREATE OR REPLACE FUNCTION REVERSE_STR(string_in IN VARCHAR2 )
   RETURN VARCHAR2
   AS
   v_reverse VARCHAR2(256) ;
   v_length NUMBER ;
   BEGIN
   SELECT length(string_in) into v_length
   FROM dual ;
   FOR I IN REVERSE 1..v_length
   LOOP
   v_reverse := v_reverse || substr(string_in,i,1);
   END LOOP;
   RETURN v_reverse ;
   END reverse_str ;}
   
   
3. FIBONACCI
   As the name suggests, the following PL/SQL code will return the first n numbers of a fibonacci sequence
   ^{_{CREATE OR REPLACE FUNCTION fibonacci (number_in IN NUMBER)
   RETURN VARCHAR2
   IS
   v_first NUMBER := 0 ;
   v_second NUMBER := 1;
   v_next NUMBER;
   v_result VARCHAR2(4000) := '';
   BEGIN
   IF number_in <= 0 THEN
   RETURN 'Invalid input. Please enter a positive number. ' ;
   ELSIF number_in = 1 THEN
   RETURN '0';
   ELSE
   v_result := '0,1';
   FOR I IN 3..number_in
   LOOP
   v_next := v_first + v_second ;
   v_result := v_result || ', ' || v_next ;
   v_first := v_second ;
   v_second := v_next ;
   END LOOP;
   END IF;
   RETURN v_result ;
   END fibonacci ;}}
   
   
4. IS_PRIME
   The following function accepts a number returns ‘TRUE’ if it’s a prime number or ‘FALSE’ if it is not
   _{CREATE OR REPLACE FUNCTION is_prime( number_in in NUMBER )
   RETURN VARCHAR2
   IS
   v_is_prime VARCHAR2(10) ;
   v_val NUMBER ;
   BEGIN
   v_val := mod(number_in, 2);
   IF
   v_val <> 0
   THEN
   v_is_prime := 'TRUE' ;
   ELSE
   v_is_prime := 'FALSE' ;
   END IF
   RETURN v_is_prime ;
   END is_prime ;}
   
   
5. TO_MONTH
   Lastly and not the least and one that did spring from a use case to_month. Anyone familiar with the extract function that is pre-prepared for us in the oracle database? Well if you run a query something like
   _{SQL> l
   1 SELECT extract( month from sysdate ) current_month
   2* FROM dual
   SQL> /
   CURRENT_MONTH
   12}
   You get your month value as a digit. You may need this value in text which you can achieve with a case statement (that would be a fun long query). Introducing to_month , I just turn your case statement into pl/sql code and you can have
   
   _{SQL> SELECT to_month( extract( month from sysdate ) ) current_month
   2 FROM DUAL;}
   and it will return ‘DECEMBER’ . Pretty straight forward also pretty useful. The code is as follows ;
   
   _{CREATE OR REPLACE FUNCTION to_month ( month_no in NUMBER )
   RETURN VARCHAR2
   IS
   v_num NUMBER(2,0) ;
   v_month VARCHAR2(128) ;
   BEGIN
   v_num := month_no ;
   IF v_num = 1 THEN v_month := 'JANUARY';
   ELSIF v_num = 2 THEN v_month := 'FEBRUARY';
   ELSIF v_num = 3 THEN v_month := 'MARCH';
   ELSIF v_num = 4 THEN v_month := 'APRIL' ;
   ELSIF v_num = 5 THEN v_month := 'MAY' ;
   ELSIF v_num = 6 THEN v_month := 'JUNE';
   ELSIF v_num = 7 THEN v_month := 'JULY';
   ELSIF v_num = 8 THEN v_month := 'AUGUST';
   ELSIF v_num = 9 THEN v_month := 'SEPTEMBER';
   ELSIF v_num = 10 THEN v_month := 'OCTOBER';
   ELSIF v_num = 11 THEN v_month := 'NOVEMBER';
   ELSIF v_num = 12 THEN v_month := 'DECEMBER ;
   RETURN v_month ;
   END to_month ;}
   

New to PL/SQL and want to get a quick start on how it works, I recommend Steven Feurstein’s building blocks with PL/SQL blog series.
That’s all I have to share for this one hopefully you found at least one of the above useful and feel free to leave a comment.

Cheers .