Technorati Теги:

Реализация проверки бизнес-правил в виде триггеров:

CREATE OR REPLACE TRIGGER check_sal
  BEFORE UPDATE OF sal ON emp
  FOR EACH ROW
  WHEN (new.sal<old.sal OR new.sal>old.sal*1.1)
BEGIN
  RAISE_APPLICATION_ERROR(-20508, ‘Do not decrease salary not increase by more than 10%’);
END;

SHOW ERRORS – показ ошибок компиляции в SQL*Plus

перекомпиляция инвалидных пакетов:

declare
  str varchar2(200);
  err varchar2(2000);
  cursor c_obj is
    select object_name, object_type from user_objects where status=upper(‘invalid’);
begin
  for c1 in c_obj loop
    if c1.object_type=upper(‘package body’) then
      str:=’alter package ‘||c1.object_name||’ compile body’;
    else
      str:=’alter ‘||c1.object_type||’ ‘||c1.object_name||’ compile’;
    end if;
    begin
      execute immediate str;
    exception
      when others then
        select text
          into err
          from user_errors
          where name=c1.object_name and type=c1.object_type and rownum=1;
        dbms_output.put_line(c1.object_name||’:’||err);
        err:=null;
    end;
  end loop;
end;

использование условий типа:

where (i.id, i.date1) in (select k.id, k.date2 from table2) –почему такую конструкцию мы не использовали раньше – загадка, как говориться – век живи – век – учись

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

<span>%d</span> bloggers like this: