ORACLE autogrow is easily callable by encapsulating functions

Good programming habits are a necessary process. Good programming habits can vary from person to person, but simple, basic code-level: Write comments, proper indentation, line wrapping, variable naming, and so on.

For US programmers, most of the time on the computer, in the computer for most of the time in the code, either to look at someone else's code, or to write code. In the process of looking at someone else's code, when you see someone else's code is very messy, the heart will certainly say, this fucking who wrote the code, looks really laborious, to comment No comment, the line is not wrapped, indentation and no rules. So, a good programming habit, one advantage is to facilitate themselves, but also convenient for others to see their own code. When programming, many things are not noticed because of some details. In the process of their own code to appear in the corresponding error, good code habits, but also conducive to the problem code quickly positioning. Good programming habits, the advantage is that you can avoid some problems, so as to improve work efficiency.

In general, our Oracle use of the keyword to use uppercase, which is beneficial to the programming specification, so here is a small tip:

In PL/SQL Tool-Preferences:

　　

In an Oracle database, the process of creating a table, and where the primary key is automatically growing, because Oracle does not provide an auto-growing self-ID, so we need to create the sequence ourselves and then in the method of calling the sequence to automatically grow the general process is as follows:

--Create TableCREATE TABLEt_test (test_idINTEGER CONSTRAINTT_test_pkPRIMARY KEY, nameVARCHAR2(Ten) not NULL); --Add comments to the tableCOMMENT on TABLEt_test is 'Test Table';--Add Comments to the columnsCOMMENT on COLUMNt_test.test_id is 'self-increment primary key'; COMMENT on COLUMNT_test.name is 'name';--create/sequencesCREATESEQUENCE seq_t_test MINVALUE1MAXVALUE 1E27 START with 1INCREMENT by 1NOCACHE CYCLE;--InsertINSERT  intoT_test (Test_id,name)VALUES(Seq_t_test.nextval,'Linkepeng');

every time I insert data like this, if I start without creating the corresponding sequence, we have to create it, and it is possible to name the conflict, and so on some cases, feel inconvenient to use .

Here I write a function to solve, the automatically created function so that the caller, do not have to care about whether the sequence has been created, and as long as each invocation of the corresponding table name, so that the corresponding growth ID can be obtained.

The first step:/*Description: Take a few characters to the left of the string Linkepeng Date: 2016-08-07*/CREATE OR REPLACE FUNCTIONLeftstr (M_textVARCHAR2, M_countINTEGER)RETURN VARCHAR asL_resultVARCHAR(4000);BEGINL_result:="'; IFM_text is  not NULL  ThenL_result:=Substr (M_text,1, M_count); END IF; RETURNL_result;END;/*Description: Create a Sequence object Linkepeng date: 2016-08-07*/CREATE OR REPLACE PROCEDURECreateSequence (M_tablenameinch VARCHAR2 --table name or sequence object name)--in the case of ordinary users, this statement needs to be added. AUTHIDCurrent_User asL_usernameVARCHAR2( -); L_sqlstringVARCHAR2( +); L_sequencename_cVARCHAR2( -); L_rowcountINTEGER; L_maxid Number; L_key_fieldnameVARCHAR2( -);BEGIN   --get the sequence name based on the table nameL_sequencename_c:= REPLACE(M_tablename,'-','_'); IF UPPER(Leftstr (L_sequencename_c,4))<>'seq_'  ThenL_sequencename_c:= 'seq_' ||L_sequencename_c; END IF; L_sequencename_c:= UPPER(Leftstr (L_sequencename_c, -)); --to remove a primary key field from a table name  BEGIN    SELECTcolumn_name intoL_key_fieldname fromUser_cons_columnsWHEREConstraint_nameinch (          SELECTconstraint_name fromuser_constraintsWHEREConstraint_type= 'P'  and UPPER(table_name)=UPPER(m_tablename)) andROWNUM=1; --Remove the maximum value from the table name, primary key fieldL_sqlstring:= 'SELECT MAX (' ||L_key_fieldname|| ') from' ||M_tablename; BEGIN      EXECUTEIMMEDIATE l_sqlstring intoL_maxid; EXCEPTION--Catch Error       whenOTHERS ThenL_maxid:=0; END; EXCEPTION whenOTHERS ThenL_maxid:=0; END; L_MAXID:=NVL (L_maxid,0); --Modify the next valueL_MAXID:=L_maxid+ 1; L_sqlstring:= 'CREATE SEQUENCE' ||L_sequencename_c; L_sqlstring:=L_sqlstring||'MINVALUE 1 MAXVALUE 1E27 START with'||To_char (L_MAXID)|| 'INCREMENT by 1 NOCACHE CYCLE';  PRAGMA autonomous_transaction; EXECUTEIMMEDIATE l_sqlstring;END;/*Description: Get a table primary key new ID Linkepeng Date: 2016-08-07*/CREATE OR REPLACE FUNCTIONGetnewid (M_tablenameinch VARCHAR2)RETURN INTEGERAUTHIDCurrent_User asL_strsqlVARCHAR2( +); L_newidINTEGER; L_rowcountINTEGER; l_sequencename_tVARCHAR2(255);BEGINl_sequencename_t:=LEFTSTR ('seq_'||REPLACE(M_tablename,'-','_'), -); --determine if a sequence exists       SELECT COUNT(*) intoL_rowcount fromuser_objectsWHEREObject_type= 'SEQUENCE'  and Upper(object_name)= Upper(l_sequencename_t); IFL_rowcount=0  Then         --creating a sequence from a stored procedurecreatesequence (m_tablename); END IF; L_strsql:='SELECT'||l_sequencename_t||'. Nextval from dual'; EXECUTEIMMEDIATE L_strsql intoL_newid; RETURNL_newid;END;

Where AUTHID Current_User prevents the user from appearing: Execute IMMEDIATE If there is no such thing in the stored procedure when executing the DDL, there will be insufficient permissions.

Another solution is to give the user elevated privileges: Under SYSDBA permissions user, increase the permissions command: GRANT CREATE any TABLE to ' username ';

After this is created, we can invoke our INSERT statement like this:

INSERT into T_test (test_id,name) VALUES (Getnewid (' t_test '), ' Linkepeng ');

When writing code, exceptions that may appear to be sequences that are not created can be well avoided.

ORACLE autogrow is easily callable by encapsulating functions