PERGAMINHO_ID = :2' USING L_EXPRESSAO_REGULAR, P_PERGAMINHO_ID;EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END;/*********************************************************************************/PROCEDURE CALCULA_VERBO(P_PERGAMINHO_ID NUMBER) IS L_EXPRESSAO_REGULAR VARCHAR2(32) := '^.{5,7}[vkbps]$';BEGIN EXECUTE IMMEDIATE 'UPDATE PERGAMINHOS P SET P.VERBO = ( SELECT COUNT(*) FROM PALAVRAS WHERE PERGAMINHO_ID = P.PERGAMINHO_ID AND REGEXP_LIKE(PALAVRA,:1) ) WHERE PERGAMINHO_ID = :2' USING L_EXPRESSAO_REGULAR, P_PERGAMINHO_ID;EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END;/*********************************************************************************/PROCEDURE CALCULA_VERBO_PRIMEIRA_PESSOA(P_PERGAMINHO_ID NUMBER) IS L_EXPRESSAO_REGULAR VARCHAR2(32) := '^[vkbps].{4,6}[vkbps]$';BEGIN EXECUTE IMMEDIATE 'UPDATE PERGAMINHOS P SET P.VERBO_PRIMEIRA_PESSOA = ( SELECT COUNT(*) FROM PALAVRAS WHERE PERGAMINHO_ID = P.PERGAMINHO_ID AND REGEXP_LIKE(PALAVRA,:1) ) WHERE PERGAMINHO_ID = :2' USING L_EXPRESSAO_REGULAR, P_PERGAMINHO_ID;EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END;/*********************************************************************************/PROCEDURE PALAVRAS_TO_LISTA_VOCABULARIO(P_PERGAMINHO_ID NUMBER) IS L_LISTA_VOCABULARIO VARCHAR2(4000); L_ALFABETO_GOOGLON VARCHAR2(20) := 'dcxkjsmvrlgftwpqznbh'; L_ALFABETO_PORTUGUES VARCHAR2(20) := 'abcdefghijklmnopqrst';BEGIN L_LISTA_VOCABULARIO := NULL; FOR REGISTRO IN (SELECT DISTINCT PALAVRA FROM PALAVRAS WHERE PERGAMINHO_ID = P_PERGAMINHO_ID ORDER BY TRANSLATE(PALAVRA, L_ALFABETO_GOOGLON, L_ALFABETO_PORTUGUES)) LOOP L_LISTA_VOCABULARIO := L_LISTA_VOCABULARIO || REGISTRO.PALAVRA || GOOGLE_DEV_DAY_BRASIL_2011.SEPARADOR; END LOOP; UPDATE PERGAMINHOS SET LISTA_VOCABULARIO = L_LISTA_VOCABULARIO WHERE PERGAMINHO_ID = P_PERGAMINHO_ID; EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE(SQLERRM); END;END;/*********************************************************************************/PROCEDURE CALCULA_NUMERO_BONITO_DISTINTO(P_PERGAMINHO_ID NUMBER) IS L_ORDEM NUMBER; L_LETRA CHAR; L_UNIDADE NUMBER; L_NUMERO NUMBER; I NUMBER;BEGIN I := 1; L_ORDEM := 1; FOR REGISTRO IN (SELECT PALAVRA FROM PALAVRAS WHERE PERGAMINHO_ID = P_PERGAMINHO_ID) LOOP L_NUMERO := 0; FOR I IN 1 .. LENGTH(REGISTRO.PALAVRA) LOOP L_LETRA := SUBSTR(REGISTRO.PALAVRA, I, 1); L_UNIDADE := GOOGLE_DEV_DAY_BRASIL_2011.LETRA_TO_UNIDADE(L_LETRA); L_NUMERO := L_NUMERO + (L_UNIDADE * POWER(20, I - 1)); END LOOP; IF GOOGLE_DEV_DAY_BRASIL_2011.IS_NUMERO_BONITO(L_NUMERO) THEN INSERT INTO NUMEROS(NUMERO_ID, PERGAMINHO_ID, ORDEM, NUMERO) VALUES(NUMEROS_SEQ.NEXTVAL, P_PERGAMINHO_ID, L_ORDEM, L_NUMERO); L_ORDEM := L_ORDEM + 1; END IF; I := I + 1; END LOOP; UPDATE PERGAMINHOS P SET P.NUMERO_BONITO_DISTINTO = (SELECT COUNT(DISTINCT NUMERO) FROM NUMEROS WHERE PERGAMINHO_ID = P.PERGAMINHO_ID) WHERE P.PERGAMINHO_ID = P_PERGAMINHO_ID;EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END; /*********************************************************************************/ BEGIN NULL;END GOOGLE_DEV_DAY_BRASIL_2011;Daqui pra frente utilizaremos o sqlplus como cliente Oracle.Antes de inserir os registros do pergaminho, e necessario alterar o parametro DEFERRED_SEGMENT_CREATION do Oracle 11g R2 x86_64. Por padrao, o parametro DEFERRED_SEGMENT_CREATION e TRUE.Segundo a documentacao oficial do Oracle 11g R2 x86_64:DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.Desabilitando o parametro DEFERRED_SEGMENT_CREATION:SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = FALSE SCOPE=MEMORY; System alteredCaso nao desabilite, a primeira insercao de cada tabela que utiliza sequencia ira "falhar", em consequencia e perdido a sequencia 1, e o primeiro registro utilizara a sequencia 2.Agora vamos inserir os registros dos PERGAMINHOS:SQL> INSERT INTO PERGAMINHOS(PERGAMINHO_ID,NOME,PERGAMINHO) VALUES(PERGAMINHOS_SEQ.NEXTVAL,'Texto A','wsnmx fvjxrdfd nqxhxs mlzlpk glrxnsh dlrbnkxq znsnkrfc kjdqxgm ddcf dghwxk wmx wrflj tlqx zhpmzrtf xfgm slgfdjl hzvqf vksfg tqz jmjq rfgcwcf ckggzsd wpnn jxhmhnhf mbbxzl pwctdf drnnzlr kqbtck ddrpbj dmsbns prfcvllg cckfsb xwqplnvv smnw cgqt lcdgwf wxhr pfh hqfqlzxp bnbftrf hkrw thsbxw xwsxlm ftzzzct tnqrkjjf gjkz gqdw lsr xcxjtpfn rblfvc wwfrgkj ttfjct xcdgbt kmszsfzw hrhmj gnlqwj ztkzbhz dcrplxs zgp qmq rppnmhtw qwbpchlg xrpqrgj mlz vnrhwv kptzcdbq qtx whjsms lmrtr xgtmm qzwjvwpp jtwtwvd rwdqbl pnrs gkp gjvnt tpk bbkcm npjsm jbccm