示例的数据表下载的地址:http://download.csdn.net/detail/u010342038/9506955
示例1:
--启动后台输入
set serveroutput ondeclare
--定义基本变量类型
--基本数据类型
pnumber number(7,2);
--字符串变量
pname varchar2(20);
--日期变量
pdate date;
begin
--赋值
pnumber:=1;
--打印该变量
dbms_output.put_line(pnumber);
pname:='Tom';
dbms_output.put_line(pname);
pdate:=sysdate;
dbms_output.put_line(pdate);
--计算明天的日期
dbms_output.put_line(pdate+1);
end;
示例2:
set serveroutput on
--引用型变量
declare
--定义引用型变量:查询并打印7839的姓名和薪水
--pname varchar2(20);
--psal number;(相当于下面二个)
pname emp.ename%type;
psal emp.sal%TYPE;
begin
--得到7839的姓名和薪水(into 把ename,sal的值赋给pname,psal)
select ename,sal into pname,psal from emp where empno=7839;
--打印姓名和薪水
dbms_output.put_line(pname||'的薪水是'||psal);
end;
/
示例3:
set serveroutput on
--使用记录变量,查询并打印7839的姓名和薪水
declare
--定义记录型变量:注意代表一行
emp_rec emp%rowtype;
begin
--得到7839一行的信息
select * into emp_rec from emp where empno=7839;
--打印姓名和薪水
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
示例4:
set serveroutput on
--接收一个键盘输入
--num:地址值,含义:在该地址上保存了输入的值
accept num prompt '请输入一个数字';
declare
--定义变量保存用户从键盘输入的数字(number类型,所以键盘必须输入是数字,否则报错)
pnum number:=#
begin
--执行if语句进行条件判断
if pnum = 0 then dbms_output.put_line('你输入数字是0');
elsif pnum = 1 then dbms_output.put_line('你输入数字是1');
elsif pnum = 2 then dbms_output.put_line('你输入数字是2');
else dbms_output.put_line('其他数字是'|| pnum);
end if;
dbms_output.put_line(pnum);
end;
/
示例5:
set serveroutput on
declare
--定义循环变量
pnum number:=1;
begin
/*
--使用while循环打印数字的1-10
while pnum <= 10 loop
--循环体
--打印该变量的值
dbms_output.put_line(pnum);
--使该变量+1
pnum:=pnum + 1;
end loop;
*/
/*
--使用loop循环打印1-10
loop
--退出条件:循环变量大于10
exit when pnum > 10;
--打印该变量的值
dbms_output.put_line(pnum);
--使该变量+1
pnum := pnum + 1;
end loop;
*/
--范围为(1-10)
for pnum in 1..10 loop
--打印该变量的值
dbms_output.put_line(pnum);
end loop;
end;
/
示例6:
set serveroutput on
/*
光标就是结果集
光标的属性:
%found:查找有值就返回true
%notfound:查找没有值就返回true
%isopen:判断光标是否打开,打开返回true
%rowcount:影响的行数(如果总共有一百条记录,我取走10条记录,那影响的行数就是:10条)
光标的限制:(一个会话只有300光标的限制,查看sql如下)
SQL> conn system/todaytech
已连接。
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- -----
ursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
修改光标open的数量
alter system set open_cursors=400 scope=both;
scope的取值:both(包括memory/spfile),memory(表示只更改当前的实例),spfile(表示只更改参数文件,不更改当前实例,只有oracle重启才生效);
*/
declare
--定义一个光标(光标相当数组)
cursor cemp is select ename,sal from emp;
--
pname emp.ename%type;
psal emp.sal%type;
begin
--
open cemp;
loop
--取一条记录
fetch cemp into pname,psal;
--exit when没有取到记录(cemp%notfound:=true)
exit when cemp%notfound;
--打印
dbms_output.put_line(pname||'的薪水是'||psal);
end loop;
--关闭光标
close cemp;
end;
光标%isopen和%rowcount的示例:
set serveroutput on
declare
--定义光标代表给哪些员工涨工资
cursor cemp is select empno,ejob from emp;
pempno emp.empno%type;
pjob emp.ejob%type;
begin
--打开光标
open cemp;
if cemp%isopen then dbms_output.put_line('光标已经打开');
else dbms_output.put_line('光标没有打开');
end if;
loop
--取出一个员工
fetch cemp into pempno,pjob;
exit when cemp%notfound;
dbms_output.put_line('rowcount:'||cemp%rowcount);
end loop;
--关闭光标
close cemp;
end;
示例7:
--给员工涨工资,总裁1000,经理800,其他400
set serveroutput on
declare
--定义光标代表给哪些员工涨工资
cursor cemp is select empno,ejob from emp;
pempno emp.empno%type;
pjob emp.ejob%type;
begin
--打开光标
open cemp;
loop
--取出一个员工
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判断员工的职位
if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
--关闭光标
close cemp;
--对于oracle,默认的事务隔离级别是read committed
--事务的ACID(原子性,一致性,隔离性,持久性)
commit;
dbms_output.put_line('涨工资完成');
end;
示例8:
--查询某个部门中员工的姓名
set serveroutput on
declare
--定义带参数的光标
cursor cemp(dno number) is select ename from emp where deptno=dno;
pname emp.ename%type;
begin
--打开光标(查询部门号为10的员工)
open cemp(10);
loop
--取出每个员工姓名
fetch cemp into pname;
exit when cemp%notfound;
dbms_output.put_line(pname);
end loop;
--关闭光标
close cemp;
end;
示例9:
set serveroutput on
declare
years varchar2(4);
--Total number:=0;
count80 number :=0;
count81 number :=0;
count82 number :=0;
count87 number :=0;
cursor cemp is select to_char(hiredate,'YYYY') from emp;
begin
open cemp;
loop
fetch cemp into years;
exit when cemp%notfound;
--Total :=Total+1;
if years = '1980' then count80:=count80+1;
elsif years = '1981' then count81:=count81+1;
elsif years = '1982' then count82:=count82+1;
else count87:=count87+1;
end if;
end loop;
close cemp;
dbms_output.put_line('Total:'||(count80+count81+count82+count87));
dbms_output.put_line(' 1980:'||count80);
dbms_output.put_line(' 1981:'||count81);
dbms_output.put_line(' 1982:'||count82);
dbms_output.put_line(' 1987:'||count87);
end;
示例10:
set serveroutput on
declare
cursor cemp is select empno ,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
countEmp number :=0;
salTotal number;
begin
select sum(sal) into salTotal from emp;
open cemp;
loop
exit when salTotal > 60000;
fetch cemp into pempno,psal;
exit when cemp%notfound;
update emp set sal=sal*1.1 where empno=pempno;
countEmp := countEmp + 1;
salTotal :=salTotal + psal*0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('人数:'|| countEmp||',涨后的工资总额:'||salTotal);
end;
示例11:
/*
统计每个部门的人数和部门的工资总额
*/
set serveroutput on
declare
cursor cdept is select deptno from dept;
pdeptno dept.deptno%type;
cursor cemp(pdeptno number) is select sal from emp where deptno=pdeptno;
psal emp.sal%type;
count1 number;
count2 number;
count3 number;
saltotal number;
begin
open cdept;
loop
--取出一个部门
fetch cdept into pdeptno;
exit when cdept%notfound;
--初始化的工作
count1 :=0;count2 :=0;count3 :=0;--saltotal:=0;--(方法1)
--得到部门的工资总额
select sum(sal) into saltotal from emp where deptno=pdeptno;
--(这个效率低,因为要操作数据库,相于方法1)
open cemp(pdeptno);
loop
fetch cemp into psal;
exit when cemp%notfound;
--判断薪水的范围
if psal < 3000 then count1:= count1+1;
elsif psal >= 3000 and psal < 6000 then count2:= count2+1;
else count3:= count3+1;
end if;
--saltotal:= saltotal+psal;--(方法1)
end loop;
close cemp;
--保存当前部门的结果(要在第一个loop内保存,否则会在初始化工作时初始化为0)
insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));
end loop;
close cdept;
commit;
dbms_output.put_line('统计完成');
end;