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;---ifdeclare  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;---casedeclare  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;--loopdeclare 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;--whiledeclare temp_num int :=1;begin  while temp_num<10 loop--进入循环条件     dbms_output.put_line(temp_num);     temp_num:=temp_num+1;  end loop;end; --fordeclare temp_num int :=1;begin  for temp_num in  reverse 1..10 loop     dbms_output.put_line(temp_num);  end loop;end; ---动态sql--ddldeclare   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.更容易维护。*/----系统exceptiondeclare  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(字段类型)---%rowtypedeclare   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;

?