Common Oracle statements

-- How to Use the grade Table data to update the usertable data (with the associated field userid)
Update usertable u set u. grade =
(Select g. grade from grade g where g. userid = u. userid );

-- How to generate serial numbers for query result Fields
Select rownum, t. * from sm_t_pad_new t

-- How to quickly create a backup table that is the same as the original table
Create table new_table as (select * from user );

-- How to view the data file storage path
Select tablespace_name, file_id, bytes/1024/1024, file_name
From dba_data_files order by file_id;

-- Query Information of employees with the same name
Select u1.userid, u1.username from user u1,
(Select username, count (*) from user group by username having count (username)> 1) u2
Where u1.username = u2.username;

-- Query by Time

Select * from user
Where create_time> = to_date ('2017-4-16 00:00:00 ', 'yyyy-MM-DD HH24: mi: ss ')
And create_time <= to_date ('2017-4-16 12:00:00 ', 'yyyy-MM-DD HH24: mi: ss ')

-- Batch Delete method 1
Declare
V_temp number;
Begin
Loop
Begin
Select 1 into v_temp from user
Where create_time <= to_date ('2017-4-16 17:35:22 ', 'yyyy-MM-DD HH24: mi: ss') and rownum <= 1;

Delete from user
Where create_time <= to_date ('2017-4-16 17:35:22 ', 'yyyy-MM-DD HH24: mi: ss') and rownum <= 2;

Commit;
Exception when no_data_found then exit;
End;
End loop;
End;

-- Batch Delete method 2
Declare
V_log_num number; -- number of log files in the database
V_archive number; -- number of log files to be archived
Begin
Select count (1) into v_log_num from v $ log;
Loop
Loop
Select count (1) into v_archive from v $ archive;
If v_archive <v_log_num-1 then exit;
Else dbms_lock.sleep (60 );
End if;
End loop;
Delete from user
Where create_time <= to_date ('2017-4-16 17:39:44 ', 'yyyy-MM-DD HH24: mi: ss') and rownum <= 2;
If SQL % rowcount = 0 then exit;
End if;
Commit;
End loop;
End;

-- Batch Delete method 3
Declare
V_ids varchar2 (4000 );
V_id varchar (20 );
V_char char;
Begin
V_ids: = '2017 1 ';
V_char: = ',';
While (length (v_ids)> 0)
Loop
Begin
If (instr (v_ids, v_char)> 0)
Then v_id: = substr (v_ids, 0, instr (v_ids, v_char)-1 );
V_ids: = substr (v_ids, instr (v_ids, v_char) + 1 );
Else
V_id: = v_ids;
V_ids: = '';
End if;
Delete from user where userId = v_id;
-- If SQL % rowcount = 0 then exit;
-- End if;
Dbms_output.put_line ('delete a piece of data. ');
End;
Commit;
Dbms_output.put_line (v_id );
End loop;
End;

-- Batch delete stored procedure
Create or replace procedure batchInsert (ids in varchar2, v_char in varchar2)
As
V_ids varchar2 (4000 );
V_id varchar2 (20 );
Begin
V_ids: = ids;
While (length (v_ids)> 0)
Loop
Begin
If (instr (v_ids, v_char)> 0) -- search for ',' in ids and return the location of ','. if not, return 0;
Then v_id: = substr (v_ids, 1, instr (v_ids, v_char)-1 );
V_ids: = substr (v_ids, instr (v_ids, v_char) + 1 );
Else
V_id: = v_ids;
V_ids: = '';
End if;
Delete from user where userId = v_id;
-- If SQL % rowcount = 0 then exit;
-- End if;
Dbms_output.put_line ('delete a piece of data. ');
End;
Commit;
Dbms_output.put_line (v_id );
End loop;
End batchInsert;

-- Execute the Stored Procedure
Declare
V_ids varchar2 (4000 );
V_char varchar2 (20 );
Begin
V_ids: = '1970, 2062,208 ';
V_char: = ',';
BatchInsert (v_ids, v_char );
End;

========================================================== ======================================

Create or replace procedure sm_p_sendNotice (
V_noticeId in varchar2, -- announcement number, corresponding to NOTICE_ID of the SM_T_NOTICE table
V_unitId in varchar2, -- Organization ID
V_title in varchar2, -- announcement TITLE, corresponding to the TITLE of the SM_T_NOTICE table
V_model_id in VARCHAR2 -- announcement category number, corresponding to MODEL_ID of the SM_T_NOTICE table
)
IS
V_notice_model_name NVARCHAR2 (100 );
BEGIN
SELECT NOTICE_NAME INTO v_notice_model_name FROM sm_t_notice_model WHERE NOTICE_MODEL_ID = v_model_id;
Insert into sm_t_notice_newest (NOITCE_STAFF_ID, NOTICE_ID, STAFF_ID, TITLE, out_time, notice_model_name)
(Select SM_S_NOITCE_NEWEST_ID.Nextval, v_noticeId, t. staff_id, v_title, sysdate, v_notice_model_name from SM_t_STAFF t
WHERE t. unit_id IN (SELECT unit_id FROM sm_t_unit start with unit_id = v_unitId connect by super_unit_ID = PRIOR unit_id ));
END sm_p_sendNotice;
--
DECLARE
V_noticeId varchar2 (4000 );
V_unitId varchar2 (20 );
V_title VARCHAR2 (500 );
V_model_id NVARCHAR2 (100 );
BEGIN
V_noticeId: = '20140901 ';
V_unitId: = '01 ';
V_title: = '929555993 Response Processing caliber ';
V_model_id: = '28 ';
Sm_p_sendNotice (v_noticeId, v_unitId, v_title, v_model_id );
END;
--

-- Task queue manager

Begin
Sys. dbms_job.submit (job =>: job,
What => 'sm _ p_insertMessage ;',
Next_date => to_date ('07-09-2010 02:30:00 ', 'dd-mm-yyyy hh24: mi: ss '),
Interval => 'trunc (SYSDATE + 1) + (2*60 + 30)/(24*60 )');
Commit;
End;

--

Oracle statements used to determine whether a table exists when creating a table

Declare cnt number;

Begin
--- Query whether the table to be created exists
Select count (*) into cnt from user_tables where table_name = 'entry _ modifystatus ';
--- Delete the table if it exists.
If cnt> 0 then
Dbms_output.put_line ('table exists not create ');
Else
Dbms_output.put_line ('table does not exist ');
Execute immediate 'create table ENTRY_MODIFYSTATUS (
ENTRY_ID VARCHAR2 (18) not null,
APPLY_TIME DATE not null,
Status number (2 ),
Constraint PK_ENTRY_MODIFYSTATUS primary key (ENTRY_ID, APPLY_TIME)
)';
End if;

Cnt: = 0;
End;

========================================================== ======

Create or replace procedure AP_T_RPT_PRO_LOGINCOUNT
AS
Login_count NVARCHAR2 (100 );
Week_login_count NVARCHAR2 (100 );
-- Date_time DATE;
Now_time DATE;
Flag CHAR (1 );
BEGIN
Select trunc (SYSDATE) INTO now_time FROM dual;
-- Date_time: = to_date ('2017-6-7 ', 'yyyy-MM-DD ');
-- WHILE (date_time <now_time-6)
-- Date_time: = date_time + 7;
-- SELECT d. ANALYSIS_FLAG INTO flag FROM ap_t_rpt_login_dict d
For a in (SELECT start_time FROM ap_t_rpt_login_dict WHERE start_time <now_time-6)
LOOP
BEGIN
SELECT d. ANALYSIS_FLAG INTO flag FROM ap_t_rpt_login_dict d WHERE d. start_time = A. START_TIME;
IF (flag = 'n ')
THEN
For c in (SELECT u1.unit _ id FROM sm_t_unit u1)
LOOP
Select count (DISTINCT t. staff_id) INTO login_count FROM SM_t_login T
WHERE t. unit_id = C. UNIT_ID
AND t. login_time> add_months (A. START_TIME + 7,-2)
AND t. login_time <A. START_TIME + 7;

Select count (DISTINCT t. staff_id) INTO week_login_count FROM SM_t_login T
WHERE t. unit_id = C. UNIT_ID
AND t. login_time> A. START_TIME
AND t. login_time <A. START_TIME + 7;

Insert into ap_t_rpt_login (rpt_login_id, unit_id, start_time, user_count, week_login_user)
VALUES (ap_s_rpt_login.nextval, C. UNIT_ID, A. START_TIME, login_count, week_login_count );

End loop;
UPDATE ap_t_rpt_login_dict SET ANALYSIS_FLAG = 'y' WHERE start_time = A. START_TIME;

End if;
END;
End loop;

END;