一, 概述
1, 每种数据库都有内部的语言
2, PL/SQL是 Oracle 中使用的编程语言
3, 编程语言
① 数据类型
② 语法
4, Procedural Language/SQL
过程化语言, 也就是带有分支循环的语言
SQL语言功能太单一, 需要结合其他的语言完成复杂的功能.
PL/SQL是oracle数据库对sql语句的扩展。
在普通SQL语句的使用上增加了编程语言的特点，
所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，
通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
二, PL/SQL
1, 匿名块
(1)格式
[declare
变量声明
]
begin
正常流程语句
[exception
异常处理
]
end
(2)HelloWorld
复制代码 代码示例:
SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line('Hello World!');
3 end;
4 /
Hello World!
注: 必须打开输出开关, 才能看到输出显示
(3)declare
① 变量名命名惯例, 以 "v_" 为前缀
② declare 变量名 类型;
③ 赋值语句-> 变量名 := 值;
复制代码 代码示例:
SQL> declare
2 v_name varchar2(20);
3 begin
4 v_name := 'myname';
5 dbms_output.put_line(v_name);
6 end;
7 /
myname
(4)exception
复制代码 代码示例:
SQL> declare
2 v_num number := 0;
3 begin
4 v_num := 1 / v_num;
5 dbms_output.put_line(v_num);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
error
2, 变量声明的规则
(1) 变量名不能使用保留字, 如 from, select 等
(2) 第一个字符必须是字母
(3) 变量名最多包含30个字符
(4) 不要与数据库的表或者列同名
(5) 每一行只能声明一个变量
3, 常用变量类型
(1) binary_integer : 整数, 主要用来计数而不是用来表示字段类型.
(2) number : 数字类型
(3) char : 定长字符串
(4) varchar(2) : 变长字符串
(5) date : 日期
(6) long : 长字符串, 最大 2GB
(7) boolean : 布尔类型, 取值 true/false/null
4, 简单变量
(1)
复制代码 代码示例:
declare
v_temp number(1) := 0;
v_count binary_integer := 0;
v_sal number(7, 2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3, 2) := 3.14; -- 常量
v_valid boolean := false;
v_name varchar2(20) not null := 'MyName'; -- 不能取空值
begin
dbms_output.put_line('v_temp value:' || v_temp);
end;
(2) 变量的作用
通常用于存储某张表的字段的值.
(3) %type , 同步 表字段/其他变量 的类型
复制代码 代码示例:
declare
v_empno number(4); -- 自定义类型
v_empno2 emp.empno%type; -- emp表的empno字段的类型
v_empno3 v_empno2%type; -- v_empno2的类型
begin
dbms_output.put_line('test %type');
end;
5, 复杂变量 table record
(1) Table 变量类型 , 类似 java中的数组
注: 先声明类型, 在声明该类型的变量
复制代码 代码示例:
declare
type type_table_empno -- 声明 类型
is table of emp.empno%type -- 指定是 table类型 以及每个元素的类型
index by binary_integer; -- 指定下标的类型
-- 声明一个 type_table_empno 类型的变量
v_empnos type_table_empno;
begin
v_empnos(0) := 1111;
v_empnos(1) := 2222;
v_empnos(2) := 3333;
dbms_output.put_line(v_empnos(1));
end;
(2) Record 变量类型, 类似于 C语言中的结构体
①
复制代码 代码示例:
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'zhangsan';
dbms_output.put_line('v_temp.deptno ' || v_temp.deptno);
dbms_output.put_line('v_temp.dname ' || v_temp.dname);
end;
② 使用 %rowtype声明 record变量
复制代码 代码示例:
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 55;
v_temp.dname := 'zhangsan';
v_temp.loc := 'WuHan';
dbms_output.put_line('v_temp.deptno ' || v_temp.deptno);
dbms_output.put_line('v_temp.dname ' || v_temp.dname);
dbms_output.put_line('v_temp.loc ' || v_temp.loc);
end;
6, SQL语句的运用 into
(1) select into
注: 有且只能返回一行数据
① %type
复制代码 代码示例:
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename, v_sal
from emp
where empno = 7566;
dbms_output.put_line('v_ename ' || v_ename);
dbms_output.put_line('v_sal ' || v_sal);
end;
② %rowtype
复制代码 代码示例:
declare
v_emp emp%rowtype;
begin
select * into v_emp
from emp
where empno = 7566;
dbms_output.put_line('v_emp.empno ' || v_emp.empno);
dbms_output.put_line('v_emp.ename ' || v_emp.ename);
dbms_output.put_line('v_emp.sal ' || v_emp.sal);
end;
(2) insert into
复制代码 代码示例:
create table dept2 as select * from dept;
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'game';
v_loc dept.loc%type := 'WuHan';
begin
insert into dept2 values(v_deptno, v_dname, v_loc);
commit;
end;
(3) update -- sql%rowcount
复制代码 代码示例:
注: 关键字sql, 其属性rowcount, 刚刚执行的语句影响了几条记录
create table emp2 as select * from emp;
declare
v_deptno emp2.deptno%type := 10;
v_count number;
begin
update emp2 set sal = sal / 2 where deptno = v_deptno;
-- select count(*) into v_count from emp; --影响一条记录
dbms_output.put_line(sql%rowcount || ' 条记录被影响');
commit;
end;
7, 循环 loop
(1) 类似于 java中 do{ .. }while(); 循环
复制代码 代码示例:
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when ( i >= 11 );
end loop;
end;
(2) 类似于 java中 while(){} 循环
复制代码 代码示例:
declare
i binary_integer := 1;
begin
while i < 11 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
(3) 类似于 java中 for(){} 循环
① for each
复制代码 代码示例:
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
② 反转
复制代码 代码示例:
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
8, IF语句
①
格式:
if condition then
...
end if;
例子:
复制代码 代码示例:
declare
x number := 1;
begin
if x < 10 then
dbms_output.put_line(x || ' is less than 10');
end if;
end;
②
格式:
if condition then
...
else
...
end if;
例子:
复制代码 代码示例:
declare
x number(2) := 11;
begin
if x < 10 then
dbms_output.put_line(x || ' is less than 10');
else
dbms_output.put_line(x || ' is bigger than 10');
end if;
end;
③
格式:
if condition_1 then
...
elsif condition_2 then
...
else
...
end if;
举例:
复制代码 代码示例:
declare
x number(2) := 10;
begin
if x < 10 then
dbms_output.put_line(x || ' is less than 10');
elsif x < 20 then
dbms_output.put_line(x || ' is less then 20');
else
dbms_output.put_line(x || ' is bigger than 20');
end if;
end;
9, 异常处理
(1) too_many_rows
复制代码 代码示例:
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
(2) no_data_found
复制代码 代码示例:
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;
(3) 记录错误
复制代码 代码示例:
-- 记录错误的日志表
create table errorlog
(
id number primary key,
errorcode number,
errormsg varchar2(1024),
errordate date
);
-- 创建序列
create sequence seq_errorlog_id start with 1 increment by 1;
-- 出错后, 插入错误日志表, SQLCODE, SQLERRM
declare
v_deptno dept.deptno%type := 10;
v_errorcode errorlog.errorcode%type;
v_errormsg errorlog.errormsg%type;
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errorcode := SQLCODE;
v_errormsg := SQLERRM;
insert into errorlog
values(seq_errorlog_id.nextval, v_errorcode, v_errormsg, sysdate);
commit;
end;
-- clean
-- drop sequence seq_errorlog_id;
-- drop table errorlog;
10, case 语句
参考: http://www.jb200.com/db/12711.html
(1) CASE WHEN 表达式有两种形式
① 简单 case函数
复制代码 代码示例:
case sex
when '1' then '男'
when '2' then '女'
else '其他'
end
② case 搜索函数
复制代码 代码示例:
case
when sex = '1' then '男'
when sex = '2' then '女'
else '其他'
end
(2) CASE WHEN 在语句中不同位置的用法
① SELECT CASE WHEN 用法
复制代码 代码示例:
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生，2位女生*/
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;
② WHERE CASE WHEN 用法
复制代码 代码示例:
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1
WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1
ELSE 0
END) = 1
③ GROUP BY CASE WHEN 用法
复制代码 代码示例:
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL
END salary_class, -- 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL
END;
