二、PL/SQL

pl/sql procedural language 过程化语言 是Oracle在标准的SQL基础之上的扩展，可以定义变量、使用逻辑控制语句

作用：默认Oracle一次只能处理一条语句，如果使用PL/SQL，可以使用块组织多条语句，作为一个整体执行，只向数据库服务器发送一次请求调用

pl/sql组成
块，最基本的单元，由块可组成不同的程序形式，如匿名块、存储过程、函数、包、触发器等
注释
– 单行注释
/*
多行注释
*/

命名规范：
变量 v_name v_age
常量 c_pi c_tax
过程 pro_calcSal
函数 fun_raiseSal
1.匿名块
1.定义匿名块：
declare：定义部分 —可选部分
Begin：执行部分 —-必选部分
Exception：异常处理部分 –可选部分
End;
—打开控制台输出开关
Set serveroutput on f5执行
——-往控制台输出helloworld
Begin
Dbms_output.put_line(‘helloworld’);
END;
–定义变量，赋值给变量
DECLARE
v_id NUMBER(3);
BEGIN
v_id:=1;
dbms_output.put_line(v_id);
END;
—-查询数据表中使用into的数据输出到控制台
DECLARE
v_name VARCHAR2(20);
v_sal NUMBER(7,2);
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=7788;
dbms_output.put_line(v_name ||’,’||v_sal);
END;
&：调用输入框接收输入数据
—-查询指定员工的薪水
SELECT * FROM emp WHERE empno=&NO;

SELECT * FROM emp WHERE UPPER(eNAME)=UPPER(‘&ename’);
添加异常处理：
DECLARE
v_name VARCHAR2(20);
v_sal NUMBER(7,2);
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&NO;
dbms_output.put_line(v_name ||’,’||v_sal);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘对不起，你输入的用户不存在’);
END;
%type:使用数据库中某一列的数据类型作为变量的数据类型
语法：表名.列名%type;
v_name emp.ename%type;
%rowtype:行类型，使用和数据库中某一个表的一行为数据类型
DECLARE
v_emp emp%rowtype;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=&no;
dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename || ‘,’ || v_emp.sal);
end;
2.使用returning … into 保存增删改表数据时的一些列的值
2.1增加数据时保存数据
Declare
V_ename emp.ename%type;
V_sal emp.sal%type;
Begin
Insert into emp(empno,ename,job,sal ) values (&no,’ 张三’,‘ddd’,2500)returning ename,dal into v_ename,v_sal;
Dbms_output.put_line(v_name||’,’||v_sal);
2.2修改数据时保存数据
2.3删除数据时保存数据
十八、用户管理
1. 创建用户：数据库的使用者
Sys身份登录
创建新用户：Create user 用户名 identified by 密码
Create user lwj identified by lwj
修改用户密码：alter user lwj identified by 123（新密码）
注意：在Oracle里，新建的用户对数据库没有任何操作权限
2. 赋权限
赋系统权限：GRANT 权限名 TO 用户名 |角色名[WITH ADMIN OPTION]
With admin option :允许权限的接收者再把权限传给其他用户
常用的系统权限：允许用户在创建数据库中执行的指定行为
Create session:登录权限
Create table:建表权限
Create sequence:创建序列权限
Unlimited tablespace:限制表空间

Grant create session,create table,create sequence,unlimited tablesapce
to lwj with admin option
回收系统权限： revoke 权限 from 用户名
赋用户权限：允许用户访问和操作一个指定的对象
grant 权限名 on 对象名 to 用户名|public |角色名[with grant option]
把员工表的查询权限给lwj: Grant select on scott.emp to lwj
赋予所有权限：Grant all on scott.emp to lwj
回收对象权限：revoke 权限名 on 对象名 from 用户名
Revoke select on scott.emp from lwj
3. 角色:是权限的集合，作用是简化权限的管理
DBA:所有权限，是oracle中预定义的角色
Resource：对实体进行操作，不能对结构操作
Connect :只能连接
可以使用角色给用户赋予权限
Grant 角色名 to 用户名：将角色拥有的权限赋给用户
——给一个用户赋所有权
Grant dba to 用户名；
自定义角色：create role 角色名 not identified;
4.修改密码
修改密码:Alter user 用户名 identified by 新密码
给用户解锁:Alter user 用户名 account unlock
锁定用户：alter user 用户名 account lock
5.删除用户：drop user 用户名 cascade
Drop user lwj cascade;
Cascade:表示级联删除
2.分支结构
1.if
If 条件 then 结果;
Elsif 条件 then 结果;
Else 结果;
End if;
—查询员工的薪水，如果大于3000，输出白领
DECLARE
v_sal emp.sal%TYPE;
v_result VARCHAR2(10);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=&NO;
IF v_sal>3000 THEN
v_result :=’白领’;
ELSIF v_sal>1500 AND v_sal<=3000 THEN
v_result :=’蓝领’;
ELSE v_result :=’灰领’;
END IF;
dbms_output.put_line(v_result);
END;
2.case
格式一：
Case when 条件 then 结果；
When 条件 then 结果；
Else 结果；
End case;
格式二：
Case 条件表达式 when 值1 then 结果1；
When 值2 then 结果2；
Else 结果n;
End case;
DECLARE
v_score NUMBER(4);
v_flag VARCHAR2(5);
–输入一个成绩，返回成绩的等级
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’;
WHEN 6 THEN v_flag :=’D’;
ELSE v_flag :=’E’;

END CASE;
dbms_output.put_line(v_flag);
END;
3.循环结构
3.1.直到型循环
Loop
执行循环体；
Exit when 条件；
End loop;
3.2while 循环
While 条件
Loop
执行循环体；
End loop;
3.3 for循环
for 循环变量 in 最小值..最大值
loop
执行循环体；
End loop;
练习：
创建一张表，只包含一个字段id
先在表中插入一条记录5
然后将10—20之间的数插入两遍
CREATE TABLE test1(
ID NUMBER(5)
)
INSERT INTO test1 VALUES(5);
SELECT * FROM test1

DECLARE
v_i NUMBER(5);
BEGIN
FOR v_i IN 1..2
LOOP
FOR v_i IN 10..20
LOOP
INSERT INTO test1 VALUES(v_i);
END LOOP;
END LOOP;
END;
3.存储过程
1)概述
存储过程用于执行特定的操作，完成某个功能，可以有输入参数in和输出参数out
2)语法
create [or replace] procedure 存储过程名 (参数1 in|out|in out 数据类型,参数2 in|out|in out 数据类型)
is | as
定义部分
begin
执行部分
exception
异常处理部分
end;
注：in表示输入参数,out表示输出参数,如果是输入参数，可以省略in，out不能省略
is或as是关键字，使用任意一个都可以
定义存储过程时形参不能指定类型的长度

例：编写一个存储过程，向表中添加数据
create or replace procedure pro_demo1
is
begin
insert into emp(empno,ename,sal) values (1234,’cs’,2000);
end;

调用存储过程：
exec 存储过程名(参数1,参数2);
call 存储过程名(参数1,参数2);
在块中调用存储过程

注：exec和call区别
exec是sqlplus命令
call是sql命令,任何工具都可以用，没有参数时调用时必须加()，例如：call pro_demo1();3.1创建一个没有参数的存储过程
3.2创建一个有输入参数的存储过程
3.3创建一个有输入参数也有输出参数的存储过程
例：编写一个存储过程，可以根据雇员编号，查询雇员姓名和工资
create or replace procedure pro_demo4 (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_ename emp.ename%type;
v_sal emp.sal%type;
begin
pro_demo3(&no,v_ename,v_sal);
dbms_output.put_line(‘姓名:’||v_ename||’,工资:’||v_sal);
end;

create or replace procedure pro_demo5 (no emp.empno%type,name out emp.ename%type,salary out emp.sal%type)
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=no;
name:=v_ename;
salary:=v_sal;
end;
4.函数
1)函数用于返回特定的结果，在创建函数时，在函数的头部使用return子句，指定返回值的类型，在执行部分使用return子句来返回结果
2)语法
create or replace function 函数名(参数1 in|out|in out 数据类型,参数2 in|out|in out 数据类型) return 数据类型
is | as
定义部分
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_demo1(no emp.empno%type) return varchar2
Is
V_ename emp.ename%type;
V_hiredate emp.hiredate%type;
V_sal emp.dal%type;
V_info varchar2(200);
Begin
Select ename,hiredate,sal into v_ename,v_hiredate,v_sal from emp where empno=no;
V_info :=v_name||’,’||v_hiredate||’,’||v_sal;
Return v_info;
End;
Select fun_demo2(7788) from dual;
存储过程和函数的区别：
1.返回值的区别，函数返回值只有一个，而存储过程是通过输出参数来返回，可以有多个
2.调用的区别，函数可以在查询语句中直接调用，而存储过程必须单独调用
3.应用场合的区别，函数一般用来计算并返回计算的结果，而存储过程一般用来执行特定的操作，如创建表、创建用户等DDL语句
4. 包
包用于组织存储过程、函数等（可包含若干个函数或存储过程），包分为两部分：包说明、包体
1)语法
create or replace package 包名
is | as
声明部分
end;

create package body 包名
is | as
存储过程或函数的实现
end;
–1.创建一个包，包含一个为雇员加薪的过程，一个为雇员减薪的过程
创建包
CREATE OR REPLACE PACKAGE pac_1
IS
PROCEDURE pro_addsal(NO emp.empno%TYPE,addsal emp.sal%TYPE);
PROCEDURE pro_jiansal(NO emp.empno%TYPE,jiansal emp.sal%TYPE);
END;
–实现包
CREATE OR REPLACE PACKAGE BODY pac_1
IS
PROCEDURE pro_addsal(NO emp.empno%TYPE,addsal emp.sal%TYPE)
IS
BEGIN
UPDATE emp SET sal=sal+addsal WHERE empno=NO;
END;
PROCEDURE pro_jiansal(NO emp.empno%TYPE,jiansal emp.sal%TYPE)
IS
BEGIN
UPDATE emp SET sal=sal-jiansal WHERE empno=NO;
END;
END;
–执行
EXEC pac_1.pro_addsal(7788,200);
SELECT sal FROM emp WHERE empno=7788
5．游标
1)概念
游标是一个指针，指向的是Oracle的缓冲区或内存区
分类：显式游标（查）、隐式游标（增删改）
显式游标：用户定义的游标，需要手动声明、打开、关闭，主要针对查询操作
隐式游标：Oracle自动管理的游标，不用声明、打开或关闭，主要针对增删改操作

2)显示游标使用的步骤（四步）
a)声明游标：cursor 游标名 is 查询语句
b)打开游标（游标不能重复打开）：open 游标名
c)读取游标的数据： fetch 游标名 into 变量名
b)关闭游标：close 游标名
3)游标属性
Notfound：是否没有数据，没有返回true，有返回false
%found：是否发现数据，如果发现返回true 否则返回false)
Isopen：是否是打开状态，是返回true,否则返回false
Rowcount：返回涉及的行数
语法：游标名%属性
1. 显式游标
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);
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);
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;
5. 异常
1. 预定义异常：有异常号，也有异常名
2. 非预定义异常：有异常号，没有异常名
3. 自定义异常：没有异常号，也没有异常名

1. 触发器
   1)概念
   触发器是一个特殊的程序，当触发某个事件时会自动执行，用户不能直接调用
   主要用于对数据库的特定操作或系统事件进行监听并响应

2)分类
DML触发器：触发的对象是表，触发事件：insert delete update
替代触发器：触发的对象是视图，也称为instead of触发器
系统触发器：系统事件或DDL

3)组成
触发事件
触发条件：可选
触发时间：触发器代码在触发事件完成前before还是完成后after执行
1. DML触发器：
1)语法
create or replace trigger 触发器名
before|after insert|delete|update[of 字段] on 表名
[for each row] –行触发器
[when 触发条件]
begin
触发体
end;
—如果有人想删除emp表中的数据，给出警告信息
Create or replace trigger tri_1
After delete on emp
Begin
Dbms_output.put_line(‘有人删除表中的数据，请注意！’);
End;