PL/SQL中的存储过程可在表中插入新记录 [英] Stored procedure in PL/SQL to insert new record in table

问题描述

我需要创建一个PL/SQL存储过程以将记录添加到tblCity2表中. tblCity2示例:

I need to create a PL/SQL stored procedure to add a record to the tblCity2 table. Sample of tblCity2:

+-------------+--------+-------------+------------+
|     NAME    | CAPITAL|  POPULATION | STATE_CODE |
+-------------+--------+-------------+------------+
| Monterrey   |   Y    |   2015000   |      MX19  |
| Mazatlan    |   N    |    199830   |      MX25  |
| Guadalajara |   Y    |   2325000   |      MX14  |
+-------------+--------+-------------+------------+

该过程接收4个参数:城市名称，城市(如果不是首都)，城市人口和州名.状态名称是从表tblState2中获取的:

The procedure receives 4 parameters: the name of the city, if the city is a capital or not, the city’s population and the name of the state. The name of the state is being taken from the table tblState2:

+------+-----------------------+---------+-----------+
| CODE |         NAME          | POP1990 | AREA_SQMI |
+------+-----------------------+---------+-----------+
| MX02 | Baja California Norte | 1660855 | 28002.325 |
| MX03 | Baja California Sur   |  317764 | 27898.191 |
| MX18 | Nayarit               |  824643 | 10547.762 |
+------+-----------------------+---------+-----------+

该过程调用一个存储的函数，并向其传递状态名称，该函数返回该状态的代码，然后将其与其他参数一起用于在tblCity2中插入新记录.

The procedure calls a stored function passing to it the state name, and the function returns the state’s code, which is then used along with the other parameters to insert the new record in tblCity2.

我已经开始了一些代码，但是不确定下一步:

I have started some code but I'm not sure of the next step:

PROCEDURE question2
(i_StateName IN TBLSTATE2.NAME%TYPE,
i_CityName IN TBLCITY2.NAME%TYPE,
i_CityCapital IN TBLCITY2.CAPITAL%TYPE,
i_CityPopulation IN TBLCITY2.POPULATION%TYPE);

FUNCTION create_city
(i_StateName IN TBLSTATE2.NAME%TYPE)
RETURN [something??]
INSERT INTO TBLCITY2 VALUES;
END question2;

我还需要插入一个匿名程序块来调用该过程.这样对吗?我可以使用与上面相同的代码来运行此代码，还是必须单独运行它?

I also need to insert an anonymous program block to call the procedure. Is this correct? Can I run this code in the same code as above or does it have to be run separately?

BEGIN
question2(i_StateName, i_CityName, i_CityCapital, i_CityPopulation);
END;

推荐答案

您可以编写带有SELECT .. INTO语句的函数，以根据状态名称获取状态代码. INTO子句允许您指定用于存储select语句返回值的变量.

You can write a function with a SELECT .. INTO statement to get the state code based on the state name. The INTO clause allows you to specify variables in which the return values of the select statement are stored.

FUNCTION getStateCode
  (i_StateName IN TBLSTATE2.NAME%TYPE) 
RETURN tblState2.CODE%TYPE IS
  V_CODE tblState2.CODE%TYPE;
BEGIN
    SELECT CODE
    INTO   V_CODE
    FROM   tblState2
    WHERE  NAME = i_StateName;

    RETURN V_CODE;
END;

请注意，查询必须仅返回一行.如果返回0或多于1行，它将失败，并且找不到数据或行太多.

Note that the query must return exactly one row. If it returns 0 or more than 1 row, it will fail with No Data Found or Too Many Rows.

将查询嵌入到SELECT FROM DUAL查询中可以捕获未找到数据错误:

A No Data Found error can be caught by embedding the query in a SELECT FROM DUAL query:

FUNCTION getStateCode
  (i_StateName IN TBLSTATE2.NAME%TYPE) 
RETURN tblState2.CODE%TYPE IS
  V_CODE tblState2.CODE%TYPE;
BEGIN
    SELECT (SELECT CODE
            FROM   tblState2
            WHERE  NAME = i_StateName)
    INTO   V_CODE
    FROM   DUAL

    RETURN V_CODE;
END;

该函数返回代码，然后可以在过程的插入语句中使用该代码:

That function returns the code, which can then be used in an insert statement in a procedure:

PROCEDURE insertCity
  (i_StateName      IN TBLSTATE2.NAME%TYPE,
   i_CityName       IN TBLCITY2.NAME%TYPE,
   i_CityCapital    IN TBLCITY2.CAPITAL%TYPE,
   i_CityPopulation IN TBLCITY2.POPULATION%TYPE) IS

BEGIN

    INSERT INTO TBLCITY2 (NAME, CAPITAL, POPULATION, STATE_CODE)
    VALUES(
      i_CityName,
      i_CityCapital,
      i_CityPopulation,
      getStateCode(i_StateName)
    );

END;

然后您可以像完全一样调用该过程:

You can then call the procedure exactly like you do:

BEGIN
  insertCity(i_StateName, i_CityName, i_CityCapital, i_CityPopulation);
END;

就像@APC在评论中提到的那样，在函数中修改数据是不正确的做法.这样做的原因是，也可以从查询中调用函数，因此这意味着当您只想查询数据时，可能会意外地修改数据.

Like @APC has mentioned in the comments, it's bad practice to modify data inside a function. The reason for this, is functions can be called from queries as well, so that means you can accidentally modify data when you just want to query the data.

因此在上面的设置中，有一个仅查询数据的功能.该函数可以在SQL或过程中使用(在这种情况下).该过程可以安全地修改数据，因为无论如何它都不能在选择查询中使用.

So in the setup above, there is a function that just queries data. The function can be used in SQL or (in this case) in a procedure. The procedure can safely modify data, since it cannot be used in a select query anyway.

这篇关于PL/SQL中的存储过程可在表中插入新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！