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;
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;
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.