plsql学习

--设置数据库输出，默认为关闭，每次新打开窗口都要重新设置
set serveroutput on

--调用     包           函数    参数
execute dbms_output.put_line('hello world');
--或者用call调用，相当于java中的调试程序打桩
call dbms_output.put_line('hello world');

--pl语句块是pl/sql里最小的编程块，其中可以再嵌套begin end
begin
	dbms_output.put_line('Hello World');
	dbms_output.put_line('2*3='||(2*3));
	dbms_output.put_line('what''s');
end;

--如何声明变量，所有变量必须再declare中声明，程序中不允许声明
--没有初始化的变量默认值为null，屏幕上null是看不见的，命名习惯：一般变量以v_开头
--注意number也能存小数，最长38位，所以以后建议整数都用binary_integer存
--long是字符类型，boolean类型补能打印
--标准变量类型：数字，字符，时间，布尔

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);
end;
/
--组合类型：record table
--record类型最常用，声明的时候可以加not null，但必须给初始值
--如果record类型一致可以相互赋值，如果类型不同，里面的字段恰好相同，不能互相赋值
declare
       type t_first is record(
            id number(3),
            name varchar2(20)
       );
       v_first t_first;
begin
     v_first.id:=1;
     v_first.name:='ws';
     dbms_output.put_line(v_first.id);
     dbms_output.put_line(v_first.name);
end;
/

--考虑一下，oracle中赋值方式是拷贝还是引用方式
declare
       --v_number1 number:=1;
       --v_number2 number;
       type t_first is record(
         id number,
         name varchar2(20)   
       );
       v_first t_first;
       v_second t_first;
begin
       --v_number2:=v_number1;
       --v_number1:=2;
       --dbms_output.put_line(v_number1);
       --dbms_output.put_line(v_number2);
       v_first.id:=1;
       v_first.name:='susu';
       
       
       --v_second:=v_first;
       v_second.id:=v_first.id;
       v_second.name:=v_first.name;
       
       
       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;
 /
 
 -------------------------
 --table类型，相当于java中的map，就是一个可变长的数组，key必须是整数，可以是负数，value可以是标量，也可以是record
 --可以不按顺序赋值，但必须先赋值后使用
 
 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,name varchar2(20));
        type t_tb is table of t_rd index by binary_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;
/

--%type 和 %rowtype 以及如何从数据库把数据取回来
create table student33(
       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 student33 where rownum=1;
       dbms_output.put_line(v_name2);
end;
/

--查找多个字段的变量
declare
        v_id student33.id%type;
        v_name student33.name%type;
        v_age student33.age%type;
begin
      select id,name,age into v_id,v_name,v_age from student33 where rownum=1;
      dbms_output.put_line(v_id||'  '||v_name||'  '||v_age);
end;
/
--查找一个类型的变量，推荐用*

declare
       v_student student33%rowtype;
begin
       select * into v_student from student33 where rownum=1;
       dbms_output.put_line(v_student.id||'  '||v_student.name||'  '||v_student.age);
end;
/

--也可以按字段查找，但是字段顺序必须一样，不推荐这样做
declare
       v_student student33%rowtype;
begin
     select id,name,age into v_student from student33 where rownum=1;
     dbms_output.put_line(v_student.id||'  '||v_student.name||'  '||v_student.age);
end;
/

declare
       v_student student33%rowtype;
begin
     select id,name,age into v_student.id,v_student.name,v_student.age from student33 where id=1;
     --select * into v_student.id,v_student.name,v_student.age from student33 where id=1;
     dbms_output.put_line();
end;
/

--注意：insert，update，delete，select都可以，create table，drop table不行
--dpl，dml，和流程控制可以在pl/sql里用，ddl不行
declare
       v_name student33.name%type:='wang';
begin
     insert into student33(id,name,age) values(2,v_name,26);
end;
/
--下边这种方式也可以
begin
     insert into student33(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 student33 set name='qinaide' where id=2;
end;
/

--delete
begin
     delete from student33 where id=5;
end;
/

--变量的可见空间
declare
       v_i1 binary_integer:=1;
begin
     declare
            v_i2 binary_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);
end;
/

------------------------流程控制

--if判断
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):='ws';
begin
     if v_name='0701' then
        dbms_output.put_line('0701');
     elsif v_name='ws' then
        dbms_output.put_line('ws');
     else
         dbms_output.put_line('false');
     end if;
end;
/ 

--loop循环，注意推出exit是推出循环，而不是推出整个代码块
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;
/

--while循环
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;
/

--for循环，注意不需要声明变量
begin
     for v_i in 0..10 loop
         dbms_output.put_line('hello'||v_i);
     end loop;
         dbms_output.put_line('over');
end;
/

--练习1用循环往student里插入30条记录
--要求：id为0,1,2...
--			name为kettas0,kettas1,kettas2...
--			age为11,12,13...
--练习2，假设不知道数据库里的数据规则和数量,
--把所有的student33数据打印到终端

--问题1答案
declare
       v_student student33%rowtype;
begin
     delete from student33;
     for v_i in 0..20 loop
         insert v_student into student33(id,name,age) values(v_i,'kettas'||v_i,10+v_i);
     end loop;
end;
/
--也可以这样
begin
     delete from student33;
     for v_i in 0..20 loop
         insert into student33 values(v_i,'susu'||v_i,18+v_i);
     end loop;
end;

--问题2答案
--rownum是伪列，在表里没有
--数据库先是执行from student33遍历student33表，如果没有where条件过滤，则先做成一个结果集，然后再看select
--后面的条件挑出合适的字段形成最后的结果集，如果有where条件，则不符合条件的就会从第一个结果集中删除
--后面的数据继续加进来判断，所以如果直接写rownum=2，或者rownum>10这样的语句就查不出数据
--可以用一个子查询解决
select rownum,id from student33 where rownum=2;

declare
       v_number binary_integer;
       v_student student33%rowtype;
begin
     select count(*) into v_number from student33;
     for i in 1..v_number loop
         select id,name,age into v_student from(
                select rownum rn,id,name,age from student33
         )where rn=i;
         dbms_output.put_line('id: '||v_student.id||' name:'||v_student.name);
     end loop;
end;

--异常的定义使用
begin
     dbms_output.put_line(1/0);
exception
         when others then
              dbms_output.put_line('error');
end;

declare
       e_myException exception;
begin
     dbms_output.put_line('hello');
     raise e_myException;--raise抛出异常,用此关键字向外抛异常
     dbms_output.put_line('world');
     dbms_output.put_line(1/0);
exception
         when e_myException then
              dbms_output.put_line(sqlcode);--当前会话执行状态，错误编码
              dbms_output.put_line(sqlerrm);--当前错误信息
              dbms_output.put_line('my error');
         when others then
              dbms_output.put_line('error');
end;

--error表，每次有异常可以加到表里，相当于log日志
--id，code，errm，information，create date
--目前先这么写，学完存储过程后再改成一个过程，用的时候只需要调用即可
drop table error;

create table error(
       id number,
       code number,
       errm varchar2(4000),
       information varchar2(4000),
       create_date date
);

drop sequence my_key;
create sequence my_key;
insert into error values(my_key.nextVal,1,'xxx','xxxxx',sysdate);

--具体做法
declare
       e_myException exception;
begin
     dbms_output.put_line('hello');
     raise e_myException;
     dbms_output.put_line('world');
     dbms_output.put_line(1/0);
exception
         when e_myException then
              declare
                     v_code binary_integer;
                     v_errm varchar2(4000);
              begin
                   v_code:=sqlcode;
                   v_errm:=sqlerrm;
                   insert into error values(my_key.nextVal,v_code,v_errm,'e_myException',sysdate);
              end;
         when others then
              dbms_output.put_line('error');
end;
/   

--cursor 游标(结果集)用于提取多行数据
--定义后不会有数据，使用后才有
--一旦游标被打开，就无法再次打开(可以先关闭，再打开)
declare
       cursor c_student is
              select * from student33;
begin
     open c_student;
     close c_student;
end;

--第二种游标的定义方式，用变量控制结果集的数量
declare
       v_id binary_integer;
       cursor c_student is select * from student33 where id>v_id;
begin
     v_id:=10;
     open c_student;
     close c_student;
end;
/

--第三种游标的定义方式，带参数的游标，用的最多
declare
       cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
begin
     open c_student(10);
     close c_student;
end;
/

--游标的使用，一定别忘了关游标
declare
       v_student student33%rowtype;
       cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
begin
     open c_student(10);
     fetch c_student into v_student;
     close c_student;
     dbms_output.put_line(v_student.name);
end;

--如何遍历游标fetch
--游标的属性 %found，%notfound,%isopen,%rowcount
--%found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常
--%notfound，与%found行为相反
--%isopen，判断游标是否打开
--%rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001

--loop方式遍历游标
declare
       v_student student33%rowtype;
       cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
begin
     open c_student(10);
     loop
         fetch c_student into v_student;
         exit when c_student%notfound;
         dbms_output.put_line('name:'||v_student.name);
     end loop;
     close c_student;
end;

--while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环
--如何解决：while nvl(c_student%found,true) loop
declare
       v_student student33%rowtype;
       cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
begin
     open c_student(10);
     while c_student%found is null or c_student%found loop
           fetch c_student into v_student;
           dbms_output.put_line('name:'||v_student.name);
     end loop;
     close c_student;
end;

--for循环遍历，最简单，用的最多，不需要声明v_student,打开关闭游标，fetch
declare
       cursor c_student(v_id binary_integer) is select * from student33 where id>v_id;
begin
     for v_student in c_student(10) loop
         dbms_output.put_line('name:'||v_student.name);
     end loop;
end;

--goto例子，不推荐使用goto，会使程序结构变乱
declare
       i binary_integer:=0;
begin
     if i=0 then goto hello;end if;
     <<hello>>
     begin
          dbms_output.put_line('hello');
          goto over;
     end;
     <<world>>
     begin
          dbms_output.put_line('world');
          goto over;
     end;
     <<over>>
             dbms_output.put_line('over');
end;


--pl/sql day2
--存储过程：把匿名块存储下来
--匿名块运行后不会在数据库留下
declare
       v_content varchar2(4000):='hello';
begin
     dbms_output.put_line(v_content);
end;

--创建或修改一个过程，ws_println(形式参数，声明类型即可)，as可以替换成is
--变量可以声明在as和begin之间
create or replace procedure ws_println(v_content varchar2)
as 
begin
     dbms_output.put_line(v_content);
end;

--调用有参过程
execute ws_println('ws');
call ws_println('ws');
begin
     ws_println('ws');
end;

--删除一个过程
drop procedure ws_println;
--查看数据库里的过程
select * from user_procedures;
select object_name,procedure_name from user_procedures;
desc user_procedures;

--procedure里可以调用其它的procedure
create or replace procedure say_hello
is
begin
     ws_println('hello');
end;

--或者这样也可以
create or replace procedure say_hello
as
begin
     ws_println('hi susu');
end;

--调用无参过程的方式：
execute say_hello;
call say_hello();
begin
     say_hello;
end;

--一个参数的存储过程
--输入参数in，输入参数不能进行赋值，默认不写就是in
--存储过程没有重载，这个有参的say_hello会替代上面的无参say_hello
create or replace procedure say_hello(v_name in varchar2)
as
begin
     --v_name:='a';
     ws_println('hello  '||v_name);
end;

--调用有参的存储过程的两种方式
begin
     say_hello('susu');--方式1
     say_hello(v_name=>'xianbin');--方式2
end;

--多个参数的存储过程
create or replace procedure say_hello
       (v_first_name in varchar2,v_last_name in varchar2)
as
begin
     ws_println('hello '||v_first_name||'.'||v_last_name);
end;

--调用多个参数的两种方式
--方式一，这种方式需按顺序给出值，否则出现异常
begin
     say_hello('he','susu');
end;
--方式二，用指定型参名的方式调用可以不按顺序赋值
begin
     say_hello(v_last_name=>'he',v_first_name=>'susu');
end;

--out输出参数，用于利用存储过程给一个或多个变量赋值，类似于返回值
create or replace procedure 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);
     ws_println(v_con);
end;

--in out参数，既赋值，又取值
create or replace procedure say_hello(v_name in out varchar2)
as
begin
     v_name:='hi '||v_name;
end;

--调用
declare
       v_inout varchar2(20):='wangsu';
begin
     say_hello(v_inout);
     ws_println(v_inout);
end;

--缺省参数
create or replace procedure say_hello(v_name varchar2 default 'susu',v_content varchar2 default 'hello')
as
begin
     ws_println(v_name||' '||v_content);
end;

--调用，用指明形参名的方式调用更好
begin
     say_hello();
end;
begin
     say_hello(v_name=>'wangsu');
end;

begin
     say_hello(v_content=>'hi');
end;

--function函数
--过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程
--有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的
--主要区别不在于返回值，而在于他们的调用方式，过程是作为一个独立执行语句调用的，函数以合法的表达
--式的方式调用
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('susu');
     ws_println(v_name);
end;

--out 参数的函数
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);
       ws_println(v_name);
       ws_println(v_name1);
end;

--in out 参数
create or replace function func(v_name in out varchar2)
return varchar2
is
begin
     v_name:=v_name||'  hello';
     return 'ws';
end;
--调用
declare
       v_inout varchar2(20):='world';
       v_ret varchar2(20);
begin
     v_ret:=func(v_inout);
     ws_println(v_inout);
     ws_println(v_ret);
end;