pl/sql基本语法

PL/SQL块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事务控制语句

(COMMIT,ROLLBACK,SAVEPOINT),而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句(GRANT,REVOKE)

1.检索单行数据

1.1%type

declare
v_ename emp.ename%type;
v_sal   emp.sal%type;
begin
select ename,sal into v_ename,v_sal
from emp
where empno=&no;
DBMS_OUTPUT.PUT_LINE(v_ename);

end;

注意:

不管在sqlplus下，还是在sqldeveloper下要想实现DBMS_OUTPUT.PUT_LINE(v_ename);

的输出必须 设置 set serveroutput on;

1.2%rowtype

declare
emp_number CONSTANT emp.empno%type:=7369;
one_emp emp%rowtype;
begin
select *
into one_emp
from emp
where empno=emp_number;
DBMS_OUTPUT.PUT_LINE(one_emp.ename);
end;

1.2使用记录变量接受数据（%type，record）

set serveroutput on

--例9.4
 declare
 type emp_type is record(
 empno emp.EMPNO%type,
 ename emp.ENAME%type,
 job  emp.JOB%type,
 sal emp.SAL%type
 );
 one_emp emp_type;
 begin
 select empno,ename,job,sal
 into one_emp
 from emp
 where empno=7900;
 DBMS_OUTPUT.PUT_LINE('员工编号： '||one_emp.empno);
 end;

 

%rowtype类型：

--例9.3
 declare
 emp_number constant emp.empno%type :=7900;
 one_emp emp%rowtype;
 begin
  select *
  into one_emp
  from emp
  where empno=emp_number;
  dbms_output.PUT_LINE('查询的员工编号:'||one_emp.empno);
  dbms_output.PUT_LINE('该员工的姓名:'||one_emp.ename);
 end;

 

 

表类型：

declare
 type my_emp is table of emp%rowtype
 index by binary_integer;
 new_emp my_emp;
 begin
 new_emp (1).empno :=6800;
 new_emp (1).ename :='TRACY';
 new_emp (2).empno :=6900;
 new_emp (2).ename :='LUCY';
 dbms_output.PUT_LINE(new_emp (1).empno|| ',' || new_emp (1).ename);
 dbms_output.PUT_LINE(new_emp (2).empno||',' || new_emp (2).ename);
 end;

例9.7

declare
score BINARY_INTEGER :=61;
begin
 IF score >=90 then dbms_output.put_line('优秀');
 elsif score >=80 then dbms_output.put_line('良好');
 elsif score >=60 then dbms_output.put_line('及格');
 else dbms_output.put_line('不及格');
 end if;
 end;
/

例9.8

declare
 grade varchar2(4) :='良好';
 begin
 case grade
 when '优秀' then dbms_output.put_line('大于90分');
 when '良好' then dbms_output.put_line('大于80分,小于90分');
 when '及格' then dbms_output.put_line('大于60分,小于80');
 when '不及格' then dbms_output.put_line('小于60分');
 else dbms_output.put_line('此等级不对应任何分数段');
 end case;
 end;

例9.9搜索case表达式

declare
score BINARY_INTEGER := 61;
begin
   case
   when score>=90 then dbms_output.put_line('优秀');
   when score>=80 then dbms_output.put_line('良好');
   when score>=60 then dbms_output.put_line('及格');
   else dbms_output.put_line('不及格');
   end case;
end;

注意：与简单的case表达式相比较，可以发现case关键字后面不在跟随带球表达式，而when自居中的表达式也换成了条件语句condition，其实搜索case表达式就是将来待求表达式放在条件语句中进行范围比较，而不再想简单case表达式那样只能与单个值进行比较。

--例9.10：使用简单的loop循环语句，输出数字1到10语句。
declare
i binary_integer :=1;
begin
      loop
          dbms_output.put_line('张');
          i :=i+1;
          exit when i>10;
      end loop;
end;

--例9.11使用while循环语句，输出数字1到10语句如下
declare
i binary_integer :=1;
begin
  while i<=10


    loop
        dbms_output.put_line(i);
        i := i+1;
    end loop;


end;

--9.12使用for循环语句，输出数据1到10，语句如下
declare


  begin
    for i in 1 .. 10
      loop
          dbms_output.put_line(i);
      end loop;
    
end;

--reverse递减输出

9.5游标

使用select语句可以返回一个结果集，而如果对结果集中单独的进行操作，则需要使用游标。

使用游标主要遵循4个步骤-生命游标、打开游标、检索游标和关闭游标

--例9.13在pl/sql中，声明一个游标 emp_cursor对应emp表中的查询操作，
--此查询操作检索emp表中指定部门的员工的部分信息
declare

cursor emp_cursor (dept_num number :=20)
is
select empno,ename,job,sal
from emp where deptno=dept_num;
begin
...
end;

注意：游标的声明与使用等都学要在pl/sql快中进行，其中声明游标需要在declare子句进行。

--例9.14在声明游标指定了查询语句，但是此时该查询语句并不会被oracle执行，只有打开游标后
--oracle才会执行查询语句，在打开游标时，如果游标有输入参数，游湖还需要为这些参数赋值。
--否则将会报错（除非参数设置了默认值）
declare
cursor emp_cursor (dept_num number :=20)
is
select empno,ename,job,sal
from emp where deptno=dept_num;
begin
open emp_cursor(20);
end;

--例9.15检索游标和关闭游标
declare
type emp_type is record(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
sal emp.sal%type
);


one_emp emp_type;


cursor emp_cursor (dept_num number :=20)
is
select empno,ename,job,sal
from emp where deptno=dept_num;


begin
open emp_cursor(20);
fetch emp_cursor into one_emp;
dbms_output.put_line(one_emp.ename);
dbms_output.put_line(one_emp.empno);
close emp_cursor;
end;

注意：

打开游标后，游标所对应的select语句也就被执行了，如果想要获取结果集中的数据，就需要检索游标。检索游标，实际上就是从结果集中获取单行数据并保存到定义的变量中。

--9.5.5简单游标循环
--前面一直提到检索的数据是单行数据，而游标中的查询语句返回的是一个结果集，结果集一般包含
--多行数据，那么检索出来的的单行数据是结果集中的那一行呢？实际上，游标中偶的记录是需要循环
--读取的，每循环一次，就读取一行记录。
--首先来了解游标的属性，如下：
--1、%found：返回布尔类型的值，用于判断最近一次读取记录时是否有数据行返回，如果有则返回true，
--否则返回false


--2、%notfound：返回布尔类型的值，与%found相反


--3、isopen：返回布尔类型的值，用于判断游标是否已经打开，
--如果已经打开则返回true，否则返回false


--4、%rowcount：返回数据类型的值，由于返回已经从游标中读取的记录数。

--注意：游标的使用，实在属性浅添加游标名称，如 cursor_name%found

--例9.16使用loop血环语句循环读取emp_crusor 游标中的记录，如下：
declare


cursor emp_cursor (dept_num number :=20)
is
select empno,ename,job,sal
from emp
where deptno=dept_num;


type emp_type is record(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
sal emp.sal%type
);
one_emp emp_type;
begin
open emp_cursor(20);
loop


fetch emp_cursor into one_emp;--检索游标
exit when emp_cursor%notfound;--当游标无返回记录退出循环
dbms_output.put_line('当前检索第' || emp_cursor%rowcount || '行' || one_emp.ename);
end loop;
close emp_cursor;
end;

--9.5.6游标for循环
--使用for语句也可以控制游标的循环操作，而且在这种情况下，不需要手动打开和关闭游标，
--页不需要手动判断游标是否还有返回记录，而且在for语句中设置的循环变量
--本身就存储了当前检索记录的所有列值，因此也不再需要定义变量接受记录值


--注意：
--使用for循环是，不能对游标进行open、fetch和close操作，
--如果游标有输入参数，则只能是由该参数的默认值

--例9.17使用for循环实现例9.16实例中的效果
declare
cursor emp_cursor (dept_num number :=20)
is
 select empno,ename,job,sal
 from emp
 where deptno=dept_num;
 begin
 for current_cursor in emp_cursor
 
 loop
 
 dbms_output.put_line('当前检索第' || emp_cursor%rowcount || '行:' || current_cursor.ename);
 
 end loop;
 
 end;

--9.5.7使用游标更新数据
--使用游标还可以更新表中的数据，其更新操对当前游标所定位的数据行，
--要想实现使用游标更新数据，首先需要在声明游标时使用for update子句，然后就可以在uspdate
--和delete语句使用where current of 子句，修改或删除游标结果集中当前行对应的表中的数据行

例9.18使用带for update 子句的cursor语句创建游标，该游标检索emp表中的数据，语句如下：

declare
--定义cursor
cursor emp_cursor (dept_num number :=20)
is
 select empno,ename,job,sal
 from emp
 where deptno=dept_num
 for update of sal nowait;
 /*
--定义记录类型的变量one_emp
type emp_type is record(
 empno emp.empno%type,
 ename emp.ename%type,
 job emp.job%type,
 sal emp.sal%type
);
*/
one_emp emp_cursor%rowtype;--可以代替记录类型
begin
    open emp_cursor(20);
     loop
      fetch emp_cursor into one_emp;
      exit when emp_cursor%notfound;
      
      
      update emp
      set sal=1000
      where current of emp_cursor;
      dbms_output.put_line('当前检索'||emp_cursor%rowcount ||'行'||one_emp.ename||'薪水:'||one_emp.sal);
      
     end loop;
      
    close emp_cursor;
    
  end;

注意：

上面的语句中使用of子句锁定sal列，因此更新时只能针对sal列进行更新。

Oracle 参数 游标[游标更新删除数据]：

一、参数游标 
参数游标是带有参数的游标，在定义参数游标之后，

当使用不同参数值多次打开游标时，可以产生不同的结果集，

语法如下： 
cursor cursor_name(parameter_name datatype) 

is 

select_statement; 
定义参数游标时，游标参数只能指定数据类型，而不能指定长度。 
示例如下：


Oracle代码 ：

例1、

declare   

--第一cursor
cursor temp_cursor(no number) --只指定数据类型，不指定数据的精度和长度

is 

select name 

from cip_temps 

where id=no; 

  
v_name cip_temps.name%type;    
begin  

 --执行open之后才执行定义cursor中的select

open temp_cursor(1);   

--循环检索游标

loop   
fetch temp_cursor into v_name;   
exit when temp_cursor%notfound;   
dbms_output.put_line(v_name);   
end loop;  

 

--游标也是一种资源，用完关闭

close temp_cursor;   
end;

二、使用游标更新或删除数据 
通过使用显示游标，不仅可以一行一行的处理select语句结果，

而且也可以更新或删除当前游标的数据，

注意，如果要通过游标更新或删除数据，在定义游标时一定要带有for update子句，

语法如下： 
cursor cursor_name(parameter_name datatype) 

is 

select_statement for updae [of column_reference][nowait];

如上所示：

for update子句:

用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作，

当select语句要引用到多张表是，使用of子句可以确定哪些表要加锁，

如果没有of子句，则会在select语句所引用的全部表上加锁，

nowait子句：

用于指定不等待锁，为了更新或删除当前游标行数据，必须在update 或delete语句中引用where current of 子句，语法如下： 
update table_name set column=.. where current of cursor_name; 
delete from table_name  where current of cursor_name; 

1、使用游标更新数据

Oracle代码 
declare   
cursor temp_cursor 

is 

select name,address,id 

from cip_temps 

for update;   
v_name cip_temps.name%type;   
v_address cip_temps.ADDRESS%type;   
v_id cip_temps.id%type;   
begin   
open temp_cursor;   
loop   

fetch temp_cursor into v_name,v_address,v_id;   
exit when temp_cursor%NOTFOUND;   

if(v_id>4) then   
update cip_temps 

set name='name'||to_char(v_id),address='address'||to_char(v_id) 

where current of temp_cursor;   
end if;   
end loop;   
close temp_cursor;   
end;


2、使用游标删除数据  

Oracle代码 
1.declare   
2.cursor temp_cursor is select name,address,id from cip_temps for update;   
3.v_name cip_temps.name%type;   
4.v_address cip_temps.ADDRESS%type;   
5.v_id cip_temps.id%type;   
6.begin   
7.open temp_cursor;   
8.loop   
9.fetch temp_cursor into v_name,v_address,v_id;   
10.exit when temp_cursor%NOTFOUND;   
11.if(v_id>2) then   
12.delete from cip_temps where current of temp_cursor;   
13.end if;   
14.end loop;   
15.close temp_cursor;   
16.end;  
declare
cursor temp_cursor is select name,address,id from cip_temps for update;
v_name cip_temps.name%type;
v_address cip_temps.ADDRESS%type;
v_id cip_temps.id%type;
begin
open temp_cursor;
loop
fetch temp_cursor into v_name,v_address,v_id;
exit when temp_cursor%NOTFOUND;
if(v_id>2) then
delete from cip_temps where current of temp_cursor;
end if;
end loop;
close temp_cursor;
end;


3、使用of子句在特定表加行共享锁。 
如果使用子查询涉及到多张表，那么默认情况下会在所有表上加行共享锁，为了只在特定表上加行共享锁，需要在for update子句后带有of子句，of后面跟字段名，如果跟表名或游标名称，则会报错：标示符无效。示例如下：


Oracle代码 

declare

cursor gData is select name,address,cip_temps.id from cip_temps,cip_t 
where cip_temps.id=cip_t.id for update  of address;
rs gData%rowtype;
begin
  open gData;

  loop
     fetch gData into rs;
     exit when gData%notfound;
         if rs.id=1 then
            delete from cip_temps where current of gData; 
         else
            update cip_temps set name='塞北的雪' where current of gData;
         end if;
  
  end loop;
  close gData;
end;


4、使用nowait子句 
使用for update语句对被作用于行加锁，如果其他会话已经在被作用于行上加锁，那么默认情况下当前会话要一直等待对方释放锁，通过在for update子句中指定 nowait语句，可以避免等待锁，当指定了nowait子句之后，如果其他会话已经在被作用行加锁，那么当前会话会显示错误提示信息，并退出PL/SQL,示例如下：


Oracle代码 
1.declare   
2.cursor gData is select name,address,cip_temps.id from cip_temps,cip_t    
3.where cip_temps.id=cip_t.id for update  nowait;   
4.rs gData%rowtype;   
5.begin   
6.  open gData;   
7.  loop   
8.     fetch gData into rs;   
9.     exit when gData%notfound;   
10.         if rs.id=1 then   
11.            delete from cip_temps where current of gData;    
12.         else   
13.            update cip_temps set name='塞北的雪' where current of gData;   
14.         end if;   
15.     
16.  end loop;   
17.  close gData;   
18.end;  
declare
cursor gData is select name,address,cip_temps.id from cip_temps,cip_t 
where cip_temps.id=cip_t.id for update  nowait;
rs gData%rowtype;
begin
  open gData;
  loop
     fetch gData into rs;
     exit when gData%notfound;
         if rs.id=1 then
            delete from cip_temps where current of gData; 
         else
            update cip_temps set name='塞北的雪' where current of gData;
         end if;
  
  end loop;
  close gData;
end;

三、游标for循环 

   使用游标for循环是循环游标最简单的方法，oracle会隐含打开游标、循环提取数据、关闭游标，

语法如下： 
     for record_name  in cursor_name  loop 
           .......... 
     end loop; 

如上所示：cursor_name是已经定义的游标名称，record_name是oracle隐含定义的记录变量。 
1、使用游标for循环 
   当使用游标开发程序时，建议使用for循环，从而简化代码程序，示例如下：


Oracle代码 
1.declare   
2.cursor temp_cursor 

is  

select name,age,address,id 

from cip_temps;   
3.begin   

--for循环游标，挺简单的

for emp_record in temp_cursor loop   
dbms_output.put_line(temp_cursor%rowcount||'第一行数据:'||emp_record.name||':'|| emp_record.age||':'|| emp_record.address||':'|| emp_record.id);   
6.end loop;   
7.end;  
declare
cursor temp_cursor is  select name,age,address,id from cip_temps;
begin
for emp_record in temp_cursor loop
dbms_output.put_line(temp_cursor%rowcount||'第一行数据:'||emp_record.name||':'|| emp_record.age||':'||  emp_record.address||':'|| emp_record.id);
end loop;
end;


2、在游标for循环时直接使用子查询


Sql代码 

1.declare      
2.begin      
3.for emp_record in (select * from cip_temps) 

loop      
4.dbms_output.put_line('第一行数据:'||emp_record.name||':'|| emp_record.age||':'||  emp_record.address||':'|| emp_record.id);      
5.end loop;      
6.end;

for循环输出select：

declare
begin
for emp_record in (select * from emp)
loop
 dbms_output.put_line('员工姓名:'||emp_record.ename||'员工编号:'||emp_record.empno);
end loop;
end;