Orcale_04 使用PL/SQLF&自定义函数

1.PL/SQL概述. PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言(过程化SQL语言)；

PL/SQL是Oracle对SQL语言的扩展，扩展部分内容：变量、常量、存储过程、程序包、游标、函数、触发器、条件控制、循环控制等；

PL/SQL与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性；

PL/SQL语法特点：PL/SQL程序都是以块（block）为基本单位，整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以 begin开头，以end结尾）和异常处理部分（以Exception开头）。其中执行部分是必须的，其他两个部分可选。无论PL/SQL程序段的代码量有多大，其基本结构就是由这三部分组成。

2.变量和常量. 2.1变量. 2.1.1语法结构.

declare
变量名 数据类型；
begin 执行代码块内容；   
end；

2.1.2赋值方式. 方式一：“:=”

方式二：“select into 语句”

2.1.3案例演示. 根据给定的员工编号查询员工名字，并输出。

declare
sid number(7);          --声明变量 sid 用来保存员工编号(未初始化值)
sname varchar(10);  --声明变量 sname 用来保存查询出来的员工名字，未初始化值
begin
sid:=7499;                 --通过“:=”给变量 sid 赋值
select ename into sname from emp where empno=sid;           --通过 select into 语句给 sname 变量赋值
dbms_output.put_line('要查询的名字是：'||sname);              --打印出结果。
end;

在声明变量的同时也可以给变量赋值，如：sid number(7):=7499; 2.2常量 2.2.1语法结构.

decare
变量名1 constant 数据类型 := 值；
begin
执行代码块内容；
end；

2.2.2常量和变量的区别. 常量声明的时候要在数据类型前面加 constant 关键字，表示常量的意思；

常量必须初始值，且值不能被修改；

2.2.3案例演示. 根据给定的员工编号查询员工名字，并输出。

declare
sid constant number(7) := 7499;     --声明常量 sid 用来保存员工编号，并且初始化值
sname varchar(10);           --声明变量 sname 用来保存查询出来的员工名字，未初始化值
begin
select ename into sname from emp where empno=sid;          --通过 select into 语句给 sname 变量赋值
dbms_output.put_line('要查询的名字是：'||sname);            --打印出结果。
end;

3.%type和%rowtype. 3.1 %type. 作用：用来引用数据表里面某个字段的数据类型；

通俗解释：当我们想要声明一个变量来保存表内某个字段值但是又不知道该变量用什么数据类型的时候，我们就可以引用表内该字段的数据类型来作为变量的数据类型。

案例演示：根据员工编号查询出员工的姓名和入职日期。

declaresid number(7) :=7499;
sname varchar2(10);
mydate emp.hiredate%type;--通过%type引用 emp 表里面 hiredate 字段的数据类型。
beginselect ename,hiredate into sname,mydate from empwhereempno=sid;dbms_output.put_line('名字是：'||sname||'，入职日期是：'||mydate);
end;

3.2 %rowtype. 作用：可以声明一个基于表的行对象，也称为记录对象。案例：根据员工编号查询出名字和岗位(用一个变量保存)。

declaresid number :=7499;
mysum emp%rowtype;
beginselect * into mysum from emp where empno=sid;dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
end;

4.控制结构. 4.1条件控制语句 4.1.1 if语句语法结构：

if(条件1) then
条件1成立执行代码块
elsif(条件2) then
条件2成立执行代码块
.......
else
以上条件都不成立执行代码块
end if;

案例演示：根据学号查询薪水，如果薪水大于3500则“要纳税”，小于则“要努力”，等于则“刚刚好”。

declaresid number:=7499;
mysal emp.sal%type;
beginselect sal into mysal from emp where empno=sid;if(mysal>3500) thendbms_output.put_line('要纳税');elsif(mysal=3500) thendbms_output.put_line('刚刚好');elsedbms_output.put_line('要努力');end if;
end;

4.1.2 case语句语法结构：

begin
case '&变量'
when '值1' then 执行代码块1；
when '值2' then 执行代码块2；
when '值3' then 执行代码块3；
when '值4' then 执行代码块4；
..............
else 当以上值都不满足的时候执行代码块
end case;
end;

“&”：作用相等于Java里面的Scanner；

案例演示：输入等级，如果等级是 A，则输出：优异A；如果等级是 B，则输出：优异B；如果等级是 C，则输出：优异；否则，输出：成绩太差了！

begincase '&scode'when 'A' then dbms_output.put_line('优异A');when 'B' then dbms_output.put_line('优异B');when 'C' then dbms_output.put_line('优异C');when 'D' then dbms_output.put_line('优异D');else dbms_output.put_line('成绩太差了，来个F吧！！！');end case;
end;

4.2循环控制语句. 4.2.1 loop循环. 案例1：输出1-100之间所有数字；

declarei number:=0;
begin
<<aloop>>                              --给loop循环取别名：alooploopdbms_output.put_line(i);     --输出每一个值i:=i+1;                                   --i的值在叠加exit aloop when i=101;        --当i等于100的时候aloop循环退出 end loop;
end;

案例2：求1-100之间的和。

declarei number(7):=0;
sumb number(7):=0;               --保存和，注意这里不能定义sum
begin
<<myloop>>                             --给loop循环取
loopsumb:=sumb+i;i:=i+1;exit myloop when i=101;
end loop;
dbms_output.put_line(sumb);   --循环完成后输出最终求的和end;

4.2.2 while循环. 案例：求1-100之间的和。

declarei number(8):=0;
mysum number(8):=0;
beginwhile(i<=100) loopmysum:=mysum+i;i:=i+1;end loop;dbms_output.put_line(mysum); --循环完成后输出最终求的和
end;

4.2.3 for循环. 案例：求1-100之间的和。

declaremysum number(7):=0;
beginfor i in 1..100 loop   mysum:=mysum+i;end loop;dbms_output.put_line(mysum);
end;

Orcale_04 使用PL/SQLF&自定义函数原创于 2022-04-30 14:48:07 发布 · 131 阅读 ·

1 ·0· CC 4.0 BY-SA版权文章标签： #sql #数据库 #oracle 文章目录 1.PL/SQL概述. 2.变量和常量. 2.1变量. 2.1.1语法结构. 2.1.2赋值方式. 2.1.3案例演示. 2.2常量 2.2.1语法结构. 2.2.2常量和变量的区别. 2.2.3案例演示. 3.%type和%rowtype. 3.1 %type. 3.2 %rowtype. 4.控制结构. 4.1条件控制语句 4.1.1 if语句语法结构： 4.1.2 case语句语法结构： 4.2循环控制语句. 4.2.1 loop循环. 4.2.2 while循环. 4.2.3 for循环. 5.异常处理. 5.1预定义异常/系统自带异常. 5.1.1 too_many_rows.u 5.1.2 no_data_found. 6.自定义函数. 1.PL/SQL概述. PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言(过程化SQL语言)；

PL/SQL是Oracle对SQL语言的扩展，扩展部分内容：变量、常量、存储过程、程序包、游标、函数、触发器、条件控制、循环控制等；

PL/SQL与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性；

2.变量和常量. 2.1变量. 2.1.1语法结构. declare 变量名数据类型； begin 执行代码块内容；
end； AI写代码 sql 1 2 3 4 5 2.1.2赋值方式. 方式一：“:=”

方式二：“select into 语句”

2.1.3案例演示. 根据给定的员工编号查询员工名字，并输出。

declare sid number(7); --声明变量 sid 用来保存员工编号(未初始化值) sname varchar(10); --声明变量 sname 用来保存查询出来的员工名字，未初始化值 begin sid:=7499; --通过“:=”给变量 sid 赋值 select ename into sname from emp where empno=sid; --通过 select into 语句给 sname 变量赋值 dbms_output.put_line('要查询的名字是：'||sname); --打印出结果。 end; AI写代码 sql 1 2 3 4 5 6 7 8 在声明变量的同时也可以给变量赋值，如：sid number(7):=7499; 2.2常量 2.2.1语法结构. decare 变量名1 constant 数据类型 := 值； begin 执行代码块内容； end； AI写代码 sql 1 2 3 4 5 2.2.2常量和变量的区别. 常量声明的时候要在数据类型前面加 constant 关键字，表示常量的意思；

常量必须初始值，且值不能被修改；

2.2.3案例演示. 根据给定的员工编号查询员工名字，并输出。

declare sid constant number(7) := 7499; --声明常量 sid 用来保存员工编号，并且初始化值 sname varchar(10); --声明变量 sname 用来保存查询出来的员工名字，未初始化值 begin select ename into sname from emp where empno=sid; --通过 select into 语句给 sname 变量赋值 dbms_output.put_line('要查询的名字是：'||sname); --打印出结果。 end; AI写代码 sql 1 2 3 4 5 6 7 3.%type和%rowtype. 3.1 %type. 作用：用来引用数据表里面某个字段的数据类型；

案例演示：根据员工编号查询出员工的姓名和入职日期。

declaresid number(7) :=7499; sname varchar2(10); mydate emp.hiredate%type;--通过%type引用 emp 表里面 hiredate 字段的数据类型。 begin select ename,hiredate into sname,mydate from emp whereempno=sid; dbms_output.put_line('名字是：'||sname||'，入职日期是：'||mydate); end; AI写代码 sql 1 2 3 4 5 6 7 8 3.2 %rowtype. 作用：可以声明一个基于表的行对象，也称为记录对象。案例：根据员工编号查询出名字和岗位(用一个变量保存)。

declaresid number :=7499; mysum emp%rowtype; begin select * into mysum from emp where empno=sid; dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job); end; AI写代码 sql 1 2 3 4 5 6 4.控制结构. 4.1条件控制语句 4.1.1 if语句语法结构： if(条件1) then 条件1成立执行代码块 elsif(条件2) then 条件2成立执行代码块 ....... else 以上条件都不成立执行代码块 end if; AI写代码 sql 1 2 3 4 5 6 7 8 案例演示：根据学号查询薪水，如果薪水大于3500则“要纳税”，小于则“要努力”，等于则“刚刚好”。

declaresid number:=7499; mysal emp.sal%type; begin select sal into mysal from emp where empno=sid; if(mysal>3500) then dbms_output.put_line('要纳税'); elsif(mysal=3500) then dbms_output.put_line('刚刚好'); else dbms_output.put_line('要努力'); end if; end; AI写代码 sql

1 2 3 4 5 6 7 8 9 10 11 12 4.1.2 case语句语法结构： begin case '&变量' when '值1' then 执行代码块1； when '值2' then 执行代码块2； when '值3' then 执行代码块3； when '值4' then 执行代码块4； .............. else 当以上值都不满足的时候执行代码块 end case; end; AI写代码 sql

1 2 3 4 5 6 7 8 9 10 “&”：作用相等于Java里面的Scanner；

案例演示：输入等级，如果等级是 A，则输出：优异A；如果等级是 B，则输出：优异B；如果等级是 C，则输出：优异；否则，输出：成绩太差了！

begin case '&scode' when 'A' then dbms_output.put_line('优异A'); when 'B' then dbms_output.put_line('优异B'); when 'C' then dbms_output.put_line('优异C'); when 'D' then dbms_output.put_line('优异D'); else dbms_output.put_line('成绩太差了，来个F吧！！！'); end case; end; AI写代码 sql 1 2 3 4 5 6 7 8 9 4.2循环控制语句. 4.2.1 loop循环. 案例1：输出1-100之间所有数字；

declarei number:=0; begin <<aloop>> --给loop循环取别名：aloop loop dbms_output.put_line(i); --输出每一个值 i:=i+1; --i的值在叠加 exit aloop when i=101; --当i等于100的时候aloop循环退出 end loop; end; AI写代码 sql 1 2 3 4 5 6 7 8 9 案例2：求1-100之间的和。

declarei number(7):=0; sumb number(7):=0; --保存和，注意这里不能定义sum begin <<myloop>> --给loop循环取 loop sumb:=sumb+i; i:=i+1; exit myloop when i=101; end loop; dbms_output.put_line(sumb); --循环完成后输出最终求的和

end; AI写代码 sql

1 2 3 4 5 6 7 8 9 10 11 12 4.2.2 while循环. 案例：求1-100之间的和。

declarei number(8):=0; mysum number(8):=0; begin while(i<=100) loop mysum:=mysum+i; i:=i+1; end loop; dbms_output.put_line(mysum); --循环完成后输出最终求的和 end; AI写代码 sql 1 2 3 4 5 6 7 8 9 4.2.3 for循环. 案例：求1-100之间的和。

declaremysum number(7):=0; begin for i in 1..100 loop
mysum:=mysum+i; end loop; dbms_output.put_line(mysum); end; AI写代码 sql 1 2 3 4 5 6 7 5.异常处理. 5.1预定义异常/系统自带异常. 当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发。

5.1.1 too_many_rows.u too_many_rows：行太多。意为：实际返回的行数超出请求的行数。异常演示：

declaresid number:=7499;
mysum emp%rowtype;
beginselect * into mysum from emp;dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);
end;

处理异常：

declaresid number:=7499;
mysum emp%rowtype;
beginselect * into mysum from emp;dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);exception when too_many_rows thendbms_output.put_line('太多行');
end;

5.1.2 no_data_found. no_data_found：行太少。意为：未查询到数据。异常演示：

declaresid number:=0000;
mysal emp.sal%type;
beginselect sal into mysal from emp where empno=sid;if(mysal>3500) thendbms_output.put_line('要纳税');elsedbms_output.put_line('要努力');end if;
end;

处理异常：

declaresid number:=0;
mysum emp%rowtype;
beginselect * into mysum from emp where empno=sid;dbms_output.put_line('名字是：'||mysum.ename||'，岗位是：'||mysum.job);exception when no_data_found thendbms_output.put_line('行太少');
end;

5.2自定义异常.

语法：

declare  异常变量 exception;beginif(触发异常条件) thenraise 异常变量;    else未触发异常执行的代码块end if;exception when 异常变量 then触发异常执行的代码块end;

案例演示：–根据工号查询工资，如果工资低于3500则抛出异常：‘你的工资太低了’。否则就输出：‘你的工资可以哟’。

declare  sid number:=7369;mye exception;mysal emp.sal%type;beginselect sal into mysal from emp where empno=sid;if(mysal<3500)thenraise mye;elsedbms_output.put_line('你的工资可以哟');end if;exception when mye thendbms_output.put_line('你的工资太低了');end;

6.自定义函数. 语法结构

create [or replace] function 函数名 (参数1,参数2)return 返回值类型  is|as   
定义返回变量(要取长度)；
begin函数要执行的sql语句return 变量名;          
end;

案例演示：定义函数：通过学号返回学生的名字。

create or replace function  fun_name(sid number)      --函数名，带参数return varchar2 is               --返回值的类型，注意这里不能指定数据类型的长度sname varchar2(10);          --定义返回的变量(要取长度)beginselect ename into sname from emp where empno=sid;return sname;                   --返回定义的变量snameend;

调用函数：