PL/SQL学习三

8、参照变量
参照变量是指用于存放数值指针的变量。主要有游标变量和对象类型变量两种。
8.1、REF CURSOR(游标变量)
当使用显示游标的时候，您需要在定义显示游标的时候指定相应的SELECT语句，这种显示游标又称为静态游标。
当使用游标变量的时候，不需要指定SELECT语句，而是在打开游标的时候指定，这种游标称为动态游标。

-- ex:pl/sql_10  
DECLARE
  TYPE TMP_CUR IS REF CURSOR; --定义游标变量类型
  CUR01  TMP_CUR; --定义游标变量
  V_NAME EMP.ENAME%TYPE;
  V_SAL  EMP.SAL%TYPE;
BEGIN
  OPEN CUR01 FOR --打开游标
    SELECT ENAME, SAL FROM EMP WHERE ROWNUM = 1;
  LOOP
    FETCH CUR01
      INTO V_NAME, V_SAL;
    EXIT WHEN CUR01%NOTFOUND;
    --输出
    DBMS_OUTPUT.PUT_LINE('name:' || V_NAME);
  END LOOP;
  CLOSE CUR01;
  --异常处理
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('error');
END;

8.2、REF obj_type(对象类型)
如7.3中嵌套表中的EM_TYPE和7.4中的tmp_type类型就是对象类型。
可以通过对象类型创建对象表。如创建7.4的tmp_type的对象表如下：

SQL> create table tmp_test of tmp_type;

表已创建。

SQL> desc tmp_test
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------

 TITLE                                              VARCHAR2(30)
 PDATE                                              DATE

REF实际是做对象类型的指针使用，作用是为了共享相同的对象，从而降低占用空间。
如:

--创建测试对象类型和测试对象表,并往测试对象表插入两条数据
SQL> drop table tmp_test;

表已删除。
SQL> drop type tmp_array;

类型已删除。
SQL> --创建一对象类型
SQL> CREATE OR REPLACE TYPE TMP_TYPE AS OBJECT
  2  (
  3    STREET  VARCHAR2(60),
  4    CITY    VARCHAR2(30),
  5    STATE   VARCHAR2(30),
  6    ZIPCODE VARCHAR2(8),
  7    OWNER   VARCHAR2(20)
  8  );
  9  /   
类型已创建。

SQL> --创建对象表
SQL> CREATE TABLE TMP_TABLE OF TMP_TYPE;

表已创建。
SQL> --插入数据
SQL> INSERT INTO TMP_TABLE VALUES('北京', '朝阳区', '朝阳街', '010', '张三');

已创建 1 行。

SQL> INSERT INTO TMP_TABLE VALUES('广州', '天河区', '中山大道', '020', '李四');


已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> --创建表person并引用tmp_type对象类型
SQL> CREATE TABLE person(
  2  ID NUMBER(8) PRIMARY KEY,
  3  NAME VARCHAR2(20),
  4  addr REF tmp_type);

表已创建。

--插入数据,'王二'的地址和'张三'一样直接利用SELECT查询出来引用到表person中
--注意REF()括号中应当用别名,不然会报错
--不能直接用对象表名,通过REF(t)是得到的一个指向tmp_table对象表相应数据的地址指针
SQL> select REF(tmp_table) from tmp_table;
select REF(tmp_table) from tmp_table
           *
第 1 行出现错误:
ORA-00904: "TMP_TABLE": 标识符无效
SQL> select REF(t) from tmp_table t;--因为只有2条记录,所以对应2个指针地址值

REF(T)
--------------------------------------------------------------------------------------

0000280209EF4CBC59804040DD8AB78B9E3B01189A6AA8DCCD6A5942E4A119009E64908FD60440184E0000

000028020913F3BEB30AE840D08E32FC32415EFB826AA8DCCD6A5942E4A119009E64908FD60440184E0001

-- 向表person中插入数据,注意引用了tmp_table的数据
SQL> INSERT INTO person SELECT 1,'王二',REF(t)
  2  FROM tmp_table t WHERE owner='张三';

已创建 1 行。

SQL> INSERT INTO person SELECT 2,'王四',REF(t)
  2  FROM tmp_table t WHERE t.owner='李四';

已创建 1 行。
--sqlplus调整输出
SQL> col id for 999
SQL> col name format a10
SQL> col addr format a40
SQL> select * from person;
--注意与上面tmp_table的ref(t)值是不一样的,是不同的指针来的
  ID NAME       ADDR
---- ---------- ----------------------------------------
   1 王二         0000220208EF4CBC59804040DD8AB78B9E3B0118
                9A6AA8DCCD6A5942E4A119009E64908FD6

   2 王四         000022020813F3BEB30AE840D08E32FC32415EFB
                826AA8DCCD6A5942E4A119009E64908FD6

9、LOB类型
LOB类型是用来存储大批量数据的变量。主要分为两种：
内部LOB：CLOB,BLOB(存储二进制数据),NCLOB
内部LOB的数据都是存储在数据库中的，且支持事务操作。
外部LOB：BFILE(存储的是指向OS文件的指针)
外部LOB的数据是存储在OS文件中的，不支持事务操作。

10、非PL/SQL变量
10.1 使用SQL*PLUS变量
在PL/SQL中使用SQL*PLUS变量，必须先用variable进行变量定义。如:

   SQL> var t_name varchar2(20);
   SQL> BEGIN
     2    SELECT ename
     3    INTO :T_NAME
     4    FROM emp
     5    WHERE empno=7788;
     6* END;
   SQL> /
   PL/SQL 过程已成功完成。
   SQL> print t_name
T_NAME
--------------------------------
SCOTT

10.2 使用Pro*C/C++变量
在PL/SQL中使用Pro*C/C++宿主变量时，必须先定义宿主变量。如：

CHAR NAME[10];
EXEC SQL EXECUTE
 BEGIN
  SELECT ename INTO :NAME FROM emp
  WHERE empno=7788;
 END;
END-EXEC;
printf("雇员名:%s\n",NAME);--c语言输出

11、标识符
合法的：
v_enamevarchar2(10);
v$say number(8,2);
v#error exception;
“123456” varchar2(12);--以数字开始带有双引号

-- ex:pl/sql_11
SQL>1  DECLARE
  2    "123"   VARCHAR2(20) := 'test';
  3    "变量A" NUMBER(10, 2);
  4    T       VARCHAR2(20);
  5    N       NUMBER(10, 2);
  6  BEGIN
  7    T       := "123";
  8    "变量A" := 12.22;
  9    N       := "变量A";
 10    DBMS_OUTPUT.PUT_LINE(T);--注意dbms包不能直接输出"变量A"或"123"
 11    DBMS_OUTPUT.PUT_LINE(N);
 12* END;
SQL> /
test
12.22

PL/SQL 过程已成功完成。

非法标识符:
带特殊符号的， 如:v%enam varchar2(10);
以#开头的, 如:#vl exception;
以数字开头的, 如：2say number(6,2);
连续定义变量的, 如:v1,v2,v3 varchar2(10);
以汉字开头的, 如:变量B varchar2(10);
用关键字的, 如:select number(6,2);

12、PL/SQL代码编写约定
当定义变量的时候,建议用V_作为前缀,如v_ename,v_sal等，
当定义常量的时候,建议用c_作为前缀,如c_rate
当定义游标的时候,建议用_cursor作为后缀,如emp_cursor
当定义异常的时候,建议使用e_作为前缀,如e_int_error
当定义表类型的时候,建议用_table_type作为后缀
当定义表变量的时候,建议用_table作为后缀
当定义记录类型的时候,建议用_record_type作为后缀
当定义记录变量的时候,建议用_record作为后缀

建议关键字和数据类型等采用大写格式,变量名和参数、数据库对象、列名采用小写格式。

13、 简单的查询语句(比较简单,不细说了)
使用SQL*PLUS命令describe(简写desc),可以显示表结构，如：

SQL> desc scott.emp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

使用SELECT * FROM scott.emp;查询表emp的所有数据。
使用SELECT 列名(如ename) from SCOTT.EMP;查询表emp指定列的数据(这里是ename列)
使用DISTINCT关键字去掉重复行。
使用AS 别名(注意别名包含特殊字符或空格、中文等需要用双引号引住)：
SELECT ename AS "姓名",to_char(hiredate,'yyyyy-mm-dd') AS "日期" FROM scott.emp;
或者用空格 别名
SELECT ename "姓名",to_char(hiredate,'yyyyy-mm-dd') "日期" FROM scott.emp;
使用||连接字符串或数字:
select 'I''m '||123||' PL/SQL ' from dual;--输出I'm 123 PL/SQL (注意输出'的方法)
使用WHERE子句限制查询结果
1. WHERE条件中使用数字
SELECT ename,sal FROM scott.emp WHERE say>500;
2. WHERE条件中使用字符
SELECT job,sal FROM scott.emp WHERE ename='SCOTT';
3. WHERE条件中使用日期
SELECT ename,sal,hiredate FROM scott.emp WHERE hiredate>TO_DATE('2010-01-01','YYYY-MM-DD';
4. WHERE条件中BETWEEN...AND
SELECT ename,sal,hiredate,job FROM SCOTT.emp WHERE sal BETWEEN 1000 AND 2000;
5. WHERE条件中LIKE进行模糊查询
SELECT ename,sal FROM scott.emp WHERE ename LIKE 'S%';
SELECT ename,sal FROM scott.emp WHERE ename LIKE '_A%';--查询第2个字符为大写A的雇员的雇员名和工资。
SELECT ename,sal FROM scott.emp WHERE ename LIKE '%a_%' ESCAPE 'a';--显示雇员名包含_的雇员信息,其中ESCAPE表示字符'a'为转义字符。
6. WHERE条件中使用IN
SELECT ename,sal FROM scott.emp WHERE sal IN(800,1250);
7. WHERE条件中使用IS NULL/IS NOT NULL
SELECT ename,sal FROM scott.emp WHERE mgr IS NULL;--注意当有NULL值的时候不要使用=、<、>等操作符进行比较
SELECT ename,sal FROM scott.emp WHERE mgr IS NOT NULL;
8. WHERE条件中使用逻辑操作符AND、OR、NOT
SELECT ename,sal FROM scott.emp WHERE deptno=20 AND job='CLERK';
SELECT ename,sal FROM scott.emp WHERE sal>2500 OR job='MANAGER';
SELECT ename,sal FROM scott.emp WHERE sal NOT IN(800,1250);
9. WHERE条件中使用ORDER BY对相应字段进行排序
SELECT ename,sal FROM scott.emp WHERE deptno=30
ORDER BY sal;--按sal字段排序,默认从小到大,如果有NULL会显示在最前面。使用DESC关键字来进行降序排序。

14、NULL值处理
NULL既不是空格也不是0,NULL加减乘除任何数值都等于NULL。
14.1 使用NVL函数处理NULL。
NVL(exp1,exp2);---如果exp1是NULL值,那么返回exp2,否则返回exp1.注意:exp1与exp2数据类型必须要匹配.

SQL> select nvl(null,'aaa') from dual;

NVL
---
aaa

SQL> select nvl(null*0,6) from dual;

NVL(NULL*0,6)
-------------
            6

SQL> select nvl(null*0,'aaa') from dual;
select nvl(null*0,'aaa') from dual
                  *
第 1 行出现错误:
ORA-01722: 无效数字

14.2 使用NVL2函数处理NULL。
NVL2是oracle 9i新增的函数。
NVL2(exp1,exp2,exp3)--如果exp1不是NULL,就返回exp2,如果exp1是NULL,就返回exp3
--exp2、exp3与exp1的数据类型必须要匹配

SQL> select nvl2(null*0,9,'a') from dual;
select nvl2(null*0,9,'a') from dual
                     *
第 1 行出现错误:
ORA-01722: 无效数字


SQL> select nvl2(null*0,'b','a') from dual;

N
-
a

SQL> select nvl2(null*0,'a',9) from dual;

N
-
9

15、DML语句
DELETE、INSERT、UPDATE属于DML语句，分别用来删除、插入、更新数据。
15.1、插入数据(INSERT)
注意插入数据时，必须要满足约束规则,必须要为主键列和NOT NULL列提供数据。

   SQL> INSERT INTO EMP VALUES --插入表的所有列
     2  (7876,'ADAMS','CLERK',7788,to_date('13-7-87','dd-mm-yyyy')-51,1100,NULL,20);
   
   已创建 1 行。
   SQL> INSERT INTO EMP  --只插入指定列的值
     2    (EMPNO, ENAME, JOB, HIREDATE)
     3  VALUES
     4    (1356, 'MARY', 'CLERK', TO_DATE('1988-10-20', 'yyyy-mm-dd'));
   
   已创建 1 行。
   --使用DEFAULT值来插入,如果指定的DEFAULT值则插入指定的值，否则插入NULL
   SQL> INSERT INTO dept VALUES(60,'MARKET',DEFAULT);

   已创建 1 行。

使用子查询结果进行插入:

   SQL> INSERT /*+APPEND */INTO EMPLOYEES --使用/*+APPEND*/表示采用直接装载数据
     2    (EMPNO, ENAME, SAL, DEPTNO)
     3    SELECT EMPNO, ENAME, SAL, DEPTNO
     4    FROM EMP WHERE DEPTNO = 20;

   已创建5行。

当需要插入大量数据的时候，采用/*+APPEND*/选项会大大提高速度。其具体原因是使用选项
APPEND后,数据会直接加到表的最后面,而不会去利用表的空闲块去插入。

多表同时插入:

   --使用ALL执行多表插入,每个满足条件的记录都会插入到表
SQL> create table clerk as select * from emp where 1=2;

表已创建。

SQL> create table dept10 as select * from emp where 1=2;

表已创建。

SQL> create table dept20 as select * from emp where 1=2;

表已创建。

SQL> create table dept30 as select * from emp where 1=2;

表已创建。

SQL> create table other_dept as select * from emp where 1=2;

表已创建。   
SQL> INSERT ALL
  2  WHEN DEPTNO = 10 THEN INTO DEPT10
  3  WHEN DEPTNO = 20 THEN INTO DEPT20
  4  WHEN DEPTNO = 30 THEN INTO DEPT30
  5  WHEN job='CLERK' THEN INTO clerk
  6  ELSE INTO OTHER_DEPT
  7  SELECT * FROM emp;

已创建18行。
SQL> select count(*) from dept10;

  COUNT(*)
----------
         3

SQL> select count(*) from dept20;

  COUNT(*)
----------
         5

SQL> select count(*) from dept30;

  COUNT(*)
----------
         6
SQL> select count(*) from clerk;

  COUNT(*)
----------
         4

--使用FIRST进行多表插入

SQL> rollback;

回退已完成。
SQL> INSERT FIRST
  2  WHEN DEPTNO = 10 THEN INTO DEPT10
  3  WHEN DEPTNO = 20 THEN INTO DEPT20
  4  WHEN DEPTNO = 30 THEN INTO DEPT30
  5  WHEN job='CLERK' THEN INTO clerk
  6  ELSE INTO OTHER_DEPT
  7  SELECT * FROM emp;

已创建14行。
SQL> select count(*) from dept10;

  COUNT(*)
----------
         3

SQL> select count(*) from dept20;

  COUNT(*)
----------
         5

SQL> select count(*) from dept30;

  COUNT(*)
----------
         6

SQL> select count(*) from clerk;

  COUNT(*)
----------
         0

使用FIRST进行多表插入的时候,如果该记录已经满足条件并插入到前面的某个表了,
那么后面将不再插入到任何表。

15.2 更新数据( UPDATE )
当更新数据时，数据必须要满足约束条件。
当更新数据时，数据必须要与列的数据类型匹配。
1.更新单列数据,如:

   SQL> UPDATE emp SET sal=2460 WHERE upper(ename)='SCOTT';

   已更新 1 行。
   

2.更新多列数据,如:

   SQL> UPDATE emp SET sal=sal+100,comm=sal*0.5 WHERE deptno=20;

   已更新5行。

3.使用DEFAULT选项更新数据(9i及以后的版本),如:
该列在建表时如果没有指定的DEFAULT值,那么更新后就是NULL，否则就是设定的DEFAULT值

   SQL> UPDATE emp SET job=DEFAULT WHERE lower(ename)='scott';

   已更新 1 行。

   SQL> SELECT job FROM emp WHERE lower(ename)='scott';

   JOB
   ---------

   

4.使用子查询更新数据
用来更新关联数据,如：

     SQL> --更新使得SCOTT的职位job、工资sal、补助comm与SMITH的一致
     SQL> UPDATE EMP
       2     SET (JOB, SAL, COMM) = (SELECT JOB, SAL, COMM
       3                               FROM EMP
       4                              WHERE ENAME = 'SMITH')
       5   WHERE ENAME = 'SCOTT';
     
     已更新 1 行。

用来复制表数据,如:

     SQL> -- 当emp表与employee表的JOB一样都为‘CLERK’(对应EMPNO--7788)时,
     SQL> -- 使用emp表的deptno更新employee的DEPTNO
     SQL> UPDATE EMPLOYEE
       2     SET DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO = 7788)
       3   WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7788);
       
     已更新 1 行。

15.3 删除数据( DELETE、TRUNCATE )
注意使用删除DELETE的时候，如果没有带WHERE条件,将会删除整个表的数据。
删除相关数据的时候,还要注意是否该表数据是另外一个表的主表(也就是当子表不存在相关记录才可以删除)
这里的主从关系即是指主外键约束关系。当一个表的主键在另外一个表做外键,那么这个表称为主表，依赖主键存在(外键所在的表)的表称为从表
1.使用DELETE删除指定的数据,如:

     SQL> DELETE FROM emp WHERE lower(ename)='smith';

     已删除 1 行。

2.如果不带WHERE子句，将删除表的所有数据,使用ROLLBACK回滚(后面会介绍)。

     SQL> DELETE FROM emp;

     已删除13行。
     SQL> ROLLBACK;

     回退已完成。

3.使用TRUNCATE截断表(注意TRUNCATE属于DDL操作,截断表后不可回滚)
使用TRUNCATE删除整个表的数据比DELETE快得多,TRUNCATE表后，表的高水平线和索引将会被重新设置(释放了相关空间),
所以在TRUNCATE之后的表操作速度比DELETE操作后的表要快。

     SQL> TRUNCATE TABLE employee;

     表被截断。

4.使用子查询删除数据

     SQL> DELETE FROM EMP
       2   WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOWER(DNAME) = 'sales');

     已删除6行。