[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vi) procedure, function and Sequence of common objects in database

Link navigation in this series:

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (a) table space, user

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ii) connection of databases

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iii) Import, export

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iv) DML and DDL for SQL statements

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (v) table, View of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vi) procedure, function and Sequence of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vii) Cursor of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (eight) common exception

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ix) Common package of database common objects

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (10)%type and%rowtype and common functions

5.3, procedure Operation 5.3.1, create the stored procedure

1 Create or Replace procedureProc_getperson (argnameinch varchar2, argmsg outvarchar2, argtable out Pkg. Refcur)
--parameter, do not need to define length2  as 3VNamevarchar2( -);--define the variable to set the length4 begin5VName:=Argname;--assignment, with each statement ending with a semicolon6  　　 OpenPke. Refcur is Select *  fromPersonwhereName likeVName||'%';--log query results to the return cursor7  　　 --of course, other complex judgments, conversions and other operations can be performed.8Argmsg:='Normal';9  　　 Commit;Ten  One 　　 Exception A  　　　　  whenOthers ThenArgmsg='Exception'; -  　　　　　　 rollback;--If you have previously executed a transactional statement, it is necessary to keep the data secure - EndProc_getperson;

5.3.2, deleting stored procedures

Drop procedure Pro_getperson;

5.4. function Operation5.4.1, creating functions

• With in Parameter

1 Create or Replace functionFun_getpersonname (argnameinch varchar2, Arggenderinch varchar2)return varchar22  as3Resultvarchar2( -);--Defining Variables4 begin5  　　 SelectName intoResult fromPersonwhereName=Argname andGender=Arggender andRowNum=1;6  　　 returnResult;--return value 8 EndFun_getpersonname;

• With out parameters

1 Create or Replace functionFun_getpersonname (argnameinch varchar2, Arggender outvarchar2)return varchar22  as3Resultvarchar2( -);--Defining Variables4 begin5  　　 SelectName,gender intoResult,arggender fromPersonwhereName=Argname andRowNum=1;6  　　 returnResult;--return value7 EndFun_getpersonname;

5.4.2, deleting functions

Drop function fun_getpersonname;

5.5, the operation of sequence

In Oracle, sequence is the serial number, which automatically increases each time it is taken, and sequence is not related to the table.
First, create sequence or create any sequence permissions.

5.5.1, creating sequences

1 Createsequence Seqpersonid2Increment by 1 --Add 1 each time3Start with 1 --sequence counting starting from 14Nomaxvalue--do not set the maximum value5Nocycle--consistent accumulation, non-cyclic6CacheTen; Sets the cache sequence, if the system is down, or if other conditions cause the sequence to be discontinuous,7  　　 --NoCache;--not the most cached

5.5.2, get the value of sequence

Currval: Returns the current value of the sequence
Nextval: Returns sequence Next value

1 Select  from -- To get the current value of the sequence, it must be used after the nextval is called, otherwise the error 2 Select  from -- gets the next value of the sequence, if it is used for the first time, returns the initial value set at the time of Creation

Where sequence can be used in SQL statements:

1. SELECT statements that do not contain subqueries, snapshot, and view
2. In the subquery of the INSERT statement
3. In the values of the INSERT statement
4. In the set of UPDATE

As in the INSERT statement

Insert  into person (id,name)values(seqpersonid.nextval,'sequence insert test ');

5.5.3, modifying a sequence

All sequence parameters other than start can be modified, and if you want to modify the value of start, you must first drop and recreate

Alter 99999999;

5.5.4, deleting a sequence

drop sequence Seqpersonid;

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vi) procedure, function and Sequence of common objects in database