
小余
2023/02/13阅读:19主题:科技蓝
oracle基础--1
基础
PL/SQL结构
1.声明 2.执行 3.异常
declare
变量名字 类型;
begin
程序运行
exception
when 异常 then
处理
end;
语法
字符集
不区别大小写
包括:
所有大小写字母
数字
空白符
符号:+-*等
分隔符
分隔符 | 描述 |
---|---|
+,-, *, / | 加法,减法/负,乘法,除法 |
% | 游标属性绑定或代表任意字符的通配符 |
' | 字符串分隔符 |
. | 从属关系 |
, | 表达式或列表分隔符 |
: | 主机变量指示符 |
" | 引用标识符分隔符 |
= | 关系等 |
@ | 数据库链接符 |
; | 声明或语句终止符 |
:= | 赋值运算符 |
=> | 位置定位符 |
ΙΙ | 字符串连接运算符 |
** | 指数运算符 |
<<, >> | 标签分隔符(开始和结束) |
/*, */ | 多行注释分隔符(开始和结束) |
-- | 单行注释指示符 |
.. | 范围运算符 |
<, >, <=, >= | 关系运算符 |
<>, '=, ~=, ^= | 不同版本的”不等于”运算符 |
常量
1.字符型
2.数字
3.布尔
4.日期
注释
单行 --
多行 /* */
初值
每一行只能定义一个变量
constant代表常量必须赋初值
not null 关键字不行赋初值
:= / default 赋初值
sql语句
SELECT语句
select into语句 注意这个语句一次只能记录一个信息 如果没有查到则会发生NO_DATA_FOUND异常 如果查到多个则会产生TOO_MANY_ROWS异常 查询b员工的信息和id为109员工的名字
declare
v_emp emp%rowtype;
v_name emp.name%type;
begin
select * into v_emp from emp where name='b';
dbms_output.put_line(v_emp.id||' '||v_emp.salary);
select name into v_name from emp where id = 109;
dbms_output.put_line(v_name);
end;
DML语句
pl/sql中对dml语句进行了扩展,允许使用变量
控制结构
选择判断
if
if 条件 then 语句;
elsif 条件2 then 语句2;
else 语句3;
end if;
条件中:如果值为null则视为flase
case 语句
case
when 条件 then ..;
when ... then ...;
end case;
循环
简单循环
loop
.....;
exit [when 退出条件];
end loop;
while
while 进入条件 loop
.....;
end loop;
for
for 循环变量 in {reverse} 下界..上界
.....;
end loop;
1.循环变量不需要显示的定义 2.系统默认变量从下界到上界,如果使用reverse 则从上界到下界递减 3.循环变量只能在循环用
例子求1-100之间偶数和
//普通语句
declare
v_count binary_integer :=1;
v_sum number :=0;
begin
loop
if mod(v_count,2)=0 then
v_sum:=v_sum+v_count;
end if;
v_count := v_count + 1;
exit when v_count > 100;
end loop;
dbms_output.put_line(v_sum);
end;

declare
v_count binary_integer :=1;
v_sum number :=0;
begin
while v_count <=100 loop
if mod(v_count,2)=0 then
v_sum:=v_sum+v_count;
end if;
v_count := v_count + 1;
end loop;
dbms_output.put_line(v_sum);
end;

declare
v_sum number :=0;
begin
for v_count in 1..100 loop
if mod(v_count,2)=0 then
v_sum:=v_sum+v_count;
end if;
end loop;
dbms_output.put_line(v_sum);
end;

跳转结构
goto实现
<<>>标记
goto 标记;
pl/sql内部可以跳转,内可以跳到外,外不能跳到内
输入
虽然我不知道sql需要这种输入干嘛,不过要考,还是搞一下。
输入可以在定义阶段和代码阶段来进行,通过&符号来表示
变量:=&提示;
输出
dbms_output.put_line(输出的内容);
游标
前面我们知道了select into一次只能查询一条语句。
这里介绍利用游标处理select语句返回多行数据
还可以通过select……bulk collect into处理多行数据
游标分为:
显示游标:用户定义,多行select查询
隐式游标:系统自动,DML和单行select查询
显示游标
定义游标
语法:
cursor 名字 is select查询
1.必须在声明部分定义 2.定义时可以引用PL/SQL变量,但必须在游标定义前 3.定义时没有生成数据,只是保存在数据字典中。 4.可以使用名字%rowtype 定义记录类型的变量。
打开游标
open 变量名
1.只有打开游标才会,查询 2.一旦打开就无法再次打开,除非关闭 3.如果游标定义中变量值发生改变,则只能在下次打开才能其作用
检索游标/遍历
fetch 游标名 into 行名
关闭游标
close 名
游标属性
属性 | 数据类型 | 含义 |
---|---|---|
%ISOPEN | 布尔 | 游标是否打开 |
%FOUND | 布尔 | 判断最近一次使用fetch语句是否有数据 |
%NOTFOUND | 布尔 | 判断最近一次是否没有数据,没有返回true |
%ROWCOUNT | 数值 | 检索个数 |
%BULK_ROWCOUNT | 数值 | 第i给元素所影响的行数 |
循环检索实例
输出各个部门的平均工资 普通循环
declare
cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
v_dep c_dep%rowtype;
begin
open c_dep;
loop
fetch c_dep into v_dep;
exit when c_dep%notfound;
dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
end loop;
closse c_dep;
end;
while循环
declare
cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
v_dep c_dep%rowtype;
begin
open c_dep;
while c_dep%found loop
fetch c_dep into v_dep;
dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
end loop;
closse c_dep;
end;
for 循环 for循环会自动打开检索和关闭游标
declare
cursor c_dep IS select d_id,avg(salary) avgsfrom emps group by d_id;
begin
for v_dep in c_dep loop
dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
end loop;
end;
对于for循环而已,都是自动进行,所以可以不在声明部分定义游标,而是直接在for语句中使用,上面代码可以写成这样。
begin
for v_dep in (select d_id,avg(salary) avgsfrom emps group by d_id) loop
dbms_output.put_line(v_dep.avgs||' '||v_dep.d_id);
end loop;
end;
隐式游标
所有的sql语句都有一个执行的缓冲区,隐式游标就是该缓冲区的指针,又称sql游标
属性
属性 | 类型 | 说明 |
---|---|---|
SQL%isopen | 布尔 | 是否打开 |
SQL%found | 布尔 | 判断是否对数据库产生影响,如果插入、删除、修改或者查到数据则true |
SQL%notfound | 布尔 | 判断是否没有产生影响 |
SQL%rowcount | 布尔 | 影响行数 |
游标变量
定义
引用类型
type 名字 is ref cursor [return 类型]
系统预留一个叫sys_refcursor的引用类型
变量
名字 引用类型名字;
打开游标变量
open 游标 for select的sql;
检索游标
loop
fetch 名 into ...;
exit when 名%notfound;
end loop;
只能用简单循环和while,不能用for。
关闭游标
close 名;
异常
异常概述
异常分为:预定异常、非预定异常、用户异常。 常见异常:这些例举的是我遇到的较多的,详细的可以去搜。
预定义异常 | oracle错误 | 说明 |
---|---|---|
too_mangy_rows | ora-01422 | 返回多行数据 |
no_data_found | ora-01403 | 没有发现数据 |
zero_divide | ora-01476 | 除数为0 |
异常处理
异常定义
异常变量 exception;
还需要与一个oracle错误相关联 pragma exception_init(异常变量,错误号(-20999~-20000))
异常抛出
raise 异常;
异常捕获
exception
when 异常 then 处理;
...;
end;
others异常处理器
总是作为最后的一个异常处理器,处理没有被其他异常捕获的异常。 其中有2个函数
函数 | 描述 |
---|---|
sqlcode | 当前错误码 |
sqlerrm | 错误文本 |
例题
1.要求根据输入的不同表名进行不同处理,若表名为employees,则显示高于10号部门平均工资的员工信息。若表名为departments,则显示各个部门的人数。
完成这个需求有这样几个sql 查询高于10号部门平均工资的员工信息 select * from employees where salary > (select avg(salary) from employees where department_id =10); 各个部门的人数 select count(*) num ,department_id from employees group by department_id;
declare
v_table char(20);
type t_cursor is ref cursor;
v_cursor t_cursor;
v_emp employees%rowtype;
v_deptno employees.department_id%type;
v_num number;
begin
v_table:='$table_name';
if v_table = 'employees' then
open v_cursor for select * from employees where salary > (
select avg(salary) from employees where department_id =10);
while v_cursor%notfound loop
fetch v_cursor into v_emp;
dbms_output.put_line(v_emp....);
end loop
elseif v_table ='departments' then
open v_emp for select count(*) num ,department_id from employees group by department_id;
while v_cursor%notfound loop
fetch v_cursor into v_num,v_deptno;
dbms_output.put_line(v_deptno||' '||v_num);
end loop
else
raise_application_error(-20000,'Input must be ''emp'' or ''dept''');
end if
close v_cursor
end;
作者介绍
