一、匿名块
1、使用returning ... INTO 保存增删改表数据时的一些列的值
（01）增加数据时保存数据
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
INSERT INTO emp(empno,ename,JOB,sal,comm,deptno) VALUES(&no,'zhaoliu','deve',3000,500,10) RETURNING ename,sal INTO v_ename,v_sal;
dbms_output.put_line(v_ename || ','||v_sal);
end; ---打开控制台输出开关
SET serveroutput ON; (02)修改数据时保存数据
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%type;
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=&no RETURNING ename,sal INTO v_ename,v_sal;
dbms_output.put_line(v_ename || ','||v_sal);
END; (03)删除数据时保存数据
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
DELETE FROM emp WHERE empno=&no RETURNING ename,sal INTO v_ename,v_sal;
dbms_output.put_line(v_ename || ','||v_sal);
END; 二、分支结构
1、if
IF 条件 THEN 结果;
END IF; IF 条件 THEN 结果;
ELSE 结果;
END IF; IF 条件 THEN 结果;
elsif 条件 THEN 结果;
....
ELSE 结果;
END IF; ---查询一个员工薪水，判断员工薪水如果大于3000，输出他是一个白领；如果工资小于等于3000大于1500，输出他是一个蓝领；其它情况输出是一个灰领;
DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=&no;
IF v_sal>3000 THEN
dbms_output.put_line('白领');
elsif v_sal>1500 THEN
dbms_output.put_line('蓝领');
ELSE dbms_output.put_line('灰领');
END IF;
END; 2、case:
格式一：
CASE WHEN 条件 THEN 结果;
WHEN 条件 THEN 结果;
...
ELSE 结果;
END CASE; 格式二：
CASE 条件表达式 WHEN 值1 THEN 结果1;
WHEN 值2 THEN 结果2;
....
ELSE 结果m;
END CASE; ---输入一个成绩，判断该成绩等级，如果在99~90之间，返回为A，如果在89~80之间，返回为B，如果在79~70之间，返回C，其它返回为D
DECLARE
v_score NUMBER(3);
v_flag VARCHAR2(1);
BEGIN
v_score:=&score;
CASE trunc(v_score/10) WHEN 9 THEN v_flag:='A';
WHEN 8 THEN v_flag:='B';
WHEN 7 THEN v_flag:='C';
ELSE v_flag:='D';
END CASE;
dbms_output.put_line(v_score || '，该 成绩的等级为：' || v_flag);
END; 三、循环结构
1、直到型循环
loop
执行循环体;
exit WHEN 条件;
END loop; ---在控制台输出1~5
DECLARE
v_i NUMBER(1):=1;
BEGIN
loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
exit WHEN v_i>5;
END loop;
end; 2、while:
while 条件
loop
执行循环体;
END loop; ---在控制台输出1~5
DECLARE
v_i NUMBER(1):=1;
BEGIN
while v_i<=5
loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
END loop;
end; 3、fOR:
FOR 循环变量 IN 最小值..最大值
loop
执行循环体;
END loop; ---在控制台输出1~5
BEGIN
for v_i in 1..5
loop
dbms_output.put_line(v_i);
END loop;
end; 练习：
创建一张表，只包含一个字段id
先在表中插入一条记录5
然后将10—20之间的数插入两遍 四、存储过程
语法：
CREATE [OR REPLACE] PROCEDURE 存储过程名(形参1 IN|out 数据类型,形参2 IN|out 数据类型...)
IS | AS
定义部分;
begin
执行部分;
exception
异常部分;
END; 调用过程：
exec 存储过程名(实参...);
call 存储过程名(实参...); ---如果调用的是无参存储过程，过程名后面必须() 1、创建一个无参存储过程
CREATE PROCEDURE pro_demo1
IS
BEGIN
INSERT INTO dept VALUES(50,'deve','nanjing');
end; exec pro_demo1;
call pro_demo1(); 2、创建一个输入参数存储过程
---根据输入的员工工号和新的月薪修改数据库中该 员工的薪水
CREATE OR REPLACE PROCEDURE pro_demo1(NO emp.empno%TYPE,newsal emp.sal%TYPE)
is
BEGIN
update emp set sal=newsal where empno=no;
end; 一个为雇员加薪的过程
CREATE OR REPLACE PROCEDURE pro_demo1(NO emp.empno%TYPE,addsal emp.sal%TYPE)
is
BEGIN
UPDATE emp SET sal=sal+addsal WHERE empno=NO;
end; CALL pro_demo1(7788,10000); SELECT * FROM emp; 3、创建一个即有输入参数也有输出参数存储过程
---输出指定员工的姓名和工资
CREATE OR REPLACE PROCEDURE pro_demo2(NO emp.empno%TYPE,NAME out emp.ename%TYPE,salary out emp.sal%TYPE)
IS
BEGIN
SELECT ename,sal INTO NAME,salary FROM emp WHERE empno=NO;
END; --使用匿名块调用存储过程
DECLARE
v_name VARCHAR2(30);
v_sal NUMBER(7);
BEGIN
pro_demo2(&no,v_name,v_sal);
dbms_output.put_line(v_name || ',' || v_sal);
end; 五、函数
CREATE OR REPLACE FUNCTION 函数名(形参 数据类型,...) RETURN 数据类型
IS
定义部分;
BEGIN
执行部分;
exception
异常部分;
END; ---返回指定员工的年薪
CREATE OR REPLACE FUNCTION fun_demo1(NO emp.empno%TYPE) RETURN NUMBER
IS
v_salary NUMBER(10,2);
BEGIN
SELECT sal*12 INTO v_salary FROM emp WHERE empno=NO;
RETURN v_salary;
END; SELECT fun_demo1(7788) FROM dual; ---写一个函数，返回用户的姓名，入职日期，工资
CREATE OR REPLACE FUNCTION fun_demo2(NO emp.empno%TYPE) RETURN VARCHAR2
IS
v_ename emp.ename%TYPE;
v_hiredate varchar2(50);
v_sal emp.sal%TYPE;
v_info varchar2(200);
BEGIN
SELECT ename,to_char(hiredate,'yyyy-mm-dd'),sal INTO v_ename,v_hiredate,v_sal FROM emp WHERE empno=NO;
v_info:=v_ename ||','|| v_hiredate ||','|| v_sal;
RETURN v_info;
end; SELECT fun_demo2(7788) FROM dual; 存储过程和函数的区别：
1.返回值的区别，函数返回值只有一个，而存储过程是通过输出参数来返回，可以有多个
2.调用的区别，函数可以在查询语句中直接调用，而存储过程必须单独调用
3.应用场合的区别，函数一般用来计算并返回计算的结果，而存储过程一般用来执行特定的操作，如创建表、创建用户等DDL语句 五、包,里面包含的内容：存储过程，函数
语法：包括两个部分
声明部分：
CREATE OR REPLACE PACKAGE 包名
IS
声明内容;
END; 包体的实体：
CREATE OR REPLACE PACKAGE BODY 包名
is
实现部分;
end; CREATE OR REPLACE PACKAGE pac_demo1
IS
PROCEDURE pro_demo1(NO emp.empno%TYPE,salary emp.sal%TYPE);
PROCEDURE pro_demo2(NO emp.empno%TYPE);
FUNCTION fun_demo1(NO emp.empno%TYPE) RETURN NUMBER;
end; create or replace package body pac_demo1
IS
PROCEDURE pro_demo1(NO emp.empno%TYPE,salary emp.sal%TYPE)
is
BEGIN
UPDATE emp SET sal=salary WHERE empno=NO;
END;
PROCEDURE pro_demo2(NO emp.empno%TYPE)
IS
BEGIN
DELETE FROM emp WHERE empno=NO;
END;
FUNCTION fun_demo1(NO emp.empno%TYPE) RETURN NUMBER
IS
v_salary number(10,2);
BEGIN
SELECT sal*12 INTO v_salary FROM emp WHERE empno=NO;
RETURN v_salary;
END;
end; CALL pac_demo1.pro_demo1(7788,20000);
SELECT * FROM emp;
CALL pac_demo1.pro_demo2(7788);
select pac_demo1.fun_demo1(7369) from dual; --1.创建一个包，包含一个为雇员加薪的过程，一个为雇员减薪的过程
CREATE OR REPLACE PACKAGE pac_test1
IS
PROCEDURE addsal(NO emp.empno%TYPE,addsal emp.sal%TYPE);
procedure minussal(no emp.empno%type,minussal emp.sal%type);
END; CREATE OR REPLACE PACKAGE body pac_test1
IS
PROCEDURE addsal(NO emp.empno%TYPE,addsal emp.sal%TYPE)
is
BEGIN
UPDATE emp SET sal=sal+addsal WHERE empno=NO;
end;
PROCEDURE minussal(NO emp.empno%TYPE,minussal emp.sal%TYPE)
IS
BEGIN
UPDATE emp SET sal=sal-minussal WHERE empno=NO;
end;
END; --2.编写一个过程，验证用户登陆。如果用户名、密码匹配，输出Y，否则输出N emp表中的ename为用户名 empno为密码
CREATE OR REPLACE PROCEDURE pro_test2(username VARCHAR2,PASSWORD NUMBER,flag out VARCHAR2)
IS
v_count number(2);
BEGIN
SELECT count(empno) INTO v_count FROM emp WHERE ename=username AND empno=PASSWORD;
IF v_count>0 THEN flag:='Y';
ELSE flag:='N';
END IF;
END; --3.编写一个函数，根据雇员编号，计算调整后的工资
如果该雇员从事经理工作，且工资大于3000，则工资上涨20%，否则上涨35%
其他情况均上涨10%，输出雇员编号、姓名、上涨后的工资 CREATE OR REPLACE FUNCTION fun_test3(NO emp.empno%TYPE) RETURN VARCHAR2
IS
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_newsal emp.sal%TYPE;
v_job emp.job%type;
v_info varchar2(100);
BEGIN
SELECT ename,sal,JOB INTO v_ename,v_sal,v_job FROM emp WHERE empno=NO;
IF upper(v_job)='MANAGER' THEN
IF v_sal>3000 THEN
v_newsal:=v_sal*1.2;
ELSE v_newsal:=v_sal*1.35;
END IF;
ELSE v_newsal:=v_sal*1.1;
END IF;
v_info:= NO || ',' || v_ename || ',' || v_newsal;
RETURN v_info;
end; --4.创建一个包
包中含有一个存储过程，实现涨工资的功能，输入雇员姓名，如果其工作年限超过20年，工资增加2000
工作年限在10-20年，工资增加1000，工作年限在5-10年，工资增加500，输出雇员姓名和增涨后的工资
包中还含有一个函数，实现输入工资，判断工资如果大于4500，返回1，否则返回0 CREATE OR REPLACE PACKAGE pac_test4
IS
PROCEDURE pro_addsal(NAME emp.ename%TYPE,info out VARCHAR2);
FUNCTION fun_sal(salary emp.sal%TYPE) RETURN NUMBER;
end; CREATE OR REPLACE PACKAGE body pac_test4
IS
PROCEDURE pro_addsal(NAME emp.ename%TYPE,info out VARCHAR2)
IS
v_year NUMBER(5);
v_sal emp.sal%TYPE;
BEGIN
SELECT trunc(months_between(SYSDATE,hiredate)/12),sal INTO v_year,v_sal FROM emp WHERE upper(ename)=upper(NAME);
IF v_year>20 THEN v_sal:=v_sal+2000;
elsif v_year>=10 THEN v_sal:=v_sal+1000;
elsif v_year>=5 THEN v_sal:=v_sal+500;
END IF;
info:=NAME || ','||v_sal;
end;
FUNCTION fun_sal(salary emp.sal%TYPE) RETURN NUMBER
IS
v_flag NUMBER(1);
BEGIN
IF salary>4500 THEN
v_flag:=1;
ELSE v_flag:=0;
END IF;
RETURN v_flag;
end;
END; 六、游标：
1、显示游标（查）、隐式游标（增删改）
使用显示游标有四个步骤：
（01）：声明游标：cursor 游标名 IS 查询语句;
(02):打开游标：open 游标名;
(03):获取数据：fetch 游标名 INTO 变量名;
(04):关闭游标：close 游标名; 游标属性：
%found:是否发现数据，如果发现，返回true，否则返回false
%notfound:是否没有数据，如果没有，返回true，否则返回false
%isopen:是否打开，如果打开，，返回true，否则返回false
%rowcount:返回涉及的行数 DECLARE
CURSOR cur IS SELECT * FROM dept;
v_dept dept%rowtype;
BEGIN
OPEN cur;
fetch cur INTO v_dept;
loop
dbms_output.put_line(v_dept.deptno || ','|| v_dept.dname || ','||v_dept.loc);
fetch cur INTO v_dept;
exit WHEN cur%notfound;
END loop;
CLOSE cur;
end; ---使用for循环读取游标指向的数据，打开游标、获取游标数据、关闭游标都自动
DECLARE
CURSOR cur IS SELECT * FROM dept;
BEGIN
FOR v_dept IN cur
loop
dbms_output.put_line(v_dept.deptno || ','|| v_dept.dname || ','||v_dept.loc);
END loop;
end; 2、隐式游标：游标名为SQL,增删改 ---修改指定员工的工资，如果员工不存在，提示员工不存在，如果存在，输出更新数据的行数
BEGIN
UPDATE emp SET sal=sal*1.2 WHERE empno=&no;
IF SQL%found THEN
dbms_output.put_line('更新数据的行数为：' || SQL%rowcount);
ELSE
dbms_output.put_line('对不起，该用户不存在');
END IF;
end; 六、异常：
1、预定义异常:有异常号也有异常名
DECLARE
v_emp emp%rowtype;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=&no;
exception
WHEN no_data_found THEN
dbms_output.put_line('用户不存在');
WHEN too_many_rows THEN
dbms_output.put_line('返回了多行数据');
WHEN others THEN
dbms_output.put_line('程序出现了错误');
END; 2、非预定义异常：有异常号没有异常名
3、自定义异常：没有异常号也没有异常名 七、触发器
1、DML触发器： CREATE OR REPLACE TRIGGER 触发器名
AFTER|BEFORE INSERT|UPDATE|DELETE ON 表名
[FOR EACH ROW] ---行触 发器
BEGIN
执行体;
end; ---如果有人删除emp表中的数据，给出警告信息
CREATE OR REPLACE trigger tri_demo1
AFTER DELETE ON emp
BEGIN
dbms_output.put_line('有人删除了emp表中的数据，请注意');
end; DELETE FROM emp WHERE empno=7566; CREATE OR REPLACE TRIGGER tri_demo1
AFTER DELETE or update or insert ON emp
BEGIN
IF deleting THEN
dbms_output.put_line('有人在删除emp表的数据');
elsif inserting THEN
dbms_output.put_line('有人新增了数据');
elsif updating THEN
dbms_output.put_line('有人修改了数据');
end if;
END; DELETE INSERT UPDATE
:NEW 无 有效 有效
:old 有效 无 有效 CREATE OR REPLACE TRIGGER tri_demo3
before DELETE OR UPDATE OR INSERT ON emp
for each row
BEGIN
IF deleting THEN
dbms_output.put_line('有人在删除emp表的数据');
dbms_output.put_line(:old.ename ||','||:old.sal);
elsif inserting THEN
dbms_output.put_line('有人新增了数据');
dbms_output.put_line(:new.ename ||','||:new.sal);
elsif updating THEN
dbms_output.put_line('有人修改了数据');
dbms_output.put_line('原数据为：'||:OLD.ename ||','||:OLD.sal);
dbms_output.put_line('修改后的数据为：'||:new.ename ||','||:new.sal);
END IF;
END; 语句触发器：无论增加、删除、修改多少行数据，触发器只触发一次
行触发器：增加、删除、修改对于每一行涉及的数据，触发器都会执行 INSERT INTO emp(empno,ename,sal) VALUES(1000,'bbb',6000);
commit; DELETE FROM emp WHERE deptno=10;
commit;