Oracle笔记

Oracle笔记3

存储过程PROCEDURE

将SQL或者PL/SQL代码块集中用于完成特定功能的集合

CREATE [ OR REPLACE] PROCEDURE procedure_name [ ( parameter1 [
{ IN | OUT | IN OUT} param1_type,parameter2 [ { IN | OUT | IN OUT}
param2_type …… parameterN [ { IN | OUT | IN OUT} paramN_type]] {IS |
AS}
– 定义变量或者游标
BEGIN
– PL/SQL
END;

1， 存储过程参数不带取值范围，in表示传入，out表示输出
类型可以使用任意Oracle中的合法类型。
2， 变量带取值范围，后面接分号
3， 在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4， 用select 。。。into。。。给变量赋值
5， 在代码中抛异常用 raise+异常名

CREATE OR REPLACE PROCEDURE PRO_EMP(
P_USER_NAME IN NVARCHAR2, P_AMOUNT IN OUT NUMBER) 
AS
BEGIN
	UPDATE T_EMP2 SET SALARY=SALARY+P_AMOUNT WHERE USER_NAME=P_USER_NAME;
	SELECT SALARY INTO P_AMOUNT FROM T_EMP2 WHERE USER_NAME=P_USER_NAME;
END;

– 调用存储过程
– 如果没有参数，就不需要（）
– 如果是输出参数，就必须要用变量来传

DECLARE
	V_RESULT NUMBER:=10;
BEGIN
	PRO_EMP('张三',V_RESULT);
  DBMS_OUTPUT.PUT_LINE(V_RESULT);
END;

函数FUNCTION

与过程类似，是一组SQL语句或者PL/SQL语句块的集合，同时能够返回执行结果

CREATE [ OR REPLACE] FUNCTION function_name [ ( parameter1 [ { IN
|OUT | IN OUT} param1_type, parameter2 [ { IN | OUT | IN OUT}
param2_type …… parameterN [ { IN | OUT | IN OUT} paramN_type]]
RETURN returntype
{ IS | AS }
BEGIN
– PL/SQL
END;

CREATE OR REPLACE FUNCTION FUN_EMP(
P_USER_NAME IN NVARCHAR2, P_AMOUNT IN NUMBER) 
RETURN NUMBER
AS
V_RESULT NUMBER;
BEGIN
	-- UPDATE T_EMP2 SET SALARY=SALARY+P_AMOUNT WHERE USER_NAME=P_USER_NAME;
	SELECT SALARY INTO V_RESULT FROM T_EMP2 WHERE USER_NAME=P_USER_NAME;
  RETURN V_RESULT;
END;


DECLARE
	V_RESULT NUMBER:=10;
	V_RESULT2 NUMBER;
BEGIN
	V_RESULT2 := FUN_EMP('张三',V_RESULT);
  DBMS_OUTPUT.PUT_LINE(V_RESULT||','||V_RESULT2);
END;

存储过程，函数
函数有返回值，可用在sql语句中(对结果进行复杂的封装)

工龄满5年，薪水满4000，奖金500 工龄满5年，薪水满2000，奖金400 工龄满2年，薪水满4000，奖金300
工龄满2年，薪水满2000，奖金200;

CREATE OR REPLACE FUNCTION FUN_BONUS(P_GL IN NUMBER, P_SAL IN NUMBER)
RETURN NUMBER
AS
V_BONUS NUMBER;
BEGIN
	IF P_GL >= 5 AND P_SAL>=4000 THEN
		V_BONUS:=500;
	ELSIF P_GL>=5 AND P_SAL>=2000 THEN
		V_BONUS:=400;
	ELSIF P_GL>=2 AND P_SAL>=4000 THEN
		V_BONUS:=300;
	ELSIF P_GL>=2 AND P_SAL>=2000 THEN
		V_BONUS:=200;
	ELSE
		V_BONUS:=0;
	END IF;
	RETURN V_BONUS;
END;

SELECT USER_NAME,trunc(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) GL, trunc(SALARY), 
FUN_BONUS(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12,SALARY) BONUS FROM T_EMP2;

触发器

建立一个触发器，当对应表发生对应行为时触发

建立一个触发器, 当职工表 T_EMP 表被删除一条记录时， 把被删除记录写到职工表删除日志表中去，且加上字段删除人，删除时间

CREATE TABLE T_EMP_LOG AS SELECT * FROM T_EMP2 WHERE 1=2;
ALTER TABLE T_EMP_LOG ADD OPT_USER NVARCHAR2(10);
ALTER TABLE T_EMP_LOG ADD CREATE_TIME DATE;


CREATE OR REPLACE TRIGGER TRI_DEL_EMP
AFTER DELETE
ON T_EMP2
FOR EACH ROW
DECLARE
	V_OPT_NAME T_EMP_LOG.OPT_USER%TYPE;
BEGIN
	 SELECT USER INTO V_OPT_NAME FROM DUAL;
	INSERT INTO T_EMP_LOG(USER_ID,USER_NAME,HIREDATE,AGE,SALARY,DEPT_ID,OPT_USER,CREATE_TIME)
 VALUES(:OLD.USER_ID,:OLD.USER_NAME,:OLD.HIREDATE,:OLD.AGE,:OLD.SALARY,
:OLD.DEPT_ID,V_OPT_NAME,SYSDATE);
END;

TRUNC函数

参数是数字
TRUNC(M,N) 对M保留N位小数，不四舍五入，直接截断

SELECT TRUNC(-1111.23322,2) FROM DUAL;
TRUNC(-1111.23622,2)
-1111.23

参数是日期

其他类推

SELECT TO_CHAR(TO_DATE('2018-11-9','YYYY-MM-DD'),'MM')-TO_CHAR(TO_DATE('2017-11-9','YYYY-MM-DD'),'MM') FROM DUAL;
--结果为0
--字符串可直接运算
--按格式截取出来的其实仅是月(根据规定)

自定义异常

RAISE_APPLICATION_ERROR(-20999,'IS ERROR');
可用的已定义异常范围为 -20000 - -20999

写一个触发器，用于限制对t_emp表的修改（包括INSERT,DELETE,UPDATE）,
即不允许在非工作时间内修改t_emp表的数据。 工作时间定义为：每周一到周五的9:00到18:00。

DECLARE
V_FLAG NUMBER:=0;
BEGIN
IF TRUNC(SYSDATE,'DD')-TRUNC(SYSDATE,'DAY')>=1 AND TRUNC(SYSDATE,'DD')-TRUNC(SYSDATE,'DAY')<=5 THEN
IF TO_CHAR(SYSDATE,'HH24')-9>=0 AND TO_CHAR(SYSDATE,'HH24')-18<0 THEN
V_FLAG:=1;
END IF;
END IF;
IF V_FLAG=0 THEN
RAISE_APPLICATION_ERROR(-20999,'TIME IS ERROR');
END IF;
END;
--TRUNC(SYSDATE,'DAY')返回本周的第一天(返回的是时间，可和时间运算)
--TO_CHAR(SYSDATE,'DAY')返回本周第一天(星期日，MONDAY)，但是无法参与数学运算，可能返回中文和英文两种情况

TRUNCATE函数

删除表中所有数据,如果数据量大用DELETE较慢，TRUNCATE相当于删除表再重建，较快，但是只能全删。

TRUNCATE TABLE T_TEST;
DELETE FROM T_TEST;

索引

主键默认索引，为字段添加索引能加快查询速度，但插入速度变慢(增删改)。

CREATE INDEX IDX_ENAME ON T_EMP2(USER_NAME)

索引(INDEX)
建立索引的目的是：
提高对表的查询速度；
对表有关列的取值进行检查。

但是，对表进行insert,update,delete处理时，由于要表的存放位置记录到索引项中而会降低一些速度。
注意：一个基表不能建太多的索引；
空值不能被索引
只有唯一索引才真正提高速度,一般的索引只能提高30%左右