demo03.sql

Expert Oracle Database Architecture 9i and 10g sql源码

variable hash varchar2(100);

begin
    for x in ( select deptno, dname, loc
                 from dept
                where deptno = 10 )
    loop
        dbms_output.put_line( 'Dname:  ' || x.dname );
        dbms_output.put_line( 'Loc:    ' || x.loc );
        dbms_output.put_line( 'Hash:   ' ||
          dbms_crypto.hash
          ( utl_raw.cast_to_raw(x.deptno||'/'||x.dname||'/'||x.loc),
            dbms_crypto.hash_sh1 ) );
          :hash := dbms_crypto.hash
          ( utl_raw.cast_to_raw(x.deptno||'/'||x.dname||'/'||x.loc),
            dbms_crypto.hash_sh1 );
    end loop;
end;
/
begin
    for x in ( select deptno, dname, loc
                 from dept
                where deptno = 10
                  for update nowait )
    loop
        if ( hextoraw( :hash ) <>
             dbms_crypto.hash
             ( utl_raw.cast_to_raw(x.deptno||'/'||x.dname||'/'||x.loc),
               dbms_crypto.hash_sh1 ) )
        then
            raise_application_error(-20001, 'Row was modified' );
        end if;
    end loop;
    update dept
       set dname = lower(dname)
     where deptno = 10;
    commit;
end;
/
begin
    for x in ( select deptno, dname, loc
                 from dept
                where deptno = 10 )
    loop
        dbms_output.put_line( 'Dname:  ' || x.dname );
        dbms_output.put_line( 'Loc:    ' || x.loc );
        dbms_output.put_line( 'Hash:   ' ||
          dbms_crypto.hash
          ( utl_raw.cast_to_raw(x.deptno||'/'||x.dname||'/'||x.loc),
            dbms_crypto.hash_sh1 ) );
    end loop;
end;
/