Oracle游标及应用实例1、For循环游标：定义游标定义游标变量使用for循环来使用这个游标www.2cto.com2、游标应用例子：Sql代码declare--定义游标cursorc_jobisselectempno,ename,job,salfr...SyntaxHighl
1、For 循环游标：
定义游标
定义游标变量
使用for循环来使用这个游标
www.2cto.com
2、游标应用例子：
Sql代码
declare
--定义游标
cursor c_job is select empno,ename,job,sal from emp where job=&＃39;MANAGER&＃39;;
--定义游标变量，该类型为游标中的一行数据类型
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.empno||&＃39;-&＃39;||c_row.ename||&＃39;-&＃39;||c_row.job||&＃39;-&＃39;||c_row.sal);
end loop;
end;
www.2cto.com
3、Fetch游标
Sql代码
--使用的时候必须要明确的打开和关闭
declare
--定义游标
cursor c_job is select empno,ename,job,sal from emp where job=&＃39;MANAGER&＃39;;
--定义一个游标变量
c_row c_job%rowtype;
begin
open c_job;
loop
--提取一行数据到c_row
fetch c_job into c_row;
--判读是否提取到值，没取到值就退出，取到值c_job%notfound 是false，取不到值c_job%notfound 是true
exit when c_job%notfound;
dbms_output.put_line(c_row.empno||&＃39;-&＃39;||c_row.ename||&＃39;-&＃39;||c_row.job||&＃39;-&＃39;||c_row.sal);
end loop;
--关闭游标
close c_job;
end;
PS：
www.2cto.com
4、游标实例一
Sql代码
--创建表INERT语句
create or replace procedure SP_REPORT_CREATE_INSERT_SQL(tab_name varchar2)
as
cursor mycur is select table_name from user_tables where table_name like &＃39;ANALYSIS%&＃39; AND TABLE_NAME LIKE &＃39;%&＃39;||tab_name||&＃39;%&＃39; order by table_name;
SM mycur%rowtype;
SS varchar2(250);
cn NUMBER;
cursor mycur_columns is SELECT COLUMN_NAME from USER_TAB_COLS where TABLE_NAME = SS order by COLUMN_ID;
coulumn mycur_columns%rowtype;
cursor mycur_columns2 is SELECT COLUMN_NAME from USER_TAB_COLS where TABLE_NAME = SS order by COLUMN_ID;
coulumn2 mycur_columns2%rowtype;
begin
for SM in mycur loop
SS := SM.table_name;
dbms_output.put_line(&＃39;---------------&＃39;||SS||&＃39;---------------&＃39;);
dbms_output.put_line(&＃39;&＃39;);
dbms_output.put_line(&＃39; ![CDATA[&＃39;);
dbms_output.put(&＃39; insert into &＃39;||SS||&＃39;(&＃39;);
cn:=1;
for coulumn in mycur_columns loop
if(cn=1) then
dbms_output.put(coulumn.COLUMN_NAME);
else
dbms_output.put(&＃39;,&＃39;||coulumn.COLUMN_NAME);
end if;
cn:=cn+1;
end loop;
dbms_output.put_line(&＃39;)&＃39;);
dbms_output.put(&＃39; values(SEQ_&＃39;||SS||&＃39;.nextval&＃39;);
cn:=1;
for coulumn2 in mycur_columns2 loop
if(cn>1) then
dbms_output.put(&＃39;,:&＃39;||coulumn2.COLUMN_NAME);
end if;
cn:=cn+1;
end loop;
dbms_output.put_line(&＃39;)&＃39;);
dbms_output.put_line(&＃39; ]]&＃39;);
dbms_output.put_line(&＃39;&＃39;);
end loop;
end;
www.2cto.com
5、游标实例二
Sql代码
create or replace procedure SP_SPLIT_CREATE_TABLE(ID INT,TAB_NAME VARCHAR2)
as
cursor mycur is select STATEMENT_SQL from SPLIT_TABLE_STATEMENT WHERE P_ID = ID ORDER BY ORDERNUM;
SM mycur%rowtype;
SS varchar2(1000);
begin
for SM in mycur loop
SS := SM.STATEMENT_SQL;
Select REPLACE(SS,&＃39;@TAB_NAME&＃39;,TAB_NAME) Into SS From DUAL;
dbms_output.put_line(SS);
execute immediate SS;
insert into split_table_history(int_id,split_name,procedurename,create_time) values(
SEQ_SPLIT_TABLE_HISTORY.NEXTVAL,TAB_NAME,&＃39;CREATE TABLE&＃39;||TAB_NAME,sysdate());
end loop;
end;
www.2cto.com
6、游标实例三
Sql代码
create or replace procedure SP_PKEY_AUTO_INCREMENT
as
--游标：获取所有Sequence的名称
cursor mycur is select SEQUENCE_NAME from user_sequences;
SM mycur%rowtype;
--表名
TABNAME varchar2(32);
--主键字段名
PKEY varchar2(20);
--游标：获取主键名称
cursor mycur_columns is select COLUMN_NAME from user_cons_columns cu,user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = &＃39;P&＃39; and au.table_name = TABNAME;
coulumn mycur_columns%rowtype;
begin
for SM in mycur loop
--表名
TABNAME:=REPLACE(SM.SEQUENCE_NAME,&＃39;SEQ_&＃39;,&＃39;&＃39;);
--主键字段名
for coulumn in mycur_columns loop
PKEY := coulumn.COLUMN_NAME;
end loop;
--组合
dbms_output.put_line(&＃39;ALTER TABLE &＃39;||TABNAME||&＃39; MODIFY COLUMN &＃39;||PKEY||&＃39; INT NOT NULL AUTO_INCREMENT;&＃39;);
end loop;
end;
PS：
www.2cto.com
7、存储过程执行动态脚本：
Sql代码
CREATE OR REPLACE PROCEDURE SP_TRUN_TABLE_DATA_BY_NAME
(TAB_NAME in varchar2)
AS
SQLSTR varchar2(255);
BEGIN
SQLSTR := &＃39;TRUNCATE TABLE &＃39;|| TAB_NAME;
execute immediate SQLSTR;
insert into split_table_history(int_id,split_name,procedurename,create_time) values(
SEQ_SPLIT_TABLE_HISTORY.NEXTVAL,TAB_NAME,&＃39;TRUNCATE TABLE&＃39;||TAB_NAME,sysdate());
END;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有