Oracle 数据库 PL/SQL [函数 function , 存储过程 PROCEDURE , 触发器 Trigger , 任务 JOB] 入门教程...

最新推荐文章于 2024-01-10 02:42:41 发布

ctvm52032

最新推荐文章于 2024-01-10 02:42:41 发布

阅读量330

点赞数

文章标签：数据库操作系统

PL/SQL(Procedural Language/SQL , 过程语言/SQL) 是结合了 Oracle 过程语言和结构化查询语言（SQL）的一种扩展语言。

PL/SQL 可用来创建存储过程，触发器，程序包等，也用来处理业务规则，数据库事件或者给SQL命令的执行添加程序逻辑。

PL/SQL 与 Oracle服务器和 Oracle工具紧密集成，因而具有可移植性和安全性。

PL/SQL的优点：
》支持 SQL
》支持面向对象的编程（OOP）
》更好的性能
》可移植性
》与SQL集成
》安全性

PL/SQL 体系结构：

PL/SQL 是一种块结构的语言，它将一组语句放在一个块中。构成PL/SQL程序的基本单元是逻辑块（如过程，函数和匿名块），该逻辑块可以包含任何数量的嵌套子块，每个逻辑块对应要解决的问题或子问题。

匿名块是一个未在数据库中命名的PL/SQL块，在运行时被传递到PL/SQL引擎以便执行。

在PL/SQL块中可以使用SELECT ，INSERT ，UPDATE ，DELETE ，等DML语句，事物控制语句以及SQL函数等。
在PL/SQL块中不允许直接使用CREATE ， DROP ， ALTER 等 DDL语句，但是通过动态SQL来执行他们。

PL/SQL块可以分为：
声明部分：

可执行部分：

异常处理部分：

结束符号。

[DECLARE
declarations]

BEGIN
executable statements

[EXCEPTION
exception handlers]

END;

PL/SQL 的一些特征：
(1)PL/SQL对大小写不敏感,但是用户和用户的开发团队应该选者一个合适的编码标准，以确保最好地使用共享池。
(2)在PL/SQL块的可执行部分引用变量和常量前，必须先对其进行声明。变量和常量在PL/SQL块的声明部分声明，在PL/SQL块的可执行部分使用它们。

PL/SQL中的一些符号：
:= 赋值操作符号
|| 连接操作符号
-- 单行注释符号
/* ... */ 多行注释符号
<> 标签分隔符号
.. 范围操作符号
** 求幂操作符号

声明变量：
声明变量时必须指定变量的数据类型，乐意在声明变量时初始化。
语法：variable_name data_type[(size)] [:=init_value];

变量的赋值：
可以在声明的时候赋值；
可以在自行部分赋值；
可以通过SELECT...INTO..语句从数据库中提取记录并且将侄赋给变量

常量的声明：
常量在声明的时候被赋予初始值：
语法：variable_name constant data_type := value;

OR : variable_name constant data_type default value;

一条语句只能声明一个变量。
例如：以下声明是非法的：I，J number(4);
应该写成：I NUMBER(4);J NUMBER(4);

数据库PL/SQL 内置的数据类型：
标准数据类型：
数字字符布尔类型日期类型
LOB类型：
组合数据类型：
应用数据类型：

用户还可以定义自己的子类型和使用属性类型，
属性类型（%TYPE %ROWTYPE）

数字数据类型：
分为三种：
BINARY_INTEGER , NUMBER 和 PLS_INTEGER;

BINARY_INTEGER 用于存储带符号的整数。范围是 -(2^31)-1 到 (2^31)-1
NATURAL 可以限制变量存储非负整数值，既自然数
NATURALN 可以限制变量存储自然数，并且非空
POSITIVE 可以限制变量存储正整数
POSITIVEN 可以限制变量存储正整数，并且非空
SIGNTYPE 可以限制变量只存储值 -1，0，1三个值

NUMBER 用于存储整数，定点数和浮点数。范围是 1E-130 到 10E125
NUMBER [(precision,scale)] precision:精度 scale:小数位数(后面的四舍五入)

NUMBER(precision) == NUMBER(precision,0)是声明不带小数的整数

子类型包括：
DECIMAL 用于声明最高精度为38位的十进制数字的定点数。
FLOAT 用于声明最高精度为126位的二进制数字的浮点数。
INTEGER 用于声明最高精度为38位的十进制数字的整数
REAL 用于声明最高精度为63位二进制数字(大约相当于18位十进制数字)的浮点数

PLS_INTEGER
用于存储带符号的整数。PLS_INTEGER 的大小范围介于-2^31 到 2^31 之间。与NUMBER 和 BINARY_INTEGER 相比，它的运算速度更快。

字符数据类型：
CHAR
语法：CHAR [(maximum_size[char|byte])]

举例 : CHAR(12 CHAR) 表示能存12个字符长度的内容
CHAR(16) 表示能存16个字节长度的内容(中文字符占两个字节)
注意：最大不能超过32767个字节。

RAW 此类型用于存储二进制数据或字符串。
语法：RAW(maximum_size)

LONG

LONG ROW

VARCHAR2
此类型变量可以容纳可变长度字符串。其属性类似CARCHAR2数据库类型。
语法：VARCHAR2(maximum_size[char|BYTE])

日期时间数据类型
DATE

TIMESTAMP[precision]
precision 是精度。
precision 是可选参数。在指定精度参数时，它代表秒字段小数部分中的位数

布尔数据类型
BOOLEAN
本数据类型可以是用于存储逻辑值 true,false,null.他们不带任何参数。不能将boolean数据
插入到数据库列中，不能将列值提取或者选择到BOOLEAN 变量中。
只允许boolean变量执行逻辑操作。

定义记录类型变量
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;
使用定义的记录变量类型来声明变量：
record_name record_type;
---------
实例：
DECLARE
TYPE emp IS RECORD(
id s_emp.id%type,
name s_emp.name%type,
salary s_emp.salary%type);

v_emp emp;
BEGIN
select id,name,salary into v_emp from s_emp where id=10;
dbms_output.put_line('id='||v_emp.id||',name='||v_emp.name||',salary='||v_emp.salary);

END;
/
---------

属性类型：
%TYPE
引用某个变量或数据库列的数据库类型来声明变量。

例如：
icode itemfile.itemcode%TYPE;

%ROWTYPE
提供表示表中一行的记录类型。记录类型可以存储从表中选择或由游标提取的整行数据。
emp_rec emp%ROWTYPE;

逻辑比较
= 等于
<> != 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于

控制结构：

条件控制：条件控制包括IF语句和 CASE语句

IF语句
IF语句有3种形式：IF-THEN IF-THEN-ELSE IF-THEN-ELSIF

IF-THEN
IF condition THEN
sequence_of_statements;
END IF;

IF-THEN-ELSE
IF condition THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;

IF-THEN-ELSIF
IF condition1 THEN
sequence_of_statements1
ELSEIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;

CASE语句
结构紧凑的CASE语句用语根据条件将单个变量或者表达式与多个值进行比较。
它不接受WHEN子句中的比较运算符。
CASE语句使用选择器与WHEN子句中的表达式匹配而不是与多个布尔表达式匹配。

CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression2 THEN sequence_of_statements2;
WHEN expression3 THEN sequence_of_statements3;
WHEN expression4 THEN sequence_of_statements4;
...
[ELSE sequence_of_statementsN+1;]
END CASE;

举例：
SET SERVEROUTPUT ON --这样写是打开输出开关。如果没有打开就
BEGIN
CASE '&wyd'
when 'a' then dbms_output.put_line('aa');
when 'b' then dbms_output.put_line('bb');
when 'c' then dbms_output.put_line('cc');
when 'd' then dbms_output.put_line('dd');
else dbms_output.put_line('没有选项');
END CASE;
END;
/

我们输入a,打印出 aa.
我们输入A，打印出 ‘没有选项’。

循环控制：
循环控制包括： LOOP 和 EXIT语句。
使用EXIT语句可以立即退出循环。
使用EXIT WHEN 语句可以根据条件结束循环。

循环共有3种类型，具体包括：
LOOP循环
语法： LOOP
sequence_of_statements;
END LOOP;

该循环将无限循环下去，为了能够结束，中间要加入判断，当判断达成，运行EXIT；

例子：
BEGIN
LOOP
IF &marks >60 THEN
DBMS_OUTPUT.PUT_LINE('已经通过');
EXIT;
ELSE DBMS_OUTPUT.PUT_LINE('没有通过');
END IF;
END LOOP;
END;

小心哦，输入小于60的数就会出现死循环哦！！

WHILE 循环
语法：
WHILE condition LOOP
sequence_of_statement;
END LOOP;

FOR 循环
语法：
FOR counter IN [REVERSE] value1 .. value2
LOOP
sequence_of_statements;
END LOOP;
reverse 在 for 循环中属于可选项，只有在需要对值从大到小执行循环时，才会使用reverse关键字。

例子：
SET SERVEROUTPUT ON
BEGIN
FOR QQ IN reverse 1..25
LOOP
DBMS_OUTPUT.PUT_LINE('你知道吗？'||QQ*2);
END LOOP;
END;
/

顺序控制：

GOTO语句
无条件的将控制权交到标签指定的语句。标签是用双尖括号括起来的标识符，在PL/SQL块内
必须具有唯一的名称，标签后必须紧跟着可执行语句或者PL/SQL块。GOTO语句不能跳转到IF
语句，CASE语句，LOOP语句或子块中。

NULL语句
什么也不做，只是将控制权转到下一条语句。NULL语句是可执行语句。NULL语句用在IF或其他语句
语法要求至少需要一条可执行语句。但又不需要执行操作的情况。

举例：（GOTO AND NULL）
DECLARE
wyd varchar2(30);
BEGIN
wyd:='&wyd';
IF wyd = 'gg' THEN
GOTO updation;
ELSE
GOTO quit;
END IF;
<>
DBMS_OUTPUT.PUT_LINE('我是001');
DBMS_OUTPUT.PUT_LINE('我是002');
<>
DBMS_OUTPUT.PUT_LINE('我是003');
DBMS_OUTPUT.PUT_LINE('我是004');
END;

结果提示：
如果我们输入 gg ,输出我是001，我是002，我是003，我是004
如果我们输入其他,输出我是003，我是004

动态SQL
在一般的PL/SQL程序开发中，可以使用SQL的DML语句和事物控制语句，但是DDL语句以及会话控制与却不能在
PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句以及会话控制语句，可以通过动态SQL来实现。

所谓动态SQL是指在PL/SQL块编译时SQL语句是不确定的，例如根据拥护输入参数的不同而执行不同的操作。
编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句，对语句进行语法分析并且执行该语句。

ORALCE 中的动态SQL可以通过本地动态SQL命令来执行，也可以通过DBMS_SQL程序包来执行。

执行动态SQL的语法是：
EXECUTE IMMEDIATE dynamic_sql_string
[INTO define_variable_list]
[USING bind_argument_list];

举例：
SET SERVEROUTPUT ON
DECLARE
sql_stmt varchar2(200);
student_id student.id%TYPE:=1;
student_name student.name%TYPE;
student_class student.class%TYPE;
BEGIN
sql_stmt :='create table teacher (id number(10),name varchar2(30))';
DBMS_OUTPUT.PUT_LINE('sql_stmt='||sql_stmt);
DBMS_OUTPUT.PUT_LINE('要执行sql_stmt了！');
EXECUTE IMMEDIATE sql_stmt;

sql_stmt :='select name,class from student where id=:id';
DBMS_OUTPUT.PUT_LINE('sql_stmt='||sql_stmt);
DBMS_OUTPUT.PUT_LINE('要执行sql_stmt了！');

EXECUTE IMMEDIATE sql_stmt INTO student_name,student_class using student_id;
DBMS_OUTPUT.PUT_LINE('student_name='||student_name);
DBMS_OUTPUT.PUT_LINE('student_class'||student_class);
END;

EXECUTE IMMEDIATE 语句只能用于处理返回单行或没有返回的SQL语句，要处理返回多行的
动态SQL请使用REF游标的OPEN....FOR语句。

错误处理：
在运行程序时出现的错误叫做异常。发生异常后，语句将停止执行，PL/SQL引擎立即将控制权
转到PL/SQL块的异常部分处理。异常部分处理机制简化了代码中的错误检测。预定义的异常是
在运行时由系统自动引发的，而用户定义的异常必须使用RAISE语句显式引发。

PL/SQL预定义异常：
异常 | 说明
----------------------------------------------------------------------------------------------
ACCESS_INTO_NULL | 在未初始化对象时出现
----------------------------------------------------------------------------------------------
CASE_NOT_FOUND | 在CASE语句中的选项与用户输入的数据不匹配的时候出现
----------------------------------------------------------------------------------------------
COLLECTION_IS_NULL | 在给尚未初始化的表或数组赋值的时候出现
----------------------------------------------------------------------------------------------
CURSOR_ALREADY_OPEN | 在用户试图从新打开已经打开的游标时出现，在从新打开游标前必须先将其关闭
----------------------------------------------------------------------------------------------
DUP_VAL_ON_INDEX | 在用户试图将重复的值存储在时候唯一索引的数据库列中时出现
----------------------------------------------------------------------------------------------
INVALID_CURSOR | 在执行非法游标运算(如打开一个尚未打开的游标)时出现
----------------------------------------------------------------------------------------------
INVALID_NUMBER | 在将字符串转换为数字时出现
----------------------------------------------------------------------------------------------
LOGIN_DENIED | 在输入的用户名或密码无效时出现
----------------------------------------------------------------------------------------------
NO_DATA_FOUND | 在表中不存在请求的行时出现。此外，当程序引用已经删除的元素时
| 也会引发NO_DATA_FOUND 异常
----------------------------------------------------------------------------------------------
TOO_MANY_ROWS | 在执行SELECT INTO 语句返回多行时出现
----------------------------------------------------------------------------------------------
VALUE_ERROR | 在产生大小限制错误时出现。例如，变量中的列值超出变量的大小
----------------------------------------------------------------------------------------------
ZERO_DIVIDE | 以零作除数时出现异常
----------------------------------------------------------------------------------------------

异常处理的语法：
BEGIN
sequence_of_statements;
EXCEPTION
WHEN THEN
sequence_of_statements1;
WHEN THEN
sequence_of_statements2;
....
WHEN OTHERS THEN
sequence_of_statements;
END;

自己定义异常，抛出异常和处理异常

DECLARE
e_1and1 EXCEPTION;
BEGIN
IF 1=1 THEN
RAISE e_nodata;
END IF;

EXCEPTION
WHEN e_1and1 THEN
DBMS_OUTPUT.PUT_LINE('无法识别该类');
END;

********************************************************************
******* 游标管理
********************************************************************
游标是构建在PL/SQL中，用来查询数据，获得记录集合的指针。他可以让开发者一次访问结果集中一行。

游标的分类：
游标分为：

静态游标
|
----隐式游标
|
----显式游标 (特殊的有循环游标)

REF游标(引用游标)

//**********************************

隐式游标

所有的SQL数据操纵语句（QML）都有隐式声明游标，称为隐式游标。
隐式游标是用户不能直接命名和控制的游标。

隐式游标的四个属性：
%FOUND
只有用在DML语句影响一行的多行的时候，%FOUND属性返回TRUE，否则返回FALSE

%NOTFOUND
与%FOUND属性的作用相反，如果DML没有影响任何行，返回TRUE，否则返回FALSE

%ROWCOUNT
返回DML影响数据库表的行数，值是 0 到多

%ISOPEN
返回游标是否被打开的消息。在执行SQL语句之后，Oracle自动关闭SQL游标，所以
隐式游标的%ISOPEN属性始终是关闭的。

SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISOPEN

----------------
操作

set serveroutput on

begin
insert into s_emp(id,name,salary) values('1','tom','4000');
--要执行的sql语句
IF sql%found THEN
DBMS_OUTPUT.PUT_LINE('用sql%found得知表更新了！');
ELSE
DBMS_OUTPUT.PUT_LINE('用sql%found得知表没更新！');
END IF;
--用sql%found来判断是否更新数据库了
IF sql%notfound THEN
DBMS_OUTPUT.PUT_LINE('用sql%notfound 得知表没有更新！！');
ELSE
DBMS_OUTPUT.PUT_LINE('用sql%notfound 得知表更新了！！');
END IF;
DBMS_OUTPUT.PUT_LINE('表更新了----&gt'||sql%rowcount||'行');
--用sql%rowcount来查看更新了多少行
if sql%isopen then
dbms_output.put_line('cursor is open');
else
dbms_output.put_line('cursor is close');
end if;
--用sql%isopen来查看游标是否关闭了

DBMS_OUTPUT.PUT_LINE('end!!');
end;
/
----------------
----------------------------------------------------------------------------------------------

显式游标：

显式游标是用户声明的游标，查询返回的行集合可以包含 0行到多行。
显式游标的标准操作过程：

(1)声明游标。
(2)打开游标。
(3)从游标中获取记录。
(4)关闭游标。

------------------------
(1)声明游标：
CURSOR cursor_name [(parameter[,parameter]....)]
[RETURN return_type]
is select_statement;

cursor_name : 游标名称
parameter : 游标指定输入参数
return_type : 定义游标提取的行的类型
select_statement : 指游标定义的查询语句

(2)
OPEN cursor_name [(parameters)];

(3)
FETCH cursor_name INTO variables;
cursor_name : 指游标的名字
variables : 变量名

(4)
// 在处理完游标中的所有行之后，必须关闭游标，以释放分配给游标中的所有资源。
CLOSE cursor_name;

-----------

%FOUND
如果执行最后一条FETCH语句成功提取行，返回TRUE，否则返回FALSE
%NOTFOUND
如果执行最后一条FETCH语句成功提取行，返回FALSE，否则返回TRUE
%ISOPEN
如果游标关闭，返回FALSE，游标开启，返回TRUE
%ROWCOUNT
返回到目前位置游标提取的行数。当成功FETCH一行后，数量+1；

-------
实例：
set serveroutput on

declare
emp_id s_emp.id%type;
emp_name s_emp.name%type;
emp_salary s_emp.salary%type;
cursor emp_cur is
select id,name,salary from s_emp;
begin
open emp_cur;
loop
fetch emp_cur into emp_id,emp_name,emp_salary;
exit when emp_cur%notfound;
dbms_output.put_line('emp id --&gt'||emp_id||',emp name--&gt'||emp_name||',emp salary--&gt'||emp_salary);

end loop;
close emp_cur;

end;
/
-------

显式游标的特殊情况：循环游标

可以使用循环游标简化显式游标的处理代码。循环游标隐式打开游标，自动从活动集获取行，然后在处理完所有行时关闭游标。
循环游标自动创建%ROWTYPE类型的变量并且将此变量用作记录索引。

语法：
FOR record_index IN cursor_name
LOOP
executable_statements;
END LOOP;

-----------
具体实例：
SET SERVEROUTPUT ON

DECLARE
CURSOR emp_cur is
select id,name,salary from s_emp;
BEGIN
--其中emp_rec 是一个存放一行的变量。我们在使用前不需要先声明。直接用
for emp_rec in emp_cur
LOOP
DBMS_OUTPUT.PUT_LINE('EMP ID--&gt'||emp_rec.id||' EMP NAME--&gt'||emp_rec.name||' EMP SALARY--&gt'||emp_rec.salary);
END LOOP;
END;

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

REF 游标 (引用游标)

REF游标可以在运行的时候决定执行何种查询。

使用过程：
1，创建游标

TYPE ref_cursor_name IS REF CURSOR [RETURN record_type];
注释：return 语句是可选择子句，用于指定游标提取结果集的返回类型。
如果没有return那么游标类型是一个弱类型。

2，在PLSQL的执行部分打开游标变量。用于打开REF游标的语法：
OPEN cursor_name FOR select_statement;

or OPEN cursor_name FOR dynamic_select_string [USING bing_argument_list];

----------
实例01
DECLARE
sqls VARCHAR2(200);
p_salary number:=8500;
id number;
name varchar2(50);
salary number;

TYPE cursor_salary IS REF CURSOR;
ref_cursor cursor_salary;
BEGIN
sqls :='select id from s_emp where salary>:1 ';
open ref_cursor FOR sqls USING p_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于8500的人的ID有：');
LOOP
fetch ref_cursor into id;
EXIT WHEN ref_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('ID='||id);
END LOOP;
--close ref_cursor;
END;
/

实例02
DECLARE
type emp is record(
id s_emp.id%type,
name s_emp.name%type,
salary s_emp.salary%type
);
p_salary number:=8500;
id number;
name varchar2(50);
salary number;

TYPE cursor_salary IS REF CURSOR RETURN emp;
ref_cursor cursor_salary;
BEGIN

open ref_cursor FOR select id,name,salary from s_emp where salary>8500;
DBMS_OUTPUT.PUT_LINE('薪水大于8500的人的ID有：');
LOOP
fetch ref_cursor into id,name,salary;
EXIT WHEN ref_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('ID='||id||',name='||name||',salary='||salary);
END LOOP;
close ref_cursor;
END;
/

得出几点：
(1)在声明ref游标的时候要用记录类型来声明 return
(2)游标查询结果一定要和声明的记录类型对应。
(3)不要忘了关闭游标变量
----------

**********************************************************************************
******************** 子程序（存储过程和函数） **************************
**********************************************************************************

子程序是已经命名的PLSQL块，他们存储在数据库中，可以为他们指定参数，可以在客户端和
应用程序中调用它们。
子程序包括存储过程和函数，程序包是存储过程和函数的集合。

存储过程--语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_list)]
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [procedure_name];

语法说明：
procedure_name 是过程名字
parameter_list 是参数列表
local_declarations 是局部声明
executable_statements 是可执行语句，
execption_handlers是异常处理程序

-------
create or replace procedure wyd_print(times number)
is
i number :=0;
begin
loop
i :=i+1;

DBMS_OUTPUT.PUT_LINE('第'||i||'次循环！！');

exit when i>= times;

end loop;
end;
/

直接调用存储过程：
exec wyd_print(20)
execute wyd_print(30);

过程参数模式

调用程序是通过参数向被调用的过程传递值的。
参数传递的模式有3种：IN ，OUT ，IN OUT 。也就是输入，输出，输入输出

定义过程参数的语法：
parameter_name [IN |OUT |IN OUT] datatype [{:=| DEFAULT} expression]

参数IN模式是默认模式
如果要指定OUT | IN OUT 模式参数要明确指定

---------
实例：
CREATE OR REPLACE PROCEDURE mypar(a IN number,b OUT number,c IN OUT number)
IS

BEGIN
DBMS_OUTPUT.PUT_LINE('a --&gt'||a);
DBMS_OUTPUT.PUT_LINE('b --&gt'||b);
DBMS_OUTPUT.PUT_LINE('c --&gt'||c);

--a:=111;
b:=222;
c:=333;

DBMS_OUTPUT.PUT_LINE('a --&gt'||a);
DBMS_OUTPUT.PUT_LINE('b --&gt'||b);
DBMS_OUTPUT.PUT_LINE('c --&gt'||c);

END mypar;
/
-----

declare

aa number:=11;
bb number:=22;
cc number:=33;
begin
DBMS_OUTPUT.PUT_LINE('before aa --&gt'||aa);
DBMS_OUTPUT.PUT_LINE('before bb --&gt'||bb);
DBMS_OUTPUT.PUT_LINE('before cc --&gt'||cc);
mypar(aa,88,cc);
DBMS_OUTPUT.PUT_LINE('after aa --&gt'||aa);
DBMS_OUTPUT.PUT_LINE('after bb --&gt'||bb);
DBMS_OUTPUT.PUT_LINE('after cc --&gt'||cc);

end;
/

注意几点：
(1)IN类型参数在过程中不能再给赋值
(2)OUT INOUT 类型可以进入后赋值。
(3)OUT 类型参数在调用过程传入参数时无效，
(4)OUT ,IN OUT 类型参数在调用过程时传入的必须是变量

---------
一个过程创建了，将执行权限授予其他用户

语法：
GRANT EXECUTE ON procedure_name TO USER_Name; //授权给特定用户执行过程的权限
GRANT EXECUTE ON procedure_name TO public ; //授权给所有数据库用户执行过程的权限

查看数据库里面的存储过程：
select object_name from user_objects where object_type='PROCEDURE';

select substr(object_name,1,20) object_name,object_type from user_objects where object_type='PROCEDURE';

select substr(object_name,1,10) object_name,object_type from user_objects;
删除一个过程：

DROP procedure procedure_name;

____________________________________________________________________

_______________ 函数的定义和使用 ____________________

____________________________________________________________________

函数与过程相似，也是数据库中存储的已经命名的PL/SQL块。
函数的主要特性：
(1)必须有一个返回值
(2)函数不能单独执行，只能通过SQL语句或者PL/SQL程序块来调用。

函数定义 -- 语法：
CREATE [OR REPLACE] FUNCTION function_name
[(parameter1,parameter2...)]
RETURN datatype
{IS|AS}
[local_declarations]
BEGIN
Exexutable_Statements;
[EXCEPTION
Exception_handlers;]
END;
/
注意：
(1)函数只能带有 IN 参数，而不能带有 IN OUT 或 OUT 参数。
(2)形式参数必须只能使用数据库类型，不能使用PL/SQL类型。
(3)函数返回类型也必须是数据库类型。
--------------
创建一个函数：
create or replace function fun_sum(a number,b number)
return number
IS
BEGIN
return a+b;
END;

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

通过SQL来执行函数：

SELECT function_name[(parameter1,parameter2...)] FROM DUAL;

select fun_sum(5,6) from dual;

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

查看数据库里面的用户创建的函数：
select object_name from user_objects where object_type='FUNCTION';

select substr(object_name,1,20) object_name,object_type from user_objects where object_type='FUNCTION';

--------------
函数的授权：

GRANT EXECUTE ON function_name TO USER_Name; //函数的使用权授予特定的用户

GRANT EXECUTE ON function_name TO public; //函数的使用权授予数据库中所有用户

example:
GRANT EXECUTE ON fun_sum to public;

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

*** 自主事务处理

自主事务处理是有另一个事务处理(主事务处理) 启动的独立事务处理。

举例：
//P1就是自主事务处理
CREATE OR REPLACE PROCEDURE p1
AS
PRAGMA AUTONOMOUS_TRANSACTION; --就这句话，让事务处理独立开来了
BEGIN
END p1;
/

CREATE OR REPLACE PROCEDURE p2
AS
BEGIN
...
p1;
...
END p2;
/
p2 过程调用了P1 过程，但是 p1过程声明了事务独立，
使得p1的运行对p2没有直接影响。

删除一个函数：
语法：
DROP FUNCTION Function_name;

Example:
drop FUNCTION fun_sum;

----------------------------------------------------------------
****************** 程序包的创建和使用 ****************
----------------------------------------------------------------
程序包是一种数据库对象，它是对相关PL/SQL类型，子程序，游标，异常，变量
和常量的封装。

创建一个程序包有两个步骤：
(1) 创建程序包规范。(也可以说成是 ‘声明程序包’)

基本语法：

CREATE [OR PEPLACE] PACKAGE package_name
IS|AS
[public type and item declarations]
[subprogram specifications]
END [package_name];
/
------------

(2) 创建程序包主体。(也可以说成是‘实现程序包’)

基本语法:

CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
[Public type and item declarations]
[Subprogram bodies]
[BEGIN
Initialization_statements]
END [package_name];

package_name
Public type and item declarations 声明变量，常量，游标，异常或者类型。
Subprogram bodies 定义公共和私有PL/SQL子程序

------------------
实际例子：
(1)创建程序包规范：
CREATE OR REPLACE PACKAGE wyd_package
IS
PROCEDURE wyd_print(name varchar2);
FUNCTION wyd_sum(a number,b number) return number;
END wyd_package;

(2)创建程序包主体：
CREATE OR REPLACE PACKAGE BODY wyd_package
AS
--实现wyd_print 存储过程
PROCEDURE wyd_print(name varchar2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello,'||name);
END wyd_print;

--实现wyd_sum 函数
FUNCTION wyd_sum(a number,b number) RETURN NUMBER
IS
BEGIN
return a+b;
END wyd_sum;

END wyd_package;

-----------
调用创建的包中的存储过程和函数

set serveroutput on

exec wyd_package.wyd_print('WangYuDong');

select wyd_package.wyd_sum(12,55) from dual;

-----------
查看数据库里面的用户创建的程序包：
select object_name from user_objects where object_type='PACKAGE';

select substr(object_name,1,20) object_name,object_type from user_objects;

select substr(object_name,1,20) object_name,object_type from user_objects where object_type='PACKAGE';

删除一个程序包：
语法：
DROP PACKAGE PACKAGE_name;

Example:
drop package wyd_package;

________________________________________________________________
********************** 触发器 ********************************
----------------------------------------------------------------

触发器是当特定事件出现时自动执行的代码块。
触发器与过程的区别在于：
过程是由用户或程序显式调用的，
而触发器是不能被直接调用的。Oracle会在事件请求触发器时，执行适当的触发器。

触发器可以用加强Oracle的默认功能，提供高度可定制的数据库。触发器能够执行
的功能有：
》自动生成数据
》强制复杂的完整性约束
》自定义复杂的安全权限
》提供审计和日志记录
》启用复杂的业务逻辑

触发器的一般语法：

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF}
{INSERT|DELETE|UPDATE[OF column[,column]...]}
[OR {INSERT|DELETE|UPDATE [OF column[,column]...]}]
ON [schema.]table_or_view_name
[REFERENCING [NEW AS new_row_name] [OLD AS old_row_name]]
[FOR EACH ROW]
[WHERE(condition)]
[DECLARE
variable_declation]
BEGIN
statements;
[EXCEPTION
exception_handlers]
END [trigger_name];

----------------
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual;
----------------
实例01：
CREATE OR REPLACE TRIGGER s_emp_trigger
BEFORE
INSERT OR DELETE OR UPDATE OF salary,name,id on s_emp
FOR EACH ROW
DECLARE
e_outtime EXCEPTION;
time_now varchar2(2);
time_upper varchar2(2) :='08';
time_lower varchar2(2) :='19';
BEGIN
time_now :=to_char(sysdate,'hh24');
IF time_now time_lower THEN
RAISE e_outtime;
ELSE DBMS_OUTPUT.PUT_LINE('修改数据库成功！！');
END IF;
EXCEPTION
WHEN e_outtime THEN
RAISE_APPLICATION_ERROR(-2222,'数据库在工作时间以外不允许修改！！');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现未知异常！！');
END;
/

上面的触发器是限制在一天中8 点到19点以外的时间修改表 s_emp;
----------------
测试触发器
insert into S_EMP(ID,NAME,SALARY)
VALUES('5','yangguo','5000');

----------------
实例02：
CREATE OR REPLACE TRIGGER student_trigger
BEFORE
INSERT OR UPDATE OF name on student
FOR EACH ROW
BEGIN
:new.name:='wangyudong';
END;
/

----
上面的触发器是把要插入的值进行了更换，使得原先插入的name的value被修改为'wangyudong'
insert into student(id,name,class)
values('2','zhang','class2');
/

______________________________

触发器的组成部分：
1，触发器语句
触发器语句是那些可以导致触发器的事件，
既在表或者视图上执行的INSERT，DELETE和UPDATE之类的
DML(Data manipulation language 数据库操纵语言)语言，
在模式对象上执行的DDL(Data definition language
数据库定义语句)语句或数据库事件。
[insert|delete|update] of [column,[column..]]on [tableName |viewName]
or
[insert|delete|update] of [column,[column..]]on [tableName |viewName]
[FOR EACH ROW]//写的话是行级，不写就是语句级别

2，触发器限制
触发器限制条件包含一个boolean表达式，该值必须为真才能激活触发器。
如果该值为假或未知，将不会触发。
[WHEN(condition)]

3，触发器操作
触发器操作是触发器的主体，包含一些SQL语句和代码，这些代码在执行触发器语句
且触发器限制条件的值为真时运行。
行级触发器允许触发操作中的语句访问行的列值。
[DECLARE
variable_declation]
BEGIN
statements;
[EXCEPTION
exception_handlers]
END [trigger_name];

----------------------------------------------------------------
触发器类型：
1，行级别触发器
行级别触发器对DML语句影响的每个行执行一次。如果是UPDATE语句，可能影响
多行，也就多次执行触发器。
可以在CREATE TRIGGER命令中指定FOR EACH ROW子句创建行级别触发器

new.column 表示要插入更新的列的新值
old.column 表示要插入更新的列的旧值
:new.column := new_value 表示给新的列值变量赋值
用到new.column and old.column 一般在行级别触发器的 BEFORE 触发器里面使用

2，语句级别触发器
语句级别触发器对每一个DML语句执行一次。如果一个INSERT语句在表中插入200行，那么
在表上的INSERT语句级别触发器只执行一次。
语句级别的触发器以便不用于处理于数据相关的操作，通常用于强制实施在表上的安全
措施。语句级别触发器是CREAT TRIGGER命令创建的触发器的默认类型。

3，INSTEAD OF 触发器
INSTEAD OF 触发器是在视图上而不是在表上定义的触发器，它是用来替换所有使用实际语句
的触发器。这样的触发器可以用于克服Oracle在任何视图上设置的限制，允许拥护修改不能直接
修改的视图。
举例：
SET SERVEROUTPUT ON

CREATE OR REPLACE TRIGGER student_trigger
INSTEAD OF
INSERT OR UPDATE OF name on student_view
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('这视图是不允许被操作的！！');
END;
/

4，模式触发器
相当于是帐户级别触发器
在Oracle数据库中有多个帐户。
可以在模式级别的操作上建立触发器，如 CREATE，ALTER，DROP，GRANT，
REVOKE和 TRUNCATE 等DDL语句。
用户可以创建触发器类防止删除自己创建的表。
模式触发器提供的主要功能是阻止DDL操作以及在发生DDL操作的时候提供
额外的安全监控。
语法：
______________________________________
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE|AFTER}trigger_event
ON [schema.]SCHEMA
WHEN(trigger.condition)

trigger_body;
_______________________________________
实例：
(1)我们来创建一个表来存放删除表的信息：
create table dropped_object(
obj_name varchar2(30),
obj_type varchar2(30),
drop_date DATE
);

(2)我们来创建一个模式级别的触发器。用来记录被删除的对象
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO dropped_object(obj_name,obj_type,drop_date)
values(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);
END;

5，数据库级别触发器
可以创建在数据库事件上的触发器，包括启动，关闭，服务器错误，登陆和注销等。
这些都是实例范围的，不与特定的表或视图关联。可以使用这种类型的触发器自动进行
数据库的维护和审计活动。
语法-例子：
CREATE OR REPLACE TRIGGER system_startup
AFTER STARTUP ON DATABASE
BEGIN
--Do something
END;
/

_______________________________________________________________
_______________________________________________________________
启用和禁用触发器
触发器一旦创建，默认就立即生效。我们也可以手动将触发器停止或者启动。

起用或者禁用已知道的触发器
语法：
ALTER TRIGGER trigger_name {ENABLE|DISABLE};

起用或者禁用在特定表上建立的所有触发器
语法：
ALTER TABLE table_name {ENABLE|DISABLE} ALL TRIGGERS;

查看有关触发器的信息

DESC USER_TRIGGERS;

SELECT TRIGGER_NAME FROM USER_TRIGGERS;

删除触发器
语法：
DROP TRIGGER ;

___________________________________________________________
___________________________________________________________

ORACLE 内置程序包

内置包列表

_________________________
DBMS_OUTPUT程序包
DBMS_OUTPUT.ENABLE[(buffer_size)]
ENABLE过程用来起用对PUT，PUT_LINE和NEW_LINE等过程的调用，它
只有一个输入参数，即缓冲区大小(BUFFER_SIZE)。
缓冲区默认2000Byte,最小2000Byte,最大1000,000Byte

DBMS_OUTPUT.DISABLE
DISABLE没有输入和输出。DISABLE用于禁用对put put_line,new_line的调用.
调用DISABLE还可以清除环存里边的数据。

DBMS_OUTPUT.PUT(value)
我们输入一个参数，此参数被重载接受vachar2,number,date值。put用于在环存中
写入一条消息。注意，没有结束标记

DBMS_OUTPUT.PUT_LINE(value)
我们输入一个参数，它给我们输出，并且换行。里面有结束标记

DBMS_OUTPUT.NEW_LINE
没有参数，它的调用使得缓存中的东西被写到界面并且换行。

DBMS_LOB程序包
省略

DBMS_XMLQUERY包
省略

DBMS_RANDOM包
产生随即数。
举例：
declare
value number;
begin
for i in 1..20 loop
value:=dbms_random.random;
DBMS_OUTPUT.PUT_LINE(value);
end loop;
end;
/
要说明的是产生的数很随即，有正有负。大小不定。
如果要说明产生的随机数是1到100的，那应该怎么办？？
可以先用100求余 value:=MOD(value,100);
然后求绝对值 value:=ABS(value);
修改后：
declare
value number;
begin
for i in 1..20 loop
value:=dbms_random.random;
value:=MOD(value,100);
value:=ABS(value);
DBMS_OUTPUT.PUT_LINE(value);
end loop;
end;
/

DTL_FILE包
忽略

-----------------------------------------------------------
@@@@@@@@@@@@@@@@@@ 作业(job) @@@@@@@@@@@@@@@@@@@@@@@@
-----------------------------------------------------------
创建一个作业，语法：
dbms_job.submit(:jobnumber,procedure_name,first_run_time,next_run_time);
其中 jobnumber [number类型变量] 是作业号
procedure_name [varchar2类型] 是准备执行的存储过程的名字
格式--&gt'procedure_name;' 注意';'不要忘了
this_run_time [date类型] 是准备这一次存储过程执行的时间
next_run_time [varchar2类型] 是下一次存储过程执行的时间

我们来写个例子：
(1)创建测试表
create table mybook(
name varchar2(30),
price number(6,2)
);
/

(2)创建一个存储过程，每次调用都能给mybook插入1行记录
create or replace procedure insert_mybook
as
begin
insert into mybook(name,price)values('oracle 9i','85.55');
commit;
end insert_mybook;
/

(3)创建一个JOB，有两种方法，一种是通过全局变量来创建；另外一种是通过局部变量来创建
<1>通过全局变量来创建一个作业(job)
SQL>variable jobno number;
SQL>EXEC dbms_job.submit(:jobno,'insert_mybook;',sysdate,'trunc(sysdate)+5/(24*60*60)');
<2>通过局部变量来创建一个作业(job)
variable jobid number;
begin
dbms_job.submit(:jobid,'insert_mybook;',sysdate,'sysdate+2/24/60/60');
end;
/

(4)我们可以查看一下我们创建好的作业(job).
select job,what from user_jobs;

(4)创建好的JOB默认是钝化状态的，我们要手动让它运行起来。
语法：dbms_job.run(job_number);

SQL>exec dbms_job.run(1);

dbms_job.remove(1);

___________________________________________________________
___________________________________________________________
关于解决用Oracle 的rownum来分页的过程：
1，创建一张表
create table book(
id number primary key,
bookname varchar2(30),
bookprice number(6,2)
);

2，创建一个匿名存储过程来给表插入30条数据
BEGIN
FOR pk in 1 .. 30 LOOP
insert into book(id,bookname,bookprice)
values(pk,'core java','85.00');
END LOOP;
END;

3，我们来通过Oracle的 ROWNUM 来进行分页查询
SELECT id,bookname,bookprice FROM(select ROWNUM r,book.* from book)
where r between 1 and 10;

SELECT id,bookname,bookprice FROM(select ROWNUM r,book.* from book)
where r between 11 and 20;

SELECT id,bookname,bookprice FROM(select ROWNUM r,book.* from book)
where r between 21 and 30;

______________________________________________

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

实例：
测试目标：
创建一个触发器，来判断插入的内容，如果内容不合格，不允许执行该插入操作[利用异常的抛出]
//该实例的一个经典运用就是“在非法时间段禁止用户把数据写入数据库”。

1 创建表
create table student_goal(
id number(7) primary key,
name varchar2(30) not null,
goal number(3) not null
);

2 创建一个触发器

create or replace trigger student_goal_trigger
before insert on student_goal
for each row
declare

begin

IF :new.goal>100 THEN
DBMS_OUTPUT.PUT_LINE('The goal is too big!!');
RAISE_APPLICATION_ERROR(-20001,'The goal is too big!!');
ELSE
DBMS_OUTPUT.PUT_LINE('OK,begin insert.');
END IF;

END student_goal_trigger;

3 启动触发器

ALTER TRIGGER student_goal_trigger ENABLE

4 打开oracle输出
set serveroutput on

5 查看触发器状态
select trigger_name,status from USER_TRIGGERS;

6 写输入SQL

insert into student_goal(id,name,goal)
values(1,'MM01',60);

insert into student_goal(id,name,goal)
values(2,'MM02',101);

7,执行结果
SQL> insert into student_goal(id,name,goal)
2 values(1,'MM01',60);
OK,begin insert.

已创建 1 行。

SQL> insert into student_goal(id,name,goal)
2 values(2,'MM02',101);
insert into student_goal(id,name,goal)
*
ERROR 位于第 1 行:
ORA-20001: The goal is too big!!
ORA-06512: 在"WYD.STUDENT_GOAL_TRIGGER", line 7
ORA-04088: 触发器 'WYD.STUDENT_GOAL_TRIGGER' 执行过程中出错

8 查询结果：
SQL> select * from student_goal;

ID NAME GOAL
---------- ------------------------------ ----------
1 MM01 60

//************测试结束，通过

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

SaiYa
2007-3-8 祝贺天下妇女节日快乐

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11082357/viewspace-989016/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11082357/viewspace-989016/