/******************************************************************************/
/****          Generated by IBExpert 2009.01.19 3/2/2009 16:23:36          ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1252;

SET CLIENTLIB 'C:\Arquivos de programas\Firebird\Firebird_2_0\bin\fbclient.dll';

CREATE DATABASE 'c:\temp\teste.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1252;

/******************************************************************************/
/****                              Exceptions                              ****/
/******************************************************************************/
CREATE EXCEPTION EXC_ERROR '-General Error!-';



SET TERM ^ ; 

/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/
CREATE OR ALTER PROCEDURE P_TEST (
    TEMP SMALLINT)
AS
BEGIN
  EXIT;
END^

SET TERM ; ^


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/
CREATE TABLE COUNTRIES (
    ID    CHAR(3) CHARACTER SET WIN1252 NOT NULL,
    NAME  VARCHAR(100) CHARACTER SET WIN1252 NOT NULL
);

/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/
ALTER TABLE COUNTRIES ADD CONSTRAINT PK_COUNTRIES PRIMARY KEY (ID);


/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/
SET TERM ^ ;

CREATE OR ALTER PROCEDURE P_TEST (
    TEMP SMALLINT)
AS
begin
  begin
     if (TEMP < 0) then
        exception EXC_ERROR 'VARIABLE < ZERO';

     if (exists (select * from COUNTRIES WHERE ID = '111')) then
        update COUNTRIES set NAME = 'VARIABLE OK.' where ID = '111';
     else
        insert into COUNTRIES (ID, NAME) values ('111','VARIABLE OK.');

     when any do
     begin
        if (exists (select * from COUNTRIES WHERE ID = '111')) then
          update COUNTRIES set NAME = 'VARIABLE < ZERO' where ID = '111';
        else
          insert into COUNTRIES (ID, NAME) values ('111','VARIABLE < ZERO');
        exception;
     end
  end
end^


SET TERM ; ^

/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/

/* Privileges of procedures */
GRANT SELECT, INSERT, UPDATE ON COUNTRIES TO PROCEDURE P_TEST;
