EXEC PKG_EMP.CALCULTE_SAL('100');
使用CREATE OR REPLACE PROCEDURE语句创建 Oracle 存储过程。可选的OR REPLACE子句将覆盖具有相同名称的现有存储过程（如果存在）。
CREATE OR REPLACE PROCEDURE EMP_SAL_RAISE
(P_EMP_ID IN NUMBER, SAL_RAISE IN NUMBER)
V_EMP_CURRENT_SAL NUMBER;
BEGIN
SELECT SALARY INTO V_EMP_CURRENT_SAL
FROM EMPLOYEES WHERE EMPLOYEE_ID=P_EMP_ID;
UPDATE EMPLOYEES
SET SALARY=V_EMP_CURRENT_SAL+SAL_RAISE
WHERE EMPLOYEE_ID=P_EMP_ID;
DBMS_OUTPUT.PUT_LINE('New Salary For Employee ID: ' || P_EMP_ID ||
' Is ' || (V_EMP_CURRENT_SAL + SAL_RAISE));
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'An error was encountered - ' ||
SQLCODE || ' -ERROR-' || SQLERRM);
ROLLBACK;
COMMIT;
-- Run
EXEC EMP_SAL_RAISE(200, 1000);
使用CREATE OR REPLACE FUNCTION语句创建一个函数。
CREATE OR REPLACE FUNCTION EMP_PERIOD_OF_SERVICE_YEAR
(P_EMP_ID NUMBER)
RETURN NUMBER
V_PERIOD_OF_SERVICE_YEARS NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(HIRE_DATE))
INTO V_PERIOD_OF_SERVICE_YEARS
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_EMP_ID;
RETURN V_PERIOD_OF_SERVICE_YEARS;
SELECT EMPLOYEE_ID, FIRST_NAME,
EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS PERIOD_OF_SERVICE_YEAR
FROM EMPLOYEES;
EMPLOYEE_ID FIRST_NAME PERIOD_OF_SERVICE_YEAR
174 Ellen 13
166 Sundar 9
130 Mozhe 12
105 David 12
204 Hermann 15
116 Shelli 12
167 Amit 9
172 Elizabeth 10
使用CREATE OR REPLACE PACKAGE语句创建包。
CREATE OR REPLACE PACKAGE PCK_CHINOOK_REPORTS
PROCEDURE GET_ARTIST_BY_ALBUM(P_ARTIST_ID ALBUM.TITLE%TYPE);
PROCEDURE CUST_INVOICE_BY_YEAR_ANALYZE;
使用CREATE OR REPLACE PACKAGE BODY语句创建新包。
CREATE OR REPLACE PACKAGE BODY PCK_CHINOOK_REPORTS
PROCEDURE GET_ARTIST_BY_ALBUM(P_ARTIST_ID ALBUM.TITLE%TYPE)
V_ARTIST_NAME ARTIST.NAME%TYPE;
BEGIN
SELECT ART.NAME INTO V_ARTIST_NAME
FROM ALBUM ALB JOIN ARTIST ART USING(ARTISTID)
WHERE ALB.TITLE = P_ARTIST_ID;
DBMS_OUTPUT.PUT_LINE('ArtistName: ' || V_ARTIST_NAME);
PROCEDURE CUST_INVOICE_BY_YEAR_ANALYZE
V_CUST_GENRES VARCHAR2(200);
BEGIN
FOR V IN
(SELECT CUSTOMERID, CUSTNAME, LOW_YEAR, HIGH_YEAR, CUST_AVG
FROM TMP_CUST_INVOICE_ANALYSE)
IF SUBSTR(V.LOW_YEAR, -4) > SUBSTR(V.HIGH_YEAR , -4) THEN
SELECT LISTAGG(GENRE, ',') WITHIN GROUP (ORDER BY GENRE)
INTO V_CUST_GENRES FROM (
SELECT DISTINCT FUNC_GENRE_BY_ID(TRC.GENREID) AS GENRE
FROM TMP_CUST_INVOICE_ANALYSE TMPTBL
JOIN INVOICE INV USING(CUSTOMERID)
JOIN INVOICELINE INVLIN ON INV.INVOICEID = INVLIN.INVOICEID
JOIN TRACK TRC ON TRC.TRACKID = INVLIN.TRACKID
WHERE CUSTOMERID=V.CUSTOMERID);
DBMS_OUTPUT.PUT_LINE('Customer: ' || UPPER(V.CUSTNAME) ||
' - Offer a Discount According To Preferred Genres: ' ||
UPPER(V_CUST_GENRES));
END IF;
END LOOP;
EXEC PCK_CHINOOK_REPORTS.GET_ARTIST_BY_ALBUM();
EXEC PCK_CHINOOK_REPORTS.CUST_INVOICE_BY_YEAR_ANALYZE;
前面的示例演示了基本的 Oracle PL/SQL 过程和函数功能。Oracle PL/SQL 提供了大量不在本文档范围内的特性和功能。
有关详细信息，请参阅 Oracle 文档中的 CREATE FUNCTION 和 CREATE PROCEDURE 。
PostgreSQL 用法
PostgreSQL 使用CREATE FUNCTION语句为存储过程和存储函数提供支持。需要强调的是，PostgreSQL使用的过程语句仅支持CREATE FUNCTION语句。CREATE PROCEDURE语句与此 PostgreSQL 版本不兼容。
PL/pgSQL 是用于从Oracle PL/SQL代码迁移的主要数据库编程语言。PostgreSQL 支持的编程语言不限于PL/pgSQL，也支持这些语言：
PL/pgSQL
PL/Python
PL/Perl
Oracle PL/SQL 和 PostgreSQL PL/pgSQL 之间的互换性
PostgreSQL PL/pgSQL语言通常被认为是从Oracle PL/SQL代码迁移的理想候选者，因为PostgreSQL PL/pgSQL代码支持许多Oracle PL/SQL语法元素。
例如，PostgreSQL PL/pgSQL支持Oracle CREATE OR REPLACE PROCEDURE语句。还支持许多其他PL / SQL语法元素，使PostgreSQL和PL / pgSQL在从Oracle迁移时成为自然的替代方案。
PostgreSQL 创建函数权限
要创建函数，用户必须具有该语言的USAGE权限。创建函数时，可以指定语言参数，如示例中所示。
将 Oracle 存储过程和函数转换为 PostgreSQL PL/pgSQL。
使用 PostgreSQL 的CREATE FUNCTION命令创建一个名为FUNC_ALG的新函数。
CREATE OR REPLACE FUNCTION FUNC_ALG(P_NUM NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
RETURN P_NUM * 2;
END;$$
LANGUAGE PLPGSQL;
使用CREATE OR REPLACE语句创建新函数或替换现有函数，但有以下限制：
您无法更改函数名称或参数类型。
该语句不允许更改现有函数返回类型。
用户必须拥有函数才能替换它。
INPUT参数 （P_NUM） 的实现方式与 Oracle PL/SQL 的INPUT参数类似。
两个美元符号用于防止需要使用单引号字符串转义元素。使用两个美元符号时，使用单引号 （ ’ ） 时无需在代码中使用转义字符。两个美元符号出现在关键字AS之后和函数关键字END之后。
使用LANGUAGE PLPGSQL参数指定所创建函数的语言。
将Oracle EMP_SAL_RAISE PL/SQL函数转换为PostgreSQL PL/pgSQL。
CREATE OR REPLACE FUNCTION EMP_SAL_RAISE
(IN P_EMP_ID DOUBLE PRECISION,
IN SAL_RAISE DOUBLE PRECISION)
RETURNS VOID
AS $$
DECLARE
V_EMP_CURRENT_SAL DOUBLE PRECISION;
BEGIN
SELECT SALARY INTO STRICT V_EMP_CURRENT_SAL
FROM EMPLOYEES WHERE EMPLOYEE_ID = P_EMP_ID;
UPDATE EMPLOYEES
SET SALARY = V_EMP_CURRENT_SAL + SAL_RAISE
WHERE EMPLOYEE_ID = P_EMP_ID;
RAISE DEBUG USING MESSAGE := CONCAT_WS('',
'NEW SALARY FOR EMPLOYEE ID: ', P_EMP_ID, 'IS ',
(V_EMP_CURRENT_SAL + SAL_RAISE));
EXCEPTION
WHEN OTHERS THEN
RAISE USING ERRCODE := '20001', MESSAGE :=
CONCAT_WS('', 'AN ERROR WAS ENCOUNTERED - ',
SQLSTATE, ' -ERROR-', SQLERRM);
END;$$
LANGUAGE PLPGSQL;
select emp_sal_raise(200, 1000);
将Oracle EMP_PERIOD_OF_SERVICE_YEAR PL/SQL函数转换为PostgreSQL PL/pgSQL。
CREATE OR REPLACE FUNCTION
EMP_PERIOD_OF_SERVICE_YEAR (IN P_EMP_ID DOUBLE PRECISION)
RETURNS DOUBLE PRECISION
AS $$
DECLARE
V_PERIOD_OF_SERVICE_YEARS DOUBLE PRECISION;
BEGIN
SELECT
EXTRACT (YEAR FROM NOW()) - EXTRACT (YEAR FROM (HIRE_DATE))
INTO STRICT V_PERIOD_OF_SERVICE_YEARS
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
RETURN V_PERIOD_OF_SERVICE_YEARS;
END;$$
LANGUAGE PLPGSQL;
SELECT EMPLOYEE_ID, FIRST_NAME,
EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS PERIOD_OF_SERVICE_YEAR
FROM EMPLOYEES;
Oracle 包规格和包体
PostgreSQL 不支持 Oracle 包对象和包体。所有 PL/SQL 对象都必须转换为 PostgreSQL 函数。以下示例介绍了转换时如何处理 Oracle 包对象和包体名称。
Oracle 包名称：PCK_CHINOOK_REPORTS。Oracle 包过程：GET_ARTIST_BY_ALBUM。
EXEC PCK_CHINOOK_REPORTS.GET_ARTIST_BY_ALBUM('');
你可以在转换到 PostgreSQL 代码时，使用符号$分隔包和过程名称。
SELECT PCK_CHINOOK_REPORTS$GET_ARTIST_BY_ALBUM('');
将 Oracle 包和包体转换为 PostgreSQL PL/pgSQL。
在以下示例中，Oracle 包名称为PCK_CHINOOK_REPORTS，Oracle 包过程为GET_ARTIST_BY_ALBUM。
CREATE OR REPLACE FUNCTION
chinook."PCK_CHINOOK_REPORTS$GET_ARTIST_BY_ALBUM"
(p_artist_id text)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
V_ARTIST_NAME CHINOOK.ARTIST.NAME%TYPE;
BEGIN
SELECT art.name INTO STRICT V_ARTIST_NAME
FROM chinook.album AS alb
JOIN chinook.artist AS art
USING (artistid)
WHERE alb.title = p_artist_id;
RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'ArtistName: ', V_ARTIST_NAME);
$function$;
-- Procedures (Packages) Verification
set client_min_messages = 'debug';
-- Equivalent to Oracle SET SERVEROUTPUT ON
select chinook.pck_chinook_reports$get_artist_by_album(' Fireball');
在以下示例中，Oracle 包名称为PCK_CHINOOK_REPORTS，Oracle 包过程为CUST_INVOICE_BY_YEAR_ANALYZE。
CREATE OR REPLACE FUNCTION chinook."pck_chinook_reports$cust_invoice_by_year_analyze" ()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
v_cust_genres CHARACTER VARYING(200);
v RECORD;
BEGIN
FOR v IN
SELECT customerid, custname, low_year, high_year, cust_avg
FROM chinook.tmp_cust_invoice_analyse
IF SUBSTR(v.low_year, - 4) > SUBSTR(v.high_year, - 4) THEN
-- Altering Oracle LISTAGG Function With PostgreSQL STRING_AGG Function
select string_agg(genre, ',') into v_cust_genres
from (select distinct chinook.func_genre_by_id(trc.genreid) as genre
from chinook.tmp_cust_invoice_analyse tmptbl
join chinook.INVOICE inv using(customerid)
join chinook.INVOICELINE invlin on inv.invoiceid = invlin.invoiceid
join chinook.TRACK trc on trc.trackid = invlin.trackid
where customerid=v.CUSTOMERID) a;
-- PostgreSQL Equivalent To Oracle DBMS_OUTPUT.PUT_LINE()\
RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Customer: ',
UPPER(v.custname), ' - Offer a Discount According To Preferred Genres: ',
UPPER(v_cust_genres));
END IF;
END LOOP;
$function$;
-- Running
SELECT chinook.pck_chinook_reports$cust_invoice_by_year_analyze();
PostgreSQL 版本 10 中LATERAL FROM子句使用的集合返回函数的新行为。
CREATE TABLE emps (id int, manager int);
INSERT INTO tab VALUES (23, 24), (52, 23), (21, 65);
SELECT x, generate_series(1,5) AS g FROM tab;
id |g
---|--
23 |1
23 |2
23 |3
23 |4
23 |5
52 |1
52 |2
52 |3
52 |4
52 |5
21 |1
21 |2
21 |3
21 |4
21 |5
SELECT id, g FROM emps, LATERAL generate_series(1,5) AS g;
id |g
---|--
23 |1
23 |2
23 |3
23 |4
23 |5
52 |1
52 |2
52 |3
52 |4
52 |5
21 |1
21 |2
21 |3
21 |4
21 |5
在这里，规划器可以选择将设置-返回函数放在 nestloop 连接的外部，因为它对 emps 表没有实际的横向依赖关系。
有关详细信息，请参阅 PostgreSQL 文档中的 CREATE FUNCTION、PL/pgSQL — SQL过程语言、过程语言和查询语言（SQL）函数。
