使用PL/SQL&自定义函数(Oracle)

简介: 使用PL/SQL&自定义函数(Oracle)

1.PL/SQL概述.

  PL/SQL是过程语言PL与结构化查询语言SQL结合而成的编程语言。

  PL/SQL是针对Oracle数据库的;

  它是过程语言 + 结构化查询语言的结合;

  过程语言PL:如变量声明,流程的控制,循环等;

  查询语言SQL:SQL语言,如增、删、改、查等;

 

  PL/SQL是SQL的扩展版,SQL能做的,PL/SQL绝大多数都能做。

2.PL/SQL的优点:

  1.支持SQL:数据操纵命令,事务控制命令,游标控制,SQL函数和SQL运算符;

  2.支持面向对象编程;

  3.可移植性,可运行在任何操作系统上;

  4.经过编译执行,性能佳;

  5.与SQL紧密集成,简化数据处理,支持SQL数据类型,支持NULL值,支持%type和%rowtype属性类型(oracle中最有意思的);

  6.安全性

PL/SQL分成三个部分:

  1. 声明部分

  2. 可执行部分

  3. 异常片理部分

语法结构:

[declare declaration]  --声明部分

begin

   executable  statements  --可执行部分

   [exception  handlers]  --异常区

end;

输出

select 'abc' from dual;

dbms_output.put_line('abc');

   --打印输出(必带begin)

   begin

       dbms_output.put_line('abc');

  end

赋值:( := )

   --变量声明赋值,并打印

   declare i number(6);

   begin

       i:=77;   //:=赋值,select...into也是赋值

       dbms_output.put_line(i);

   end;

--在emp表中将工号为7369的姓名输出

declare sid number;

sname varchar2(22);

begin

   sid:=7369;

   select ename into sname from emp where empno=sid;  --select...into赋值方式

   dbms_output.put_line(sname);

end;

提示下:在begin里面用select语句,必定要用select…into

数据类型:

1.标量类型

2.LOB类型

3.属性类型:

 %type:提供某个变量或数据库表列的数据类型

 %rowtype:提供表中一行的记录类型(非常有特色)

3.1 %type

--求7369的入职日期(在不知道该列是什么类型的情况下)

--申请一个与“入职日期”一样的

declare sid number;

shiredate emp.hiredate%type;  --声明个变量,它的类型与表中某个列的类型一样

begin

   sid:=7369;

   select hiredate into shiredate from emp where empno=sid;

   dbms_output.put_line(shiredate);

end;

--也可以sb shiredate%type;

--求某某的所有信息

declare sid number;

sname emp.ename%type;

sjob emp.job%type;

begin

   sid:=7369;

   select ename,job into sname,sjob from emp where empno=sid;

   dbms_output.put_line(sname||'   '||sjob);

end;

当然,要是表中有很多的列,还像以上这么写吗?

NO,使用行类型:%rowtype;

--查询某某的所有信息

declare sid number;

er emp%rowtype;

begin

   sid:=7369;

   select * into er from emp where empno=sid;

   dbms_output.put_line(er.ename||'   '||er.job);

end;

输入& (一般做测试用,其它情况不怎么用)

declare sid number;

er emp%rowtype;

begin

   sid:=&请输入;  --&类似scanner

   select * into er from emp where empno=sid;

   dbms_output.put_line(er.ename||'   '||er.job);

end;

 注意:sid:=&请输入;  --代表录入的是整型

   sid:='&请输入'; --代表录入的是varchar2类型

逻辑比较:

<> , !=

控制语句:

if 条件 then

……

else或者elsif…then

……

end if

--工资大于3500交税,=3500刚好,<3500努力

if语句:

begin

 if sal>3500 then

     dbms_output.put_line('交税');

 elsif sal=3500 then

   dbms_output.put_line('刚好');

 else

   dbms_output.put_line('努力');

 end if;

end;

case语句:

case

   when   then ;

   when   then ;

   else

end case;

循环语句:有三种:

1.loop 无条件循环

2.while

3.for

--打印1~100

declare i number;

begin

   i:=1;

   loop

       dbms_output.put_line(i);

       i:=i+1;

       exit when i=100;    --退出及退出条件

   end loop;

end;

declare i number;

begin

   i:=1;

   <<b_loop>>  --loop循环的名字

   loop

       dbms_output.put_line(i);

       i:=i+1;

       exit b_loop when i=100;    --退出及退出条件

   end loop;

end;


--求1~100的和

declare i number;

mysum number;

begin

   i:=0;

   mysum:=0;

   while i<=100 loop

       mysum:=mysum+i;

       i:=i+1;

   end loop;

   dbms_output.put_line('总和:'||mysum);

end;

--循环里都会使用到loop

--for循环的写法:

declare

mysum number;

begin

   mysum:=0;

   for i in 1..100 loop

       mysum:=mysum+i;

   end loop;

   dbms_output.put_line(mysum);

end;

--求奇数之和

declare

mysum number;

begin

   mysum:=0;

   for i in 1..100 loop

       if mod(i,2)=1 then

       mysum:=mysum+i;

       end if;

   end loop;

   dbms_output.put_line(mysum);

end;

--顺序语句:goto 标名……<<标名>>

 


异常:

1.系统自带的异常---预定义异常

2.我们写代码报的异常---自定义异常

1.预定义异常

 too_many_rows :  行太多

 no_data_found :  数据未找到

例示:预定义异常:

declare sid number;

shiredate emp.hiredate%type;

sb shiredate%type;

begin

   sid:=7369;

   select hiredate into shiredate from emp;

   dbms_output.put_line(shiredate);

--异常

   exception  --发生异常就会报错

   when too_many_rows then

   dbms_output.put_line('行太多');

end;

例示:自定义异常

三步:声明,判断,捕捉

iee exception;

raise iee;

exception when iee then

   dbms_output.put_line('错误信息');

   when too_many_rows then

   dbms_output.put_line('错误信息');

   ……

   可以带多个异常体

===============================================

函数:

create or replace function  f_name [(参数1,参数2..)]

return 类型

is/as  

   

[local declarations]  --函数体里面的变量声明全放该位置

begin

   --执行体

   return

   --异常

end;

--案例:给编号,返回工资'交税'还是'刚好',还是'努力'

create or replace function f_n126(sid number)

return varchar2

is

   ssal number(8,2);

   str varchar2(22); --注意,该处不用declare定义

begin

   select sal into ssal from emp where empno=sid;

   if ssal>3500 then

       str:='交税';

   elsif ssal=3500 then

       str:='刚好';

   else

       str:='努力';

   end if;

   return str;

end;

--如何调用函数?

--oracle调用方式:

select f_n126(7369) from dual;

 

--pl/sql调用方式:

declare str varchar2(22);

begin

   str:=f_n126(7369);

   dbms_output.put_line(str);

end;

======自定义异常=========================================

declare

 异常变量exception;

 begin

   if(触发异常条件) then

     raise 异常变量;  

  else

    未触发异常执行的代码块

  endif;

  exceptionwhen异常变量then

            触发异常执行的代码块

 end;

declare n_s number(5);

e_my exception;

pragma exception_init(e_my,-20001);

begin

   select count(stuname) into n_s from stuinfo where stuname like '赵%';

   if n_s=0 then

       raise e_my;

   end if;

   dbms_output.put_line('数量是'||n_s);

   exception

       when e_my then

       dbms_output.put_line('人员为空');

end;

=====函数=================================================

create[or replace]function 函数名 (参数1,参数2)

return返回值类型  is|as  

定义返回变量(要取长度);

begin

 函数要执行的sql语句

 return 变量名;          

end;

create or replace function my_temp(n_s in varchar2, n_max in out number)

 return number is

 n_tavg number(5, 2);

begin

 select avg(stuage),max(stuage) into n_tavg,n_max from stuinfo where stuname like n_s||'%';

 return n_tavg;

end;

create or replace function my_sum(n_a in  number)

return number

is

n_sum number(5):=0;

begin

for int_s in 1..n_a loop

n_sum:=n_sum+int_s;

end loop;

return n_sum;

end;

 


相关文章
|
26天前
|
SQL Oracle 关系型数据库
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
74 0
|
1月前
|
SQL 存储 Oracle
oracle如何定期备份数据库sql文件
【1月更文挑战第7天】oracle如何定期备份数据库sql文件
48 8
|
1月前
|
SQL Oracle 关系型数据库
Oracle PL/SQL基础知识及应用案例
Oracle PL/SQL基础知识及应用案例
18 0
|
1月前
|
SQL Java 流计算
Flink SQL UDF(用户自定义函数)需要打包成JAR文件并上传到Flink集群中
【1月更文挑战第1天】【1月更文挑战第2篇】Flink SQL UDF(用户自定义函数)需要打包成JAR文件并上传到Flink集群中
41 0
|
1月前
|
SQL Oracle 关系型数据库
oracle查询数据库参数sql语句
oracle查询数据库参数sql语句
|
1月前
|
SQL Oracle 关系型数据库
oracle查询数据库状态sql语句
oracle查询数据库状态sql语句
|
1月前
|
SQL Oracle 关系型数据库
Oracle PL/SQL 第五章–复合类型
Oracle PL/SQL 第五章–复合类型
|
1月前
|
SQL 存储 Oracle
Oracle PL/SQL 第四章--数据类型
Oracle PL/SQL 第四章--数据类型
|
24天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
44 0
|
24天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
36 0

推荐镜像

更多