[ Pobierz całość w formacie PDF ]
.*/CLOSE emp_cv;When declaring a cursor variable as the formal parameter of a subprogram thatcloses the cursor variable, you must specify theIN orINOUT mode.If you try to close an already-closed or never-opened cursor variable, PL/SQL raisesthe predefined exceptionINVALID_CURSOR.5-24 PL/SQL User s Guide and Reference Using Cursor VariablesExample 1Consider the stored procedure below, which searches the database of a main libraryfor books, periodicals, and tapes.A master table stores the title and category code(where1 = book,2 = periodical,3 = tape) of each item.Three detail tables storecategory-specific information.When called, the procedure searches the master tableby title, uses the associated category code to pick anOPEN-FOR statement, thenopens a cursor variable for a query of the proper detail table.CREATE PACKAGE cv_types ASTYPE LibCurTyp IS REF CURSOR;.END cv_types;CREATE PROCEDURE find_item (title VARCHAR2(100),lib_cv IN OUTcv_types.LibCurTyp)AScode BINARY_INTEGER;BEGINSELECT item_code FROM titles INTO codeWHERE item_title = title;IF code = 1 THENOPEN lib_cv FOR SELECT * FROM booksWHERE book_title = title;ELSIF code = 2 THENOPEN lib_cv FOR SELECT * FROM periodicalsWHERE periodical_title = title;ELSIF code = 3 THENOPEN lib_cv FOR SELECT * FROM tapesWHERE tape_title = title;END IF;END find_item;Interaction with Oracle 5-25 Using Cursor VariablesExample 2A client-side application in a branch library might use the following PL/SQL blockto display the retrieved information:DECLARElib_cv cv_types.LibCurTyp;book_rec books%ROWTYPE;periodical_rec periodicals%ROWTYPE;tape_rec tapes%ROWTYPE;BEGINget_title(:title); -- title is a host variablefind_item(:title, lib_cv);FETCH lib_cv INTO book_rec;display_book(book_rec);EXCEPTIONWHEN ROWTYPE_MISMATCH THENBEGINFETCH lib_cv INTO periodical_rec;display_periodical(periodical_rec);EXCEPTIONWHEN ROWTYPE_MISMATCH THENFETCH lib_cv INTO tape_rec;display_tape(tape_rec);END;END;Example 3The following Pro*C program prompts the user to select a database table, opens acursor variable for a query of that table, then fetches rows returned by the query:#include#includevoid sql_error();main(){char temp[32];EXEC SQL BEGIN DECLARE SECTION;char * uid = "scott/tiger";SQL_CURSOR generic_cv; /* cursor variable */int table_num; /* selector */struct /* EMP record */5-26 PL/SQL User s Guide and Reference Using Cursor Variables{int emp_num;char emp_name[11];char job_title[10];int manager;char hire_date[10];float salary;float commission;int dept_num;} emp_rec;struct /* DEPT record */{int dept_num;char dept_name[15];char location[14];} dept_rec;struct /* BONUS record */{char emp_name[11];char job_title[10];float salary;} bonus_rec;EXEC SQL END DECLARE SECTION;/* Handle Oracle errors.*/EXEC SQL WHENEVER SQLERROR DO sql_error();/* Connect to Oracle.*/EXEC SQL CONNECT :uid;/* Initialize cursor variable.*/EXEC SQL ALLOCATE :generic_cv;/* Exit loop when done fetching.*/EXEC SQL WHENEVER NOT FOUND DO break;Interaction with Oracle 5-27 Using Cursor Variablesfor (;;){printf("\n1 = EMP, 2 = DEPT, 3 = BONUS");printf("\nEnter table number (0 to quit): ");gets(temp);table_num = atoi(temp);if (table_num PRINT return_codeRETURN_CODE-----------1Interaction with Oracle 5-29 Using Cursor VariablesThe SQL*Plus datatypeREFCURSOR lets you declare cursor variables, which youcan use to return query results from stored subprograms.In the script below, youdeclare a host variable of typeREFCURSOR.You use the SQL*Plus commandSETAUTOPRINTON to display the query results automatically.CREATE PACKAGE emp_data ASTYPE EmpRecTyp IS RECORD (emp_id NUMBER(4),emp_name VARCHAR2(10),job_title VARCHAR2(9),dept_name VARCHAR2(14),dept_loc VARCHAR2(13));TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;PROCEDURE get_staff (dept_no IN NUMBER,emp_cv IN OUT EmpCurTyp);END;/CREATE PACKAGE BODY emp_data ASPROCEDURE get_staff (dept_no IN NUMBER,emp_cv IN OUT EmpCurTyp) ISBEGINOPEN emp_cv FORSELECT empno, ename, job, dname, loc FROM emp, deptWHERE emp.deptno = dept_no AND emp.deptno = dept.deptnoORDER BY empno;END;END;/COLUMN EMPNO HEADING NumberCOLUMN ENAME HEADING NameCOLUMN JOB HEADING JobTitleCOLUMN DNAME HEADING DepartmentCOLUMN LOC HEADING LocationSET AUTOPRINT ONVARIABLE cv REFCURSOREXECUTE emp_data.get_staff(20, :cv)5-30 PL/SQL User s Guide and Reference Using Cursor VariablesReducing Network TrafficWhen passing host cursor variables to PL/SQL, you can reduce network traffic bygroupingOPEN-FOR statements.For example, the following PL/SQL block opensfive cursor variables in a single round trip:/* anonymous PL/SQL block in host environment */BEGINOPEN :emp_cv FOR SELECT * FROM emp;OPEN :dept_cv FOR SELECT * FROM dept;OPEN :grade_cv FOR SELECT * FROM salgrade;OPEN :pay_cv FOR SELECT * FROM payroll;OPEN :ins_cv FOR SELECT * FROM insurance;END;This might be useful in Oracle Forms, for instance, when you want to populate amulti-block form.When you pass host cursor variables to a PL/SQL block for opening, the querywork areas to which they point remain accessible after the block completes.Thatallows your OCI or Pro*C program to use these work areas for ordinary cursoroperations.In the following example, you open several such work areas in a singleround trip:BEGINOPEN :c1 FOR SELECT 1 FROM dual;OPEN :c2 FOR SELECT 1 FROM dual;OPEN :c3 FOR SELECT 1 FROM dual;OPEN :c4 FOR SELECT 1 FROM dual;OPEN :c5 FOR SELECT 1 FROM dual;.END;The cursors assigned toc1,c2,c3,c4, andc5 behave normally, and you can usethem for any purpose.When finished, simply release the cursors, as follows:BEGINCLOSE :c1;CLOSE :c2;CLOSE :c3;CLOSE :c4;CLOSE :c5;.END;Interaction with Oracle 5-31 Using Cursor VariablesAvoiding ErrorsIf both cursor variables involved in an assignment are strongly typed, they musthave the same datatype.In the example below, even though the cursor variableshave the same return type, the assignment raises an exception because they havedifferent datatypes.However, if one or both cursor variables are weakly typed, theyneed not have the same datatype.DECLARETYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,tmp_cv IN OUT TmpCurTyp) ISBEGIN.emp_cv := tmp_cv; -- causes wrong type errorEND;If you try to fetch from, close, or apply cursor attributes to a cursor variable thatdoes not point to a query work area, PL/SQL raisesINVALID_CURSOR.You canmake a cursor variable (or parameter) point to a query work area in two ways:OPEN the cursor variableFOR the query.Assign to the cursor variable the value of an alreadyOPENed host cursorvariable or PL/SQL cursor variable [ Pobierz całość w formacie PDF ]
zanotowane.pl doc.pisz.pl pdf.pisz.pl odbijak.htw.pl
.*/CLOSE emp_cv;When declaring a cursor variable as the formal parameter of a subprogram thatcloses the cursor variable, you must specify theIN orINOUT mode.If you try to close an already-closed or never-opened cursor variable, PL/SQL raisesthe predefined exceptionINVALID_CURSOR.5-24 PL/SQL User s Guide and Reference Using Cursor VariablesExample 1Consider the stored procedure below, which searches the database of a main libraryfor books, periodicals, and tapes.A master table stores the title and category code(where1 = book,2 = periodical,3 = tape) of each item.Three detail tables storecategory-specific information.When called, the procedure searches the master tableby title, uses the associated category code to pick anOPEN-FOR statement, thenopens a cursor variable for a query of the proper detail table.CREATE PACKAGE cv_types ASTYPE LibCurTyp IS REF CURSOR;.END cv_types;CREATE PROCEDURE find_item (title VARCHAR2(100),lib_cv IN OUTcv_types.LibCurTyp)AScode BINARY_INTEGER;BEGINSELECT item_code FROM titles INTO codeWHERE item_title = title;IF code = 1 THENOPEN lib_cv FOR SELECT * FROM booksWHERE book_title = title;ELSIF code = 2 THENOPEN lib_cv FOR SELECT * FROM periodicalsWHERE periodical_title = title;ELSIF code = 3 THENOPEN lib_cv FOR SELECT * FROM tapesWHERE tape_title = title;END IF;END find_item;Interaction with Oracle 5-25 Using Cursor VariablesExample 2A client-side application in a branch library might use the following PL/SQL blockto display the retrieved information:DECLARElib_cv cv_types.LibCurTyp;book_rec books%ROWTYPE;periodical_rec periodicals%ROWTYPE;tape_rec tapes%ROWTYPE;BEGINget_title(:title); -- title is a host variablefind_item(:title, lib_cv);FETCH lib_cv INTO book_rec;display_book(book_rec);EXCEPTIONWHEN ROWTYPE_MISMATCH THENBEGINFETCH lib_cv INTO periodical_rec;display_periodical(periodical_rec);EXCEPTIONWHEN ROWTYPE_MISMATCH THENFETCH lib_cv INTO tape_rec;display_tape(tape_rec);END;END;Example 3The following Pro*C program prompts the user to select a database table, opens acursor variable for a query of that table, then fetches rows returned by the query:#include#includevoid sql_error();main(){char temp[32];EXEC SQL BEGIN DECLARE SECTION;char * uid = "scott/tiger";SQL_CURSOR generic_cv; /* cursor variable */int table_num; /* selector */struct /* EMP record */5-26 PL/SQL User s Guide and Reference Using Cursor Variables{int emp_num;char emp_name[11];char job_title[10];int manager;char hire_date[10];float salary;float commission;int dept_num;} emp_rec;struct /* DEPT record */{int dept_num;char dept_name[15];char location[14];} dept_rec;struct /* BONUS record */{char emp_name[11];char job_title[10];float salary;} bonus_rec;EXEC SQL END DECLARE SECTION;/* Handle Oracle errors.*/EXEC SQL WHENEVER SQLERROR DO sql_error();/* Connect to Oracle.*/EXEC SQL CONNECT :uid;/* Initialize cursor variable.*/EXEC SQL ALLOCATE :generic_cv;/* Exit loop when done fetching.*/EXEC SQL WHENEVER NOT FOUND DO break;Interaction with Oracle 5-27 Using Cursor Variablesfor (;;){printf("\n1 = EMP, 2 = DEPT, 3 = BONUS");printf("\nEnter table number (0 to quit): ");gets(temp);table_num = atoi(temp);if (table_num PRINT return_codeRETURN_CODE-----------1Interaction with Oracle 5-29 Using Cursor VariablesThe SQL*Plus datatypeREFCURSOR lets you declare cursor variables, which youcan use to return query results from stored subprograms.In the script below, youdeclare a host variable of typeREFCURSOR.You use the SQL*Plus commandSETAUTOPRINTON to display the query results automatically.CREATE PACKAGE emp_data ASTYPE EmpRecTyp IS RECORD (emp_id NUMBER(4),emp_name VARCHAR2(10),job_title VARCHAR2(9),dept_name VARCHAR2(14),dept_loc VARCHAR2(13));TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;PROCEDURE get_staff (dept_no IN NUMBER,emp_cv IN OUT EmpCurTyp);END;/CREATE PACKAGE BODY emp_data ASPROCEDURE get_staff (dept_no IN NUMBER,emp_cv IN OUT EmpCurTyp) ISBEGINOPEN emp_cv FORSELECT empno, ename, job, dname, loc FROM emp, deptWHERE emp.deptno = dept_no AND emp.deptno = dept.deptnoORDER BY empno;END;END;/COLUMN EMPNO HEADING NumberCOLUMN ENAME HEADING NameCOLUMN JOB HEADING JobTitleCOLUMN DNAME HEADING DepartmentCOLUMN LOC HEADING LocationSET AUTOPRINT ONVARIABLE cv REFCURSOREXECUTE emp_data.get_staff(20, :cv)5-30 PL/SQL User s Guide and Reference Using Cursor VariablesReducing Network TrafficWhen passing host cursor variables to PL/SQL, you can reduce network traffic bygroupingOPEN-FOR statements.For example, the following PL/SQL block opensfive cursor variables in a single round trip:/* anonymous PL/SQL block in host environment */BEGINOPEN :emp_cv FOR SELECT * FROM emp;OPEN :dept_cv FOR SELECT * FROM dept;OPEN :grade_cv FOR SELECT * FROM salgrade;OPEN :pay_cv FOR SELECT * FROM payroll;OPEN :ins_cv FOR SELECT * FROM insurance;END;This might be useful in Oracle Forms, for instance, when you want to populate amulti-block form.When you pass host cursor variables to a PL/SQL block for opening, the querywork areas to which they point remain accessible after the block completes.Thatallows your OCI or Pro*C program to use these work areas for ordinary cursoroperations.In the following example, you open several such work areas in a singleround trip:BEGINOPEN :c1 FOR SELECT 1 FROM dual;OPEN :c2 FOR SELECT 1 FROM dual;OPEN :c3 FOR SELECT 1 FROM dual;OPEN :c4 FOR SELECT 1 FROM dual;OPEN :c5 FOR SELECT 1 FROM dual;.END;The cursors assigned toc1,c2,c3,c4, andc5 behave normally, and you can usethem for any purpose.When finished, simply release the cursors, as follows:BEGINCLOSE :c1;CLOSE :c2;CLOSE :c3;CLOSE :c4;CLOSE :c5;.END;Interaction with Oracle 5-31 Using Cursor VariablesAvoiding ErrorsIf both cursor variables involved in an assignment are strongly typed, they musthave the same datatype.In the example below, even though the cursor variableshave the same return type, the assignment raises an exception because they havedifferent datatypes.However, if one or both cursor variables are weakly typed, theyneed not have the same datatype.DECLARETYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,tmp_cv IN OUT TmpCurTyp) ISBEGIN.emp_cv := tmp_cv; -- causes wrong type errorEND;If you try to fetch from, close, or apply cursor attributes to a cursor variable thatdoes not point to a query work area, PL/SQL raisesINVALID_CURSOR.You canmake a cursor variable (or parameter) point to a query work area in two ways:OPEN the cursor variableFOR the query.Assign to the cursor variable the value of an alreadyOPENed host cursorvariable or PL/SQL cursor variable [ Pobierz całość w formacie PDF ]