Oracle_PL/SQL的根本语法操作

Oracle_PL/SQL的基本语法操作

declare
   temp_name varchar2(20):='hello world';
   temp_num int :=1;
begin
--set serveroutput on
   dbms_output.put_line(temp_num||'==='||temp_name);
end;

---if
declare
  temp_num int :=&请输入num的值;
begin
  if temp_num>5 then
     dbms_output.put_line('temp_num>5');
  elsif temp_num=5 then
     dbms_output.put_line('temp_num==5');
  else
    dbms_output.put_line('temp_num<5');
  end if;
end;
---case
declare
  score char:='&num';
begin
  case score
    when 'A' then 
    dbms_output.put_line('very good');
    when 'B' then 
    dbms_output.put_line('is good');
    when 'C' then 
    dbms_output.put_line('is normal');
    when 'D' then 
    dbms_output.put_line('is bad');
    else
    dbms_output.put_line('input a value');
  end case; 
end;

--loop
declare
 temp_num int :=1;
begin
  loop
     if(temp_num>=10) then--出循环条件
        exit;
     end if;
     dbms_output.put_line(temp_num);
     temp_num:=temp_num+1;
  end loop;
end;
--while
declare
 temp_num int :=1;
begin
  while temp_num<10 loop--进入循环条件
     dbms_output.put_line(temp_num);
     temp_num:=temp_num+1;
  end loop;
end; 
--for
declare
 temp_num int :=1;
begin
  for temp_num in  reverse 1..10 loop
     dbms_output.put_line(temp_num);
  end loop;
end; 
---动态sql--ddl
declare 
  tbName varchar2(10):='my_tab';
begin
  execute immediate '
   create table '||tbName||' (
     id int,
     name varchar2(10),
     age int
  )';
end;



---绑定参数
declare 
  id int:=1;
  tbName varchar2(10):='my_tab';
  age int:=11;
  uname varchar2(10):='my_tab';
begin
  execute immediate 
  'insert into my_tab values(:1,:abc,:2) returning name into :uname'
  using id,tbName,age returning into uname
  ; 
end;

--execute immediate 字符串 using 输入参数 returning into 返回值参数 
/*

Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

DML = Data Manipulation Language 数据操作语句[m'nipjulbl] 
DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言 
DCL（Data Control Language）：.?是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL 
TCL（Transaction Control Language）事务控制语言 .SAVEPOINT 设置保存点 .ROLLBACK? 回滚 .SET TRANSACTION .

使用动态SQL原因
1.业务：只能在运行时才能确定sql。
2.在开发动态拼装SQL，更灵活，更强大。
3.更容易维护。

*/

----系统exception
declare
  uname varchar2(10);
begin
  select name into uname from users where id=2;
  case '22'
    when '1' then uname:='1';
     when '2' then uname:='2';
  end case;
  dbms_output.put_line(uname);
  exception
   when too_many_rows then
     dbms_output.put_line('查询到多行数据');
   when others then 
     dbms_output.put_line('异常');
end;

---自定义异常
declare
   n int:=&5;
   e exception;--定义
begin
  if(n=5) then
    raise e;--手动抛出
  end if;
  exception--处理
   when e then 
     dbms_output.put_line('自定义异常');
end;
---隐式游标
declare 
  uname varchar2(10);
begin
  select name into uname from users where id=2;
  dbms_output.put_line('查询到'||sql%rowcount);
  if(sql%found)then
   dbms_output.put_line('found is true');
  else
   dbms_output.put_line('found is false');
  end if;
   if(sql%notfound)then
   dbms_output.put_line('notfound is true');
  else
   dbms_output.put_line('notfound is false');
  end if;
   if(sql%isopen)then
   dbms_output.put_line('isopen is true');
  else
   dbms_output.put_line('isopen is false');
  end if;
end;

---%type(字段类型)
---%rowtype
declare 
  uname users.name%type;
  myrow users%rowtype;--行类型
begin
  select name into uname from users where id=2;
  select * into myrow from users where id=2;
  dbms_output.put_line(uname);
  dbms_output.put_line(myrow.name||'==='||myrow.password);
end;

---显示游标
declare
  cursor my_cursor  is
   select * from users;--声明
 myrow users%rowtype;
begin
  open my_cursor;--打开
  if(my_cursor%notfound) then
    dbms_output.put_line('无数据');
  else
    fetch my_cursor into myrow;--取数据
     dbms_output.put_line(myrow.id||'==='||myrow.name||'==='||myrow.password);
  end if; 
  close my_cursor;--关闭
end;
--while循环
declare
  cursor my_cursor  is
   select * from users;--声明
 myrow users%rowtype;
begin
  open my_cursor;--打开
  fetch my_cursor into myrow;--取数据
  while (my_cursor%found) loop
     dbms_output.put_line(myrow.id||'==='||myrow.name||'==='||myrow.password);
     fetch my_cursor into myrow;
  end loop;
  close my_cursor;--关闭
end;
--for循环
declare
  cursor my_cursor  is
   select * from users;--声明
 myrow users%rowtype;
begin
  for myrow in my_cursor loop
     dbms_output.put_line(myrow.id||'==='||myrow.name||'==='||myrow.password);
  end loop;
end;
--loop循环
declare
    cursor my_cursor is select * from info;
    myrow info%rowtype;
    begin
         open my_cursor;
         fetch my_cursor into myrow;
         loop
              if my_cursor%found then
                 dbms_output.put_line(myrow.id);
                 fetch my_cursor into myrow;
              else
                 exit;
              end if;
         end loop;
         close my_cursor;
    end;