Oracle数据库游标使用

Oracle数据库游标使用
较好的PL/SQL程序设计是在PL/SQL块中使用象DBMS_SQL这样的内建 包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令， PL/SQL编译器保证对象引用以及用户的权限。 SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ 该sql是隐性游标, SELECT语句只返回一行数据。如果超过一行数据， 那么就要使用显式游标.
举例
• DELETE FROM emp • WHERE empno=p_empno; • UPDATE former_emp • SET date_deleted=SYSDATE • WHERE empno=p_empno; • EXCEPTION • • • WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(Employee Number Not Found!); END
隐性游标的处理
• 当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护 查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关 闭。隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT, SQL%ISOPEN四个属性.SQL%FOUND,SQL%NOTFOUND是布尔值， SQL%ROWCOUNT是整数值, SQL%ISOPEN是布尔值。
%ROWTYPE
• 使用%ROWTYPE属性可以声明一个基于表或游标的行对象，也称为记录 对象，例如，可以基于ARTIST声明一个行对象： • declare • newXmUnitBg kwmis_xm_unit_bg%rowtype; • 一旦生成这条记录，那么就可以通过游标使用句号来提取newXmUnitBg 的字段值， • cursor frdm_cursor is SELECT FRDM_TZH FROM kwmis_xm_unit_bg WHERE bgoid=V_BGOID and bg_flag='1' • AND FRDM_TZH in(select frdm from kwmis_unit_baseinfo); • begin • OPEN frdm_cursor; • LOOP • FETCH frdm_cursor INTO v_frdm; • if frdm_cursor%notfound then frdm_cursor%found • exit when frdm_cursor%notfound frdm_cursor%found • %TYPE是跟表中的字段的类型一致,是变量类型.而%ROWTYPE是跟表中的 行类型一致,是record类型.
SQL%ROWCOUNT和SQL%ISOPEN
• 在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功， SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND. • SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭， 则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式 游标在DML语句执行时打开，结束时就立即关闭。
存储过程
• create [or replace] procedure procedure_name • [ (argment [ { in| in out }] TYPE, • argment [ { in | out | in out } ] type • { is | as } • <类型.变量的说明> • ( 注: 不用 declare 语句 ) • Begin • <执行部分> • exception • <可选的异常处理说明> • end;
显式游标
• • • • • • • • • • • • • • • • • • • • 当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into语句。 cursor cursor_name is 查询语句 查询语句; open cursor_name; Fetch cursor_name into 变量列表; Close cursor_name; declare cursor c1 is select ename, sal from emp where rownum<11; v_ename varchar2(10); v_sal number(7,2); begin open c1; fetch c1 into v_ename, v_sal; while c1%found loop dbms_output.put_line(v_ename||to_char(v_sal) ); fetch c1 into v_ename, v_sal; end loop; close c1; end;
显性游标的处理
• 在执行任何DML语句前cursor_name%FOUND和 cursor_name%NOTFOUND的值都是NULL,在执行DML语句后， cursor_name%FOUND的属性值将是： • . TRUE :fetch成功返回true. • 当cursor_name%FOUND为TRUE时, cursor_name%NOTFOUND 为FALSE。
数字式循环
• • • • • • • • • • • • • • • • • For 循环计数器 in 下限 .. 上限 loop 要执行的语句; end loop; 例子: DECLARE V_counter number := 7; Begin Inert into temp_table (num_col) Values ( v_counter ); For v_counter IN 20 .. 30 loop Insert into temp_table (num_col ) Values ( v_counter ); End loop; Inert into temp_table (num_col ) Values( v_counter ); End ;
举例
• CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number) • AS
• v_ename EMP.ENAME%TYPE; • BEGIN
• SELECT ename INTO v_ename • FROM emp • WHERE empno=p_empno; • INSERT INTO FORMER_EMP(EMPNO,ENAME) • VALUES (p_empno,v_ename);
WHILE 循环
• While <布尔表达式> loop • 要执行的语句; • end loop; • 例子. • declare • x number; • begin • x:= 1; • while x<10 loop • dbms_output.put_line(to_char(x)||’还小于10’); • x:= x+1; • end loop; • end;
%type
• %TYPE：属性的一种方式是在变量声明中使用。%TYPE是用于取的表或游 标中的字段类型， • 例如，可以使用它来生命和ARTIST表中字段类型相同的变量，注意可以 在表中名和字段之间使用点号（.）。 • DEALARE • V_UNIT_NAME kwmis_xm_unit_bg.UNIT_NAME%TYPE; • V_BGOID kwmis_xm_unit_bg.BGOID%TYPE; • 通过这种方式就不需要明确指定SQL数据类型，从而改善了代码的可维 护性，因为表中字段类型的改变会自动反映在PL/SQL中
SQL%FOUΒιβλιοθήκη D和SQL%NOTFOUND• 在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执 行DML语句后，SQL%FOUND的属性值将是： • . TRUE :INSERT,至少有一行被insert成功 • . TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE. • . TRUE :SELECT INTO至少返回一行 • 当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。
简单循环
• • • • Loop 要执行的语句; exit where 条件; end loop;
• 例子. • declare • x number; • begin • x:= 0; • loop • x:=x+1; • dbms_output.put_line(to_char(x)); • exit when x=10; • end loop; • end;
cursor_name%rowcount和cursor_name%isopen
• 在执行任何DML语句之前，cursor_name%ROWCOUNT的值都是NULL,对于 fetch语句，如果执行成功，cursor_name%ROWCOUNT的值为1,如果没 有成功，cursor_name%ROWCOUNT的值为0，同时产生一个异常 NO_DATA_FOUND. • cursor_name%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果 游标关闭，则为FALSE.对于隐式游标而言cursor_name%ISOPEN总是 FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。