[数据库汇总]-- oracle存储过程-CSDN博客

我们一般使用exec 过程名();调用存储过程。或者使用call 过程名();调用过程。exec和execute是sqlplus的命令，只能在sqlplus或者sqlcommand命令窗口中使用。call是sql命令，任何工具都可以使用，比如高级语言编程中调用数据库的存储过程，也包括在sql windows。

描述：[带异常处理的过程]

create or replace procedure pro1 is

v_name varchar2(128);

begin

dbms_output.put_line('------------');

select ename into v_name from emp where empno='7369';

dbms_output.put_line('雇员的名字是'||v_name);

exception

when no_data_found then

dbms_output.put_line('没有查询到记录');

end;

备注：Oracle为我们预定义了一系列的异常处理函数，供我们程序员来调用。这些异常是预定义好的我们在使用的时候直接拿过来的用就可以，具体的异常如下图所示：

通过前面预热学习我们对Oracle的存储过程有所了解。现在我们下面再详细的介绍一下Oracle存储过程当中的一些细节。

Oracle存储过程变量

Oracle存储过程中的变量大致分为三种类型标量类型（scalar）、复合类型（composite）、参照类型(reference)、大对象（lob）.

标量类型

所谓的标量类型就是我们常用的类型

描述：[描述]

create or replace procedure pro1 is

v_name varchar2(128):='蒋中洲';

v_birth date:=sysdate;

v_sex boolean:=true;

v_age integer:=26;

v_balance float:=12000;

begin

dbms_output.put_line('name:'||v_name||'birth:'||v_birth||'age'||v_age||'balance'||v_balance);

end;

备注：注意dbms_output.put_line不支持输出boolean类型的数据

SQL> desc dbms_output.put_line

Parameter Type Mode Default?

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

A VARCHAR2 IN

我们在给变量赋值的时候一定得注意是:=赋值不再是=号赋值。

%type类型介绍

在PL/SQL中可以将变量和常量声明为内建或用户定义的数据类型，以引用一个列名，同时继承他的数据类型和大小。这种动态赋值方法是非常有用的，比如变量引用的列的数据类型和大小改变了，如果使用了%TYPE,那么用户就不必修改代码，否则就必须修改代码。

描述：[输入员工编号打印出该员工的名字工作和薪水]

create or replace procedure pro1(v_in_empno in number) as

v_name emp.ename%type;

v_job emp.job%type;

v_sal emp.sal%type;

begin

select ename,job,sal into v_name,v_job,v_sal from emp where empno=v_in_empno;

dbms_output.put_line(v_name||'-'||v_job||'-'||v_sal);

end;

备注：

复合变量

复合变量一般分为记录（record）、表（table）。其实我们可以简单的将记录理解为高级语言编程中的结构体。需要我们注意的是当引用记录成员的时候必须要加记录变量作为前缀（记录变量.记录成员）。定义一个记录的语法如下所示

描述：[record语法要求]

type 自定义的pl/sql记录名字 is record(

变量名变量类型,

变量名变量类型

);

备注：

record的一个案例

描述：[编写一个记录来存放emp的信息]

create or replace procedure pro1(v_in_empno in number) as

type MyInfo is record(

v_name emp.ename%type,

v_job emp.job%type,

v_sal emp.sal%type

);

mydata MyInfo;

begin

select ename,job,sal into mydata from emp where empno=v_in_empno;

dbms_output.put_line(mydata.v_name||'-'||mydata.v_job||'-'||mydata.v_sal);

end;

备注：

数组类型

自定义数组类型建议自定义package便于后期的管理。

描述：[自定义一个数组]

create or replace procedure pro1(v_in_empno in number) as

type sp is table of varchar2(32)

index by binary_integer;

stable sp;

begin

select ename into stable(0) from emp;

end pro1;

备注： type numbers is table of number index by binary_integer;其作用是,加了”index by binary_integer ”后，numbers类型的下标就是自增长，numbers类型在插入元素时，不需要初始化，不需要每次extend增加一个空间。

而如果没有这句话“index by binary_integer”，那就得要显示对初始化，且每插入一个元素到numbers类型的table中时，都需要先extend.

描述：[如果没有书写index by binary_integer]

declare

type mytable is table of number;

v_table mytable:=mytable();

begin

v_table.extend;

v_table(1):=123;

v_table.extend;

v_table(2):=123;

dbms_output.put_line('asas');

end;

备注：

描述：[书写index by binary_integer]

declare

type numbers is table of number

index by binary_integer;

n numbers;

begin

n(1) := 2;

n(2) := 3;

for i in 1 .. n.count loop

dbms_output.put_line(n(i));

end loop;

end;

备注：注意在声明变量n的时候就不能给变量初始化了，这点需要注意.

参照类型-游标

描述：[写一个游标的例子]

Declare

type mycuror is ref cursor;

v_cursor mycuror;

v_row emp%rowtype;

begin

open v_cursor for select * from emp;

loop

exit when v_cursor%notfound;

fetch v_cursor into v_row;

dbms_output.put_line(v_row.ename||'----'||v_row.sal);

end loop;

end;

备注：cursor不能作为参数传递，如果作为参数传递我们需要使用sys_refcursor

create or replace procedure por1(v_out_res sys_refcursor) is

type mycuror is ref cursor;

v_cursor mycuror;

v_row gm_pcategory%rowtype;

begin

open v_cursor for select * from Gm_Pcategory;

loop

exit when v_cursor%notfound;

fetch v_cursor into v_row;

dbms_output.put_line(v_row.id||'----'||v_row.name);

end loop;

end;

描述：[游标的第二种写法]

Declare

cursor v_cursor is select * from emp;

v_row emp%rowtype;

begin

open v_cursor;

loop

exit when v_cursor%notfound;

fetch v_cursor into v_row;

dbms_output.put_line(v_row.ename||'----'||v_row.sal);

end loop;

end;

备注：这种写法建议大家了解就行,重点掌握第一种写法。

描述：[使用for循环遍历游标]

create or replace procedure por1 is

cursor v_cursor is select * from emp;

begin

for x in v_cursor loop

dbms_output.put_line(x.ename||'----'||x.sal);

end loop;

end;

备注：

描述：[使用for循环遍历游标简单写法]

BEGIN

FOR emp_record IN (SELECT * FROM emp)

LOOP

DBMS_OUTPUT.PUT_LINE(emp_record.ename||' ---- '||emp_record.sal);

END LOOP;

END;

备注：

循环控制语句

if过程控制语句

描述：[if语句语法要求]

if 布尔表达式 then

--代码

elsif 布尔表达式 then

--代码

else

--代码

end if;

备注：

描述：[写一个过程，输入成绩显示结果]

create or replace procedure pro1(v_in_score in number) is

begin

if v_in_score/10>9 then

dbms_output.put_line('你是优秀的~');

elsif v_in_score/10>=8 then

dbms_output.put_line('你不错的');

elsif v_in_score/10>=7 then

dbms_output.put_line('hi,小子你得加油了~');

else

dbms_output.put_line('你不及格了');

end if;

end;

备注：需注意这里面的elsif 不可以写成else if 这里面的条件控制一定要和java中加以区分

while循环控制语句

描述：[while循环的语法要求]

create or replace procedure pro1(v_in_score in number) is

v_num number:=v_in_score;

begin

while v_num>0 loop

v_num:=v_num-1;

dbms_output.put_line('显示数据'||v_num);

end loop;

end;

备注：这里面注意在存储过程我们不能修改v_in_score的值所以这里面需要定义一个变量。

for循环控制语句

描述：[for循环语法]

create or replace procedure pro1(v_num in number) is

begin

for i in 1..v_num loop

if mod(i,2)=0 then

exit when i=8;

end if;

dbms_output.put_line(i);

end loop;

end;

备注：exit when 相当于break语句。同时对于循环语句也支持continue关键字。

描述：[使用for循环打印num以内所有偶数的和并输出]

create or replace procedure pro1(v_num in number) is

v_res number:=0;

begin

for i in 1..v_num loop

if mod(i,2)=0 then

v_res:=v_res+i;

end if;

end loop;

dbms_output.put_line('res is '|| v_res);

end;

备注：

描述：[使用for循环打印num以内所有偶数的和并输出，使用continue关键字]

create or replace procedure pro1(v_num in number) is

v_res number:=0;

begin

for i in 1..v_num loop

if mod(i,2)=1 then

continue;

end if;

v_res:=v_res+i;

end loop;

dbms_output.put_line('res is '|| v_res);

end;

备注：这里面的continue的用法和java中的作用一样，这里面不做过多的解释。注意continue在10g的数据库版本中不支持,但是在Oracle11g以全面支持continue,这里需要读者注意。

loop循环

描述：[存储过程中简单的死循环]

create or replace procedure pro1(v_num in number) is

v_res number:=0;

begin

loop

dbms_output.put_line('----');

end loop;

end;

备注：这样会出现死循环

描述：[推出循环一般使用exit when 表达式]

create or replace procedure pro1(v_num in number) is

v_res number:=0;

begin

loop

v_res:=v_res+1;

exit when v_res=10;

dbms_output.put_line('----');

end loop;

end;

备注：exit也可以直接使用exit when 等价以 if 条件 then exit end if;

使用Oracle10g支持goto关键字

描述：[推goto推出循环]

declare

v_res number:=0;

begin

loop

v_res:=v_res+1;

if v_res=10 then

goto lable;

end if;

dbms_output.put_line('----');

end loop;

<<lable>>

dbms_output.put_line('结束了');

end;

备注：但是注意<<lable>>不能放在end标签上

case-when控制块

描述：[case-when语句的练习]

create or replace procedure pro1(v_in_score in number) is

begin

dbms_output.put_line(v_in_score/10);

case

when v_in_score/10>=9 then

dbms_output.put_line('你是优秀的~');

when v_in_score/10>=8 then

dbms_output.put_line('你不错的');

when v_in_score/10>=7 then

dbms_output.put_line('hi,小子你得加油了~');

else

dbms_output.put_line('你不及格了');

end case;

end;

备注：

存储过程-过程/函数

编写一个过程

描述：[书写过程的语法]

create or replace procedure 过程名(参数名参数类型,参数名参数类型，… …) is

--变量的声明

begin

--执行的sql脚本

end;

备注：

描述：[编写一个过程]

create or replace procedure pro is

type ddd is record(

id t_user.id%type,

name t_user.name%type

);

aa ddd;

begin

select * into aa from t_user;

dbms_output.put_line(aa.name||aa.id);

end;

备注：为了可以输出必须设置set serveroutput on

语法

描述：[书写函数的语法]

create or replace function 函数名(参数名参数类型,参数名参数类型，… …) return 参数类型 is

--变量的声明

begin

--执行的sql脚本

return 返回值

end;

备注：

描述：[写一个函数求两个数的和]

create or replace function mysum(v_in_num1 number,v_in_num2 number) return number is

v_res number;

begin

v_res:=v_in_num1+v_in_num2;

return v_res;

end;

备注：

plsql的编写和规范

描述：[pl/sql的编写规范]

注释

单行注释 --

多行注释 /*注释的sql块*/

标示符号和命名规范

当定义一个变量的时候建议使用v_作为前缀v_sal

当定义一个常量的时候建议使用c_作为前缀c_rate

当定义游标的时候建议使用_cursor作为后缀

当定义一个例外异常的时候建议e_作为前缀e_error

备注：

通过上述知识点的学习我们了解了存储过程，现在总结一下存储过程和函数的区别和联系。

描述：[procedure和function的区别]

本质上没区别。只是函数有限制只能返回一个标量，而存储过程可以返回多个。并且函数是可以嵌入在SQL中使用的，可以在SELECT等SQL语句中调用，而存储过程不行。执行的本质都一样。

1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。

3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数

可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

4. 当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在

procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。

从参数的返回情况来看：

如果返回多个参数值最好使用存储过程，如果只有一个返回值的话可以使用函数；

从调用情况来看：

如果在SQL语句（DML或SELECT）中调用的话一定是存储函数或存储的封装函数不可以是存储过程，但调用存储函数的时候还有好多限制以及函数的纯度等级的问题，可以参考《ORACLE 9I PL\SQL程序设计》

如果是在过程化语句中调用的话，就要看你要实现什么样的功能。函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等），所以虽然他们的语法上很相似但用户在使用他们的时候所需要完成的功能大部分情况下是不同的。

备注：Procedure cache：中保存的是执行计划，当编译好之后就执行procedure cache中的execution plan，之后Oracle会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

存储过程包概念

类似一个容器，能打包相应的pl/sql变量、常量、函数、过程、复合数据类型等元素到这个容器里面。用来限制对内容的访问权限。

包的构成包通常有两部分构成分别是包头和包体。包头里面通常写上述元素的声明，相当于应用程序的接口；包体理通常写上述元素的实现，相当于应用程序接口的具体实现。注意包头和包体分别会作为数据对象存在。

描述：[建立一个包的语法]

create or replace package 包名 is

type 自定义的pl/sql记录名字 is record(

变量名变量类型,

变量名变量类型

);

function 函数名(参数1，参数2,... ...) return 返回值类型;

procedure 过程名(参数1，参数2,... ...)

end;

备注：

描述：[简历一个package案例]

create or replace package zpark is

type MyData is record(

v_name varchar2(32),

v_sal number

);

type MyTale is table of varchar2(32)

index by binary_integer;

function fun1(v_in_empno in number,v_out_name out varchar2) return varchar2;

procedure pro1(v_in_empno in number);

end;

备注：

描述：[package的实现]

create or replace package body zpark is

v_mydata zpark.MyData;

v_mytable zpark.MyTale;

v_name emp.ename%type;

function fun1(v_in_empno in number,v_out_name out varchar2) return varchar2 is

begin

select ename ,sal into v_mydata from emp where empno=v_in_empno;

v_out_name:=v_mydata.v_name;

return v_out_name;

end;

procedure pro1(v_in_empno in number)is

begin

select ename into v_name from emp where empno=v_in_empno;

dbms_output.put_line('用户名是'||v_name);

end;

备注：实现的包和定义的包的名字一定保持一致，并且实现包必须实现包中声明的方法和过程。

存储过程编写分页过程

描述：[存储过程的分页函数的封装]

CREATE OR REPLACE PROCEDURE PRO1(V_IN_TABLENAME IN VARCHAR2,V_IN_PAGENOW IN NUMBER,V_IN_PAGESIZE IN NUMBER,V_OUT_RESULT OUT PACKAGE1.MY_CURSOR,V_OUT_ROWCOUNT OUT NUMBER,V_OUT_TOTALPAGE OUT NUMBER) IS

V_SQL VARCHAR2(2000);

V_START NUMBER:=(V_IN_PAGENOW-1)*V_IN_PAGESIZE+1;

V_END NUMBER:=V_IN_PAGESIZE*V_IN_PAGENOW;

BEGIN

SELECT COUNT(*) INTO V_OUT_ROWCOUNT FROM V_IN_TABLENAME;

IF MOD(V_OUT_ROWCOUNT,V_IN_PAGESIZE)=0 THEN

V_OUT_TOTALPAGE:=V_OUT_ROWCOUNT/V_IN_PAGESIZE;

ELSE

V_OUT_TOTALPAGE:=V_OUT_ROWCOUNT/V_IN_PAGESIZE+1;

END IF;

V_SQL:='select * from(select t1.*,rownum rn from(select * from '||V_IN_TABLENAME||') t1 where rownum<='||V_END||' ) e2 where e2.rn>='||V_START;

OPEN V_OUT_RESULT FOR V_SQL;

END PRO1;

存储过程中的异常

描述：[异常的定义]

declare

myexception EXCEPTION;

pragma exception_init(myexception,-20001);

begin

dbms_output.put_line('--执行的sql脚本--');

raise myexception;

exception

when myexception then

dbms_output.put_line('自定义异常出现了~~~');

when others then

dbms_output.put_line('哎，没有捕获到该异常');

end;

备注：raise myexception;是手动跑出自己定义的异常信息，同时我们也可以这样抛出一个异常

描述：[使用RAISE_APPLICATION_ERRORP抛出异常]

declare

myexception EXCEPTION;

pragma exception_init(myexception,-20001);

begin

dbms_output.put_line('--执行的sql脚本--');

raise_application_error('-20001','ssss');

exception

when myexception then

dbms_output.put_line('自定义异常出现了~~~');

when others then

dbms_output.put_line('我草，没有捕获到该异常');

end;

备注：

描述：[使用异常管理事物控制]

declare

myexception EXCEPTION;

pragma exception_init(myexception,-20001);

begin

savepoint aa;

insert into test values(7,'蒋中洲',100);

insert into test values(8,'蒋中亚',100);

exception

when myexception then

dbms_output.put_line('自定义异常出现了~~~');

when others then

dbms_output.put_line('er，没有捕获到该异常');

rollback to aa;

end;

备注：savepoint aa;创建一个事物的回滚点如果出现未知异常就会回滚事物。

这里面建议大家建立一个自己的异常包以后有自己来使用正阳不会产生冲突

描述：[建立一个异常包]

create or replace package myexception is

e_userregister EXCEPTION;

pragma exception_init(e_userregister,-20001);

e_userlogin EXCEPTION;

pragma exception_init(e_userlogin,-20002);

end;

备注：自定义异常信息方便后续调用。

描述：[使用自己建立的异常信息]

declare

begin

savepoint aa;

insert into test values(7,'蒋中洲',100);

insert into test values(8,'张晓玉',100);

raise myexception.e_userregister;

exception

when myexception.e_userregister then

dbms_output.put_line('自定义异常出现了~~~');

when others then

dbms_output.put_line('哎，没有捕获到该异常');

rollback to aa;

end;

备注：

描述：[如果自己不想写异常类我们也可以简单的使用现成的异常信息]

declare

myexception EXCEPTION;

pragma exception_init(myexception,-20001);

begin

savepoint aa;

raise TIMEOUT_ON_RESOURCE;

exception

when myexception then

dbms_output.put_line('自定义异常出现了~~~');

when others then

dbms_output.put_line('er，没有捕获到该异常');

rollback to aa;

end;

备注：

异常总结：

异常的抛出有三种通过pl/sql运行时、使用RAISE关键字、调用RAISE_APPLICATION_ERROR存储过程来实现异常的抛出。这里面需要注意的是RAISE_APPLICATION_ERROR内建函数用于抛出一个异常并给异常赋予一个错误号以及错误信息。自定义异常的缺省错误号是+1,缺省信息是User_Defined_Exception。RAISE_APPLICATION_ERROR函数能够在pl/sql程序块的执行部分和异常部分调用，显式抛出带特殊错误号的命名异常。　 Raise_application_error(error_number,message[,true,false]))。错误号的范围是-20,000到-20,999。错误信息是文本字符串，最多为2048字节。TRUE和FALSE表示是添加(TRUE)进错误堆(ERROR STACK)还是覆盖(overwrite)错误堆(FALSE)。缺省情况下是FALSE。

Oracle中的触发器

触发器简介

触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、 Insert、 Delete 这些操作时

描述：[描述]

触发器类似于过程、函数，因为他们收拾拥有说明部分和语句执行部分以及异常处理部分，同包类似，触发器必须存储在数据库中，并且不能被块进行本地化说明。但是对于过程而言，可以从另一个块中通过一个过程显示的调用另外的一个过程，同时在调用的时候可以传递参数。碎语触发器而言，当触发事件发生的时候就会显示的执行该触发器，并且触发器不接收参数。

备注：

触发器的分类大致可以分为dml触发器、系统触发器、ddl触发器。

DML触发器（insert/delete/update）

描述：[触发器的语法]

create or replace trigger 触发器名

{before|after} [update|delete|insert] on 表名

（for each row）

begin

--执行的sql语句块

end;

备注：{}表示必须有[]表示可以有其中的一个（）表示有也可以没有也可以

描述：[在用户修改test表的时候打印一句话]

create or replace trigger mytrigger

before update on test

begin

dbms_output.put_line('用户正在修该数据');

end;

备注：该触发器就实现了在用户修改某一条记录的时候打印‘用户正在修改数据’，但是我们看到用户在修改很多条记录的时候我们的控制台只打印了如下的数据：

SQL> update test set id=id-2;

用户正在修该数据

7 rows updated

如果这里面大家想看到打印七条数据我们需要在begin前添加for each row

create or replace trigger mytrigger

before update on test

for each row

begin

dbms_output.put_line('用户正在修该数据');

end;

描述：[在星期日和星期六的时候不允许修改test表]

create or replace trigger mytrigger

before insert or update or delete on scott.test

begin

if to_char(sysdate,'day') in ('星期日','星期六') then

dbms_output.put_line('周末之间不能变更人员~');

RAISE_APPLICATION_ERROR (-20001,'对不起不能删除员工，不合法操作！');

end if;

end;

备注：

触发器中的谓语动词inserting/updating/deleting使用

为了更加准确的描述用户对表的操作我们使用oracle为我们提供的谓语动词。

描述：[谓语动词的使用]

create or replace trigger mytrigger

before insert or update or delete on scott.temp

begin

case

when inserting then

RAISE_APPLICATION_ERROR(-20001,'sorry you cant insert');

when updating then

RAISE_APPLICATION_ERROR(-20002,'sorry you cant update');

when deleting then

RAISE_APPLICATION_ERROR(-20003,'sorry you cant delete');

else

dbms_output.put_line('用户正常操作');

end case;

end;

备注：

表级触发器的NEW和OLD关键字的使用：

描述：[谓语old动词的使用]

create or replace trigger mytrigger

before delete on scott.temp

for each row

begin

dbms_output.put_line('数据已近备份~');

insert into temp_bak values(:old.id,:old.name,:old.logtime);

end;

备注：

描述：[谓语old/new动词的使用]

create or replace trigger mytrigger

before update on scott.temp

for each row

begin

dbms_output.put_line('数据已近备份~');

dbms_output.put_line('-->修改数据以前是：'||:old.id||:old.name||:old.logtime);

dbms_output.put_line('-->修改数据以前是：'||:new.id||:new.name||:new.logtime);

end;

备注：

描述：[谓语old/new 薪水支付的案例]

create or replace trigger mytrigger

before update on scott.temp

for each row

begin DBMS_OUTPUT.put_line('-OLDSLARY-'||:OLD.SALARY||'NEWSLARY'||:NEW.SALARY);

IF :NEW.SALARY-:OLD.SALARY<0 THEN

raise_application_error(-20001,'不能克扣员工工资');

ELSIF (:NEW.SALARY-:OLD.SALARY)/:OLD.SALARY>0.5 THEN

raise_application_error(-20001,'不能乱加薪');

ELSE

DBMS_OUTPUT.PUT_LINE('加薪成功');

END IF;

end;

备注：

DDL触发器

描述：[DDL触发器]

CREATE OR REPLACE TRIGGER ddltrigger

BEFORE DDL

ON SCOTT.SCHEMA

DECLARE

oper varchar2(32);

BEGIN

SELECT ora_sysevent INTO oper FROM DUAL;

dbms_output.put_line(oper);

IF oper = 'DROP' THEN

RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop In Production Has Been Logged');

ELSIF oper = 'TRUNCATE' THEN

RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table Has Been Logged');

ELSIF oper = 'ALTER' THEN

RAISE_APPLICATION_ERROR(-20997, 'Attempt To modify table has been loged');

END IF;

END ddltrigger;

备注：通常是数据库管理系统的一部分，用于定义数据库的所有特性和属性，尤其是行布局、列定义、键列（有时是选键方法）、文件位置和存储策略。不需要dba权限

包括命令：DROP,CREATE,ALTER,GRANT,REVOKE, TRUNCATE

描述：[等价用于]

CREATE OR REPLACE TRIGGER ddltrigger

BEFORE DROP OR ALTER OR TRUNCATE OR GRANT OR REVOKE OR CREATE

ON SCOTT.SCHEMA

DECLARE

oper varchar2(32);

BEGIN

SELECT ora_sysevent INTO oper FROM DUAL;

dbms_output.put_line(oper);

IF oper = 'DROP' THEN

RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop

In Production Has Been Logged');

ELSIF oper = 'TRUNCATE' THEN

RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A

Production Table Has Been Logged');

ELSIF oper = 'ALTER' THEN

RAISE_APPLICATION_ERROR(-20997, 'Attempt To modify table has been loged');

ELSE

RAISE_APPLICATION_ERROR(-20096,'OTHER OPERATION LOGED');

END IF;

END ddltrigger;

备注：这样操作后用户就不能对scott下的表做任何改动了

Oracle中的系统触发器

描述：[系统触发器概述]

系统触发器是指基于oracle事件（例如logon和startup）所建立的是触发器，通过使用系统事件触发器，提供了跟踪系统或是数据库变化的机制。下面介绍一些常用的系统触发事件函数和建立各种事件触发器的方法在建立系统触发器的时候，我们需要使用事件属性函数。

ora_client_ip_address//返回客户端的ip

ora_database_name//返回数据库名字

ora_login_user//返回登录的用户名

ora_sysevent//返回触发的系统事件名字

ora_des_encrypted_password//返回用户des（MD5）加密的密码

备注：

使用系统触发器记录用户登录记录，记住在使用系统触发器的时候一定要求用户是system用户才有整改权限。

描述：[系统触发语法]

create or replace trigger 触发器名字

{after|before} [logon |logoff|startup|shutdown] on database

begin

end;

备注：注意不是任意匹配 after [logon|startup] before[logoff|shutdown]

描述：[用户登录触发器]

create or replace trigger logontrigger

after logon on database

begin

insert into user_log(username,databasename,address,logon_time,password,event)

values(ora_database_name,ora_login_user,ora_client_ip_address,sysdate,ora_des_encrypted_password,'用户logon');

end;

备注：

create table user_log(

username varchar2(32),

databasename varchar2(32),

address varchar2(128),

event varchar2(32),

logon_time date,

password varchar2(32)

)

描述：[用户登录出去触发器]

create or replace trigger logofftrigger

before logoff on database

begin

insert into user_log(username,databasename,address,logon_time,password,event)

values(ora_database_name,ora_login_user,ora_client_ip_address,sysdate,ora_des_encrypted_password,'用户logoff');

end;

备注：常见的系统触发器有

ora_client_ip_address:用于返回客户端的IP地址

ora_database_name：用于返回当前数据库名

ora_des_encrypted_password:用于返回DES加密后的用户口令

ora_dict_obj_name:用于返回DDL操作所对应的数据库对象名

ora_dict_obj_name_list(name_list_ OUT ora_name_list_t):用于返回字事件中被修改的对象名列表

ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名。

ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表

ora_dict_obj_type:用于返回DDL操作所对应的数据库对象的类型。

ora_grantee(user_list OUT ora_name_list_t):用于返回授权时事件授权者。

ora_instance_num:用于返回历程号。

ora_is_alter_column(column_name IN VARCHAR2):用于检测特定列是否被修改

ora_is_creating_nested_table:用于检测是否正在建立嵌套表

ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除

ora_is_servererror(error_number):用于检测是否返回了特定Oracle错误。

ora_login_user：用于返回登录用户名

ora_sysevent：用于返回触发触发器的系统时间名

Oracle中的JOB

描述：[job概述]

定时在后台执行相关操作:

1.如每天晚上0点将一张表的数据保存到另一张表中,

2：定时备份数据库等

备注：这样操作后用户就不能对scott下的表做任何改动了

快速入门

描述：[创建一个job实现数据每一秒钟添加一条记录]

create or replace procedure pro1(v_id number) is

begin

insert into temptable values(sysdate);

end;

variable job2010 number ;–-声明一个变量保存job的编号

begin

dbms_job.submit(:job2010,'pro1;'，sysdate，sysdate+1/(24*3600)');

end;

--启动该任务

begin

dbms_job.run(:job2010);

end;

备注：查看当前下的job使用SELECT * FROM uses_jobs a WHERE a.WHAT = 'pro1;';

或者使用：select * from user_jobs也可以查看job任务

删除job

描述：[删除一个job任务]

--移除job

begin

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

end;

备注：

暂停job

描述：[暂停一个job任务]

--暂停一个job

begin

dbms_job.broken(25,True);

end;

Commit;

备注：

恢复job

描述：[恢复一个job任务]

--暂停一个job

begin

dbms_job.broken(25,false,sysdate);

end;

commit;

备注：提交很重要

修改job

描述：[修改一个job任务]

--暂停一个job

Begin

dbms_job.change(25,'JOBPROCEDURE1;',sysdate,'sysdate+1/(24*60)');

end;

备注：

修改job的执行间隔时间

描述：[修改一个job任务间隔时间]

--修改job的执行时间

Begin

dbms_job.interval(job => 25,interval => 'sysdate+1/(24*3600)');

end;

备注：exec dbms_job.interval(job => 25,interval => 'sysdate+1/(24*3600)');

提交指定id的job

描述：[提交一个任务指定jobid]

begin

dbms_job.isubmit(200,'JOBPROCEDURE;',sysdate,'sysdate+1/(24*60)');

end;

备注：isubmit该过程submit的区别在于submit是oracle自动分配job的id但是isubmit是程序员手动设置id如果该id存在就会抛出异常。

运行job

描述：[运行一个job]

begin

dbms_job.run(200);

end;

备注:开启一个提交的job该job的id 是200

指定特定时间按运行job

描述：[运行一个指定特定的时间]

begin

dbms_job.next_date(200, sysdate);

end;

备注:

补充视图

---视图的主要用途

1.简化操作

2.提高安全

2.满足不同用户的查询需求

视图不是一个真正的物理表，他是根据其他表动态的生成的数据。

create or replace view aaa as select [whith read only]

Oracle数据库的而备份

EXP和IMP是Oracle提供的一种逻辑备份工具。逻辑备份创建数据库对象的逻辑拷贝并存入一个二进制转储文件。这种逻辑备份需要在数据库启动的情况下使用, 其导出实质就是读取一个数据库记录集（甚至可以包括数据字典）并将这个记录集写入一个文件,这些记录的导出与其物理位置无关，导入实质就是读取转储文件并执行其中的命令。此备份方式是通过Oracle的实用工具export和import来实施的， export是把数据库中的数据导出，import是把export卸出的数据导入数据库中。通过此工具可以衍生出多种功能, 比如整个数据库的备份、表结构重建、数据的传输、用户的改变等等。

Oracle Export/Import工具提供了灵活多样的功能和导出/导入模式，最常使用的三种模式是User、table和full database。除此以外，还可以在导出/导入时决定是否包含与对象有关的数据字典信息，如索引、约束、权限等等。

注意：通过逻辑备份可以备份整个数据库, 或仅备份部分重要数据。因为是逻辑上的备份,故只能用于逻辑的恢复数据, 一旦数据库被物理损坏, 导致不能启动,逻辑备份的数据不能帮助恢复数据库

描述：[数据库的备份]

exp userid=用户名/密码@数据库实例名 tables=(表名1,表名2,… …)file=备份的路径

备注：将数据的表结构备份数据到磁盘导出后数据就会备份到D盘的bak.DMP

描述：[将scott用户的数据到D:\bak.DMP]

exp userid=用户名/密码@数据库实例名 tables=(表名1,表名2,… …)file=备份的路径 rows=n

备注：rows=n 指的是不要数据

描述：[指导处表的结构不导出数据]

exp userid=scott/root@orcl tables=(dept,emp)file=D:\bak

备注：

描述：[直接导出方式]

exp userid=用户名/密码@数据库实例名 tables=(表名1,表名2,… …)file=备份的路径 direct=y

备注：需要数据库的字符集和客户端字符集一致，否者会报错。

描述：[数据的额导入]

imp sys/password file=*.dmp fromuser=olduser touser=newuser indexes=Y rows=Y

备注：

JAVA代码调用存储过程

JAVA代码调用过程

描述：[java调用存储过程不带参数]

create or replace procedure pro1(v_id number) is

begin

insert into temptable values(sysdate);

end;

Class.forName("oracle.jdbc.driver.OracleDriver");

String url="jdbc:oracle:thin:@localhost:1521:orcl";

Connection conn=DriverManager.getConnection(url,”scott”,"root");

CallableStatement cs=conn.prepareCall("{call pro1()}");

cs.execute();

备注：

描述：[java调用存储过程带参数]

create or replace procedure pro1(v_id in number,v_name in varchar2,v_sex in varchar2) is

begin

insert into temp_1 values(v_id,v_name,v_sex);

end;

Class.forName("oracle.jdbc.driver.OracleDriver");

String url="jdbc:oracle:thin:@localhost:1521:orcl";

Connection conn=DriverManager.getConnection(,"system","root");

CallableStatement cs=conn.prepareCall("{call pro1(?,?,?)}");

cs.setInt(1, 2);

cs.setString(2,"蒋中洲");

cs.setString(3," 男");

cs.execute();

备注：

描述：[java调用存储过程带参数和带返回值]

create or replace procedure pro1(v_id in number,v_name in varchar2,v_sex in varchar2,v_out_date out date) is

begin

insert into temp_1 values(v_id,v_name,v_sex);

select sysdate into v_out_date from dual;

end;

Class.forName("oracle.jdbc.driver.OracleDriver");

String url="jdbc:oracle:thin:@localhost:1521:orcl";

Connection conn=DriverManager.getConnection(url,"system","root");

CallableStatement cs=conn.prepareCall("{call pro1(?,?,?,?)}");

cs.setInt(1,5);

cs.setString(2, "蒋中洲");

cs.setString(3, "男");

cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DATE);

cs.execute();

Date date=cs.getDate(4);

System.out.println(date.toLocaleString());

备注：

JAVA代码调用函数

描述：[java调用函数带参数和带返回值]

create or replace function fun(v_in_num1 in number,v_in_num2 in number) return number is

v_sum number;

begin

v_sum:=v_in_num1+v_in_num2;

return v_sum;

end;

Class.forName("oracle.jdbc.driver.OracleDriver");

String url="jdbc:oracle:thin:@localhost:1521:orcl";

Connection conn=DriverManager.getConnection(url,"system","root");

PreparedStatement pstm=conn.prepareStatement("select fun(?,?) from dual");

pstm.setInt(1, 1);

pstm.setInt(2, 2);

ResultSet rs=pstm.executeQuery();

if(rs.next()){

System.out.println(rs.getInt(1));

}

备注：

HIBERNATE调用过程

描述：[java调用函数带参数和带返回值]

create or replace procedure pro1(v_id in number,v_name in varchar2,v_sex in varchar2,v_out_date out date) is

begin

insert into temp_1 values(v_id,v_name,v_sex);

select sysdate into v_out_date from dual;

end;

Configuration configuration=new Configuration().configure();

SessionFactory sessionFactory=configuration.buildSessionFactory();

Session session=sessionFactory.openSession();

session.doWork(new Work() {

@Override

public void execute(Connection connection) throws SQLException {

// TODO Auto-generated method stub CallableStatement cs=connection.prepareCall("{call pro1(?,?,?,?)}");

cs.setInt(1,6);

cs.setString(2, "蒋中洲");

cs.setString(3, "男");

cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DATE);

cs.execute();

Date date=cs.getDate(4);

System.out.println(date.toLocaleString());

}

});

备注：

HIBERNATE调用函数

描述：[java调用函数带参数和带返回值]

create or replace function fun(v_in_num1 in number,v_in_num2 in number) return number is

v_sum number;

begin

v_sum:=v_in_num1+v_in_num2;

return v_sum;

end;

Configuration configuration=new Configuration().configure();

SessionFactory sessionFactory=configuration.buildSessionFactory();

Session session=sessionFactory.openSession();

session.doWork(new Work() {

@Override

public void execute(Connection connection) throws SQLException {

// TODO Auto-generated method stub//oracle.jdbc.driver.T4CConnection--oracle.jdbc.driver.T4CConnection

PreparedStatement pstm=connection.prepareStatement("select fun(?,?) from dual");

pstm.setInt(1, 1);

pstm.setInt(2, 2);

ResultSet rs=pstm.executeQuery();

if(rs.next()){

int a=rs.getInt(1);

System.out.println(a);

}

});

备注：

数据库的SQL优化策略

SQL语句的优化策略

（1）选择最有效率的表名顺序

Oracle的解析器按照从右到左的顺序处理From子句中的表名，因此写在from子句中最后的表(基础表 driving table )将被最先处理，在from表包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

（2） where子句的连接顺序

Oracle采用的是自下而上的顺序解析where子句。根据这个原理，表之间连接必须写在其他where条件之前,那些可以过滤掉的最大数量记录条件必须写在where子句的末尾。

（3） select子句中避免使用‘*’

Oracle在解析的过程中，会将‘*’依次转换所有的列名，这个工作是通过查询数据字典完成的，这意味着将消耗给等多的时间。

（4）减少访问数据库的次数

oracle数据库内部执行的许多工作解析sql语句，估算索引利用率，绑定变量、读数据块等；

（5）删除表中重复记录

delete from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no= e. emp_no);