ORACLE学习笔记

n         日期：2004-06-10

2        select t1.*, t2.name as table_name from v$lock t1, sys.obj$ t2 where t1.id1=t2.obj#

以上语句可以查出当前数据库表的锁情况，其中SID表示会话ID号，table_name表示被加锁的表的名称，lmode表示锁的类别，如下表：

编号	描述
0	无
1	空
2	行共享
3	行专用
4	共享
5	共享行专用
6	专用

Select count(*) from v$lock where lmode=0;

该语句可以快速知道是否有会话正在等待任何表的锁。

2        在PL/SQL中，声明常量的语法为：CONSTANT，例如：

PI CONSTANT REAL := 3.1415926

2        在PL/SQL中，测试某个变量是否为NULL或是否为非NULL值，用 IS NULL 和 IS NOT NULL，例如：IF  (X IS NULL) THEN …，IF (X IS NOT NULL) THEN…

2        一个真正灵活的特性称为“配置”变量。不需要确实地编写一个数据类型，可以使用其它变量、数据库列或表的数据类型。可以使用%TYPE或%ROWTYPE属性实现这个目的，%TYPE属性提供了所需数据库列或变量的类型及长度。%ROWTYPE属性允许人们定义一个记录变量，它的成员变量拥有表或游标中每一列正确的类型和长度。使用点符号引用记录变量中的每个成员变量，如：

?         quantity orders.qty%TYPE – 基于表订单中的qty列

?         orders_rec orders%ROWTYPE – 基于表订单中的一列

 

n         日期：2004-06-11

2        游标的声明语句：

DECLARE

-- Get all orders in database  

CURSOR get_orders IS

       SELECT * FROM orders;

-- Get a few columns for a specified order number

CURSOR get_order_item(Porder_no orders.order_no%TYPE) IS

       SELECT seq_num, quantity, unit_price, extended_price

       FROM orders

       WHERE order_no = Porder_no;

-- Get the whole row for a particular item#

CURSOR get_items(Pitem_no items.item_no%TYPE) RETURN items%ROWTYPE IS

       SELECT * FROM items

       WHERE item_no = Pitem_no;

-- Get the item name for the particular item#

              CURSOR get_item_name(Pitem_no items.item_no%TYPE) RETURN items.item_no%

                     TYPE IS select item_name FROM items WHERE item_no = Pitem_no;

2        当打开一个游标时，执行ＳＱＬ语句并计算相应的数据集合，但是没有记录被真正的返回给程序，使用FETCH语句一次提取一条记录，在执行另外一条提取记录前，被提取的记录保持为当前行，只能正向提取记录，在数据集中没有可以回退的控制。

2        游标的属性

属性	返回值	说明
ISOPEN	TRUE/FALSE	指出一个游标是打开的还是关闭的
FOUND	TRUE/FALSE	指出是否发现一条记录
NOTFOUND	TRUE/FALSE	指出是否没有发现一条记录
ROWCOUNT	NUMBER	每条提取记录的序数

       使用示例：

       IF (cur_order%FOUND) THEN

              OPEN cur_items(cur_order.order_no);

              FETCH cur_items INTO order_item;

              LOOP

                     EXIT WHEN cur_items%NOTFOUND;

                     DBMS_OUTPUT.PUT_LINE(‘On Row #’ || TO_CHAR(cur_items%

                            ROWCOUNT));

                     FETCH cur_items INTO order_item;

              END LOOP ;

       END IF;

       IF cur_items%ISOPEN THEN

              CLOSE cur_items;

       END IF;

2        根据定义所有的隐式游标都被假设为只返回一条记录。

2        Oracle的异常处理：

EXCEPTION

       WHEN ….. THEN ….

       WHEN ….. THEN ….

       WHEN OTHERS THEN …

2        SQLCODE函数获得异常编码，SQLERRM函数获得异常的详细描述信息。

2        游标还可以使用FOR循环来读取。

2        本地动态SQL

RPOCEDURE INSERT_ITEMS(Ptable varchar2, Pprod varchar2, Pdesc varchar2)

IS

       Sql_statement varchar2(500);

BEGIN

       Sql_statement := ‘insert into ‘ || Ptable || ‘ values( :prod, :desc )’;

       EXECUTE IMMEDIATE sql_statement USING Pprod, Pdesc;

END;

2        存储子程序和包的数据字典视图

视图名称	说明
DBA_SOURCE	所有编译模块的文本源代码
DBA_ERRORS	全部模块编译错误的文本清单
DBA_OBJECT_SIZE	编译模块的状态，如：有效性、对象大小
DBA_OBJECTS	编译模块的种类（存储过程、包、函数）
DBA_DEPENDENCIES	对象相关性列表，如在包中引用的表

如果一个存储模块变为非法，必须由服务器自动重新编译它或由它的拥有者手工编译它。

2        检查全部存储子程序状态的ＳＱＬ语句

COLUMN object_name FORMAT A30

COLUMN timestamp FORMAT A20

SELECT object_name,object_type,timestamp,status

FROM user_objects

WHERE object_type IN (‘FUNCTION’,’PROCEDURE’,’PACKAGE’,’PACKAGE

BODY’) ORDER BY object_name, object_type;

提示：表结构的修改有时候会使存储过程和对象无效，可以使用ALTER….

COMPILE命令重新编译对象，如下面的例子所示：

ALTER PROCEDURE PROCEDURE_NAME COMPILE;

2        包的概念

包是程序对象的集合，可以提供数据、游标及其它语言结构的延续性。当引用包里面的任意对象时，整个包便被加载到内存中。

包还允许子程序重载，这个特性对于纯粹的子程序是不可用的。

2        错误号：ORU-10027的处理

错误号ORU-10027是指超出了Oracle的输出缓存，Oracle默认的输出缓存是2000个字节，当用DBMS_OUTPUT输出时，可能会遇到这样的错误，解决的办法是通过DBMS_OUTPUT.ENABLE(20000)设置一个更大缓存。

2        关于包中全局变量的使用

应该尽量少用全局变量或根本不用全局变量，除了常量和各种表的记录变量。

2        DBMS_APPLICATION_INFO包

这个包能够让开发者在视图V$session和V$sqlarea中添加追踪信息。

2        DBMS_DDL包

可以使用这个包做两件事：

?         使用alter_compile()重新编译存储子程序和包

?         使用analyze_object()分析一个表、索引或簇

下面的存储过程用于查找当前用户所有无效的存储子程序和包并重新编译它：

CREATE OR REPLACE PRO_RECOMPILE_INVALID_OBJECT

IS

       CURSOR invalid_prog_obj IS

              SELECT object_name,object_type FROM user_objects

WHERE status = ‘INVALID’;

                     rec invalid_prog_obj%ROWTYPE;

                     status NUMERIC;

              BEGIN

                     OPEN invalid_prog_obj;

                     FETCH invalid_prog_obj INTO rec;

LOOP

                            EXIT WHEN invalid_prog_obj%NOTFOUND;

                            DBMS_DDL.alter_compile(rec.object_type, NULL, rec.object_name)

                            FETCH invalid_prog_obj INTO rec;

                     END LOOP ;

                     CLOSE invalid_prog_obj;

              EXCEPTION

              WHEN OTHERS THEN

                     BEGIN

Status := SQLCODE;

                         DMBS_OUTPUT.put_line(SQLERRM(status));

                         IF (invalid_prog_obj%ISOPEN) THEN

                                CLOSE invalid_prog_obj;

                         END IF;

                  EXCEPTION WHEN OTHERS THEN

                         NULL;

                  END;

              END;

2        关于包中全局变量的使用

应该尽量少用全局变量或根本不用全局变量，除了常量和各种表的记录变量。

n         日期：2004-06-25

2        SYS和SYSTEM模式

SYS模式是所有内部数据库表、结构、供给包、过程等等的拥有者，它所拥有所有V$和数据字典视图，并创建所有封装的数据库角色（DBA、CONNECT、RESOURCE），SYS是一个数据库的根用户或系统管理员，应尽量避免作为SYS注册到系统中工作。

SYS用户是唯一能够访问特定内部数据字典表的用户，其默认密码为CHANGE_ON_INSTALL，请在安装后立即更改该密码。

SYSTEM模式是用于DBA任务的缺省帐户，其默认密码为MANAGER。

2        数据库对象

数据库对象分为两类，一类是供RDBMS内部使用的对象，被称为系统数据库对象；另一类是可以通过任何程序访问的对象，被称为用户数据库对象。

系统数据库对象包括：

l         初始化参数文件

l         控制文件

l         联机和归档重做日志文件

l         追踪文件

l         ROWID（行内部地址）

l         Oracle块

 

2        逻辑备份与恢复

完全逻辑备份：

       D:/EXP USERID=USER_ID/PASSWORD@SERVICE_NAME FULL=Y

              FILE=BACKUP_FILE_PATH

完全逻辑恢复：

       D:/IMP USERID=USER_ID/PASSWORD@SERVICE_NAME FULL=Y

              FILE=BACKUP_FILE_PATH IGNORE=Y