Oracle PL/SQL编程

1、变量
变量首字符必须是字母；最多30个字符；不能与表、列同名；一行声明一个变量；含_,$,#，不分大小写。

变量类型
binary_integer：整数，用于计数
number：数值
char：定长字符串
varchar2：变长字符串
date：日期
long：长字符串，最长2G
boolean：布尔类型，true/false/null，不能在dbms_output中输出

变量声明
declare
    v_temp number(1);
    v_count binary_integer := 0;
    v_sal number(7, 2) := 4000.0;
    v_date date := sysdate;
    v_pi constant number(3, 2) := 3.14;
    v_valid boolean := false;
    v_name varchar2(20) not null := 'MyName';
begin
    --||拼接为串
    dbms_output.put_line('v_name 的值为：'||v_name||'，v_sal 的值为:'||v_sal);
end;
/

默认不显示输出信息，设置：
set  serveroutput on size 10000  --最多支持1M
L   --显示语句块
/ 　--执行
save c:/plsql_01.txt
dbms_output.put_line('Hello World!');
dbms_output.put('string');
dbms_output.new_line;
@ c:/plsql_01.txt
行注释：--
块注释：/* ...  */


(1)字段类型
declare
    v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno = 7369;
    if (v_sal < 1200) then
       dbms_output.put_line('low');
    elsif(v_sal <2000) then  --elsif 而不是 elseif
       dbms_output.put_line('middle');
    else
       dbms_output.put_line('high');
    end if;
end;
/

(2)数组类型
declare
    type type_table_emp_empno is table of emp.empno%type index by binary_integer;
    v_empnos type_table_emp_empno;
begin
    v_empnos(0) := 7369;
    v_empnos(-1) := 9999;
    dbms_output.put_line(v_empnos(-1));
end;
/

(3)记录类型
支持select语句的返回值，使用记录可以将一行数据看成一个单元处理。

declare
type myrecord is record(
    deptno dept.deptno%TYPE, --number,使用与dept表deptno相同类型和长度
    dname varchar2(14),
    loc varchar2(13)
);
real_record myrecord;
begin
   select deptno,dname,loc into real_record from dept where deptno=10;
   dbms_output.put_line(real_record.deptno||','||real_record.dname||','||real_record.loc);
end;
/
只能返回一行，不能返回多行。

简捷方式：
declare
   myrec dept%ROWTYPE; --使得myrecord与dept表一行的定义相同，每个元素使用列名。
begin
   select * into myrec from dept where deptno=10; --或者
   select deptno,dname,loc into myrec from dept where deptno=10;
   dbms_output.put_line(myrec.deptno||','||myrec.dname||','||myrec.loc);
end;
/

2.查询语句
PL/SQL中的select语句必须返回一条记录且只返回一条记录，返回多行数据或没有返回数据均报错。

定义列类型
declare
   v_name emp.ename%type;
   v_sal emp.sal%type;
begin
  select ename, sal into v_name, v_sal from emp where empno = 7369;
  dbms_output.put_line(v_name ||'-'|| v_sal);
end;
/

定义行类型
declare
    v_temp emp%rowtype;
begin
    select * into v_temp from emp where empno=7369;
    dbms_output.put_line(v_temp.ename || ' ' || v_temp.empno);
end;
/
 
保留变量 ：sql%rowcount
declare
    v_deptno dept.deptno%type := 10;
    v_count number;
begin  
    update emp set sal = sal/2 where deptno=v_deptno;
    --select deptno into v_deptno from emp where deptno =v_deptno;
    --select count(*) into v_count from emp;
    dbms_output.put_line(sql%rowcount || '条记录被影响！');
    rollback;
end;
/
select empno,ename,sal from emp where deptno=10;

3.DDL语句
begin
    execute immediate 'create table tsql (id number(5),sql varchar2(100) default ''select * from emp;'')';
end;
/

4、分支语句
--if分支
declare
    a number;
    b varchar2(10);
begin
    a:=2;
    if a=1 then
       b:='A';
    elsif a=2 then
       b:='B';
    else
       b:='C';
    end if;
    dbms_output.put_line('b的值为：'||b);
end;
/
--case分支
declare
    a number;
    b varchar2(10);
begin
    a:=2;
    case
        when a=1 then b:='A';
        when a=2 then b:='B';
        else b:='C';
    end case;
    dbms_output.put_line('b的值为：'||b);
end;
/

5.循环语句
(1)loop循环
declare
   i binary_integer := 1;
begin
    loop
        dbms_output.put_line(i);
        i := i + 1;
        exit when ( i>= 11);
    end loop;
end;
/

(2)while循环
declare
    i binary_integer := 1;
begin
   while i < 11 loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
/

(3)for循环
begin
    for k in 1..10 loop
        dbms_output.put_line(k);
    end loop;
    for k in reverse 1..10 loop
        dbms_output.put_line(k);
    end loop;
end;
/

6.错误处理
--除0异常
declare
    v_num number := 0;
begin
    v_num := 2/v_num;
    dbms_output.put_line(v_num);
exception
    when others then
        dbms_output.put_line('error');
end;
/
--select异常
declare
    test varchar2(10);
begin
    select dname into test from dept where deptno=1;
    dbms_output.put_line(test);
exception
    when NO_DATA_FOUND then
        dbms_output.put_line('没有找到数据');
end;
/

--多个异常
declare
    v_temp number(4);
begin
    select empno into v_temp from emp where deptno = 10;
    exception
        when too_many_rows then
            dbms_output.put_line('太多记录了');
        when others then
            dbms_output.put_line('error');
end;
/

--自定义异常
declare
    tname varchar2(10);
    e exception;
begin
    select dname into tname from dept where deptno=10;
    if tname<>'SALES' then
        raise e;
    end if;
    dbms_output.put_line(tname);
exception
    when e then
        dbms_output.put_line('没有找指定部门的数据');
end;
/

--记录错误的方法
create sequence seq_errorlog_id start with 1 increment by 1;
create table errorlog
(
    id number primary key,
    errcode number,
    errmsg varchar2(1024),
    errdate date
);

declare
    v_deptno dept.deptno%type := 10;
    v_errcode number;
    v_errmsg varchar2(1024);
begin
    delete from dept where deptno = v_deptno;
    commit;
    exception
        when others then
            rollback;
            v_errcode:= SQLCODE; --出错代码
            v_errmsg := SQLERRM;  --出错信息
            insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
            commit;
end;
/
select to_char(errdate,'yyyy-mm-dd hh24:mi:ss') from errorlog;