Commonly used Plsql sub-programs

01. Empty the Recycle Bin
Begin
EXECUTE IMMEDIATE ' purge RecycleBin ';
End
/
02. Truncate the table
Declare
Begin
Execute immediate ' TRUNCATE TABLE PERSON_BAK2 ';
End
/
03. Modify the table column to be empty
--Modification Date: September 1, 2014
--Modify Content: Modify table bt_sale_contract field Trans_time allow empty
--Modification Reason: Sales contract import times trans_time null 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 Column column length (character type, only allowed to grow, not allowed to shorten)
--Modification Date: September 3, 2014
--Modify the content: Modify the length of the table Bt_buy_detail field Product_Name
--Modify the length of the table Bt_buy_fprecord field Product_Name
--Change reason: import times its field length is not enough

--Modify the Product Name field, the Purchase Detail Import report its field length is not enough
ALTER TABLE Bt_buy_detail Modify (product_name varchar2 (500));
--Modify the Product Name field, invoice sheet Import report its field length is not enough
ALTER TABLE Bt_buy_fprecord Modify (product_name varchar2 (500));
Commit
05. Create a Table
--Modified by: Yixian
--Modified content: New pre-cast product/rework product library, available for selection at tender purchase
DECLARE
Vc_str VARCHAR2 (5000);
Vn_count number;
BEGIN
--See if the existing system has a Bt_product_model table
SELECT COUNT (*)
Into Vn_count
From User_tables
WHERE table_name = ' Bt_product_model ';
--If there is no new table, if there is no processing
IF Vn_count < 1 Then
VC_STR: = ' CREATE TABLE Bt_product_model
(
PRODUCT_MODEL_ID number is 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. table Column Increment field
--Modification Time: 2014-9-16
--Modified content: Sales Contract table increase contract mark
DECLARE
Vn_count number;
Vc_str VARCHAR2 (1000);
BEGIN
--See if the 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. Turn on the Recycle Bin
Begin
Execute immediate ' alter session set Recyclebin=on ';
End
/
08. Create a 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. Unlocking the 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 ' 132,37 ';

The system has 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;

Row not selected

Sql>
SQL Note
When writing SQL scripts, you should note the following:
1. Single quotation marks in SQL script, such as: Need to insert the string "AB ' 1 ' cd" In a field, cannot write ' ab ' 1 ' CD ', Need to write ' ab ' 1 ' ' CD '
2, SQL encountered in the string has the address & problem, need to change to ' | | '                                & ' | | ' For example: ' Abc&def ' needs to be modified to ' abc ' | | ' & ' | | ' def
or use the function Chr (38)
Sql> select CHR from dual;

C
-
&

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Commonly used Plsql sub-programs