Compatible with Oracle functions and stored procedures

1. Objective

  • This document is intended to be compatible with the syntax of Oracle PLSQL functions and stored procedures, which we call PLISQL in IvorySQL.

2. Function description

FUNCTION

THE FUNCTION SYNTAX SUPPORTS EDITIONABLE/NONEDITIONABLE

THE FUNCTION syntax supports the RETURN, IS keywords, and does not specify language

THE FUNCTION syntax functions have no arguments, and the function name does not follow ()

The maximum number of CREATE FUNCTION parameters is 32767

THE CREATE FUNCTION in END; End with / in psql

THE CREATE FUNCTION syntax variable declaration is not preceded by the DECLARE keyword

THE CREATE FUNCTION SYNTAX SUPPORTS THE OUT PARAMETER NOCOPY

THE CREATE FUNCTION syntax supports sharing_clause

THE CREATE FUNCTION syntax supports invoker_rights_clause, and the default permission is changed to DR (DEFINER)

THE CREATE FUNCTION syntax supports ACCESSIBLE BY

THE CREATE FUNCTION SYNTAX SUPPORTS DEFAULT COLLATION

THE CREATE FUNCTION syntax supports result_cache_clause

THE CREATE FUNCTION syntax supports aggregate_clause

THE CREATE FUNCTION syntax supports pipelined_clause

THE CREATE FUNCTION syntax supports sql_macro_clause

ALTER FUNCTION syntax

Functions and stored procedure-related views
Stored procedures

THE CREATE PROCEDURE SYNTAX SUPPORTS EDITIONABLE/NONEDITIONABLE

THE CREATE PROCEDURE syntax function has no arguments, no () after the function name

The maximum number of CREATE PROCEDURE parameters is 32767

THE CREATE PROCEDURE in END; End with / in psql

THE CREATE PROCEDURE syntax supports sharing_clause

THE CREATE PROCEDURE SYNTAX SUPPORTS DEFAULT COLLATION

THE CREATE PROCEDURE syntax supports invoker_rights_clause

THE CREATE PROCEDURE syntax supports ACCESSIBLE BY

ALTER PROCEDURE syntax

Stored procedures have no parameters, and invocation support is not carried out with ()

Stored procedure calls support EXEC

When calling a stored procedure in PL/SQL, you can omit CALL and use the stored procedure name directly

Both annotation methods are supported — and /**/

3. Test cases

declare
i integer := 10;
begin
 raise notice '%', i;
 raise notice '%', main.i;
end;
/
NOTICE:  10
NOTICE:  10
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE grade
    WHEN 'A' THEN raise notice 'Excellent';
    WHEN 'B' THEN raise notice 'Very Good';
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    raise notice 'No such grade';
END;
/
NOTICE:  Very Good