PLSQL基本知识

create table student (
   xh number(4), --学号
   xm varchar2(20), --姓名
   sex char(2), --性别
   birthday date, --出生日期
   sal number(7,2) --奖学金
);
/*
*第一种写法
*/
declare 
  v_name varchar(100);
  v_xh number :=0;
begin 
  select xm,xh into v_name,v_xh  from student where xh=3;
  dbms_output.put_line(v_name||'------------>'||v_xh);
end;
/*
*第二种写法
*/
declare 
  type stu_record is record(
    v_name varchar(100),
    v_xh number :=0
  );
  v_interface stu_record;
begin 
  select xm,xh into v_interface  from student where xh=6;
  dbms_output.put_line(v_interface.v_name||'------------>'||v_interface.v_xh);
end;



/*
*if else
*/

declare 
    v_xh student.xh%TYPE;
    v_temp varchar(30);
begin
  select xh into v_xh from student where xh=23;
  if v_xh<6 then 
    v_temp:='xh小于6';
  elsif v_xh=6 then 
    v_temp:='xh等于6';
  else 
    v_temp:='xh大于6';
  end if;
  dbms_output.put_line(v_temp);
end;

/*
*case when then end
*/

declare 
    v_xm varchar(30);
    v_temp varchar(30);
begin
  select xm into v_xm from student where xh=2;
  v_temp:=''
  case v_xm when 'john' then 'xh是偶数'      
            when 'martin' then 'xh是奇数'      
            else 'null'   
  end;
  dbms_output.put_line(v_temp);
end;

/**
*loop 循环
*/
declare 
  --①
  v_i number(5) :=1;
begin
 loop  
   dbms_output.put_line(v_i);
 --③
 exit when v_i>=100;
   --②
   v_i:=v_i+1;
 end loop;
end;

/*
*while
*/
declare 
  v_i number(5) :=1;
begin
  while v_i <=100 loop 
    dbms_output.put_line('--------------->'||v_i);
    v_i:=v_i+1;
  end loop;
end;

/*
*for
*/
begin
  for c in 1..100 loop 
    dbms_output.put_line('c'||c);
  end loop;
end;



/*
*游标
*/
declare 
  v_name student.xm%Type;
  --定游标
  cursor stu is select xm from student;
begin
  --打开游标
  open stu;
  --提取游标
  fetch stu into v_name;
  while stu%found loop
    dbms_output.put_line(v_name);
    fetch stu into v_name;
  end loop;
end;
/*
*练习
*/
--游标练习
declare 
  type stu_record is record(
    v_xm varchar(100),
    v_xh number :=0
  );
  v_student stu_record;
  --定游标
  cursor stu is select xm,xh from student;
begin
  --打开游标
  open stu;
  --提取游标
  fetch stu into v_student;
  while stu%found loop
    dbms_output.put_line(v_student.v_xh||'---------------->'||v_student.v_xm);
    fetch stu into v_student;
  end loop;
  --关闭游标
  close stu;
end;
/**
*for代替游标
*/
declare 
  --定游标
  cursor stu is select xm,xh from student;
begin
  for c in stu loop 
    dbms_output.put_line(c.xh||'---------------->'||c.xm);
  end loop;
end;


/*
*函数
*/
create or replace function hello(v_xm varchar2)
return varchar2 
is
begin 
  return '===========hello=============='||v_xm;
end;
select hello('王正和') from dual;
--练习
--1
create or replace function get_sysadte
return date 
is
begin 
  return sysdate;
end;
select get_sysadte from dual;
--2
create or replace function add_parm(v_num1 number,v_num2 number)
return number 
is
 v_sum number(10);
begin 
  v_sum:=v_num1+v_num2;
  return v_sum;
end;
select add_parm(100,300) from dual;
/*
*存过
*/
create or replace procedure deal_hello
is 
begin 
  dbms_output.put_line('---------------->hello');
end;
--1
create or replace procedure deal_sum_xh
is 
v_sum number(20) :=0;
cursor stu is select xh,xm from student;
begin 
  for c in stu loop
     v_sum:=c.xh+v_sum;
     dbms_output.put_line('<-----数据----------->'||c.xh);
  end loop;
  dbms_output.put_line('-----最终数据----------->'||v_sum);
end;