/*
达内学习 Oracle day42 2013-10-31
*/
多重循环的退出
declare
var_m number;
var_n number;
begin
while var_m < 4 loop
end loop;
end;
begin
for var_m in 1..3 loop
for var_n in 1..3 loop
dbms__output.put_line(var_n);
if var_n =2 then
goto endouter;
end if;
end loop;
end loop;
<<endouter>>
NULL;
end;
1,在内层循环中修改外层循环条件
2,标签 goto
-------------------------------------------------------------
SQL 在plsql中的分类
1,select 语句在plsql语句中使用时 要和 into 结合变量使用
select first_name into var_name from s_emp where id = var_id;
2,insert delete update DML
commit rollback savepoint TCL
这写语句可以直接在plsql中使用
declare
var_id number :=1;
var_name varchar2(30):='test';
begin
insert into testplsql values(2,'app');
commit;
end;
3,DDL
create table drop table alter table
这些语句不能直接在Plsql中使用,需要动态sql
把sql语句变成字符串
动态sql:把一个字符串对应的sql语句,当成真正的sql来执行。
declare
sqlstr varchar2(100);
begin
sqlstr:='create table testplsql(id) number)';
sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
sqlstr:=sqlstr||','||'name varchar2(20)';
/*把字符串 对应 sql当真正sql来执行*/
execute immediate sqlstr;
end;
4,DML语句的
declare
var_id number:=1;
var_name varchar2(30):='test';
sqlstr varchar2(100);
begin
sqlstr:='insert into testplsql values(2,''app'')';
dbms_output.put_line(sqlstr);
sqlstr:= 'insert into testplsql values('||var_id||','''||var_name||''')';
替代字符串拼接 —— 使用占位符
sqlstr:='insert into testplsql values(:b0,:b1)';
execute immediate sqlstr using var_id,var_name;
5,select 语句动态sql
只能返回一个结果
declare
var_name s_emp.first_name%type;
sqlstr varchar2(300);
begin
sqlstr='select first_name from s_emp where id =2';
execute immedaite sqlstr into var_name;
end;
6,一次可以放多条数据的数据类型 游标cursor
cursor 的使用步骤
1,声明cursor
没有declare关键字 cursor 游标名 is sql语句;
2, 打开cursor
打开游标 open 游标名;
3, 提取数据,处理数据
fetch 游标名 into 变量名;
4,关闭游标
close 游标名;
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||' '||var_emp.first_name||' '||var_emp.salary);
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||' '||var_emp.first_name||' '||var_emp.salary);
close empcursor;
end;
b,游标的遍历
游标的属性
found 提取数据时,提取到返回真 ,没有提取到返回假(游标必须处于打开),不打开就是非法游标,打开不fetch使用这个属性返回 NULL。 游标名%found
notfound 游标必须处于打开状态,打开不fetch 则返回 null,如果提取到新数据 返回假,没有提取到新数据 就返回真。
isopen 游标是否打开 ,打开返回真,关闭返回假,打开的不能再打开,关闭的不能再关闭
rowcount 当前游标的指针位移量
使用loop简单循环 结合notfound 遍历游标
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||' '||var_emp.first_name||' '||var_emp.salary);
end loop;
close empcursor;
end;
-----------------------使用while found
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
while empcursor%found loop
dbms_output.put_line(var_emp.id||' '||var_emp.first_name||' '||var_emp.salary);
fetch empcursor into var_emp;
end loop;
close empcursor;
end;
智能循环(for循环)
declare
cursor empcursor is select * from s_emp;
begin
for var_emp in empcursor loop --不用定义变量,不用 open fetch close
dbms_output.put_line (var_emp.id||':'||var_emp.salary);
end loop;
end;
c,带参游标
plsql中的参数不能有任何长度修饰
但是可以使用 %type
但参游标 只要打开游标时 传入实参即可
declare
cursor empcursor(var_id number) is select * from s_emp where id>var_id;
var_emp empcursor%rowtype;
begin
open empcursor(10); --传参
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||' '||var_emp.first_name||' '||var_emp.salary);
end loop;
close empcursor;
end;
----
for 循环写法
declare
cursor empcursor(var_id number) is select * from s_emp where id>var_id;
begin
for var_emp in empcursor(10) loop --传参 不用定义变量,不用 open fetch close
dbms_output.put_line (var_emp.id||':'||var_emp.salary);
end loop;
end;
d,参考游标
把一个游标对应的sql语句关联到一个sql字符串上, 动态sql 和游标 的结合
declare
type myrefcursor is ref cursor;
/*使用 参考游标类型 定义游标变量*/
empcursor myrefcursor;
sqlstr varchar2(100):='select * from s_emp where id >:b0';
var_emp s_emp%rowtype;
var_id s_emp.id%type:=15;
begin
/*打开游标关联到字符串*/
open empcursor for sqlstr using var_id;
/* 后边和正常的游标一样*/
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.salary);
end loop;
end;
-------------------------------------------------------------------------
存储过程 和 函数
1,存储过程 procedure
给我们的匿名块 起名子 存储到数据库中
设计两个整数参数 打印其中最大值
create or replace procedure getmax (x number,y number)
is
var_x number;
var_y number;
begin
var_x:=x;
var_y:=y;
if var_x<var_y then
dbms_output.put_line(var_x);
else
dbms_output.put_line(var_y);
end if;
end;
出现重名函数 drop function getmax
2,察看存储过程参数 desc getmax
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X NUMBER IN
Y NUMBER
参数的模式: in(传入) out(传出) in out(传入又传出)
default: 参数的默认值
3.调用存储过程
exec getmax(100,200);
call getmax(1,201);
匿名块调用 或者 有名块调用
declare
a number :1;
b number :99;
begin
/*参数的位置赋值*/
call getmax(a,b);
-- call getmax(x=>a,y=>b); 参数的名字赋值
end
设计一个存储过程 把两个整数参数和放入第三个参数中
create or replace procedure getmax (x in number,y in number,z out number)
is
begin
z:=x+y;
end;
调用:
declare
var_z number:=0;
begin
getmax(1,1,var_z);
end;
----------------------
设计一个存储过程 传入两个整数参数
要求输出两个整数参数最大值 还要求把两个参数的和放入第二个参数中
察看存储过程 并调用
create or replace procedure mypro(x in number,y in out number,z out number)
is
begin
if x<y then
z:=y;
else
z:=x;
end if;
y:=x+y;
end;
declare
a number:=0;
b number:=4;
c number:=0;
begin
mypro(2,b,c);
dbms_output.put_line(b||' '||c);
end;
察看存储过程的源代码
desc user_source;
select text from user_source where name ='MYPRO';
------------------------------------------------------------------
函数
函数和存储过程的区别
1,关键字不同 procedure function
2,过程没有返回值和返回值类型,函数可以有返回值和返回值类型
return
3,存储过程可以直接在匿名块或有名块里调用,函数必须组成表达式或者在sql语句中使用。
设计一个函数,传入两个整数参数 返回两个参数的最大值
create or replace function wfungetmax(x in number,y in number) return number
is
begin
if x<y then
return y;
end if;
return x;
end;
察看函数 desc fungetmax
---------------------------------------------------------------
设计一个函数,传入两个整数参数,要求返回两个整数参数的最大值,并且要求把两个参数的和放入第二个参数中。
察看函数 调用函数
create or replace function fungetmaxsum(x in number,y in out number) return number
is
begin
if x>y then
y:=x+y;
return x;
end if;
x:=x+y;
y:=x-y;
x:=x-y;
y:=x+y;
return x;
end;
-----------------------------------------
create or replace function fungetmaxsum(x in number,y in out number) return number
is
var_temp number; --这里可以设临时变量
begin
var_temp:=y;
y:=x+y;
if x<var_temp then
return var_temp;
end if;
return x;
end;
---------------------------------------------------------
参数的默认值
create or replace function testpardef(x number, y number:=123) return number -- 默认值必须靠右,参数有默认值他右侧必须也有默认值
is
begin
if x<y then
return y;
else
return x;
end if;
end;
测试:
select testpatdef() from dual; --123
select testpatdef(46545) from dual; --46545
select testpatdef(11,12) from dual; --12
---------------
1,建立存储过程的语法
create or replace procedure 过程名 (参数名 参数模式 参数类型)
is
临时变量
begin
end;
2,察看存储过程
desc 过程名
参数名 类型 参数模式(in out in out) 参数默认值
3,调用存储过程
sqlplus call exec
匿名块 有名块 调用
declare
/*变量定义*/
begin
/*过程调用*/
end;
4,删除存储过程
drop procedure 过程名;
5,函数和存储过程的区别
1,关键字
procedure function
2,过程没有返回值,函数有返回值和返回值类型 return
3,调用方式不同,过程可以直接调用,函数必须组成表达式
4,参数的默认值
----------------------------------------------------
包:package
系统提供的包
dbms_output 系统输入输出包
dbms_random 随机数包
desc ddbms_random;
select trunc(dbms_random.value(1,100)) from dual;
dbms_job 定时任务调度包
定时调用存储过程
submit(job binary_integer,what varchar2,next_date date,interval varchar2)
会把定时任务提交给系统,系统会为这个人物分配一个编号 放入job中
next_date 第一次调用的时间
interval 下一次调用的时间
run(job binary_integer); 运行定时任务
remove(job binary_integer); 删除定时任务
1,建立一张表
create table testjob(
id number primary key,
name varchar2(30),
);
2,建立一个序列
create sequence testjob_id;
3, 写一个存储过程 向表添加一条数据
create or replace procedure insert_job
is
begin
insert into testjob values(testjob_id.nextval,'test'||testjob_id.currval);
commit;
end;
4,把存储过程的调用 交给 dbms_job
declare
jobno binary_integer;
begin
dbms_job.submit(jobno,'insert_job();',sysdate,'sysdate+1/24/60');
dbms_output.put_line('jobbo='||jobno);
dbms_job.run(jobno);
end;
5,删除
begin
dbms_job.remove(jobno );
end;
----------------------------
自定义包 package
把一组相关的函数 变量 过程等仿佛一个逻辑结构中
相当于头文件
create or replace package mypack
is
procedure getmax(x number,y number);
function getmin(x number,y number) return number;
end;
包体:相当与C中的实现文件
create or replace package body mypack
is
procedure getmax(x number,y number)
is
begin
if x<y then
dbms_output.put_line(y);
else
dbms_output.put_line(x);
end if;
end;
function getmin(x number,y number) return number
is
begin
if x<y then
dbms_output.put_line(x);
else
dbms_output.put_line(y);
end if;
end;
/*如何调用 数据前加包名*/
begin
mypack.getmin(1,2) from ;
end;