oracle学习笔记一

一、定义并使用变量
在PL/SQL程序中定义变量、常量和参数时，则必须要为它们指定类型。
有标量（Scalar）类型、复合（composite)类型、参照（Reference)类型和LOB(large Object)类型等四种。
/定义标量变量/
当编写PL/SQL程序时，如果要引用变量变量，必须首先在定义部分定义变量变量，然后才能执行部分或例外处理部分中使用这些标量变量。

1、语法
identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expr]
identifier:用于指定变量或常量的名称。
CONSTANT:用于指定常量。当定义常量时，必须指定它的初始值，并且其数值不能改变。
datatype:用于指定变量或常量的数据类型
NOT NULL 用于强制初始化变量不能为NULL，当指定NOT NULL选项时，必须要为变量提供数值。
:= 用于为变量和常量指定初始值
DEFAULT 用于为变量和常量指定初始值
expr:用于指定初始值的PL/SQL表达式，可以是文本值、其他变量、函数等。

2、定义标量变量示例
当定义标量变量时，必须要使用标量数据类型。示例如下
v_ename VARCHAR2(10);
v_sal NUMBER(6,2);
v_balance BINARY_FLOAT;
c_tax_rate CONSTANT NUMBER (3,2):=5.5;
v_hiredate DATE;
v_valid BOOLEAN NOT NULL DEFAULT FALSE;

 

3、使用标量变量
当在定义部分定义了标量变量之后，在执行部分和例外处理部分可以引用这些标量变量。需要注意，在PL/SQL块中为变量赋值不同于其他编程语言，必须在等号前加冒号：=。下面以输入雇员号显示雇员姓名、工资、个人所得税为例，说明在PL/sql块中使用标量变量的方法。示例如下：
DECLARE
v_ename VARCHAR2(5);
v_sal NUMBER(6,2);
c_tax_rate CONSTANT NUMBER(3,2):=0.03;
v_tax_sal NUMBER(6,2);
BEGIN
  SELECT ename,sal INTO v_ename, v_sal
  FROM emp WHERE empno=&eno;
  v_tax_sal:=v_sal*c_tax_rate;
  dbms_output.put_line('雇员名'||v_enama);
  dbms_output.put_line('雇员工资'||v_sal);
 dbms_output.put_line('所得税'||v_tax_sal);
END;
/
输入eno的值：7788
雇员名：SCOTT
雇员工资：1500
所得税：45

/使用%TYPE属性/
当定义PL/SQL变量存放列值时，必须确保变量使用合适的数据类型和长度，否则在运行过程中可能会出现PL/SQL运行错误。当使用%TYPE属性定义变量时，它会按照数据库列或其他变量在确定新变量的类型和长度。示例如下
DECLARE
 v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
c_tax_rate CONSTANT NUMBER(3,2):=0.03;
v_tax_sal v_sal%TYPE;
BEGIN
 SELECT ename,sal INTO v_ename, v_sal
  FROM emp WHERE empno=&eno;
  v_tax_sal:=v_sal*c_tax_rate;
  dbms_output.put_line('雇员名'||v_enama);
  dbms_output.put_line('雇员工资'||v_sal);
 dbms_output.put_line('所得税'||v_tax_sal);
END;
/
输入eno的值：7788
雇员名：SCOTT
雇员工资：1500
所得税：45
如例所示，变量v_ename,v_sal 与EMP表的ename,sal列的数据类型和长度完全一致，而变量v_tax_sal与变量v_sal的数据类型和长度完全一致，这样，当ename 和sal列的类型和长度发生改变时，该PL/sql块将不需要进行任何修改。

/参照变量/
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用空间。在编写PL/SQL程序时，可以使用游标变量（REF CURSOR）和对象类型变量REF Obj_type 等两种参照变量类型。
1、REF CURSOR
当使用显式游标时，需要在定义显式游标时指定相应的SELECT 语句，这种显式游标称为静态游标。当使用游标变量时，在定义游标变量时不需要指定SELECT语句，而是在打开游标时指定SELECT语句，从而实现动态的游标操作。
DECLARE
TYPE c1 IS REF CURSOR;  /* c1为REF CURSOR 类型*/
emp_cursor c1;          /* emp_cursor为游标变量 */    
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
  OPEN emp_cursor FOR   /* 在打开游标变量时指定了其所对应的SELECT语句*/
  SELECT ename,sal FROM emp WHERE  deptno=10;
LOOP
  FETCH emp_cursor INTO v_ename,v_sal;
  EXIT WHEN emp_cursor%NOTFOUND;
  dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
/
2、REF obj_type

当编写对象类型应用时，为了共享相同对象，可以使用REF引用对象类型，REF实际是指向对象实例的指针。下面通过示例说明如何使用REF，首先建立对象类型home和对象表homes,然后插入数据。示例如下：
CREATE OR REPLACE TYPE home_type AS OBJECT(
street VARCHAR2(50),city VARCHAR2(20),
state VARCHAR2(20),zipcode VARCHAR2(6),
owner VARCHAR2(10)
);
/
CREATE TABLE homes OF home_type;
INSERT INTO homes VALUES ('天河12号','广州','广东','010010','李丽');
INSERT INTO homes VALUES ('天河13号','广州','广东','010010','王磊');
COMMIT;
如例所示，对象表homes存放着家庭所在地以及户主姓名。假定每个家庭有四口人，当进行人口统计时，为了使得同一家庭成员可以共享家庭地址，可以使用REF引用home_type对象类型，从而降低占用空间。示例如下：
CREATE TABLE person (
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(10),addr REF home_type
);
INSERT INTO person SELECT 1,'李好',ref(p)
FROM homes p WHERE p.owner='李丽';
INSERT INTO person SELECT 2, '李红',ref(p)
FROM homes p WHERE p.owner ='李丽';
INSERT INTO person SELECT 3,'王军',ref(p)
FROM homes p WHERE p.owner='李丽';
commit;

/LOB变量/
LOB变量是指用于存储大批量数据的变量。Oracle将LOB分为两种：内部LOB和外部LOB、内部LOB包括CLOB,BLOB,NCLOB三种类型，他们的数据被存储在数据库中，并且支持事物操作（提交、回退、保存点）。外部LOB只有BFILE一种类型，该类型的数据被存储在OS文件中，并且不支持事务操作。其中，CLOB/NCLOB用于存储大批量字符数据，BLOB用户存储大批量二进制数据，而BFILE则存储指向OS文件的指针。
因为医药的配方需要用大量中文说明，所以可以将其所对应的数据库列定义为CLOB类型，因为照片对应于图形/图像（二进制数据），所以可以将其所对应的数据库列定义为BLOB类型，为例通过数据库访问OS电影文件，可以在数据库中定义BFILE类型的数据库列。
/非PL/SQL变量/
当在SQL*PLUS或应用程序（例如PRO*C/C++）中与PL/sql块之间进行数据交互时，需要使用SQL*PLUS变量或应用程序标量来完成。当在PL/SQL块中引用非PL/SQL变量时，必须要在非PL/SQL变量前加冒号（“：”）

1、使用SQL*PLUS变量
在PL/sql块引用SQL*PLUS变量时，必须首先使用VARIABLE命令定义变量 ；而如果要在SQL*PLUS中输出变量内容，则需要使用PRINT 命令。示例如下：
var name varchar2(10)
BEGIN
 SELECT ename INTO :name FROM emp
 WHERE empno =7788;
END;
/
PRINT name
NAME
------------
SCOTT
2、使用Procedure Builder 变量
当在PL/SQL块中引用PROCEDURE builder 变量时，必须首先使用.CREATE命令定义变量，而如果在Procedure Builder中输出变量内容，则可以使用包TEXT_IO.示例如下:
PL/SQL>.CREATE CHAR name LENGTH 10
PL/SQL>BEGIN
     +>SELECT ename INTO :name FROM emp
     +> WHERE empno=7788;
     +>END;
PL/SQL>TEXT_IO.PUT_LINE(:name);
SCOTT