oracle基础知识语法大全

五、游标打点游标范例：隐式游标，显式游标，REF游标REF游标用于处理赏罚运行时才气确定的动态SQL查询的功效 ==========隐式游标==========在PL/SQL中行使DML语句时自动建设隐式游标隐式游标自动声明、打开和封锁，其名为SQL隐式游标的属性：%found SQL语句影响实质后返回true%notfound SQL语句没有影响实质后返回true%rowcount SQL语句影响的行数%isopen 游标是否打开，始终为false示例：beginupdate 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 游标是否打开 示例：declareusers user_tbl%rowtype;cursor boys_cur is select * from user_tbl where sex=‘h‘;beginopen boys_cur;loopfetch 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; 带参的显式游标declareusers user_tbl%rowtype;cursor boys_cur(sexParam varchar2)is select * from user_tbl where sex=sexParam;beginopen boys_cur(‘&sex‘);loopfetch 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; 行使显式游标更新行declarecursor user_update_cur is select sex from user_tbl for update;usersex user_tbl.sex%type;beginopen user_update_cur;loopfetch 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; 循漫游标declarecursor user_cur is select * from user_tbl;beginfor 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;----声明强范例的游标declaretype ref_cur is ref cursor return user_tbl%rowtype;users_cur ref_cur;----声明弱范例的游标declaretype ref_cur is ref cursor;users_cur ref_cur;示例----强范例declaretype ref_cur is ref cursor return user_tbl%rowtype;users_cur ref_cur;users user_tbl%rowtype;beginopen 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;----弱范例declaretype ref_cur is ref cursor;my_cur ref_cur;users user_tbl%rowtype;stus stu_tbl%rowtype;beginopen 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;loopfetch my_cur into stus;exit when my_cur%notfound;dbms_output.put_line(stus.stu_Name);end loop;close my_cur;end;----动态SQL游标declaretype ref_cur is ref cursor;my_cur ref_cur;users user_tbl%rowtype;username varchar2(20);sqlstmt varchar2(200);beginusername:=‘&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 procedureproce_name (parameter_list)is|aslocal variable declarationbeginexecutable statementsexceptionexception_handlersend proce_name; 进程参数的三种模式：In----用于吸取挪用的值，默认的参数模式Out----用于向挪用措施返回值In out----用于吸取挪用措施的值，并向挪用措施返回更新的值执行进程的语法：Execute proce_name(parameter_list);或DeclareVariable var_list;BeginProce_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 functionFun_name (parameter_list)Return datatype is|asLocal declarationsBeginExecutable statements;Return result;ExceptionExce_handlers;End;函数只能吸取in参数，不能接管out或in out参数，形参不能是PL/SQL范例函数的返回范例也必需是数据库范例会见函数的方法：J 行使PL/SQL块J 行使SQL语句Select fun_name(parameter_list) from dual;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!