PL/SQL
*** PL/SQL ***
过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的数据库编程语言
PL提供流程控制的语句
SQL提供访问数据库的语句
PL/SQL是对sql的扩展 使其具有流程控制能力
PL/SQL的特点：
>支持sql的所有数据类型
>支持对象类型和集合类型的数据
>让sql具有选择和循环的流程控制能力
>具有可移植性和安全性
>可以创建函数,存储过程,触发器等 实现sql语句的复用
PL/SQL代码组成：pl代码+sql代码
PL/SQL结构组成：声明部分+可执行部分+异常捕获部分
基本格式：
declare
定义变量
begin
可执行sql语句
exception
异常捕获要执行的代码
end;
选择结构
/*** -- 选择结构：单分支 ***/
declare
resultsex char(3);
begin
/*给变量赋值方式1*/
resultsex:='男';
/*给变量赋值方式2*/
select sex into resultsex from student where tid=1;
dbms_output.put_line('resultsex'||resultsex);
if resultsex='男' then
dbms_output.put_line('resultsex='||resultsex||':称呼是:大帅哥！');
end if;
if resultsex='女' then
dbms_output.put_line('resultsex='||resultsex||':称呼是:小美女！');
end if;
exception
when others then
dbms_output.put_line('出错了!');
end;
-----------------------------------------------------------------------
-- 选择结构：双分支
declare
resultsex char(30);
begin
resultsex:='&请输入性别'; /*弹出一个输入框*/
dbms_output.put_line('resultsex='||resultsex);
if resultsex='男' then
dbms_output.put_line('帅哥！');
else
dbms_output.put_line('美女！');
end if;
exception
when others then
dbms_output.put_line('出错了！！！');
end;
-----------------------------------------------------------------------
-- 选择结构：多分支 使用if elsif
declare
score number(4,1);
begin
score:='&请输入分数';
dbms_output.put_line('分数='||score);
if score < 0 or score > 100 then
dbms_output.put_line('分数无效！');
elsif score < 60 then
dbms_output.put_line('不及格！');
elsif score < 70 then
dbms_output.put_line('及格！');
elsif score < 80 then
dbms_output.put_line('良好！');
elsif score < 100 then
dbms_output.put_line('优秀！');
else
dbms_output.put_line('满分！');
end if;
exception
when others then
dbms_output.put_line('出错了！！');
end;
-----------------------------------------------------------------------
-- 选择结构：多分枝 使用case
declare
score number(4,1);
scoreInt number(4,1);
begin
score:='&请输入分数';
scoreInt:=trunc(score/10);
case scoreInt
when 6 then
dbms_output.put_line('及格！');
when 7 then
dbms_output.put_line('良好！');
when 8 then
dbms_output.put_line('优秀！');
when 9 then
dbms_output.put_line('优秀！');
when 10 then
dbms_output.put_line('满分！');
else
dbms_output.put_line('不及格！');
end case;
exception
when others then
dbms_output.put_line('出错了！');
end;
练习
--1 判断输入的数是不是水仙花数
declare
n int;
sumn int;
a int; b int; c int;
begin
/*给n赋值*/
n:='&请输入一个三位数的整数：';/*&xxx获取值时 提示信息中不能有:*/
if n>=100 and n<1000 then
/*获取各个位数的值*/
a:=mod(n,10);
b:=mod(trunc(n/10),10);
c:=trunc(n/100);
/*获取三次方之和*/
sumn:=power(a,3)+power(b,3)+power(c,3);
if sumn=n then
dbms_output.put_line(n||'是三位数，是水仙花数！');
else
dbms_output.put_line(n||'是三位数，但不是水仙花数！');
end if;
else
dbms_output.put_line(n||'不是三位数！更不是水仙花数！');
end if;
exception
when others then
dbms_output.put_line('出错了！');
end;
-----------------------------------------------------------------------
-- 2 根据年龄和性别 判断称呼
declare
resultSex char(3);
resultAge int;
chengHu varchar(100);
begin
resultSex:='&请输入性别：';
resultAge:='&请输入年龄：';
dbms_output.put(resultSex||':::'||resultAge);
/*判断*/
if resultSex='男' then
if resultAge < 18 then
chengHu:='小男孩';
elsif resultAge < 35 then
chengHu:='帅哥';
elsif resultAge < 60 then
chengHu:='叔叔';
else
chengHu:='爷爷';
end if;
else
if resultAge < 18 then
chengHu:='小女孩';
elsif resultAge < 35 then
chengHu:='美女';
elsif resultAge < 60 then
chengHu:='阿姨';
else
chengHu:='奶奶';
end if;
end if;
dbms_output.put_line('->称呼是'||chengHu);
exception
when others then
dbms_output.put_line('出错了！');
end;
-----------------------------------------------------------------------
-- 3 输入年月日 判断距离今天的天数
declare
myYear int;
myMonth int;
myDay int;
days int;
myDate date;
begin
myYear:='&请输入年：';
myMonth:='&请输入月：';
myDay:='&请输入日：';
/*封装为date对象*/
myDate:=to_date(myYear||'-'||myMonth||'-'||myDay,'yyyy-mm-dd');
days:=abs(myDate-sysdate);
dbms_output.put_line(myYear||'-'||myMonth||'-'||myDay||':距离今天的天数是：'||days);
exception
when others then
dbms_output.put_line('出错了！');
end;
-----------------------------------------------------------------------
-- 4 判断指定年月的天数
declare
myYear int;
myMonth int;
days int;
begin
myYear:='&请输入年：';
myMonth:='&请输入月：';
case myMonth
when 4 then
days:=30;
when 6 then
days:=30;
when 9 then
days:=30;
when 11 then
days:=30;
when 2 then
/*判断闰年*/
if (mod(myYear,4)=0 and mod(myYear,100)!=0) or (mod(myYear,400)=0) then
days:=29;
else
days:=28;
end if;
else
days:=31;
end case;
dbms_output.put_line(myYear||'年'||myMonth||'月的天数是：'||days);
exception
when others then
dbms_output.put_line('出错了！');
end;
循环结构
-- 循环结构
-- loop while for
-----------------------------------------------------------------------
-- 1：循环结构: loop::不带条件的循环
/*
loop
循环执行的代码
if 条件 then
exit;
end if;
end loop;
*/
-----------------------------------------------------------------------
-- 打印从1到10
declare
i int;
begin
i:=1;
loop
if i > 10 then
exit;/*结束循环*/
end if;
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
-----------------------------------------------------------------------
-- 打印99乘法表
declare
i int:=1;
j int:=1;
begin
loop
j:=1;
loop
if j > i then
exit;
end if;
if i*j >=10 then
dbms_output.put(j||'*'||i||'='||i*j||' ');
else
dbms_output.put(j||'*'||i||'='||i*j||' ');
end if;
j:=j+1;
end loop;
if i > 9 then
exit;
end if;
i:=i+1;
dbms_output.put_line('');
end loop;
end;
-----------------------------------------------------------------------
-- 2：循环结构: while::带条件的循环
/*
while 条件 loop
循环执行的代码
end loop;
*/
-----------------------------------------------------------------------
-- 获取1到100内的数的和
declare
sumn int:=0;
n int:=1;
begin
while n<=100 loop
sumn:=sumn+n;
n:=n+1;
end loop;
dbms_output.put_line('1到100内的数的和='||sumn);
end;
-----------------------------------------------------------------------
-- 获取1到100内质数的和
declare
sumn int:=0;
n int:=1;
m int;
b boolean;
begin
/*定义一个变量让其从1跑到100*/
while n<=100 loop
/*判断当前数n是不是质数*/
m:=2;
b:=true;
while m<=sqrt(n) loop
if mod(n,m)=0 then
b:=false;
exit;
end if;
m:=m+1;
end loop;
if b and n!=1 then
dbms_output.put_line(n||'是质数！');
sumn:=sumn+1;
end if;
n:=n+1;
end loop;
dbms_output.put_line('1到100内的质数的和='||sumn);
end;
-----------------------------------------------------------------------
-- 3：循环结构: for::带条件的循环
/*
for n in a...b loop
循环执行的代码
end loop;
*/
-----------------------------------------------------------------------
-- 99乘法表
begin
for i in 1..9 loop
for j in 1..i loop
if i*j >=10 then
dbms_output.put(j||'*'||i||'='||i*j||' ');
else
dbms_output.put(j||'*'||i||'='||i*j||' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
end;
-----------------------------------------------------------------------
=======================================================================
-- 标记使用1：结束多层循环
begin
dbms_output.put_line('*');
<<a>> /*在此位置定义一个标记*/
for i in 1..9 loop
for j in 1..9 loop
dbms_output.put_line(i||':'||j);
if j=5 then
exit a; /*结束外层for循环*/
end if;
end loop;
end loop;
dbms_output.put_line('*');
end;
----------------------------------------------------------------------
-- 标记使用2：实现跳转到执行位置
begin
dbms_output.put_line('*');
for i in 1..9 loop
for j in 1..9 loop
dbms_output.put_line(i||':'||j);
if j=5 then
goto a; /*结束外层for循环,跳到标记位置*/
end if;
end loop;
end loop;
dbms_output.put_line('*');
<<a>> /*在此位置定义一个标记*/
dbms_output.put_line('*');
dbms_output.put_line('***');
end;
练习
-- 练习1：从1开始累加 判断加到几时 和第一次大于1000
declare
sumn int:=0; /*总数*/
n int:=1; /*步长123456...*/
begin
while sumn<=1000 loop
sumn:=sumn+n;
n:=n+1;
dbms_output.put_line(sumn||'--'||n);
end loop;
dbms_output.put_line('加到'||n||'时，和第一次大于1000');
dbms_output.put_line(sumn);
end;
-----------------------------------------------------------------------
-- 练习2：获取祖冲之计算到3.1415926到3.1415927时 计算的次数
-- 圆周率规律：4/1-4/3+4/5-4/7+4/9......
declare
fm number:=1;
pi number:=0.0;
cs int:=0;
begin
while pi > 3.1415927 or pi < 3.1415926 loop
pi:=pi+4/fm;
if fm<0 then
fm:=fm-2;
else
fm:=fm+2;
end if;
fm:= -fm;/*只有正数才能参与运算*/
cs:=cs+1;
end loop;
dbms_output.put_line('当圆周率到 3.1415926 和 3.1415927 之间时，需要计算'||cs||'次！');
end;
----------------------------------------------------------------------
-- 练习3： 输入一个字符串 打印其大小写转换 并删除数字后的结果
declare
str varchar(200):='&请输入一个字符串：';
d varchar(200);
c varchar(200);
begin
for i in 1..length(str) loop
d:=substr(str,i,1);
if ascii(d)>ascii('A') and ascii(d)<ascii('Z') then
d:=lower(d);
c:=c||d;
elsif ascii(d)>ascii('a') and ascii(d)<ascii('z') then
d:=upper(d);
c:=c||d;
else ascii(d)>ascii('9') and ascii(d)<ascii('0') then
c:=c||d;
end if;
end loop;
dbms_output.put_line(c);
end;
-----------------------------------------------------------------------
-- 练习4：获取1到10000内 所有的完数：n的所有因子之和等于n ：
-- 如果m可以除尽n m就是n的因子 本身不是本身的因子 举例:6->1+2+3
-- 普通写法：
declare
wnum int;
begin
for i in 1..10000 loop
wnum:=0;
for j in 1..i/2 loop
if mod(i,j)=0 then
wnum:=wnum+j;
end if;
end loop;
if wnum=i and i!=1 then
dbms_output.put_line(i);
end if;
end loop;
end;
-- 算法优化：
declare
counts int:=0;
begin
for i in 2..10000 loop
for j in 2..sqrt(i) loop
if mod(i,j)=0 then
counts:=counts+j+(i/j);
end if;
if mod(i,sqrt(i))=0 then
counts:=counts+sqrt(i);
end if;
end loop;
if i=counts+1 then
dbms_output.put_line(i);
end if;
counts:=0;
end loop;
end;
子程序
子程序: 有名字的plsql块->编译和运行在数据库中
子程序1: 存储过程 没有返回值的子程序 ：procedure
子程序2: 函数 有返回值的子程序 ：function
子程序的优点：
>模块化
一个子程序代表一个完整的功能
>复用性
定义完后 可以通过名字直接来调用
>提高维护性
直接定位方法 维护起来方便简单
>提高安全性
调用者只能看到子查询到的声明 无法干涉程序具体执行
可以通过设置权限 只让可信任的用户进行操作此程序
存储过程的格式：
create or replace procedure
过程名字(参数列表)
as|is
定义变量
begin
实现功能的plsql代码
exception
处理异常的plsql代码
end;
-----------------------------------------------------------------------
-- *** 调用存储过程 ***
-- 使用plsql块调用
begin
pro_1(参数);
end;
-- 使用execute--pl/sql developer软件不支持execute
-- 打开oracle的客户端命令行：sql plus
-- 开启客户端的输出流：set serverout on
-- 执行execute命令
execute pro_1(参数);
-----------------------------------------------------------------------
--1: 打印指定sid的学生的名字和分数信息
create or replace procedure
pro_1(sidParam int)
as
resultSname varchar(30);
resultScore number(4,1);
begin
select sname,sage into resultSname,resultScore
from student
where sid=sidParam;
dbms_output.put_line(sidParam||'学生是：'||resultSname||',分数是：'||resultScore);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
--2：特殊类型:%type->表里边什么类型，创建的属性就赋什么类型
create or replace procedure
pro_2(sidParam int)
as
resultSname student.sname%type;/*变量resultSname的数据类型和student.sname一致*/
resultScore student.score%type;/*变量resultScore的数据类型和student.score一致*/
begin
select sname,sage into resultSname,resultScore
from student
where sid=sidParam;
dbms_output.put_line(sidParam||'学生是：'||resultSname||',分数是：'||resultScore);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
--3：特殊类型:%rowtype
create or replace procedure
pro_3(sidParam int)
as
stu student%rowtype;/*stu变量的类型为student的行类型*/
begin
select * into stu from student where sid=sidParam;
dbms_output.put_line(sidParam||'学生是：'||stu.sname||',分数是：'||stu.score);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
练习
-- 练习1：从1开始累加 判断加到几时 和第一次大于1000
create or replace procedure
pro_4
is
n int:=1;
sumn int:=0;
begin
while sumn<1000 loop
sumn:=sumn+n;
n:=n+1;
end loop;
dbms_output.put_line('从1开始累加,加到'||(n-1)||'时,和第一次大于1000 sumn='||sumn);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
-- 练习2：圆周率计算到3.1415926到3.1415927时,计算的次数
-- 规律: 4/1-4/3+4/5-4/7
create or replace procedure
pro_5
is
pi float:=0;
npi float;
ciShu int:=0;
begin
while pi>3.1415927 or pi<3.1415926 loop
ciShu:=ciShu+1;
/*获取每次加入pi的值*/
npi:=4/(ciShu*2-1);
/*奇数加 偶数减 pi*/
if mod(ciShu,2)=0 then
pi:=pi-npi;
else
pi:=pi+npi;
end if;
end loop;
dbms_output.put_line('圆周率计算到3.1415926到3.1415927时,计算的次数为：'||ciShu);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
-- 练习3： 输入一个字符串 打印其大小写转换 并删除数字后的结果
create or replace procedure
pro_6
is
str varchar(100):='ABCabc123!@#$';
strNew varchar(100):='';
lengthStr int;
c varchar(3);
begin
/*获取长度*/
lengthStr:=length(str);
for i in 1..lengthStr loop
/*获取当前字符*/
c:=substr(str,i,1);
/*判断类型*/
if c>='a' and c<='z' then
strNew:=strNew||upper(c);
elsif c>='A' and c<='Z' then
strNew:=strNew||lower(c);
elsif c>'9' or c<'0' then
strNew:=strNew||c;
end if;
end loop;
dbms_output.put_line(str||'-->'||strNew);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
-- 练习4：获取1到10000内,所有的完数：n的所有因子之和等于n ：
-- 如果m可以除尽n m就是n的因子,本身不是本身的因子 举例: 6：1 2 3
create or replace procedure
pro_7
is
summ int;
begin
for i in 1..10000 loop
summ:=0;
for j in 1..i/2 loop
if mod(i,j)=0 then
summ:=summ+j;
end if;
end loop;
if summ=i and i!=1 then
dbms_output.put_line(i||'是完数！');
end if;
end loop;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
=======================================================================
-- 1 打印所有女生的基本信息：最高分 最低分 平均分 总人数
create or replace procedure
pro_11(sexParam char)
is
maxScore int;
minScore int;
avgScore int;
countStu int;
begin
select max(score),min(score),avg(score),count(*)
into maxScore,minScore,avgScore,countStu
from student
where sex=sexParam;
dbms_output.put_line('最高分：'||maxScore||' 最低分:'||minScore||' 平均分:'||avgScore||' 总人数:'||countStu);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
create or replace procedure
pro_12
is
zhiShuSum int:=0;
b boolean;
begin
for i in 100..1000 loop
b:=true;
for j in 2..i-1 loop
if mod(i,j)=0 then
b:=false;
end if;
end loop;
if b and instr(i,'1',1)!=0 then
dbms_output.put_line(i);
zhiShuSum:=zhiShuSum+i;
end if;
end loop;
dbms_output.put_line('质数的和为：'||zhiShuSum);
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-----------------------------------------------------------------------
-- 3 打印指定的二维乘法口诀
create or replace procedure
pro_13
is
hang int:=&请输入乘法表行列数：;
begin
for i in 1..hang loop
for j in 1..i loop
dbms_output.put(j||'*'||i||'='||i*j);
if i*j<10 then
dbms_output.put(' ');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
end;
-----------------------------------------------------------------------
-- 4 打印参数浮点类型,保留参数位数小数的四舍五入值
create or replace procedure
pro_14(numb float,w int)
is
newn float;
begin
newn:=round(numb,w);
dbms_output.put_line(newn);
end;
-----------------------------------------------------------------------
-- 5 判断参数int是不是质数
create or replace procedure
pro_15(numb int)
is
b boolean:=true;
begin
for i in 2..numb-1 loop
if mod(numb,i)=0 then
b:=false;
end if;
end loop;
if b then
dbms_output.put_line(numb||'：是质数！');
else
dbms_output.put_line(numb||'：不是质数！');
end if;
end;
-----------------------------------------------------------------------
-- 6 求参数字符串表示的时间距离当前时间有多少天
declare
myYear int:='&请输入年';
myMonth integer:='&请输入月';
myDay int:='&请输入日';
begin
pro_16(myYear,myMonth,myDay);
end;
-----------------------------------
create or replace procedure
pro_16(myYear int,myMonth int,myDay int)
is
days int;
myDate date;
begin
myDate:=to_date(myYear||'-'||myMonth||'-'||myDay,'yyyy-mm-dd');
days:=abs(myDate-sysdate);
dbms_output.put_line(myYear||'-'||myMonth||'-'||myDay||':距离今天的天数是：'||days);
exception
when others then
dbms_output.put_line('出错了！');
end;
----------------------------------------------------------------------
-- 7 获取参数字符串中所有数字组成的最大整数
create or replace procedure
pro_17(str varchar)
as
shuZi varchar(100);
maxSZ varchar(100);
begin
for i in 1..length(str) loop
if substr(str,i,1)>='0' and substr(str,i,1)<='9' then
shuZi:=shuZi||substr(str,i,1);
end if;
end loop;
dbms_output.put_line(shuZi);
for i in 0..9 loop
for j in 1..length(shuZi) loop
if substr(shuZi,j,1)=i then
maxSZ:=substr(shuZi,j,1)||maxSZ;
end if;
end loop;
end loop;
dbms_output.put_line(maxSZ);
end;
=======================================================================
-- 1 打印所有女生的基本信息： 最高分 最低分 平均分 总人数
-- 参数不能加精度
create or replace procedure
pro_1(sexParam char)
as
maxScore float;
minScore float;
avgScore float;
countRenShu int;
begin
select max(nvl(score,0)),min(nvl(score,0)),avg(nvl(score,0)),count(*)
into maxScore,minScore,avgScore,countRenShu
from student
where sex=sexParam;
dbms_output.put_line(sexParam||'生的统计信息是：最高分='||maxScore||',最低分='||minScore||',平均分='||avgScore||',总人数='||countRenShu);
exception
when others then
dbms_output.put_line('出错了！');
end;
begin
pro_1('女');
dbms_output.put_line('---------------');
pro_1('男');
end;
-----------------------------------------------------------------------
-- 2 打印100到1000内所有位数上含有1的质数的和
create or replace procedure
pro_2
as
sumn int:=0;
b boolean;
strn varchar(4);
begin
for n in 100..1000 loop
/*判断当前n是不是含有1*/
strn:=n||'';
/*字符串索引从1开始*/
if instr(strn,'1',1)!=0 then
/*判断当前数字是不是质数*/
b:=true;
for m in 2..n-1 loop
if mod(n,m)=0 then
b:=false;
exit;
end if;
end loop;
if b and n!=1 then
dbms_output.put_line(n||'是数上含有1的质数');
sumn:=sumn+n;
end if;
end if;
end loop;
dbms_output.put_line('100到1000内所有位数上含有1的质数的和='||sumn);
exception
when others then
dbms_output.put_line('出错了！');
end;
begin
pro_2();
end;
-----------------------------------------------------------------------
-- 3 打印指定的二维乘法口诀：
create or replace procedure
pro_3(n int)
as
begin
if n=-1 then
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(j||'*'||i||'='||i*j);
if i*j>=10 then
dbms_output.put(' ');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
elsif n=0 then
for i in 1..9 loop
for j in 1..9 loop
if (10-j)>=i then
dbms_output.put(j||'*'||(10-i)||'='||(10-i)*j);
end if;
if (10-i)*j>=10 then
dbms_output.put(' ');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
elsif n=1 then
for i in 1..9 loop
for j in 1..9 loop
if (10-i)>=(10-j) then
dbms_output.put((10-j)||'*'||(10-i)||'='||(10-i)*(10-j));
if (10-i)*(10-j)>=10 then
dbms_output.put(' ');
else
dbms_output.put(' ');
end if;
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
else
for i in 1..9 loop
for j in 1..9 loop
if i>=(10-j) then
dbms_output.put((10-j)||'*'||i||'='||i*(10-j));
if i*(10-j)>=10 then
dbms_output.put(' ');
else
dbms_output.put(' ');
end if;
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
end if;
end;
begin
pro_3(-1);
dbms_output.put_line('--------------------------------------------------');
pro_3(0);
dbms_output.put_line('--------------------------------------------------');
pro_3(1);
dbms_output.put_line('--------------------------------------------------');
pro_3(2);
end;
-----------------------------------------------------------------------
-- 4 打印参数浮点类型 保留参数位数小数的四舍五入值
-- 123.456789 3 ----123.456
create or replace procedure
pro_4(n number,weiShu int) /* 123.456789,3*/
as
n1 float;
n2 float;
n3 int;
n4 float;
n10 int:=power(10,weiShu);
begin
dbms_output.put_line('解决方法1：'||round(n,weiShu));
n1:=n*n10; /*123456.789*/
n3:=trunc(n1); /*123456*/
n2:=n1-n3;/*0.789*/
if n2>0.5 then
n3:=n3+1;
end if;
n4:=n3/n10;
dbms_output.put_line(n||'四舍五入取'||weiShu||'位='||n4);
exception
when others then
dbms_output.put_line('出错了！');
end;
begin
pro_4(123.456789,3);
end;
-----------------------------------------------------------------------
-- 5 判断参数int是不是质数
create or replace procedure
pro_5(n number)
as
b boolean:=true;
begin
for i in 2..n-1 loop
if mod(n,i)=0 then
b:=false;
exit;
end if;
end loop;
if b and n>1 then
dbms_output.put_line(n||'是质数');
else
dbms_output.put_line(n||'不是质数');
end if;
exception
when others then
dbms_output.put_line('出错了！');
end;
begin
pro_5(1);
end;
-----------------------------------------------------------------------
-- 6 求参数字符串表示的时间距离当前时间有多少天
create or replace procedure
pro_6(str varchar)
as
days int;
dateStr date;
begin
dateStr:=to_date(str,'yyyy-mm-dd hh24:mi:ss');
days:=abs(dateStr-sysdate);
dbms_output.put_line(str||' 距离当前时间的天数是：'||days);
exception
when others then
dbms_output.put_line('出错了！');
end;
begin
pro_6('2022-1-30');
end;
-----------------------------------------------------------------------
-- 7 获取参数字符串中所有数字组成的最大整数
create or replace procedure
pro_7(str varchar)
as
c varchar(3);
nStr varchar(4000):='';
cmax varchar(3);
indexMax int;
maxStr varchar(4000):='';
begin
/*获取str中所有的数字字符*/
for i in 1..length(str) loop
c:=substr(str,i,1);
if c>='0' and c<='9' then
nStr:=nStr||c;
end if;
end loop;
dbms_output.put_line(str||'---'||nStr);
/*对所有的数字字符进行排序*/
for i in 1..length(nStr) loop
/*获取nStr中最大的字符*/
cmax:=substr(nStr,1,1);
indexMax:=1;
for j in 1..length(nStr) loop
c:=substr(nStr,j,1);
if c>cmax then
cmax:=c;
indexMax:=j;
end if;
end loop;
/*把最大的字符加入maxStr中*/
maxStr:=maxStr||cmax;
/*把nStr中indexMax处的字符删除123 5 46*/
nStr:=substr(nStr,1,indexMax-1)||subStr(nStr,indexMax+1);
end loop;
dbms_output.put_line(str||'其中最大的整数是：'||maxStr);
exception
when others then
dbms_output.put_line('出错了！');
end;
begin
pro_7('@#!asd1773466546!@#!');
end;
存储过程 参数模式
参数列表：定义方法时 定义的变量列表 用于接收方法运行所需要的原始数据
返回值：方法调用后 返回给调用者的结果数据
参数列表：接收原始数据的变量
返回值：调用后的结果数据
存储过程：procedure:子程序：没有返回值的子程序
in模式的参数：接受原始数据的参数(默认)
在代码中不能给in模式的参数赋值
out模式的参数：用于返回结果数据的参数
in out模式的参数：既能接收原始数据 还能返回结果数据
-----------------------------------------------------------------------
-- 给用户开启一个空间
create tablespace wen datafile 'E:\oracle\WEN.DBF' size 10M;
-- 给用户设置密码，并把用户放到空间里
create user WEN identified by "123" default tablespace WEN;
-- 给用户赋予登录权限
grant create session to WEN;
-- 授予指定用户存储过程的使用权限
grant execute on WEN.pro_1 to WEN;
-- 撤销指定用户存储过程的使用权限
revoke execute on WEN.pro_1 from WEN;
-- 删除存储过程
drop procedure pro_1;
-----------------------------------------------------------------------
-- 打印两个int的和
create or replace procedure
pro_1(n1 in int,n2 in int) /*in模式的参数*/
as
sumn int;
begin
/* n1:=1+1; in模式参数 不能再次赋值 */
sumn:=n1+n2;
dbms_output.put_line(n1||'+'||n2||'='||sumn);
end;
-----------------------------------------------------------------------
-- 获取两个int的和
create or replace procedure
pro_2(n1 in int,n2 in int,sumn out int) /*sumn用于获取结果数据*/
as
begin
sumn:=n1+n2;
dbms_output.put_line(n1||'+'||n2||'='||sumn);
end;
declare
sum1 int:=1;/*此时的初始值没有意义*/
begin
pro_2(1,3,sum1);/*sum1参数用于接受存储过程中out模式的参数sumn*/
dbms_output.put_line('sum1='||sum1);
end;
-----------------------------------------------------------------------
-- 获取一个int数的二次方
create or replace procedure
pro_3(n in out int) /*in模式接收原始数据 out模式用于返回结果数据*/
as
begin
n:=power(n,2);
dbms_output.put_line(n||'的2次方='||n);
end;
declare
m int:=3;
begin
pro_3(m);
end;
-----------------------------------------------------------------------
-- 获取ab的和 并把a+1
create or replace procedure
pro_4(a in out int,b int,sumab out int)
as
begin
sumab:=a+b;
a:=a+1;
end;
declare
sum1 int;
aa int:=4;
begin
pro_4(aa,3,sum1);
dbms_output.put_line('aa='||aa||'，和='||sum1);
end;
-----------------------------------------------------------------------
-- 获取a的二次方 三次方 四次方 开方 并+1
create or replace procedure
pro_5(a1 in out int,a2 out int,a3 out int,a4 out int,akf out number)
as
begin
a2:=power(a1,2);
a3:=power(a1,3);
a4:=power(a1,4);
akf:=power(a1,0.5);
a1:=a1+1;
end;
declare
aa int:=10;
aa2 int;
aa3 int;
aa4 int;
aakf number;
begin
pro_5(aa,aa2,aa3,aa4,aakf);
dbms_output.put_line('aa='||aa||'，aa2='||aa2||'，aa3='||aa3||'，aa4='||aa4||'，aakf='||aakf);
end;
异常
异常：程序运行出现非正常清空
在plsql块中，正常代码出现异常 将终止执行正常代码 然后跳转到exception对应的代码
oracle中常见的异常：
ACCESS_INTO_NULL
未定义对象
CASE_NOT_FOUND
CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL
集合元素未初始化
CURSER_ALREADY_OPEN
游标已经打开
DUP_VAL_ON_INDEX
唯一索引对应的列上有重复的值
INVALID_CURSOR
在不合法的游标上进行操作
INVALID_NUMBER
内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND
使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS
执行 select into 时，结果集超过一行
ZERO_DIVIDE
除数为 0
SUBSCRIPT_BEYOND_COUNT
元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT
使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR
赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED
PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON
PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR
PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH
宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL
使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR
运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID
无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE
Oracle 在等待资源时超时
-----------------------------------------------------------------------
declare
a1 int;
begin
insert into student(sid,sname) values(1,'红尘');
a1:=1;
case a1
when 2 then
dbms_output.put_line('a1=2');
when 3 then
dbms_output.put_line('a2=3');
end case;
select sage into a1 from student;
select sage into a1 from student where sid=6;
select to_number('abc1') into a1 from dual;
a1:=2/0;
exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('出现异常：DUP_VAL_ON_INDEX：违反了唯一约束');
when SELF_IS_NULL then
dbms_output.put_line('出现异常：SELF_IS_NULL：调用null的方法');
when CASE_NOT_FOUND then
dbms_output.put_line('出现异常：CASE_NOT_FOUND：没有找到对应的case 并且没有else');
when TOO_MANY_ROWS then
dbms_output.put_line('出现异常：TOO_MANY_ROWS：数据超出一行');
when NO_DATA_FOUND then
dbms_output.put_line('出现异常：NO_DATA_FOUND：没有获取数据');
when INVALID_NUMBER then
dbms_output.put_line('出现异常：INVALID_NUMBER：数字转换异常');
when ZERO_DIVIDE then
dbms_output.put_line('出现异常：ZERO_DIVIDE：被0除！');
when others then
dbms_output.put_line('出现异常：出现其他异常！');
end;
自定义异常
declare
age int:='&请输入年龄';
MY_AGE_EXCEPTION exception; /*自定义异常*/
begin
/*指定出现自定义异常的条件*/
if age<=0 or age>120 then
raise MY_AGE_EXCEPTION; /*产生异常情况*/
end if;
dbms_output.put_line('age='||age);
exception
when MY_AGE_EXCEPTION then
dbms_output.put_line('出现异常：MY_AGE_EXCEPTION：年龄范围出错！');
/*通过弹出框 显示异常编号和异常原因*/
RAISE_APPLICATION_ERROR(-20001,'年龄范围出错:必须是[0,120]');
when others then
dbms_output.put_line('出现异常：出现其他异常！');
end;
函数
函数;function
有返回值的子程序：对功能的封装
格式:
create or replace function
函数名字(参数列表)
return 返回值类型
as|is
定义变量
begin
实现功能的代码块
esception
处理异常的代码块
end;
-----------------------------------------------------------------------
注意：
如果function的参数类型或者返回值值类型是sql支持的类型或者%type
这样的function可以被plsql调用也可以被select调用
如果function的参数类型或者返回值值类型是boolean或者%rowtype
这样的function只能被plsql调用，不能被select调用
-----------------------------------------------------------------------
-- 求一个int和一个double的和
create or replace function
f_1(a int,b float)
return float
as
sumab float;
begin
sumab:=a+b;
return sumab;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-- 调用函数：方式1：plsql块
declare
sumab float;
begin
sumab:=f_1(3,11.1);
dbms_output.put_line('sumab='||sumab);
end;
-- 调用函数：方式2：select
select f_1(2,11.1) from dual;
-----------------------------------------------------------------------
-- 无参数的函数
-- 获取当前时间对应的字符串：格式是xxxx年xx月xx日
create or replace function
f_2 /*和存储过程相同 如果没有参数列表()不能写*/
return varchar
as
str varchar(100);
begin
str:=to_char(sysdate,'yyyy"年"mm"月"dd"日"');
return str;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-- 调用函数：方式2：select
select f_2() from dual;
-----------------------------------------------------------------------
-- %type类型 可以被select调用
create or replace function
f_3(sidMy student.sid%type)
return student.sage%type
as
sageMy student.sage%type;
begin
select sage into sageMy from student where sid=sidMy;
return sageMy;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-- 调用函数：方式2：select
select f_3(2) FROM dual;
-----------------------------------------------------------------------
-- 判断boolean 可以被select调用
-- 如果b为true 获取a+1的值 如果b为false获取a-1的值
-- 参数是boolean
create or replace function
f_4(a int,b boolean)
return int
as
aa int;
begin
if b then
aa:=a+1;
else
aa:=a-1;
end if;
return aa;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-- 调用函数：plsql调用
declare
aa int;
begin
aa:=f_4(10,true);
dbms_output.put_line('aa='||aa);
end;
-- 调用函数：select
-- 参数是boolean的function 不能被select调用
select f_4(11,true) from dual;
----------------------------------------------------------------------
-- 返回值是boolean
create or replace function
f_5(a int )
return boolean
as
begin
return a>0;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-- 调用：通过plsql调用
declare
bb boolean;
begin
bb:=f_5(1);
if bb then
dbms_output.put_line('bb=true');
else
dbms_output.put_line('bb=false');
end if;
end;
-- 调用函数：select
--返回值是boolean的function 不能被select调用
select f_5(1) from dual;
-----------------------------------------------------------------------
-- 判断rowtype类型的参数是否可以被select调用
create or replace function
f_6(stu student%rowtype)
return int
as
sageMy int;
begin
sageMy:=stu.sage;
return sageMy;
exception
when others then
dbms_output.put_line('出现异常了！');
end;
-- 调用：通过plsql调用
declare
sageMy int;
stu student%rowtype;
begin
select * into stu from student where sid=2;
sageMy:=f_6(stu);
dbms_output.put_line('sageMy='||sageMy);
end;
-- 调用：通过select
declare
stu student%rowtype;
begin
select * into stu from student where sid=2;
--参数是%rowtype的function 不能被select调用
select f_6(stu) from dual;
end;
-----------------------------------------------------------------------
-- 判断rowtype类型的返回值是是否可以被select调用
create or replace function
f_7(sidMy int)
return student%rowtype
as
stu student%rowtype;
begin
select * into stu from student where sid=sidMy;
return stu;
end;
-- 调用：通过plsql调用
declare
stu student%rowtype;
begin
stu:=f_7(2);
dbms_output.put_line(stu.sid||','||stu.sname);
end;
-- 调用：通过select
select f_7(2) from dual;
练习
--1 判断一个数是不是质数
create or replace function
fun_1(n int)
return boolean
as
begin
for m in 2..sqrt(n) loop
if mod(n,m)=0 then
return false;
end if;
end loop;
return n>1;
end;
-- 调用
begin
for n in 1..100 loop
if fun_1(n) then
dbms_output.put_line(n||'是质数!');
end if;
end loop;
end;
-----------------------------------------------------------------------
--2 获取祖冲之老先生计算到3.1415926到3.1415927之间用了多少次
create or replace function
fun_2
return int
as
ciShu int:=0;
pi float:=0;
piNum float;
begin
while pi>3.1415927 or pi <3.1415926 loop
ciShu:=ciShu+1;
piNum:=4/(ciShu*2-1);
if mod(ciShu,2)=0 then
piNum:=piNum*(-1);
end if;
pi:=pi+piNum;
end loop;
return ciShu;
end;
-- 调用
begin
dbms_output.put_line('用的次数是：'||fun_2());
end;
-----------------------------------------------------------------------
--3 数学黑洞：一个四位数 所有位数不是完全重复 进行如下运算
-- 运算法则：当前数是n：取各个位数的值 组成一个最大值 再组成一个最小值 相减 循环操作
-- 最多7次 变为6174 7641-1467=6174
create or replace function
fun_3(n int)
return int
as
ciShu int:=0;
a int;b int;c int;d int; k int;
maxn int;minn int;
m int:=n;
begin
while m!=6174 loop
/*获取个位数的值*/
a:=mod(trunc(m/1),10);
b:=mod(trunc(m/10),10);
c:=mod(trunc(m/100),10);
d:=mod(trunc(m/1000),10);
for i in 1..3 loop
if a < b then
k:=a; a:=b; b:=k;
end if;
if b < c then
k:=b; b:=c; c:=k;
end if;
if c < d then
k:=c; c:=d; d:=k;
end if;
end loop;
/*获取最大值和最小值*/
maxn:=1000*a+100*b+10*c+d;
minn:=1000*d+100*c+10*b+a;
m:=maxn-minn;
ciShu:=ciShu+1;
end loop;
return ciShu;
end;
-- 调用
begin
for n in 1..10000 loop
if n>=1000 and n<10000 and mod(n,1111)!=0 then
dbms_output.put_line(n||':'||fun_3(n));
end if;
end loop;
end;
-----------------------------------------------------------------------
-- 4 获取参数年和参数月 有多少天
create or replace function
fun_4(myYear int,myMonth int)
return int
as
begin
case myMonth
when 4 then
return 30;
when 6 then
return 30;
when 9 then
return 30;
when 11 then
return 30;
when 2 then
if (mod(myYear,4)=0 and mod(myYear,100)!=0) or (mod(myYear,400)=0) then
return 29;
end if;
return 28;
else
return 31;
end case;
end;
-- 调用
begin
for myYear in 2000..2020 loop
for myMonth in 1..12 loop
dbms_output.put_line(myYear||'年'||myMonth||'月'||'天数是：'||fun_4(myYear,myMonth));
end loop;
end loop;
end;
-----------------------------------------------------------------------
-- 5 写一个函数fun_5(n) 打印参数行的空心菱形 n为大于等于5的奇数 n为总行数
/*
行数： 起始位置 结束位置
* 1 (n+1)/2 (n+1)/2
*** 2 (n+1)/2-1 (n+1)/2+1
***** 3 (n+1)/2-2 (n+1)/2+2
******* 4 (n+1)/2-3 (n+1)/2+3
i (n+1)/2+1-i n+1)/2-1+i
***** 1 2 n-1
*** 2 3 n-2
*
i i+1 n-i
*/
create or replace procedure
pro_5(n int)
as
begin
/*前(n+1)/2行*/
for i in 1..(n+1)/2 loop
for j in 1..n loop
if j >= ((n+1)/2+1-i) and j <=((n+1)/2-1+i) then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
/*后(n+1)/2-1行*/
for i in 1..(n+1)/2-1 loop
for j in 1..n loop
if j >= i+1 and j <=n-i then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
end;
-- 调用
begin
for n in 3 ..20 loop
if mod(n,2)!=0 then
pro_5(n);
end if;
end loop;
end;
--- 空心菱形
create or replace procedure
pro_5(n int)
as
begin
/*前(n+1)/2行*/
for i in 1..(n+1)/2 loop
for j in 1..n loop
if j = ((n+1)/2+1-i) or j =((n+1)/2-1+i) then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
/*后(n+1)/2-1行*/
for i in 1..(n+1)/2-1 loop
for j in 1..n loop
if j = i+1 or j =n-i then
dbms_output.put('*');
else
dbms_output.put(' ');
end if;
end loop;
dbms_output.put_line('');
end loop;
end;
-- 调用
begin
for n in 3 ..20 loop
if mod(n,2)!=0 then
pro_5(n);
end if;
end loop;
end;
游标
游标： cursor
类似于集合中的迭代器 用于遍历结果集
分类： 隐式游标：进行dml语句时 数据库自动创建的游标
显式游标：进行dql语句时 由程序员自己创建的游标
属性： %FOUND: 判断是否还有元素可以遍历
%NOTFOUND: 判断是否已无元素可以遍历
%ROWCOUNT: 获取影响的行数
%ISOPEN: 判断游标是否开启
隐式游标 默认统一名字SQL
默认是关闭的：%ISOPEN= false
/*
定义游标
cursor 游标名字 as select xxx
使用游标：
open c_1; 开启游标
fetch c_1 into 遍历
close c_1; 关闭游标
*/
-----------------------------------------------------------------------
-- 获取多行一列
declare
cursor c_1 is select score from student;/*定义游标*/
myScore float;
begin
open c_1;/*开启游标*/
loop
fetch c_1 into myScore;
if c_1%NOTFOUND then
exit;/*结束循环*/
end if;
dbms_output.put_line('myScore='||myScore);
end loop;
close c_1;/*关闭游标*/
end;
-----------------------------------------------------------------------
-- 获取多行多列
declare
cursor c_2 is select * from student;/*定义游标*/
stu student%rowtype;
begin
open c_2;/*开启游标*/
loop
fetch c_2 into stu;
if c_2%NOTFOUND then
exit; /*结束循环*/
end if;
dbms_output.put_line(stu.sid||'-'||stu.sname||'-'||stu.sage);
end loop;
close c_2;/*关闭游标*/
end;
----------------------------------------------------------------------
-- 获取多行多列
declare
cursor c_3 is select sid,sname,sage from student;
mysid student.sid%type;
mysage student.sage%type;
mysname student.sname%type;
begin
open c_3;
loop
fetch c_3 into mysid,mysname,mysage;
if c_3%NOTFOUND then
exit;
end if;
dbms_output.put_line(mysid||'-'||mysage||'-'||mysname);
end loop;
close c_3;
end;
-----------------------------------------------------------------------
-- 快捷写法1 获取多行多列
declare
cursor c_4 is select sid,sname,sage from student;
mysid student.sid%type;
mysname student.sname%type;
mysage student.sage%type;
begin
open c_4;
loop
fetch c_4 into mysid,mysname,mysage;
exit when c_4%notfound;
dbms_output.put_line(mysid||'-'||NVL(mysage,0)||'-'||mysname);
end loop;
close c_4;
end;
-----------------------------------------------------------------------
--快捷写法2 使用for遍历游标
/*
for 变量 in 游标 loop
操作变量的代码
end loop;
*/
-----------------------------------------------------------------------
-- 案例1：获取所有列
declare
cursor c_5 is select * from student;
begin
for stu in c_5 loop
dbms_output.put_line(stu.sid||'-'||stu.sname||'-'||stu.sage);
end loop;
end;
-- 案例2：获取部分列
declare
cursor c_6 is select sid,sname,sage from student;
begin
for stu in c_6 loop
dbms_output.put_line(stu.sid||'-'||stu.sname||'-'||stu.sage);
end loop;
end;
游标遍历
/*
游标：类似于集合的迭代器 用于遍历结果集
：cursor
格式：cursor 游标名 is select 语句
*/
-----------------------------------------------------------------------
-- 遍历游标1
declare
cursor c_1 is select * from student;
stu student%rowtype;
begin
open c_1;
loop
fetch c_1 into stu;
if c_1%notfound then
exit;
end if;
dbms_output.put_line(stu.sid||','||stu.sname||','||nvl(stu.score,0));
end loop;
close c_1;
end;
-----------------------------------------------------------------------
-- 遍历游标2
declare
cursor c_2 is select * from student;
begin
for stu in c_2 loop
dbms_output.put_line(stu.sid||','||stu.sname||','||nvl(stu.score,0));
end loop;
end;
游标对记录dml
/*
通过游标对记录进行dml
格式：cursor 游标名 is select语句 for update
*/
-----------------------------------------------------------------------
declare
cursor c_3 is select * from student for update;
begin
for stu in c_3 loop
dbms_output.put_line(stu.sid||','||stu.sname||','||nvl(stu.score,0));
if stu.sex='女' then
update student set score=score+1 where current of c_3;/*更改游标的当前行记录*/
else
delete student where current of c_3;/*删除游标的当前行记录*/
end if;
end loop;
commit;/*提交事务*/
dbms_output.put_line('修改成功！');
exception
when others then
rollback;/*回滚*/
dbms_output.put_line('修改失败！');
end;
包
结构
/*
包：package:对数据库对象进行分类管理
数据库对象：user table sequence index view cursor function procedure等
包=包声明+包体
包声明格式
create or replace package
包名
as|is
function 函数名(参数列表) return 返回值类型;
function 函数名(参数列表) return 返回值类型;
procedure 过程名(参数列表);
procedure 过程名(参数列表);
end 包名;
包体格式
create or replace package body
包名
as|is
function 函数名(参数列表) return 返回值类型
as
定义变量
begin
实现功能的代码块
return 返回值;
end 函数名;
function 函数名(参数列表) return 返回值类型
as
定义变量
begin
实现功能的代码块
return 返回值;
end 函数名;
procedure 过程名(参数列表)
as
定义变量
begin
实现功能的代码块
end 过程名;
procedure 过程名(参数列表);
procedure 过程名(参数列表)
as
定义变量
begin
实现功能的代码块
end 过程名;
procedure 过程名(参数列表);
end 包名;
*/
使用
-- 包声明
create or replace package
package_1
is
function f_1(n int) return boolean;/*判断是不是质数*/
function f_2(str varchar) return varchar;/*对字符串进行转换*/
procedure p_1(n in out int,m int);/*用n获取n+m*/
procedure p_2(str in out varchar);/*用s1获取s1所有不重复的字符*/
end package_1;
-----------------------------------------------------------------------
-- 包体
create or replace package body
package_1
is
function f_1(n int) return boolean
as
begin
for m in 2..n-1 loop
if mod(n,m)=0 then
return false;
end if;
end loop;
return n>1;
end f_1;
function f_2(str varchar) return varchar
as
strNew varchar(4000):='';
c varchar(3);
begin
for i in 1..length(str) loop
c:=substr(str,i,1);
if c>='a' and c<='z' then
c:=upper(c);
strNew:=strNew||c;
elsif c>='A' and c<='Z' then
c:=lower(c);
strNew:=strNew||c;
elsif c>'9'or c<'0'then
strNew:=strNew||c;
end if;
end loop;
return strNew;
end f_2;
procedure p_1(n in out int,m int)
as
begin
n:=n+m;
end p_1;
procedure p_2(str in out varchar)
as
strNew varchar(4000):=substr(str,1,1);
c varchar(3);
begin
for i in 2..length(str) loop
c:=substr(str,i,1);
dbms_output.put_line(c||'-'||instr(strNew,c));
if instr(strNew,c,1)=0 then
strNew:=strNew||c;
end if;
end loop;
str:=strNew;
end p_2;
end package_1;
-----------------------------------------------------------------------
-- 调用
begin
for n in 1..100 loop
if package_1.f_1(n) then
dbms_output.put_line(n||'是质数');
end if;
end loop;
end;
select package_1.f_2('asdASD!@#$') from dual;
declare
a int:=100;
begin
package_1.p_1(a,200);
dbms_output.put_line(a);
end;
declare
str varchar(20):='&请输入一个字符串：';
begin
package_1.p_2(str);
dbms_output.put_line('转换后：'||str);
end;
触发器
概念
/*
触发器：trigger:可以自动执行的存储过程
当指定事件发生时 自动执行的plsql块
注意：触发器不能被主动调用
作用：
--自动生成数据
--自定义复杂的安全权限
--提供审计和日志记录
--启用复杂的业务逻辑
分类：根据事件不同分类
-- ddl(模式)触发器：对表进行创建 修改表结构 删除表
-- dml触发器：执行dml语句：delete update insert
--- 行级触发器: dml语句执行时影响几行 触发器就执行多少次
--- 语句级触发器: dml语句执行一次 触发器只执行一次
-- 数据库级触发器： (略)
登录 退出 创建连接 关闭连接等事件时执行的触发器
*/
格式
注意：dml触发器 有三个boolean属性updating,deleting,inserting 获取当前dml语句的类型
格式1：
--dml语句级触发器
create or replace trigger
触发器名
after|before insert[or delete or update] on 表名
[declare 定义变量 ]
begin
plsql块
end;
格式2：
--dml行级触发器
create or replace trigger
触发器名
after|before insert[or delete or update] on 表名
for each row
[declare 定义变量 ]
begin
plsql块
end;
注意：行级触发器中有两个变量:old和:new 分别记录的是触发器执行前后的行的信息
/*
-- 启用触发器
alter trigger tri_1 enable ;
-- 禁用触发器
alter trigger tri_1 disable ;
-- 删除触发器
drop trigger tri_1;
-- 语句级触发器
*/
属性
/*
ddl触发器可以使用的属性：
ORA_CLIENT_IP_ADDRESS 客户端IP地址
ORA_DATABASE_NAME 数据库名称
ORA_DES_ENCRYPTED_PASSWORD 当前用户的DES算法加密后的密码
ORA_DICT_OBJ_NAME 触发DDL的数据库对象名称
ORA_DICT_OBJ_NAME_LIST 受影响的对象数量和名称列表
ORA_DICT_OBJ_OWNER 触发DDL的数据库对象属主
ORA_DICT_OBJ_OWNER_LIST 受影响的对象数量和名称列表
ORA_DICT_OBJ_TYPE 触发DDL的数据库对象类型
ORA_GRANTEE 被授权人数量
ORA_INSTANCE_NUM 数据库实例数量
ORA_IS_ALTER_COLUMN 如果操作的参数column_name指定的列，返回true,否则false
ORA_IS_CREATING_NESTED_TABLE 如果正在创建一个嵌套表则返回true,否则false
ORA_IS_DROP_COLUMN 如果删除的参数column_name指定的列，返回true,否则false
ORA_LOGIN_USER 触发器所在的用户名
ORA_PARTITION_POS SQL命令中可以正确添加分区子句位置
ORA_PRIVILEGE_LIST 授予或者回收的权限的数量。
ORA_REVOKEE 被回收者的数量
ORA_SQL_TXT 触发了触发器的SQL语句的行数。
ORA_SYSEVENT 导致DDL触发器被触发的时间
ORA_WITH_GRANT_OPTION 如果授权带有grant选项，返回true。否则false
*/
测试
-- 语句级触发器
create or replace trigger
t_1
after insert or update or delete
on student
declare
un varchar(100);
begin
select USERNAME into un from user_users;
if updating then
dbms_output.put_line(un||'对student表进行了update语句！');
elsif inserting then
dbms_output.put_line(un||'对student表进行了insert语句！');
elsif deleting then
dbms_output.put_line(un||'对student表进行了delete语句！');
end if;
end;
drop trigger t_1;
select * from student;
insert into student values(1,'叶晨','男',99,61.6);
update student set sname='叶星辰' where sname='叶晨';
delete from student where sid=1;
-----------------------------------------------------------------------
-- 行级触发器1
create or replace trigger
t_2
after insert or update or delete
on student
for each row
declare
un varchar(100);
begin
select username into un from user_users;
if updating then
dbms_output.put_line(un||'对student表进行了update语句！22');
elsif inserting then
dbms_output.put_line(un||'对student表进行了insert语句！22');
elsif deleting then
dbms_output.put_line(un||'对student表进行了delete语句！22');
end if;
end;
drop trigger t_2;
select * from student;
insert into student values(1,'叶晨','男',99,61.6);
update student set sname='叶星辰' where sname='叶晨';
delete from student where sid=1;
-----------------------------------------------------------------------
-- 行级触发器2
create or replace trigger
t_3
after insert or update or delete
on student
for each row
declare
un varchar(100);
begin
select username into un from user_users;
if updating then/*有old和new*/
dbms_output.put_line(un||'：student：update：33：旧数据='||:old.sid||'-'||:old.score);
dbms_output.put_line(un||'：student：update：33：新数据='||:new.sid||'-'||:new.score);
elsif inserting then
dbms_output.put_line(un||'：student：insert：33：新数据='||:new.sid||'-'||:new.score);
elsif deleting then
dbms_output.put_line(un||'：student：delete：33：旧数据='||:old.sid||'-'||:old.score);
end if;
end;
drop trigger t_3;
select * from student;
insert into student values(1,'叶晨','男',99,61.6);
update student set sname='叶星辰' where sname='叶晨';
delete from student where sid=1;
-----------------------------------------------------------------------
-- ddl触发器(模式触发器) 了解
-- 创建一个表记录操作的信息
create table obj(
oname varchar2(30),
otype varchar2(20),
odate date
);
-- 创建触发器：当前用户执行ddl语句时触发
create or replace trigger
t_4
after drop or create or alter on schema/*schema模式，对应当前用户下的所以表*/
declare
un varchar(100);
begin
select username into un from user_users;
dbms_output.put_line(un||'执行了ddl语句！');
insert into obj values(ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
end;
select * from obj;
select * from tabs;/*查询所有表*/
create table tab_10(tid int primary key,tname varchar(100));
insert into tab_10 values(1,'夕颜');
insert into tab_10 values(2,'若曦');
create table tab_11(tid int primary key,tname varchar(100),tsex varchar(100));
insert into tab_11 values(1,'夕颜','男');
insert into tab_11 values(2,'若曦','男');
-----------------------------------------------------------------------
-- oracle的导入和导出
-- 注意必须在cmd中写命令
exp wen/123@ORCL tables=(tab_11,tab_10) file='F:\1.dmp'; --导出指定表
exp wen/123@ORCL full=y inctype=complete file='F:\1.dmp'; --导出整个用户的所有对象和数据
imp wen/123@ORCL file='e:\1.dmp' tables=(tab_11,tab_10);
面试题
--oracle面试题
--1 oracle和mysql的区别
--2 数据库优化
--3 oracle的导入导出
--4 oracle和mysql实现分页
--5 oracle序列
--6 函数和存储过程的区别
--7 存储过程和触发器的区别
--8 什么是视图 是否可以对视图解析增删改
--9 索引
