我是李香兰小朋友

PL/SQL编程

2015/02/15 Oracle

1 数据类型

    (1)PLSQL支持所有的SQL数据类型
    (2)标量：数值、字符、布尔、日期时间
       1>. number 包括(decimal、float、integer、real)  
       2>. oracle11g新加入一种数据类型：SIMPLE_INTEGER 范围(-2147483648 ~ +2147483647) 
           数据类型不为空.对于此数据类型，ORACLE可以将这个数据类型的操作直接作用于硬件，从而提高性能。
       3>. 字符型：SQL: char(长度2000)/varchar2(长度4000) PLSQL: 长度都是32767  
       4>. boolean (逻辑判断 true、false、null)
           v_num1 := 50;
           v_num2 := 60;
           v_bool := (v_num1 < v_num2);
       5>. 日期时间(DATE、TIMESTAMP)
    (3)LOB类型(最大可存储4G)，用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。
       BLOB(二进制对象存储)
       CLOB(大文本字符)
       NCLOB(存储UNICODE字符)
       BFILE(讲二进制存储在操作系统中,只读)
    (4)复合类型
       %TYPE      引用变量和数据库列的数据类型一致
       %ROWTYPE   提供表示表中一行的记录类型
    (5)对于常量 就是需要用CONSTANT关键字声明并且一定要初始化值 形如: c_salary_rate CONSTANT NUMBER(7,2) := 0.25;

大对象读取示例：(示例准备)

CREATE TABLE MY_BOOK(
CHARTER_ID NUMBER,
 CHARTER_TEXT  CLOB
  );
  INSERT INTO MY_BOOK (CHARTER_ID, CHARTER_TEXT) VALUES('5','oracle11g新加入一种数据类型：SIMPLE_INTEGER 范围(-2147483648 ~ +2147483647) 
>数据类型不为空.对于此数据类型，ORACLE可以将这个数据类型的操作直接作用于硬件，从而提高性能');
SELECT * FROM MY_BOOK;
drop table my_book;

DECLARE
v_clob CLOB;
v_amount  INTEGER;
v_offset  INTEGER;
v_outdata VARCHAR2(1000);
BEGIN 
  SELECT M.CHARTER_TEXT INTO v_clob FROM MY_BOOK M WHERE M.CHARTER_ID = '5';
  v_amount := 300;
  v_offset := 1;
  dbms_lob.read(v_clob, v_amount, v_offset, v_outdata);
  dbms_output.put_line(v_outdata);
END;

复合类型

%TYPE(列类型)

DECLARE
   v_ename EMP.ENAME%TYPE;  --与emp表中的ename类型相同
BEGIN
   SELECT E.ENAME INTO v_ename FROM EMP E WHERE E.ENAME = 'SMITH';  
   DBMS_OUTPUT.put_line(v_ename);
END;

%TYPE绑定某一列

DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_hiredate emp.hiredate%TYPE;
BEGIN
  SELECT empno, ename, hiredate INTO v_empno, v_ename, v_hiredate FROM emp WHERE empno='7369';
  dbms_output.put_line('员工编号:' || v_empno);
  dbms_output.put_line('员工姓名:' || v_ename);
  dbms_output.put_line('入职日期:' || v_hiredate);
END;

%ROWTYPE定义游标类型的变量

DECLARE
  CURSOR cur_emp
  IS 
   SELECT E.EMPNO, E.ENAME, E.JOB  FROM EMP E;   --使用%rowtype定义游标类型的变量
   v_emprow cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp;
  LOOP
FETCH cur_emp INTO v_emprow;
--注意退出游标
EXIT WHEN cur_emp%NOTFOUND;
dbms_output.put_line(v_emprow.empno || ' ' || v_emprow.ename || ' ' || v_emprow.job);
  END LOOP; 
END;

自定义记录类型

--定义一个记录类型
--定义了一个my_record变量
declare
type emp_record_type is record(name emp.ename%type, salary emp.sal%type);
--定义一个my_record变量 这个变量的类型是emp_record_type
my_record emp_record_type; 

DECLARE
--自定义类型
TYPE emp_record_type IS RECORD(
 NAME  emp.ename%TYPE,
 salaryemp.sal%TYPE,
 title emp.job%TYPE);
--变量接受自定义类型   
emp_record emp_record_type;
--执行程序
BEGIN
  SELECT E.ENAME, E.SAL, E.JOB INTO emp_record FROM EMP E 
  WHERE EMPNO = '7788';
  DBMS_OUTPUT.put_line('雇员名：' || emp_record.name);
END;

表类型(索引表)

PL/SQL表只有俩列，一个索引列和一列(一列不代表一个字段) 通过索引列中的索引值来操作PL/SQL表中对应的用户自定义列.

declare
--定义了一个pl/sql表类型my_table_type, 该类型是用于存放emp.ename%type
type my_table_type is table of emp.ename%type 
--索引列必须是binary_integer类型的
index by binary_integer
--定义了一个my_table变量,这个变量的类型是 my_table_type
my_table my_table_type

DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
  SELECT ename INTO ename_table(1) FROM emp WHERE empno = 7788;
  dbms_output.put_line(ename_table(1));
END;

PLSQL结构、存储过程、函数

EXCEPTION捕获

  [DECLARE declarations]
  BEGIN
executable statements
[EXCEPTION handlers]
  END;  

DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = &empno;
  dbms_output.put_line('员工姓名:' || v_ename);
  EXCEPTION
WHEN NO_DATA_FOUND 
THEN dbms_output.put_line('查无此人!');  
END;

条件判断

条件判断
IF boolean THEN executable END IF;
IF boolean THEN executable ELSE executable END IF;
IF boolean THEN executable ELSIF boolean THEN executable ELSE executable END IF;

循环 (LOOP WHILE FOR)

**-- LOOP循环**
DECLARE
   j NUMBER := 0;
BEGIN
   j := 1;
   LOOP
 dbms_output.put_line(j);
 EXIT WHEN j >= 7;  
 j := j + 1;
   END LOOP;
END;

**-- WHILE循环**
DECLARE
   j NUMBER := 0;
BEGIN
   j := 1;
   WHILE j <= 8
   LOOP
   dbms_output.put_line(j || '---');
   j := j+1;
   END LOOP;
END;

**-- FOR循环**
DECLARE 
  j NUMBER := 0;
BEGIN
  FOR j IN 1..8
  LOOP
dbms_output.put_line(j || '---');
  END LOOP;  
END;

存储过程（PROCEDURE）

参数形式三种 (in, outm in out)

CREATE OR REPLACE PROCEDURE PROC1(i IN NUMBER)
AS 
 a VARCHAR2(50);
BEGIN
  a := '';
  FOR j IN 1..i 
  LOOP
a := a || '*';
dbms_output.put_line(a);
  END LOOP;
END;

两种方式调用存储过程 1 通过exec执行 2 通过块执行

exec PROC1(4);--第一种

BEGIN
  PROC1(7);--第二种
END;

CREATE OR REPLACE PROCEDURE PROC2(i OUT NUMBER)
AS
BEGIN
  i := 100;
  dbms_output.put_line(i);
END;

DECLARE
 k NUMBER;
BEGIN
  PROC2(K);
  dbms_output.put_line(k);
END; 

CREATE OR REPLACE PROCEDURE PROC3(p1 IN OUT NUMBER , p2 IN OUT NUMBER)
AS
  v_temp NUMBER;
BEGIN
  v_temp := p1;
  p1 :=  p2;
  p2 := v_temp;
END;

DECLARE 
num1 NUMBER := 10; 
num2 NUMBER := 20;
BEGIN
  PROC3(num1, num2);
  dbms_output.put_line(num1); 
  dbms_output.put_line(num2); 
END;

函数：（函数是可以返回值的命名的PL/SQL 子程序）

 CREATE [OR REPLACE] FUNCTION <FUNCTION NAME> [(PARAM...)]
 RETURN <DATATYPE> IS|AS 
 [LOCAL DECLARATIONS]
 BEGIN
   EXECUTABLE STATEMENTS;
   RETURN RESULT;
   EXCEPTION 
     EXCEPTION HANDLERS; 
 END;


创建一个函数,可以接受用户输入的学号，得到该学生的名次，输出名次。

-- 实验准备
CREATE TABLE STUDENT (STU_NO NUMBER(3), NAME VARCHAR2(10), SCORE NUMBER(3));
INSERT INTO STUDENT VALUES (1 , '小小', 99);
INSERT INTO STUDENT VALUES (2 , '小G',  80);
INSERT INTO STUDENT VALUES (3 , '诺诺', 98);
INSERT INTO STUDENT VALUES (4 , '小北', 79);
COMMIT;
SELECT * FROM STUDENT;


CREATE OR REPLACE FUNCTION FUNC1(SNO INT) RETURN INT
AS
 v_score NUMBER;
 v_mingci NUMBER;
BEGIN
 SELECT SCORE INTO v_score FROM STUDENT WHERE STU_NO = SNO;
 SELECT COUNT(*) INTO v_mingci FROM STUDENT WHERE SCORE > v_score;
 v_mingci := v_mingci + 1;
 RETURN v_mingci;
END;

SELECT FUNC1(3) FROM DUAL;

PL/SQL 游标

游标：三类：
   1隐性游标(SQL%FOUND.缺省写法存在、可以捕捉到、很少用)  
   2显式游标  
   3参照(REF)游标
   操作步骤: 声明游标 -打开游标 -提取行 -变量 -关闭游标.

(1)显式游标写法1:

DECLARE
  V_SAL EMP.SAL%TYPE;
  CURSOR MYCURSOR IS 
  SELECT SAL FROM EMP WHERE SAL > 2500;
BEGIN
  OPEN MYCURSOR;
  LOOP
FETCH MYCURSOR INTO V_SAL;
EXIT WHEN MYCURSOR%NOTFOUND;
DBMS_OUTPUT.put_line('工资为:' || V_SAL);
  END LOOP;
  CLOSE MYCURSOR;
END;

(2)显式游标写法2：

DECLARE
  V_SAL EMP.SAL%TYPE;
  --显示游标一定要在DECLARE里面出现
  CURSOR MYCURSOR IS
  SELECT SAL FROM EMP WHERE SAL > 2500;
BEGIN
  --打开游标
  OPEN MYCURSOR;
  --获取下一个值
  FETCH MYCURSOR INTO V_SAL;
--循环
WHILE MYCURSOR%FOUND LOOP
  DBMS_OUTPUT.put_line('工资为:' || V_SAL);
  FETCH MYCURSOR INTO V_SAL;
END LOOP;
  --关闭游标
  CLOSE MYCURSOR;
END;

显式游标可以带参数提高灵活性
CURSOR <cursor_name>(<param_name> <param_type>)
IS SELECT STATEMENT
允许使用游标删除或更新活动集中的行
声明游标时必须使用 SELECT ... FOR UPDATE语句
CURSOR <cursor_name> 
IS SELECT STATEMENT FOR UPDATE;
UPDATE <table_name>
SET <set_clause>
WHERE CURRENT OF <cursor_name>
DELETE FROM <table_name>
WHERE CURRENT OF <cursor_name>

循环游标用于简化游标处理代码
当用户需要从游标中提取所以记录时使用
循环游标的语法如下:

FOR<record_index> IN <cursor_name>
LOOP
  <executable statements>
END LOOP;

(3)带参数的游标

DECLARE
 -- 声明学号变量
 V_SNO STUDENT.STU_NO%TYPE;
 -- 一行多利记录
 V_STU STUDENT%ROWTYPE;
 CURSOR MYCURSOR(INPUT_NO NUMBER) 
 IS
 SELECT * FROM STUDENT WHERE STU_NO > INPUT_NO;
BEGIN
  V_SNO := &学生学号;
  OPEN MYCURSOR(V_SNO);
  LOOP
FETCH MYCURSOR INTO V_STU;
EXIT WHEN MYCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('学号是:' || V_STU.STU_NO || '姓名是:' || V_STU.NAME);
  END LOOP;
  CLOSE MYCURSOR;
END;

(4)显式游标 进行更新

DECLARE
 V_STU STUDENT%ROWTYPE;
 CURSOR MYCURSOR 
 IS
 SELECT * FROM STUDENT WHERE STU_NO = 2 OR STU_NO = 3 
 FOR UPDATE;
BEGIN 
  OPEN MYCURSOR;
  LOOP
   FETCH MYCURSOR INTO V_STU;
   EXIT WHEN MYCURSOR%NOTFOUND;
   UPDATE STUDENT SET STU_NO = STU_NO + 100
   WHERE CURRENT OF MYCURSOR;
  END LOOP;
  CLOSE MYCURSOR; 
END;

(5)显式游标和记录类型结合

DECLARE 
   --定义一个记录类型
   TYPE EMP_RECORD_TYPE IS RECORD(NAME EMP.ENAME%TYPE, SALARY EMP.SAL%TYPE);
   --定义变量
   V_ET EMP_RECORD_TYPE;
   CURSOR MYCURSOR 
   IS
   SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 10;
BEGIN
   OPEN MYCURSOR;
   LOOP
FETCH MYCURSOR INTO V_ET;
EXIT WHEN MYCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:'|| V_ET.NAME || '薪水:' || V_ET.SALARY);
   END LOOP;
   CLOSE MYCURSOR;
END;

(6)显式游标和表类型结合

DECLARE 
   -- 定义一个表类型
   TYPE ENAME_TABLE_TYPE
   IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
   -- 定义变量
   V_ET ENAME_TABLE_TYPE;
   CURSOR MYCURSOR
   IS 
   SELECT ENAME FROM EMP WHERE DEPTNO = 10;
BEGIN
   OPEN MYCURSOR;
   FETCH MYCURSOR BULK COLLECT INTO V_ET;
   FOR I IN 1..V_ET.COUNT LOOP
   DBMS_OUTPUT.PUT_LINE(V_ET(I));
   END LOOP;
   CLOSE MYCURSOR;
END;

(7)显式游标用于子程序UPDATE

   --实验准备
   CREATE TABLE PERSON (XH NUMBER, XM VARCHAR2(10));
   INSERT INTO PERSON VALUES(1, 'A');
   INSERT INTO PERSON VALUES(2, 'B');
   INSERT INTO PERSON VALUES(3, 'C');
   INSERT INTO PERSON VALUES(4, 'D');
   COMMIT;
   CREATE TABLE ADDRESS (XH NUMBER, ZZ VARCHAR2(20));
   INSERT INTO ADDRESS VALUES(2, '北京');
   INSERT INTO ADDRESS VALUES(1, '广州');
   INSERT INTO ADDRESS VALUES(3, '上海');
   INSERT INTO ADDRESS VALUES(4, '西安');
   COMMIT;
   SELECT * FROM PERSON;
   SELECT * FROM ADDRESS;
   ALTER TABLE PERSON ADD ZZ CHAR(10);


DECLARE 
   V_XH NUMBER;
   V_ZZ VARCHAR2(10);
   CURSOR MYCURSOR 
   IS
   SELECT XH, ZZ FROM ADDRESS;
BEGIN
   OPEN MYCURSOR;
   LOOP
FETCH MYCURSOR INTO V_XH, V_ZZ;
EXIT WHEN MYCURSOR%NOTFOUND;
UPDATE PERSON SET ZZ = V_ZZ WHERE XH = V_XH;
   END LOOP;
   CLOSE MYCURSOR;
END;

  
UPDATE PERSON P SET P.ZZ = '';
-- 使用SQL也可以实现上面的操作
UPDATE PERSON P SET P.ZZ = (SELECT A.ZZ FROM ADDRESS A WHERE A.XH = P.XH);

   


   参照游标(REF)
   游标和游标变量用于处理运行时动态执行的SQL查询
   创建游标变量需要俩个步骤:
   声明 REF 游标类型
   声明 REF 游标类型的变量
   用于声明 REF 游标类型的语法为:
   TYPE <ref_cursor_name> IS REF CURSOR
   [RETURN <return_type>]

DECLARE 
TYPE REFCUR IS REF CURSOR;
MYCURSOR REFCUR;
V_TBNAME VARCHAR2(50);
V_NO STUDENT.STU_NO%TYPE;
V_NAME STUDENT.NAME%TYPE;
BEGIN
V_TBNAME := '&表名';
IF V_TBNAME = 'STUDENT' THEN
   OPEN MYCURSOR FOR SELECT STU_NO,NAME FROM STUDENT;
   LOOP
FETCH MYCURSOR INTO V_NO, V_NAME;
EXIT WHEN MYCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('学号:' || V_NO || '姓名:' || V_NAME);
   END LOOP;
   CLOSE MYCURSOR;
ELSE 
   DBMS_OUTPUT.PUT_LINE('表名不正确');
END IF;
END;

PL/SQL 异常处理、触发器

异常处理

异常有俩种类型
预定义异常 - 当PL/SQL 程序违反Oracle规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在PL/SQL块的声明部分定义异常,自定义的异常通过RAISE语句显式引发
命名的系统异常
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR
CURSOR_ALREADY_OPEN
COLLECTION_IS_NULL
ACCESS_INTO_NULL
TIMEOUT_ON_RESOURCE
ZERO_DIVIDE
...

RAISE APPLICATION_ERROR 过程
用于创建用户自定义的错误信息
可以在执行部分和异常处理部分使用
错误编号必须介于 -20000 和 - 20999 之间
错误消息长度最多 2048个字节
引发应用程序错误的语法:
RASIE_APPLICATION_ERROR(-20001,'身份证信息与出生日期不符');

注意RAISE 异常会使程序停止,不会进行后续任何操作

实验准备

CREATE TABLE STUDENT (
SID CHAR(18) PRIMARY KEY,
SNAME CHAR(10),
SBIRTH DATE
);

CREATE OR REPLACE PROCEDURE PROC4(P1 CHAR, P2 CHAR, P3 DATE)
IS
-- 自定义异常
INVALID_DATE EXCEPTION;
V_A CHAR(8);
V_B CHAR(8);
BEGIN
  INSERT INTO STUDENT VALUES (P1, P2, P3);
  SELECT SUBSTR(P1, 7, 8) INTO V_A FROM STUDENT S  WHERE S.SID = P1;
  SELECT TO_CHAR(P3,'yyyymmdd') INTO V_B FROM STUDENT S  WHERE S.SID = P1;
  IF V_A = V_B THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('插入成功');
  ELSE 
ROLLBACK;
--升起异常
RAISE INVALID_DATE;
  END IF;
  EXCEPTION
WHEN INVALID_DATE THEN
--DBMS_OUTPUT.PUT_LINE('身份证信息与出生日期不符！');
RAISE_APPLICATION_ERROR(-20001,'身份证信息与出生日期不符');
END;

DECLARE
V_SID CHAR(18);
V_SNAME CHAR(10);
V_SBIRTH DATE;
BEGIN
V_SID := '220104196708274110';
V_SNAME := 'BHZ';
V_SBIRTH := TO_DATE('19770827','yyyymmdd');
PROC4(V_SID, V_SNAME, V_SBIRTH);
END;

触发器

触发器由三部分组成:

> 1 触发器语句(事件)
> 定义集合触发器的DML事件和DDL事件
> 2 触发器限制
> 执行触发器的条件 该条件必须为真才能激活触发器
> 3 触发器操作(主体)
> 包含一些SQL语句和代码,他们在发出了触发器语句且触发限制的值为真时运行
> 
> CREATE [OR REPLACE] TRIGGER trigger_name
> AFTER | BEFORE | INSTEAD OF
> [INSERT] [ [OR] UPDATE [OF column_list] ]
> [[OR] DELETE]
> ON table_or_view_name
> [REFERENCING { OLD [AS] OLD/NEW [AS] new}]
> [FOR EACH ROW]
> [WHEN (condition)]
> PL/SQL_BLOCK

1 DDL 触发器：在模式中执行 DDL语句时执行
2 数据库触发器：在发生打开、关闭、登陆和退出数据库等系统事件时执行
3 DML 触发器：正在对表或视图执行DML语句时执行
      语句级触发器：无论受影响的行数是多少，都只执行一次
      行级触发器：对DML语句修改的每个执行一次
      INSTEAD OF 触发器：用于用户不能直接使用DML语句修改的视图 
4 注意: 触发器里面不能使用(TCL、DDL语句)：
  rollback、commit、savepoint、create、drop、alter等..

当用户插入、更新EMP1表中的记录时,就输入一个提示”TRIG1 BEGIN TO WORK”

实验准备
CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM EMP1;


CREATE OR REPLACE TRIGGER TRIG1
AFTER INSERT OR UPDATE ON EMP1
BEGIN
  DBMS_OUTPUT.PUT_LINE('TRIG1 RESPONDED!');
END;

-- 新增
INSERT INTO EMP1 SELECT * FROM EMP E WHERE E.EMPNO = '7788';
-- 更新
UPDATE EMP1 SET SAL = 3000 WHERE EMPNO = '7782';

行级触发器

CREATE OR REPLACE TRIGGER TRIG2 
AFTER UPDATE ON EMP1 
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('TRIG2 ROW UPDATE!');
END;

UPDATE EMP1 SET SAL = 4000;
ROLLBACK;

OLD.FIELD :NEW.FIELD 使用,级联触发更新

准备实验

create table PRODUCTS
(
  product_id   NUMBER(38),
  product_name VARCHAR2(60),
  category VARCHAR2(60),
  CONSTRAINT PK_PRODUCTS PRIMARY KEY (product_id)
);

create table TLOG
(
   CHANGE_IDVARCHAR2(32) NOT NULL,
   DATA_CODEVARCHAR2(40) NOT NULL,
   CHANGE_TYPE  VARCHAR2(10)  NOT NULL,
   CHANGE_BYVARCHAR2(80) NOT NULL,
   CHANGE_TIME  TIMESTAMPNOT NULL,
   CONSTRAINT PK_MST_CHANGE PRIMARY KEY (CHANGE_ID)
);

create table TCOL
(
  CHANGE_ID   VARCHAR2(32) not null,
  COLNAME VARCHAR2(40),
  BEFOREVALUE VARCHAR2(256),
  AFTERVALUE  VARCHAR2(256),
  CONSTRAINT PK_TCOL PRIMARY KEY (CHANGE_ID, COLNAME)
);
ALTER TABLE TCOL
   ADD CONSTRAINT FK_TCOL FOREIGN KEY (CHANGE_ID)
  REFERENCES TLOG (CHANGE_ID);

insert into PRODUCTS (product_id, product_name, category) values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS (product_id, product_name, category) values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS (product_id, product_name, category) values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS (product_id, product_name, category) values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS (product_id, product_name, category) values (1717, 'HARRY POTTER', 'DVD');
insert into PRODUCTS (product_id, product_name, category) values (1666, 'HARRY POTTER', 'DVD');
insert into PRODUCTS (product_id, product_name, category) values (1718, 'AAA', 'TTT');
commit;

创建触发器

CREATE OR REPLACE TRIGGER t_case_change
AFTER INSERT OR UPDATE
ON PRODUCTS
FOR EACH ROW
DECLARE
  v_uid   TLOG. CHANGE_ID%TYPE;
  v_type  TLOG.CHANGE_TYPE%TYPE;
BEGIN
  SELECT sys_guid() INTO v_uid FROM DUAL;
  IF INSERTING THEN
 v_type := 'INSERT';
 INSERT INTO TLOG VALUES (v_uid, 'PRODUCTS', v_type, :NEW.PRODUCT_ID, SYSTIMESTAMP);
 INSERT INTO TCOL VALUES (v_uid, 'PRODUCT_NAME', :NEW.PRODUCT_NAME, :NEW.PRODUCT_NAME);
 INSERT INTO TCOL VALUES (v_uid, 'CATEGORY', :NEW.CATEGORY, :NEW.CATEGORY);
  END IF;
  IF UPDATING THEN
 v_type := 'UPDATE';
 INSERT INTO TLOG VALUES (v_uid, 'PRODUCTS', v_type, :OLD.PRODUCT_ID, SYSTIMESTAMP);
 IF UPDATING('PRODUCT_NAME') THEN 
 INSERT INTO TCOL VALUES (v_uid, 'PRODUCT_NAME', :OLD.PRODUCT_NAME, :NEW.PRODUCT_NAME);
 END IF;
 IF UPDATING('CATEGORY') THEN
 INSERT INTO TCOL VALUES (v_uid, 'CATEGORY', :OLD.CATEGORY, :NEW.CATEGORY); 
 END IF;
  END IF;
END;

测试数据

insert into products values(2001, '新增数据2001', '新增版块2001');
insert into products values(2002, '新增数据2002', '新增版块2002');
update products set PRODUCT_NAME = '更新PRODUCT_NAME字段-1' where PRODUCT_ID = '1717' ;
update products set PRODUCT_NAME = '更新PRODUCT_NAME字段-2', CATEGORY = '更新CATEGORY字段-2' where PRODUCT_ID = '1718';
查询语句
select * from products
select * from TLOG
select * from TCOL

truncate table PRODUCTS
truncate table TLOG
truncate table TCOL

PL/SQL 包、定时任务

包

 (1)包PACKAGE
 DBMS_OUTPUT
 DBMS_LOB
 DBMS_JOB..
 包(包头、包体)
 包头: 公共类型的变量、常量、异常、过程、函数等的声明。
 包体：私有类型的变量、常量、异常、过程、函数等的声明以及包头内的过程、函数的实现
 
 (2)包规范[包头]
 CREATE [OR REPLACE] 
 PACKAGE <package_name> 
 IS|AS
 [Public item declarations]
 [Subprogram specification]
 END [package_name];
 
 (3)包规范[包体]
 CREATE [OR REPLACE] 
 PACKAGE BODY <package_nams>
 IS|AS
 [Private item declarations]
 [Subprogram bodies]
 [BEGIN Initialization]
 END [package_name];
 
 (4)程序包的优点     
 模块化
 更轻松的应用程序设计
 信息隐藏
 新增功能
 性能更佳

包示例

实验准备

CREATE TABLE STUDENT (STU_NO NUMBER(3), NAME VARCHAR2(10), SCORE NUMBER(3));
INSERT INTO STUDENT VALUES (1 , '小小', 99);
INSERT INTO STUDENT VALUES (2 , '小G',  80);
INSERT INTO STUDENT VALUES (3 , '诺诺', 98);
INSERT INTO STUDENT VALUES (4 , '小北', 79);
COMMIT;
SELECT * FROM STUDENT;

包头

CREATE OR REPLACE PACKAGE PACK1
IS
V_A INT := 9;
--定义显示游标1
CURSOR MYCURSOR1 RETURN STUDENT%ROWTYPE;
--定义参照游标1
TYPE REFCUR1 IS REF CURSOR;

--定义参照游标2
TYPE REFCUR2 IS REF CURSOR;

PROCEDURE INSERT_STUDENT(P1 IN STUDENT%ROWTYPE);
PROCEDURE UPDATE_STUDENT(P2 IN STUDENT%ROWTYPE);
--定义过程中使用显示游标
PROCEDURE MYCURSOR1_USE;
--定义过程中使用参照游标1
PROCEDURE REFCUR1_USE;
--定义过程中使用参照游标2
PROCEDURE REGCUR2_USE(P_NO IN NUMBER, P_CURSOR OUT PACK1.REFCUR2);

END PACK1;

包体

CREATE OR REPLACE PACKAGE BODY PACK1
IS
V_B INT := 5;

CURSOR MYCURSOR1 RETURN STUDENT%ROWTYPE
  IS 
  SELECT * FROM STUDENT; 

PROCEDURE INSERT_STUDENT(P1 IN STUDENT%ROWTYPE)
  IS
  BEGIN
INSERT INTO STUDENT(STU_NO, NAME, SCORE)
VALUES(P1.STU_NO, P1.NAME, P1.SCORE);
COMMIT;
  END;

PROCEDURE UPDATE_STUDENT(P2 IN STUDENT%ROWTYPE)
  IS
  BEGIN
UPDATE STUDENT SET NAME = P2.NAME, SCORE = P2.SCORE
WHERE STU_NO = P2.STU_NO;
COMMIT;
  END;

PROCEDURE MYCURSOR1_USE
  IS 
  V_STU STUDENT%ROWTYPE;
  BEGIN
OPEN MYCURSOR1;
LOOP
 FETCH MYCURSOR1 INTO V_STU;
 EXIT WHEN MYCURSOR1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE('学号为:' || V_STU.STU_NO || '姓名为:' || V_STU.NAME);
END LOOP;
CLOSE MYCURSOR1;
  END MYCURSOR1_USE;

PROCEDURE REFCUR1_USE
  IS
  MYREFCUR1 REFCUR1;
  V_STU STUDENT%ROWTYPE;
  BEGIN
OPEN MYREFCUR1 FOR SELECT * FROM STUDENT;
LOOP
 FETCH MYREFCUR1 INTO V_STU;
 EXIT WHEN MYREFCUR1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE('学号为:' || V_STU.STU_NO || '姓名为:' || V_STU.NAME);
END LOOP;
CLOSE MYREFCUR1;
  END;

 
  PROCEDURE REGCUR2_USE(P_NO IN NUMBER, P_CURSOR OUT PACK1.REFCUR2)
  IS
  BEGIN
OPEN P_CURSOR FOR SELECT * FROM EMP WHERE DEPTNO = P_NO;
  END;

END PACK1;

查看包信息

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS 
WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');
--调用PACK1.REGCUR2_USE
DECLARE
  MYCURSOR PACK1.REFCUR2;
  V_EMP EMP%ROWTYPE;
BEGIN
  PACK1.REGCUR2_USE(10,MYCURSOR);
  LOOP
   FETCH MYCURSOR INTO V_EMP;
   EXIT WHEN MYCURSOR%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('姓名为:' || V_EMP.ENAME);
  END LOOP;
END;

定时任务

实验准备

CREATE TABLE TEST(A DATE);

CREATE OR REPLACE PROCEDURE PROC_TEST
IS
BEGIN
 INSERT INTO TEST VALUES (SYSDATE);
END;

定义任务

VARIABLE job1 NUMBER;
BEGIN
 DBMS_JOB.SUBMIT(:job1, 'PROC_TEST;', SYSDATE, 'SYSDATE+1/1440');
END;

运行任务

BEGIN
 DBMS_JOB.RUN(:job1);
END;

删除任务

BEGIN
  DBMS_JOB.REMOVE(:job1);
END;
SELECT * FROM TEST;

Search

    Table of Contents