PL/SQL



Isopen是打开游标

Rowecount记录数

&从用户输入

Reverse表反转由大到小

%rowtype表示参照这张表的一行类型全部。

Index by table

For update用于锁表（表级锁）

For update nowait锁表但不等待

使用where current of游标

Raise抛出异常

Job表示定时的工作。

 

 

PL/SQL

什么是PL/SQL？

在 SQL的基础上，加了流程的控制，以及块的结构

是 ORACLE独有的一种语言

使用PL/SQL好处？

1、有了流程的概念，可以实现更加灵活的业务逻辑

2、一次编译，永久运行。

3、将多条SQL语句放在一起运行，提高性能

4、作为后台程序运行，提高性能

5、方便数据库的移置(exp , copy , RMAN)

6、减少前端开发语言的代码量

块的类型：

1、无名块

2、命名块：过程、函数、包、触发器

 

 

PL/SQL块的基本结构：

Declare

声明部分

begin

语句执行体（必须的）;

exception

异常处理;

END;

 

创建存储过程的基本结构：

Createorreplaceprocedure过程名(

参数

）is

begin

语句执行体（必须的）;

exception

异常处理;

End;

 

变量类型：

1、标量:charvarchar2numberdateBooleanlong

2、混合:自定义的，是个集合记录record

3、参考：%type（参考某个字段的类型）  %rowtype（????）

4、大对象：blob clob nctlobbfile???????????

标识符命名规则：

1、字母开头

2、含有A----Z，a-----z，0-----9，_ $ #

3、不能ORACLE保留字

4、长度不超过30

--无名快：查询员工号为144的员工的年薪（不包括奖金）--

Declare

V_sal employees.salary%TYPE;

Begin

Select salary *12into v_sal from employees

whereemployee_id=144 ;

--输出相当于java中的system.out.println()--

Dbms_output.put_line(v_sal );

EXCEPTION

WHENNO_DATA_FOUNDTHEN—没找到数据--

Dbms_output.put_line('没找到数据');

END;

 

单行函数：

1、字符型lower（转换为小写）upper（转换为大写） initcap（？？？）concat（？？？） substr（截取） instr（？？？）

Length（？？？） lpad（？？？） rpad（？？？） rtrim（？？？） ltrim（？？？）

2、数值型 round（四舍五入） trunc（截取）mod（取模）

3、日期型 months_bwteenadd_months round trunk

Last_day next_day

4、转换型 to_number to_charto_date

5、其他 nvl nvl2 nullif case decode

 

PL/SQL中的SQL

SELECT。。。INTO。。。FROM。。。。WHERE。。。

只能返回一条记录

如果没有返回行：NO_DATA_FOUND—抛出异常--

如果返回多行： TOO_MANY_TOWS--抛出异常--

 

--这个无名快怎么调用呢？？？？？--

解决办法，通过异常处理：

Declare

V_sal employees.salary%type;

Begin

Select salary *12into v_salfrom employees

whereemployee_id=844 ;

Dbms_output.put_line(v_sal );

exception

whenno_data_foundthen

Dbms_output.put_line('没有这个员工');

End;

 

--隐含游标SQL的使用 %rowcount代表隐含游标的sql的类型 --

createtable e1 asselect *from employees;

begin

deletefrom e1 where department_id =90;

dbms_output.put_line(‘删除记录行数：’ || sql%rowcount);

end;

 

 

 

隐含游标：

SELECT结果是一行

UPDATE、INSERT、DELETE记录行数可以多条

隐含游标的名字：SQL，这个是固定的

隐含游标的属性：%found（？？？） %notfound（？？？） %isopen（打开游标）%rowcount（累计的记录数）

可以直接使用，写法sql%属性

Rooms:

Id seats

1100

2200

Update roomsset seats=50whereid=9;

--练习：如果没有9号房间，需要INSERT 9 50--

createtable rooms(

idnumber(1)primarykey,

seatsnumber(3));

insertinto rooms values(1,100);

insertinto rooms values(2,200);

 

--无名快这里没写定义部分--

begin

update roomsset seats=50whereid=9;

ifsql%notfoundthen—-sql含义？？？？--

insertinto roomsvalues(9,50);

endif;

commit;

end;

流程控制语言

1.if……then……elsif……then….else….end if;

2.loop……end loop;

3.while循环

while……loop……end loop;

4.for循环

 

 

---流程控制语言------------------

条件语句：

if条件1then

语句1；

elsif条件2then

语句2；

else

语句3；

ENDIF；

--案例--

Declare

V_dept BANK_ACCOUNT.ACCOUNT_ID%type := &account_id;

begin

updateBANK_ACCOUNTset ACCOUNT_BALANCE=1800where

account_id = V_dept;

ifsql%notfoundthen

dbms_output.put_line('ERROR!!');

ELSE

dbms_output.put_line('OK!!');

endif;

IFsql%ROWCOUNT=1THEN

dbms_output.put_line('HAO!!');

ELSE

dbms_output.put_line('BUHAO!!');

ENDIF;

commit;

end;

------案例：loop循环插入数据------

CREATETABLE loop1 (loopIDNUMBER(5),loopValueVARCHAR2(30) )

SELECT * FROM loop1

 

 

declare

v1number(2):=1;

begin

loop

insertinto loop1values(v1,'LOOP');

v1 := v1 +1;

exitwhen v1 >10;

endloop;

commit;

end;

-----案列：while循环-----

declare

v1number(2):=1;

begin

WHILE v1 <11loop

insertinto loop1values(v1,'WHILE');

v1 := v1 +1;

endloop;

commit;

end;

-----案列：for循环----

begin

for v1in1..10loop

insertinto loop1values(v1,'FOR');

endloop;

commit;

end;

----

begin

for v1inreverse10..15loop—reverse表示翻转循环从15开始，10结束--

insertinto loop1values(v1,'FOR');

endloop;

commit;

end;

 

 

----组合数据类型 RECORD（一条条的记录）---

declare

type t_empisrecord

(nameemployees.last_name%type,

sal employees.salary%type,

job employees.job_id%type);

v_emp t_emp;--相当于把类型赋值给变量，先定义再赋值--

begin

selectlast_name,salary,job_idinto v_emp

fromemployeeswhere employee_id=144;

dbms_output.put_line

(v_emp.name||' '||v_emp.sal||' '||v_emp.job);

end;

----类型为：%ROWTYPE表示类型与表中整行都相同-----

declare

v_emp employees%rowtype;

begin

select *into v_emp

fromemployeeswhere employee_id=144;

dbms_output.put_line

(v_emp.last_name||' '||v_emp.salary||

' '||v_emp.job_id);

end;

 

 

----类型为：INDEX BY TABLE？？？？？？？？？？-----

Declare

TYPE t_salISTABLEOF

employees.salary%TYPE

INDEXBYBINARY_INTEGER;

V_sal t_sal;

Begin

For Iin100..104loop

Select salaryinto v_sal(i)

Fromemployeeswhere employee_id = I;

ENDLOOP;

END;

----、类型为：INDEX BY TABLE OF RECORDS？？？？？---

Declare

TYPEemp_table_typeistableof

employees%ROWTYPEINDEXBYBINARY_INTEGER;

my_emp_table emp_table_type;

BEGIN

FOR iIN my_emp_table.FIRST..my_emp_table.LASTLOOP

DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);

ENDLOOP;

END;

 

Declare

TYPE t_nameISTABLEOF

Departments.department_name%TYPE

INDEXBYBINARY_INTEGER;

V_name t_name;

Begin

For Iin1..7loop

Selectdepartment_nameinto v_name(i)

Fromdepartmentswhere department_id = I*10;

Endloop;

For Iinv_name.first..v_name.lastloop

Dbms_output.put_line(i||' '||v_name(i));

ENDLOOP;

END;

 

游标

----游标：显示、隐示----

/*

游标：是指向一个结果集的指针

类型：隐式和显式

隐式游标：SQL

SELECT结果只有一行

UPDATE、DELETE、INSERT

显式游标：四个步骤

定义和申明、打开、提取（循环）、关闭

*/

--使用游标，显示前10个部门ID和名字

Declare

Type t_deptisrecord(

Iddepartments.department_id%type,

Namedepartments.department_name%type);

V_dept t_dept;

/*声明*/

Cursor c_deptisselectdepartment_id,department_name

Fromdepartments;

BEGIN

/*打开*/

Open c_dept;

LOOP

/*提取*/

Fetch c_deptinto v_dept;

Exitwhen c_dept%notfoundor c_dept%rowcount >10;

Dbms_output.put_line(v_dept.id|| v_dept.name);

Endloop;

/*关闭*/

Close c_dept;

End;

 

---不需要写：OPEN FETCH（不需要变量的定义） CLOSE----

Declare

Cursor c_deptisselectdepartment_id,department_name

Fromdepartments;

Begin

For Iin c_deptloop

If c_dept%rowcount = 11then

Exit;

else

Dbms_output.put_line(i.department_id||

i.department_name);

Endif;

Endloop;

End;

---***如果不定义游标***----

Begin

For iin（selectdepartment_id,department_name

Fromdepartments）loop

Dbms_output.put_line(i.department_id||

i.department_name);

Endloop;

End;

----高级显式游标----

DECLARE

Cursor c_emp(id employees.department_id%type)

Isselect last_namefrom employees

Wheredepartment_id =id;

V_charvarchar2(250);

V_anumber(3):=10;

Begin

While v_a <110loop

For Iin c_emp(v_a)loop

V_char:=v_char ||' ' ||i.last_name;

Iflength(v_char)>240then

Dbms_output.put_line(v_a||'部门的人员'||v_char);

V_char:=null;

Endif;

Endloop;

Dbms_output.put_line(v_a||'部门的人员'||v_char);

Dbms_output.put_line('----------------------------------

-------------------------------------------');

v_char :=null;

V_a := v_a +10;

Endloop;

End;

 

/*

游标定义中有for update(用于锁表)、

使用where current of游标

将10---100号部门人员的工作年限，回填到a列

*/

Declare

Cursor c_emp (id emp.department_id%type)

is

Selecthire_datefrom emp;

Wheredepartment_id =id

--用于锁表，同时不等待，如果等待就抛出异常--

Forupdateofnowait;

--定义变量--

 V_1number(3) := 10;

Begin

While v_1 <110loop

For iin c_emp(v_1)loop

Update empset

a=round(months_between(sysdate,i.hire_date)/12)

wherecurrentof c_emp;

endloop;

v_1:= v_1+10;

endloop;

commit;

end;

 

oracle for update和for update nowait的区别

对比区别：

　　select * from TTable1forupdate锁定表的所有行，只能读不能写

　　2 select *from TTable1where pkid =1forupdate只锁定pkid=1的行

　　3 select *from Table1 ajoin Table2 bon a.pkid=b.pkidforupdate锁定两个表的所有记录

　　4select *from Table1 ajoin Table2 b on a.pkid=b.pkidwhere a.pkid =10forupdate锁定两个表的中满足条件的行

　　5.select *from Table1 ajoin Table2 b on a.pkid=b.pkidwhere a.pkid =10forupdateof a.pkid只锁定Table1中满足条件的行

　　forupdate是把所有的表都锁点forupdateof根据of后表的条件锁定相对应的表

　　-----------

　　关于NOWAIT(如果一定要用FORUPDATE，我更建议加上NOWAIT)

　　当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待(比如:要查的行已经被其它事务锁了,当前的锁事务与之冲突,加上nowait,当前的事务会结束会提示错误并立即结束 STATEMENT而不再等待).

　　如果加了for update后该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。

　　因为FOR  UPDATE子句获得了锁，所以COMMIT将释放这些锁。当锁释放了，该游标就无效了。

　　就是这些区别了

 

*/

异常处理

----异常处理---

/*

类型：

1、预定义异常（ORACLE ERRORS）

1）异常的名字ORACLE，已经定义好了，各自对应一个ERROR

2）异常的名字ORACLE，没有定义好，需要我们去初始化，调用pragma

Exception_init

2、自定义异常（logical）

*/

---系统已定义---

Declare

。。。。

Begin

。。。。

Exception

Whenno_data_foundthen

。。。。

End;

------案例：自定义异常--

Declare

E_1exception;--自己定义一个异常--

Begin

。。。。。。

Exception

When e_1then

。。。。。

End;

/*

自定义的分三个步骤：

1）定义

2）扑获（IF）

3）处理

*/

declare

e_1exception;

/*自定义错误代码可不写自定义号码段：20000-20999*/

PRAGMAEXCEPTION_INIT (e_1, -2292);--即这句哈可写可不写--

begin

updateemployeesset salary=1000where employee_id = &1;

/*捕获异常*/

ifsql%notfoundthen

raise e_1;--raise抛出异常--

endif;

dbms_output.put_line('$$$$$');

exception

when e_1THEN

/*处理异常*/

dbms_output.put_line('NO MAN!');

end;

 

---过程-----

/*

命名块：

1）过程：描述一组有关联的操作

2）函数：做算术运算，有返回值

参数模式：

1） IN 将数值带入到过程里面

2） OUT 将数值带出到过程外面

3） IN OUT 将数值带入到过程里面，还可以将数值带出到过程外面

*/

 

/*写一个存储过程，通过ID查询员工的工资信息*/

Createorreplaceprocedure pro1

(idinemployees.employee_id%type,

Salout employees.salary%type)

Is

Begin

Select salaryinto salfrom employees

Whereemployee_id =id;

Exception

Whenno_data_foundthen

Dbms_output.put_line(‘NO MAN!’);

End;

/*这里在plsql里面调用调用过程*/

declare

v_1 employees.salary%type;

begin

pro1(&1,v_1);

dbms_output.put_line(v_1);

end;

/*在sqlplus中调用过程*/

Variable anumber

Execpro1(&1,:a)

Print a

 

 

 

 

Createorreplaceprocedure pro2(ainoutnumber)

Is

Begin

A:=a*a*a;

End;

 

Declare

V1number(6);

Begin

For Iin1..30loop

V1:= I;

Pro2(v1);

Dbms_output.put_line(i||' ' ||v1);

Endloop;

End;

---函数-------

/*

特点：

1）有返回值

2）调用要在表达式中

3）适合做算术运算

*/

创建函数的基本结构：

Createorreplacefunction函数名(

……

)

return返回值的类型

is

定义返回值及其类型

begin

语句执行体（必须的）;

return返回值；

exception

异常处理

end;

--案例--

CREATEORREPLACEFUNCTION get_sal

(p_idINemployees.employee_id%TYPE)

RETURNNUMBER

IS

v_salary employees.salary%TYPE :=0;

BEGIN

SELECT salaryINTO v_salaryFROM employees

WHEREemployee_id = p_id;

RETURNv_salary;

Exception

Whenno_data_foundthen

Dbms_output.put_line(‘NOTHE MAN!’);

END get_sal;

 

/*在plsql调用：*/

declare

v_1 employees.salary%type;

begin

v_1 := get_sal(&1);--:=表示赋值--

Dbms_output.put_line(v_1);

end;

 

---案例2---

CREATEORREPLACEFUNCTION get_sal2

(p_idINemployees.employee_id%TYPE,

P_jobout employees.job_id%type)

RETURNNUMBER

IS

v_salary employees.salary%TYPE :=0;

BEGIN

SELECT salary,job_idINTO v_salary , p_job

FROMemployeesWHERE employee_id = p_id;

RETURNv_salary;

Exception

Whenno_data_foundthen

Dbms_output.put_line('NO THE MAN!');

ENDget_sal2;

/*调用*/

declare

v_1 employees.salary%type;

v_2 employees.job_id%type;

begin

v_1 := get_sal2(&1,v_2);

Dbms_output.put_line(v_1);

Dbms_output.put_line(v_2);

end;

----管理子程序---

系统权限：

Createanyprocedure

Alter…

Drop

Execute

对象权限

Execute

Grant ….To…

查询HR用户创建的存储过程名字

Selectobject_name

Fromuser_objects

Whereobject_type='PROCEDURE';

查询HR用户创建的函数名字

Selectobject_name

Fromuser_objects

Whereobject_type='FUNCTION';

查询某个函数的定义

select textfrom user_source

wherename='FUNC1'

orderby line;

查看PKG中过程和函数说明

Descdbms_output

查询函数、过程的编译错误

SELECT line ||’/’ || position POS, text

FROMuser_errors

WHEREname = ’LOG_EXECUTION’

ORDERBY line;

Showerrors

Desc func1

Desc proc1

 

查看自己的系统权限

Select *from session_privs;

查看自己的角色

Select *from SESSION_ROLES;

如果是开发人员需要的系统角色：

CONNECT

RESOURCE

GRANTCONNECT,RESOURCETO SCOTT;

 

---包----

/*

包：是过程和函数的封装

包分为两个部分：包和包体

*/

/*包头定义*/

CREATEORREPLACEPACKAGE PKG1

IS

PROCEDURE add_job

( p_emp_id job_history.employee_id%type

, p_start_date job_history.start_date%type

, p_end_date job_history.end_date%type

, p_job_id job_history.job_id%type

, p_department_id job_history.department_id%type

);

FUNCTION sal

(p_idINemployees.employee_id%TYPE)

RETURNNUMBER;

END;

/*包体定义*/

CREATEORREPLACEPACKAGEBODY PKG1

IS

PROCEDURE add_job

( p_emp_id job_history.employee_id%type

, p_start_date job_history.start_date%type

, p_end_date job_history.end_date%type

, p_job_id job_history.job_id%type

, p_department_id job_history.department_id%type

)

IS

BEGIN

INSERTINTO job_history(employee_id, start_date,

end_date,

job_id, department_id)

VALUES(p_emp_id,p_start_date, p_end_date, p_job_id,

p_department_id);

END add_job;

FUNCTION sal

(p_idINemployees.employee_id%TYPE)

RETURNNUMBER

IS

v_salary employees.salary%TYPE :=0;

BEGIN

SELECT salaryINTO v_salaryFROM employees

WHEREemployee_id = p_id;

RETURNv_salary;

Exception

Whenno_data_foundthen

Dbms_output.put_line('NO THE MAN!');

END sal;

END;

/

调用：

SELECTPKG1.SAL(144)FROM DUAL;

EXECPKG1.ADD_JOB(144,SYSDATE-7,SYSDATE,'ST_CLERK',10)

 

/*包的高级使用*/

 

1、过程和函数的重载

几个过程或者函数，同名字，但是参数的个数或者数据类型不同

CREATEORREPLACEPACKAGE over_pack

IS

PROCEDURE add_dept

(p_deptnoINdepartments.department_id%TYPE,

p_nameINdepartments.department_name%TYPE

DEFAULT’unknown’,

p_locINdepartments.location_id%TYPEDEFAULT0);

PROCEDURE add_dept

(p_nameINdepartments.department_name%TYPE

DEFAULT’unknown’,

p_locINdepartments.location_id%TYPEDEFAULT0);

ENDover_pack;

2、包中的过程、函数次序：

先定义、后使用

SELECT语句中，可以直接调用包中的函数

在包中定义的游标，可以用于包中的所有过程、函数

CREATEORREPLACEPACKAGE pack_cur

IS

CURSOR c1ISSELECT employee_id

FROMemployees

ORDERBY employee_idDESC;

PROCEDUREproc1_3rows;

PROCEDURE proc4_6rows;

ENDpack_cur;

-----

CREATEORREPLACEPACKAGEBODY pack_curIS

v_empnoNUMBERIS

PROCEDUREproc1_3rowsIS

BEGIN

OPEN c1;

LOOP

FETCH c1INTO v_empno;

DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno));

EXITWHEN c1%ROWCOUNT >=3;

ENDLOOP;

ENDproc1_3rows;

PROCEDUREproc4_6rowsIS

BEGIN

-- OPEN c1;

LOOP

FETCH c1INTO v_empno;

DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno));

EXITWHEN c1%ROWCOUNT >=6;

ENDLOOP;

CLOSE c1;

ENDproc4_6rows;

ENDpack_cur;

 

6、在包中可以使用RECORDTABLE

CREATEORREPLACEPACKAGE emp_package

IS

TYPEemp_table_typeISTABLEOF employees%ROWTYPE

INDEXBYBINARY_INTEGER;

PROCEDUREread_emp_table

(p_emp_tableOUTemp_table_type);

ENDemp_package;

 

CREATEORREPLACEPACKAGEBODY emp_packageIS

PROCEDUREread_emp_table

(p_emp_tableOUTemp_table_type)IS

iBINARY_INTEGER :=0;

BEGIN

FORemp_recordIN (SELECT *FROM employees)

LOOP

p_emp_table(i) := emp_record;

i:= i+1;

ENDLOOP;

ENDread_emp_table;

ENDemp_package;

动态SQL

---、动态SQL：使用EXECUTE IMMEDIATE

 

CREATEPROCEDURE del_rows

(p_table_nameINVARCHAR2,

p_rows_deldOUTNUMBER)

IS

BEGIN

EXECUTEIMMEDIATE ’deletefrom ’||p_table_name;

p_rows_deld :=SQL%ROWCOUNT;

END;

 

VARIABLE deletedNUMBER

EXECUTEdel_rows(’test_employees’,:deleted)

PRINT deleted

---

Createorreplaceprocedure upd_pro

(idnumber , numoutnumber)

Is

begin

Executeimmediate

'update emp set salary=salary*1.2 where department_id= '||id ;

Num :=sql%rowcount;

End;

/*命令窗口*/

variable v1number

exec upd_pro(30,:v1)

----job----

使用Submit()过程，工作被正常地计划好。

这个过程有五个参数：job、what、next_date、interval与no_parse。

 

PROCEDURE Submit (job      OUT binary_ineger,

What     IN varchar2,

next_dateIN date,

interval IN varchar2,

no_parse IN booean:=FALSE)

 

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。

what参数是将被执行的PL/SQL代码块。

next_date参数指识何时将运行这个工作。

interval参数何时这个工作将被重执行。

no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE

指示此PL/SQL代码在它第一次执行时应进行语法分析，

而FALSE指示本PL/SQL代码应立即进行语法分析。

 

在command window窗口中执行下面脚本

variable job1number;

begin

sys.dbms_job.submit(job => :job,

what =>'prc_g_test;',

next_date => to_date('22-10-2008 10:06:41','dd-mm-yyyy hh24:mi:ss'),

interval =>'sysdate+1/1440');--每天1440分钟，即一分钟运行test过程一次

commit;

end;

---在plSQL中的做法是----

declare  

jobnumber;     

begin

sys.dbms_job.submit(job,'prc_g_test;',sysdate,'sysdate+1/1440');

end;

查看创建的job

查看相关job信息

1、相关视图

dba_jobs

all_jobs

user_jobs

dba_jobs_running包含正在运行job相关信息。

如：

select *from dba_jobs

 

6、运行JOB

说明：Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：

SQL>begin

2 dbms_job.run(:job);

3 end;

4 /

 

----------------------------------------------------------------------------------

在plSQL中的做法是：

begin

dbms_job.run(3017);

end;

----------------------------------------------------------------------------------

 

7、删除JOB

SQL>begin

2 dbms_job.remove(:job);--:job可以用dba_jobs.job的值代替如：1198

3 end;

4 /

 

--触发器--

/*

是PL/SQL块，定义在表、视图、用户或者数据库上

自动激活，不需要调用

时间：前触发、后触发、替代

事件：INSERT UPDATE DELETE

语句级、行级

触发器中不可以写COMMIT ROLLBACK

*/

CREATEORREPLACETRIGGER secure_emp

BEFOREINSERTON employees

BEGIN

IF(TO_CHAR(SYSDATE,'DY')IN ('SAT','SUN'))OR

(TO_CHAR(SYSDATE,'HH24:MI')

NOTBETWEEN'08:00'AND'18:00')

THENRAISE_APPLICATION_ERROR (-20500,'ERROR');

ENDIF;

END;

-----

CREATEORREPLACETRIGGER EMP_TR1

BEFOREINSERTORUPDATEOF SALARYON EMPLOYEES

FOREACHROW

BEGIN

IF:NEW.SALARY<2000OR :NEW.SALARY>10000THEN

RAISE_APPLICATION_ERROR(-20200,'INVALID SALARY! ');

ENDIF;

END;

---

对于不同操作的判断

CREATEORREPLACETRIGGER secure_emp

BEFOREINSERTORUPDATEORDELETEON employees

BEGIN

IF (TO_CHAR(SYSDATE,’DY’)IN (’SAT’,’SUN’))OR

(TO_CHAR (SYSDATE, ’HH24’) NOTBETWEEN ’08’AND ’18’)

THEN

IF DELETINGTHEN

RAISE_APPLICATION_ERROR (-20502,’NOTdelete’);

ELSIFINSERTINGTHEN

RAISE_APPLICATION_ERROR (-20500,’NOTinsert’);

ELSIF UPDATING(’SALARY’)THEN

RAISE_APPLICATION_ERROR (-20503,’NOTupdate’);

ELSE

RAISE_APPLICATION_ERROR (-20504,’ERRORS’);

ENDIF;

ENDIF;

END;

---行级----

CREATEORREPLACETRIGGER restrict_salary

BEFOREINSERTORUPDATEOF salaryON employees

FOREACHROW

BEGIN

IFNOT (:NEW.job_idIN (’AD_PRES’,’AD_VP’))

AND:NEW.salary >15000

THEN

RAISE_APPLICATION_ERROR (-20202,’Employee

cannot earn this amount’);

ENDIF;

END;

---

创建一个跟踪表，记录SALARY、JOBID的修改时间、人员和新旧值

Createtable audit1(

Usernamechar(10),

Edit_datedate,

OldsalNUMBER(8,2),

NewsalNUMBER(8,2),

OldjobVARCHAR2(10),

NewjobVARCHAR2(10));

Createorreplacetrigger emp_tr2

Afterinsertorupdateordeleteof salary, job_idon employees

foreachrow

begin

insertinto audit1values

(user,sysdate, :old.salary,:new.salary , :old.job_id, :new.job_id);

End;

-----

在触发器中可以调用过程和函数

CREATEORREPLACETRIGGER audit_emp_trig

AFTERUPDATEorINSERTorDELETEon EMPLOYEES

FOREACHROW

BEGIN

IF DELETINGTHEN

var_pack.set_g_del(1);

ELSIFINSERTINGTHEN

var_pack.set_g_ins(1);

ELSIF UPDATING(’SALARY’)THEN

var_pack.set_g_up_sal(1);

ELSE

var_pack.set_g_upd(1);

ENDIF;

ENDaudit_emp_trig;

 

/*

SCOTT：

1、创建表emp1，结构数据同EMP表

2、做一个触发器，当修改30号部门员工工资时，需要满足范围

2000----4500

3、创建表emp_avgsal , 数据是各个部门的平均工资

4、当某个员工的工资变化，自动更新emp_avgsal相应部门的平均工资

5、打印工资最高的前3个员工，如果工资相同，需要打印出来

NO1 KING 5000

NO2 BLAKE 2850

NO3 SMITH 2600

NO3 ADAMS 2600

NO3 FORD 2600

NO3 SCOTT 2600

NO3 JONES 2600

6、创建一个表T2，其中的数据是SCOTT用户下，所有表的名字

7、写一个存储过程，通过动态SQL语句，查询T2中所有表的记录行数，并且

打印结果

EMP 14

DEPT 6

SALES 2000

*/