BO and Stored Procedures

thickbugΛογισμικό & κατασκευή λογ/κού

28 Οκτ 2013 (πριν από 3 χρόνια και 8 μήνες)

69 εμφανίσεις

BO and Stored Procedures

Vanderbilt MIS

Vanderbilt University

Overview


Create a BO report by:


Stored Procedure Option


Free
-
hand SQL Option


Stored Procedure requirements


Why use a stored procedures?


Pros


Cons


Questions

Vanderbilt University

Data Access

Vanderbilt University

Select database**

Vanderbilt University

Select Procedure

Vanderbilt University

Fill Parameters

Vanderbilt University

Results

Vanderbilt University

Cannot select a new connection

Vanderbilt University

Free
-
hand SQL call to Stored Procedure

Vanderbilt University

Allows connection change

Vanderbilt University

SQL code block

begin

get_users( @Prompt('USERID','A',,mono,free), :cbo);

end;


':cbo' is the variable to represent the REF CURSOR.

Vanderbilt University

Requirements to access stored procedures
from BO


Create package that defines the REF CURSOR. This
REF CURSOR must be strongly bound to a static
pre
-
defined structure.


The procedure must have a parameter that is a REF
CURSOR type.


The REF CURSOR parameter must be defined as IN
OUT (read/write mode).


The stored procedure can only return one ResultSet.

Vanderbilt University

Simple Package

1
--

Package defining ref cursor

2
PACKAGE

BO_PACKAGE
is

3

4
--

Define the record for the

5 cursor returning the results

6
TYPE

BO_REC
IS

RECORD

(

7 userid varchar2(10),

8 first_name varchar2(20),

9 last_name varchar2(20));

11

12
--

Define a ref cursor that is

13 Defined by the record

14
TYPE

BO_CUR
IS

REF

CURSOR

15
RETURN

BO_REC;

16

17
END

BO_PACKAGE;

Vanderbilt University

Requirements to access stored procedures
from BO


Create package that defines the REF CURSOR. This
REF CURSOR must be strongly bound to a static
pre
-
defined structure.


The procedure must have a parameter that is a REF
CURSOR type.


The REF CURSOR parameter must be defined as IN
OUT (read/write mode).


The stored procedure can only return one ResultSet.

Vanderbilt University

Simple Stored Procedure

1

--

Stored Procedure to select Users

2
PROCEDURE

GET_USERS(userId varchar2,

3 ResultSet1
IN OUT

BO_PACKAGE.BO_CUR)

4
is

5

6
BEGIN

7
IF

userId = 'ALL'
THEN

8
OPEN

ResultSet1
for

9
SELECT

user_id, first_name, last_name

10
FROM

USERS;

11
ELSE

12
OPEN

ResultSet1
for

13
SELECT

user_id, first_name, last_name

14
FROM

USERS

15
WHERE
user_id = userId;

16
END

IF
;

17
end

GET_USERS;

Vanderbilt University

Two SP Data Providers

Vanderbilt University

Comparing Data Providers

Vanderbilt University

Modifying Object types

Vanderbilt University

Modifying Object types

Vanderbilt University


Pros Cons



No Universe



Isolate SQL changes



Use variety of developer
skills



Flexible data manipulation



Speed





Stored Procedure: Cannot
change data source



Linking is not automatic



Coding Multi
-
Value prompts


Vanderbilt University

Questions



Vanderbilt University

Completed SP

1
--

Package defining ref cursor

2
PACKAGE

BO_PACKAGE
is

3

4
--

Define the record for the

5 cursor returning the results

6
TYPE

BO_REC
IS

RECORD

(

7 userid varchar2(10),

8 first_name varchar2(20),

9 last_name varchar2(20));

11

12
--

Define a ref cursor that is

13 Defined by the record

14
TYPE

BO_CUR
IS

REF

CURSOR

15
RETURN

BO_REC;

16

17
END

BO_PACKAGE;

--

Stored Procedure to select Users

PROCEDURE

GET_USERS(userId varchar2,


ResultSet1
IN OUT

BO_PACKAGE.BO_CUR)

is


BEGIN


IF

userId = 'ALL'
THEN


OPEN

ResultSet1
for


SELECT

user_id, first_name, last_name


FROM

USERS;


ELSE


OPEN

ResultSet1
for


SELECT

user_id, first_name, last_name


FROM

USERS


WHERE
user_id = userId;


END

IF
;

end

GET_USERS;



Vanderbilt University

Definitions


Stored Procedure: sets of SQL statements that
are saved as executable files in a database.


REF Cursor: a pointer to a work area in which
a result set is stored



Vanderbilt University

Contact

Aldrea Vertison

aldrea.vertison@vanderbilt.edu