Declare to pass variable in SQL query?

Question

Oracle

Hello!

The question is how to pass a variable in a sql query to a construct?

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;

As you understand, instead of Latin, it would be interesting for me to see the variables that I send from c #

For example:

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();

how, for example, this request leaves, I wanted to get it in the end ... Can anyone tell me or show me where to read?

As a result, I would like to get something like this:

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;";

but working

Epanko, in general, the solution is nowhere simpler, you have to take a walk :)

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);

Answer 1

The answer couldn't be simpler:

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);