plsql 游标动态sql语句_sqlserver游标使用和循环

PLSQL游标使用 　　  　　游标是一个指针，它指向一块SQL区域，该区域用于存储处理过来的SELECT或者其他的DML操作返回的数据。由PLSQL创建并管理的游标成为隐式游标，用户创建并管理的成为显示游标。游标可以看做是指向记录集合的指针，他可以在集合记录中移动以访问每条记录的功能。 　　  　　显示游标的使用： 　　  　　创建游标 　　CURSOR cursor_name IS sql_statement； 　　游标一旦创建成功，Oracle会为其分配内存，并与定义的SQL关联起来。 　　  　　打开游标 　　OPEN cursor_name [argument，。。。]; 　　  　　数据 　　FETCH cusrsor_name INTO variblep[，varible]; 　　①执行上述指令时，只能记录集合中的一行记录，将这行记录放入随后的变量中，这些变量的数据类型必须与记录中每列的数据类型相同。 　　  　　关闭游标 　　CLOSE cursor_name 　　  　　游标的属性 　　%ISOPEN：判断游标是否打开 　　%FOUND：游标是否发现数据 　　%NOTFOUND：游标没有发现数据 　　%ROWCOUNT：游标可以遍历的记录数量 　　访问游标属性的方法: cursor_name.attribute_name 　　  　　游标使用实例： 　　DECLARE 　　 var1 VARCHAR2(20);–变量的声明 　　 var2 VARCHAR2(20); 　　 CURSOR cursor_name IS SELECT * FROM table_name;–创建游标 　　BEGIN 　　 OPEN cursor_name; –打开游标 　　 LOOP  –循环 　　   FETCH cursor_name INTO var1,var2; –遍历游标 * 　　   dbms_output.put_line(‘bianlaing’||var1||’==’||var2); 　　   EXIT WHEN cursor_name%NOTFOUND; –判断是否有数据 　　 END LOOP; –循环结束 　　 CLOSE cursor_name; –关闭游标 　　END; 　　①的解释var1 和 var2 必须和table_name表遍历结果集的数据类型分别对应 　　  　　隐式游标的使用： 　　隐式游标是没有声明的游标，没有显示的创建游标只是在PLSQL代码块中执行SQL语句，这些语句就是隐式游标。 　　DECLARE 　　 names VARCHAR2(10); 　　 BEGIN 　　   SELECT count(first_name) INTO names FROM emp; 　　   dbms_output.put_line(‘count：=’||names); 　　  (这就是隐式游标) 　　 END; 　　 在用户每次运行select或者DML操作时，PLSQL会自动创建一个隐式游标，隐式游标无法控制，一旦涉及的SQL语句结束，隐式游标也会自动关闭。想要获得游标信息可以通过隐式游标的属性。 　　  　　隐式游标的属性 　　SQL%ISOPEN：判断游标是否打开 　　SQL%FOUND：游标是否发现数据 　　SQL%NOTFOUND：游标没有发现数据 　　(该属性对于PLSQL的select into 语句没有用处，PLSQL认为其是一个非法操作，因为如果发生select into没有数据返回的情况，将会触发一个预定义异常NO_DATA_FOUND) 　　SQL%ROWCOUNT：用于发现游标中涉及的返回结果行的数量。 　　  　　FOR游标 　　使用for游标不需要使用变量，也不需要显示的打开和关闭游标，这些是自动执行的，也不需要去fetch游标。 　　DECLARE 　　 CURSOR cursor_nameIS SELECT * FROM table_name;–声明游标 　　BEGIN 　　FOR cursor_record IN cursor_name -for游标的使用 　　 LOOP 　　   dbms_output.put_line(‘变量’||cursor_record .chars||’==’||cursor_record .num); 　　 END LOOP; 　　END; 　　For 游标语法中cursor_record 为一条记录的集合，他自动定义一个%ROWTYPE类型的变量，%ROWTYPE变量包含对应于记录中的多列变量，通过这个变量可以依次访问记录中的每个列值 　　  　　游标表达式 　　Select deptno,dname,cursor(select empno,ename,sal from emp where deptno=d.deptno)from dept d; 　　  　　游标变量/动态游标 　　像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。 　　其主要运用于PLSQL函数或存储过程以及其他编程语言Java等程序之间作为参数传递。 　　    不像游标的一点，游标变量没有参数。 　　    游标变量具有以下属性： 　　    (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) 　　  　　1、声明格式： 　　    DECLARE   　　           TYPE ref_cursor_name IS REF CURSOR RETURN tablename%ROWTYPE; 　　  　　游标变量又分为强类型strong(with a return type)和弱类型(with no return type)： 　　   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; — 强类型 　　   TYPE genericcurtyp IS REF CURSOR; — 弱类型 　　   my_cursor SYS_REFCURSOR; — 使用预定义游标变量sys_refcursor 　　例子： 　　 TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; 　　   dept_cv deptcurtyp; — 声明游标变量 　　   或是返回record类型： 　　   DECLARE 　　   TYPE EmpRecTyp IS RECORD ( 　　   employee_id NUMBER, 　　   last_name VARCHAR2(25), 　　   salary NUMBER(8,2)); 　　  –声明record 　　   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; 　　   emp_cv EmpCurTyp; 　　  　　使用游标变量最为参数传递 　　DECLARE   　　TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;   　　emp empcurtyp;   　　PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS  –参数类型为游标类型 　　person employees%ROWTYPE;   　　OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;   　　process_emp_cv(emp);   　　CLOSE emp;   　　  　　在包中声明游标变量 　　CREATE PACKAGE emp_data AS   　　TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;   　　PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);   　　END emp_data;  　　  　　提取游标记录到集合类型 　　DECLARE 　　TYPE empcurtyp IS REF CURSOR;–声明动态游标 　　TYPE namelist IS TABLE OF employees.last_name%TYPE; –namelist集合 　　TYPE sallist IS TABLE OF employees.salary%TYPE;–sallist集合 　　emp_cv empcurtyp; 　　names namelist;–lsit -name 　　sals sallist;–list -sal 　　BEGIN 　　OPEN emp_cv FOR SELECT last_name, salary FROM employees 　　WHERE job_id = ‘SA_REP’; 　　FETCH emp_cv BULK COLLECT INTO names, sals; –BULK COLLECT INTO 的使用 　　CLOSE emp_cv; 　　FOR i IN names.FIRST .. names.LAST   　　LOOP   　　DBMS_OUTPUT.PUT_LINE(‘Name = ‘ || names(i) || ‘, salary = ‘ || sals(i));   　　END LOOP; 　　END; 　　  　　通过bulk collect减少loop处理的开销 　　采用bulk collect可以将查询结果一次性地加载到collections中。 　　而不是通过cursor一条一条地处理。 　　可以在select into,fetch into,returning into语句使用bulk collect。 　　注意在使用bulk collect时，所有的into变量都必须是collections. 　　  　　游标变量的使用限制 　　 1、不能再包说明中声明游标变量； 　　 2、不能用“=”运算符比较游标变量相等性、不等性及是否为空； 　　 3、不能存储于表列中； 　　 4、不能将游标变量存在于关联数组、嵌套表或数组； 　　 5、游标和游标变量之前是不可互操作的！