PL/SQL学习三

来源：互联网发布：mobi域名2017最新消息编辑：程序博客网时间：2024/05/16 19:18

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)--------------------------------------------------------------------------------------0000280209EF4CBC59804040DD8AB78B9E3B01189A6AA8DCCD6A5942E4A119009E64908FD60440184E0000000028020913F3BEB30AE840D08E32FC32415EFB826AA8DCCD6A5942E4A119009E64908FD60440184E0001-- 向表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 999SQL> col name format a10SQL> col addr format a40SQL> 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_nameT_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_ename varchar2(10);
   v$say   number(8,2);
   v#error exception;
   “123456” varchar2(12);--以数字开始带有双引号

-- ex:pl/sql_11SQL>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> /test12.22PL/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---aaaSQL> select nvl(null*0,6) from dual;NVL(NULL*0,6)-------------            6SQL> 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-aSQL> 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(*)----------         3SQL> select count(*) from dept20;  COUNT(*)----------         5SQL> select count(*) from dept30;  COUNT(*)----------         6SQL> 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(*)----------         3SQL> select count(*) from dept20;  COUNT(*)----------         5SQL> select count(*) from dept30;  COUNT(*)----------         6SQL> 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行。