Common PLSQL subprograms and plsql

Common PLSQL subprograms and plsql

01. Clear the recycle bin
Begin
Execute immediate 'purge recyclebin ';
End;
/
02. truncation table
Declare
Begin
Execute immediate 'truncate table person_bak2 ';
End;
/
03. Modify the table column to be empty.
-- Modification date: January 1, September 1, 2014
-- Modification content: the TRANS_TIME field of table BT_SALE_CONTRACT can be left blank.
-- Reason for modification: when the sales contract is imported, TRANS_TIME is NULL and cannot be inserted.
DECLARE
VN_NULLABLE VARCHAR2 (1 );
BEGIN
SELECT nullable
INTO VN_NULLABLE
FROM user_tab_columns
WHERE table_name = 'bt _ SALE_CONTRACT'
AND column_name = 'Trans _ time ';
IF VN_NULLABLE = 'n' THEN
Execute immediate 'alter TABLE BT_SALE_CONTRACT MODIFY TRANS_TIME DATE null ';
End if;
COMMIT;
END;
/
04. Modify the length of a column (in bytes type, variable length is allowed, and variable length is not allowed)
-- Modification date: January 1, September 3, 2014
-- Modify: Modify the length of PRODUCT_NAME in the BT_BUY_DETAIL field of the table.
-- Modify the length of PRODUCT_NAME in the BT_BUY_FPRECORD Field
-- Reason for modification: the length of the field is insufficient during import.

-- Modify the product name field and import the purchase details to indicate that the length of the field is insufficient.
Alter table BT_BUY_DETAIL modify (PRODUCT_NAME varchar2 (500 ));
-- Modify the product name field. When the invoice table is imported, the length of the field is insufficient.
Alter table BT_BUY_FPRECORD modify (PRODUCT_NAME varchar2 (500 ));
Commit;
05. Create a table
-- Modifier: Yi xiaoqun
-- Modified: added pre-investment products/product repair libraries, which can be selected during bidding and procurement.
DECLARE
VC_STR VARCHAR2 (5000 );
VN_COUNT NUMBER;
BEGIN
-- Check whether the existing system has a BT_PRODUCT_MODEL table
Select count (*)
INTO VN_COUNT
FROM USER_TABLES
WHERE TABLE_NAME = 'bt _ PRODUCT_MODEL ';
-- If no table exists, the table is added. If yes, the table is not processed.
IF VN_COUNT <1 THEN
VC_STR: = 'create table BT_PRODUCT_MODEL
(
Product_model_id NUMBER not null,
Product_code VARCHAR2 (30 ),
Product_name VARCHAR2 (30 ),
Product_type VARCHAR2 (30 ),
Product_desc VARCHAR2 (100 ),
Constraint PK_PRODUCT_MODEL_ID primary key (PRODUCT_MODEL_ID)
)';
Execute immediate VC_STR;
End if;
END;
/
06. Add fields to the table column
-- Modification time:
-- Modified: Add contract tags to the sales contract Table
DECLARE
VN_COUNT NUMBER;
VC_STR VARCHAR2 (1000 );
BEGIN
-- Check whether this field exists in the table
Select count (*)
INTO VN_COUNT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'bt _ SALE_CONTRACT 'AND COLUMN_NAME = 'contractflag ';
IF VN_COUNT <1 THEN
VC_STR: = 'alter TABLE BT_SALE_CONTRACT add contractflag VARCHAR2 (30 )';
Execute immediate VC_STR;
End if;
END;
/
07. Enable Recycle Bin
Begin
Execute immediate 'alter session set recyclebin = On ';
End;
/
08. Create Sequence
DECLARE
VC_STR VARCHAR2 (5000 );
VN_COUNT NUMBER;
BEGIN
Select count (*)
INTO VN_COUNT
FROM ALL_SEQUENCES
WHERE SEQUENCE_NAME = 'audit _ SEQ ';
IF VN_COUNT <1 THEN
VC_STR: = 'create SEQUENCE audit_seq
Start with 1000
Increment by 1
NOMAXVALUE
Nocycle nocache ;';
Execute immediate VC_STR;
End if;
END;
/
09. unlock a user
SQL> SELECT OBJECT_NAME, S. SID, S. SERIAL #
2 from gv $ LOCKED_OBJECT L, DBA_OBJECTS O, GV $ SESSION S
3 where l. OBJECT_ID = O. OBJECT_ID
4 and l. SESSION_ID = S. SID;

OBJECT_NAME
-----------------------------------------------------------------------

Sid serial #
--------------------
BT_USER
132 37

SQL> alter system kill session '2017, 37 ';

The system has been changed.

SQL> SELECT OBJECT_NAME, S. SID, S. SERIAL #
2 from gv $ LOCKED_OBJECT L, DBA_OBJECTS O, GV $ SESSION S
3 where l. OBJECT_ID = O. OBJECT_ID
4 and l. SESSION_ID = S. SID;

Unselected row

SQL>
SQL note
Note the following when writing SQL scripts:
1. nested single quotes in SQL scripts. For example, if you need to insert the string "AB '1' cd" into a field, it cannot be written as 'AB '1' cd ', you need to write 'AB' '1' as 'cd'
2. If the SQL statement encounters an issue of getting the address character & problem, it must be changed to '|' & '|' For example: 'abc & def 'needs to be changed to 'abc' |' & '| 'def'
Or use the function CHR (38)
SQL> select chr (38) from dual;

C
-
&

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.