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_ename varchar2(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行。