Stored procedures use cursor variables to return result sets1) Login sqlplus, the user is Scott, using the table EMP provided by the user[SQL]C:/users/administrator>sqlplus Scott/scott2) write a function with the return value as a cursor
Recently, a project was created to improve database security. The customer asked all database operations to be stored in the stored procedure. It is easier to return a value for an update operation. However, if you want to return a record set, it is
4. AccessOracleProcess/function (1)
Stored procedures are often used when SQL Server is used as a program,OracleYou can also use the process or function.OracleThe process does not seem to have a return value, and the function that has a return value
Paging is one of the common functions in development, especially the Repeater paging, which collects some information and finds that the Repeater paging is usually divided into two types.
1. False paging (PagedDataSource implementation) 2. Real
Create a stored procedure under Scott in Oracle:
(Note: Starting from 9i, sys_refcursor is available. In earlier Oracle versions, you must use ref cursor and put it in a package)
Create or replace procedure sp_getdept
(Result out
Source: Network/Editor: getting started with programming: Unknown
The crystal report version is 8.5
The stored procedure is:
create or replace package pkg_test4astype v_cursor is ref cursor;end pkg_test4;/create or replace procedure
-- PagingSelect * from (select t1.ename, rownum rn from (select * from emp) t1 where rownum = 6;
-- Develop a packageCreate or replace package tespackageType test_cursor is ref cursor;End tespackage;
-- Paging Stored ProcedureCreate or replace
Select * from USER_TAB_COLUMNS to retrieve the table structures of all tables supported by this rule (detailed information of each column)
Field tablename, column_name, data_type, data_length, nullable (null), column_id (the order of the field in
Most of the questions raised in the Forum recently are related to column and column conversion. Therefore, I have summarized the knowledge of column and column conversion,I hope it will be helpful to you and make some mistakes at the same time.
(1) how to read the values in SEQNAME. NEXTVAL
You can directly insert into test values (SEQNAME. NEXTVAL) as follows:
SELECT tmpdata_seq.NEXTVAL
INTO id_temp
From dual; then id_temp can be used
(2) PLS-00103: The symbol ">" in the need of
Assume that, as shown in the following table, the number of rows corresponding to each I value is not fixed.
SQL> select * from T;
I a d---------------1 B 10:55:421 A 2008-03-27 10:55:461 D 10:55:302 Z 2008-03-27 10:55:552 t 10:55:59
To obtain the
A cursor cannot be opened with a dynamic statement,You can use the cursor variableBut the row variable cannot be determined when the table name in the dynamic statement is not sure,The original table is used as the row variable template to solve
Oracle Time Difference Calculation
Two Date fields: START_DATE, END_DATE, calculate the time difference between the two dates (by day, hour, minute, second, millisecond): Day: ROUND (TO_NUMBER (END_DATE-START_DATE )) hour: ROUND (TO_NUMBER
Original post address: connect to: Oracle9i Enterprise Edition Release 9.2.0.7.0-Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0-Production
The ZHS16GBK Character Set and AL16UTF16 NCHAR character set
Previously, we used the pagination function provided by the GridView. The speed is really slow. We decided that the custom paging Oracle database had more than pieces of data.
AspnetPager is doing well. Use it.
Oracle paging stored procedure:
Create
Create or replace Package TypesAsType ref_cursor is ref cursor;End;/
Create Table stock_prices (Ric varchar (6) primary key,Price number (7,2 ),Updated Date );/
Create or replace function sp_get_stocks (v_price in number)Return types.
1. What is a cursor?① Retrieve the result set from the table and point to a record for interaction at a time.② Operations in relational databases are performed on the complete set of rows.The row set returned by the SELECT statement includes all
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.