Interested to Buy Any Domain ? << Click Here >> for more details...
Answer Posted / anjurose
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>;
| Is This Answer Correct ? | 4 Yes | 1 No |
Post New Answer View All Answers
What are the restraints imposed on the table design by a merge replication?
Can we write ddl in trigger?
How can you know if the row fetched from cursor is still valid in underlying table?
How will you know when statistics on a table are obsolete?
How to verify the port number of the sql server?
How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
What is the cartesian product of the table?
You want to implement the one-to-many relationship while designing tables. How would you do it?
Can one drop a column from a table?
What are various ways to enhance the ssrs report?
Explain the difference between HTTP and HTTPS in database?
Explain view in sql server?
What are the differences between decimal and float in ms sql server?
Should you normalize audio?
Can you tell me some of the common replication dmv's and their use? : sql server replication
