Schipplock - Oracle PL/SQL Quickstart

Oracle PL/SQL Quickstart

, //

Oracle PL/SQL? Naa…that’s scary, isn’t it? Well, it’s as scary as Pg/PLSQL.

However, in my small guide I am showing you the basics of PLSQL and when you are finished with it you will probably have a better understanding of plsql and probably use it in your daily workflow.

For oracle newcomers the entry might be a bit hard as setting up oracle costs time and a lot of ram so a dedicated machine with a dual core with at least 4GB of ram is recommended. However, if you simply want to learn the language and don’t want to put your money on new hardware, you can apply for a developer account at AppsHosting.com. I’m using this developer account myself because they give you a simple web-ui where you can enter and save your sql-statements. And all that for free, but it’s really just for testing purposes. Try to apply because I base my example code on the “DEMO_”-tables. If you don’t have access, don’t worry. My examples will be easy so relax :). You can map these onto your own data, I’m sure of it. Btw. I _do_ have a dedicated server for oracle but most of the time I’m not at home and I don’t want to let the beast run all the day just so I can execute some statements from time to time :).

Btw.: I will use “dbms_output.put_line” to print out certain debug information and to make things clear.

if then else
Like any other programming language oracles plsql also has conditionals:

begin
  if 1=1 then
    dbms_output.put_line('1 is equal 1');
  end if;
end;

begin
  if 1=2 then
    dbms_output.put_line('1 is equal 1');
  else
    dbms_output.put_line('1 is not equal 2');
  end if;
end;

begin
  if 100 < 1000 and 100 > 99 then
    dbms_output.put_line('true');
  else
    dbms_output.put_line('false');
  end if;
end;

“true” :).

Now you are becoming rich:

the case when construct

declare
  salary number := 2000;
begin
  case true
    when (salary < 1000) then
      dbms_output.put_line('not enough money');
    when (salary <= 1000) then
      dbms_output.put_line('better than nothing');
    when (salary > 1000 and salary < 2000) then
      dbms_output.put_line('it''s getting better');
    else
      dbms_output.put_line('now you are becoming rich');
  end case;
end;

Simple for loops

begin
  for run in 1..10 loop
    dbms_output.put_line('run: ' || run);
  end loop;
end;

This prints out:

run: 1
run: 2
run: 3
run: 4
run: 5
run: 6
run: 7
run: 8
run: 9
run: 10

Now I make use of the “DEMO_CUSTOMERS” table:

From that table I can select all first and last names with the following sql statement:

select cust_first_name, cust_last_name from demo_customers;

Now with the for-loop I can also iterate through this result:

declare
  cursor my_cursor is select cust_first_name, cust_last_name from demo_customers;
begin
  for run in my_cursor loop
    dbms_output.put_line('firstname: ' || run.cust_first_name);
  end loop;
end;

And the output is:

firstname: Eugene
firstname: John
firstname: William
firstname: Fiorello
firstname: Albert
firstname: Edward
firstname: Edward "Butch"

OMG! We are so flexible by now without having too much sql in it…well it is sql but…ah you know :).

You can do almost the same with “while”:

declare
  first_name demo_customers.cust_first_name%TYPE;
  cursor my_cursor is select cust_first_name from demo_customers;
begin
  open my_cursor;
  while my_cursor%ISOPEN loop

  fetch my_cursor into first_name;

  if my_cursor%NOTFOUND then
    close my_cursor;
  end if;
  
  dbms_output.put_line('firstname: ' || first_name);

  end loop;
end;

But as you can see it’s a bit more to write :). However, it might fit your coding style somehow.

Now that you’ve seen “begin” and “end” you can go on with creating non-anonymous functions with a name.

create a simple function

create or replace function hello return string as
begin
  return 'Hello World';
end;

This will create the function “hello” and you can look it up in the object browser. If you need a description of the function you can of course use “describe hello” to let oracle describe your function. This can be very helpful if you are in the need to know the “interface”.

You can use the function like this:

select hello as helloworld from dual;

This will return “Hello World” as defined in your function.

Wasn’t that easy? Ok, let’s go on!

create a simple function with declared variables

create or replace function hello2 return string as
  first_name varchar2(100) := 'Andreas';
begin
  return first_name;
end;

select hello2 as foobar from dual;

This now returns “Andreas”.

functions with parameters:

create or replace function hello3 (first_name varchar, last_name varchar) return varchar as
begin
  return first_name || '-' || last_name;
end;

select hello3('Andreas','Schipplock') as foobar from dual;

Result: “Andreas-Schipplock”

create a simple procedure:

create or replace procedure hello4 (first_name IN OUT varchar) is
begin
  first_name := 'Hello ' || first_name;
  dbms_output.put_line(first_name);
end;

Here I defined a simple procedure with the parameter “first_name” to be in and out. That means I cannot only use this variable inside the procedure but also change a value of a referenced argument.

Comments