一、存储过程
创建存储过程的语法：
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
CREATE OR REPLACE PROCEDURE
find_emp(emp_no NUMBER)
AS
empname VARCHAR2(20);
BEGIN
SELECT ename INTO empname
FROM EMP WHERE empno = emp_no;
DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp;
存储过程参数的三种模式：
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值
执行存储过程的语法：
EXECUTE procedure_name(parameters_list);
CREATE OR REPLACE PROCEDURE
itemdesc(item_code IN VARCHAR2)
IS
v_itemdesc VARCHAR2(5);
BEGIN
SELECT itemdesc INTO v_itemdesc
FROM itemfile
WHERE itemcode = item_code;
DBMS_OUTPUT.PUT_LINE(item_code||
'项目的说明为'||v_itemdesc);
END;
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE itemdesc('i201');
CREATE OR REPLACE PROCEDURE
test( value1 IN VARCHAR2,
value2 OUT NUMBER )
IS
identity NUMBER;
BEGIN
SELECT ITEMRATE INTO identity
FROM itemFile
WHERE itemcode = value1;
IF identity < 200 THEN
value2:=100;
END IF;
END;
DECLARE
value1 VARCHAR2(5) := 'i202';
value2 NUMBER;
BEGIN
test (value1, value2);
DBMS_OUTPUT.PUT_LINE('value2 的值为'
|| TO_CHAR(value2));
END;
DECLARE
num1 NUMBER := 100;
num2 NUMBER := 200;
BEGIN
swap(num1, num2);
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
END;
将存储过程的执行权限授予其他用户：
GRANT EXECUTE ON find_emp TO MARTIN;
GRANT EXECUTE ON swap TO PUBLIC;
删除存储过程：
DROP PROCEDURE find_emp;
二、自定义函数
函数是可以返回值的命名的 PL/SQL 子程序。
创建自定义函数的语法：
CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
定义自定义函数的限制：
函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式：
使用 PL/SQL 块
使用 SQL 语句
创建自定义函数：
CREATE OR REPLACE FUNCTION fun_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '朋友，您好';
END;
从 SQL 语句调用自定义函数：
SELECT fun_hello FROM DUAL;
CREATE OR REPLACE FUNCTION
item_price_range (price NUMBER)
RETURN VARCHAR2 AS
min_price NUMBER;
max_price NUMBER;
BEGIN
SELECT MAX(ITEMRATE), MIN(ITEMRATE)
INTO max_price, min_price
FROM itemfile;
IF price >= min_price AND price <= max_price
THEN
RETURN '输入的单价介于最低价与最高价之间';
ELSE
RETURN '超出范围';
END IF;
END;
DECLARE
P NUMBER := 300;
MSG VARCHAR2(200);
BEGIN
MSG := item_price_range(300);
DBMS_OUTPUT.PUT_LINE(MSG);
END;
过程和函数的比较
存 储 过 程 自 定 义 函 数
作为 PL/SQL 语句执行 作为表达式的一部分调用
在规格说明中不包含 RETURN 子句 必须在规格说明中包含 RETURN 子句
不返回任何值 必须返回单个值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值 必须包含至少一条 RETURN语句
三、程序包
程序包是对相关存储过程、函数、变量、游标和异常等对象的封装
程序包由规范和主体两部分组成
内置程序包
扩展数据库的功能
为 PL/SQL 提供对 SQL 功能的访问
用户 SYS 拥有所有程序包
是公有同义词
可以由任何用户访问
一些内置程序包：
程序包名称 说明
STANDARD和DBMS_STANDARD 定义和扩展PL/SQL语言环境
DBMS_LOB 提供对 LOB数据类型进行操作的功能
DBMS_OUTPUT 处理PL/SQL块和子程序输出调试信息
DBMS_RANDOM 提供随机数生成器
DBMS_SQL 允许用户使用动态 SQL
DBMS_XMLDOM 用DOM模型读写XML类型的数据
DBMS_XMLPARSER XML解析，处理XML文档内容和结构
DBMS_XMLQUERY 提供将数据转换为 XML 类型的功能
DBMS_XSLPROCESSOR 提供XSLT功能，转换XML文档
UTL_FILE 用 PL/SQL 程序来读写操作系统文本文件
DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。
BEGIN
DBMS_OUTPUT.PUT_LINE('打印三角形');
FOR i IN 1..9 LOOP
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT('*');
END LOOP for_j;
DBMS_OUTPUT.NEW_LINE;
END LOOP for_i;
END;
DBMS_LOB 包提供用于处理大型对象的过程和函数。
DBMS_XMLQUERY 包用于将查询结果转换为 XML 格式。
DECLARE
result CLOB;
xmlstr VARCHAR2(32767);
line VARCHAR2(2000);
line_no INTEGER := 1;
BEGIN
result := DBMS_XMLQuery.getXml('SELECT empno, ename
FROM employee');
xmlstr := DBMS_LOB.SUBSTR(result,32767);
LOOP
EXIT WHEN xmlstr IS NULL;
line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);
DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);
xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);
line_no := line_no + 1;
END LOOP;
END;
DBMS_RANDOM 包可用来生成随机整数
DECLARE
l_num NUMBER;
counter NUMBER;
BEGIN
counter:=1;
WHILE counter <= 10
LOOP
l_num := DBMS_RANDOM.RANDOM;
DBMS_OUTPUT.PUT_LINE(l_num);
counter:=counter+1;
END LOOP;
END;
UTL_FILE 包用于读写操作系统文本文件
操作文件的一般过程是打开、读或写、关闭
UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象
CREATE DIRECTORY TEST_DIR AS 'C:/DEVELOP';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;
DECLARE
input_file UTL_FILE.FILE_TYPE;
input_buffer VARCHAR2(4000);
BEGIN
input_file := UTL_FILE.FOPEN(
'TEST_DIR', 'employees.xml', 'r');
LOOP
UTL_FILE.GET_LINE(input_file,input_buffer);
DBMS_OUTPUT.PUT_LINE(input_buffer);
END LOOP;
UTL_FILE.FCLOSE(input_file);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('------------------');
END;
创建程序包:
程序包规范
CREATE [OR REPLACE] PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
程序包主体
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
CREATE OR REPLACE PACKAGE BODY pack_me AS
PROCEDURE order_proc (orno VARCHAR2) IS
stat CHAR(1);
BEGIN
SELECT ostatus INTO stat FROM order_master
WHERE orderno = orno;
……
END order_proc;
FUNCTION order_fun(ornos VARCHAR2)
RETURN VARCHAR2
IS
icode VARCHAR2(5);
ocode VARCHAR2(5);
BEGIN
……
END order_fun;
END pack_me;
程序包的优点:
模块化
更轻松的应用程序设计
信息隐藏
新增功能
性能更佳
程序包中的游标:
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是：
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型
CREATE OR REPLACE PACKAGE BODY cur_pack AS
CURSOR ord_cur(vcode VARCHAR2)
RETURN order_master%ROWTYPE IS
SELECT * FROM order_master WHERE VENCODE=vcode;
PROCEDURE ord_pro(vcode VARCHAR2) IS
or_rec order_master%ROWTYPE;
BEGIN
OPEN ord_cur(vcode);
LOOP
FETCH ord_cur INTO or_rec;
EXIT WHEN ord_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LIne(’返回的值为' || or_rec.orderno);
END LOOP;
END ord_pro;
END cur_pack;
查看子程序、程序包信息:
USER_OBJECTS 视图包含用户创建的子程序和程序包的信息
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY');
USER_SOURCE 视图存储子程序和程序包的源代码
SELECT line, text FROM USER_SOURCE
WHERE NAME='TEST';
四、触发器
触发器是当特定事件出现时自动执行的存储过程
特定事件可以是执行更新的DML语句和DDL语句
触发器不能被显式调用
触发器的功能：
自动生成数据
自定义复杂的安全权限
提供审计和日志记录
启用复杂的业务逻辑
创建触发器的语法：
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;
触发器由三部分组成：
触发器语句（事件）
定义激活触发器的 DML 事件和 DDL 事件
触发器限制
执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行
CREATE OR REPLACE TRIGGER trig_sal
AFTER UPDATE OF empsal ON salary_records －－在更新 emp_sal 列之后激活触发器
FOR EACH ROW
WHEN (NEW.empsal>OLD.empsal) －－只有在WHEN子句中的条件得到满足时，才激活trig_sal 触发器
DECLARE
Sal_diff NUMBER;
BEGIN －－如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码
sal_diff:=:NEW.empsal-:OLD.empsal;
DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff);
END;
创建触发器：
CREATE OR REPLACE TRIGGER aiu_itemfile
AFTER INSERT
ON itemfile
FOR EACH ROW
BEGIN
IF (:NEW.qty_hand = 0) THEN
DBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');
ELSE
DBMS_OUTPUT.PUT_LINE(‘已插入记录');
END IF;
END;
触发器的类型有：模式(DDL)触发器、数据库级触发器、DML触发器（行级触发器、语句级触发器、INSTEAD OF触发器）
DDL 触发器：在模式中执行 DDL 语句时执行
数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行
DML 触发器：在对表或视图执行DML语句时执行
语句级触发器：无论受影响的行数是多少，都只执行一次
行级触发器：对DML语句修改的每个行执行一次
INSTEAD OF 触发器：用于用户不能直接使用 DML 语句修改的视图
行级触发器:
CREATE TABLE TEST_TRG
(ID NUMBER, NAME VARCHAR2(20));
CREATE SEQUENCE SEQ_TEST;
CREATE OR REPLACE TRIGGER BI_TEST_TRG
BEFORE INSERT OR UPDATE OF ID
ON TEST_TRG
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
ELSE
RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');
END IF;
END;
语句级触发器:
CREATE OR REPLACE TRIGGER trgdemo
AFTER INSERT OR UPDATE OR DELETE
ON order_master
BEGIN
IF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(‘已更新 ORDER_MASTER 中的数据');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE(‘已删除 ORDER_MASTER 中的数据');
ELSIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(‘已在 ORDER_MASTER 中插入数据');
END IF;
END;
INSTEAD OF 触发器:
CREATE OR REPLACE TRIGGER upd_ord_view
INSTEAD OF UPDATE ON ord_view
FOR EACH ROW
BEGIN
UPDATE order_master
SET vencode=:NEW.vencode
WHERE orderno = :NEW.orderno;
DBMS_OUTPUT.PUT_LINE(‘已激活触发器');
END;
模式触发器:
CREATE TABLE dropped_obj (
obj_name VARCHAR2(30),
obj_type VARCHAR2(20),
drop_date DATE);
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO dropped_obj
VALUES( ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_TYPE, SYSDATE);
END;
启用和禁用触发器:
ALTER TRIGGER aiu_itemfile DISABLE;
ALTER TRIGGER aiu_itemfile ENABLE;
删除触发器:
DROP TRIGGER aiu_itemfile;
查看触发器的信息:
USER_TRIGGERS 数据字典视图包含有关触发器的信息
SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS
WHERE TABLE_NAME='EMP';
SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
FROM USER_TRIGGERS
WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';
五、备份与恢复、导出和导入
1.备份与恢复简介
备份是数据库中数据的副本，它可以保护数据在出现意外损失时最大限度的恢复
Oracle数据库的备份包括以下两种类型： 物理备份、逻辑备份
物理备份是对数据库的操作系统物理文件
（如数据文件、控制文件和日志文件等）的备份
逻辑备份是对数据库逻辑组件
（如表、视图和存储过程等数据库对象）的备份
导致数据库操作中止的故障包括四种类型： 语句故障、用户进程故障、实例故障、介质故障
2.导出和导入实用程序
导出和导入实用程序用于实施数据库的逻辑备份和恢复
导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中
导入实用程序读取二进制导出文件并将对象和数据载入数据库中
导出和导入实用程序的特点有：
可以按时间保存表结构和数据
允许导出指定的表，并重新导入到新的数据库中
可以把数据库迁移到另外一台异构服务器上
在两个不同版本的Oracle数据库之间传输数据
在联机状态下进行备份和恢复
可以重新组织表的存储结构，减少链接及磁盘碎片
导出实用程序有以下常用命令参数：
参数 说明
USERID 确定执行导出实用程序的用户名和口令
BUFFER 确定导出数据时所使用的缓冲区大小，其大小用字节表示
FILE 指定导出的二进制文件名称，默认的扩展名是.dmp
FULL 指定是否以全部数据库方式导出，只有授权用户才可使用此参数
OWNER 要导出的数据库用户列表
HELP 指定是否显示帮助消息和参数说明
ROWS 确定是否要导出表中的数据
TABLES 按表方式导出时，指定需导出的表和分区的名称
PARFILE 指定传递给导出实用程序的参数文件名
TABLESPACES 按表空间方式导出时，指定要导出的表空间名
导出实用程序：
exp scott/tiger@orcl file=scott_back owner=scott 按用户方式导出数据
exp scott/tiger@orcl tables=(emp, dept) file=scott_back_tab 按表方式导出数据
exp system/tiger@orcl tablespaces=(users) file=tbs_users 按表空间方式导出数据
导入实用程序有如下常用命令参数：
参数 说明
USERID 指定执行导入的用户名和密码
BUFFER 指定用来读取数据的缓冲区大小，以字节为单位
COMMIT 指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交
FILE 指定要导入的二进制文件名
FROMUSER 指定要从导出转储文件中导入的用户模式
TOUSER 指定要将对象导入的用户名。FROMUSER与TOUSER可以不同
FULL 指定是否要导入整个导出转储文件
TABLES 指定要导入的表的列表
ROWS 指定是否要导入表中的行
PARFILE 指定传递给导入实用程序的参数文件名，此文件可以包含这里列出的所有参数
IGNORE 导入时是否忽略遇到的错误，默认为N
TABLESPACES 按表空间方式导入，列出要导入的表空间名
导入实用程序:
imp hy/hy@orcl file=item_back.dmp ignore=y full=y 将整个文件导入数据库
imp system/tiger@orcl file=scott_back fromuser=scott touser=martin tables=(emp,dept) 将scott用户的表导入到martin用户
扫一扫
612
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
