ORACLE stored procedure batch table creation - Notes

catalogue

1, Introduction to stored procedures and basic syntax

3, Write stored procedures and test them

4, Record of problems encountered this time

1, Introduction to stored procedures and basic syntax

(1) Definition

Stored procedure is a set of SQL statements to complete specific functions, which are compiled and stored in the database.

There are two kinds of stored procedures: (1) parameterless stored procedures; (2) Stored procedure with parameters.

(2) Basic syntax (no parameters)

create or replace procedure [Stored procedure name] is

begin

null;

end [Stored procedure name];

Of which:

create or replace procedure: an SQL statement that informs the Oracle database to create a stored procedure called [stored procedure name], If it exists, overwrite it;.

[stored procedure name]: a user-defined name, which is automatically generated according to the name filled in during creation.

is: keyword, indicating that it will be followed by a PL/SQL body.

begin: keyword indicating the beginning of PL/SQL body.

null: PL/SQL statement. If nothing is done, this sentence cannot be deleted, because at least one sentence is required in the PL/SQL body;

End: keyword indicating the end of PL/SQL body.

2, Table creation statement analysis

Only some table creation statements are intercepted here

select 'create table '||project_name||to_char(sysdate,'MMDD') ||' tablespace  XINJIA_DATA as 
select * from '||project_name||'@LINK96; '
from table_temp
where table_temp.project_name='contract_condition_term'
union
select 'create table '||project_name||to_char(sysdate,'MMDD') ||' tablespace  XINJIA_DATA as
select * from '||project_name||'@LINK96; '
from table_temp
where table_temp.project_name='contract_equip'
union all
select 'create table '||project_name||to_char(sysdate,'MMDD') ||' tablespace  XINJIA_DATA as
select * from '||project_name||'@LINK96; '
from table_temp
where table_temp.project_name='contract_fund_rent_income'

The query results are as follows:

That is, 3 table creation statements

Our target requirement for creating stored procedures: batch table creation.

However, we want the table name to include the date of the execution day, so the table name cannot be written dead, otherwise manual modification will add a lot of work.

The above is a query statement, and the query result is a table creation statement, so we actually want to execute the query result, not the query statement.

3, Write stored procedures and test them

Take one of the query statements as an example to create a table. The script is as follows:

create or replace procedure HWJ_EVERYDAY_BACKUP is
  V_SQL varchar2(2000);
begin
  --Article 1
  select 'create table ' || project_name || to_char(sysdate, 'MMDD') ||
         ' tablespace  XINJIA_DATA as 
    select * from ' || project_name || '@LINK96 ' INTO V_SQL
    from table_temp
   where project_name = 'contract_condition_term';
  --Output first
  DBMS_OUTPUT.PUT_LINE(V_SQL);
  --Implementation of Article 1
  BEGIN
    EXECUTE IMMEDIATE V_SQL;
    --Throw exception(Article 1)
    EXCEPTION 
      WHEN OTHERS THEN 
      DBMS_OUTPUT.put_line('sqlcode :' || sqlcode); 
      DBMS_OUTPUT.put_line('sqlerrm :' || sqlerrm); 
  END; 
end HWJ_EVERYDAY_BACKUP;

Of which:

V_SQL varchar2(2000);: A named V is defined_ SQL, a variable of type VARCHAR2, is used to store the execution result of the first query statement, that is, the first table creation statement.

--Article 1
  select 'create table ' || project_name || to_char(sysdate, 'MMDD') ||
         ' tablespace  XINJIA_DATA as 
    select * from ' || project_name || '@LINK96 ' INTO V_SQL
    from table_temp
   where project_name = 'contract_condition_term';

select... into... from: store the execution results of query statements in V_ In SQL.

  --Output first
  DBMS_OUTPUT.PUT_LINE(V_SQL);

DBMS_OUTPUT.PUT_LINE: output a line, the maximum length is 255, and the output is v_ Table building statements stored in SQL.

    BEGIN
        EXECUTE IMMEDIATE V_SQL;
        --Throw exception(Article 1)
        EXCEPTION 
          WHEN OTHERS THEN 
          DBMS_OUTPUT.put_line('sqlcode :' || sqlcode); 
          DBMS_OUTPUT.put_line('sqlerrm :' || sqlerrm); 
    END;

EXECUTE IMMEDIATE: execute 'execute sql statement immediately' in pl/sql. Here, execute create table

4, Record of problems encountered this time

1. About assigning values to variables:

There are two methods of assignment. The second method is adopted this time, not only because of the demand, but also because the first method always reports errors, so it is abandoned.

         a. Use: = to assign values
　　          v_name := 'wangwu'; -- Assign 'Wangwu' directly to the variable v_name；
         b. Assign values using select..into

2. About the report exception problem of executing the table creation statement:

(1) "Invalid character" exception reported:

Reference here: ORA-00911: invalid character problem and solution

(2) "Insufficient permission" exception is reported

Reference here: When you create tablesapce in plsql, you will be prompted that the permission is not enough to solve the problem

Specific process:

--Query user
SELECT *FROM DBA_USERS
--to grant authorization
grant create table to [Authorized user required]

3. Test the process of creating tables:

--Query table
select * from contract_condition_term1112
--Delete table
drop table contract_condition_term1112 
--View tables in the recycle bin
select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;
--Recovery table
FLASHBACK TABLE contract_condition_term1112 TO BEFORE DROP;

Reference here:

select * from dba_users;select * from user_users; select * from all_users; What are the respective roles_ Baidu Knowshttps://zhidao.baidu.com/question/453395255.html How to delete tables completely in Oracle Database_ weixin_33800463 blog - the table of CSDN blog keyword [purge] drop is not completely deleted, but stored in the recycle bin. If you want to manually clear the tables in the recycle bin, you need to use the keyword purge. Clear the specified table: purge table TABLE_NAME; Clear the recycle bin of the current user: purge recyclebin; Clear recycle bin for all users: purge dba_recyclebin; When deleting a table, do not put it in the Recycle Bin: drohttps://blog.csdn.net/weixin_33800463/article/details/94011067 Restore the dropped table in oracle - step by step, surprise cloud - blog Garden View the table select object in the recycle bin_ name,original_ name,partition_ name,type,ts_ name,createtime,droptime from recyclhttps://www.cnblogs.com/xiaoyu1994/p/8304969.html