Writing oracle procedure.

You can write a stored procedure in oracle using any editor. I use sql developer by oracle. I am very new to write procedures in oracle. I found it's syntax way different than the syntax of MySql. I am fond fo opensource projects because of the support and simplicity of the solutions it has. Mysql has very simple ways to extacts rows, have lost's of in built funtions to use which makes it simple to use. After writting few oracle procedures I found how easy and simple mysql is compared to oracle in syntax. Anyway, Oracle has it's own advantage, and I like learning new stuff and the way to write oracle procedure. Here are 2 common requirement which I faced while writting a procedure in oracle.

1) situations where you want to limit number of resultset in range (10-20) in oracle. In mysql you have limit caluse where you simply say

limit start, count

in your query and it's done, but in oracle you don't have this clause.

here is how you go about the same.

suppose you have table emp with coulumn id,name,sal.

CREATE OR REPLACE
PROCEDURE GETEMPLISTING(
var_name VARCHAR2,
var_limit_start IN INTEGER,
var_limit_end IN INTEGER,
page_cur out SYS_REFCURSOR)

AS
var_data_query varchar2(4000) default '';
BEGIN
var_data_query := 'select *
from
( select rownum rnum, a.*
from (SELECT id,name,sal FROM emp WHERE name LIKE ''%'||var_name||'%'') a
where rownum <= '|| var_limit_end ||')
where rnum >= '|| var_limit_start ||'';

OPEN page_cur FOR
var_data_query

2) you want same data but based on ids using IN caluse, In Oracle this is how I've acheived the same.

Declare a new data type

create or replace TYPE "MYNUMTABLE" as table of number;

Declare a function num_list

create or replace FUNCTION "NUM_LIST"
( p_str in varchar2,
p_delim in varchar2 default ',')
return myNumTable
as
l_data myNumTable := myNumTable();
l_str long default p_str || p_delim;
l_n number;
begin
loop
exit when l_str is null;
l_data.extend;
l_n := instr( l_str, p_delim );
l_data( l_data.count ) := substr( l_str, 1, l_n-1 );
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;

now write a procedure

CREATE OR REPLACE
PROCEDURE GETEMPLISTING(
var_ids VARCHAR2,
page_cur out SYS_REFCURSOR)

AS
BEIGIN
OPEN page_cur FOR
SELECT id,name,sal FROM emp WHERE id IN(SELECT * FROM THE
( SELECT CAST( num_list(p_users_id) AS myNumTable
)
FROM dual) a
);

now you can call procedure to get the result expected. page_ids parameter has to be id seperated by comma (,) in this case for example parameter could be 1,2,5,7. which in reutrn shuld give records of the respected emplyee 1,2,5,7.


Well that all for the day. Will cover next learning soon.