SQL PL/SQL复习

SQL:

SELECT

DML (data manipulation language): UPDATE, INSERT, DELETE

DDL (data definition language): CREATE, ALTER, DROP

Data control language: GRANT, REVOKE

Transaction control statement: COMMIT, ROLLBACK, SAVEPOINT

Session control statement: ALTER SESSION, SET ROLE

System control statement: ALTER SYSTEM

 

处理NULL

NVL(a, b): if a is not null, return a; else, return b.

NVL2(a, b, c): if a is not null, return b; else, return c.

和null比较用IS NULL

 

模糊匹配：％０个或多个字符，　_单个字符

转义:转义_

Select ename, sal from emp

Where ename like ‘%a_%’ escape ‘a’;

 

SELECT * FROM TBL WHERE COL LIKE 'ABC\%\_%' ESCAPE '\';

这样\后的字符就会被转义．最后一个％是通配符．

 

当select语句同时包含多个子句(where, group by, having, order by)时，order by必须是最后一条子句．

 

当使用union, union all, intersect, minus等集合操作，并查询结果时，如果选择列表的列名不同，并且希望进行排序，则必须使用列位置来排序。

 

数字函数：

ROUND四舍五入

TRUNC截断数字值到特定位数，比如trunc(22.45, 1) = 22.4

MOD取余数

CEIL:大于等于的最小整数

FLOOR:小于等于的最大整数

 

字符函数：

Upper, lower, instr, substr, lpad, rpad

 

正则表达式

REGEXP_LIKE

 

使用直接装载方式复制数据

INSERT /*+APPEND */ INTO employee (empno, ename, sal, deptno)

SELECT empno, ename, sal, deptno FROM emp

WHERE deptno=20;

使用append提示进行insert叫做直接路径加载插入，使用这种提示因为系统不去查找freelist链表中的空闲块，直接在高水标记位以上插入数据，因此速度比较快。但是也应该注意直接路径加载插入有一些不同：
1.它不记录日志，因此一旦插入的数据在没有保存回磁盘的时候发生掉电之类的状况插入的数据不能重做。
2.它比较浪费磁盘空间.

 

在INSERT子句中使用子查询：

INSERT INTO (SELECT empno, ename, sal, deptno FROM emp

WHERE depot=30) VALUES(1112, ‘MARY’, 2000, 30);

加WITH CHECK OPTION后，可以限制被插入数据满足子查询WHERE子句的要求。

INSERT INTO (SELECT empno, ename, sal, deptno FROM emp

WHERE depot=30 WITH CHECK OPTION) VALUES(1112, ‘MARY’, 2000, 30);

 

多表插入：

无条件

INSERT ALL

INTO sal_history values(ename, hiredate, sal)

INTO mgr_history values(ename, mgr, sal)

SELECT ename, hiredate, mgr, sal FROM emp;

 

有条件

INSERT ALL

WHEN sal>1000 THEN

INTO sal_history VALUES(ename, hiredate, sal)

WHEN mgr>7700 THEN

INTO mgr_history VALUES(ename, mgr, sal)

SELECT ename, hiredate, mgr, sal FROM emp;

 

有条件INSERT FIRST:对满足第一个ＷＨＥＮ条件的纪录，随后的WHEN子句直接跳过。

INSERT ALL

WHEN sal>3000 THEN

INTO sal_history VALUES(ename, hiredate, sal)

WHEN sal>1000 THEN

INTO mgr_history VALUES(ename, mgr, sal)

SELECT ename, hiredate, mgr, sal FROM emp;

 

 

Merge:

MERGE INTO new n USING emp e

ON (n.empno = e.empno)

WHEN MATCHED THEN UPDATE SET n.sal = e.sal

WHEN NOT MATCHED THEN

INSERT (n.empno, n.ename, n.sal, n.comm)

VALUES(e.empno, e.ename, e.sal, e.comm);

 

Delete可以回退，truncate不可以。

 

SET TRANSACTION READ ONLY;

随后的查询操作读到的数据都是在该语句执行的那个点的一致性数据。

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

该事务只能看到自己所修改的数据，看不到在该语句执行后，其他事务所作的修改。

 

当使用连接查询时，必须在where子句中指定有效的连接条件。如果不指定，或者连接条件无效，那么会导致声称笛卡尔积。

 

各种连接：

外连接+：不推荐用了

 

CROSS JOIN笛卡尔积

Select d.dname, e.ename from dept d cross join emp e;

 

Natural join基于同名列执行相等连接

当有多个同名列时，使用natural joi会基于多个同名列进行相等连接，如果只希望某个列执行相等连接，使用using

Select d.dname, e.ename from dept d join emp e using (deptno);

 

如果相等连接，但列名不同，则只用on

Select e.ename, e.sal, d.dname from emp e join dept d

On e.deptno = d.deptno and e.deptno = 10;

 

左连接：返回满足连接条件的，以及不满连接条件的左边表的其他数据。

Select a.dname, b.ename from dept a left join emp b

On a.deptno = b.deptno and a.deptno = 10;

 

右连接，反之。

 

完全连接：返回满足连接条件的，以及不满连接条件的左边表和右边表的其他数据。

Select a.dname, b.ename from dept a full join emp b

On a.deptno = b.deptno and a.deptno = 10;

 

常用分组函数
MAX, MIN
AVG, SUM
COUNT

select xx
from xx
where xxx
group by xxx
having xxx  (限制分组后的显示结果）
order by xx;
如果选择列表同时包含有列、表达式和分组函数，那么列和表达式必须出现在GROUP BY字句中，例如：
select deptno, job, avg(sal), max(sal) from emp
group by deptno, job;
注意：
分组函数只能出现在选择列表、order by子句、having子句中。
当使用分组函数时，会忽略NULL行。

ROLLUP, CUBE
select deptno, job, avg(sal) from emp
group by rollup(deptno, job);
生成每部门每岗位的平均工资、每部门的平均工资、所有雇员平均工资。
select deptno, job, avg(sal) from emp
group by cube(deptno, job);
生成每部门每岗位的平均工资、每部门的平均工资、岗位平均工资、所有雇员平均工资。

GROUPING：为了确定统计结果是否使用了特定列，可以使用GROUPING函数。返回0，使用；返回1，没有使用。

GROUPING SETS:生成多种分组的合并结果
比如
select deptno, job, avg(sal) from emp
group by grouping sets(deptno, job);
相当于把group by deptno, group by job的结果做union。

SELECT deptno, job, mgr, sum(sal) FROM emp
GROUP BY GROUPING SETS(deptno), GROUPING SETS(job,mgr);
相当于group by deptno, job和group by deptno, mgr的结果做union。

相关子查询：子查询中引用了主sql的表列。

WITH子句：在复杂查询中多次引用的查询块
WITH
dept_sum AS (
  SELECT d.dname, sum(e.sal) total FROM dept d, emp e
  WHERE d.deptno = e.deptno GROUP BY d.dname),
dept_avg_sum AS (
  SELECT sum(total)/count(*) avg_sum FROM dept_sum)
SELECT dname, total FROM dept_sum
WHERE total>(SELECT avg_sum FROM dept_avg_sum);

集合操作：UNION, UNION ALL, INTERSECT, MINUS

注意：如果选择列表包含有表达式或者函数，那么必须为表达式或函数定义列别名。

UNION: 去掉重复行，以第一列的结果进行升序排序

UNION ALL：不去掉重复行，不排序

INTERSECT:交集，以第一列的结果进行升序排序

 

MINUS:差集，以第一列的结果进行升序排序

需要指定按某列排序时，使用order by。列名相当的可以用列名，不同的必须使用列位置。

 

层次查询：

SELECT LPAD(‘ ‘, 3*(LEVEL-1))||ename ename,

LPAD(‘ ‘, 3*(LEVEL-1__||job job FROM emp

START WITH ename = ‘JONES’

CONNECT BY PRIOR empno=mgr;

可以这样理解，PRIOR后面跟着的是START WITH后面行的属性。因此，上述语句就是从JONES开始，将一个行的mgr值和JONES的empno值相当的在其后显示，然后依次层级往下。也就是把JONES的下属按层次显示。

 

如果CONNECT BY PRIOR mgr=empno

就是JONES.mgr 和一个行的empno相等的，依次显示。也就是把JONES的上级按层次显示。

 

Decode

Decode(expression,

Search1, result1,

Search2, result2,

…

Default)

 

CASE

等值比较：

SELECT ename, deptno, sal, CASE deptno

  WHEN 10 THEN sal*1.2

  WHEN 20 THEN sal*1.1

  ELSE sal END “Actual Salary”

FROM emp;

 

多种条件比较：

SELECT ename, sal CASE

  WHEN sal<2000 THEN sal*1.2

  WHEN sal<3000 THEN sal*1.1

  ELSE sal END “Actual Salary”

FROM emp;

 

 

LOB类型：最大长度4GB；小于4000，存放表段中，否则存放在LOB段。

 

ALTER TABLE emp01 ADD eno NUMBER(4);

ALTER TABLE emp01 MODIFY job varchar2(15) DEFAULT ‘Clerk’;

ALTER TABLE emp01 DROP COLUMN dno;

ALTER TABLE emp01 RENAME COLUMN eno TO empno;

RENAME emp01 TO employee;

 

TRUNCATE TABLE employee;删除数据，释放空间

DROP TABLE employee;删除数据，删除表结构

 

外键：外键定义在从表上，但主表必须具有主键约束或唯一约束。定义了外键后，外键数据必须在主表的主键列（或唯一列）存在，或者为NULL。

 

增加约束：

ALTER TABLE emp02 MODIFY name NOT NULL;

ALTER TABLE emp04 ADD CONSTRAINT u_emp04 UNIQUE(name);

ALTER TABLE dept01 ADD PRIMARY KEY(dno);

ALTER TABLE emp01 ADD dno NUMBER(2) REFERENCE dept01(dno);

ALTER TABLE emp01 ADD CHECK(salary BETWEEN 800 AND 5000);

 

ALTER TABLE emp01 DROP CONSTRAINT ck_emp01_salary;

 

ALTER TABLE emp05 DISABLE CONSTRAINT SYS_C005022;

ALTER TABLE emp05 ENABLE CONSTRAINT SYS_C005022;

 

 

建立只读视图：　

CREATE VIEW emp_vu20 AS

SELECT * FROM emp WHERE deptno=20

WITH READ ONLY;

 

WITH CHECK OPTION：通过视图进行的修改，必须也能通过该视图看到修改后的结果。

 

建立索引的指导方针：

1. 索引应该建立在where子句经常饮用的表列上。

2. 在连接列（多表连接）上建立索引。

3. 在需要基于某列或某几列执行排序操作

4. 不要在小表上建立索引。

 

对于复合索引，比如

CREAT INDEX i_deptno ON emp(deptno, job);

当使用and谓词引用deptno和job，或者单独使用deptno，都会使用该索引。

 

当进行delete操作时，会删除表述据，但在索引上只进行逻辑删除，其占用空间不能供其他插入操作使用。只有当索引块的所有索引入口全部被删除之后，该索引块上的空间才能使用。如果在索引列上频繁执行update/delete操作，那么应该定期重建索引，以提高其空间利用率。

ALTER INDEX xx REBUILD;

索引可以提高查询速度，但会降低DML操作速度。

 

序列：当使用rollback回退事务时，会造成序列缺口。

 

CREATE PUBLIC SYNONYM xxx FOR xxx;

CREATE SYNONYM xxx FOR xxx;

 

复合数据类型：

PL/SQL记录

TYPE emp_record_type IS RECORD (

Name emp.ename%TYPE,

Salary emp.sal%TYPE,

Title emp.job%TYPE);

 

PL/SQL表

TYPE ename_table_type IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER;

 

INDEX BY可以跟 BINARY_INTEGER, PLS_INTERGER, VARCHAR2.

 

1.使用的时候需要先赋值后读取，至少也要先初期化一下，否则会出现异常：ORA-01403: no data found。

2.这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。

 

嵌套表

TYPE TYPE2 IS TABLE OF VARCHAR2(10);

如果在表列中使用，则必须定义：CREATE TYPE emp_type AS xxx

 

 

1.必须进行初始化，否则会出现异常：ORA-06531: Reference to uninitialized collection

2.初期化方法：

v1 TYPE2 := TYPE2(); --声明时初期化数组为空

v2 TYPE2 := TYPE2('1','2','3','4','5'); --声明时初期化数组为5个元素

v1 := TYPE2(); --初期化后数组为空

v2 := TYPE2('1','2','3','4','5'); --初期化后数组为5个元素

3.数组元素的访问：

下标从1开始，不能超过数组所有元素的总和，当下标超出允许范围时，出现异常：ORA-06532: Subscript. outside of limit

因为不能访问空数组，所以空数组的场合，必须进行数组扩展。

例：v1.EXTEND;

V1(1):= ‘1’; --访问合法

v1(2):= ‘2’; --访问非法，之前必须再次执行v1.EXTEND

例：v2的下标范围是1～5。

v2(5):= ‘Hello’; --访问合法

DBMS_OUTPUT.put_line(v2(6)); --访问非法

 

如果在表列中使用嵌套表，则需要:

CREATE OR REPLACE TYPE phone_type IS TABLE OF VARCHAR2(20);

并且在建表时为嵌套表列指定专门的存储表：

CREATE TABLE person(

Id NUMBER(4), name VARCHAR2(10), sal NUMBER(6,2),

Phone phone_type

)NESTED TABLE phone STORE AS phone_table;

插入数据：

INSERT INTO person VALUES(1, ‘SCOTT’,800,

Phone_type(’0471-3456789’, ‘13849382003’));

检索：

SELECT phone INTO phone_table

FROM person WHERE name = ‘SCOTT’;

FOR I in 1..phone_table.COUNT LOOP

  Dbms_output.put_line(phone_table(i));

END LOOP;

 

 

变长数组

TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);

由于类型定义时的元素个数限制，所以TYPE3的变量在使用时最大的元素个数不能超过5个。与嵌套表基本相同。也需要通过构造方式进行初始化。如果在表列中使用，需要使用CREATE OR REPLACE TYPE xxx。

 

 

这三种类型的集合之间由许多差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都不相同。绑定涉及到集合中元素数量的限制，VARRAY集合中的元素的数量是有限，Index_by和嵌套表则是没有限制的。稀疏性描述了集合的下标是否有间隔，Index_by表总是稀疏的，如果元素被删除了.嵌套表可以是稀疏的，但VARRAY类型的集合则是紧密的，它的下标之间没有间隔。

　　Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。

　　虽然这三种类型的集合有很多不同之处，但他们也由很多相似的地方：

　　. 都是一维的类似数组的结构

　　. 都有内建的方法

　　. 访问由点分隔

　　Index_by表

　　Index_by表集合的定义语法如下：

　　TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX

　　BY BINARY_INTERGET;

　　这里面重要的关键字是INDEX BY BINARY_INTERGET，没有这个关键字，那么集合将是一个嵌套表，element_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合类型对数据库的数据类型都有限制

 

集合方法：

EXISTS: if id_table.exists(5) then …

COUNT: id_array.count

LIMIT:用于vvarray变量所允许的最大值。Id_array.limit

FIRST, LAST:返回第一个元素和最后一个元素的下标。  Ename_table(ename_table.first)

PRIOR, NEXT: 返回当前元素前一个和后一个元素的下标。Ename_table(ename_table.prior(1))

EXTEND: 用于嵌套表和varray

TRIM:从集合变量尾部删除元素，用于嵌套表和varray

DELETE:删除特定元素，用于嵌套表和pl/sql表。

 

集合之间可以赋值。

嵌套表变量可以进行SET, MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT等操作。

SET: 取消嵌套表变量的重复值。

MULTISET UNION:合并两个嵌套表变量，包含重复值. Nt2:= Nt1 MULTISET UNON nt2;

MULTISET UNION DISTINCT: 取消重复值。

MULTISET INTERSECT:交集

MULTISET EXCEPT:差集

 

嵌套表变量操作：

是否为NULL: IS NULL, IS EMPTY

比较 ：　=      !=

元素个数：cardinality(nt1)

SUBMULTISET OF: IF nt1 SUBMULTISET OF nt2 THEN

MEMBER OF: 检测特定数据是否为嵌套表变量的元素　if v1 MEMBER OF nt1 THEN

IS A SET: 检测嵌套表变量是否存在具有相当数值的元素　if nt1 IS A SET THEN // TRUE代表无重复值　

 

 

 

 

 

 

参照变量

REF CURSOR

TYPE c1 IS REF CURSOR;

Dyn_cursor c1;

 

OPEN dyn_cursor FOR SELECT xxx FROM xxx;

FETCH dyn_cursor INTO xxx;

CLOSE dyn_cursor;

 

使用sql*plus变量

SQL>var name varchar2(10)

SQL>BEGIN

SELECT ename into :name FROM emp

WERE empno=&eno;

END;

/

Input eno:

:name是绑定变量，eno是sql plus。

 

 

在PL/SQL中只能直接嵌入SELECT, DML和事务控制语句，不能直接嵌入DDL和DCL。

 

常见异常：NO_DATA_FOUND, TOO_MANY_ROWS

 

SQL游标：

SQL%FOUND

SQL%ROWCOUNT

 

 

CASE语句

CASE v_sal

WHEN 10 THEN

   Xxx;

WHEN 20 THEN

  Xxx;

ELSE

  Xxx;

END CASE;

 

 

CASE

WHEN v_sal<1000 THEN

   UPDATE emp SET comm=100 WHERE ename=v_ename;

WHEN v_sal<2000 THEN

   UPDATE emp SET comm=80 WHERE ename=v_ename;

WHEN v_sal<6000 THEN

   UPDATE emp SET comm=50 WHERE ename=v_ename;

END CASE;

 

LOOP

LOOP

Xxxx;

EXIT WHEN xxx;

END LOOP;

 

WHILE

WHILE i<=10 LOOP

  xxx;

END LOOP;

 

FOR

FOR i IN REVERSE 1..10 LOOP

  xxx

END LOOP;

 

嵌套循环和标号：

<>

FOR I IN 1..100 LOOP

<>

FOR j in 1..100 LOOP

  Result := i*j;

  EXIT outer WHEN result =1000;

  EXIT WHEN result = 500;

END LOOP inner;

END LOOP outer;

 

 

GOTO语句

…

GOTO end_loop;

…

<>

…

 

批量绑定：

BULK COLLECT: SELECT, FETCH, DML返回子句

FORALL: DML

 

FORALL:

FORALL I in 1..id_table.count

INSERT INTO demo VALUES(id_table(i), name_table(i));

 

FORALL I in 1..id_table.count

UPDATE demo SET name=name_table(i) WHERE id=id_table(i);

 

FORALL I in 100..200

DELETE FROM demo WHERE id=id_table(i);

 

INDICES OF:跳过PL/SQL集合变量中的NULL元素。

FORALL I IN INDICES OF id_table

  DELETE FROM demo WHERE id=id_table(i);

 

VALUES OF:从PL/SQL集合变量中取得集合下标值。

FORALL I IN VALUES OF index_pointer

  INSERT INTO new_demo VALUES(id_table(i), name_table(i));

 

BULK COLLECT:

SELECT:

SELECT * BULK COLLECT INTO emp_table

FROM emp WHERE deptno=&no;

DML

UPDATE emp SET sal=sal*1.1 WHERE deptno=&no

RETURNING ename, sal

BULK COLLECT INTO ename_table, sal_table;

 

 

显式游标；

Emp_cursor%ISOPEN

Emp_cursor%FOUND: 游标结果集存在数据

Emp_cursor%NOTFOUND: 不存在数据

Emp_cursor%ROWCOUNT：返回已提取的实际行数

 

LOOP

FETCH emp_cursor INTO v_ename, v_deptno;

EXIT WHEN emp_cursor%NOTFOUND or emp_cursor%ROWCOUNT=5;

..

END LOOP;

 

使用显式游标：

CURSOR emp_cursor IS select XXXX;

OPEN emp_cursor;

FETCH emp_cursor INOT xxx;

CLOSE emp_cursor;

 

%ROWTYPE不仅可以基于表和视图定义记录变量，而且可以基于游标定义。

CURSOR emp_cursor IS xxxx;

Emp_record emp_cursor%ROWTYPE;

 

游标FOR循环：简化了游标使用，定义完直接使用，隐含的打开游标、提取数据并关闭游标。

1. CURSOR emp_cursor IS xxx;

FOR emp_record IN emp_cursor LOOP

  …

END LOOP;

 

2. FOR emp_record IN (xxx) LOOP

…

END LOOP;

 

参数游标

CURSOR emp_cursor(dno NUMBER) IS

SELECT ename, job FROM emp WHERE deptno=dno;

 

更新或删除游标行：

CURSOR emp_cursor IS

SELECT ename, sal, deptno FROM emp FOR UPDATE OF sal;

 

FOR emp_record IN emp_cursor LOOP

UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor;

END LOOP;

 

游标变量－动态游标：

1. 无返回值.打开游标时可以制定任何select 语句。

TYPE ref_cursor_type IS REF CURSOR;

Ref_cursor ref_cursor_type;

 

OPEN ref_cursor FOR select xxxxx;

LOOP

FETCH ref_cursor INTO xxx;

EXIT WHEN ref_cursor%NOTFOUND;

Xxx;

END LOOP;

 

CLOSE ref_cursor;

 

2. 有返回值。打开游标时select语句的返回结果必须与return子句匹配。

TYPE ref_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;

Ref_cursor ref_cursor_type;

 

OPEN ref_cursor FOR select xxxxx;

LOOP

FETCH ref_cursor INTO xxx;

EXIT WHEN ref_cursor%NOTFOUND;

Xxx;

END LOOP;

 

CLOSE ref_cursor;

 

 

批量提取

CURSOR emp_cursor IS xxx;

OPEN emp_cursor;

FETCH emp_cursor BULK COLLECT INTO emp_table;

CLOSE emp_cursor;

需要限制提取行数的时候用LIMIT

FETCH emp_cursor BULK COLLECT INTO emp_table LIMIT 5;

 

 

异常处理；

1. Oracle提供了21个预定义异常

2. 除此之外的oracle异常需要定义：

DECLARE

e_integrity EXCEPTION;

PRAGMA EXCEPTION_INIT(e_integrity,-2291);

BEGIN

Xxx;

EXCEPTION

WHEN e_integrity THEN

   Xxx;

END;

3. 自定义异常

DECLARE

e_no_rows EXCEPTION

 PRAGMA EXCEPTION_INIT(e_integrity,-2291);

BEGIN

Xxx;

IF SQL%NOTFOUND THEN

  Raise e_no_rows;

END IF;

EXCEPTION

WHEN e_no_rows THEN

  Xxx;

END;

 

异常处理函数: SQLCODE, SQLERRM

 

本地动态SQL

当在pl/sql块中嵌入DDL/DCL语句时，必须使用动态sql.

 

动态sql处理方法：

1. 使用execute immediate: DDL, DCL, DML, 单行SELECT

2. OPEN-FOR, FETCH, CLOSE，用于多行查询

3. 批量动态sql:加快处理，提高性能

 

EXECUTE IMMEDIATE:

使用execute immediate处理含占位符的DML:

DECLARE

Dml_stat VARCHAR2(100):=’INSERT INTO emp ‘ ||

  ‘(empno, ename, sal) VALUES(:no, :name, :sal)’;

BEGIN

  EXECUTE IMMEDIATE dml_stat USING &1, &2, &3;

END;

 

使用RETURNING子句：只能接受单行数据。

DECLARE

Dml_stat VARCHAR2(100):=’DELETE emp WHERE ‘

  || ‘empno=&eno RETURNING ename INTO :name’;

  V_name emp.ename%TYPE;

BEGIN

EXECUTE IMMEDIATE dml_stat RETURNING INTO v_name;

END;

 

包含占位符和RETURNING：

DECLARE

Dml_stat VARCHAR2(100):=’UPDATE emp SET sal:= :salary ‘

  || ‘WHRE empno=:eno RETURNING sal INTO :new_sal’;

  V_sal emp.sal%TYPE;

BEGIN

EXECUTE IMMEDIATE dml_stat

USING &sal, &eno

RETURNING INTO v_sal;

END;

 

单行查询：

DECLARE

Dml_stat VARCHAR2(100):=’SELECT * FROM emp ‘

  || ‘WHRE LOWER(ename)=LOWER(‘’&name’’)’;

  Emp_record emp%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE dml_stat

INTO emp_record;

END;

 

处理含占位符：

DECLARE

Dml_stat VARCHAR2(100):=’SELECT * FROM emp ‘

  || ‘WHRE empno=:eno’;

  Emp_record emp%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE dml_stat

INTO emp_record

USING &eno;

END;

 

动态SQL处理多行查询：

DECLARE

TYPE empcurtype IS REF CURSOR;

Emp_cv empcurtype;

BEGIN

Sql_stat := ‘SELECT * FROM emp WHERE deptno=:dno’;

OPEN emp_cv FOR sql_stat USING &dno;

LOOP

  FETCH emp_cv INTO emp_record;

  EXIT WHEN emp_cv%NOTFOUND;

END LOOP;

  CLOSE emp_cv;

END;

 

动态SQL使用批量绑定

1. FORALL i IN 1..ename_table.COUNT

      EXECUTE IMMEDIATE sql_stat USING ename_table(i);

2. EXECUTE IMMEDIATE sql_stat USING &dno

      RETURNING BULK COLLECT INTO ename_table, sal_table;

3. EXECUTE IMMEDIATE sql_stat  (这个很好用)

      BULK COLLECT INTO ename_table, hiredate_table USING &dno;

4. OPEN emp_cv FOR sql_stat USING &dno;

FETCH emp_cv BULK COLLECT INTO ename_table, sal_table;

CLOSE emp_cv;

(3, 4类似)

 

可以用PRAGMA RESTRICT_REFERENCES(max_sal, WNPS);限制包中的公共函数max_sal对公用变量的修改操作。

WNDS: 禁止DML

WNPS: 禁止变量赋值

RNDS:禁止select

RNPS: 禁止变量赋值给其他变量

 

 

对象

CREATE OR REPLACE TYPE employee_type AS OBJECT(

  Eno NUMBER(6), name VARCHAR2(10), salary NUMBER(6, 2),

Job VARCHAR2(10), dno NUMBER(2),

CONSTRUCTOR FUNCTION employee_type(eno NUMBER, name VARCHAR2)

  RETURN SELF AS RESULT,  构造函数，必须同名

MEMBER PROCEDURE change_sal(new_sal NUMBER),对象实例调用

MEMBER FUNCTION get_sal RETURN NUMBER,

STATIC FUNCTION get_time RETURN VARCHAR2, 对象类型调用

MAP MEMBER FUNCTION sal_sort RETURN NUMBER排序，还有一个ORDER方法，和MAP只能定义其一，是用来比较两个对象的，MAP比较多个对象

);

/

 

使用行对象

CREATE TABLE employee OF employee_type;

对象类型的属性可以直接作为普通表的表列引用

 

使用列对象

CREATE TABLE person (

Basic_info employee_type, sex varchar2(6)

);

INSERT INTO person VALUES(

 Employee_type(1112, ‘SCOTT’), ‘Male’

);

UPDATE person a SET a.basic_info.salary=2000

WHERE a.basic_info.eno=1111;

 

建立对象表时，必须为嵌套表类型提供单独的存储表。

 

继承

当定义父类型时，必须指定NOT FINAL选项，否则该类型不能被继承（默认FINAL）。

为了禁止基于该父类型定义对象实例，使用NOT INSTANTIABLE选项（默认INSTANTIABLE）。 

CREATE OR REPLACE TYPE food_type UNDER goods_type (XXXX);

 

 

参照对象类型，指向对象实例的指针。

CREATE TABLE housemaster OF hm_type;

CREATE TABLE person(

Id NUMBER(8) PRIMARY KEY, name VARCHAR2(10),

Sex CHAR(2), birthdate DATE, master REF hm_type);

 

INSERT INTO person SELECT 1, ‘XX’, ‘XX’, ‘XXX’,

  REF(a) FROM housemaster a WHERE a.name = ‘xx’;

REF列存放指向行对象数据的指针。要获得实际值，用DEREF。

SELECT deref(master).info() FROM person

Where name=’xx’;

 

 

LOB

BLOB(binary), CLOB(character), BFILE(存放指向os文件的指针)

当建立clob列时，oracle会自动建立LOB段。

CLOB列需要初始化。

 

CREATE TABLE clob_table (

Id NUMBER(4) PRIMARY KEY, name VARCHAR2(10), resume LOB

) LOB(resume) STORE AS SEGNAME (DISABLE STORAGE IN ROW);

 

INSERT INTO clob_table (id, name) VALUES(1, ‘jack’);

INSERT INTO clob_table VALUES(2, ‘mary’, ‘born in 1978’); --分配LOB定位符

INSERT INTO clob_table VALUES(3, ‘alice’, empty_clob());--分配LOB定位符

UPDATE clob_table SET resume=empty_clob() WHRE id=1; --分配LOB定位符

 

为clob列追加数据

SELECT resume INTO clob_loc FROM clob_table

WHERE  id=3 FOR UPDATE;

Offset := DBMS_LOG.GETLENGTH(clob_loc) + 1;

Amount := LENGTH(text);

DBMS_LOB.WRITE(clob_loc, amount, offset, text);

 

BFILE：

建立目录对象，并赋予用户访问相应目录对象的权限

建立包含BFILE列的表

初始化BFILE列  bfilename

将bfile内容灌入CLOB列

 

BLOB列：

Empty_blob初始化

 

 

DBMS_SQL动态SQL可以在客户端程序中使用:

DECLARE

c INTEGER;

ret INTEGER;

BEGIN

c:= dbms_sql.open_cursor;

dbms_sql.parse(c, string, DBMS_SQL.NATIVE);

ret := dbms_sql.execute(c);

dbms_sql.close_cursor(c);

END;

/

 

批量插入:

Empno_array DBMS_SQL.NUMBER_TABLE,

Ename_array DBMS_SQL.VARCHAR2_TABLE,

 

Stat := ‘INSERT INTO emp (empno, ename, sal) ‘||

   ‘VALUES(:num_array, :name_array, :salary_array)’;

C:= DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(C, stmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_ARRAY( c, ‘:num_array’, empno_array);

DBMS_SQL.BIND_ARRAY( c, ‘:name_array’, ename_array);

DBMS_SQL.BIND_ARRAY( c, ‘:salary_array’, sal_array);

Ret := DBMS_SQL.EXECUTE©;

DBMS_SQL.CLOSE_CURSOR(c );

类似的，可以批量更新和删除。

Stat := ‘UPDATE emp SET sal =:sal_array ‘ ||

 ‘WHERE empno = :num_array’;

 

批量查询：

Stmt := ‘SELECT empno, ename, sal FROM emp WHERE deptno = :no’;

C := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE( c, stmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE( c, ‘:no’, dno);

DBMS_SQL.DEFINE_ARRAY( c, 1, empno_array, 100, 1);

DBMS_SQL.DEFINE_ARRAY( c, 2, ename_array, 100, 1);

DBMS_SQL.DEFINE_ARRAY( c, 3, sal_array, 100, 1);

Ret := DBMS_SQL.EXECUTE( c);

Ret := DBMS_SQL.FETCH_ROW( c );

DBMS_SQL.COLUMN_VALUE( c, 1, empno_array);

DBMS_SQL.COLUMN_VALUE ( c, 2, ename_array);

DBMS_SQL.COLUMN_VALUE ( c, 3, sal_array);

DBMS_SQL.CLOSE_CURSOR( c );

 

 

 

 

 

 

 

 

 

 

 

 

 

范式：

第一范式（1NF，台湾译作第一正规化）是数据库规范化中所使用的一种正规形式。第一范式是为了要排除 重复组 的出现，所采用的方法是要求数据库的每个字段都只能存放单一值，而且每笔记录都要能利用一个惟一的主键来加以识别。

 

第二范式（2NF，台湾译作第二正规化）是数据库规范化中所使用的一种正规形式。它的规则是要求数据表里的所有数据都要和该数据表的主键有完全相依关系；如果有哪些数据只和主键的一部份有关的话，就得把它们独立出来变成另一个数据表。如果一个数据表的主键只有单一一个字段的话，它就一定符合第二范式。

 

一个数据表符合第二范式当且仅当

１．它符合第一范式

２．所有非主键的字段都一定和主键有关

 

第三范式（3NF，台湾译作第三正规化）是数据库规范化中所使用的一种正规形式，用来检验是否所有非键属性都只和候选键有相关性，也就是说所有非键属性互相之间应该是无关的。

 

第三范式和第二范式不同的地方在于，在第三范式里，所有的非键属性都必须和每个候选键有直接相关。如果再对第三范式做进一步加强就成了BC范式，它所强调的重点就在于 "数据间的关系是奠基在键上、以整个键为考量、而且除了键之外不考虑其他因素"。

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11903161/viewspace-683076/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11903161/viewspace-683076/