​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Calling PL/SQL code from SQL*PLUS and embedded C/SQL 


Its possible to use native PL/SQL within the SQL*PLUS environment as can be seen in the following example:-

SQL> variable nr_rows number;
SQL> begin
2        delete from mytable where emp_no = 999;
3        :nr_rows := sql%rowcount;
4    end;
5   
/


PL/SQL procedure successfully completed.


SQL> print nr_rows;

NR_ROWS
-------
999


Another way is to create a package, function or procedure like this:-

SQL> set serveroutput on;
SQL> create or replace procedure testproc(col_val in number) as
2    begin
3        delete from mytable where emp_no = col_val;
4        if sql%rowcount = 0 then
5            dbms_output.put_line('No rows deleted');
6        else
7            dbms_output.put_line(sql%rowcount || ' rows deleted');
8        end if;
9    end testproc;
10   /


If your procedure does not compile properly a useful command to use is show errors to see why. Once compiled properly run the procedure using:-

SQL> execute procedure testproc(999);
2    /


The following explicit cursor attributes are available to PL/SQL.

SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
SQl%ROWCOUNT

e.g

1)update testtab set sal = sal*1.1 where emp_name = 'HARRY';
      if sql%notfound
then
insert into testtab(name,sal) values(harry,1000);
end if;

2)delete from testtab where name = 'HARRY'
if sql%rowcount > 100
then
raise too_many_deletes;
end if;
exception
when too_many_deletes then
rollback;



PL/SQL blocks can also be run from embedded C/SQL programs by enclosing the block within EXEC SQL EXECUTE/END-EXEC statements. In general, performance can be increased through the use of embedded PL/SQL because it helps reduce communication between the app and the database by being able to send entire SQL blocks to the server rather than one at a time as with embedded C/SQL alone. Here's a short example:-

#include <stdio.h>
exec sql include sqlca;

/***************************************
NB varchar is a pseudotype and varchar empname[20] for instance is
equivalent to the C declaration

struct
{
unsigned short len;
unsigned char arr[20];
}
***************************************/
exec sql begin declare section;
varchar job_type[9];
int salary;
varchar empname[20];
exec sql end declare section;

main()
{
printf("Enter employee Name: ");
gets(empname.arr);
exec sql connect "myuser" identified by "mypass";



EXEC SQL EXECUTE/* PL/SQL block starts here */
BEGIN
select job,sal
into :job_type,:salary
from employee
where ename = UPPER(:empname.arr);
END;

END-EXEC;/* PL/SQL block ends here */

printf("Job type = %s\n",job_type.arr);
printf("salary = %d\n",job_type.salary);

}