oracle基础语法汇总

[declaredeclarations]beginexecutable statements[exceptionhandlers]end;打开输出set serverout on;

declarescore user_tbl.score%type;beginselect score into score from user_tbl where id='&id';if score>90 thendbms_output.put_line('优秀');elsif score>80 thendbms_output.put_line('良好');elsif score>60 thendbms_output.put_line('及格');elsedbms_output.put_line('差');end if;end;

declarescore user_tbl.score%type;beginselect score into score from user_tbl where user_name='&name';if score>90 thendbms_output.put_line('优秀');elsif score>80 thendbms_output.put_line('良好');elsif score>60 thendbms_output.put_line('及格');elsedbms_output.put_line('差');end if;end;

declaregrade user_tbl.grade%type;beginselect grade into grade from user_tbl where id='&id';case gradewhen 'A' then dbms_output.put_line('优异');when 'B' then dbms_output.put_line('优秀');when 'C' then dbms_output.put_line('良好');else dbms_output.put_line('一般');end case;end;

declarei number(4):=1;beginloopdbms_output.put_line('loop size:'||i);i:=i+1;exit when i>10;end loop;end;

declarei number(4):=1;beginwhile i<=10 loopdbms_output.put_line('while loop size='||i);i:=i+1;end loop;end;

declarei number(4):=1;beginfor i in 1..10 loopdbms_output.put_line('for loop Size:'||i);end loop;end;
declarei number(2):=1;j number(2):=1;beginfor i in reverse 1..9 loopfor j in 1..i loopdbms_output.put(j||'x'||i||'='||j*i||' ');end loop;dbms_output.put_line('');end loop;end;

declareuserId number(2);sql_str varchar2(100);userName user_tbl.user_name%type;beginexecute immediate 'create table testExe(id number,test_name varchar2(20))';userId:='&userId';sql_str:='select user_name from user_tbl where id=:id';execute immediate sql_str into userName using userId;dbms_output.put_line(userName);end;(ordeclareid_param number:='&id_param';sql_str varchar2(100);name_param stu_tbl.stu_name%type;beginsql_str:='select stu_name from stu_tbl where id=:p';execute immediate sql_str into name_param using id_param;dbms_output.put_line(name_param);end;/)

declaregrade number(4);begingrade:='&grade';case gradewhen 1 then dbms_output.put_line('好的');--else dbms_output.put_line('不好');end case;exceptionwhen case_not_found thendbms_output.put_line('输入类型不匹配！');end;

declarerowD user_tbl%rowtype;beginselect * into rowD from user_tbl;dbms_output.put_line(rowD.id||''||rowD.user_name||' '||rowD.password);exceptionwhen too_many_rows thendbms_output.put_line('不能将多行赋予一个属性！');end;ordeclarerowD user_tbl%rowtype;beginselect * into rowD from user_tbl where id=5;dbms_output.put_line(rowD.id||' '||rowD.user_name||' '||rowD.password);exceptionwhen too_many_rows thendbms_output.put_line('不能将多行赋予一个属性！');when no_data_found thendbms_output.put_line('没有您要查找的数据！');end;

declareinvalidError exception;category varchar2(20);begincategory:='&category';if category not in('附件','顶盘','备件') thenraise invalidError;elsedbms_output.put_line('您输入的类别是：'||category);end if;exceptionwhen invalidError thendbms_output.put_line('无法识别的类别！');end;

declareapp_exception exception;grade user_tbl.grade%type;beginselect grade into grade from user_tbl where id=&id;if grade='A' thenraise app_exception;elsedbms_output.put_line('查询的等级为：'||grade);end if;exceptionwhen app_exception thenraise_application_error(-20001,'未知的等级！');end;