ORACLE之PL/SQL简单基础语法学习（二）

以下是我的简单测试：还有更多哈...  不告诉你了

select * from scott.emp
--基本数据类型变量的定义方法
declare 
age number(3):=26;
begin
  dbms_output.put_line(age);
  end;
---使用%rowtype定义变量
declare
   mytable scott.emp%rowtype;
   begin
     select * into mytable from scott.emp where sal=800;
     dbms_output.put_line(mytable.job);
     end;
---表达式
declare 
   result integer;
   begin
     result:=10+3**2;
     dbms_output.put_line(to_char(result));
     end;
     
--------------------------游标
declare 
   myrecord scott.emp%rowtype;
   cursor my_cursor is select * from scott.emp;
begin
  open  my_cursor;
  if(my_cursor%isopen) then
     loop 
       fetch my_cursor into myrecord;
          if (my_cursor%notfound) then
              dbms_output.put_line('not found data'); 
              exit;
          else
              dbms_output.put_line(myrecord.empno||myrecord.ename||myrecord.job);
          end if;
     end loop;
   end if;
   close my_cursor;
   commit;
end;

declare
cursor mycursor is select scott.emp.ename,scott.emp.empno from scott.emp;
begin
  for m in mycursor loop
    dbms_output.put_line(m.empno||m.ename);
    end loop;
    end;


-----------定义带参数的游标
declare 
myrecord scott.emp%rowtype;
cursor mycursor(empno1 number)is select * from scott.emp where scott.emp.empno=empno1;
begin 
  open mycursor(7369);
  loop
    fetch mycursor into myrecord;
    exit when mycursor%notfound;
    begin 
      dbms_output.put_line(myrecord.ename||myrecord.empno);
      end;
      end loop;
      close mycursor;
      commit;
      end;
      
-----------------------
declare 
   na scott.emp.ename%type;
   id scott.emp.empno%type;
   cursor cursor1 is select scott.emp.ename,scott.emp.empno from scott.emp where scott.emp.ename='smith';
   begin
     open cursor1;
     if cursor1%found then
       loop
         fetch cursor1 into na,id;      
         if cursor1%found then
           dbms_output.put_line('found data');
           else
             dbms_output.put_line('not found data');
             end if;
             exit when cursor1%notfound;
             dbms_output.put_line('循环中的数据是：'||na||'=='||id);
        end loop;
           dbms_output.put_line('提取的行数：'||cursor1%rowcount);
           close cursor1;
      else
          dbms_output.put_line('游标没有打开');
   end if;
end;

函数应用

declare 
mydate scott.emp.hiredate%type;
begin 
  commit;
end;


declare
type myrecord is record(myrecordnumber int,mycurrentdate date,myname varchar2(10));
srecord myrecord;
begin
  select empno,hiredate, ename into srecord from scott.emp where sal=1300;
  dbms_output.put_line(srecord.myname);
end;


declare
mytable scott.emp%rowtype;
begin
  select * into mytable
  from scott.emp
  where sal=1300;
  dbms_output.put_line(mytable.ename);
end;




------------------------------条件控制
--------if...then...end if
declare
a integer:=1;
begin
  --第二种方法定义a：a:=1;  
  if(a=1)
  then dbms_output.put_line('a='||a);
  end if;
end; 
------------------------------循环控制
-------loop...exit...end loop
declare
 a integer:=10;
begin
 loop
     dbms_output.put_line('a='||a);--循环块1
   if a=1
   then
     dbms_output.put_line('结束循环');
     exit;    -----结束循环
   else
     a:=a-1;   --循环块2       先进行循环块1，再判断是否退出循环，否则执行循环块2，再回到1
     end if;
  end loop;
 end;  
-------loop...exit...when...end loop
declare 
a integer:=10;
begin
  loop
    dbms_output.put_line('a='||a);
    a:=a-1;
    exit when a=0;
    end loop;
end;
-------while...loop...end loop
declare
a integer:=10;
begin
  while a!=0
  loop
    dbms_output.put_line('a='||a);
    a:=a-1;
    end loop;
end;
-------for...in...loop...end 
declare
a integer;
begin
  for a in 2..10
  loop
    dbms_output.put_line('a='||a);
    end loop;
end;


-----------大小写转换函数
select lower('YYYYYY')from dual  --大写转小写   
select upper('YttttY') from dual    --小写转大写
select initcap('ttuU jfie') from dual    ---将每个单词的第一个字母转大写，其余转小写


-------------字符处理函数
---ltrim/rtrim
select ltrim('xyzadxams','xyz')from dual;
select rtrim('xyzamsdams','ams') from dual;
select ltrim(' ttt ') from dual;--去掉左右空格


-------------字符串填充函数
select lpad('test',5,0) from dual
select rpad('test',6,0) from dual  --用0填充test右边，返回长度为6


-------------字符串查找函数
select instr('worldwide','d','2') from dual;
select instr('rcchnctausvf00040.191336845.200','.',-1,1) from dual;   --负数代表从后开始计算
select instr('rcchnctausvf00040.191336845.200','.',-2,2) from dual;


-------------替换函数
--（expr，被替换串，替换串）
select replace('jack and jue','jack','b') from dual

-------------字符串获取子串、连接、获取长度
select substr('abcdefg',3,2) from dual;
select concat('hello','word')from dual;
select concat('010-','8888888')||'xa23' from dual;
select length('rrrrr') from dual

-------数值函数
select Ceil(66.9) from dual      --取大于等于数值n的最小整数
select floor(66.9) from dual     --取小于等于数值n的最大整数：66
select trunc(66.64,1) from dual  --将m所表示的数值截取到小数点后的第n位
select mod(66,7) from dual       --取m除n后的余数：3
select round(4.99,1)from dual    --四舍五入，保留1位：5
select chr('67') from dual       --C


-------------时间计算函数
select months_between('19-2月-1999','19-3月-1999')from dual  --的到date1-date2的值， 如果前面的比后面的小，则返回负数
--系统默认格式19-2月-1999
select months_between(to_date('1999-09-09','YYYY-MM-dd'),to_date('1999-03-23','YYYY-MM-dd')) from dual;
select add_months(sysdate,6) from dual  --当前系统时间加上n个月，n可以是负数
select to_char(add_months(sysdate,-1),'yyyymmdd') from dual; --上个月
select last_day(sysdate) from dual  --求出Date所在月的最后一天
select round(sysdate) from dual   --将四舍五入为最近的天，超出中午12点增加一天
select next_day('17-4月-2012','星期四') next_day from dual; --该日期之后的第一个星期四的日期
select next_day(to_date('2012-4-17','yyyy-mm-dd'),'星期五') from dual
select to_char(sysdate,'dd-MM-yyyy day')from dual
--指定格式的Round和Trunc函数示例
select sysdate,round(sysdate,'yyyy'),trunc(sysdate,'yyyy') from dual
--不指定格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now_date, 
 to_char(round(sysdate),'yyyy-mm-dd hh24:mi:ss') round_date,
 to_char(Trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') trunc_date
 from dual;
 
select to_char(sysdate,'DY') from dual
-------to_char用于数值型
select to_char(88.89,'$99,999') from dual
select to_char(888.8999,'990.9999') from dual
select to_char('12345','9009999') from dual

---------------------------通用函数
select * from student
select id,name,age,nvl(age,'为null') age1 from student
select User from dual
select Uid from dual

--------------------左右连接     我的最爱
select student1.id,student.name from student1 right join student on student.id=student1.id

-------------------------------------------
declare
begin
  for i in 1..20
  loop
  insert into student values(lpad(student_seq.nextval,4,0),'张三','','') ;
  end loop;
end;
select * from student

-------------------------------------------
select  translate('liyan4h123ui','#liyanhui','#') from dual --#用#替换
select translate('abcbbaadef','bad','#@') from dual 
select  Ceil(86.77)      from dual 
select  floor(66.87)      from dual 


select  trunc(66.64,1)   from dual
select  mod(66,7)      from dual
select add_months(sysdate ,6)     from dual 


-------------异常
declare
id1 number:=1;
id2 number:=2;
id3 number:=0;
id4 number:=0;
myexception Exception;
begin
  id1:=10;
  id2:=23;
  id4:=id1/id3;
  if(id1<id2)
  then raise myexception;-----抛出异常
  end if;
  Exception
    when myexception then
      dbms_output.put_line('11111111111111');
      when others then
        dbms_output.put_line('222222222222');
end;


----------------------游标
select * from scott.emp;
declare 
str varchar2(22);
begin
  select ename into str from scott.emp where empno=7369;---系统有个隐示的游标
  dbms_output.put_line(str);
  Exception
    when too_many_rows then
      dbms_output.put_line('数据太多了');
      when no_data_found then
        dbms_output.put_line('没有数据');
end;
---------显示游标属性的使用  用于循环表的数据
declare 
cursor mycursor is select * from scott.emp ;
myrecord scott.emp%rowtype;
begin
  open mycursor;--将数据从数据库中提取出来放到内存中
  if(mycursor%isopen) then --游标是否打开
      loop
          fetch mycursor into myrecord;   --打开后游标下移  滚动游标
          if(mycursor%found) then       ---如果有数据则循环   mycursor%notfound  没有数据
              dbms_output.put_line(myrecord.empno||myrecord.ename);
          else 
            dbms_output.put_line('meiyou');
            exit;
          end if;
       end loop;
   end if; 
end;




declare 
cursor mycursor is select * from scott.emp;
begin
  for m in mycursor loop  ---m 代表游标变量
      dbms_output.put_line(m.empno||m.ename);
    end loop;
end;
------------带参数的游标
declare 
cursor mycursor(id number) is select * from scott.emp where empno=id; 
begin
  for m in mycursor(7369) loop
    dbms_output.put_line(m.empno||m.ename);
    end loop;
end;