Fórum Script #40522

CREATE DATABASE ´ALTAROCHA:C:\Documents and Settings\Marcos Fernando\Desktop\Brasil Sistemas\Agenda\BRAGENDA.FDB´ PAGE_SIZE 8192 user SYSDBA password ´masterkey´

DEFAULT CHARACTER SET NONE;

/* Table: CLIENTES, Owner: SYSDBA */

CREATE TABLE ´CLIENTES´
(
´INDICE´INTEGER NOT NULL,
´ENDERECO´VARCHAR(40),
´BAIRRO´VARCHAR(20),
´CEP´VARCHAR(10),
´EMAIL´VARCHAR(40),
´URL´VARCHAR(100),
´UF´VARCHAR(2),
´NOME´VARCHAR(60),
´TELEFONE´VARCHAR(40),
´OBS´VARCHAR(255),
´CELULAR´VARCHAR(20),
´CONTATO´VARCHAR(20),
´ESTADO´VARCHAR(50),
´CIDADE´VARCHAR(50),
´FAX´VARCHAR(20),
CONSTRAINT ´PK_INDICE´ PRIMARY KEY (´INDICE´)
);

/* Table: COMPROMISSOS, Owner: SYSDBA */

CREATE TABLE ´COMPROMISSOS´
(
´INDICE´INTEGER NOT NULL,
´DESCRICAO´VARCHAR(50),
´DATA´TIMESTAMP,
´USUARIO´INTEGER NOT NULL,
´LEMBREI´INTEGER
);

/* Table: FIREMPRESA, Owner: SYSDBA */

CREATE TABLE ´FIREMPRESA´
(
´INDICE´INTEGER,
´EMPRESA´VARCHAR(60),
´ENDERECO´VARCHAR(50),
´CIDADE´VARCHAR(54),
´UF´VARCHAR(2),
´CNPJCFP´VARCHAR(18),
´FONEFAX´VARCHAR(30),
´ISCRICAOESTADUAL´VARCHAR(20),
´LOCALEMPRESA´VARCHAR(6),
´LOGO´BLOB SUB_TYPE 0 SEGMENT SIZE 80,
´HD´VARCHAR(10),
´DATAINICIAL´TIMESTAMP,
´DATAFINAL´TIMESTAMP,
´OPERADOR´VARCHAR(15),
´RESPONSAVEL´VARCHAR(15),
´SERIAL´VARCHAR(8)
);

/* Table: LIGACOES, Owner: SYSDBA */

CREATE TABLE ´LIGACOES´
(
´INDICE´INTEGER,
´DATA´TIMESTAMP,
´NTELEFONE´VARCHAR(20),
´PESSOA´VARCHAR(30),
´DESTINO´VARCHAR(20),
´FINALIDADE´VARCHAR(30),
´OBS´VARCHAR(70)
);

/* Table: USUARIO, Owner: SYSDBA */

CREATE TABLE ´USUARIO´
(
´INDICE´INTEGER,
´USUARIO´VARCHAR(30),
´LOGIN´VARCHAR(10),
´SENHA´VARCHAR(6)
);

CREATE GENERATOR ´GERAR_AGENDAR´;
CREATE GENERATOR ´GERAR_CONTATOS´;
CREATE GENERATOR ´GERAR_LIGACOES´;
CREATE GENERATOR ´GERAR_USUARIO´;

/* View: VCONTATOS, Owner: SYSDBA */

CREATE VIEW ´VCONTATOS´ (
´INDICE´,
´ENDERECO´,
´BAIRRO´,
´CEP´,
´EMAIL´,
´URL´,
´UF´,
´NOME´,
´TELEFONE´,
´OBS´,
´CELULAR´,
´CONTATO´,
´ESTADO´,
´CIDADE´,
´FAX´
) AS

SELECT INDICE, ENDERECO, BAIRRO, CEP, EMAIL,
URL,
UF,
NOME,
TELEFONE,
OBS,
CELULAR,
CONTATO, ESTADO, CIDADE, FAX
FROM clientes
;

/* View: VCOMPROMISSOS, Owner: SYSDBA */

CREATE VIEW ´VCOMPROMISSOS´ (
´INDICE´,
´DESCRICAO´,
´DATA´,
´USUARIO´,
´LEMBREI´
) AS

SELECT INDICE, DESCRICAO, DATA, USUARIO, LEMBREI
FROM COMPROMISSOS
;

/* View: VLIGACOES, Owner: SYSDBA */

CREATE VIEW ´VLIGACOES´ (
´INDICE´,
´DATA´,
´NTELEFONE´,
´PESSOA´,
´DESTINO´,
´FINALIDADE´,
´OBS´
) AS

SELECT INDICE, DATA, NTELEFONE, PESSOA, DESTINO, FINALIDADE, OBS
FROM LIGACOES
;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE ´DELETE_COMPROMISSOS´
(
´COD_PORJ´ INTEGER
)
RETURNS
(
´INDICE´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´DELETE_CONTATOS´
(
´COD_PORJ´ INTEGER
)
RETURNS
(
´INDICE´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´DELETE_LIGACOES´
(
´COD_PORJ´ INTEGER
)
RETURNS
(
´INDICE´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´GERAR_COD_AGENDAR´
RETURNS
(
´ATUAL´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´GERAR_COD_CONTATOS´
RETURNS
(
´ATUAL´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´GERAR_COD_USUARIO´
RETURNS
(
´ATUAL´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´GERAR_LIGACOES´
RETURNS
(
´ATUAL´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´INSERT_COMPROMISSOS´
(
´INDICE´ INTEGER,
´DESCRICAO´ VARCHAR(50),
´DATA´ TIMESTAMP,
´USUARIO´ INTEGER,
´LEMBREI´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´INSERT_CONTATOS´
(
´INDICE´ INTEGER,
´ENDERECO´ VARCHAR(40),
´BAIRRO´ VARCHAR(20),
´CEP´ VARCHAR(10),
´EMAIL´ VARCHAR(40),
´URL´ VARCHAR(100),
´UF´ VARCHAR(2),
´NOME´ VARCHAR(60),
´TELEFONE´ VARCHAR(40),
´OBS´ VARCHAR(255),
´CELULAR´ VARCHAR(20),
´CONTATO´ VARCHAR(20),
´ESTADO´ VARCHAR(50),
´CIDADE´ VARCHAR(50),
´FAX´ VARCHAR(20)
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´INSERT_LIGACOES´
(
´INDICE´ INTEGER,
´DATA´ TIMESTAMP,
´NTELEFONE´ VARCHAR(20),
´PESSOA´ VARCHAR(30),
´DESTINO´ VARCHAR(20),
´FINALIDADE´ VARCHAR(30),
´OBS´ VARCHAR(70)
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´UPDATE_COMPROMISSOS´
(
´INDICE´ INTEGER,
´DESCRICAO´ VARCHAR(50),
´DATA´ TIMESTAMP,
´USUARIO´ INTEGER,
´LEMBREI´ INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´UPDATE_CONTATOS´
(
´INDICE´ INTEGER,
´ENDERECO´ VARCHAR(40),
´BAIRRO´ VARCHAR(20),
´CEP´ VARCHAR(10),
´EMAIL´ VARCHAR(40),
´URL´ VARCHAR(100),
´UF´ VARCHAR(2),
´NOME´ VARCHAR(60),
´TELEFONE´ VARCHAR(40),
´OBS´ VARCHAR(255),
´CELULAR´ VARCHAR(20),
´CONTATO´ VARCHAR(20),
´ESTADO´ VARCHAR(50),
´CIDADE´ VARCHAR(50),
´FAX´ VARCHAR(20)
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE ´UPDATE_LIGACOES´
(
´INDICE´ INTEGER,
´DATA´ TIMESTAMP,
´NTELEFONE´ VARCHAR(20),
´PESSOA´ VARCHAR(30),
´DESTINO´ VARCHAR(20),
´FINALIDADE´ VARCHAR(30),
´OBS´ VARCHAR(70)
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE ´DELETE_COMPROMISSOS´
(
´COD_PORJ´ INTEGER
)
RETURNS
(
´INDICE´ INTEGER
)
AS
begin
BEGIN
DELETE FROM COMPROMISSOS
WHERE INDICE = :COD_PORJ;
END
end
^


ALTER PROCEDURE ´DELETE_CONTATOS´
(
´COD_PORJ´ INTEGER
)
RETURNS
(
´INDICE´ INTEGER
)
AS
begin
BEGIN
DELETE FROM CLIENTES
WHERE INDICE = :COD_PORJ;
END
end
^


ALTER PROCEDURE ´DELETE_LIGACOES´
(
´COD_PORJ´ INTEGER
)
RETURNS
(
´INDICE´ INTEGER
)
AS
begin
BEGIN
DELETE FROM LIGACOES
WHERE INDICE = :COD_PORJ;
END
end
^


ALTER PROCEDURE ´GERAR_COD_AGENDAR´
RETURNS
(
´ATUAL´ INTEGER
)
AS
begin
ATUAL = GEN_ID(GERAR_USUARIO,1);
SUSPEND;
end
^


ALTER PROCEDURE ´GERAR_COD_CONTATOS´
RETURNS
(
´ATUAL´ INTEGER
)
AS
begin
ATUAL = GEN_ID(GERAR_CONTATOS,1);
SUSPEND;
end
^


ALTER PROCEDURE ´GERAR_COD_USUARIO´
RETURNS
(
´ATUAL´ INTEGER
)
AS
begin
ATUAL = GEN_ID(GERAR_USUARIO,1);
SUSPEND;
end
^


ALTER PROCEDURE ´GERAR_LIGACOES´
RETURNS
(
´ATUAL´ INTEGER
)
AS
begin
ATUAL = GEN_ID(GERAR_LIGACOES,1);
SUSPEND;
end
^


ALTER PROCEDURE ´INSERT_COMPROMISSOS´
(
´INDICE´ INTEGER,
´DESCRICAO´ VARCHAR(50),
´DATA´ TIMESTAMP,
´USUARIO´ INTEGER,
´LEMBREI´ INTEGER
)
AS
begin
insert into COMPROMISSOS (INDICE,DESCRICAO, DATA, USUARIO, LEMBREI)
values
(:INDICE, :DESCRICAO, :DATA, :USUARIO, :LEMBREI);
suspend;
end
^


ALTER PROCEDURE ´INSERT_CONTATOS´
(
´INDICE´ INTEGER,
´ENDERECO´ VARCHAR(40),
´BAIRRO´ VARCHAR(20),
´CEP´ VARCHAR(10),
´EMAIL´ VARCHAR(40),
´URL´ VARCHAR(100),
´UF´ VARCHAR(2),
´NOME´ VARCHAR(60),
´TELEFONE´ VARCHAR(40),
´OBS´ VARCHAR(255),
´CELULAR´ VARCHAR(20),
´CONTATO´ VARCHAR(20),
´ESTADO´ VARCHAR(50),
´CIDADE´ VARCHAR(50),
´FAX´ VARCHAR(20)
)
AS
begin
insert into CLIENTES (INDICE,ENDERECO,BAIRRO,CEP,EMAIL,URL,UF,NOME,TELEFONE,OBS,CELULAR,CONTATO, ESTADO, CIDADE, FAX)
values
(:INDICE,:ENDERECO,:BAIRRO,:CEP,:EMAIL,:URL,:UF,:NOME,:TELEFONE,:OBS,:CELULAR,:CONTATO, :ESTADO, :CIDADE, :FAX);
suspend;
end
^


ALTER PROCEDURE ´INSERT_LIGACOES´
(
´INDICE´ INTEGER,
´DATA´ TIMESTAMP,
´NTELEFONE´ VARCHAR(20),
´PESSOA´ VARCHAR(30),
´DESTINO´ VARCHAR(20),
´FINALIDADE´ VARCHAR(30),
´OBS´ VARCHAR(70)
)
AS
begin
insert into LIGACOES (INDICE, DATA, NTELEFONE, PESSOA, DESTINO, FINALIDADE, OBS)
values
(:INDICE, :DATA, :NTELEFONE, :PESSOA, :DESTINO, :FINALIDADE, :OBS);
suspend;
end
^


ALTER PROCEDURE ´UPDATE_COMPROMISSOS´
(
´INDICE´ INTEGER,
´DESCRICAO´ VARCHAR(50),
´DATA´ TIMESTAMP,
´USUARIO´ INTEGER,
´LEMBREI´ INTEGER
)
AS
begin
BEGIN
UPDATE COMPROMISSOS SET
INDICE=:INDICE,
DESCRICAO=:DESCRICAO,
DATA=:DATA,
USUARIO=:USUARIO,
LEMBREI=:LEMBREI
WHERE INDICE = :INDICE;
END
end
^


ALTER PROCEDURE ´UPDATE_CONTATOS´
(
´INDICE´ INTEGER,
´ENDERECO´ VARCHAR(40),
´BAIRRO´ VARCHAR(20),
´CEP´ VARCHAR(10),
´EMAIL´ VARCHAR(40),
´URL´ VARCHAR(100),
´UF´ VARCHAR(2),
´NOME´ VARCHAR(60),
´TELEFONE´ VARCHAR(40),
´OBS´ VARCHAR(255),
´CELULAR´ VARCHAR(20),
´CONTATO´ VARCHAR(20),
´ESTADO´ VARCHAR(50),
´CIDADE´ VARCHAR(50),
´FAX´ VARCHAR(20)
)
AS
begin
BEGIN
UPDATE CLIENTES SET
INDICE=:INDICE,
ENDERECO=:ENDERECO,
BAIRRO=:BAIRRO,
CEP=:CEP,
EMAIL=:EMAIL,
URL=:URL,
UF=:UF,
NOME=:NOME,
TELEFONE=:TELEFONE,
OBS=:OBS,
CELULAR=:CELULAR,
CONTATO=:CONTATO,
ESTADO=:ESTADO,
CIDADE=:CIDADE,
FAX=:FAX
WHERE INDICE = :INDICE;
END
end
^


ALTER PROCEDURE ´UPDATE_LIGACOES´
(
´INDICE´ INTEGER,
´DATA´ TIMESTAMP,
´NTELEFONE´ VARCHAR(20),
´PESSOA´ VARCHAR(30),
´DESTINO´ VARCHAR(20),
´FINALIDADE´ VARCHAR(30),
´OBS´ VARCHAR(70)
)
AS
begin
BEGIN
UPDATE LIGACOES SET
INDICE=:INDICE,
DATA=:DATA,
NTELEFONE=:NTELEFONE,
PESSOA=:PESSOA,
DESTINO=:DESTINO,
FINALIDADE=:FINALIDADE,
OBS=:OBS
WHERE INDICE = :INDICE;
END
end
^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

/* Grant Roles for this database */


/* Grant permissions for this database */




no proprio Interbase tem um arquivo chamado EMPLOYEE.GDB acesse esse arquivo e vc terá algums exeplos