oracle 的游标，经常用的函数，存储过程，包

--第一个例子
--赋值语句
set serveroutput on;

declare
var_username varchar2(100);
begin
select ename into var_username from scott.emp where empno=7788;
dbms_output.put_line('用户名：'||var_username);
end;

--第二个例子
--赋值语句
set serveroutput on;

declare
c_databases varchar(50):='10 g';
begin
dbms_output.put_line(c_databases);
end;

--第三个例子
--条件语句if
set serveroutput on;

declare
v_number number:=10;
begin
if v_number<0 then
dbms_output.put_line(v_number||'是个负数');
elsif v_number=0 then
dbms_output.put_line(v_number||'为零');
else
dbms_output.put_line(v_number||'是个正数');
end if;
end;

--第四个例子
--分支语句case

set serveroutput on;

declare
v_day   integer :=3;
v_data  varchar2(20);

begin

v_data :=case v_day

when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
when 4 then '星期四'
when 5 then '星期五'
when 6 then '星期六'
when 7 then '星期日'
end;
dbms_output.put_line(v_data);
end;

 

--第五个例子
--循环语句loop...exit...end
setserveroutput on;

declare
v_num integer:=1;
v_sum integer:=0;
begin
loop
v_sum:=v_sum+v_num;
dbms_output.put_line(v_num);

if v_num=3 then
exit;
end if;

dbms_output.put_line('+');
v_num:=v_num+1;
end loop;
dbms_output.put_line('=');
dbms_output.put_line(v_sum);
end;

--第六个例子
--循环语句loop...exit when...end
set serveroutput on;
declare
v_num integer:=1;
v_sum  integer:=0;
begin
loop
v_sum:=v_sum+v_num;
dbms_output.put_line(v_num);

exit
when v_num=3;

v_num:=v_num+1;
dbms_output.put_line('+');
end loop;
dbms_output.put_line('=');
dbms_output.put_line(v_sum);
 
end;

--第七个例子
----循环语句 while loop...end loop

set serveroutput on;

declare
v_num integer:=1;
v_sum integer:=0;
begin

while v_num<4

loop
v_sum:=v_sum+v_num;
dbms_output.put_line(v_num);
dbms_output.put_line('+');
v_num:=v_num+1;
end loop;
dbms_output.put_line('=');
dbms_output.put_line(v_sum);
end;

--第八个例子（不主要）
--循环语句loop...in..loop...end loop
set serveroutput on;
declare
v_num integer:=1;
v_sum integer:=0;

begin

for v_num in 1..3
loop
v_sum:=v_sum+v_num;
dbms_output.put_line(v_num);
if v_num<3 then
dbms_output.put_line('+');
end if;
end loop;
dbms_output.put_line('=');
dbms_output.put_line(v_sum);

end;

--异常例子1(value_error)
set serveroutput on;
declare
v_num number;
begin
v_num:='d123';

exception
when value_error then
dbms_output.put_line('数据类型错误');

end;

 

--异常例子(综合例子)
set serveroutput on;
declare
v_username varchar2(200);
begin
select ename into v_username from emp where job='s12';
exception

when no_data_found then
dbms_output.put_line('没有数据');
when too_many_rows then
dbms_output.put_line('返回多行数据');
when others then
dbms_output.put_line('错误情况不明');

end;

--abs 返回表达式的绝对值

set serveroutput on;
begin
dbms_output.put_line(abs(-3333));
end;

--ceil 返回大于或等于所给数字的最小整数

set serveroutput on;

begin
dbms_output.put_line(ceil(3333.55));
end;

--floor 返回小于或等于所给数字的最大整数
set serveroutput on;

begin
dbms_output.put_line(floor(3333.55));
end;
--power 返回表达式给定的次方值

set serveroutput on;
begin
dbms_output.put_line(power(12,2));
end;
--round 返回四舍五入

set serveroutput on;
begin
dbms_output.put_line(round(1.2));
dbms_output.put_line(round(1.9));
end;
 
--ascii  返回ascii码

set serveroutput on;
begin
dbms_output.put_line(ascii('A'));
end;

--length 返回字符串表达式的字符（而不是字节）个数

set serveroutput on;
begin
dbms_output.put_line(length('d    '));
dbms_output.put_line(length('张山东省'));
end;

--upper 返回将小写转化为大写
set serveroutput on;
begin
dbms_output.put_line(upper('ffff'));
end;

--输出当前日期(sysdate)

set serveroutput on;
begin
dbms_output.put_line(sysdate);
end;

--将日期转化为字符串(to_char)

set serveroutput on;
begin
dbms_output.put_line(to_char(sysdate)||'字符串');
end;
--返回指定日期月份的最后一天的日期(last_day)
set serveroutput on;
begin
dbms_output.put_line(last_day(sysdate));
end;

--返回两个日期之间的月份数目

set serveroutput on;
begin
dbms_output.put_line(months_between( to_date('2012-11-12','yyyy-mm-dd'),to_date('2012-12-14','yyyy-mm-dd')));
end;

--将字符串转化为日期（to_date）
set serveroutput on;
begin
dbms_output.put_line(to_date('2012-11-12','yyyy-mm-dd'));
end;

--统计函数(max)

set serveroutput on ;
declare

v_num number ;
begin
select max(sal) into v_num from emp;
dbms_output.put_line('最高工资'||v_num);
end;
--统计函数(min)
set serveroutput on ;
declare

v_num number ;
begin
select min(sal) into v_num from emp;
dbms_output.put_line('最低工资'||v_num);
end;
--统计函数(count)
set serveroutput on ;
declare

v_num number ;
begin
select count(ename) into v_num from emp;
dbms_output.put_line('总人数：'||v_num);
end;

--游标
--游标:从字面的意思是游动的光标，用数据库的语言描述，
--游标是映射在结果集一行数据上的位置实体，，有了游标，
--用户就可以访问结果集中的任意一行数据，将游标放置到某一行后，
--即可对该行数据进行操作，最常见的操作是提取当前行的数据

--游标for循环是显示游标的一种快捷方式，
--当使用for循环开始时，游标被自动打开(不需要使用open语句);
--每循环一次，系统自动读取游标当前行的数据(不需要使用fetch语句)；
--当退出for循环时，游标自动关闭(不需要使用close语句)

set serveroutput on;

declare
v_id number;
v_name varchar2(30);

cursor jacobcursor(v_type number)is
select empno,ename from emp
where deptno=v_type;
begin

open jacobcursor(20);
fetch jacobcursor into v_id,v_name;
close jacobcursor;
dbms_output.put_line('用户的id：'||v_id||'用户名:'||v_name);

end;

 

--游标(%isopen)

set serveroutput on;

declare
v_id number;
v_name varchar2(30);

cursor zzucursor(v_type number)is
select empno,ename from emp
where deptno=v_type;
begin

if zzucursor%isopen=false then
open zzucursor(30);

end if;
fetch zzucursor into v_id,v_name;
close zzucursor;
dbms_output.put_line('userid:  '||v_id||'   username:  '||v_name);
end;

--游标(%found|| %notfound)

set serveroutput on;
declare
v_id number;
v_name varchar2(50);
cursor zzucursor(v_type number)is
select empno,ename from emp
where deptno=v_type;
begin
if zzucursor%isopen=false then
open zzucursor(30);
end if;

fetch zzucursor into v_id,v_name; --必须在这个位置

 

while zzucursor%found
loop
dbms_output.put_line('userid  '||v_id||'username  '||v_name);
fetch zzucursor into v_id,v_name;
end loop;

close zzucursor;
end;

--游标(%rowcount)
set serveroutput on;
declare
v_id number;
v_name varchar2(30);
cursor zzucursor(v_type number)is
select empno ,ename from emp
where deptno =v_type;
begin
 if zzucursor%isopen= false then
 open zzucursor(30);
 end if;
 fetch zzucursor into v_id,v_name;
 while zzucursor%notfound=false
 loop
 dbms_output.put_line('userid   :'||v_id||'username  :'||v_name);
 if zzucursor%rowcount=3 then
 exit;
 end if;
 fetch zzucursor into v_id,v_name;
 end loop;
 end;
 
 --记录与游标
 set serveroutput on;
 declare
 --记录
 type user_record is  record
 ( v_id  emp.empno%type,
 v_name   emp.ename%type );
 
 v_user_record  user_record;
 cursor zzucursor(v_type number)is
 select empno ,ename from emp
 where deptno=v_type;
 begin
 if zzucursor%isopen =false then
 open zzucursor(30);
 end if;
 fetch zzucursor into v_user_record;
 while zzucursor%found
 loop
 dbms_output.put_line('userid  :'||v_user_record.v_id||'username :'||v_user_record.v_name);
 fetch zzucursor into v_user_record;
 
 end loop;
 close zzucursor;
 end;

 --记录
 
set serveroutput on;
declare
type user_record is record
( v_id emp.empno%type,
v_name emp.ename%type
);
v_user_record user_record;
 
cursor zzucursor(v_type number)is
select empno,ename from emp
where deptno =v_type;
begin
if zzucursor%isopen=false then
open zzucursor(30);
end if;
fetch zzucursor into v_user_record;

while zzucursor%found
loop
dbms_output.put_line('userid  :'||v_user_record.v_id||'username  :'||v_user_record.v_name);
if zzucursor%rowcount=3 then
exit;
end if;
fetch zzucursor into v_user_record;
end loop;
close zzucursor;
end;
 
 
 --定义一个记录
 type user_record is record
 (
 v_id emp.empno%type,
 v_name emp.ename%type
 );
 
 ---for 与 记录 
 
set serveroutput on;
declare
type user_record is record
( v_id     emp.empno%type,
  v_name   emp.ename%type
);
v_user_record   user_record;
 
cursor zzucursor(v_type number)is
select empno,ename from emp
where deptno =v_type;

begin
for  v in zzucursor(30) loop
v_user_record.v_id:=v.empno;
v_user_record.v_name:=v.ename;
dbms_output.put_line('userid  :'||v_user_record.v_id||'username  :'||v_user_record.v_name);
end loop ;
end;

--存储过程

create or replace procedure  zzuprocedure(v_type  number)
as
begin
update  emp  set ename='dddd'
where empno=v_type;
end;

--存储过程2
create or replace procedure zzucursor(v_type number)
as
v_name varchar2(30);

begin

select ename into v_name from emp
where empno=v_type;

dbms_output.put_line(v_name);
 end;
 
 
 
 
  --调用存储过程
 call procedurename;
 
 execute  procedurename;
 
 --删除存储过程
 drop procedure name;
 
  --函数
 
  create or replace function zzufunction(v_type number)
  return  emp.ename%type
 
  as
  names  emp.ename%type;
 
  begin
  select ename into names from emp where empno=v_type;
  return names;
  end;
 
 
  set serveroutput on;
  declare
  v_name emp.ename%type;
  begin
  v_name := zzufunction(7788);
  dbms_output.put_line(v_name);
  end;
 
 
 
 
 --程序包
 create or replace package zzupackage is
 procedure zzucursor(v_type number);
 function zzucurfunction(v_type number)
 return emp.ename%type;
 end zzupackage;

 

--创建包体
create or replace package body  zzupackage
as

procedure zzucursor(v_type number)is
begin

dbms_output.put_line('isss'||v_type);
end;

function zzucurfunction(v_type number)
return emp.ename%type
as
 out_name  emp.ename%type;
 
 begin
 select ename into out_name from emp
 where empno=v_type;
 return out_name;
 end;
 end zzupackage;
 
 --调用包体
 
set serveroutput on ;
begin
zzupackage.zzucursor(222);
end;

--删除包体
drop package body   packagename;

--触发器
create or replace trigger zzutrigger
after
update on emp
for each row
begin
dbms_output.put_line('已经触发触发器');
end;
 
 

--自定义异常
create or  replace procedure zzuprocedure(v_num number)
is
myexception exception;
begin
update emp set sal=sal+200 where empno=v_num;
if sql%notfound then
raise myexception;
end if;

exception
when myexception  then
dbms_output.put_line('没有找到用户');
end;

create or replace view  zzu_view   as select * from emp;

 

--定义索引
create index index_id on emp(ename);
--删除索引
drop index index_id;

 

--定义序列

create sequence user_sequence
minvalue 1
nomaxvalue
start with 1
increment by 1
nocycle;
-- minvalue 表示序列的最小值为1
--nomaxvalue  表示序列没有最大值的限制
--start with 1   表示序列的初始值为1
--increment by 1  表示序列间隔为1
--nocycle     不循环

--删除序列

drop sequence user_sequence