Who Can Create a Procedure
The CREATE PROCEDURE
statement can be executed by:
-
Users with the
CREATE PROCEDURE
privilege
The user executing the CREATE PROCEDURE
statement becomes the owner of the table.
The CREATE PROCEDURE
statement can be executed by:
Users with the CREATE PROCEDURE
privilege
The user executing the CREATE PROCEDURE
statement becomes the owner of the table.
Creating a stored procedure that inserts a record into the BREED
table and returns the code of the inserted record:
CREATE PROCEDURE ADD_BREED (
NAME D_BREEDNAME, /* Domain attributes are inherited */
NAME_EN TYPE OF D_BREEDNAME, /* Only the domain type is inherited */
SHORTNAME TYPE OF COLUMN BREED.SHORTNAME,
/* The table column type is inherited */
REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
CODE_BREED INT
)
AS
BEGIN
INSERT INTO BREED (
CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
VALUES (
:CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
RETURNING CODE_BREED INTO CODE_BREED;
END
Creating a selectable stored procedure that generates data for mailing labels (from employee.fdb
):
CREATE PROCEDURE mail_label (cust_no INTEGER)
RETURNS (line1 CHAR(40), line2 CHAR(40), line3 CHAR(40),
line4 CHAR(40), line5 CHAR(40), line6 CHAR(40))
AS
DECLARE VARIABLE customer VARCHAR(25);
DECLARE VARIABLE first_name VARCHAR(15);
DECLARE VARIABLE last_name VARCHAR(20);
DECLARE VARIABLE addr1 VARCHAR(30);
DECLARE VARIABLE addr2 VARCHAR(30);
DECLARE VARIABLE city VARCHAR(25);
DECLARE VARIABLE state VARCHAR(15);
DECLARE VARIABLE country VARCHAR(15);
DECLARE VARIABLE postcode VARCHAR(12);
DECLARE VARIABLE cnt INTEGER;
BEGIN
line1 = '';
line2 = '';
line3 = '';
line4 = '';
line5 = '';
line6 = '';
SELECT customer, contact_first, contact_last, address_line1,
address_line2, city, state_province, country, postal_code
FROM CUSTOMER
WHERE cust_no = :cust_no
INTO :customer, :first_name, :last_name, :addr1, :addr2,
:city, :state, :country, :postcode;
IF (customer IS NOT NULL) THEN
line1 = customer;
IF (first_name IS NOT NULL) THEN
line2 = first_name || ' ' || last_name;
ELSE
line2 = last_name;
IF (addr1 IS NOT NULL) THEN
line3 = addr1;
IF (addr2 IS NOT NULL) THEN
line4 = addr2;
IF (country = 'USA') THEN
BEGIN
IF (city IS NOT NULL) THEN
line5 = city || ', ' || state || ' ' || postcode;
ELSE
line5 = state || ' ' || postcode;
END
ELSE
BEGIN
IF (city IS NOT NULL) THEN
line5 = city || ', ' || state;
ELSE
line5 = state;
line6 = country || ' ' || postcode;
END
SUSPEND; -- the statement that sends an output row to the buffer
-- and makes the procedure "selectable"
END
With DEFINER
set for procedure p
, user US
needs only the EXECUTE
privilege on p
.If it were set for INVOKER
, either the user or the procedure would also need the INSERT
privilege on table t
.
set term ^;
create procedure p (i integer) SQL SECURITY DEFINER
as
begin
insert into t values (:i);
end^
set term ;^
grant execute on procedure p to user us;
commit;
connect 'localhost:/tmp/17.fdb' user us password 'pas';
execute procedure p(1);