Summary of several knowledge points in PL/SQL (1)

1. Update multiple fields in batches

Update dog QQ set (B, c) =
(Select B, c from cat TT where TT. A = QQ.)
The brackets above are important and cannot be omitted.

T-SQL cannot be written like this. A statement from dog is required. It is a little more troublesome.

2. generate a new table with the target table and copy the data

Create Table horse as select * from dog

3. insert data from one table to another

Insert into dog select * from cat

4. Execute a DDL statement in the Stored Procedure

Dbms_utility.exec_ddl_statement ('Grant mx_admin_role to '| p_username );

5. How to execute a dynamic DML statement

 

----------------------------
--- Copy the archived detailed data to mx_mxb_committed_t_200
--- Parameter description:
-- Pd_id_list list of product IDs, for example, '22, 34,35, 36,37, 38,39, 40'
----------------------------
Procedure mx_copy_to_200
(
Pd_id_list in varchar2
)
Is
V_cursor number;
V_numrows number;
S varchar2 (800 );
S1 varchar2 (250 );
S2 varchar2 (250 );
Begin
S: = 'insert into mxk_200 ';
S: = S | 'select Q. *, ''', ''' from mxk Q ';
S: = S | 'where mxb_id in ';

S1: = 'select B. mxb_id from mx_pd_mxb_syn A, mxk B ';
S1: = S1 | 'where a. p_id in ('| pd_id_list |') and A. p_lth_id = B. m_mxb_id ';

S2: = 'select D. mxb_id from mx_pd_mxb_syn C, mxk_200 d ';
S2: = S2 | 'where C. p_id in ('| pd_id_list |') and C. p_lth_id = D. m_mxb_id ';

S: = S | '(' | S1 | 'minus' | S2 | ')';
V_cursor: = dbms_ SQL .open_cursor;
Dbms_ SQL .parse (v_cursor, S, dbms_ SQL .v7 );
V_numrows: = dbms_ SQL .execute (v_cursor );
Dbms_ SQL .close_cursor (v_cursor );
 
End mx_copy_to_200;