一、PLSQL概述
1.什么是PL/SQL
* PL/SQL(Procedure Language/SQL).
* PLSQL是Oracle对sql语言的过程化扩展.
* 指在sql命令语言中增加了过程处理语句(如分支,循环等),是sql语言具有过程处理能力.
2.PL/SQL程序结构
declare
说明部分 (变量说明,光标说明,例外说明)
begin
语句序列(DML语句)...
exception
例外处理语句
end;
/
3.变量和常量说明
* 说明变量(char,varchar2,date,number,boolean,long)
var char(12);//说明变量名,数据类型和长度后用分号结束说明语句.
married boolean := true;
psal number(7,2);
my_name emp.ename%type;//引用型变量,即my_name的类型与emp表中的ename列的类型一样.
emp_rec emp%rowtype;// 记录型变量
* 记录型变量示例:
set serveroutput on //如果要在屏幕上输出信息,需要将serveroutput开关打开.
declare
emp_rec emp%rowtype; //定义记录型变量.
begin
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
4.if语句
格式:
if 条件 then 语句;
elsif 语句 then 语句;
else 语句;
end if;
示例:判断用户输入的数字
set serveroutput on
accept num prompt '请输入一个数字'; //prompt是提示的意思
declare
pnum number := #
begin
if pnum = 0 then
dbms_output.put_line('您输入的是0');
elsif pnum = 1 then
dbms_output.put_line('您输入的是1');
elsif pnum = 2 then
dbms_output.put_line('您输入的是2');
else
dbms_output.put_line('其它数字');
end if;
end;
/
注意:&num的意思是num绑定变量(建议尽量使用绑定变量)
例如:
select * from emp where deptno=10;
select * from emp where deptno=20;
-->可以优化成
select * from emp where deptno=#
5.循环语句
1.while loop示例:
set serveroutput on
declare
total number := 1;
begin
while total <=10 //这里没有分号.
loop
dbms_output.put_line(total);//结果输出1到10,且这里隐式转换了,数字转为字符串类型
total := total + 1;
end loop;
end;
/
2.loop exit[when 条件]示例:
set serveroutput on
declare
total number := 1;
begin
loop
exit when total>10;
dbms_output.put_line(total);//循环输出1到10
total := total + 1;
end loop;
end;
/
3.for i in 1..3 loop示例:
set serveroutput on
declare
total number := 1;
begin
for i in 1..10 //这里没有分号
loop
dbms_output.put_line(total);//循环输出1到10
total := total + 1;
end loop;
end;
/
6.光标(Cursor)==ResultSet
(1)光标基本用法
语法: cursor 光标名 [(参数名 数据类型[,参数名 数据类型])] is select 语句;
用途:用于存储一个查询返回的多行数据.
例如:cursor c1 is select ename from emp;
* 打开光标: open c1;(打开光标执行查询)
* 取一行光标的值: fetch c1 into pjob;(取一行到变量中,此pjob需要与emp表中的job类型一致)
* 关闭光标: close c1;(关闭游标释放资源)
* 光标的属性:
%isopen : 是否被打开
%rowcount : 行数
%notfound : 是否有值
(2)示例:按员工工种涨工资,总裁涨1000元,经理涨800,其他涨400.
set serveroutput on
declare
cursor cemp is select empno,job from emp;
cempno emp.empno%type;
cjob emp.job%type;
begin
open cemp;
loop
fetch cemp into cempno,cjob;
exit when cemp%notfound; //光标属性,%notfound 判断是否有值
if cjob = 'PRESIDENT' then update emp set sal=sa+1000 where empno=cempno;
elsif cjob = 'MANAGER' then update emp set sal=sal+800 where empno=cempno;
else update emp set sal=sal+400 where empno=cempno;
end if;
end loop;
close cemp;
commit; //别忘了提交
dbms_output.put_line('over!');
end;
/
(3)带参数的光标
示例:查询某个部门的员工姓名(需要把部门号传过去)
set serveroutput on
declare
cursor cemp(pdno number) is select ename from emp where deptno=pdno;
cname emp.ename%type;
begin
open cemp(20);
loop
fetch cemp into cname;
exit when cemp%notfound;
dbms_output.put_line(cname);
end loop;
close cemp;
end;
/
7.例外
例外相当于java中的异常.
(1)系统定义的例外
* No_data_found (没有找到数据)
* Too_many_rows (select...into语句匹配多个行)
* Zero_Divide (被零除)
* Value_error (算术或转换错误)
* Timeout_on_resource (在等待资源时发生超时)
示例:
set serveroutput on
declare
num number;
begin
num := 1/0;
exception
when Zero_Divide then dbms_output.put_line('1: 0 can not by divide!');
when Value_error then dbms_output.put_line('2L have error!');
when others then dbms_output.put_line('others exception!');
end;
/
(2)用户自定义例外
set serveroutput on
declare
cursor cemp is select ename from emp where deptno=50;
cname emp.ename%type;
no_data_found exception; //declare中定义例外
begin
open cemp;
fetch cemp into cname;
if cemp%notfound then
raise no_data_found; //引起例外
end if;
close cemp;
exception //例外处理.
when no_data_found then dbms_output.put_line('no data founded!');
when others then dbms_output.put_line('others exception!');
end;
/
8.注释
* -- this is a comment
* /* this is a comment */
1.什么是PL/SQL
* PL/SQL(Procedure Language/SQL).
* PLSQL是Oracle对sql语言的过程化扩展.
* 指在sql命令语言中增加了过程处理语句(如分支,循环等),是sql语言具有过程处理能力.
2.PL/SQL程序结构
declare
说明部分 (变量说明,光标说明,例外说明)
begin
语句序列(DML语句)...
exception
例外处理语句
end;
/
3.变量和常量说明
* 说明变量(char,varchar2,date,number,boolean,long)
var char(12);//说明变量名,数据类型和长度后用分号结束说明语句.
married boolean := true;
psal number(7,2);
my_name emp.ename%type;//引用型变量,即my_name的类型与emp表中的ename列的类型一样.
emp_rec emp%rowtype;// 记录型变量
* 记录型变量示例:
set serveroutput on //如果要在屏幕上输出信息,需要将serveroutput开关打开.
declare
emp_rec emp%rowtype; //定义记录型变量.
begin
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
4.if语句
格式:
if 条件 then 语句;
elsif 语句 then 语句;
else 语句;
end if;
示例:判断用户输入的数字
set serveroutput on
accept num prompt '请输入一个数字'; //prompt是提示的意思
declare
pnum number := #
begin
if pnum = 0 then
dbms_output.put_line('您输入的是0');
elsif pnum = 1 then
dbms_output.put_line('您输入的是1');
elsif pnum = 2 then
dbms_output.put_line('您输入的是2');
else
dbms_output.put_line('其它数字');
end if;
end;
/
注意:&num的意思是num绑定变量(建议尽量使用绑定变量)
例如:
select * from emp where deptno=10;
select * from emp where deptno=20;
-->可以优化成
select * from emp where deptno=#
5.循环语句
1.while loop示例:
set serveroutput on
declare
total number := 1;
begin
while total <=10 //这里没有分号.
loop
dbms_output.put_line(total);//结果输出1到10,且这里隐式转换了,数字转为字符串类型
total := total + 1;
end loop;
end;
/
2.loop exit[when 条件]示例:
set serveroutput on
declare
total number := 1;
begin
loop
exit when total>10;
dbms_output.put_line(total);//循环输出1到10
total := total + 1;
end loop;
end;
/
3.for i in 1..3 loop示例:
set serveroutput on
declare
total number := 1;
begin
for i in 1..10 //这里没有分号
loop
dbms_output.put_line(total);//循环输出1到10
total := total + 1;
end loop;
end;
/
6.光标(Cursor)==ResultSet
(1)光标基本用法
语法: cursor 光标名 [(参数名 数据类型[,参数名 数据类型])] is select 语句;
用途:用于存储一个查询返回的多行数据.
例如:cursor c1 is select ename from emp;
* 打开光标: open c1;(打开光标执行查询)
* 取一行光标的值: fetch c1 into pjob;(取一行到变量中,此pjob需要与emp表中的job类型一致)
* 关闭光标: close c1;(关闭游标释放资源)
* 光标的属性:
%isopen : 是否被打开
%rowcount : 行数
%notfound : 是否有值
(2)示例:按员工工种涨工资,总裁涨1000元,经理涨800,其他涨400.
set serveroutput on
declare
cursor cemp is select empno,job from emp;
cempno emp.empno%type;
cjob emp.job%type;
begin
open cemp;
loop
fetch cemp into cempno,cjob;
exit when cemp%notfound; //光标属性,%notfound 判断是否有值
if cjob = 'PRESIDENT' then update emp set sal=sa+1000 where empno=cempno;
elsif cjob = 'MANAGER' then update emp set sal=sal+800 where empno=cempno;
else update emp set sal=sal+400 where empno=cempno;
end if;
end loop;
close cemp;
commit; //别忘了提交
dbms_output.put_line('over!');
end;
/
(3)带参数的光标
示例:查询某个部门的员工姓名(需要把部门号传过去)
set serveroutput on
declare
cursor cemp(pdno number) is select ename from emp where deptno=pdno;
cname emp.ename%type;
begin
open cemp(20);
loop
fetch cemp into cname;
exit when cemp%notfound;
dbms_output.put_line(cname);
end loop;
close cemp;
end;
/
7.例外
例外相当于java中的异常.
(1)系统定义的例外
* No_data_found (没有找到数据)
* Too_many_rows (select...into语句匹配多个行)
* Zero_Divide (被零除)
* Value_error (算术或转换错误)
* Timeout_on_resource (在等待资源时发生超时)
示例:
set serveroutput on
declare
num number;
begin
num := 1/0;
exception
when Zero_Divide then dbms_output.put_line('1: 0 can not by divide!');
when Value_error then dbms_output.put_line('2L have error!');
when others then dbms_output.put_line('others exception!');
end;
/
(2)用户自定义例外
set serveroutput on
declare
cursor cemp is select ename from emp where deptno=50;
cname emp.ename%type;
no_data_found exception; //declare中定义例外
begin
open cemp;
fetch cemp into cname;
if cemp%notfound then
raise no_data_found; //引起例外
end if;
close cemp;
exception //例外处理.
when no_data_found then dbms_output.put_line('no data founded!');
when others then dbms_output.put_line('others exception!');
end;
/
8.注释
* -- this is a comment
* /* this is a comment */