[1] Remove the statements in the script including commit, "setserveroutoff", "setserverouton", "setescapeon", and "exec". [2] Remove"
[1] Remove the statements in the script including commit, "setserverout off", "set serverout on", "set escape on", and "exec". [2] Remove "/
[1] Remove the statements in the script including commit, "setserverout off", "set serverout on", "set escape on", and "exec ".
[2] Remove "/"
[3] it is best to use "separator" + "line feed" for statement separators, for example:
Alter table xxx ADD (temp_advtoolbar CLOB )! UPDATE xxx SET temp_advtoolbar = advtoolbar!
[4] determine whether the data exists before inserting configuration data
When we want to insert a configuration data in the WCMConfig table, we need to first determine whether the configuration parameter exists. Note that the code is "end ;!" End, such:
Declare v_count number (10 );
Begin
SELECT count (*) into v_count FROM xxx WHERE CKey = 'kms _ UPLOAD_FILE_MAX_SIZE ';
If (v_count <= 0 or v_count is null) then
Insert into xxx (configid, ctype, ckey, cvalue, cdesc) select max (configid) + 6291456, 'kms _ UPLOAD_FILE_MAX_SIZE ', '123 ', 'The maximum file size that can be uploaded during batch upload, in k' from xxx;
Update yyyset nextid = 0 where tablename = 'xxx ';
End if;
End ;!
[5] to add a column to a table, you must first determine whether the Column exists.
When we want to add the LEAFFLAG column to the xxx table, we need to first determine whether the xxx table already has the LEAFFLAG column. Note that the code is "end ;!" The Code is as follows:
-- Add the field 2011.10.12 by liwei for whether to allow the creation of sub-scenarios (that is, whether it is a leaf node)
Declare v_count number (10 );
Begin
SELECT count (*) into v_count FROM cols
WHERE table_name = 'xxx' and column_name = 'leafflag ';
If (v_count <= 0 or v_count is null) then
Execute immediate ('alter table xxx add LeafFlag number default 0 not null ');
End if;
End ;!
Note:
When we need to use the begin end statement block, only DML (data operation language, such as insert, delete, update, and select) can be used in the begin statement block ), for example, add the configuration parameter code:
Declare v_count number (10 );
Begin
SELECT count (*) into v_count FROM xxx WHERE CKey = 'kms _ UPLOAD_FILE_MAX_SIZE ';
If (v_count <= 0 or v_count is null) then
Insert into xxx (configid, ctype, ckey, cvalue, cdesc) select max (configid) + 6291456, 'kms _ UPLOAD_FILE_MAX_SIZE ', '123 ', 'The maximum file size that can be uploaded during batch upload, in k' from wcmconfig;
Update yyy set nextid = 0 where tablename = 'xxx ';
End if;
End ;!
When adding columns, we may use the alter operation to add a column to a table. In this case, we need to use dynamic SQL (that is, execute immediate) for execution. Because the begin end statement block can only execute DML language, if you want to execute DDL (alter, create, and so on) language, you need to use dynamic SQL. For example:
Declare v_count number (10 );
Begin
SELECT count (*) into v_count FROM cols
WHERE table_name = 'xxx' and column_name = 'leafflag ';
If (v_count <= 0 or v_count is null) then
Execute immediate ('alter table xxx add LeafFlag number default 0 not null ');
End if;
End ;!