Oracle中PL/SQL的用法总结

declare

      age number(3):=26; --长度为3，初始值为26

begin

      commit ;

end ;

declare

      pi constant number(9):=3.1415926; --为pi的数字型常量，长度为9，初始值为3.1415926

begin

      commit ;

end ;

set serveroutput on

  Declare

     result integer ;

  begin

     result:=10+3*4-20+5**2;

     dbms_output.put_line( '运算结果是：' ||to_char(result));

  end ;

BEGIN

      DBMS_OUTPUT.PUT_LINE( 'Hello PL/SQL' );

END ;

set serveroutput on

DECLARE

    v_char varchar2(20):= 'a' ;

    v_char1 varchar2(20):= 'b' ;

BEGIN

    DBMS_OUTPUT.PUT_LINE(v_char);

    DBMS_OUTPUT.PUT_LINE(v_char1);

  END ;

begin

   dbms_output.put_line( 'Hello World' );

   dbms_output.put_line( '2*3=' ||(2*3));

   dbms_output.put_line( 'what' 's' );

  end ;

declare

      v_number1 number;

      v_number2 number(3,2) ;

      v_number3 binary_integer :=1;

      v_name varchar2(20) := 'kettas' ;

      v_date date :=sysdate;

      v_long long := 'ni hao' ;

      v_b boolean := true ;

begin

      if (v_number1 is null ) then

      dbms_output.put_line( 'hello' );

      end if;

      dbms_output.put_line(v_number1);

      dbms_output.put_line(v_number2);

      dbms_output.put_line(v_number3);

      dbms_output.put_line(v_name);

      dbms_output.put_line(v_date);

      dbms_output.put_line(v_long);

    --dbms_output.put_line(v_b); --执行该句ORACLE提示[调用'PUT_LINE' 时参数个数或类型错误]

end ;

DECLARE

   v_Number number(4,3):=123.12312; --实际精度6位大于上限精度值4位，提示[ORA-06502:PL/SQL: 数字或值错误 :  数值精度太高]

BEGIN

   DBMS_OUTPUT.PUT_LINE(v_Number);

END ;

DECLARE

   v_Number number(7,3):=4555; --实际精度7位等于上限精度值，可以存储

BEGIN

   DBMS_OUTPUT.PUT_LINE(v_Number);

  END ;

declare

     v_number1 number:=100;

     v_number2 number;

begin

     v_number2:=v_number1;

     v_number1:=200;

     dbms_output.put_line(v_number1); --200

     dbms_output.put_line(v_number2); --100

end ;

declare

     type t_first is record(

          idnumber(3),

          namevarchar2(20)

     );

     v_first t_first;

begin

       v_first.id:=1;

       v_first. name := 'cheng' ;

       dbms_output.put_line(v_first.id);

       dbms_output.put_line(v_first. name );

  end ;

declare

     type t_first is record(

       id number,

       name varchar2(20)  

     );

     v_first t_first;

     v_second t_first;

begin

     v_first.id:=1;

     v_first. name := 'susu' ;

     v_second:=v_first; --相互赋值

     v_first.id:=2;

     v_first. name := 'kettas' ;

     dbms_output.put_line(v_first.id);

     dbms_output.put_line(v_first. name );

     dbms_output.put_line(v_second.id);

     dbms_output.put_line(v_second. name );

  end ;

declare

      type t_tb is table of varchar2(20) index by binary_integer;

      v_tb t_tb;

  begin

    v_tb(100):= 'hello' ;

    v_tb(98):= 'world' ;

    dbms_output.put_line(v_tb(100));

    dbms_output.put_line(v_tb(98));

  end ;

declare

      type t_rd is record(id number,namevarchar2(20));

      type t_tb is table of t_rd index bybinary_integer;

      v_tb2 t_tb;

  begin

      v_tb2(100).id:=1;

      v_tb2(100). name := 'hello' ;

      --dbms_output.put_line(v_tb2(100).id);

     --dbms_output.put_line(v_tb2(100).name);

     dbms_output.put_line(v_tb2(100).id|| '    ' ||v_tb2(100). name );

  end ;

declare

    type tabletype1 is table of testtable%rowtypeindex by binary_integer;

    table1 tabletype1;

  begin

     select * into table1(60) fromtempuser.testtable where recordnumber=60;

    dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);

  end ;

set serveroutput on

declare

      type tabletype1 is table ofvarchar2(9) index by binary_integer;

      table1 tabletype1;

begin

      table1(1):= '成都市' ;

      table1(2):= '北京市' ;

      table1(3):= '青岛市' ;

      dbms_output.put_line( '总记录数：' ||to_char(table1. count ));

      dbms_output.put_line( '第一条记录：' ||table1. first );

      dbms_output.put_line( '最后条记录：' ||table1. last );

      dbms_output.put_line( '第二条的前一条记录：' ||table1. prior (2));

      dbms_output.put_line( '第二条的后一条记录：' ||table1. next (2));

  end ;

create table student(

     id number,

     name varchar2(20),

     age number(3,0)

  );

  insert into student(id, name ,age) values (1, 'susu' ,23);

--查找一个字段的变量

declare

     v_name varchar2(20);

     v_name2 student. name %type;

  begin

     select name into v_name2 from studentwhere rownum=1;

     dbms_output.put_line(v_name2);

  end ;

--查找多个字段的变量

declare

      v_id student.id%type;

      v_name student. name %type;

      v_age student.age%type;

  begin

    select id, name ,age into v_id,v_name,v_age fromstudent where rownum=1;

    dbms_output.put_line(v_id|| ' ' ||v_name|| '  ' ||v_age);

  end ;

--查找一个类型的变量，推荐用*

declare

     v_student student%rowtype;

  begin

     select * into v_student from student whererownum=1;

     dbms_output.put_line(v_student.id|| ' ' ||v_student. name || '  ' ||v_student.age);

  end ;

--也可以按字段查找，但是字段顺序必须一样，不推荐这样做

declare

     v_student student%rowtype;

  begin

   select id, name ,age into v_student from student whererownum=1;

   dbms_output.put_line(v_student.id|| ' ' ||v_student. name || '  ' ||v_student.age);

  end ;

  declare

     v_student student%rowtype;

  begin

   select id, name ,age intov_student.id,v_student. name ,v_student.age from student where id=1;

   --select * intov_student.id,v_student.name,v_student.age from student where id=1;

   dbms_output.put_line();

  end ;

declare

     v_name student. name %type:= 'wang' ;

  begin

     insert into student(id, name ,age) values (2,v_name,26);

  end ;

begin

     insert into student(id, name ,age) values (5, 'hehe' ,25);

  end ;

  declare

     v_name student. name %type:= 'hexian' ;

  begin

     update student set name =v_name where id=1;

  end ;

  begin

     update student set name = 'qinaide' whereid=2;

  end ;

declare

          v_i1binary_integer:=1;

begin

       declare

          v_i2binary_integer:=2;

       begin

         dbms_output.put_line(v_i1);

         dbms_output.put_line(v_i2);

       end ;

    dbms_output.put_line(v_i1);

--dbms_output.put_line(v_i2);  解开后执行Oracle会提示[必须说明标识符'V_I2']

end ;

declare

     v_b boolean:= true ;

begin if v_b then

       dbms_output.put_line( 'ok' );

    end if;

  end ;

if else 判断

declare

      v_b boolean:= true ;

  begin

   if v_b then

      dbms_output.put_line( 'ok' );

   else

      dbms_output.put_line( 'false' );

   end if;

end ;

  if elsif else 判断

declare

      v_name varchar2(20):= 'cheng' ;

  begin

   if v_name= '0701' then

      dbms_output.put_line( '0701' );

   elsif v_name= 'cheng' then

      dbms_output.put_line( 'cheng' );

   else

      dbms_output.put_line( 'false' );

   end if;

  end ;

declare

     v_i binary_integer:=0;

  begin

   loop

       if v_i>10 then

          exit;

       end if;

       v_i:=v_i+1;

       dbms_output.put_line( 'hehe' );

   end loop;

       dbms_output.put_line( 'over' );

  end ;

declare

     v_i binary_integer :=0;

  begin

   loop

       exit when v_i>10;

       v_i :=v_i+1;

       dbms_output.put_line( 'hehe' );

   end loop;

       dbms_output.put_line( 'over' );

  end ;

declare

     v_i binary_integer:=0;

  begin

     while v_i<10 loop

          dbms_output.put_line( 'hello' ||v_i );

          v_i:=v_i+1;

     end loop;

     dbms_output.put_line( 'over' );

  end ;

begin

   for v_i in 0..10 loop

      dbms_output.put_line( 'hello' ||v_i);

   end loop;

       dbms_output.put_line( 'over' );

end ;

begin

        dbms_output.put_line(1/0);

  exception

          when othersthen

             dbms_output.put_line( 'error' );

  end ;

  declare

         e_myException exception;

  begin

         dbms_output.put_line( 'hello' );

          raisee_myException; --raise抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。

         dbms_output.put_line( 'world' );

         dbms_output.put_line(1/0);

  exception

          whene_myException then

             dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码

             dbms_output.put_line(sqlerrm); --当前错误信息

             dbms_output.put_line( 'my error' );

          when othersthen

             dbms_output.put_line( 'error' );

  end ;

declare

        cursor c_studentis  select * from book;

  begin

        open c_student;

        close c_student;

  end ;

declare

        v_id binary_integer;

        cursor c_student isselect * from book where id>v_id;

  begin

        v_id:=10;

        open c_student;

        close c_student;

  end ;

declare

        cursor c_student(v_idbinary_integer) is select * from book where id>v_id;

  begin

        open c_student(10);

        close c_student;

  end ;

declare

        v_student book%rowtype;

        cursor c_student(v_idbinary_integer) is select * from book where id>v_id;

  begin

        open c_student(10);

        fetch c_student intov_student;

        close c_student;

        dbms_output.put_line(v_student. name );

  end ;

create table BOOK

  (

  ID       VARCHAR2(10) not null ,

  BOOKNAME VARCHAR2(10) not null ,

  PRICE    VARCHAR2(10) not null ,

  CID      VARCHAR2(10) not null

  );

--insert

  create or replace procedure say_hello(

  i_name in varchar2,

o_result_msg out varchar2

  )

   as

   v_price varchar2(100);

   e_myException exception;

  begin

      insert into book(id,bookname,price) values (1,2,3);

      o_result_msg := 'success' ;

   exception

      when others then

          rollback ;

          o_result_msg := substr(sqlerrm, 1, 200);

   end ;

--update or delete

  create or replace procedure say_hello(

  i_name in varchar2,

  o_result_msg out varchar2

  )

   as

   v_price varchar2(100);

   e_myException exception;

  begin

      update book set price = '55' wherebookname = i_name;

      delete from book where bookname =i_name;

      if sql%notfound then

         raisee_myException;

      end if;

      /*

  if sql%rowcount = 0 then --写法2

         raisee_myException;

      end if;

  */

      o_result_msg := 'success' ;

   exception

      when e_myException then

          rollback ;

          o_result_msg := 'update or delete dail' ;

   end ;

--select

  create or replace procedure say_hello(

  i_name in varchar2,

  o_result_msg out varchar2

  )

   as

   v_price varchar2(100);

   e_myException exception;

  begin

      select price into v_price from bookwhere bookname = i_name;

      o_result_msg := 'success' ;

   exception

      when no_data_found then

          rollback ;

          o_result_msg := 'select into dail' ;

   end ;

declare

        v_bookname varchar2(100);

        cursor c_book(i_idnumber) is select bookname from book where id = i_id;

  begin

      Open   c_book(i_id);

      Loop

          Fetch c_bookinto v_bookname;

          exit whenc_student%notfound;

           update book set price = '33' where bookname = v_bookname;

      End Loop;

      Close c_book;

  end ;

declare

        v_bookname varchar2(100);

        cursor c_book(i_idnumber) is select bookname from book where id = i_id;

  begin

      Open   c_book(i_id);

        Fetch c_book intov_bookname;

        While c_book%Found

        Loop

           update book set price = '33' where bookname = v_bookname;

        Fetch   c_book intov_bookname;

        End Loop;

      Close c_book;

  end ;

declare

       v_bookname  varchar2(100);

       cursor c_book(i_id number) isselect bookname from book where id = i_id;

  begin

       Open   c_book(i_id);

       while nvl(c_book%found, true ) --或这种写法：while c_book%found is null or c_book%foundloop   

           Fetchc_book into v_bookname;

           updatebook set price = '33' where bookname = v_bookname;

       End Loop;

       Close c_book;

  end ;

declare

       cursor c_book(i_id number) isselect bookname from book where id = i_id;

begin

       for cur in c_book(i_id) --直接将入参i_id传入cursor即可

       loop

           updatebook set price = '53' where bookname = cur.bookname;

       end loop;

  end ;

declare

       i number:=0;

  begin

       if i=0 then

           gotohello;

       end if;

       <<hello>>

       begin

          dbms_output.put_line( 'hello' );

           gotoover;

       end ;

       <<world>>

       begin

          dbms_output.put_line( 'world' );

           gotoover;

       end ;

       <<over>>

          dbms_output.put_line( 'over' );

   end ;

set serveroutput on

   var ret1 varchar2(200);

   var ret2 varchar2(200);

   exec sp_get_product_prompt(83,:ret1,:ret2); --或execute

   print ret1;

   print ret2;

   或

   set serveroutput on

   declare

      ret1 varchar2(200);

      ret2 varchar2(200);

   begin

      sp_get_product_prompt(83,ret1,ret2);

      dbms_output.put_line(ret1);

      dbms_output.put_line(ret2);

   end ;

create or replace procedure say_hello(

     v_name in varchar2,

     v_flag number,

     o_ret out number

   )

   as

   begin

     if v_name is null and v_flag is null then --v_name和v_flag都等于null

        o_ret := 10;

     else

         o_ret := 100;

     end if;

   end ;

create or replace procedure say_hello(

     i_name in varchar2,

     i_flag number,

     o_ret out number

   )

   as

     v_name  varchar2(100);

   begin

     if i_name is null then

        v_name := '0' ;

     else

        v_name := i_name;

     end if;

     insert into phone(..,wname..,) values (..,v_name,..); 

   end ;

insert intophone(..,wname..,) values (..,nvl(v_name, ' ' ),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值

create or replace procedure say_hello(v_name in varchar2)

   as

   begin

      --v_name:='a'; --存储过程入参v_name不能做为赋值目标

     dbms_output.put_line( 'hello ' ||v_name);

   end ;

create or replace procedure say_hello(

      i_opFlag in number

   )

   as

      v_name varchar2(100);

   begin

      if i_opFlag = 1 then

v_name := '0' ;

      else

v_name := 'haha' ;

      end if;

      dbms_output.put_line( 'hello' ||v_name);

   end ;

create or replace procedure say_hello(

      i_name in varchar2

   )

   as

      v_name varchar2(100);

   begin

      if i_name is null then

v_name := '0' ;

      else

v_name :=i_name; --将入赋值给定义变量

      end if;

      dbms_output.put_line( 'hello' ||v_name);

   end ;

create or replace procedure say_hello(

      v_first_name in varchar2,

      v_last_name in varchar2)

   as

   begin

      dbms_output.put_line( 'hello' ||v_first_name|| '.' ||v_last_name);

   end ;

create or replaceprocedure say_hello(

      v_name in varchar2,

      v_content out varchar2

   )

   begin

      v_content:= 'hello' ||v_name;

   end ;

declare

      v_con varchar2(200);

      v_in varchar2(20):= 'wang' ;

   begin

      say_hello(v_in,v_con);

      dbms_output.put_line(v_con);

   end ;

create or replace procedure say_hello(v_name in outvarchar2)

   as

   begin

      v_name:= 'hi ' ||v_name;

   end ;

declare

      v_inout varchar2(20):= 'wangsu' ;

   begin

      say_hello(v_inout);

      dbms_output.put_line(v_inout);

   end ;

create or replace procedure say_hello(

      v_name varchar2 default 'susu' ,

      v_content varchar2 default 'hello'

   )

   as

   begin

      dbms_output.put_line(v_name|| '' ||v_content);

   end ;

begin

      say_hello();

   end ;

begin

      say_hello( 'cheng' );

   end ;

begin

   say_hello(v_name=> 'cheng' );

  end ;

create or replace function func(v_name in varchar2)

return varchar2

is

begin

     return (v_name|| ' hello' );

  end ;

declare

     v_name varchar2(20);

begin

     v_name:=func( 'cheng' );

     dbms_output.put_line(v_name);

  end ;

create or replace function func(

     v_name in varchar2,

     v_content out varchar2

)

return varchar2

is

begin

     v_content:=v_name|| ' hello' ;

     return v_content;

  end ;

declare

     v_name varchar2(20);

     v_name1 varchar2(20);

begin

     v_name1:=func( 'susu' ,v_name); --返回v_name值

     dbms_output.put_line(v_name1); --打印func结果

     dbms_output.put_line(v_name); --打印v_name结果

end ;

create or replace function func(

     v_name in out varchar2)

return varchar2

is

begin

     v_name:=v_name|| ' hello' ;

     return 'cheng' ;

  end ;

declare

     v_inout varchar2(20):= 'world' ;

     v_ret varchar2(20);

begin

     v_ret:=func(v_inout); --返回调用v_inout值(作为出参)

     dbms_output.put_line(v_ret); --打印func结果   

     dbms_output.put_line(v_inout); --返回v_name结果

end ;