Create a stored procedure, the incoming parameters as a condition, the purpose is to generate a tabl-CodePudding

The create or replace procedure ljy_dm_product (
Prod_id varchar2
)

V_prom_nbr varchar2 (50);

- v_offer_id varchar (50);
The begin
V_prod_id:=prod_id;

The create table ls_dm_product as
The select Amy polumbo rod_id as prod_id_dm
From the product a
Where a. ast_upd & gt; To_date (' 20180920000000 ', 'yyyymmddhh24miss')
And not the exists (select 1 from the product b where Amy polumbo rod_id=p. rod_id)
And Amy polumbo rod_id=v_prod_id;

The exception
The when others then
Null;
end;
end;

CodePudding user response:

In the stored procedure you want to EXECUTE the create table operation with dynamic execution of SQL: EXECUTE IMMEDIATE 'create table aaa the select * from CCC'

According to your requirement you need to build an parameters such as v_sql varchar2 (1000);
Then assigned to the parameters such as SQL v_sql:='create table'
Finally executing the SQL EXECUTE IMMEDIATE v_sql
Built so that you can complete a form in the middle of operation, to the parameter under the fu is a piece of his own ideas, or in part to depend on oneself

CodePudding user response:

In the process of storage DML can only use dynamic SQL

1/f, there is a little slip of the tongue, new V_SQL is not parameter variables

CodePudding user response:

reference 1st floor weixin_41637486 response:

do you want to EXECUTE the create table in the process of storage operation to use dynamic SQL: EXECUTE IMMEDIATE 'create table aaa the select * from CCC'

According to your requirement you need to build an parameters such as v_sql varchar2 (1000);
Then assigned to the parameters such as SQL v_sql:='create table'
Finally executing the SQL EXECUTE IMMEDIATE v_sql
Built so that you can complete a form in the middle of operation, to the parameter under the fu is a piece of his own ideas, or in part to their own

Well, I'm back to the
The create or replace procedure ljy_dm_product (
Prod_id varchar2
)

V_prom_nbr varchar2 (50);
V_sql_1 varchar2 (4000);

The begin

V_sql_1:='create table ls_dm_product as
The select Amy polumbo rod_id as prod_id_dm
From the product a
Where a. ast_upd & gt; To_date (' 20180920000000 ', 'yyyymmddhh24miss')
And not the exists (select 1 from the product b where Amy polumbo rod_id=p. rod_id)
And Amy polumbo rod_id=: 1 ';
The EXECUTE IMMEDIATE v_sql_1
Using prom_id;

The exception
The when others then
Null;
end;

This parameter is called is wrong? Compilation is successful, but the test operation department

CodePudding user response:

reference liujunyanjiayou reply: 3/f

Quote: refer to 1st floor weixin_41637486 response:

In the stored procedure you want to EXECUTE the create table operation with dynamic execution of SQL: EXECUTE IMMEDIATE 'create table aaa the select * from CCC'

According to your requirement you need to build an parameters such as v_sql varchar2 (1000);
Then assigned to the parameters such as SQL v_sql:='create table'
Finally executing the SQL EXECUTE IMMEDIATE v_sql
Built so that you can complete a form in the middle of operation, to the parameter under the fu is a piece of his own ideas, or in part to their own

Well, I'm back to the
The create or replace procedure ljy_dm_product (
Prod_id varchar2
)

V_prom_nbr varchar2 (50);
V_sql_1 varchar2 (4000);

The begin

V_sql_1:='create table ls_dm_product as
The select Amy polumbo rod_id as prod_id_dm
From the product a
Where a. ast_upd & gt; To_date (' 20180920000000 ', 'yyyymmddhh24miss')
And not the exists (select 1 from the product b where Amy polumbo rod_id=p. rod_id)
And Amy polumbo rod_id=: 1 ';
The EXECUTE IMMEDIATE v_sql_1
Using prom_id;

The exception
The when others then
Null;
end;

This parameter is called is wrong? Compilation is successful, but the test operation department out

Assigned to the variables of SQL statements, suggest you use first select 'create table' * * * * * * from dual generated, and then execute the SQL query, can run so you can all to v_sql_1 inside

CodePudding user response:

refer to the second floor yaiger response:

the DML can only use dynamic SQL in the process of storage

1/f, there is a little slip of the tongue, new V_SQL is variable is not parameter

uh yes, can't call parameters in this shitty

CodePudding user response:

Single quotation marks within the single quotation marks to escape