<PLSQL 入门>~

declare
begin
exception
end

 

 

SQL> set serveroutput on
SQL> begin
  2         dbms_output.put_line('Hello world!');
  3  end;
  4  /
Hello world!

 

 

 SQL>  declare
  2    v_name varchar2(20);
  3    begin
  4       v_name := 'myname';
  5       dbms_output.put_line(v_name);
  6    end;
  7    /
myname

 

SQL> declare
  2         v_num number :=0;
  3  begin
  4         v_num := 2/v_num;
  5         dbms_output.put_line(v_num);
  6  exception
  7         when others then
  8                dbms_output.put_line('error');
  9  end;
 10  /
error

 

变量声明的规则

        (1) 变量不能是保留字        通常： v_

        (2) 第一个字符必须是字母

        (3) 变量名长度小于30

        (4) 不能与表名，列名相同

        (5) 每行只能声明一个变量

 

常用变量类型

       (1) binary_integer          整数，主要用于计数

       (2) number                    数字类型

       (3) char,varchar2

       (4) date

       (5) long

       (6) boolean                  true, false,null

 

注释

/* ... */

//

 

 %type

 

v_empno2 em.empno%type;

v_empno3 v_empno2%type;

 

Table  变量类型

    type   t_table_emp_empno is table of emp.empno%type index by binary_integer;

    v_empnos t_table_emp_empno;

 

Record 变量类型

     type t_record_dept is record

         (      

                 deptno dept.deptno%type,

                 dname dept.dname%type,

                 loc        dept.loc%type

         );

     v_temp    t_record_dept;

 

     v_temp      dept%rowtype

 

Select 语句

    declare

          v_ename emp.ename%type;

          v_sal       emp.sal%type;

     begin

          select ename,sal into v_ename,v_sal from emp where empno=7369;

          dbms_output.put_line(v_ename);

     end;/

必须返回一行记录，否则报错

         v_emp   emp%rowtype

         select * into v_emp from emp where empno=7369;

 

Insert , update语句

 

         insert into dept2 values (v_deptno,v_dname,v_loc);

         commit;

 

  dbms_output.put_line(sql%rowcount) || '记录被影响'

 

 DDL语句

 begin

  execute immediate  'create table T(nnn varchar2(20))';

 end;/

 

 IF 语句

      if (v_sal < 1200) then

             xxx;

     elseif (v_sal < 2000) then

          xxx;

      else

          xxx;

      end if;

 

 循环语句

       loop

             xxx;

              i := i + 1;

              exit when (i >= 11);

       end loop;

 

 

       while j < 11 loop

             j := j + 1;

        end loop;

 

 

       for k in 1..10 loop

             xxx;

       end loop;

 

       for k in reverse 1..10 loop

             xxx;

       end loop;

 

 

  异常处理

      

        exception

             when   too_many_rows then

                   xxxxx;

             when   no_data_found then

                   xxxxx;

             when   others then

                   xxxxx;

  

   游标

     declare

        cursor c is select * from emp;

        v_emp c%rowtype

      begin

            open c;

            loop

               fetch  c into v_emp;

                   exit when (c%notfound);

                   dbms_output.put_line(v_emp.ename);

            end loop;

            close c:

       end;

 

     4个属性 

      c%isopen, c%notfound,c%found,c%rowcount

 

      for v_emp in c loop

             xxx;

      end

 

     游标自动打开，关闭，自动fetch

 

    带参数的游标

   

        declare

            cursor c (v_deptno   emp.deptno%type, v_job emp.job%type)

            is   select xxx where xxx=xxx,xxx

 

        begin

               for v_temp in c(30,'CLERK') loop

                     xxx;

               end loop;

        end;

 

     可更新的游标

 

  declare

            cursor c (v_deptno   emp.deptno%type, v_job emp.job%type)

            is   select xxx where xxx=xxx,xxx  for update

 

           update emp2 set sal = sal * 2 where current of c;

           delete from emp2 where current of c;

         

 Store proceduer

 

     create or replace procedure p

     is  (取代declare)

         plsql code

    

    执行： exec p;

              或者

                 begin

                     p;

                 end;

 

 

   带参数的存储过程

             

         create or replace procedure p (v_a in number, v_b number, v_ret out number, v_temp in out number)

         is

 

             plsql code

 

   

        in: 传入参数    默认是in

        out: 传出参数

                 

     declare

         v_a number :=3;

         v_b number :=4;

         v_ret number;

         v_temp number :=5;

      begin

             p(xx,xx,xx,xx);

           xxx;

       end;

 

   SQL> show error                 显示存储过程的编译错误

 

 

 函数function

     

       create or replace function f (v_sal number) return number

       is

 

           函数体

 

       v_ret := f();

 

       select f() from dual;

 

  Trigger 触发器

     

      create or replace trigger trig

      after insert or update or delete on emp for each row

     begin

           if inserting then

              xxx

           .................................

 

     end

 

       

    :New.deptno       :Old.deptno