The concept of cursors:
A cursor is a memory workspace of SQL that is defined by the system or user as a variable. The role of a cursor is to temporarily store blocks of data that are extracted from the database. In some cases, the data needs to be
Phase III
Q. Use a REF CURSOR to display the values in the EMP table.
A.
DECLARE
TYPE Emprectyp is record
(
EMPNO Emp.empno%type,
Ename Emp.ename%type,
JOB Emp.job%type,
MGR Emp.mgr%type,
HireDate Emp.hiredate%type,
SAL
/************************************************
Me with the cursor
/************************************************
Using an explicit cursor
Define cursor DECLARE
CURSOR cursor_name is select_statement;
Open the cursor Open
OPEN
One, no return value of the stored procedureAncient cloud: Make haste, in order to let the big guy easier to accept the paging process to write, I still from simple to complex, gradually to everyone to explain. The first is to master the simplest
This article is a partial translation of the official document, removing examples from the original text and adding a few examples at the end. Interested friends can click the link at the end of the article to read the official documentation.First,
One, no return value of the stored procedureAncient cloud: Make haste, in order to let the big guy easier to accept the paging process to write, I still from simple to complex, gradually to everyone to explain. The first is to master the simplest
Grammar:identifier [CONSTANT] datatype [not NULL] [: = | DEFAULT Expr]Identifier: Used to specify the name of a variable or constant.CONSTANT: Used to specify constants. When you define a constant, you must specify its initial value, and its value
One, no return value of the stored procedureAncient cloud: Make haste, in order to let the big guy easier to accept the paging process to write, I still from simple to complex, gradually to everyone to explain. The first is to master the simplest
1. Select queryA single SELECT statementRetrieving data from a database through select in PL/sql:Grammar: must use the INTO clause query must and can only return one row can use the full select syntax
Using the Select
1. What is a pseudo column. Give some examplesIt is a column, which is a actual column in the table.Eg USER, UID, Sysdate, ROWNUM, ROWID, NULL, and level.Suppose Customer table is there has different columns like Customer no, payments. What would be
It mainly records the knowledge about oracle storedprocedure, from definition to use. Start with the simplest example and go deeper. The refcursor is used later.
It mainly records the knowledge about oracle stored procedure, from definition to use.
Resource 1: Source Jason_zhu blog jason-zhu.iteye.comblog430647 to a salary table as an example, the table structure is as follows: table structure, each employee id pair should have multiple salary items and amount, need to query turn, change the
I. Overview:
1. The pipeline function is a function that can return a set of rows (nested table or array varray can be used). We can query it like a physical table or
Assign a value to the set variable.
2. Pipeline functions are executed in parallel.
Oracle, paging, and stored procedure are combined to create a paging stored procedure using PL/SQL.
1. Declare a referenced cursor
[SQL] view plaincopyprint? 01. create or replace package cur_ref_type02. as -- is or as can be used03. type cur_ref is
Values (name_outoutvarchar2, age_ininvarchar2) values; end; createorreplaceprocedureinsertRecord (UserIDinvarchar2, UserNameinvarchar2, UserAgeinvarchar2) isbegininsert
Alias (name_outoutvarchar2, age_ininvarchar2) as begin records = age_in; end;
Oh, when you create a stored procedure, you can directly use the ldquo; createorreplaceprocedure stored procedure name rdquo.
Oh, when you create a stored procedure, you can directly use the ldquo; create or replace procedure stored procedure name
Since Oracle stored procedures do not return values, all their return values are replaced by the out parameter, and the list is no exception. However, because it is a set, it cannot be used as a general
Since Oracle stored procedures do not return
-- Create a package specificationCreate or replace package package_pageType cursor_page is ref cursor;Procedure proc_page (P_curpage number, -- current pageP_pagesize number, -- size of each pageP_tablename varchar2, -- table name EMP EP_where
Method 1: Package and procedure
First, create a cursor for the returned result set:
Create or replace package pkgaccq_tbuserprIsType result is ref cursor;End pkgaccq_tbuserpr;
Use the result set defined above to multiply and put the result
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.