PL SQL Quick Tips

Quick Tips

Output

set serveroutput on
DBMS_OUTPUT.PUT_LINE('hi' || myVariable);

Prevent Null output

DBMS_OUTPUT.PUT_LINE(NVL(n_var2, 'No Name'));

Declaring Variables:

Limit digits

 myVariable NUMBER(2,4) := 12.1234;

Set Variable type (VARCHAR or NUMBER) automatically

 n_var2 STUDENT.STUDENT.first_name%TYPE;

Ask User Input

-- the '&' tells the compiler to ask user for input. 
myCost NUMBER :=&input;

Formatting

Output value formatted with $

select to_char(sum(revenue), '$999,999.99' )
into revenue
from student.course_revenue;

DBMS_output.put_line('Total Revenue is: ' || revenue);

Concatenate two values into one field

select to_char(lastname || ', ' || firstname)
into name
from student.student
where firstname = 'Chad' and lastname = 'Jensen';

DBMS_output.put_line(name);

For Loops

Count up to 5

  FOR Count IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(Count);
  END LOOP;

Loop Backwards

  FOR Count IN reverse 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(Count);
  END LOOP;

If Statements

  IF myValue < 10 THEN
    DBMS_OUTPUT.PUT_LINE('Value is less than 10);
  ELSIF n_avgSal > 10 THEN
     DBMS_OUTPUT.PUT_LINE('Value is greater than 10);
  ELSE 
      DBMS_OUTPUT.PUT_LINE('Value is 10);
  END IF;

Exceptions: https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm