Oracle sql全
|
表，序列，视图，索引 更改删除序列： alter sequence user_id_seq maxvalue 10000;--不能修改其start with 值 drop sequence user_id_seq; 在Hibernate中访问序列： ????? user_id_seq ? 视图以经过定制的方式显示来自一个或多个表的数据 创建视图： create or replace view user_tbl_view (vid,vname,vage) as select id,user_name,age from user_tbl [with check option]|[with read only]; 创建带有错误的视图： create force view user_tbl_force_view as select * from user_table;--此时user_table可以不存在 创建外联接视图： create view user_stu_view as select u.id,u.user_name,u.password,s.ddress from user_tbl u,stu_tbl s where u.s_id(+)=s.id;--哪一方带有(+)，哪一方就是次要的 删除视图： drop user_stu_view; ? 索引 用于提高SQL语句执行的性能 索引类型： 唯一索引，位图索引，组合索引，基于函数的索引，反向键索引 创建标准索引： create index user_id_index on user_tbl(id) tablespace schooltbs; 重建索引： alter index user_id_index rebuild; 删除索引： drop index user_id_index; 创建唯一索引： create unique index user_id_index on user_tbl(id); 创建组合索引： create index name_pass_index on user_tbl(user_name,password); 创建反向键索引： create index user_id_index on user_tbl(id) reverse; ? 四．使用PL/SQL 可用于创建存储过程，触发器，程序包，给SQL语句的执行添加程序逻辑。 支持SQL，在PL/SQL中可以使用： 数据操纵命令 事务控制命令 游标控制 SQL函数和SQL运算符 支持面向对象编程(OOP) 可移植性 更佳的性能，PL/SQL经过编译执行 ? 分为三个部分：声明部分，可执行部分和异常处理部分 [declare declarations] begin executable statements [exception handlers] end; 打开输出 set serverout on; ? --根据输入编号获取某学员的成绩--if declare score user_tbl.score%type; begin select score into score from user_tbl where id=‘&id‘; if score>90 then dbms_output.put_line(‘优秀‘); elsif score>80 then dbms_output.put_line(‘良好‘); elsif score>60 then dbms_output.put_line(‘及格‘); else dbms_output.put_line(‘差‘); end if; end; ? --根据学员姓名获取某学员的成绩--if declare score user_tbl.score%type; begin select score into score from user_tbl where user_name=‘&name‘; if score>90 then dbms_output.put_line(‘优秀‘); elsif score>80 then dbms_output.put_line(‘良好‘); elsif score>60 then dbms_output.put_line(‘及格‘); else dbms_output.put_line(‘差‘); end if; end; ? --case的使用 declare grade user_tbl.grade%type; begin select grade into grade from user_tbl where id=‘&id‘; case grade when ‘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; ? --基本循环 declare i number(4):=1; begin loop dbms_output.put_line(‘loop size:‘||i); i:=i+1; exit when i>10; end loop; end; ? ? --while循环 declare i number(4):=1; begin while i<=10 loop dbms_output.put_line(‘while loop size=‘||i); i:=i+1; end loop; end; ? --for循环 declare i number(4):=1; begin for i in 1..10 loop dbms_output.put_line(‘for loop Size:‘||i); end loop; end; ? declare i number(2):=1; j number(2):=1; begin for i in reverse 1..9 loop for j in 1..i loop dbms_output.put(j||‘x‘||i||‘=‘||j*i||‘ ‘); end loop; dbms_output.put_line(‘‘); end loop; end; ? --动态SQL declare userId number(2); sql_str varchar2(100); userName user_tbl.user_name%type; begin execute 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; (or declare ?id_param number:=‘&id_param‘; ?sql_str varchar2(100); ?name_param stu_tbl.stu_name%type; ?begin ?sql_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; ?/ ) ? --异常处理 declare grade number(4); begin grade:=‘&grade‘; case grade when 1 then dbms_output.put_line(‘好的‘); --else dbms_output.put_line(‘不好‘); end case; exception when case_not_found then dbms_output.put_line(‘输入类型不匹配！‘); end; --系统异常 declare rowD user_tbl%rowtype; begin select * into rowD from user_tbl; dbms_output.put_line(rowD.id||‘‘||rowD.user_name||‘ ‘||rowD.password); exception when too_many_rows then dbms_output.put_line(‘不能将多行赋予一个属性！‘); end; or declare rowD user_tbl%rowtype; begin select * into rowD from user_tbl where id=5; dbms_output.put_line(rowD.id||‘ ‘||rowD.user_name||‘ ‘||rowD.password); exception when too_many_rows then dbms_output.put_line(‘不能将多行赋予一个属性！‘); when no_data_found then dbms_output.put_line(‘没有您要查找的数据！‘); end; ? --自定义错误 declare invalidError exception; category varchar2(20); begin category:=‘&category‘; if category not in(‘附件‘,‘顶盘‘,‘备件‘) then raise invalidError; else dbms_output.put_line(‘您输入的类别是：‘||category); end if; exception when invalidError then dbms_output.put_line(‘无法识别的类别！‘); end; ? --引发应用程序异常 declare app_exception exception; grade user_tbl.grade%type; begin select grade into grade from user_tbl where id=&id; if grade=‘A‘ then raise app_exception; else dbms_output.put_line(‘查询的等级为：‘||grade); end if; exception when app_exception then raise_application_error(-20001,‘未知的等级！‘); end; 五、游标管理 游标类型：隐式游标，显式游标，REF游标 REF游标用于处理运行时才能确定的动态SQL查询的结果 ? ==========隐式游标========== 在PL/SQL中使用DML语句时自动创建隐式游标 隐式游标自动声明、打开和关闭，其名为SQL 隐式游标的属性： %found ????????SQL语句影响实质后返回true %notfound??????SQL语句没有影响实质后返回true %rowcount??????SQL语句影响的行数 %isopen????????游标是否打开，始终为false 示例： begin update user_tbl set score=score+5; if SQL%found then ????dbms_output.put_line(‘数据被更改： ‘||SQL%rowcount); elsif sql%notfound then ????dbms_output.put_line(‘没有找到数据！‘); end if; if SQL%isopen then ????dbms_output.put_line(‘Open‘); else ????dbms_output.put_line(‘Close‘); end if; end; ? ==========显式游标========== 在PL/SQL的声明部分定义查询，该查询可以返回多行 J?????????声明游标 J?????????打开游标 J?????????从游标中取回数据 J?????????关闭游标 声明游标完成两个任务： 给游标命名 将一个查询与游标关联 cursor cursor_name is select statement; 打开游标： ????open cursor_name; 取数据： ????fetch cursor_name into record_list; 关闭游标： ????close cursor_name; 显式游标的属性： %found ????????执行最后一条fetch语句成功返回行时为true %notfound??????执行最后一条fetch语句未能返回行时为true %rowcount??????返回到目前为止游标提取的行数 %isopen????????游标是否打开 ? 示例： declare users user_tbl%rowtype; cursor boys_cur is select * from user_tbl where sex=‘h‘; begin open boys_cur; loop fetch boys_cur into users; exit when boys_cur%notfound; dbms_output.put_line(users.user_name||‘??‘||users.password); dbms_output.put_line(boys_cur%rowcount); end loop; close boys_cur; end; ? 带参的显式游标 declare users user_tbl%rowtype; cursor boys_cur(sexParam varchar2) is select * from user_tbl where sex=sexParam; begin open boys_cur(‘&sex‘); loop fetch boys_cur into users; exit when boys_cur%notfound; dbms_output.put_line(users.user_name||‘??‘||users.password); dbms_output.put_line(boys_cur%rowcount); end loop; close boys_cur; end; ? 使用显式游标更新行 declare cursor user_update_cur is select sex from user_tbl for update; usersex user_tbl.sex%type; begin open user_update_cur; loop fetch user_update_cur into usersex; exit when user_update_cur%notfound; dbms_output.put_line(usersex); if usersex = ‘M‘ then ????update user_tbl set score=score-5 where current of user_update_cur; else ????update user_tbl set score=score+5 where current of user_update_cur; end if; end loop; close user_update_cur; commit; end; ? 循环游标 declare cursor user_cur is select * from user_tbl; begin for username in user_cur loop ????dbms_output.put_line(username.user_name||‘??‘||username.sex); end loop; end; ? ==========REF游标========== REF游标和游标变量用于处理运行时动态执行的SQL查询 创建游标变量的步骤： J?????????声明REF游标类型 J?????????声明REF游标类型的变量 声明类型的语法 Type ref_cursor_name is ref cursor [return return_type]; 打开游标变量的语法 Open cursor_name for select_statement; ----声明强类型的游标 declare type ref_cur is ref cursor return user_tbl%rowtype; users_cur ref_cur; ----声明弱类型的游标 declare type ref_cur is ref cursor; users_cur ref_cur; 示例 ----强类型 declare type ref_cur is ref cursor return user_tbl%rowtype; users_cur ref_cur; users user_tbl%rowtype; begin open users_cur for select * from user_tbl where user_name=‘ny2t92‘; loop ????fetch users_cur into users; ????exit when users_cur%notfound; ????dbms_output.put_line(users.user_Name); end loop; close users_cur; end; ----弱类型 declare type ref_cur is ref cursor; my_cur ref_cur; users user_tbl%rowtype; stus stu_tbl%rowtype; begin open my_cur for select * from user_tbl; loop ????fetch my_cur into users; ????exit when my_cur%notfound; ????dbms_output.put_line(users.user_Name); end loop; close my_cur; open my_cur for select * from user_tbl where user_name=‘ny2t92‘; loop ????fetch my_cur into users; ????exit when my_cur%notfound; ????dbms_output.put_line(users.user_Name); end loop; close my_cur; open my_cur for select * from stu_tbl; loop fetch my_cur into stus; exit when my_cur%notfound; dbms_output.put_line(stus.stu_Name); end loop; close my_cur; end; ----动态SQL游标 declare type ref_cur is ref cursor; my_cur ref_cur; users user_tbl%rowtype; username varchar2(20); sqlstmt varchar2(200); begin username:=‘&username‘; sqlstmt := ‘select * from user_tbl where user_name= :name‘; open my_cur for sqlstmt using username; loop ????fetch my_cur into users; ????exit when my_cur%notfound; ????dbms_output.put_line(users.user_Name); end loop; close my_cur; end; ? ? 六．子程序 子程序分为：存储过程和函数，它是命名的PL/SQL块，编译并存储在数据库中。 子程序的各个部分：声明部分，可执行部分，异常处理部分。 过程----执行某些操作 函数----执行操作并返回值 ? ==========存储过程========== 创建过程的语法： create or replace procedure proce_name (parameter_list) is|as local variable declaration begin executable statements exception exception_handlers end proce_name; ? 过程参数的三种模式： In----用于接收调用的值，默认的参数模式 Out----用于向调用程序返回值 In out----用于接收调用程序的值，并向调用程序返回更新的值 执行过程的语法： Execute proce_name(parameter_list); 或 Declare Variable var_list; Begin Proce_name(var_list); End; 将过程执行的权限授予其他用户： Grant execute on proce_name to scott; Grant execute on proce_name to public; 删除存储过程： Drop procedure proce_name; ? ==========函数========== 创建函数的语法： Create or replace function Fun_name (parameter_list) Return datatype is|as Local declarations Begin Executable statements; Return result; Exception Exce_handlers; End; 函数只能接收in参数，不能接受out或in out参数，形参不能是PL/SQL类型 函数的返回类型也必须是数据库类型 访问函数的方式： J?????????使用PL/SQL块 J?????????使用SQL语句 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
