การเขียน Store Procedure

posted on 20 Jul 2004 17:36 by somkiat
Procedures

PL/SQL procedures behave very much like procedures in other programming language. Here is an example of a PL/SQL procedure addtuple1 that, given an integer i, inserts the tuple (i, 'xxx') into the following example relation:

CREATE TABLE T2 (
    a INTEGER,
    b CHAR(10)
);

CREATE PROCEDURE addtuple1(i IN NUMBER) AS
BEGIN
    INSERT INTO T2 VALUES(i, 'xxx');
END addtuple1;
.
run;
A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its parameters. An option is to follow CREATE by OR REPLACE. The advantage of doing so is that should you have already made the definition, you will not get an error. On the other hand, should the previous definition be a different procedure of the same name, you will not be warned, and the old procedure will be lost.

There can be any number of parameters, each followed by a mode and a type. The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write). Note: Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a parameter declaration must be unconstrained. For example, CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter depends on the corresponding argument that is passed in when the procedure is invoked.

Following the arguments is the keyword AS (IS is a synonym). Then comes the body, which is essentially a PL/SQL block. We have repeated the name of the procedure after the END, but this is optional. However, the DECLARE section should not start with the keyword DECLARE. Rather, following AS we have:

... AS
<local_var_declarations>
BEGIN
    <procedure_body>
END;
.
run;
The run at the end runs the statement that creates the procedure; it does not execute the procedure. To execute the procedure, use another PL/SQL statement, in which the procedure is invoked as an executable statement. For example:
BEGIN addtuple1(99); END;
.
run;
The following procedure also inserts a tuple into T2, but it takes both components as arguments:
CREATE PROCEDURE addtuple2(
    x T2.a%TYPE,
    y T2.b%TYPE)
AS
BEGIN
    INSERT INTO T2(a, b)
    VALUES(x, y);
END addtuple2;
.
run;
Now, to add a tuple (10, 'abc') to T2:
BEGIN
    addtuple2(10, 'abc');
END;
.
run;
The following illustrates the use of an OUT parameter:
CREATE TABLE T3 (
    a INTEGER,
    b INTEGER
);

CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)
AS
BEGIN
    b := 4;
    INSERT INTO T3 VALUES(a, b);
END;
.
run;

DECLARE
    v NUMBER;
BEGIN
    addtuple3(10, v);
END;
.
run;
Note that assigning values to parameters declared as OUT or INOUT causes the corresponding input arguments to be written. Because of this, the input argument for an OUT or INOUT parameter should be something with an "lvalue", such as a variable like v in the example above. A constant or a literal argument should not be passed in for an OUT/INOUT parameter.

We can also write functions instead of procedures. In a function declaration, we follow the parameter list by RETURN and the type of the return value:

CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ...
In the body of the function definition, "RETURN <expression>;" exits from the function and returns the value of <expression>.

To find out what procedures and functions you have created, use the following SQL query:

select object_type, object_name
from user_objects
where object_type = 'PROCEDURE'
   or object_type = 'FUNCTION';
To drop a stored procedure/function:
drop procedure <procedure_name>;
drop function <function_name>;

Comment

Comment:

Tweet

2015-06-25song
ray bans
hollister kids clothing
ray bans
coach factory outlet
michael kors
coach outlet
gucci bags
cheap air max
tory burch handbags
coach factory
replica watches rolex
kate spade bags
ray ban sunglasses
tory burch outlet online
oakley sunglasses wholesale
true religion
michael kors uk outlet
ralph lauren polo shirts
cheap jordans free shipping
pandora bracelets
burberry outlet online
pandora charms sale clearance
cheap oakley sunglasses
ray ban sunglasses uk
cheap soccer jerseys
coach outlet store online
coach outlet store online
air max 2015
burberry scarf
kate spade handbags
toms shoes sale
ray ban sunglasses
oakley sunglasses outlet
true religion
oakley sunglasses
burberry handbags
christian louboutin
kate spade outlet
kate spade
coach outlet
ray ban outlet
polo outlet
coach outlet
prada outlet store
ray ban outlet
oakley outlet store
michael kors
michael kors handbags
ray-ban sunglasse
michael kors outlet
fitflops shoes
michael kors handbags
pandora jewelry outlet
gucci outlet
michael kors outlet
michael kors watches
hollister outlet
true religion outlet
oakley sunglasses
cheap ray bans
abercrombie kids
michael kors
prada shoes
polo ralph lauren uk
gucci handbags
chanel outlet
gucci
cheap ray ban sunglasses
true religion jeans outlet
mont blanc
kate spade
kate spade outlet
abercrombie and fitch
coach outlet online
michael kors outlet online
tory burch outlet
tory burch shoes
polo ralph lauren
football jerseys
true religion jeans
retro jordans
michael kors handbags
pandora jewelry stores
burberry outlet
oakley sunglasses sale
coach outlet
coach factory outlet
rolex watches
replica watches for sale
oakley sunglasses outlet
true religion sale
oakley sunglasses outlet
ralph lauren sale
coach outlet store online
coach outlet store online
jordans for sale
air max uk
oakley sunglasses cheap
ralph lauren uk sale
ray ban glasses
michael kors bags
coach factory outlet
pandora charms
true religion sale
michael kors handbags
ray bans