--通过q'定义||为定界符SELECT q'|What's your name?|' FROM dual;
--通过q'定义//为定界符SELECT q'/What's your name?/' FROM dual;
--通过q'定义{}为定界符
SELECT q'{What's your name?}' FROM dual;
--通过q'定义[]为定界符
SELECT q'[What's your name?]' FROM dual;
--建表CREATETABLE example (
example_id NUMBER,
example_name VARCHAR2(200));--插入数据INSERTINTO example VALUES(1,'样例1');INSERTINTO example VALUES(2,'样例2');COMMIT;--测试%TYPEDECLARE--用%TYPE属性声明p_example_id 是与example表中example_id相同的数据类型
p_example_id example.example_id%TYPE;--用%TYPE属性声明p_example_name 是与example表中example_name相同的数据类型
p_example_name example.example_name%TYPE;BEGIN--将从example表中读取的一行数据的两列分别写进p_example_id和p_example_name变量SELECT*INTO p_example_id,p_example_name
FROM example
WHERE ROWNUM =1;--在oracle输出区输出p_example_id的值
dbms_output.put_line(p_example_id);--在oracle输出区输出p_example_name的值
dbms_output.put_line(p_example_name);END;/
CREATETABLE example (
example_id NUMBER,
example_name VARCHAR2(200));INSERTINTO example VALUES(1,'样例1');INSERTINTO example VALUES(2,'样例2');COMMIT;
MERGEINTO example a
USING(SELECT1 example_id,'MERGE后的样例1' example_name FROM dual
UNIONSELECT2 example_id,'MERGE后的样例2' example_name FROM dual
UNIONSELECT3 example_id,'MERGE后的样例3' example_name FROM dual
) b
ON(a.example_id = b.example_id)WHENMATCHEDTHENUPDATESET
a.example_name = b.example_name
--限制当 example_id = 1 的时候才做更新WHERE
a.example_id =1WHENNOTMATCHEDTHENINSERT(a.example_id,a.example_name)VALUES(b.example_id,b.example_name);COMMIT;
IF语句
IF 条件1THEN--如果满足条件1,则执行该语句块中的语句
statements;[--ELSIF 可以有0个或多个
ELSIF 条件2THEN--如果不满足条件1但满足条件2,则执行该语句块中的语句
statements;][--ELSE可以有0个或1个ELSE--如果条件1和条件2都不满足,则执行该语句块中的语句
statements;]ENDIF;
DECLARE
score NUMBER;BEGIN
score :=100;IF score >=90AND score <=100THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are very good!');
ELSIF score >=70AND score <90THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are good!');
ELSIF score >=60AND score <70THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible!');ELSE
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible very much!');ENDIF;
score :=80;IF score >=90AND score <=100THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are very good!');
ELSIF score >=70AND score <90THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are good!');
ELSIF score >=60AND score <70THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible!');ELSE
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible very much!');ENDIF;
score :=65;IF score >=90AND score <=100THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are very good!');
ELSIF score >=70AND score <90THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are good!');
ELSIF score >=60AND score <70THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible!');ELSE
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible very much!');ENDIF;
score :=50;IF score >=90AND score <=100THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are very good!');
ELSIF score >=70AND score <90THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are good!');
ELSIF score >=60AND score <70THEN
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible!');ELSE
dbms_output.put_line('Your score is '|| to_char(score)||', you are terrible very much!');ENDIF;END;/
CASE表达式与CASE语句
--CASE表达式既可以用在SQL语句中,也可以用在PL/SQL中--第一种CASE表达式CASE selector
WHEN 表达式1THEN 结果1WHEN 表达式2THEN 结果2...WHEN 表达式n THEN 结果n
[ELSE 结果n+1]END;--必须以END结尾--当1=2时输出1,否则输出0--显然结果是输出0SELECTCASE1WHEN2THEN1ELSE0ENDFROM dual;--第二种CASE表达式CASEWHEN 搜索条件1THEN 结果1WHEN 搜索条件2THEN 结果2...WHEN 搜索条件n THEN 结果n
[ELSE 结果n+1]END;--必须以END结尾--当1=2时输出1,否则输出0--显然结果是输出0SELECTCASEWHEN1=2THEN1ELSE0ENDFROM dual;--CASE语句只能用在PL/SQL中--第一种CASE语句CASE selector
WHEN 表达式1THEN
statements1;WHEN 表达式2THEN
statements2;...WHEN 表达式n THEN
statementsn;[ELSE
statementsn+1]ENDCASE;--必须以END CASE;结尾--第二种CASE语句CASEWHEN 搜索条件1THEN
statements1;WHEN 搜索条件2THEN
statements2;...WHEN 搜索条件n THEN
statementsn;[ELSE
statementsn+1]ENDCASE;--必须以END CASE;结尾BEGIN--第一种CASE1WHEN2THEN
dbms_output.put_line('1 = 2');ELSE
dbms_output.put_line('1 <> 2');ENDCASE;--第二种CASEWHEN1=2THEN
dbms_output.put_line('1 = 2');ELSE
dbms_output.put_line('1 <> 2');ENDCASE;END;/
LOOP循环
/*
循环输出1到10
*/DECLARE--定义一个数,初始值为1
num NUMBER :=1;BEGIN--循环开始LOOP--输出num
dbms_output.put_line(num);--num+1
num := num +1;--循环退出条件,当num第一次出现比10大的时候退出EXITWHEN num >10;ENDLOOP;END;/
WHILE循环
/*
循环输出1到10
*/DECLARE--定义一个数,初始值为1
num NUMBER :=1;BEGIN--先判断退出循环条件,退出循环条件为 num <= 10WHILE num <=10LOOP--输出num
dbms_output.put_line(num);--num++
num := num +1;ENDLOOP;END;/
FOR循环
/*
循环输出1到10
*/BEGINFOR i IN1..10LOOP
dbms_output.put_line(i);ENDLOOP;END;/
CONTINUE
--CONTINUE退出当前循环进入下一次循环/*
循环输出1到4,6到10,不输出5
*/--可以这么写BEGINFOR i IN1..10LOOPCONTINUEWHEN i =5;
dbms_output.put_line(i);ENDLOOP;END;/--也可以这么写BEGINFOR i IN1..10LOOPIF i =5THENCONTINUE;ENDIF;
dbms_output.put_line(i);ENDLOOP;END;/
常用的组合数据类型
PL/SQL记录
创建一个记录类型;
TYPE 数据类型名 IS RECORD (
字段声明[,字段声明]...);
声明一个PL/SQL记录类型的变量;
标识符 数据类型名;
例:
DECLARE--创建一个记录类型TYPE exp_record_type IS RECORD (
example_id example.example_id%TYPE,
example_name example.example_name%TYPE);--声明记录类型的变量
exp_record exp_record_type;BEGIN--将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中SELECT
example_id,
example_name
INTO exp_record
FROM example
WHERE ROWNUM =1;--输出
dbms_output.put_line(exp_record.example_id);
dbms_output.put_line(exp_record.example_name);END;/
%ROWTYPE
利用%ROWTYPE属性声明一个能够存储一个表或视图中一整行数据的记录(变量)。
DECLARE--声明记录类型的变量
exp_record example%ROWTYPE;BEGIN--将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中SELECT
example_id,
example_name
INTO exp_record
FROM example
WHERE ROWNUM =1;--输出
dbms_output.put_line(exp_record.example_id);
dbms_output.put_line(exp_record.example_name);END;/
DECLARE
p_example_id NUMBER :=1;--声明游标CURSOR exp_cursor ISSELECT*FROM example
WHERE example_id = p_example_id;--声明基于游标的记录变量
exp_cursor_record exp_cursor%ROWTYPE;BEGIN--如果游标不是打开状态,则打开游标IFNOT exp_cursor%ISOPEN THENOPEN exp_cursor;ENDIF;--循环获取游标的数据到基于游标的记录变量LOOPFETCH exp_cursor INTO exp_cursor_record;EXITWHEN exp_cursor%NOTFOUND OR exp_cursor%NOTFOUND ISNULL;
dbms_output.put_line(exp_cursor_record.example_id ||'|'|| exp_cursor_record.example_name);ENDLOOP;CLOSE exp_cursor;END;/DECLARE
p_example_id NUMBER :=1;BEGINFOR i IN(SELECT*FROM example
WHERE example_id = p_example_id
)LOOP
dbms_output.put_line(i.example_id ||'|'|| i.example_name);ENDLOOP;END;/
参数在CURSOR的声明中声明;
DECLARE--声明游标CURSOR exp_cursor (
p_example_id NUMBER,
p_example_name VARCHAR2
)ISSELECT*FROM example
WHERE example_id = p_example_id;--声明基于游标的记录变量
exp_cursor_record exp_cursor%ROWTYPE;BEGIN--如果游标不是打开状态,则打开游标IFNOT exp_cursor%ISOPEN THENOPEN exp_cursor (1,'MERGE后的样例1');ENDIF;--循环获取游标的数据到基于游标的记录变量LOOPFETCH exp_cursor INTO exp_cursor_record;EXITWHEN exp_cursor%NOTFOUND OR exp_cursor%NOTFOUND ISNULL;
dbms_output.put_line(exp_cursor_record.example_id ||'|'|| exp_cursor_record.example_name);ENDLOOP;CLOSE exp_cursor;END;/
UPDATE example SET example_name ='111'WHERE example_id =1;--COMMIT;
另外开启一个会话,执行下面的语句;
DECLARE
p_example_id NUMBER :=1;BEGINFOR i IN(SELECT*FROM example
WHERE example_id = p_example_id
FORUPDATE WAIT 10)LOOP
dbms_output.put_line(i.example_id ||'|'|| i.example_name);ENDLOOP;END;/
在UPDATE或DELETE中使用 WHERE CURRENT OF,在CURSOR声明中说明 FOR UPDATE ;
WHERE CURRENT OF 引用显示CURSOR的当前行;
DECLARE--声明游标,并对example的example_id=1的数据行加锁CURSOR exp_cursor ISSELECT*FROM example
WHERE example_id =1FORUPDATEOF example_name;BEGIN--循环更新example表被锁定的行数据FOR i IN exp_cursor
LOOPUPDATE example
SET
example_name ='对游标中的表数据进行更新'WHERECURRENTOF exp_cursor;ENDLOOP;--若正常执行到这里,则提交COMMIT;--若出现异常,则回滚
EXCEPTION WHEN OTHERS THENROLLBACK;END;/SELECT*FROM example;
动态CURSOR
声明的时候不关联SQL查询,在打开游标的时候动态关联SQL查询;
声明游标类型;
--cursor_type 为自定义的类型名TYPE cursor_type IS REF CURSOR;
利用 BULK COLLECT INTO 可以快速地获取一个数据行的集合而不再需要使用CURSOR机制了;
returning子句将DML语句的结果直接之装入变量或者如果使用了批量绑定后可以批量装入数组。
--FORALLDECLARETYPE exp_forall_type ISTABLEOF example.name%TYPEINDEXBY PLS_INTEGER;
exp_forall exp_forall_type;BEGIN
exp_forall(1) :='潘金莲';
exp_forall(2) :='杨贵妃';
exp_forall(3) :='武则天';
exp_forall(4) :='花木兰';
exp_forall(5) :='苏妲己';--批量绑定 无需循环
FORALL i IN exp_forall.first.. exp_forall.lastINSERTINTO example VALUES(exp_forall(i));FOR i IN exp_forall.first.. exp_forall.lastLOOP
dbms_output.put_line('Inserted '||SQL%BULK_ROWCOUNT(i)||' row(s) on iteration '|| i
);ENDLOOP;COMMIT;
EXCEPTION WHEN OTHERS THENROLLBACK;END;/--BULK COLLECT INTODECLARETYPE exp_bulk_type ISTABLEOF example.name%TYPEINDEXBY PLS_INTEGER;
exp_bulk exp_bulk_type;BEGIN--将example与数组exp_bulk批量绑定SELECT*BULK COLLECT INTO exp_bulk
FROM example;--循环遍历数组FOR i IN exp_bulk.first.. exp_bulk.lastLOOP
dbms_output.put_line(exp_bulk(i));ENDLOOP;
EXCEPTION WHEN OTHERS THENNULL;END;/
--带有returning的批量绑定CREATETABLE example (
exp_id NUMBER,
exp_value NUMBER
);INSERTINTO example VALUES(1,1000);INSERTINTO example VALUES(2,2000);INSERTINTO example VALUES(3,3000);INSERTINTO example VALUES(4,4000);COMMIT;DECLARETYPE exp_returning_type ISTABLEOF example.exp_value%TYPEINDEXBY PLS_INTEGER;
exp_returning exp_returning_type;
exp_returning_new exp_returning_type;BEGIN
exp_returning(1) :=1;
exp_returning(2) :=2;
exp_returning(3) :=3;
exp_returning(4) :=4;
FORALL i IN exp_returning.first.. exp_returning.lastUPDATE example
SET exp_value = exp_value *1.1WHERE exp_id = exp_returning(i)RETURNING exp_value BULK COLLECT INTO exp_returning_new;FOR i IN exp_returning_new.first.. exp_returning_new.lastLOOP
dbms_output.put_line(exp_returning(i)||' '|| exp_returning_new(i));ENDLOOP;COMMIT;
EXCEPTION WHEN OTHERS THENROLLBACK;END;/
CREATEORREPLACE PACKAGE pkg_example AS--声明存储过程delete_examplePROCEDURE delete_example;PROCEDURE delete_example(
exp_id IN NUMBER
);END pkg_example;/CREATEORREPLACE PACKAGE BODY pkg_example AS--声明存储过程delete_examplePROCEDURE delete_example
ASBEGINDELETEFROM example;COMMIT;
EXCEPTION WHEN OTHERS THENROLLBACK;END;PROCEDURE delete_example(
exp_id IN NUMBER
)ASBEGINDELETEFROM example
WHERE example_id = exp_id;COMMIT;
EXCEPTION WHEN OTHERS THENROLLBACK;END;END pkg_example;/--分别调用2个重载的存储过程BEGIN
pkg_example.delete_example();
pkg_example.delete_example(1);END;/
DML触发器
CREATE[ORREPLACE]TRIGGER trigger_name
timing
event1 [OR event2 OR event3]ON object_name [[REFERENCING OLD AS OLD | NEW AS NEW]FOR EACH ROW[WHEN(condition)]]
trigger_body
其中:
timing = BEFORE |AFTER| INSTEAD OF
event =INSERT|DELETE|UPDATE|UPDATEOF column_list
对于行触发器,可能需要说明: 一个REFERENCING子句以选择引用当前行的旧值和新值的相关的名字 (默认值是OLD和NEW); FOR EACH ROW 指定这个触发器是行触发器; 一个使用括号中条件谓词(表达式)的WHEN子句,该子句要测试每一行以决定是否要执行触发器体(的PL/SQL程序代码);
CREATETABLE example (
example_id NUMBER,
example_name VARCHAR2(200));INSERTINTO example VALUES(1,'样例1');INSERTINTO example VALUES(2,'样例2');COMMIT;--创建语句级触发器/*
在插入数据之前判断今天是否是1号,不为1号则报错不允许插入
*/CREATEORREPLACETRIGGER secure_exp
BEFORE INSERTON example
BEGINIF to_char(SYSDATE,'dd')<>'01'THEN
raise_application_error(-20200,'今天不是1号,不允许插入数据!');ENDIF;END;/--创建带WHEN子句的行级触发器/*
当插入或更新或删除example_id为1到10的数据时报错,不允许插入、更新或删除
*/CREATEORREPLACETRIGGER no_opt_exp
BEFORE INSERTORUPDATEORDELETEON example
FOR EACH ROWWHEN((new.example_id >=1AND new.example_id <=10)OR(old.example_id >=1AND old.example_id <=10))BEGINIF inserting THEN
raise_application_error(-20331,'不允许插入编号1到10的数');
ELSIF updating ('example_id')THEN
raise_application_error(-20332,'不允许更新编号1到10的数');
ELSIF deleting THEN
raise_application_error(-20333,'不允许删除编号1到10的数');ENDIF;END;/--如果今天不是1号,直接报错 “今天不是1号,不允许插入数据!”--如果今天是1号,则报错 “不允许插入编号1到10的数”INSERTINTO example VALUES(2,'样例2');--报错 “不允许更新编号1到10的数”UPDATE example SET example_id =1;--报错 “不允许删除编号1到10的数”DELETEFROM example WHERE example_id =1;--如果今天是1号,则成功插入INSERTINTO example VALUES(11,'样例11');COMMIT;--删除触发器DROPTRIGGER no_opt_exp;DROPTRIGGER secure_exp;--INSTEAD OF 触发器执行触发器体的内容而不执行原来的DML语句,这里不再举例
语句级触发器
行级触发器
是创建触发器时的默认类型
创建触发器时使用 FOR EACH ROW子句
对于触发的事件只触发一次
对受触发事件影响的每行触发一次
没有受影响的行时也要触发一次
触发事件未影响任何数据行就不触发
数据操作
旧(OLD)值
新(NEW)值
INSERT
空值(NULL)
插入的值
UPDATE
修改之前的值
修改之后的值
DELETE
删除之前的值
空值(NULL)
触发器
过程
使用CREATE TRIGGER定义
使用CREATE PROCEDURE定义
源代码包含在user_triggers数据字典中
源代码包含在user_source数据字典中
由DML语句隐含调用
显式调用
不允许使用COMMIT、SAVEPOINT和ROLLBACK(声明自治事务后可以使用)
允许使用COMMIT、SAVEPOINT和ROLLBACK
复合触发器
--基于表的复合触发器CREATEORREPLACETRIGGER 模式.触发器名
FOR dml事件子句 ON 模式.表名
COMPOUND TRIGGERS
--initial section (初始段)--declaration (声明)--subprograms (子程序)--optional section (可选段)
BEFORE STATEMENT IS...;--optional section (可选段)AFTER STATEMENT IS...;--optional section (可选段)
BEFORE EACH ROWIS...;--optional section (可选段)AFTER EACH ROWIS...;END 模式.触发器名;--基于视图的符复合触发器CREATEORREPLACETRIGGER 模式.触发器名
FOR dml事件子句 ON 模式.表名
COMPOUND TRIGGERS
--initial section (初始段)--declaration (声明)--subprograms (子程序)--optional section (独有的)
INSTEAD OF EACH ROWIS...;END 模式.触发器名;
时机
复合触发器程序段
在触发语句之前执行
BEFORE statement
在触发语句之后执行
AFTER statement
在触发语句影响的第一行之前执行
BEFORE EACH ROW
在触发语句影响的第一行之后执行
AFTER EACH ROW
CREATETABLE example (
exp_id NUMBER,
exp_value NUMBER
);INSERTINTO example VALUES(1,10);INSERTINTO example VALUES(1,20);INSERTINTO example VALUES(1,30);INSERTINTO example VALUES(2,15);INSERTINTO example VALUES(2,20);INSERTINTO example VALUES(3,40);INSERTINTO example VALUES(3,1);COMMIT;/*
--解决变异表错误
当插入数据的时候,判断插入的example.exp_value的值是否在同一exp_id的最大值和最小值之间,
如果在最大值和最小值之间,则允许插入,否则报错“插入的值不在取值范围内!”
*/CREATEORREPLACETRIGGER check_value
FORINSERTON example
WHEN(new.exp_id >=1AND new.exp_id <=3)
compound TRIGGER--声明数组类型TYPE exp_value_type ISTABLEOF example.exp_value%TYPEINDEXBY PLS_INTEGER;TYPE exp_id_type ISTABLEOF example.exp_id%TYPEINDEXBY PLS_INTEGER;--声明数组 用来批量绑定汇总后的example
min_value exp_value_type;
max_value exp_value_type;
p_exp_id exp_id_type;--将example_id作为下标与数组绑定
p_min_value exp_value_type;
p_max_value exp_value_type;--在触发之前先汇总example并放进数组中
BEFORE STATEMENT ISBEGIN--批量绑定SELECT
exp_id,MIN(exp_value),MAX(exp_value)BULK COLLECT INTO p_exp_id,min_value,max_value
FROM example
GROUPBY exp_id;FOR i IN1.. p_exp_id.count()LOOP
p_min_value(p_exp_id(i)) := min_value(i);
p_max_value(p_exp_id(i)) := max_value(i);ENDLOOP;END BEFORE STATEMENT;--在触发语句影响一行之前
BEFORE EACH ROWISBEGINIF :new.exp_value < p_min_value(:new.exp_id)OR :new.exp_value > p_max_value(:new.exp_id)THEN
raise_application_error(-20345,'插入的exp_value值不在取值范围内!');ENDIF;END BEFORE EACH ROW;END check_value;/--插入失败,报错“插入的值不在取值范围内!”INSERTINTO example VALUES(1,1);--成功插入INSERTINTO example VALUES(1,15);COMMIT;
DDL触发器
CREATE[ORREPLACE]TRIGGER trigger_name
BEFORE |AFTER
DDL |ALTER|DROP|CREATEONDATABASE|SCHEMA
trigger_body;
DDL事件
何时触发
CREATE
使用CREATE命令创建任何数据库对象时
ALTER
使用ALTER命令更改任何数据库对象时
DROP
使用DROP命令删除任何数据库对象时
/*
当当前用户执行DDL语句的时候,像 example 表中插入一条1的记录
*/CREATETABLE example (ID NUMBER);--可以这么写CREATEORREPLACETRIGGER tri_exp_ddl
AFTERCREATEORDROPORALTERONSCHEMABEGININSERTINTO example VALUES(1);END;/--也可以这么写CREATEORREPLACETRIGGER tri_exp_ddl
AFTER DDL
ONSCHEMABEGININSERTINTO example VALUES(1);END;/--当on后面指定database时,需要sys的权限
基于系统事件的触发器
CREATE[ORREPLACE]TRIGGER trigger_name
BEFORE |AFTER
database_event1 [OR database_event2 OR...]ONDATABASE|SCHEMA
trigger_body;
数据库事件
何时触发
AFTER SERVERERROR
一个ORACLE错误被跑出时
AFTER LOGON
一个用户登录数据库时
BEFORE LOGOFF
一个用户退出数据库时
AFTER STARTUP
开启数据库时
BEFORE SHUTDOWN
正常关闭数据库时
/*
当当前用户登录的时候,想example表中插入一条1的记录
*/CREATEORREPLACETRIGGER tri_exp_sysevent
AFTER logon
ONSCHEMABEGININSERTINTO example VALUES(1);END;/
触发器中的CALL语句
CREATEORREPLACEPROCEDURE hint_exp
ASBEGIN
dbms_output.put_line('正在删除example表中的数据!');END;/CREATEORREPLACETRIGGER after_exp_delete
AFTERDELETEON example
FOR EACH ROWCALL hint_exp --此处没有分号/
CREATEORREPLACEPROCEDURE exp_insert
IS
PRAGMA AUTONOMOUS_TRANSACTION;BEGININSERTINTO example VALUES(1);COMMIT;END;/--结果1被插入进去,2没有没插入进去BEGIN
exp_insert();INSERTINTO example VALUES(2);ROLLBACK;END;/