Oracle学习笔记5

一． PL/SQL （ procedure language ）

1） Set serveroutput on;              -- 设置结果输出到屏幕

2） -- 异常处理，常见异常 too_many_rows ， no_data_found

Declare

V_num number := 0;

              Begin

                     V_num := 2 / v_num;

              Exception

                     When others then

                     Dbms_output.put_line(‘error’);

              End;

3） -- 声明变量

Declare

V_temp number(1);

V_count binary_integer := 0;

V_sal number(7, 2) := 4000.00;

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_temp value:’ || v_temp);

              End;

4） -- 变量声明，使用 %type 属性

Declare

V_empno number(4);

V_empno2 emp.empno%type;

V_empno3 v_empno2%type;

              Begin

              End;

5） --table 变量类型，相当于数组

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;

End;

6） --record 变量类型，相当于类

Declare

         Type type_record_dept is record

         (

                Deptno dept.deptno%type,

                Dname dept.dname%type,

                Loc dept.loc%type

         );

         V_temp type_record_dept;

         --v_temp dept%rowtype;                 // 使用 %rowtype 声明 record 变量

Begin

         V_temp.deptno := 50;

         V_temp.dname := ‘aaaa’;

              End;

7） --select 语句使用，必须带 into ，而且结果只能是一条

Declare

         V_ename emp.ename%type;

         V_sal emp.sal%type;

         V_emp emp%rowtype;

Begin

         Select ename, sal into v_ename, v_sal from emp where empno = 9999;

         Select * into v_emp from emp where empno = 9999;

 

End;

8） --insert 语句，必须带 commit

Declare

         V_deptno dept.deptno%type := 50;

         V_dname dept.dname%type := ‘aaaa’;

Begin

         Insert into dept values (v_deptno, v_dname);

         Commit;

End;

9） --update 语句

Declare

         V_deptno emp.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 empno = 9999;                   // 影响一条记录

         Select count(*) into v_count from emp;                                              // 影响一条记录

         Dbms_output.put_line(sql%rowcount || ‘ 条记录被影响 ’);

         Commit;

End;

10）              --ddl 语句

Begin

         Execute immediate ‘create talbe t (name varchar2(20) default ‘’a’’ )’;

End;

11）              --if 语句

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

                       Dbms_output.put_line(‘middle’);

         Else

                       Dbms_output.put_line(‘high’);

         End if;

End;

12）              --do while 循环

Declare

         I binary_integer := 1;

Begin

         Loop

                Dbms_output.put_line(i);

                I := I + 1;

                Exit when (I >= 11);

         End loop;

End;

              --while 循环

Declare

         I binary_integer := 1;

Begin

         While I < 11 loop

                Dbms_output.put_line(i);

                I := I + 1;

                     End loop;

End;

--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;

13）              -- 记录出错日志

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;               -- 系统提供 sqlcode 出错代码

                V_errmsg := sqlerrm;                        -- 系统提供 sqlerrm 出错信息

                Insert into errorlog values (seq.nextval, v_errcode, v_errmsg, sysdate);

                Commit;

End;