Declare передать переменную в запросе SQL?

Question

[Альберт]

Здравствуйте!

Вопрос такой, как передать переменную в запросе sql в конструкцию?

declare
a int;
b int;
BEGIN
SELECT city.city_id into a FROM city WHERE city.name = 'Миасс';
SELECT city.city_id into b FROM city WHERE city.name = 'Плесецк';
INSERT INTO FIRM(FIRM_ID, NAME, POST_CITY_ID, JUR_CITY_ID) VALUES(supplier_seq.NEXTVAL, 'Спутник', a, b);
COMMIT;
end;

Как вы поняли вместо латиницы мне было бы интересно видеть переменные, которые я отправлю из с#

К примеру:

String sql = "INSERT INTO FIRM(FIRM_ID, NAME, POST_CITY_ID, JUR_CITY_ID) " +
                "VALUES(supplier_seq.NEXTVAL, :NAME, :POST_CITY_ID, :JUR_CITY_ID)";
          
            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("NAME", OracleDbType.Varchar2, 25).Value = CityNameTextBox.Text.ToString();
             cmd.Parameters.Add("POST_CITY_ID", OracleDbType.Varchar2, 25).Value = CityNameJurTextBox.Text.ToString();
             cmd.Parameters.Add("JUR_CITY_ID", OracleDbType.Varchar2, 25).Value = CityNamePostTextBox.Text.ToString();
            OracleDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            var v = dt.HasErrors;
            myDataGrid.ItemsSource = dt.DefaultView;
            dr.Close();

как к примеру уходит этот запрос хотелось получить в итоге... Может кто подскажет или покажет где почитать?

В итоге хотелось бы получить что то такое:

this.SqlAddFirmTable = "declare " +
                    " a int; " +
                    " b int; " +
                    " BEGIN" +
                    " SELECT city.city_id into a FROM city WHERE city.name = :POST_CITY_ID; " +
                    " SELECT city.city_id into b FROM city WHERE city.name = :JUR_CITY_ID; " +
                    " INSERT INTO FIRM(FIRM_ID, NAME, POST_CITY_ID, JUR_CITY_ID) VALUES(supplier_seq.NEXTVAL, :NAME, a, b); " +
                    " COMMIT; " +
                    " end;";

но рабочее

Епанько я вообщем, решение проще некуда, надо погулять :)

query = String.Format("declare" +
                                " a int;" +

                                " BEGIN" +
                                " SELECT count(city.city_id) into a FROM city WHERE city.name = '{0}';" +

                                " if a = 0 THEN" +
                                  " insert into city(CITY_ID, NAME) values(supplier_seq.NEXTVAL, '{0}');" +
                                " end if;" +
                                " SELECT city.city_id into a FROM city WHERE city.city_id =(select min(city.city_id) from city where city.name = '{0}');" +

                                " INSERT INTO FIRM(FIRM_ID, NAME, JUR_CITY_ID) VALUES(supplier_seq.NEXTVAL, '{2}', a);" +
                                " COMMIT;" +
                                " end; ", JUR_CITY, POST_CITY, NAME);

Comments

[alexalexes]

Ищите что-то в духе "c# how execute anonymous pl sql block".
https://social.msdn.microsoft.com/Forums/en-US/612...

[Альберт]

alexalexes, нашлось решение проще)

Answer 1

[Альберт]

Ответ проще некуда:

query = String.Format("declare" +
         " a int;" +
         " BEGIN" +
         " SELECT count(city.city_id) into a FROM city WHERE city.name = '{0}';" +
         " if a = 0 THEN" +
         " insert into city(CITY_ID, NAME) values(supplier_seq.NEXTVAL, '{0}');" +
         " end if;" +
         " SELECT city.city_id into a FROM city WHERE city.city_id =(select min(city.city_id) from city where city.name = '{0}');" +
         " INSERT INTO FIRM(FIRM_ID, NAME, JUR_CITY_ID) VALUES(supplier_seq.NEXTVAL, '{2}', a);" +
         " COMMIT;" +
         " end; ", JUR_CITY, POST_CITY, NAME);