Post on 21-Jan-2016
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Luís Fernando Berti Santos
Consultor BC
Luis.santos@procwork.com.br
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 1 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
1) Passos para a criação no SAP de tabelas de acesso
externo:
Criar e ativar a tabela no SAP, normalmente.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 2 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
As opções técnicas não devem ser alteradas:
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 3 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Após ativada, a tabela deve ser excluída do banco de dados,
existindo somente, no dicionário SAP:
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 4 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
2) Conversão entre tipos:
TIPO SAP TIPO BASE EXTERNAP (DEC) NumericN, CHAR DATS, TIME VarcharI Integer
Com esse modelo, não teremos problema algum com a
conversão de data-types entre os ambientes. Convém ressaltar que
NÃO devemos usar os tipos específicos do SAP, para não termos
que nos preocupar com conversão de tipo de dados em todos os
programas que farão acesso à essas tabelas.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 5 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Outro ponto é que dificilmente conseguiremos criar tabelas
com chaves externas, devido justamente à criação das tabelas com
ELEMENTO DE DADOS diferentes dos utilizados pelo SAP Standard,
portanto, não podemos esquecer que essas tabelas, não são
consistidas pelo SAP.
Muito importante ressaltar que, apesar de o SAP não consistir
essas tabelas, não significa que poderemos ter com isso,
programas que gerem dados inconsistentes, pois o que pode e
deve-se fazer, é criar consistências no próprio DB (utilizando-se de
recursos de Integridade Referencial).
Isto é muito utilizado no mundo externo ao SAP, pois em
sistemas baseados em ORACLE, SYSBASE, SQL Server,
OpenINGRES, etc., quem consiste os dados não são os programas
e sim o próprio SGBD, como aliás acontece no SAP, só que é de
forma transparente para nós consultores ABAP.
Outra ferramenta muito poderosa que dispomos quando
utilizamos base de dados externas, são as triggers (ou store
procedure, etc.) pois através delas podemos fazer com que ao
inserirmos dados na tabela, automaticamente, seja disparado um
processo que atualize uma ou mais tabelas, fazendo com isso a
integridade referencial (cabe ressaltar que para o desenvolvimento
destas “rotinas”, é necessário um profissional específico do DB que
estamos trabalhando, pois esta é escrita diretamente no DB).
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 6 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
3) Criando Sinônimo (SYNONYM) na base da dados externa:
Primeiramente, apesar de normalmente nós (equipe de
ABAP) criarmos as tabelas no SAP, quando se trata de sinônimos,
devido à natureza e complexidade da criação, não nos incumbe tal
tarefa.
Nós somente devemos nos preocupar com a criação da tabela
no SAP, conforme demonstrado no item 1 acima, porém veremos
abaixo procedimentos para que possamos entender e, se
necessário for, até criarmos os sinônimos:
3.1) Definição:
Os sinônimos fazem parte do esquema de uso de tabelas
remotas, destinadas a troca de informações entre o sistema R/3 e
outros sistemas de Banco de Dados. O sinônimo ( synonym ) é um
recurso do SGBD que permite acesso a objetos em bancos de
dados remotos, de maneira transparente, que consiste
basicamente na criação de um redirecionamento de chamadas
( requests ) dos clientes para um banco remoto.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 7 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
3.2) Porque utilizarmos Sinônimos ?
Como já vimos, o sinônimo (synonym) é uma ferramenta dos
SGBD’s atuais que permitem fazermos ligação lógica entre uma
requisição de informações e a informação física.
Esquecendo um pouco o SAP (se é que é possível isso),
imaginemos um sistema qualquer, escrito em uma linguagem
qualquer (que não seja ABAP!) onde temos inúmeros programas
acessando determinada tabela em deterninado servidor e em
determinado Banco de Dados.
Por alguma razão (utilizaremos a hipótese de espaço em
disco), precisamos alterar o servidor (máquina física) e banco de
dados e, de quebra também o nome desta tabela. Pronto, temos
um enorme problema em nossas mãos: precisaremos localizar e
alterar TODOS os códigos fonte que fazem acesso à essa tabela.
Porém, é neste momento em que entra um Sinônimo, pois
através dele, “enganaremos o servidor de Banco de Dados”. Como?
Criaremos um sinônimo, no banco de dados já existente,
apontando para aquele servidor/banco de dados/tabela, fazendo
assim com que não precisemos alterar NENHUMA linha de
codificação.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 8 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
3.3) Suas Aplicações e Vantagens:
As tabelas físicas são todas criadas em um banco de dados
específico para este fim (com os devidos procedimentos de
segurança e contingência), e são acessadas através de um
Database Link (recurso do SGBD) criado manualmente com o
banco de dados do R/3.
Mantendo as tabelas não SAP em um banco de dados
separado desonera o servidor de banco de dados para o R/3 (desde
que os DB’s estejam em máquinas diferentes), pois as operações
de acesso ( select, insert, delete, update ) são processadas pelo
servidor onde as tabelas foram criadas fisicamente.
O uso do Database Link entre bancos de dados, permite o acesso
também a tabelas de Bancos de dados Terceiros, através do
produto Transparent Gateway (somente para Oracle), pois o SAP
pode fazer uma request para o Oracle que, poderá ser atendida
pelo SYBASE ou SQLServer.
Este caso serve para podermos acessar dados de sistemas
que não estão nem sequer com o mesmo tipo de Banco de Dados
onde encontra-se instalado o SAP, aumentando assim a integração
do SAP com qualquer ambiente externo.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 9 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
3.4) Desvantagens:
Por não ser standard (obviamente), não possui quase
nenhum recurso de consistência automática de dados (tab.
Verificação por exemplo), além de não podermos visualizar de
forma rápida e fácil os dados nelas contidos (temos que utilizar
aplicativos específicos dos banco de dados).
Por não conterem dados nem sequer tabela física vinculada
no SAP, não podemos utilizar estas tabelas em queries, sendo
assim somente possível acessarmos através de programa ABAP.
3.5) IMPORTANTÍSSIMO:
Outra questão muito importante é que quando um sinônimo é
criado para uma tabela do mesmo Banco de Dados, podemos
utilizar OpenSQL, isso mesmo, podemos, porém não é 100%
garantido o funcionamento. O ideal é utilizarmos diretamente
acesso Nativo, pois desta forma, evitaremos problemas futuros (no
caso “Legado”, funcionava normalmente, sendo que quando
atualizaram a versão do Oracle, parou de funcionar).
Quando trata-se de um link para outro banco de dados, não
é possível, na maioria dos casos, o acesso por OpenSQL, pois
apesar de que deveria ser transparente para o SAP, não funciona,
conforme mencionado anteriormente.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 10 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
3.6) Procedimentos para Criação do Database Link
(ORACLE/UNIX):
Deve-se incluir os novos schemas a serem acessados no listener do
DB Server para o SAP R/3. vi /oracle/SID/network/admin/tnsnames.ora
O05DS.WORLD = (DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= risc05)(Port= 1521))
(CONNECT_DATA = (SID = O05DS))
)
S08PR = (DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= risc05)(Port= 1521))
(CONNECT_DATA = (SID = s08pr)(SERVER=DEDICATED))
)
Sintaxe do comando para criação do sinônimo:
create public database link <NOME> connect to <USER> identified by using
<SERVICENAME>
onde:
<NOME>: identificação do database link
<USER>: usuário remoto utilizado para acesso (uso
do sapr3 para este fim definitivamente
não permitido)
<SERVICENAME>: identificação do sistema remoto
(conforme configurado no listener
do Oracle)
Utilizar usuário padrão do R/3 SYSTEMExemplos:
create public database link O05DS
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 11 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
connect to DSVR3 identified by SENHA using 'O05DS';
Acessando Sybase:
create public database link S08PR
connect to USERORA identified by SENHA using 'S08PR';
Onde S08PR USERORA é usuário criado no Sybase, e
S08PR é a configuração para o Transparent Gateway no servidor
dbhx05cd.
3.7) Passos no dicionário de dados ( SAP R/3 e Oracle ):
a) Criar a tabela de interface no dicionário de dados do R/3 como
mencionado no item 1 acima.
3.8) Criação do Sinônimo para a tabela do SAP R/3:
Remover, manualmente a tabela da base de dados ( manter a
definição do dicionário de dados do R/3 ), como mencionado no
item 1, ou conforme abaixo:
Utilizar usuário padrão do R/3 SAPR3
Exemplo:
drop table ZTABELA4;
Criar o sinônimo, com o nome da tabela ( transparent table )
removida, para a tabela remota.
Sintaxe do comando:create public synonym <NOME> for <USER>.<TABLE>@<SERVICENAME>.WORLD;
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 12 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
onde:
<NOME>: identificação do sinônimo
<USER>: usuário remoto utilizado para acesso ( uso
do sapr3 para este fim definitivamente
não permitido)
<TABLE>: nome da tabela criada no banco de dados
remoto
<SERVICENAME>: identificação do sistema remoto
(conforme configurado no listener
do Oracle)
Utilizar usuário padrão do R/3 SYSTEMExemplo:create public synonym "ZTABELA4" for DBAR3.T_TABELA4@O05DS.WORLD;
Exemplo para Sybase:create public synonym "ZTR30019" for "dbcdapd..T_R3_ESTOQUE"@S08PR;
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 13 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
4) Formas de Acesso à Base de Dados externa:
Um problema no acesso aos dados que estão na base de
dados externa, é que eles não são visíveis no ambiente SAP, como
as tabelas no SAP. Exemplo: não podemos visualizar o conteúdo da
tabela de forma alguma pelas transações do DDIC, sendo que ao
tentarmos, receberemos a infeliz mensagem de que a tabela não
existe no banco de dados.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 14 de 47 -
Logo ao entrar vemos que a tabela não existe no SAP
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Logo quando entramos na SE11, podemos visualizar que esta
tabela não existe no Banco de Dados (pelo menos para o SAP não),
e se tentarmos mesmo assim visualizar os dados nela contidos
através do menu Utilitários -> Conteúdo da Tabela, teremos mais
uma vez a infeliz notícia de que não existe tabela no banco de
dados para a tabela em questão:
Bem, como vimos, é impossível visualizar-mos os dados
destas tabelas pelo SAP, o que de certa forma dificulta um pouco
nosso trabalho.
A única forma de vermos as informações armazenadas nestas
tabelas, é acessando diretamente o servidor de banco de dados
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 15 de 47 -
Não adianta insistir...
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
utilizando-se para tanto algum tipo de utilitário de acesso à base
de dados (SQL plus – Oracle, RapidSQL - SYSBASE, ISQL, SQL
Server, etc.) para através destes, podermos visualizar os dados
(na maioria dos casos utilizamos comando SQL puro para visualizar
os dados ou através de botões da própria interface do aplicativo, o
que facilita nosso trabalho).
Além da forma descrita acima, a única outra forma de
visualizarmos os dados é dentro de um programa ABAP, onde
através do SELECT, carregamos os dados das tabelas em tabelas
internas (Application Server).
4.1) Acessando uma tabela externa dentro de um Programa
ABAP:
Apesar de todos os detalhes na criação e visualização destas
tabelas, o acesso à ela pode ser tão simples quanto o acesso a
qualquer tabela SAP.
Para acessarmos essas tabelas, na maioria dos casos, não
precisamos de nenhuma codificação diferenciada para extraírmos
os dados dela. É isso mesmo, na maioria dos casos, porque, em
determinadas circunstâncias, o OpenSQL não funciona, chegando
até a travar o servidor de Banco de Dados (muito comum
principalmente no SYSBASE).
Acontece que quando fazemos acesso à essas tabelas
externas com OpenSQL o SAP (por algum motivo que nem a
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 16 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
própria SAP soube responder), é aberto um cursor no Banco de
Dados e de alguma forma, perde-se a ligação com o programa
ABAP, ficando desta forma ambos os processos presos (tanto o
programa ABAP quanto o Cursor de Banco de Dados).*** Estes problemas foram constatados principalmente no SYSBASE.
Portanto, para sanarmos esse e qualquer outro eventual
problema, podemos utilizar o que a SAP chama de NativeSQL, pois
através deste método de acesso, não há interferência no comando
SQL pelo interpretador ABAP, sendo que este comando vai
diretamente para a Base de Dados externa, conforme esquema
abaixo:
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 17 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
5) Utilizando comandos NativeSQL:
Lembremos que o OpenSQL permite-nos acessar as tabelas
do banco de dados declaradas no dicionário ABAP, independente da
plataforma em que estamos trabalhando com o R/3, porém,
algumas vezes, necessitamos utilizar comandos específicos do
banco de dados e, não somente para acessar sinônimos, como
vimos até agora.
Para decidirmos pela utilização de NativeSQL, devemos ter
em mente que quando escrevemos um programa com comandos
específicos do banco de dados, este programa só funcionará no DB
para o qual foi previamente escrito, portanto, se não quisermo
incorrer em problemas futuros com possíveis mudanças de banco
de dados, não devemos utilizar NativeSQL.
Um comando NativeSQL, deve ser sempre precedido pela
declaração EXEC SQL e, finalizado sempre com a declaração
ENDEXEC, conforme exemplo abaixo:
EXEC SQL [PERFORMING <form>]. Parâmetro opcional<Comandos Native SQL> [;]
ENDEXEC.
Vejamos agora uma lista de comandos que, independente do
Banco de Dados, estão incluídos no NativeSQL. (esta lista pode ser
maior, dependendo do Banco de Dados).
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 18 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Transaction management
o COMMIT
o ROLLBACK
Data definition
o CREATE TABLE
o DROP TABLE
o ALTER TABLE
o CREATE VIEW
o DROP VIEW
o CREATE INDEX
o DROP INDEX
o GRANT
o REVOKE
Data manipulation
o SELECT
o INSERT
o UPDATE
o DELETE
o DECLARE CURSOR
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 19 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
o OPEN
o FETCH
o CLOSE
Devemos lembrar que o uso do “;” no final de um comando
NativeSQL é opcional (depende do banco de dados que estamos
trabalhando), ao passo que ao contrário de um comando ABAP, um
comando NativeSQL nunca pode terminar com “.”.
Quando utilizamos NativeSQL, as tabelas não necessitam
estar declaradas no Dicionário ABAP (depende do banco de dados,
é obrigatório) e, desta maneira, não necessitam ser declaradas
pelo comando TABLE. Porém devemos atentar para o fato de o
banco de dados ser case-sensitive ou não, pois senão teremos uma
conversinha com nosso velho amigo “DUMP” .
No NativeSQL não há especificação automática de client,
deste modo, se necessário for, devemos observar mais este
detalhe.
Os dados são transportados entre a tabela do banco de dados
e o programa ABAP, através de variáveis “host”, que devem ser
declaradas normalmente no programa ABAP (sempre atentando
para a tabela de conversão citada no item 2 acima) e, devem ser
precedidas de “:”, dentro da cláusula EXEC SQL, conforme veremos
exemplos de comandos logo abaixo. Podemos utilizar campos ou
estruturas dentro do NativeSQL.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 20 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Não é necessário utilizarmos o comando CONNECT para
acessarmos dados das tabelas com NativeSQL, pois isto já é feito
automaticamente quando o R/3 é estartado.
Ao contrário da sintaxe do ABAP, aspas duplas “ não comenta
a linha em NativeSQL, sendo que para informarmos Strings
(textos) na cláusula WHERE, sem este estar contido em uma
variável tipo CHAR, devemos utilizar aspas duplas e não aspas
simples como em ABAP (dependendo do Banco de Dados).
NUNCA um comando EXEC SQL, retornará dados
diretamente dentro de uma tabela interna, pois seu processamento
é executado linha-a-linha, fazendo com isso que, se o resultado
desejado for uma tabela, deveremos utilizar a sintaxe
PERFORMING <form>, onde transportaremos os dados da WA para
uma Tabela Interna.
Quanto utilizamos o comando PERFORMING <form>, o
formulário <form> é executado para cada registro retornado pela
cláusula SELECT, ou seja, podemos processá-los um a um, ou
executar APPEND em uma tabela interna para processamento(s)
futuro(s), sendo que o comando EXIT FROM SQL.
Outro ponto importante, é que o AUTHORIT-CHECK não é
executado quando usamos NativeSQL.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 21 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Exemplos:
a) Neste exemplo, o resultado será uma lista impressa linha-a-
linha com cada registro lido.
DATA: BEGIN OF WA, CLIENT(3), ARG1(3), ARG2(3), END OF WA.
DATA F3 VALUE ' 1 '.
EXEC SQL PERFORMING LOOP_OUTPUT.SELECT CLIENT, ARG1 INTO :WA FROM TABLE_001 WHERE ARG2 = :F3ENDEXEC.
FORM LOOP_OUTPUT. WRITE: / WA-CLIENT, WA-ARG2.ENDFORM.
b) Este exemplo, serve para criarmos uma tabela no banco
de dados, tabela esta que obviamente não estará no Dicionário
ABAP.
EXEC SQL. CREATE TABLE AVERI_CLNT ( CLIENT CHAR(3) NOT NULL, ARG1 CHAR(3) NOT NULL, ARG2 CHAR(3) NOT NULL, FUNCTION CHAR(10) NOT NULL, PRIMARY KEY (CLIENT, ARG1, ARG2) ) ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 22 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
c) Neste exemplo podemos ver que a especificação de
variáveis na cláusula INTO é possível, de maneira semelhante à
utilizada pelo OpenSQL.
DATA: F1(3), F2(3), F3(3). F3 = ' 1 ' EXEC SQL. SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT WHERE ARG2 = :F3
ENDEXEC. WRITE: / F1, F2.
d) para simplificar a cláusula INTO, podemos também
informar uma estrutura, como já mencionado, a exemplo de como
fazemos com OpenSQL.
DATA: BEGIN OF WA, CLIENT(3), ARG1(3), ARG2(3), END OF WA. DATA F3(3). F3 = ' 1 ' EXEC SQL. SELECT CLIENT, ARG1
INTO :WA FROM AVERI_CLNT WHERE ARG2 = :F3 ENDEXEC. WRITE: / WA-CLIENT, WA-ARG1.
e) podemos utilizar OpenSQL para tabelas externas, desde
que tenham sido criados sinônimos anteriormente para elas:
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_TAB.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 23 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
SELECT CLIENT, NUMBER
FROM TAB001
INTO :T_TAB
WHERE NUMBER > 100
AND CLIENT = :SY-MANDT
ENDEXEC.
OpenSQL:
SELECT NUMBER
FROM TAB001
INTO TABLE T_TAB
WHERE NUMBER > 100.
Devemos lembrar que quando utilizamos o NativeSQL, é necessário
especificarmos o mandante que desejamos selecionar, caso
contrário, o resultado será a junção de dados de TODOS os
mandantes.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 24 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
6) Comandos OpenSQL/NativeSQL avançados:
Veremos agora algumas cláusulas SQL que estão presentes
na maioria dos banco de dados atuais e, que desta forma podemos
utilizar no ambiente SAP.
6.1) Funções Agregadas ou de Agrupamento:
função retorno
1) avg(n) média do valor n, ignorando nulos2) count(expr) vezes que o número da expr avalia para algo não nulo3) max(expr) maior valor da expr4) min(expr) menor valor da expr5) sum(n) soma dos valores de n, ignorando nulos
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 25 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Exemplos:
AVG:
OpenSQL:
SELECT MATNR AVG( BSTMI ) AVG( BSTMA )
FROM MARC
INTO TABLE T_MARC
GROUP BY MATNR.
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_1.
SELECT MATNR, AVG( BSTMI ), AVG( BSTMA )
FROM MARC
INTO :T_MARC
WHERE MANDT = :SY-MANDT
GROUP BY MATNR
ENDEXEC.
FORM ZF_APPEND_1.
APPEND T_MARC.
CLEAR T_MARC.
ENDFORM.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 26 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
COUNT:
OpenSQL:
SELECT COUNT( DISTINCT MATNR )
FROM MARC
INTO V_CONTA_1.
NativeSQL:
EXEC SQL.
SELECT COUNT( MATNR )
FROM MARC
INTO :V_CONTA_2
WHERE MANDT = :SY-MANDT
ENDEXEC.
MAX:
OpenSQL:
SELECT MATNR MAX( BSTMI ) MAX( BSTMA )
FROM MARC
INTO TABLE T_MARC
GROUP BY MATNR.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 27 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_3.
SELECT MATNR, MAX( BSTMI ), MAX( BSTMA )
FROM MARC
INTO :T_MARC
WHERE MANDT = :SY-MANDT
GROUP BY MATNR
ENDEXEC.
MIN:
OpenSQL:
SELECT MATNR MIN( BSTMI ) MAX( BSTMA )
FROM MARC
INTO TABLE T_MARC
GROUP BY MATNR.
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_3.
SELECT MATNR, MIN( BSTMI ), MAX( BSTMA )
FROM MARC
INTO :T_MARC
WHERE MANDT = :SY-MANDT
GROUP BY MATNR
ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 28 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
SUM:
OpenSQL:
SELECT MATNR SUM( BSTMI ) SUM( BSTMA )
FROM MARC
INTO TABLE T_MARC
GROUP BY MATNR.
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_4.
SELECT MATNR, SUM( BSTMI ), SUM( BSTMA )
FROM MARC
INTO :T_MARC
WHERE MANDT = :SY-MANDT
GROUP BY MATNR
ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 29 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
6.2) HAVING:
É utilizado para restringir a matriz resultado dos select’s
anteriores, por exemplo.
Exemplo:
OpenSQL:
SELECT MATNR SUM( BSTMI ) SUM( BSTMA )
FROM MARC
INTO TABLE T_MARC
GROUP BY MATNR
HAVING SUM( BSTMI ) > 10 AND SUM( BSTMA ) > 20.
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_6.
SELECT MATNR, SUM( BSTMI ), SUM( BSTMA )
FROM MARC
INTO :T_MARC
WHERE MANDT = :SY-MANDT
GROUP BY MATNR
HAVING SUM( BSTMI ) > 10 AND SUM( BSTMA ) > 20
ENDEXEC.
Vale lembrar que o HAVING pode ser usado também com as outras
funções de agragamento (MAX, MIN, AVG, COUNT).
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 30 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
6.3) Join de Tabelas:
OpenSQL:
SELECT MARA~MATNR MAKT~MAKTX MARC~WERKS
FROM MARA INNER JOIN MAKT
ON MARA~MATNR EQ MAKT~MATNR
INNER JOIN MARC
ON MARA~MATNR EQ MARC~MATNR
INTO TABLE T_JOIN
WHERE MARC~WERKS = 'CE01'
AND MAKT~SPRAS = SY-LANGU.
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_7.
SELECT A.MATNR, B.MAKTX, C.WERKS
FROM MARA A, MAKT B, MARC C
INTO :T_JOIN
WHERE A.MATNR = B.MATNR
AND A.MATNR = C.MATNR
AND A.MANDT = :SY-MANDT
AND B.MANDT = :SY-MANDT
AND C.MANDT = :SY-MANDT
AND B.SPRAS = :SY-LANGU
ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 31 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
6.4) Subconsultas:
Primeiramente, devemos ressaltar que as subconsultas
dentro do SAP, deve ser utilizada através da cláusula FOR ALL
ENTRIES, sendo que em SQL, não existe tal cláusula, como
veremos a seguir:
OpenSQL:
***Apenas para utilização no FOR ALL ENTRIES
SELECT MATNR MTART
FROM MARA
INTO TABLE T_MARA
WHERE MTART = 'HAWA'.
SELECT MARA~MATNR MAKT~MAKTX MARC~WERKS
FROM MARA INNER JOIN MAKT
ON MARA~MATNR EQ MAKT~MATNR
INNER JOIN MARC
ON MARA~MATNR EQ MARC~MATNR
INTO TABLE T_JOIN
FOR ALL ENTRIES IN T_MARA
WHERE MARC~WERKS = 'CE01'
AND MAKT~SPRAS = SY-LANGU
AND MARA~MATNR = T_MARA-MATNR.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 32 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
NativeSQL:
EXEC SQL PERFORMING ZF_APPEND_8.
SELECT A.MATNR, B.MAKTX, C.WERKS
FROM MARA A, MAKT B, MARC C
INTO :T_JOIN
WHERE A.MATNR = B.MATNR
AND A.MATNR = C.MATNR
AND A.MANDT = :SY-MANDT
AND B.MANDT = :SY-MANDT
AND C.MANDT = :SY-MANDT
AND B.SPRAS = :SY-LANGU
AND A.MATNR IN
( SELECT MATNR FROM MARA WHERE MTART = 'HAWA' )
ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 33 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
6.5) Insert:
Observação: Lembre-se de que quando utilizamos NativeSQL,
é necessários especificarmos o mandante !
DATA: T_Z3DADOST LIKE Z3DADOST.
T_Z3DADOST-ZCODIGO = 00040.
T_Z3DADOST-DESCRICAO = 'TESTE DO CURSO DE SQL'.
INSERT INTO Z3DADOST VALUES T_Z3DADOST.
COMMIT WORK.
ADD 1 TO T_Z3DADOST-ZCODIGO.
EXEC SQL.
INSERT INTO Z3DADOST ( MANDT, ZCODIGO, DESCRICAO )
VALUES( :SY-MANDT, :T_Z3DADOST-ZCODIGO,
:T_Z3DADOST-DESCRICAO );
COMMIT;
ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 34 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
6.6) Trabalhando com Cursores de Banco de Dados:
Para abrirmos um cursor de banco de dados, é obrigatório
declararmos uma variável tipo CURSOR, e um cursor de banco de
dados pode ser utilizado somente para comandos SELECT e, este
comando SELECT, deve, obrigatoriamente retornar mais do que
uma linha como resultado.
Sintaxe:
OPEN CURSOR C1 FOR SELECT * FROM SFLIGHT WHERE CARRID = 'LH '.
FETCH CURSOR C1.
CLOSE CURSOR C1.
Sim, este comando é muito semelhante ao OpenSQL, até
porque pertence à classe de comandos do OpenSQL, sendo desta
forma, impossível a sua utilização através de NativeSQL.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 35 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Exemplo:
DATA:
C1 TYPE CURSOR.
OPEN CURSOR C1 FOR
SELECT MATNR MTART FROM MARA WHERE MTART = 'HAWA'.
DO.
FETCH NEXT CURSOR C1 INTO T_MARA.
IF SY-SUBRC EQ 0.
APPEND T_MARA.
ELSE.
EXIT.
ENDIF.
ENDDO.
CLOSE CURSOR C1.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 36 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
7) Utilizando o SQLTrace:
Esta é uma ferramenta muito importante no nosso dia-a-dia,
pois com ela podemos descobrir exatamente quais tabelas
determinadas transações e com quais chaves, podemos
perfeitamente utilizá-la. Veremos a seguir como sua utilização é
bem simples:
Transação ST05
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 37 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Então, ativamos o trace.
Neste momento, deveremos executar o
programa/transação/funcão que queremos fazer o trace.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 38 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
O resultado será este Report, com todos os acessos de tabela
executados pelo usuário enquanto o Trace estava ativo. Para
obtermos maiores detalhes, devemos clicar duas vezes na linha
desejada.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 39 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
Veremos então, o comando SQL que foi enviado para o DB Server,
podendo até copiarmos e utilizarmos em instruções Native ou
OpenSQL.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 40 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
UNION:
Muito pouco utilizado, mas com enorme utilidade, esta
cláusula faz com que o resultado de dois ou mais SELECT’s sejam
obtidos como resultado.
A quantidade e o tipo dos campos em TODOS os SELECT’s
devem sempre ser o mesmo, sendo que podermos preenchê-lo
com literais quando em alguma das tabelas não tivermos todos os
campos das demais.
Exemplo:
EXEC SQL PERFORMING ZF_APPEND_11.
SELECT MATNR, MTART
FROM MARA
UNION
SELECT MATNR,'XXXX'
FROM MARC
UNION
SELECT DISTINCT MATNR, 'AAAA'
FROM MARD
WHERE MATNR IN ( SELECT MATNR FROM MARA )
INTO :T_MARA
ENDEXEC.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 41 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
8) MODELO DE DADOS
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 42 de 47 -
Interface com o SAP R/3
Versão: 23/03/99
t_r3_interfaceno_tab_interface: VARCHAR2(30) NOT NULL
dc_interface: VARCHAR2(250) NOT NULLsg_sist_gerador: VARCHAR2(2) NOT NULLcd_pgm_gerador: VARCHAR2(8) NOT NULLno_resp_geracao: VARCHAR2(30) NOT NULLsg_sist_receptor: VARCHAR2(2) NOT NULLcd_pgm_receptor: VARCHAR2(8) NULLid_depend_seq: NUMBER(10) NOT NULLid_periodicidade: NUMBER(10) NULLqt_dia_retencao: NUMBER(10) NOT NULLcd_usuario: VARCHAR2(12) NOT NULLdt_atualizacao: VARCHAR2(8) NOT NULL
t_r3_xxxx xxxxnm_seq_registro: NUMBER(15,0) NOT NULL
dt_geracao: VARCHAR2(8) NULLhr_geracao: VARCHAR2(6) NULLdt_processamento: VARCHAR2(8) NULLhr_processamento: VARCHAR2(6) NULLid_sit_registro: NUMBER(3) NOT NULLcd_erro_registro: VARCHAR2(8) NULLcoluna_1: VARCHAR2(8) NULLcoluna_2: VARCHAR2(10) NULLcoluna_n: NUMBER(5) NULL
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
9) DESCRIÇÃO DO MODELO DE DADOS
As interfaces dos Sistemas “Legado” com o SAP R/3 (e vice-versa) utilizarão as tabelas:
✓ t_r3_interface (Cadastro das Interfaces com o SAP R/3)✓ t_r3_xxxxxxxx (este é um exemplo para mostrar os controles que todas as tabelas de dados
deverão conter. )
9.1 - Tabela: t_r3_interface Cadastro das Interfaces entre o sistema SAP R/3 e os demais sistemas(vice-versa).
Colunas:
no_tab_interface - Nome da tabela que contém os registros de interface.(Será utilizado pelo programa de limpeza e pelo programa de monitoração)
dc_interface - Descrição da finalidade da interface.
sg_sist_gerador - Sigla do sistema que gera a interface.
cd_pgm_gerador - Código do programa que gera a interface.
no_resp_geracao - Nome do Analista responsável pelo Sistema que fará a geração da interface.
sg_sist_receptor - Sigla do sistema que recebe a interface.
cd_pgm_receptor - Código do programa que trata os registro no sistema destino.
id_depend_seq - Indica se o processamento dos registros da tabela de interface deve obedecer a sequência de geração
Domínio: 0 - Não 1 - Sim Regra: Se uma interface possui id_depend_seq = 1 e ocorre um erro no processamento de um registro, os demais registros não poderão ser processados enquanto o problema não for resolvido.
id_periodicidade - Indica a periodicidade de geração da interface.
Domínio: 1 - Diária 7 - Semanal 15 - Quinzenal 30 - Mensal 180 - Semestral 360 – Anual
qt_dia_retencao - Quantidade de dias para retenção do registro após o processamento. dt_atualizacao - Data da última atualização no Cadastro de interfaces. cd_usuario - Código do usuário (login) responsável pela última atualização.
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 43 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
9.2 - Tabela: t_r3_xxxxxxxx Exemplo de definição de uma tabela de interface com o SAP R/3. A tabela t_r3_xxxxxxxx conterá os dados que devem ser trocados entre os Sistemas “Legado” e o SAP R/3, e também os campos de controles para a verificação do processamento de cada registro. Existirá uma tabela para cada Interface necessária. Os nomes das tabelas deverão iniciar sempre com “t_r3_”, e em seguida um nome identificando a interface. Exemplo: t_r3_mov_estoque, t_r3_faturamento_diario, t_r3_preços....
Quando for solicitado a criação dessas tabelas, devemos solicitar também a criação de um “sequence” para a tabela, com o respectivo TRIGGER para o número sequencial do registro.
Colunas:
nm_seq_registro – Número sequencial para controle de geração do registro. (Será gerado automaticamente por uma Trigger de Banco).
dt_geracao – Data em que foi a gerada a interface (a mesma data para os registros de um mesmo processamento).
hr_geracao – Hora em que foi gerada a interface(a mesma hora para os registros de um mesmo processamento).
dt_processamento – Data em que foi processada a interface(a mesma data para os registros de um mesmo processamento).
hr_processamento – Hora em que foi processada a interface(a mesma hora para os registros de um mesmo processamento). id_sit_registro – Indica a situação do processamento de um registro de interface.
Domínio:1 - Não processado2 - Em processamento3 - Processado OK4 - Processado com ERRO5 - Cancelado
Transição de estados:1 2 2 3 ou 4 ou 54 1 ou 5
cd_erro_registro – Código de erro ocorrido no processamento do registro.
Coluna_1 – Conforme definição das Solicitações de Serviço (feitas para gerar arquivos texto).
Coluna_2 – Conforme definição das Solicitações de Serviço (feitas para gerar arquivos texto).
Coluna_3 – Conforme definição das Solicitações de Serviço (feitas para gerar arquivos texto).
Coluna_n – Conforme definição das Solicitações de Serviço (feitas para gerar arquivos texto).
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 44 de 47 -
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
10) FORMAS DE INTERFACEAMENTO COM O SAP R/3
✓ Tabelas de Interface✓ Tabelas Corporativas✓ IDOC´s✓ CALL FUNCTION (RFC)
10.1) FLUXO: Troca de informações entre o SAP R/3 e os Sistemas “Legado”
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 45 de 47 -
DB
SAP
T1
T2
T3
T4
T5
Tabela
X
T1
DB
“Legado”Transação
SAP
Programa
ABAP
IDOC
Programa
Cobol ou VB5
Mensagem
Programa
Parceiro
(VB5 ou C)
T2
T3
T4
T5
Programa
Cobol ou VB5
Programa
Cobol ou VB5
Programa
Cobol ou VB5
Programa
Cobol ou VB5
Transação
SAP
Transação
SAP
Programa
ABAP
Programa
ABAP
Programa
ABAP
Programa
ABAP
Programa
VB5
Programa
Cobol ou VB5
Tabelas
Espelho
Tabelas
Interface
SAP “Legado”
ALE
DBLINK
RFC CALL FUNCTION
RFC
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
10.2) TABELAS DE INTERFACE
São as tabelas definidas no Modelo de Dados, que serão utilizadas para a troca de informações entre o SAP R/3 e os Sistemas Natura(e vice-versa). Sempre que possível utilizaremos esta solução.
Esta solução deverá ser utilizada para a maioria das interfaces com o SAP R/3, exceto nos casos onde será necessário utilizar-se de IDOC´s.
10.2.1) FLUXO: Geração de Interface no SAP R/3 utilizando as Tabelas de Interface(ORACLE)
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 46 de 47 -
DB
SAP
t_r3_interface
t_r3_xxxx
t_r3_interface
DB
“Legado”
t_r3_xxxx Programa
Cobol ou VB5
Programa
ABAP
Programa
ABAP
t_r3_interface t_r3_interface
t_r3_yyy t_r3_yyy
Programa
Cobol ou VB5
SAP Sistema “Legado”Geração de Interface
no SAPProcessamento da
Interface no Sistema
Legado
Tabelas
Interface
Tabelas
Espelho
DBLINK
ACESSO À BASE DE DADOS EXTERNACOM NATIVE E OpenSQL
10.2.2) FLUXO: Processamento da Interface no SAP R/3 utilizando as Tabelas de Interface(ORACLE).
Luís Fernando Berti Santos – Aspen Procwork – 2001
- Página 47 de 47 -
DBSAP
t_r3_xxxx
t_r3_interface
t_r3_xxxx
t_r3_interface
DB
“Legado”
TransaçãoSAP
ProgramaCobol ou VB5
ProgramaABAP
ProgramaABAP
t_r3_interface
t_r3_yyy
t_r3_interface
t_r3_yyy
ProgramaCobol ou VB5
TransaçãoSAP
SAP Sistema“Legado”Processamento
da Interface no SAP
Geração daInterface no Sistema“Legado”
TabelasInterface
TabelasEspelho
DBLINK