我对oracle中的函数有问题。 我正在尝试向现有表添加新行,但我不知道我是否一直朝着正确的方向前进,我遇到了错误。 该函数在Postgresql中工作,现在希望将其转换为Oracle。
功能:
CREATE OR REPLACE FUNCTION add_human(
first_name VARCHAR(15),
last_name VARCHAR (15),
birthday DATE,
pesel BIGINT,
employee_function(VARCHAR(15))
RETURNS void AS
$$ BEGIN
INSERT INTO WORKERS(first_name, last_name, birthday, pesel, employee_function)
VALUES ($1, $2, $3, $4, $5); END; $$ LANGUAGE plpgsql;
选择:
select add_human(‘John’,’Wick’,’1971-01-27’,’71012745639’,’Actor’);
在 Oracle 中,您将使用过程将行插入到表中,而不是函数(它们将用于返回特定值(。
那将是这样的:
SQL> CREATE OR REPLACE PROCEDURE add_human
2 (
3 p_first_name in VARCHAR2,
4 p_last_name in VARCHAR2,
5 p_birthday in DATE,
6 p_pesel in NUMBER,
7 p_employee_function in VARCHAR2
8 )
9 AS
10 BEGIN
11 INSERT INTO WORKERS
12 (first_name, last_name, birthday, pesel, employee_function)
13 VALUES (p_first_name, p_last_name, p_birthday, p_pesel, p_employee_function);
14 END add_human;
15 /
Procedure created.
SQL> BEGIN
2 add_human('John', 'Wick', date '1971-01-27', 71012745639, 'Actor');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM workers;
FIRST_NAME LAST_NAME BIRTHDAY PESEL EMPLOYEE_F
---------- ---------- ---------- -------------- ----------
John Wick 1971-01-27 71012745639 Actor
SQL>