Could you please datatable as a parameter to the oracle database stored procedures-CodePudding

In c # development, more than through stored procedures to complete table data insertion, want to directly to insert data in the form of a datatable table passed as a parameter to a stored procedure, could you tell me how to achieve? Stored procedure how to write?

CodePudding user response:

Joining together into this
Insert into t ()
The select c11, c21 from dual union all
The select c12, c22 from dual

CodePudding user response:

You can use dynamic SQL

CodePudding user response:

Can you give a case!

CodePudding user response:

The

reference 3 floor ql_nanjing response:

can you give a case!

1 # is the case

CodePudding user response:

http://blog.csdn.net/mchdba/article/details/52010323
Look at this; There is a problem to leave a message

CodePudding user response:

I want to ask is how to realize the datatable parameter passing,
The create or replace procedure emp_addmembers_proc (p_dt emp_addmembers) in the as
Cursor emp_cur is
Select * from p_dt;
My_record emp_cur % rowtype;
The begin
The open emp_cur;
Loop
The fetch emp_cur
Into my_record;
Insert into emp
(eid, ename, job, sal)
Values
(my_record. Eid, my_record ename, my_record. Job, my_record. Sal);
end loop;
The close emp_cur;
end;
This is my a stored procedure, emp_addmembers is custom and I will pass the datatable format the same table type, but a compiler error, prompt table or view does not exist, the cursor statement is not complete!

CodePudding user response:

refer to 6th floor ql_nanjing response:

I want to ask is how to realize the datatable parameter passing,
The create or replace procedure emp_addmembers_proc (p_dt emp_addmembers) in the as
Cursor emp_cur is
Select * from p_dt;
My_record emp_cur % rowtype;
The begin
The open emp_cur;
Loop
The fetch emp_cur
Into my_record;
Insert into emp
(eid, ename, job, sal)
Values
(my_record. Eid, my_record ename, my_record. Job, my_record. Sal);
end loop;
The close emp_cur;
end;
This is my a stored procedure, emp_addmembers is custom and I will pass the datatable format the same table type, but a compiler error, prompt table or view does not exist, the cursor statement is not complete!

the above p_dt in emp_addmembers you try changing p_dt in varchar2

CodePudding user response:

You mean, you want to pass in a table name, and then the name of the table with you want is the same definition of the table and then use the cursor has been that insert data right then

CodePudding user response:

refer to the eighth floor weixin_41637486 response:

what do you mean, you want to pass in a table name, and then the name of the table with you want is the same definition of the table and then use the cursor has been that this to insert data

Yes, don't know if it will work!

CodePudding user response:

Teach genuflect is begged!

CodePudding user response:

Preliminary understanding, you mean is to want to put the table name as a parameter to storage, and then to query data from insert another one with the structure of the table, because of your incoming table name is a string, a stored procedure is not directly use string concatenation SQL statements run, suggest the stored procedure string variable internal life, assembled into the query Script first string, and then use the
The Open emp_cur For Script;
Loop the Fetch emp_cur Into my_record;
Exit the When emp_cur % Notfound;
An inset... ;

End loop;
The Close Mycursor

CodePudding user response:

Converted to JSON, that is ok,