PL/SQL

hello world

sys@ORCL> edit
已写入 file afiedt.buf

  1  declare
  2  var_name varchar2(30);
  3  var_id number;
  4  begin
  5  select username
  6  into var_name
  7  from dba_users
  8  where user_id = &var_id;
  9  dbms_output.put_line('hello, '||var_name);
 10* end;
 11  /
输入 var_id 的值:  0
原值    8: where user_id = &var_id;
新值    8: where user_id = 0;
hello, SYS

PL/SQL 过程已成功完成。

sys@ORCL>

输出: dbms_output.put_line()
输入: 在变量名前加一个&实现
:=为赋值=判断是否相等
声明变量时数据类型放右边

%TYPE

声明一个变量与已知变量(或者已知表的某列)类型相同

v_name emp.ename%TYPE;
v_1 number(7,2);
v_2 v_1%TYPE

%ROWTYPE

前缀是表名
便于存储表的一行

游标

游动的光标,用于访问结果集中的一行数据

游标属性

SQL%ISOPEN
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT

declare
v_employee employees%ROWTYPE;
v1 employees.employee_id%TYPE :=100;
v2 number;
begin
select * into v_employee
from employees where employee_id=v1;
v2:=sql%rowcount;
dbms_output.put_line('选取行数:'||v2);
end;

游标操作

主要3步: 1.open 2.fetch 3.close

示例1 fetch一行数据

declare
cursor c1 is select first_name,last_name,salary from employees order by salary;
v1 c1%ROWTYPE;
begin
open c1;
fetch c1 into v1;
dbms_output.put_line(v1.first_name||' '||v1.last_name||' '||v1.salary);
close c1;
end;

示例2 loop循环遍历结果集

declare
cursor c1 is select first_name,last_name,salary from employees order by salary;
v1 c1%ROWTYPE;
n1 number;
begin
open c1;
loop
    fetch c1 into v1;
    exit when c1%notfound;
    dbms_output.put_line(v1.first_name||' '||v1.last_name||' '||v1.salary);
    n1:=c1%rowcount;
end loop;
close c1;
dbms_output.put_line('总行数'||n1);
end;

示例3 用for循环遍历 不需要open和close

declare
cursor c1 is select first_name,last_name,salary from employees order by salary;
n1 number;
begin
for v1 in c1 loop
    dbms_output.put_line(v1.first_name||' '||v1.last_name||' '||v1.salary);
    n1:=c1%rowcount;
end loop;
dbms_output.put_line('总行数'||n1);
end;

异常处理

TOO_MANY_ROWS
ZERO_DIVIDE
…