PLSQL

select deptno,

ename,

sal,

sum(sal) over (partition by deptno order by sal, ename) cum_sal,

round(100*ratio_to_report(sal) over (partition by deptno), 1) pct_dept,

round(100*ratio_to_report(sal) over (), 1) pct_over_all

from emp order by deptno, sal;

 

 

 

 

 

DECLARE

v_sal EMP.SAL%TYPE;

BEGIN

SELECT SAL INTO V_SAL FROM EMP WHERE EMP.EMPNO=7369;

CASE

        WHEN V_SAL BETWEEN 500 and 900 THEN DBMS_OUTPUT.PUT_LINE('大于500，小于900');

        WHEN V_SAL BETWEEN 900 and 1500 THEN DBMS_OUTPUT.PUT_LINE('大于900，小于1500');

     ELSE DBMS_OUTPUT.PUT_LINE('其他');

END CASE;

END;

 

 

SELECT DECODE(SAL,800,800*1.1,900,900*1.1,1000*1.1)

FROM EMP

WHERE EMP.EMPNO=7369;

 

 

merge into products p using newproducts np on (p.product_id = np.product_id)

when matched then

update set p.product_name = np.product_name

when not matched then

insert values(np.product_id, np.product_name, np.category)

 

注意ROUND是四舍五入，TRUNC是截取

 

 

 

数字有三种基本类型：

NUMBER可以描述整数或实数

PLS_INTEGER和BINARY_INTEGER只能描述整数

PLS_INTEGER和BINARY_INTEGER唯一区别：

在计算当中发生溢出时，BINARY_INTEGER型的变量会被自动指派给一个NUMBER型而不会出错，PLS_INTEGER型的变量将会发生错误

 

 

集合类型

单列多行数据，使用集合

pl/sql集合类型包括关联数组Associative array（索引表 pl/sql table）、嵌套表（Nested Table）、变长数组（VARRAY）。

Nested table与VARRY既可以被用于PL/SQL，也可以被直接用于数据库中，但是Associative array不行

Associative array是不能通过CREATE TYPE语句进行单独创建，只能在PL/SQL块（或Package）中进行定义并使用（即适用范围是PL/SQL Block级别）

Nested table与VARRAY则可以使用CREATE TYPE进行创建（即适用范围是Schema级别），它们还可以直接作为数据库表中列的类型

Associative array

DECLARE

TYPE va_planguage IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;

lang va_planguage;

idx PLS_INTEGER;

BEGIN

lang(1):='java';

lang(9):='C#';

lang(3):='C++';

idx:=lang.FIRST;

WHILE(idx IS NOT NULL) LOOP

    DBMS_OUTPUT.PUT_LINE(lang(idx));

    idx:=lang.NEXT(idx);

END LOOP;

END;

 

与Associative array不同，Nested table变量需要显式初始化。Nested table初始化之后还需要调用EXTEND过程，扩展集合的"容量"

DECLARE

TYPE nt_planguage IS TABLE OF VARCHAR2(10);

lang nt_planguage;

BEGIN

lang:=nt_planguage('java','C#','C++');

    FOR i IN 1..lang.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE(lang(i));

    END LOOP;

END;

 

 

 

VARRAY可以在声明时限制集合的长度。其索引总是连续的，而Nested table的索引在初始化赋值时是连续的，不过随着集合元素被删除，可能变得不连续

如果你需要允许使用负数索引，应该选择Associative array；

如果你需要限制集合元素的个数，应该选择VARRAY

 

 

 

 

 

 

用户自定义类型

常用内置函数

函数 说明 转换前的类型

TO_CHAR 转换成VARCHAR类型 数字型、日期型

TO_DATE 转换成DATE 字符型

TO_NUMBER 转换成number类型 字符型

 

IF语句

    IF V_SAL<500 THEN

        UPDATE EMP SET SAL=SAL*1.5 WHERE EMPNO=7788;

    ELSIF V_SAL<1500 THEN

        UPDATE EMP SET SAL=SAL*1.3 WHERE EMPNO=7788;

    ELSE

        UPDATE EMP SET SAL=SAL*1.0 WHERE EMPNO=7788;

    END IF;

 

 

 

 

 

循环语句

LOOP循环

DECLARE

    V_COUNTER NUMBER:=1;

BEGIN

    LOOP

        INSERT INTO TEMP_TABLE VALUES(V_COUNTER,'loop index');

        V_COUNTER:=V_COUNTER+1;

        IF V_COUNTER>50 THEN

        EXIT;

        END IF;

    END LOOP;

END;

 

WHILE循环

DECLARE

    V_COUNTER NUMBER:=1;

BEGIN

    WHILE V_COUNTER <=50 LOOP

        INSERT INTO TEMP_TABLE VALUES(V_COUNTER,'loop index');

        V_COUNTER:=V_COUNTER+1;

    END LOOP;

END;

 

 

 

 

 

FOR语句

DECLARE

    V_COUNTER NUMBER;

BEGIN

    FOR V_COUNTER IN 1..50 LOOP

        INSERT INTO TEMP_TABLE VALUES(V_COUNTER,'loop index');

    END LOOP;

END;

FOR v_counter IN REVERSE 1..50 LOOP

其中：IN：表示索引变量的值小到大 IN REVERSE：表示索引变量的值从大到小

 

 

GOTO语句

DECLARE

    V_VONTER NUMBER:=1;

BEGIN

    LOOP

        INSERT INTO TEMP_TABLE VALUES(V_VONTER,'loop count');

        V_VONTER:=V_VONTER+1;

        IF V_VONTER > 50 THEN

                GOTO ENDOFLOOP;

        END IF;

    END LOOP;

    <<ENDOFLOOP>>

        INSERT INTO TEMP_TABLE VALUES(-1,'done!');

END;

对于块、循环或IF语句而言，想要从外层跳到内层是非法的

从一个IF子句调转到IF的另一个子句中是非法的

出错处理块不能实行跳转

 

异常处理

系统异常

异常代码 异常名称 说明

ORA-01403 NO_DATA_FOUND 查询没有返回数据

ORA-01422 TOO_MANY_ROWS SELECT….INTO 语句返回多行结果

DECLARE

    V_COMM EMP.COMM%TYPE;

BEGIN

    SELECT COMM INTO V_COMM FROM EMP WHERE EMPNO=7839;

EXCEPTION

    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('no data!');

    WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('too many row');

    WHEN OTHERS THEN null;

END;

 

 

用户自定义异常

三个步骤：定义错误，触发错误，处理错误

DECLARE

    e_toomanystudent EXCEPTION;/*定义错误*/

    v_currentstudent NUMBER(3);

    v_maxstudent NUMBER;

    v_errortext VARCHAR2(200);

BEGIN

    SELECT current_student,max_student INTO v_currentstudent,v_maxstudent

    FROM classes WHERE department='HIS' AND course=101;

    IF v_currentstudent>v_maxstudent THEN

        RAISE e_toomanystudent; /*触发错误*/

    END IF;

EXCEPTION

    WHEN no_data_found OR too_many_rows THEN

        DBMS_OUTPUT.PUT_LINE('发生系统预定义错误');

    WHEN e_toomanystudent THEN /*处理错误*/

        INSERT INTO log_table(info) VALUES('history 101 has'|| v_currentstudent);

    WHEN others THEN

        v_errortext=sqlcode;

        v_errortext=substr(sqlerrm,1,200);

        INSERT INTO log_table(code,message,info)

VALUES(v_errortext,v_errortext,'Oracle error occured');

END;

 

 

 

游标

游标有两种类型：显示游标和隐式游标

显示游标：是由程序员定义和命名的，并且在块的执行部分中通过特定语句操作的内存工作区。

隐式游标：是由PL/SQL为DML语句和SELECT语句隐式定义的工作区

游标是一个不含INTO子句的SELECT语句

SELECT语句允许带WHERE，ORDER BY，GROUP BY等子句。

游标的定义

显示游标的处理步骤

显示游标的一些属性

%ISOPEN

%NOTFOUND

%FOUND

%ROWCOUNT

%ROWCOUNT Number 值是当前为止返回的记录数，初值为0，每取一条记录，该属性值加1。

DECLARE

v_deptno emp.deptno%type;

v_ename emp.ename%type;

v_sal emp.sal%type;

CURSOR emp_cursor IS SELECT ename ,sal FROM emp;/*定义游标*/

BEGIN

OPEN emp_cursor;/*打开游标*/

LOOP

FETCH emp_cursor INTO v_ename , v_sal;/*将当前行结果提取到变量中*/

        DBMS_OUTPUT.PUT_LINE(v_ename||' '||v_sal);

EXIT WHEN emp_cursor%NOTFOUND;/*判断数据行是否读取完*/

INSERT INTO temp VALUES(v_ename,v_sal);

END LOOP;

CLOSE emp_cursor;/*关闭游标*/

END;

/

Pl/sql语言中的&

V_deptno emp.deptno%TYPE :=&p_deptno;

Oracle中将带&的定义为变量，用户输入值后才能查询.
select * from table where id = &insertid
&insertid就是变量
打开Oracle的PL/SQL网页,
输入select * from table where id = &insertid
会出现一个文本框，要输入的值就是语句里面带&号的变量

 

 

游标的FOR循环

使用游标的FOR循环，可以简化游标的操作步骤

游标的FOR循环隐式(自动)地完成三个步骤：打开游标；(FETCT)取数据；关闭游标

记录名师系统隐式定义的游标名%ROWTYPE类型的记录变量，不必事先定义

DECLARE

v_deptno EMP.deptno%TYPE:=&p_deptno;

CURSOR emp_cursor IS SELECT ename,sal FROM EMP

WHERE deptno=v_deptno;

BEGIN

    FOR emp_record IN emp_cursor LOOP

        INSERT INTO temp(ename,sal) VALUES(emp_record.ename,emp_record.sal);

    END LOOP;

    COMMIT;

END;

/

 

要操纵数据库中数据，在定义游标的查询语句时，必须加上FOR UPDATE OF从句，表示要对表加锁。

表加锁后，在UPDATE或DELETE语句中，加WHERE CURRENT OF子句，既可以对锁定的数据进行修改

要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个Oracle游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进 行UPDATE、DELETE或SELECT...FOR UPDATE操作。

使用of子句在特定表加行共享锁。

在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
使用nowait子句
使用for update语句对被作用于行加锁，如果其他会话已经在被作用于行上加锁，那么默认情况下当前会话要一直等待对方释放锁，通过在for update子句中指定 nowait语句，可以避免等待锁，当指定了nowait子句之后，如果其他会话已经在被作用行加锁，那么当前会话会显示错误提示信息，并退出。

CURSOR 游标名IS

SELECT 列1，列2 … FORM 表 WHERE 条件

FOR UPDATE [OF column][NOWAIT];

带WHERE CURRENT OF　从句的UPDATE语句和DELETE语句

DELETE FROM 表 WHERE CURRENT OF 游标名;

UPDATE 表 SET 列1=值1，列2=值2... WHERE CURRENT OF 游标名；

 

 

 

Eg:查询emp表某部门的雇员情况，如果雇员的工资小于800，则将其工资改为800

DECLARE

DECLARE

v_deptno EMP.deptno%TYPE:=30;

v_empno EMP.empno%TYPE;

v_job EMP.job%TYPE;

v_sal EMP.SAL%TYPE;

CURSOR emp_cursor IS SELECT ename,job,sal FROM EMP

WHERE deptno=v_deptno FOR UPDATE OF sal;

BEGIN

    FOR emp_record IN emp_cursor LOOP

        IF emp_record.sal<800 THEN

            UPDATE EMP SET sal=800 WHERE CURRENT OF emp_cursor;

        END IF;

    DBMS_OUTPUT.PUT_LINE(emp_record.ename||emp_record.sal);

    END LOOP;

    COMMIT;

END;

/

删除

DECLARE

v_deptno EMP.deptno%TYPE:=30;

v_empno EMP.empno%TYPE;

v_job EMP.job%TYPE;

v_sal EMP.SAL%TYPE;

CURSOR emp_cursor IS SELECT ename,job,sal FROM EMP

WHERE deptno=v_deptno FOR UPDATE OF sal;

BEGIN

    FOR emp_record IN emp_cursor LOOP

        IF emp_record.sal<800 THEN

            DELETE FROM EMP WHERE CURRENT OF emp_cursor;

        END IF;

    DBMS_OUTPUT.PUT_LINE(emp_record.ename||emp_record.sal);

    END LOOP;

    COMMIT;

END;

/

 

 

 

 

 

 

 

Eg：为职工增加10％的工资，从最低工资开始长，增加后工资总额限制在50万以内

DECLARE

emp_num number:=0;

s_sal EMP.sal%TYPE;

e_sal EMP.sal%TYPE;

e_empno EMP.empno%TYPE;

CURSOR c1 IS SELECT empno,sal FROM emp

ORDER BY sal FOR UPDATE OF sal;

BEGIN

    OPEN c1;

    SELECT sum(sal) INTO s_sal FROM emp;

    LOOP

        FETCH c1 INTO e_empno,e_sal;

        EXIT WHEN c1%NOTFOUND;

        s_sal:=s_sal+e_sal*0.1;

        IF s_sal>=500000 THEN

        EXIT;

        END IF;

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

        emp_num:=emp_num+1;

    END LOOP;

    CLOSE c1;

    INSERT INTO msg VALUES(emp_num,s_sal);

    COMMIT;

END;

/

 

 

带参数的游标

此参数只能在游标的查询语句中使用，只能向游标传递参数数值，不能通过参数带出结果

，可以有选择地给参数提供一个默认值。

 

Eg：首先查询DEPT表取出所有的部门号，然后根据DEPT表中返回的每一个部门号，从emp表查询该部门的雇员信息

DECLARE

CURSOR dept_cursor IS SELECT deptno FROM dept;

v_ename EMP.ename%TYPE;

v_sal EMP.sal%TYPE;

CURSOR emp_cursor(v_deptno NUMBER) IS

SELECT ename,sal FROM EMP WHERE deptno=v_deptno;

BEGIN

    FOR dept_record IN dept_cursor LOOP

        EXIT WHEN dept_cursor%NOTFOUND;

        OPEN emp_cursor(dept_record.deptno);

        LOOP

            FETCH emp_cursor INTO v_ename,v_sal;

            EXIT WHEN emp_cursor%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE(dept_record.deptno||''||''||v_sal);

        END LOOP;

        CLOSE emp_cursor;

    END LOOP;

    COMMIT;

END;

/

隐式游标

由系统定义，不需要用户定义

用来处理INSERT，UPDATE，DELETE和单行的SELECT…INTO语句

游标名为SQL

游标的属性存储有关最近一次SQL命令的状态信息

隐式游标的游标属性%ISOPEN总是FALSE，因为当语句执行完后立即关闭隐式游标

SELEC….INTO语句只能执一行

 

 

存储过程

CREATE [OR REPLACE] PROCEDURE 过程名

[(参数名 [ IN | OUT | IN OUT ] 数据类型,... )]

{IS | AS}

            [说明部分]

BEGIN

            语句序列

    [EXCEPTION 出错处理]

END [过程名];

OR REPLACE 是一个可选的关键字，表示替代原有的过程

IS或AS后面是一个完整的PL/SQL块的三部分(说明部分，执行部分，异常处理部分)

IN表示输入变量，OUT表示输出变量，IN OUT表示输入输出变量，缺省表示IN

 

 

 

 

 

 

 

 

CREATE OR REPLACE

PROCEDURE modetest(p_inpara IN NUMBER,

                 p_outpara OUT NUMBER,

                 p_inoutpara IN OUT NUMBER)

AS

v_local NUMBER;

BEGIN

    v_local:=p_inpara;/*IN类型参数不能出现在:=的左边*/

p_outpara:=7;/*OUT类型参数不能出现在:=的右边*/

    v_local:=p_inoutpara;

    p_inoutpara:=7;

END;

CREATE OR REPLACE

PROCEDURE raise_salary(emp_id INTEGER,v_increase INTEGER)

AS /* 缺省IN */

BEGIN

     UPDATE EMP SET sal=sal+v_increase WHERE empno=emp_id;

     COMMIT;

END;

 

 

函数

参数都是IN类型，存储函数必须返回并且只返回一个结果,函数体的可执行部分必须有RETURN语句(RETURN 表达式).表达式的数值类型与RETURN子句定义要一致

 

CREATE OR REPLACE

FUNCTION get_sal(p_emp_no IN EMP.empno%TYPE)

RETURN NUMBER

AS

    v_emp_sal EMP.sal%TYPE:=0;

BEGIN

     SELECT sal INTO v_emp_sal FROM emp WHERE empno=p_emp_no;

     RETURN(v_emp_sal);

EXCEPTION

     WHEN no_data_found or too_many_rows THEN

             DBMS_OUTPUT.PUT_LINE('System Error');

     WHEN others THEN

                DBMS_OUTPUT.PUT_LINE(sqlerrm);

END;

 

CREATE OR REPLACE

FUNCTION average_sal(v_n IN NUMBER)

RETURN NUMBER

AS

CURSOR c_emp IS SELECT empno,sal FROM emp;

v_total_sal EMP.sal%TYPE:=0;

v_counter NUMBER;

v_emp_no EMP.EMPNO%TYPE;

BEGIN

    FOR r_emp IN c_emp LOOP

        EXIT WHEN c_emp%ROWCOUNT>v_n OR c_emp%NOTFOUND;

        v_total_sal:=v_total_sal+r_emp.sal;

        v_counter:=c_emp%ROWCOUNT;

        v_emp_no:=r_emp.empno;

        DBMS_OUTPUT.PUT_LINE('loop='||v_counter||';empno='||v_emp_no);

    END LOOP;

        DBMS_OUTPUT.PUT_LINE(v_total_sal);

    RETURN(v_total_sal/v_counter);

END average_sal;

 

 

过程和函数

参数类型不同：函数只有IN类型参数，而存储有IN，OUT，IN OUT三个类型参数

返回值的方法不同:函数返回只有一个值，而存储过程返回值由OUT参数带出来

调用方法不同：

过程(实际参数1，实际参数2……);

变量名:=函数名(实际参数1，实际参数2….);

 

过程/函数中的异常处理

CREATE OR REPLACE

PROCEDURE fire_emp(p_emp_no IN EMP.EMPNO%TYPE)

AS

invalid_employee EXCEPTION;/*定义错误*/

BEGIN

    DELETE FROM emp WHERE empno=p_emp_no;

    IF SQL%NOTFOUND THEN

        RAISE invalid_employee;/*触发错误*/

    END IF;

    EXCEPTION

        WHEN invalid_employee THEN

            ROLLBACK;

        WHEN others THEN

            DBMS_OUTPUT.PUT_LINE(sqlerrm);

END fire_emp;

 

 

在SQL*Plus中使用过程/函数

在SQL*Plus中，用VARIABLE定义的变量在引用时，必须前面加冒号(:)。用ACCEPT接受的变量在引用时，前面加&符号。

SET SERVEROUTPUT ON

ACCEPT p_emp_no PROMPT 'please enter the employee number:'

VARIABLE v_emp_name varchar2(14);

EXECUTE query_emp(&p_emp_no,:v_emp_name);

 

 

包

包是一个可以将相关对象存储在一起的PL/SQL结构

它包含了两个分离的组成部分:包说明和包主体

包的组成

可以将相关的若干程序单元组织到一块，用一个包名来标识这个集合

包中可以包含的程序单元

过程，函数，变量，游标，类型，常量，出错情况

包头

CREATE [OR REPLACE] PACKAGE 包名

｛IS | AS｝

公共变量的定义        |　

公共类型的定义        |

公共出错处理的定义    |

公共游标的定义        |

函数说明            |

过程说明　　　　　　

END;

/

包体

CREATE [OR REPLACE] PACKAGE BODY 包名

｛IS | AS｝

私有变量的定义        |　

私有类型的定义        |

私有出错处理的定义    |

私有游标的定义        |

函数定义            |

过程定义　　　　　　

END ;

/

CREATE OR REPLACE

PACKAGE SAL_PACKAGE AS

    PROCEDURE RAISE_SAL(V_EMPNO IN NUMBER, V_SAL_INCREMENT IN NUMBER);

    PROCEDURE REDUCE_SAL(V_EMPNO IN NUMBER, V_SAL_REDUCE IN NUMBER);

    v_raise_sal EMP.sal%TYPE:=0;

    v_reduce_sal EMP.sal%TYPE:=0;

end;

CREATE OR REPLACE

PACKAGE BODY SAL_PACKAGE AS

PROCEDURE RAISE_SAL(V_EMPNO IN NUMBER, V_SAL_INCREMENT IN NUMBER) AS

BEGIN

        UPDATE EMP SET EMP.SAL=SAL+V_SAL_INCREMENT WHERE EMP.EMPNO=V_EMPNO;

        COMMIT WORK;

        v_raise_sal:=v_raise_sal+v_sal_increment;

END;

 

PROCEDURE REDUCE_SAL(V_EMPNO IN NUMBER, V_SAL_REDUCE IN NUMBER) AS

BEGIN

        UPDATE emp SET sal=sal-v_sal_reduce WHERE empno=v_empno;

        COMMIT WORK;

        v_reduce_sal:=v_reduce_sal+v_sal_reduce;

END;

END;

所有提供的Package由SYS所拥有，对于不是SYS的用户，必须拥有EXCEUTE权限才能调用

所有Oracle提供的程序包都是以DBMS_或UTL_开头

 

 

静态SQL
静态SQL指直接嵌入在PL/SQL块中的SQL语句，静态SQL用于完成特定或固定的任务。
select sal from emp where empno=4000;

 

动态SQL
动态SQL运行PL/SQL块时动态输入的SQL语句。如果在PL/SQL需要执行DDL语句，DCL语句，或则需要执行更加灵活的SQL语句(select中有不同where条件)，需要用到用到动态SQL。
编写动态SQL语句时，需要将SQL语句存放到字符串变量中，而且SQL语句可以包含占位符(以冒号开始)。
v_sql varchar2(100);
v_sql:='delete from emp where empno =:v_empno';

 

存储过程里面， 一般只写 DML的语句。
也就是基本的 SELECT . INSERT, DELETE, UPDATE 这一类的语句。
如果你要在存储过程里面， 执行 DDL 语句。
也就是 CREATE, ALTER, DROP 这一类的语句。
那么需要使用动态 SQL 来处理。
也就是
EXECUTE IMMEDIATE ( 'CREATE TABLE test( id int ) ' );
EXECUTE IMMEDIATE ( 'DROP TABLE test ' );
这样的方式来处理。
对于 DDL 语句， 这类语句， 如果成功了， 就生效了， 不能回滚的。
也就是
当你 Drop Table 某个表的时候， 执行成功了， 表就被删除了。
不需要你 Commit 的。
你也无法　　Rollback ， 然这个表不删除了。

简单来说 就是你一个存储过程当中 创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，所以无法编译成功，而把insert into语句加如到 execute immediate之后 则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行。

 

 

 

execute immediate

删除表

CREATE OR REPLACE

PROCEDURE PRO_DROP_TABLE(v_table_name VARCHAR2) AS

v_sql VARCHAR2(100);

BEGIN

    v_sql:='drop table '||v_table_name;

    execute immediate v_sql;

END;

 

BEGIN

FOR I IN 1 .. 100 LOOP

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE T'||I;

EXCEPTION

WHEN OTHERS THEN NULL;

END;

EXECUTE IMMEDIATE 'CREATE TABLE T'||I||' ( DUMMY CHAR(1) )';

EXECUTE IMMEDIATE 'INSERT INTO T'||I||' VALUES ( ''X'' )';

END LOOP;

END;

/

 

 

dbms_utility的使用

将字符串每个逗号字符用方括号进行封装

dbms_utility.comma_to_table的使用

 

 

 

 

DECLARE

t_varrarray DBMS_UTILITY.LNAME_ARRAY;

vc_stringlist VARCHAR2(4000);

n_idx binary_integer;

BEGIN

    --comma to TABLE

    vc_stringlist:='dkf,dddl,fewe,klkj';

    DBMS_UTILITY.COMMA_TO_TABLE(vc_stringlist,n_idx,t_varrarray);

    DBMS_OUTPUT.PUT_LINE('TOTAL NUM: '||TO_CHAR(n_idx));

    FOR i IN 1..n_idx LOOP

        DBMS_OUTPUT.PUT_LINE(t_varrarray(i));

        t_varrarray(i):='['||t_varrarray(i)||']';

    END LOOP;

    DBMS_UTILITY.TABLE_TO_COMMA(t_varrarray,n_idx,vc_stringlist);

    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE(vc_stringlist);

END;

/

 

DECLARE

l_temp NUMBER;

BEGIN

    l_temp:=DBMS_UTILITY.get_time;

    DBMS_OUTPUT.PUT_LINE('before='||l_temp);

    DBMS_LOCK.SLEEP(5);

    l_temp:=DBMS_UTILITY.get_time;

    DBMS_OUTPUT.PUT_LINE('after='||l_temp);

END;

/

触发器

触发器由说明部分，语句执行部分和出错处理部分三部分组成的PL/SQL有名块(类似于存储过程和函数)

触发器不接受参数，不能再程序中调用

当触发事件发生时隐式地(自动地)执行

触发事件包括：在数据库中执行如下操作

INSERT

UPDATE

DELETE

 

将超过某工种工资范围的员工信息纪录到aduit_message表中。

sal_guide表纪录了每一工种的工资范围

CREATE OR REPLACE TRIGGER check_sal

BEFORE INSERT OR UPDATE OF sal,job ON emp FOR EACH ROW

WHEN (new.job <> 'PRESIDENT')

DECLAER

v_minsal sal_guide.minsal%TYPE;

v_maxsal sal_guide.maxsal%TYPE;

e_sal_out_of_range EXCEPTION;

BEGIN

SELECT minsal , maxsal INTO v_minsal , v_maxsal

FROM sal_guide WHERE job = :new.job;

IF (:new.sal < v_minsal) OR (:new.sal > v_maxsal) THEN

RAISE e_sal_out_of_range;

END IF;

EXCEPTION

WHEN e_sal_out_range THEN

INSERT INTO audit_message(line_nr,line)

VALUES(1,'salary'||to_char(:new.sal)||

'is out of range for employee'||to_char(:new.empno));

END ;

 

 

 

 

 

 

 

触发器类型：语句级和行级

触发事件：表的插入，更新，删除

触发时间：BEFORE和AFTER

触发器分语句级和行级触发器两个级别

行级触发器与语句级触发器的区别主要在于触发次数不同

如果DML语句只影响一行，则语句级和行级触发器效果一样

如果该DML语句影响多行，则行级触发器触发的次数比语句级触发器触发的次数多

语句之前触发->行之前触发->插入、更新、删除一条记录->行之后出发->语句之后触发

在触发器体内禁止使用COMMIT，ROLLBACK语句

事件指明触发事件的数据操纵语句，有三种可能的值：INSERT，UPDATE，UPDATE OF 列名1，DELETE

该触发器在一个数据操作语句发生时只触发一次

CREATE [OR REPLACE] TRIGGER 触发器名

{BEFORE | AFTER} 事件1 [OR 事件2 ...] ON 表名

PL/SQL 块；

 

 

Eg：创建一个BEFORE型语句级触发器。限制一周内向emp表插入数据的时间，如果是周六、周日，或晚上6点到第二天早上8点之间插入，则中断操作，并提示用户不允许在此时间向emp表插入

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT ON EMP

BEGIN

    IF(TO_CHAR(sysdate,'DY') IN ('SAT','SUN')

OR (TO_CHAR(sysdate,'HH24') NOT BETWEEN '8' AND '18'))

    THEN RAISE_APPLICATION_ERROR(-20500,'you may only insert emp during normal hours.');

    END IF;

END;

/

 

 

使用触发谓词(INSERTING,UPDATING,DELETING)

触发器可以包含多个触发事件，在触发器中使用谓词判断是哪个触发了触发器

谓词 行为和值

INSERTING            如果触发事件是INSERTING，则谓词的值为TRUE，否则为FALSE

 

 

 

CREATE OR REPLACE TRIGGER secure_emp

BEFORE DELETE OR INSERT OR UPDATE ON EMP

BEGIN

    IF(TO_CHAR(sysdate,'DY') IN ('SAT','SUN') OR

        (TO_CHAR(sysdate,'HH24') NOT BETWEEN '8' AND '18')) THEN

        IF DELETING THEN

            RAISE_APPLICATION_ERROR(-20502,'you may only delete emp during normal hourse');

        ELSE INSERTING THEN

            RAISE_APPLICATION_ERROR(-20502,'you may only insert emp during normal hourse');

        ELSE THEN

            RAISE_APPLICATION_ERROR(-20504,'you may only update emp during normal hours.');

        END IF;

END IF;

END;

 

通过在CREATE TRIGGER 语句中指定FOR EACH ROW 子句创建一个行触发器

CREATE [OR REPLACE] TRIGGER 触发器名

{BEFORE | AFTER} 事件1 [OR 事件2 ...] ON 表名

FOR EACH ROW [WHEN 限制条件]

PL/SQL 块；

 

Eg：将每个用户对数据库emp表进行数据操纵（插入、更新、删除）的次数纪录到audit_table表中

CREATE OR REPLACE TRIGGER audit_emp

AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW

BEGIN

IF DELETING THEN

UPDATE audit_table SET del = del + 1

WHERE user_name = user AND table_name = 'emp'

AND colun_name IS NULL;

ELSIF INSERTING THEN

UPDATE audit_table SET ins = ins + 1

WHERE user_name = user AND table_name = 'emp'

AND colun_name IS NULL;

ELSE THEN

UPDATE audit_table SET upd = upd + 1

WHERE user_name = user AND table_name = 'emp'

AND colun_name IS NULL;

END IF;

END;     

 

使用行级触发器的标识符（:OLD和:NEW）

在行级触发器中，列名前加上:OLD标识符表示该列变化前的值，加上:NEW标识符表示变化后的值

在BEFORE型行级触发器和AFTER型行级触发器中使用这些标识符

在语句级触发器中不要使用这些标识符

在触发器体的SQL语句或PL/SQL语句中使用这些标识符时，前加冒号(:)

在行级触发器的WHEN限制条件中使用这些标识符时，前面不要加冒号(:)

 

Eg:在行级触发器中获取某列的新值和旧值，为emp表中的所有数据保留一个历史档案

 

 

 

CREATE OR REPLACE TRIGGER audit_emp_values

AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW

BEGIN

    INSERT INTO audit_emp(user_name,timestamp,empno,old_ename,

new_ename,old_job,new_job,old_sal,new_sal)

    VALUES(USER,SYSDATE,:old.empno,:old.ename,:new.ename,:old.job,

:new.job,:old.sal,:new.sal);

END;

 

Eg：在行级触发器加WHEN限制条件。根据销售员工资的改变自动计算销售员的奖金

 

CREATE OR REPLACE TRIGGER derive_comm

BEFORE UPDATE OF sal ON emp FOR EACH ROW

WHEN (new.job = 'SALESMAN')

BEGIN

:new.comm := :old.comm * (:new.sal/:old.sal);

END;        

 

 

 

 

INSTEAD OF 触发器

instead of trigger 是基于视图建立的,不能建在表上,为什么要建在视图上,一般的视图如果其数据来源一个表并且包含该表的主键,就可以对视图进行DML操作.另外一种情况是 从多个表查询出来的.这样我们就不能对视图进行操作了,也就是只能查询.instead of trigger可以解决建在多表上视图的更新操作.

 

Create Or Replace Trigger Trg_InsUpdDel_t
Instead Of Insert or update or delete on t for each row
begin
    If Inserting Then
        Insert Into t1 (t11,t12) Values (:New.f1,:New.f2);
        Insert Into t2 (t11,t22) Values (:New.f1,:New.f3);
    elsif Updating Then
        Update t1 set t11=:New.f1,t12=:New.f2 where t11=:New.f1;
        Update t2 set t11=:New.f1,t22=:New.f3 where t11=:New.f1;
    elsif Deleting then
        Delete from t1 where t11=:Old.f1;
        Delete from t2 where t11=:Old.f1;
    End if;
end;

 

 

 

BULK DML

Oracle引入了两个DML语句: BUKL COLLECT 和 FORALL

这两个语句在PL/SQL内部进行以一种数据处理

BUKL COLLECT 提供对数据的高速检索

FORALL 可大大改进INSERT，UPDATE和DELETE操作的性能

Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数，从而使其性能有了显著提高

DECLARE

    TYPE books_aat IS TABLE OF book%ROWTYPE

        INDEX BY PLS_INTEGER;

    my_books books_aat;

BEGIN

    SELECT * BULK COLLECT INTO my_books

    FROM book WHERE title LIKE '%PL/SQL%';

    ...

END;

 

CREATE TYPE books_nt
IS TABLE OF book%ROWTYPE;

CREATE OR REPLACE PROCEDURE add_books ( books_in IN books_nt)
IS
BEGIN
    FORALL book_index
        IN books_in.FIRST .. books_in.LAST
    INSERT INTO book VALUES books_in(book_index);
    ...
END;

什么时候使用FORALL

FORALL可以大量的提高从源表或者视图到目标表的插入，更新，删除，合并操作

 

如果FORALL的调用集合中缺失一个元素那么错误将会发生

如果一个行级别的发生那么整个进程将会停止

DECLARE

Type region_id_tbl IS TABLE of NUMBER INDEX BY BINARY_INTEGER;

Type region_name_tbl IS TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;

region_ids region_id_tbl;

region_names region_name_tbl;

ret_code NUMBER;

ret_errmsg VARCHAR2(1000);

Procedure load_regions_bulk_bind (region_ids IN region_id_tbl,

region_names IN region_name_tbl, retcd OUT NUMBER, errmsg OUT VARCHAR2)

IS

BEGIN

-- clean up the region_tab table initially.

DELETE FROM region_tab;

FORALL i IN region_ids.FIRST..region_ids.LAST

INSERT INTO region_tab values (region_ids(i), region_names(i));

Retcd := 0;

EXCEPTION

WHEN OTHERS THEN

COMMIT;

Retcd := SQLCODE;

Errmsg := SQLERRM;

END;

BEGIN

FOR i IN 1..5 LOOP

Region_ids(i) := i;

Region_names(i) := 'REGION'||i;

END LOOP;

Load_regions_bulk_bind(region_ids, region_names, ret_code, ret_errmsg);

EXCEPTION WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20112, SQLERRM);

END;

 

DECLARE

t_outtab_type IS TABLE OF input_table%ROWTYPE;

v_outtab t_outtab_type;

BEGIN

SELECT * BULK COLLECT INTO v_outtab

FROM input_table

WHERE key_col = '<val1>';

 

FOR i IN 1..v_outtab.COUNT LOOP

-- Process the rows one by one according to required logic.

END LOOP;

END;

/

 

DECLARE

t_outtab_type IS TABLE OF input_table%ROWTYPE;

v_outtab t_outtab_type;

CURSOR c_in IS SELECT * FROM input_table

WHERE key_col = '<val1>';

BEGIN

OPEN c_in;

FETCH c_in BULK COLLECT INTO v_outtab;

FOR i IN 1..v_outtab.COUNT LOOP

-- Process the rows one by one according to required logic.

END LOOP;

 

CLOSE c_in;

END;

/

DECLARE

t_outtab_type IS TABLE OF input_table%ROWTYPE;

v_outtab t_outtab_type;

CURSOR c_in IS SELECT * FROM input_table

WHERE key_col = '<val1>';

BEGIN

OPEN c_in;

FETCH c_in BULK COLLECT INTO v_outtab;

FOR i IN 1..v_outtab.COUNT LOOP

-- Process the rows one by one according to required logic.

END LOOP;

 

CLOSE c_in;

END;

/

 

 

用bulk query的主要有点，

减少运行的时间，更少的延时

如果取得的数据很大的话，那么回增加内存的消耗

 

可以通过特定的limite数量来控制取得的数量

DECLARE

t_outtab_type IS TABLE OF input_table%ROWTYPE;

v_outtab t_outtab_type;

CURSOR c_in IS SELECT * FROM input_table WHERE key_col = '<val1>';

BEGIN

OPEN c_in;

LOOP

FETCH c_in BULK COLLECT INTO v_outtab LIMIT 100;

FOR i IN 1..v_outtab.COUNT LOOP

-- Process the rows one by one according to required logic.

NULL;

END LOOP;

EXIT WHEN c_in%NOTFOUND;

END LOOP;

CLOSE c_in;

END;

/

 

自治事物

自治事物允许你在一个事务中创建一个事务，这个事务将会独立于父事务进行提交，回滚等改变

允许你挂起现在正在执行的事务，开始一个新的事务，做一些工作，提交回滚，所有的这些事情不会影响当前正在执行事务的状态

通过pragma autonomous_transaction将一个pl/sql程序结构设定为自治事务，pragma是编译器指令，可以将procedure function package等顶级匿名块定义成自治的程序结构。

create or replace trigger EMP_AUDIT

before update on emp for each row

declare

    pragma autonomous_transaction;

    l_cnt number;

begin

    select count(*) into l_cnt from dual

    where EXISTS ( select null from emp where empno = :new.empno

        start with mgr = ( select empno from emp where ename = USER )

        connect by prior empno = mgr );

    if ( l_cnt = 0 ) then

        insert into audit_tab ( msg )

        values ( 'Attempt to update ' || :new.empno );

        commit;

        raise_application_error( -20001, 'Access Denied' );

    end if;

end;

/

 

 

 

绑定变量

下面的代码每次都要硬编码

动态SQL

create or replace procedure dsal(p_empno in number)
as
begin
    execute immediate
        'update emp set sal = sal*2

        where empno = '||p_empno;
    commit;
end;

 

通过如下改变就可以了

绑定变量

create or replace procedure dsal(p_empno in number)
as
begin
    execute immediate
        'update emp set sal = sal*2

        where empno = :x' using p_empno;
    commit;
end;

 

游标变量 : 声明游标实际上是创建一个指针 , 指针具有数据类型 REF X.REF 是 REFERENCE ,X 是表示类对象 . 因此 , 游标变量具有数据类型 REF  CURSOR.

注 : 游标总是指向相同的查询工作区 , 游标变量能够指向不同的工作区 , 因此游标和游标变量不能互操作 .

 

DECLARE

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

emp empcurtyp;

-- after result set is built, process all the rows inside a single procedure

-- rather than calling a procedure for each row

PROCEDURE process_emp_cv (emp_cv IN empcurtyp)

IS

person employees%ROWTYPE;

BEGIN

DBMS_OUTPUT.PUT_LINE('-----');

DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');

LOOP

FETCH emp_cv INTO person;

EXIT WHEN emp_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name || ' ' || person.last_name);

END LOOP;

END;

BEGIN

-- First find 10 arbitrary employees.

OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;

process_emp_cv(emp);

CLOSE emp;

 

-- find employees matching a condition.

OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';

process_emp_cv(emp);

CLOSE emp;

END;