Pessoal,

No treinamento surgiu uma pergunta sobre VIEW’s, se é possível inserir (ou alterar) em uma VIEW e se segue todas as restrições da tabela que a originou, vamos ver nos SQL’s abaixo o que acontece.

CRIAR A TABELA E INSERIR ALGUNS REGISTROS:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q3JlYXRlIHRhYmxlIFRlc3RlMQooCmNvbHVuYTEgdmFyY2hhcigxMCksCmNvbHVuYTIgY2hhcig1KSwKY29sdW5hMyBpbnRlZ2VyCik7CgppbnNlcnQgaW50byB0ZXN0ZTEKdmFsdWVzCignMDEyMzQ1Njc4OScsJzEyMzQ1JywxKTsKCmluc2VydCBpbnRvIHRlc3RlMQp2YWx1ZXMKKCcxMTIzNDU2Nzg5JywnMTIzNDYnLDIpOwoKaW5zZXJ0IGludG8gdGVzdGUxCnZhbHVlcwooJzIxMjM0NTY3ODknLCcxMjM0NycsMyk7CgppbnNlcnQgaW50byB0ZXN0ZTEKdmFsdWVzCignMzEyMzQ1Njc4OScsJzEyMzQ4Jyw0KTsKCkNvbW1pdDsKCmRlc2MgdGVzdGUxOw==” hover_enabled=”0″]Q3JlYXRlIHRhYmxlIFRlc3RlMQooCmNvbHVuYTEgdmFyY2hhcigxMCksCmNvbHVuYTIgY2hhcig1KSwKY29sdW5hMyBpbnRlZ2VyCik7CgppbnNlcnQgaW50byB0ZXN0ZTEKdmFsdWVzCignMDEyMzQ1Njc4OScsJzEyMzQ1JywxKTsKCmluc2VydCBpbnRvIHRlc3RlMQp2YWx1ZXMKKCcxMTIzNDU2Nzg5JywnMTIzNDYnLDIpOwoKaW5zZXJ0IGludG8gdGVzdGUxCnZhbHVlcwooJzIxMjM0NTY3ODknLCcxMjM0NycsMyk7CgppbnNlcnQgaW50byB0ZXN0ZTEKdmFsdWVzCignMzEyMzQ1Njc4OScsJzEyMzQ4Jyw0KTsKCkNvbW1pdDsKCmRlc2MgdGVzdGUxOw==[/et_pb_dmb_code_snippet]

CRIAR A VIEW E INICIAR OS TESTES:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q3JlYXRlIHZpZXcgdmlldzEgYXMKU2VsZWN0IGNvbHVuYTEsIGNvbHVuYTIsIGNvbHVuYTMKZnJvbSB0ZXN0ZTE7CgppbnNlcnQgaW50byB2aWV3MQp2YWx1ZXMKKCc0MTIzNDU2Nzg5JywnMTIzNDknLDUpOwoKaW5zZXJ0IGludG8gdmlldzEKdmFsdWVzCignNTEyMzQ1Njc4OScsJzEyMzQwJyw2KTsKCmluc2VydCBpbnRvIHZpZXcxCnZhbHVlcwooJzAwMTIzNDU2Nzg5JywnMDIzNDAnLDcpOwojRXJyb3IgQ29kZTogMTQwNi4gRGF0YSB0b28gbG9uZyBmb3IgY29sdW1uIOKAmGNvbHVuYTHigJkgYXQgcm93IDEKCmluc2VydCBpbnRvIHZpZXcxCnZhbHVlcwooJzAxMjM0NTY3ODAnLCcxMjM0MDAnLDcpOwojRXJyb3IgQ29kZTogMTQwNi4gRGF0YSB0b28gbG9uZyBmb3IgY29sdW1uIOKAmGNvbHVuYTLigJkgYXQgcm93IDEKCmRlc2MgdmlldzE7CiNNZXNtYXMgZGVmaW5pw6fDtWVzIGRhIHRhYmVsYTsKCmFsdGVyIHRhYmxlIHRlc3RlMSBhZGQgY29sdW1uIGNvbHVuYTQgdmFyY2hhcig1KSBub3QgbnVsbCBkZWZhdWx0ICdOJzsKI0NvbG9xdWVpIGRlZmF1bHQgcG9pcyBjb21vIMOpIE5PVCBOVUxMIGUgasOhIGV4aXN0ZW0gcmVnaXN0cm9zIHByZWNpc2FtIGluaWNpYXIgY29tIGFsZ28KCmFsdGVyIHRhYmxlIHRlc3RlMSBtb2RpZnkgY29sdW5hNCB2YXJjaGFyKDUpIG5vdCBudWxsOwojQWx0ZXJhbmRvIGEgdGFiZWxhIHBhcmUgbsOjbyB0ZXIgbWFpcyBkZWZhdWx0CgppbnNlcnQgaW50byB2aWV3MQp2YWx1ZXMKKCcwMTIzNDU2NzgwJywnNTIzNDUnLDcpOwojRXJyb3IgQ29kZTogMTQyMy4gRmllbGQgb2YgdmlldyDigJh0ZXN0LnZpZXcx4oCZIHVuZGVybHlpbmcgdGFibGUgZG9lc27igJl0IGhhdmUgYSBkZWZhdWx0IHZhbHVlCgpDcmVhdGUgb3IgcmVwbGFjZSB2aWV3IHZpZXcxIGFzClNlbGVjdCBjb2x1bmExLCBjb2x1bmEyLCBjb2x1bmEzLCBjb2x1bmE0CmZyb20gdGVzdGUxOwoKaW5zZXJ0IGludG8gdmlldzEKdmFsdWVzCignMDEyMzQ1Njc4MCcsJzUyMzQ1Jyw3LCdGT0khJyk7CgpDb21taXQ7CgphbHRlciB0YWJsZSB0ZXN0ZTEgYWRkIHByaW1hcnkga2V5IHRlc3RlMV9wazEgKGNvbHVuYTMpOwoKaW5zZXJ0IGludG8gdmlldzEKdmFsdWVzCignMDEyMzQ1Njc4MCcsJzUyMzQ1Jyw3LCdGT0khJyk7CiNFcnJvciBDb2RlOiAxMDYyLiBEdXBsaWNhdGUgZW50cnkg4oCYN+KAmSBmb3Iga2V5IOKAmFBSSU1BUlnigJk=” hover_enabled=”0″]Q3JlYXRlIHZpZXcgdmlldzEgYXMKU2VsZWN0IGNvbHVuYTEsIGNvbHVuYTIsIGNvbHVuYTMKZnJvbSB0ZXN0ZTE7CgppbnNlcnQgaW50byB2aWV3MQp2YWx1ZXMKKCc0MTIzNDU2Nzg5JywnMTIzNDknLDUpOwoKaW5zZXJ0IGludG8gdmlldzEKdmFsdWVzCignNTEyMzQ1Njc4OScsJzEyMzQwJyw2KTsKCmluc2VydCBpbnRvIHZpZXcxCnZhbHVlcwooJzAwMTIzNDU2Nzg5JywnMDIzNDAnLDcpOwojRXJyb3IgQ29kZTogMTQwNi4gRGF0YSB0b28gbG9uZyBmb3IgY29sdW1uIOKAmGNvbHVuYTHigJkgYXQgcm93IDEKCmluc2VydCBpbnRvIHZpZXcxCnZhbHVlcwooJzAxMjM0NTY3ODAnLCcxMjM0MDAnLDcpOwojRXJyb3IgQ29kZTogMTQwNi4gRGF0YSB0b28gbG9uZyBmb3IgY29sdW1uIOKAmGNvbHVuYTLigJkgYXQgcm93IDEKCmRlc2MgdmlldzE7CiNNZXNtYXMgZGVmaW5pw6fDtWVzIGRhIHRhYmVsYTsKCmFsdGVyIHRhYmxlIHRlc3RlMSBhZGQgY29sdW1uIGNvbHVuYTQgdmFyY2hhcig1KSBub3QgbnVsbCBkZWZhdWx0ICdOJzsKI0NvbG9xdWVpIGRlZmF1bHQgcG9pcyBjb21vIMOpIE5PVCBOVUxMIGUgasOhIGV4aXN0ZW0gcmVnaXN0cm9zIHByZWNpc2FtIGluaWNpYXIgY29tIGFsZ28KCmFsdGVyIHRhYmxlIHRlc3RlMSBtb2RpZnkgY29sdW5hNCB2YXJjaGFyKDUpIG5vdCBudWxsOwojQWx0ZXJhbmRvIGEgdGFiZWxhIHBhcmUgbsOjbyB0ZXIgbWFpcyBkZWZhdWx0CgppbnNlcnQgaW50byB2aWV3MQp2YWx1ZXMKKCcwMTIzNDU2NzgwJywnNTIzNDUnLDcpOwojRXJyb3IgQ29kZTogMTQyMy4gRmllbGQgb2YgdmlldyDigJh0ZXN0LnZpZXcx4oCZIHVuZGVybHlpbmcgdGFibGUgZG9lc27igJl0IGhhdmUgYSBkZWZhdWx0IHZhbHVlCgpDcmVhdGUgb3IgcmVwbGFjZSB2aWV3IHZpZXcxIGFzClNlbGVjdCBjb2x1bmExLCBjb2x1bmEyLCBjb2x1bmEzLCBjb2x1bmE0CmZyb20gdGVzdGUxOwoKaW5zZXJ0IGludG8gdmlldzEKdmFsdWVzCignMDEyMzQ1Njc4MCcsJzUyMzQ1Jyw3LCdGT0khJyk7CgpDb21taXQ7CgphbHRlciB0YWJsZSB0ZXN0ZTEgYWRkIHByaW1hcnkga2V5IHRlc3RlMV9wazEgKGNvbHVuYTMpOwoKaW5zZXJ0IGludG8gdmlldzEKdmFsdWVzCignMDEyMzQ1Njc4MCcsJzUyMzQ1Jyw3LCdGT0khJyk7CiNFcnJvciBDb2RlOiAxMDYyLiBEdXBsaWNhdGUgZW50cnkg4oCYN+KAmSBmb3Iga2V5IOKAmFBSSU1BUlnigJk=[/et_pb_dmb_code_snippet]

Bem, então podemos ver que sim, respeita todas as restrições da tabela utilizada na criação da VIEW.

Obs.: Os testes foram realizados no Workbench e também podem ser feitos no prompt do MySQL sem problemas.

Acredito que deva funcionar para Oracle também sem problemas, para executar o teste em Oracle precisamos somente alterar o VARCHAR para VARCHAR2.

MELHORIA:

Segue scripts para os alter em Oracle:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCTIEFkZC9tb2RpZnkgY29sdW1ucwphbHRlciB0YWJsZSBURVNURTEgYWRkIGNvbHVuYTQgdmFyY2hhcig1KSBkZWZhdWx0IOKAmE7igJkgbm90IG51bGw7CmFsdGVyIHRhYmxlIFRFU1RFMSBtb2RpZnkgY29sdW5hNCBkZWZhdWx0IG51bGw7CgrigJMgQ3JlYXRlL1JlY3JlYXRlIHByaW1hcnksIHVuaXF1ZSBhbmQgZm9yZWlnbiBrZXkgY29uc3RyYWludHMKYWx0ZXIgdGFibGUgVEVTVEUxCmFkZCBjb25zdHJhaW50IHRlc3RlMV9wazEgcHJpbWFyeSBrZXkgKENPTFVOQTMpOw==” hover_enabled=”0″]4oCTIEFkZC9tb2RpZnkgY29sdW1ucwphbHRlciB0YWJsZSBURVNURTEgYWRkIGNvbHVuYTQgdmFyY2hhcig1KSBkZWZhdWx0IOKAmE7igJkgbm90IG51bGw7CmFsdGVyIHRhYmxlIFRFU1RFMSBtb2RpZnkgY29sdW5hNCBkZWZhdWx0IG51bGw7CgrigJMgQ3JlYXRlL1JlY3JlYXRlIHByaW1hcnksIHVuaXF1ZSBhbmQgZm9yZWlnbiBrZXkgY29uc3RyYWludHMKYWx0ZXIgdGFibGUgVEVTVEUxCmFkZCBjb25zdHJhaW50IHRlc3RlMV9wazEgcHJpbWFyeSBrZXkgKENPTFVOQTMpOw==[/et_pb_dmb_code_snippet]

Att,
capin