[Oracle] pl/SQL, oracleplsql

[Oracle] pl/SQL, oracleplsql

This SQL statement can be used to query records at specific locations in a table.

 

-- Query Method: select * from (select t1. *, rownum rn from (select * from books) t1 where rownum <= 4) where rn> 2;

-- Use paging write Stored Procedure

-- 1 first create a package and define the cursor type

create or replace package fenyepackage as  type fenye_cursor is ref cursor; end fenyepackage; 

-- 2 main process

Create or replace procedure fenye (tablename in varchar2, -- table name pagesize in number, -- the size of pagenow in number on each page, -- myrows out number on the current page, -- total number of records mypageconnt out number, -- total number of pages l_cursor out fenyepackage. fenye_cursor -- cursor) is -- get the start and end position of the record v_begin number: = (pagenow-1) * pagesize + 1; v_end number: = pagenow * pagesize; v_ SQL varchar (1000 ); begin v_ SQL: = 'select bookid, bookname, pubhouse from (select t1. *, rownum rn from (select * from '| tablename | ') t1 where rownum <= '| v_end |') where rn> = '| v_begin; open l_cursor for v_ SQL; v_ SQL: = 'select count (*) from '| tablename; execute immediate v_ SQL into myrows; if mod (myrows, pagesize) = 0 then mypageconnt: = myrows/pagesize; else mypageconnt: = myrows/pagesize + 1; end if; -- close l_cursor; end;

-- 3 use procedure for testing

Create or replace procedure fenyeceshi (tablename in varchar2, pagesize in number, pagenow in number) is v_myrows number; v_mypagecount number; v_bookid books. bookid % type; v_bookname books. bookname % type; v_pubhouse books. pubhouse % type; l_cursor fenyepackage. fenye_cursor; begin fenye (tablename, pagesize, pagenow, v_myrows, v_mypagecount, l_cursor); -- Use loop to read records of a specific page: loop fetch l_cursor into v_bookid, v_bookname, v_pubhouse; -- not found: exit when l_cursor % notfound; dbms_output.put_line ('book ID is: '| v_bookid |' name is: '| v_bookname | 'Press is' | v_pubhouse); end loop; dbms_output.put_line ('total record: '| v_myrows | 'barri '); dbms_output.put_line ('total page number: '| v_mypagecount | 'page'); end;

SQL> exec fenyeceshi ('books ',); book ID is: 3 name: angle press is 20 book ID is: 4 name is: anglele press is 10 Total records are: the total number of four pages is: 2 pages PL/SQL procedure successfully completed

?? : The result is displayed, but the cursor is not closed during the main process.
It is normal if it is not disabled. If it is disabled, an error will occur.
Will this problem be solved later?

SQL> exec fenyeceshi ('books ',); begin fenyeceshi ('books',); end; ORA-01001: Invalid cursor ORA-06512: In "SCOTT. FENYECESHI ", line 12 ORA-06512: In line 2

This article is from the "orangleliu notebook" blog, please be sure to keep this http://blog.csdn.net/orangleliu/article/details/38309427

Oracle pl/SQL Problems

It is not a PLSQL problem, it violates the unique constraints on the table;

You can either disable the unique constraint on the table or add unexpected control to your program.

How to delete columns in oracle pl/SQL

Select a table -- select a field -- Right-click a field and choose --> drop