Oracle
SQL> --包的重载
SQL> create function to_char(id number) return varchar2(11);
2 /
Warning: Function created with compilation errors.
SQL> create or replace get_sal(A IN number) return number
2 is
3
SQL> create or replace get_sal(A IN number) return number
2 is
3 begin
4 selecr sal into vsal from emp where empno=A;
create or replace get_sal(A IN number) return number
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> create or replace get_sal(A IN number) return number
2 is
3 vsal number;
create or replace get_sal(A IN number) return number
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> create or replace get_sal(A IN number) return number
2 is
3 vsal number;
create or replace get_sal(A IN number) return number
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> create or replace get_sal(A IN number) return number
2 is
3 /
create or replace get_sal(A IN number) return number
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> create package body pack2
2 is function get_sal( IN number) return number
3 is
4 vsal number;
5 begin
6 select sal into vsal from emp where empno=A;
7 return vsal;
8 end;
9
10 /
Warning: Package Body created with compilation errors.
SQL> ed
Wrote file afiedt.buf
1 create package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where empno=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where empno=A;
22 return vsal;
23 end;
24* end;
25 /
create package body pack2
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where empno=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where empno=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where empno=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where empno=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where empno=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where empno=A;
22 return vsal;
23 end;
24* end;
25 /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where empno=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where empno=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> d
SP2-0042: unknown command "d" - rest of line ignored.
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where hiredate=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where hiredate=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PACK2' must be declared
1/14 PLS-00304: cannot compile body of 'PACK2' without its
specification
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where hiredate=A;
22 return vsal;
23 end;
24* end;
SQL> create or replace package body pack2
2 is
3 begin
4 function get_sal(A IN number) return number;
5 fuction get_sal(A IN varchar2)retunr number;
6 function get_sal(A IN hiredate)return number;
7 end;
8 /
Warning: Package Body created with compilation errors.
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 begin
4 function get_sal(A IN number) return number;
5 function get_sal(A IN varchar2)return number;
6 function get_sal(A IN hiredate)return number;
7* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/10 PLS-00103: Encountered the symbol "GET_SAL" when expecting one of
the following:
:= . ( @ % ;
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number;
4 function get_sal(A IN varchar2)return number;
5 function get_sal(A IN hiredate)return number;
6* end;
7
8 //
9 /
Warning: Package Body created with compilation errors.
SQL> ed
Wrote file afiedt.buf
1 create or replace package pack2
2 is
3 function get_sal(A IN number) return number;
4 function get_sal(A IN varchar2)return number;
5 function get_sal(A IN hiredate)return number;
6* end;
7 /
Warning: Package created with compilation errors.
SQL> show error;
Errors for PACKAGE PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: Declaration ignored
5/23 PLS-00201: identifier 'HIREDATE' must be declared
SQL> ed
Wrote file afiedt.buf
1 create or replace package pack2
2 is
3 function get_sal(A IN number) return number;
4 function get_sal(A IN varchar2)return number;
5 function get_sal(A IN date)return number;
6* end;
SQL> /
Package created.
SQL> ed
Wrote file afiedt.buf
1 te or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where hiredate=A;
22 return vsal;
23 end;
24* end;
25 /
te or replace package body pack2
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where hiredate=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4 PL/SQL: SQL Statement ignored
7/30 PL/SQL: ORA-00942: table or view does not exist
14/6 PL/SQL: SQL Statement ignored
14/32 PL/SQL: ORA-00942: table or view does not exist
21/8 PL/SQL: SQL Statement ignored
21/34 PL/SQL: ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from emp where hiredate=A;
22 return vsal;
23 end;
24* end;
SQL> /
Warning: Package Body created with compilation errors.
SQL> show error;
Errors for PACKAGE BODY PACK2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4 PL/SQL: SQL Statement ignored
7/30 PL/SQL: ORA-00942: table or view does not exist
14/6 PL/SQL: SQL Statement ignored
14/32 PL/SQL: ORA-00942: table or view does not exist
21/8 PL/SQL: SQL Statement ignored
21/34 PL/SQL: ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace package body pack2
2 is
3 function get_sal(A IN number) return number
4 is
5 vsal number;
6 begin
7 select sal into vsal from scott.emp where ename=A;
8 return vsal;
9 end;
10 function get_sal(A IN varchar2) return number
11 is
12 vsal number;
13 begin
14 select sal into vsal from scott.emp where empno=A;
15 return vsal;
16 end;
17 function get_sal(A IN date) return number
18 is
19 vsal number;
20 begin
21 select sal into vsal from scott.emp where hiredate=A;
22 return vsal;
23 end;
24* end;
SQL> /
Package body created.
SQL> --3。调用方式
SQL> select get_sal(7839) from dual;
select get_sal(7839) from dual
*
ERROR at line 1:
ORA-00904: "GET_SAL": invalid identifier
SQL> select get_sal(7839) from dual;
select get_sal(7839) from dual
*
ERROR at line 1:
ORA-00904: "GET_SAL": invalid identifier
SQL> select get_sal('20172512 ') from dual;
select get_sal('20172512 ') from dual
*
ERROR at line 1:
ORA-00904: "GET_SAL": invalid identifier
SQL> --触发器
SQL> --1.创建触发器
SQL> create or replace trigger tr1
2 before insert on emp
3 declare
4 begin
5 raise_application_error(-20001,'can not insert ! ');
6 end;
7 /
before insert on emp
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1
2 before insert on scott.emp
3 declare
4 begin
5 raise_application_error(-20001,'can not insert ! ');
6* end;
SQL> /
Trigger created.
SQL> insert into emp(empno) values (123);
insert into emp(empno) values (123)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> insert into scott.emp(empno) values (123);
insert into scott.emp(empno) values (123)
*
ERROR at line 1:
ORA-20001: can not insert !
ORA-06512: at "SYS.TR1", line 3
ORA-04088: error during execution of trigger 'SYS.TR1'
SQL> --2.阻止对emp 表进行CRUD
SQL> create or replace trigger NotCRUD
2 before insert on scott.emp
3 declare
4 begin
5 raise_application_error(-2003,'Not insert!');
6 end;
7 /
Trigger created.
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger NotCRUD
2 before insert or update or delete on scott.emp
3 declare
4 begin
5 raise_application_error(-2003,'Not insert!');
6* end;
SQL> /
Trigger created.
SQL> --3。插入操作
SQL> --3.1 小心删除，小心删除
SQL> ---逻辑组合
SQL>
SQL> create or replace trigger tr1 before insert or update or delete on emp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elseif updating then
7 dbms_output.put_line('updating');
8 elseif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10 end if;
11 /
create or replace trigger tr1 before insert or update or delete on emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before insert or update or delete on scott.mp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elseif updating then
7 dbms_output.put_line('updating');
8 elseif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10* end if;
SQL> /
create or replace trigger tr1 before insert or update or delete on scott.mp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before insert or update or delete on scott.emp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elseif updating then
7 dbms_output.put_line('updating');
8 elseif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10* end if;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8 PLS-00103: Encountered the symbol "UPDATING" when expecting one
of the following:
:= . ( @ % ;
7/8 PLS-00103: Encountered the symbol "DELETING" when expecting one
of the following:
:= . ( @ % ;
9/7 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
( begin case declare else elsif end exit for goto if loop mod
LINE/COL ERROR
-------- -----------------------------------------------------------------
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before insert or update or delete on scott.emp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elseif updating then
7 dbms_output.put_line('updating');
8 elseif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10* end if;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8 PLS-00103: Encountered the symbol "UPDATING" when expecting one
of the following:
:= . ( @ % ;
7/8 PLS-00103: Encountered the symbol "DELETING" when expecting one
of the following:
:= . ( @ % ;
9/7 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
( begin case declare else elsif end exit for goto if loop mod
LINE/COL ERROR
-------- -----------------------------------------------------------------
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before insert or update or delete on scott.emp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elseif updating then
7 dbms_output.put_line('updating');
8 elseif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10* end if;
SQL> /
Warning: Trigger created with compilation errors.
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8 PLS-00103: Encountered the symbol "UPDATING" when expecting one
of the following:
:= . ( @ % ;
7/8 PLS-00103: Encountered the symbol "DELETING" when expecting one
of the following:
:= . ( @ % ;
9/7 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
( begin case declare else elsif end exit for goto if loop mod
LINE/COL ERROR
-------- -----------------------------------------------------------------
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before insert or update or delete on scott.emp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elseif updating then
7 dbms_output.put_line('updating');
8 elseif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10 end if;
11* end;
12 /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8 PLS-00103: Encountered the symbol "UPDATING" when expecting one
of the following:
:= . ( @ % ;
7/8 PLS-00103: Encountered the symbol "DELETING" when expecting one
of the following:
:= . ( @ % ;
10/4 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if
LINE/COL ERROR
-------- -----------------------------------------------------------------
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before insert or update or delete on scott.emp
2 declare
3 begin
4 if inserting then
5 dbms_output.put_line('welcome');
6 elsif updating then
7 dbms_output.put_line('updating');
8 elsif deleting then
9 raise_application_error(-20001,'can not execute DML!');
10 end if;
11* end;
SQL> /
Trigger created.
SQL> ----------demo03
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr2 after delete on emp
2 for each row
3 declare
4 begin
5 dbms_out.put_line('--deleting---- ');
6* end;
7 /
create or replace trigger tr2 after delete on emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr2 after delete on scott.emp
2 for each row
3 declare
4 begin
5 dbms_out.put_line('--deleting---- ');
6* end;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PL/SQL: Statement ignored
3/3 PLS-00201: identifier 'DBMS_OUT.PUT_LINE' must be declared
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr2 after delete on scott.emp
2 for each row
3 declare
4 begin
5 dbms_output.put_line('--deleting---- ');
6* end;
SQL> /
Trigger created.
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr3 after delete on scott.emp
2 declare
3 begin
4 dbms_output.put_line('--after----table ');
5* end;
SQL> /
Trigger created.
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr3 after delete on scott.emp
2 declare
3 begin
4 dbms_output.put_line('--after----table ');
5* end;
SQL> delete from scott.emp where ename='KING'
2 /
delete from scott.emp where ename='KING'
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -2003 is out of
range
ORA-06512: at "SYS.NOTCRUD", line 3
ORA-04088: error during execution of trigger 'SYS.NOTCRUD'
SQL> drop trigger NOTCRUD
2 /
Trigger dropped.
SQL> delete from scott.emp where ename='KING'
2 /
delete from scott.emp where ename='KING'
*
ERROR at line 1:
ORA-20001: can not execute DML!
ORA-06512: at "SYS.TR1", line 8
ORA-04088: error during execution of trigger 'SYS.TR1'
SQL> drop trigger TR1
2 /
Trigger dropped.
SQL> delete from scott.emp where ename='KING'
2 /
--deleting----
--after----table
1 row deleted.
SQL> --4.证明表级所和行级锁
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 after insert or update or delete on emp
2 for each row
3 declare
4 begin
5 if updating then
6 insert into zz values (sysdate,'EMP','U',:old.sal,:new.sal);
7 end if;
8* end;
9 /
create or replace trigger tr1 after insert or update or delete on emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 after insert or update or delete on scott.emp
2 for each row
3 declare
4 begin
5 if updating then
6 insert into zz values (sysdate,'EMP','U',:old.sal,:new.sal);
7 end if;
8* end;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/12 PL/SQL: SQL Statement ignored
4/24 PL/SQL: ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 after insert or update or delete on scott.emp
2 for each row
3 declare
4 begin
5 if updating then
6 insert into zz values (sysdate,'EMP','U',:old.sal,:new.sal);
7 end if;
8* end;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/12 PL/SQL: SQL Statement ignored
4/24 PL/SQL: ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 after insert or update or delete on scott.emp
2 for each row
3 declare
4 begin
5 if updating then
6 insert into zz values (sysdate,'EMP','U',:old.sal,:new.sal);
7 end if;
8* end;
SQL> conn / as sysdba;
Connected.
SQL> grant create view to scott;
Grant succeeded.
SQL> conn scott/tiger;
Connected.
SQL> create or replace view v1 as select dept.deptno,empno,ename,loc
2 from emp.dept
3 where emp.dept=dept.deptno;
from emp.dept
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> create or replace view v1 as select dept.deptno,empno,ename,loc
2 from emp,dept
3 where emp.dept=dept.deptno;
where emp.dept=dept.deptno
*
ERROR at line 3:
ORA-00904: "EMP"."DEPT": invalid identifier
SQL> create or replace view v1 as select dept.deptno,empno,ename,loc
2 from emp,dept
3 where emp.deptno=dept.deptno;
View created.
SQL> /
View created.
SQL> select * from v1;
DEPTNO EMPNO ENAME LOC
---------- ---------- ---------- -------------
10 7782 CLARK NEW YORK
10 7934 MILLER NEW YORK
20 7788 nihao DALLAS
20 7902 FORD DALLAS
20 7876 ADAMS DALLAS
20 7369 SMITH DALLAS
20 7566 JONES DALLAS
30 7900 JAMES CHICAGO
30 7844 TURNER CHICAGO
30 7698 BLAKE CHICAGO
30 7654 MARTIN CHICAGO
DEPTNO EMPNO ENAME LOC
---------- ---------- ---------- -------------
30 7499 ALLEN CHICAGO
30 7521 WARD CHICAGO
13 rows selected.
SQL> insert into v1 values(50,999,'C','BJ');
insert into v1 values(50,999,'C','BJ')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> create or replace trigger tr4 instead of insert on v1
2 declare
3 begin
4 insert into dept(deptno,loc) values (new.deptno,:new.loc);
5 insert into emp(deptno,empno,ename)values (:new.deptno,:new.empno,:new.ename);
6 end;
7 /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER TR4:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2 PL/SQL: SQL Statement ignored
3/43 PL/SQL: ORA-00984: column not allowed here
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr4 instead of insert on v1
2 declare
3 begin
4 insert into dept(deptno,loc) values (:new.deptno,:new.loc);
5 insert into emp(deptno,empno,ename)values (:new.deptno,:new.empno,:new.ename);
6* end;
SQL> /
Trigger created.
SQL> insert into v1 values(50,999,'C','BJ');
insert into v1 values(50,999,'C','BJ')
*
ERROR at line 1:
ORA-04098: trigger 'SYS.TR1' is invalid and failed re-validation
ORA-06512: at "SCOTT.TR4", line 4
ORA-04088: error during execution of trigger 'SCOTT.TR4'
SQL> drop trigger tr1;
drop trigger tr1
*
ERROR at line 1:
ORA-04080: trigger 'TR1' does not exist
SQL> drop trigger TR1
2 /
drop trigger TR1
*
ERROR at line 1:
ORA-04080: trigger 'TR1' does not exist
SQL> insert into v1 values(50,999,'C','BJ');\
2 /
insert into v1 values(50,999,'C','BJ');\
*
ERROR at line 1:
ORA-00911: invalid character
SQL> insert into v1 values(50,999,'C','BJ');
insert into v1 values(50,999,'C','BJ')
*
ERROR at line 1:
ORA-04098: trigger 'SYS.TR1' is invalid and failed re-validation
ORA-06512: at "SCOTT.TR4", line 4
ORA-04088: error during execution of trigger 'SCOTT.TR4'
SQL> insert into v1 values(50,999,'C','BJ');
insert into v1 values(50,999,'C','BJ')
*
ERROR at line 1:
ORA-04098: trigger 'SYS.TR1' is invalid and failed re-validation
ORA-06512: at "SCOTT.TR4", line 4
ORA-04088: error during execution of trigger 'SCOTT.TR4'
SQL> drop trigger TR1
2 /
drop trigger TR1
*
ERROR at line 1:
ORA-04080: trigger 'TR1' does not exist
SQL> insert into v1 values(50,999,'C','BJ');
insert into v1 values(50,999,'C','BJ')
*
ERROR at line 1:
ORA-04098: trigger 'SYS.TR1' is invalid and failed re-validation
ORA-06512: at "SCOTT.TR4", line 4
ORA-04088: error during execution of trigger 'SCOTT.TR4'
SQL> show user;
USER is "SCOTT"
SQL> conn / as sysdba;
Connected.
SQL> drop trigger TR1
2
SQL> drop trigger TR1
2 /
Trigger dropped.
SQL> conn scott/tiger;
Connected.
SQL> insert into v1 values(50,999,'C','BJ');
1 row created.
SQL> create or replace trigger tr4 instead of insert on v1
2 insert into dept(deptno,loc) values (new.deptno,:new.loc);
3 insert into emp(deptno,empno,ename)values (:new.deptno,:new.empno,:new.ename);
4 /
Warning: Trigger created with compilation errors.
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr4 instead of insert on v1
2* if inserting then
3 /
if inserting then
*
ERROR at line 2:
ORA-04079: invalid trigger specification
SQL> 04.系统触发器
SP2-0734: unknown command beginning "04.系统议." - rest of line ignored.
SQL> --04.系统触发器
SQL> --用户，DDL
SQL> --数据库
SQL> --01.阻止scott 用户执行drop 语句
SQL> create or replace trigger tr1 before drop on scott.schema
2 declare
3 begin
4 raise_application_error(-20001,'cat not drop');
5 end;
6 /
Trigger created.
SQL> show user;
USER is "SCOTT"
SQL> drop trigger tr1
2 /
Trigger dropped.
SQL> create or replace trigger tr1 before drop on scott.schema
2 begin
3 raise_application_error(-20001,'can not drop');
4 end;
5 /
Trigger created.
SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: can not drop
ORA-06512: at line 2
SQL> create or replace trigger tr1 before drop on scott.schema
2 begin
3 raise_application_error(-20001,'你不能删除我呀^--^);
4 end;
5 /
Warning: Trigger created with compilation errors.
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr1 before drop on scott.schema
2 begin
3 raise_application_error(-20001,'你不能删除我呀^--^');
4* end;
SQL> /
Trigger created.
SQL> drop table emp
2 /
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: ?????????????????????^--^
ORA-06512: at line 2
SQL> ed
Wrote file afiedt.buf
SP2-0223: No lines in SQL buffer.
1 /
SQL> create or replace trigger tr1 before drop on scott.schema
2 begin
3 raise_application_error(-20001,'你不能删除我呀^--^);
4 end;
5 /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/34 PLS-00103: Encountered the symbol "?????????????????????^" when
expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string
SQL> show errors;
Errors for TRIGGER TR1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/34 PLS-00103: Encountered the symbol "?????????????????????^" when
expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string
SQL> host cls
SQL> alter table zz add(vtype varchar2(20),vname varchar2(20));
alter table zz add(vtype varchar2(20),vname varchar2(20))
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table (vtype varchar2(20),vname varchar2(20));
create table (vtype varchar2(20),vname varchar2(20))
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> create table ztable(vtype varchar2(20),vname varchar2(20));
Table created.
SQL>
SQL> create table zz(vtype varchar2(20),vname varchar2(20));
Table created.
SQL> create or replace trigger tr3 after logon on database
2 declare
3 begin
4 if ora_login_user='SCOTT' THEN
5 raise_application_error('-2000','哈哈哈，就不让你登');
6 END IF;
7 END;
8 /
create or replace trigger tr3 after logon on database
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> CONN /
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn as sysdba;
Enter user-name: sys
Connected.
SQL> create or replace trigger tr3 after logon on database
2 declare
3 if ora_login_user='SCOTT' THEN
4 raise_application_error('-2000','哈哈哈，就不让你登');
5 end if;
6 end;
7 /
create or replace trigger tr3 after logon on database
*
ERROR at line 1:
ORA-04095: trigger 'TR3' already exists on another table, cannot replace it
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr3333 after logon on database
2 declare
3 if ora_login_user='SCOTT' THEN
4 raise_application_error('-2000','哈哈哈，就不让你登');
5 end if;
6* end;
SQL> /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER TR3333:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "IF" to continue.
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr3333 after logon on database
2 declare
3 begin
4 if ora_login_user='SCOTT' THEN
5 raise_application_error('-2000','哈哈哈，就不让你登');
6 end if;
7* end;
SQL> /
Trigger created.
SQL> conn scott/tiger;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -2000 is out of
range
ORA-06512: at line 4
Warning: You are no longer connected to ORACLE.
SQL> conn scott/tiger;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -2000 is out of
range
ORA-06512: at line 4
SQL> conn scott/tiger;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -2000 is out of
range
ORA-06512: at line 4
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr3333 after logon on database
2 declare
3 begin
4 if ora_login_user='SCOTT' THEN
5 raise_application_error('-!!!!2000','哈哈哈i，就不让你登');
6 end if;
7* end;
SQL> /
SP2-0640: Not connected
SQL> /
SP2-0640: Not connected
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger tr3333 after logon on database
2 declare
3 begin
4 if ora_login_user='SCOTT' THEN
5 raise_application_error('2000','哈哈哈i，就不让你登~~> ^0-0 ^<~~ '
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69949806/viewspace-2663810/ ，如需转载，请注明出处，否则将追究法律责任。
