尚学堂马士兵Oracle学习笔记之五：PL_SQL(过程化SQL语言)

38、-------------------在客户端输出helloworld-------------------------------

       setserveroutput on;//默认是off，设成on是让Oracle可以在客户端输出数据

 

begin

       dbms_output.put_line('helloworld');

       end;

       /

----------------pl/sql变量的赋值与输出----

       declare

              v_namevarchar2(20);//声明变量v_name变量的声明以v_开头

       begin

              v_name:= 'myname';

              dbms_output.put_line(v_name);

       end;

       /

39、-----------pl/sql对于异常的处理(除数为0)-------------

       declare

              v_numnumber := 0;

       begin

              v_num:= 2/v_num;

              dbms_output.put_line(v_num);

       exception

              when others then

              dbms_output.put_line('error');

       end;

       /

40、----------变量的声明----------

       binary_integer:整数，主要用来计数而不是用来表示字段类型   比number效率高

       number:数字类型

       char:定长字符串

       varchar2：变长字符串

       date：日期

       long：字符串，最长2GB

       boolean：布尔类型，可以取值true，false，null//最好给一初值

----------变量的声明，使用 '%type'属性---------

       declare

              v_empnonumber(4);

              v_empno2emp.empno%type;

              v_empno3v_empno2%type;

       begin

              dbms_output.put_line('Test');

       end;

       /

       //使用%type属性，可以使变量的声明根据表字段的类型自动变换，省去了维护的麻烦，而且%type属性，可以用于变量身上

41、组合变量：

---------------Table变量类型(相当于java里面的数组)-------------------------------------------

       declare

              type type_table_emp_empno is table ofemp.empno%type index by binary_integer;

                     v_empnostype_tabletype_table_empno;

       begin

              v_empnos(0):= 7345;

              v_empnos(-1):=9999;

              dbms_output.put_line(v_empnos(-1));

       end;

42、-----------------Record变量类型（相当于Java里面的类）--------------------------------------------

       declare

              type type_record_dept is record

              (

                     deptnodept.deptno%type,

                     dnamedept.dname%type,

                     locdept.loc%type

              );

              v_temptype_record_dept;

       begin

              v_temp.deptno:=50;

              v_temp.dname:='aaaa';

              v_temp.loc:='bj';

              dbms_output.put_line(v_temp.deptno|| ' ' || v_temp.dname);

       end;

 

-----------使用 %rowtype声明record变量，直接参照表来声明record-------------------

       declare

              v_temp dept%rowtype;

       begin

              v_temp.deptno:=50;

              v_temp.dname:='aaaa';

              v_temp.loc:='bj';

       dbms_output.put_line(vtemp.deptno || '' || v temp.dname)                

       end;

      

43、 --------------select语句的运用(必须保证select语句有相应的返回记录)-------------------

       declare

              v_enameemp.ename%type;

              v_salemp.sal%type;

       begin

              select ename,salintov_ename,v_sal from emp where empno = 7369;

              dbms_output.put_line(v_ename|| '' || v_sal);

       end;

---------------------------select语句的应用（record）----------------------------------------

       declare

              v_emp emp%rowtype;

       begin

              select * into v_emp from emp whereempno=7369;

              dbms_output_line(v_emp.ename);

       end;

 

------------- insert语句的应用 -----------------------------

       declare

              v_deptnodept.deptno%type := 50;

              v_dnamedept.dname%type :='aaa';

              v_locdept.loc%type := 'bj';

       begin

              insert into dept2values(v_deptno,v_dname,v_loc);

              commit;

       end;

 

       -------------update 语句的应用------------------------------

       declare

              v_deptnoemp2.deptno%type := 50;

              v_count  number;

       begin

              update emp2 set sal = sal/2 where deptno =v_deptno;

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

              commit;

       end;

 

注：sql%rowcount统计上一条sql语句更新的记录条数

 

44、-----------------ddl语言，数据定义语言-----------------------

       begin

              executeimmediate 'create table T (nnn varchar(30) default ''a'')';

       end;

------------------ifelse语句--------------------------------------

    declare

              v_salemp.sal%type;

    begin

              selectsal into v_sal from emp where empno = 7369;

       if(v_sal< 2000) then

              dbms_output.put_line('low');

       elsif(v_sal > 2000) then

              dbms_output.put_line('middle');

       else

              dbms_output.put_line('height');

       end if;

     end;

45、-------------------do while循环 ---------------------------

       declare

              ibinary_integer := 1;

       begin

              loop

                            dbms_output.put_line(i);

                            i := i + 1;

                     exit when (i>=11);

              end loop;

       end;

---------------------while循环---------------------------

       declare

              jbinary_integer := 1;

       begin

              while j < 11 loop

                     dbms_output.put_line(j);

              j:=j+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;

46、-----------------------异常(1) ---------------------------

       declare

              v_tempnumber(4);

       begin

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

-----------------------异常(2) ---------------------------

       declare

              v_tempnumber(4);

       begin

              selectempno into v_temp from emp where empno = 2222;

       exception

              when no_data_found then

                     dbms_output.put_line('没有该项数据');

       end;

----------------错误记录日志(用表记录：将系统日志存到数据库便于以后查看) -----------

 

创建序列（用来处理递增的ＩＤ）：

       createsequence seq_errorlog_id start with 1 increment by 1;

 

       创建日志表:

       createtable errorlog

       (

       idnumber primary key,

       errcodenumber,

       errmsgvarchar2(1024),

       errdatedate

       );

 

       示例程序：

       declare

              v_deptnodept.deptno%type := 10;

              v_errcode  number;

              v_errmsgvarchar2(1024);

       begin

              deletefrom dept where deptno = v_deptno;

          commit;

       exception

              whenothers then

                     rollback;

                            v_errcode:= SQLCODE;

                            v_errmsg:= SQLERRM;

              insertinto errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);

                            commit;

       end;