Just some updates on PLSQL

From time to time, I just repeat old exercises. I do this to maintain my knowledge before it gets lost in the mist of time. One such areas is PLSQL. You never forget that PL SQL is used to maintain data on an Oracle database. But I do forget tiny details such as how to insert a parameter.
I like to insert the PLSQL code in the client tool SQL Developper. This tool clearly shows where the code is created. It also shows how the PLSQL functions and procedures are related to other database objects like schemes, tables and views.

Let me show the example. I created one procedure that calls a function. The function looks like:

create or replace PROCEDURE MAIN AS 
  terug int;
BEGIN
  dbms_output.put_line(' Hello World');
  terug := test(110221);
END MAIN;

This procedure is quite simple. It writes something to screen (‘Hello World’) and it calls a function with a parameter (110221). It can be invoked from the command line by the command “exec main”. in this case “main” is the name of the procedure and “exec” indicates that a procedure must be run.
Starting this from sqlplus looks like:

We see that ‘Hello World’ is returned.
The procedure calls a function. This function looks like:

create or replace function TEST
 (
   INVOER IN INT
 ) RETURN INT AS
 v_name            HR.CUSTOMER_IND.CURPNM%TYPE;
 n_table           INT;
 teller            INT:=0;
 l_uitvoer         VARCHAR(100);
 p_table_name      VARCHAR(100);
 stmt              VARCHAR(250);
 naam              VARCHAR(100);
 CURSOR cur_chief
 IS
   select curptw into v_name
   from CUSTOMER_IND where CUNOCU=invoer;
 r_chief cur_chief%ROWTYPE;
 BEGIN
   p_table_name :='ff';
   SELECT COUNT(*) INTO n_table FROM user_tables WHERE table_name = UPPER(p_table_name);
   IF (n_table = 0) THEN
     EXECUTE IMMEDIATE 'create table ' || p_table_name || '(teller int, naam varchar(50))';
   else
     EXECUTE IMMEDIATE 'truncate table ' || p_table_name;
   END IF;
   OPEN cur_chief;
   LOOP
      teller := teller + 1;
      FETCH cur_chief INTO r_chief;
      EXIT WHEN cur_chief%NOTFOUND;
      naam := r_chief.CURPTW;
      stmt := 'insert into '||p_table_name || ' (teller, naam) values(' || teller ||','''||naam||''')';
      DBMS_OUTPUT.PUT_LINE(stmt);
      EXECUTE IMMEDIATE stmt;
   END LOOP;
   RETURN 1;
 END TEST;

This function contains some ideas that I like. The first idea is the creation of a cursor that contains an outcome set. This cursor is based on a table for which the code is given here. This outcome is made dependent on a parameter. The set of outcomes will be translated into a series of records that will be translated one by one. Such a record is indicated by variable “r_chief”.
Another trick is a check whether a certain table already exists. This is done by checking the user_tables. If it is already present, the count will be larger than 0. In that case a command will be executed that truncates the table.
A final trick is looping through the cursor. Each loop writes one record to “r_chief”. From that record, some values are retrieved. The values are then stored in an insert statement. The most tricky part is getting an apostrophe around the value of a string. The apostrophe is also used to terminate a string in the PLSQL code.