CREATE OR REPLACE

创建 SQLScript 过程或函数时，可以使用 OR REPLACE 选项更改定义的过程或函数（如果已存在）。

语法

CREATE [ OR REPLACE ] FUNCTION  
   [ (  ) ] 
    RETURNS 
   [ LANGUAGE  ]
   [ SQL SECURITY { DEFINER | INVOKER } ] 
   [ DEFAULT SCHEMA  ]
   [  ]
   [ DETERMINISTIC ] 
   [ WITH ENCRYPTION ]
   [ STRUCTURED FILTER CHECK ]
   AS
     { [ HEADER ONLY ON INVALID ] BEGIN 
         
     END [  ]
     | HEADER ONLY }

CREATE [ OR REPLACE ] PROCEDURE  
   [ () ]
   [ LANGUAGE  ]
   [ SQL SECURITY { DEFINER | INVOKER } ]
   [ DEFAULT SCHEMA  ]
   [ READS SQL DATA ] 
   [  ]
   [ DETERMINISTIC ] 
   [ WITH ENCRYPTION ]
   [ AUTOCOMMIT DDL { ON | OFF } ]
   AS 
     { BEGIN [ SEQUENTIAL EXECUTION ]
        
       END
       | HEADER ONLY }

行为

此命令的行为取决于定义的过程或函数的存在。如果过程或函数已存在，则将根据新定义对其进行修改。如果未显式指定属性（例如，只读），则此属性将设置为缺省值。请参阅以下示例。如果过程或函数尚不存在，则该命令的工作方式与 CREATE PROCEDURE 或 CREATE FUNCTION 类似。

与使用后跟 CREATE PROCEDURE 的 DROP PROCEDURE 相比，CREATE OR REPLACE 具有以下优势：

• DROP 和 CREATE 进行两次对象重新验证，而 CREATE OR REPLACE 仅发生一次

• 如果用户删除过程，其权限将丢失，而 CREATE OR REPLACE 将保留它们。

  示例

  示例代码create or replace procedure proc(out o table(a int))default schema system reads sql data deterministic with encryption asbegin o = select 1 as a from dummy;end;call proc(?);-- Returns 1create or replace procedure proc(out o table(a int))language llang asbegin export Void main(Table "o" & o) { Column col = o.getColumn("A"); col.setElement(0z, 2); }end;call proc(?);-- Returns 2-- Note that this procedure is not set to read-only, deterministic, encrypted, or default schema system any more.create or replace procedure proc(out o int) asbegin o = 3;end;-- Returns an error because the signature of the new procedure does not match to that of the predefined procedure 示例代码CREATE OR REPLACE PROCEDURE test1 asbegin select * from dummy;end;call test1; -- new parameterCREATE OR REPLACE PROCEDURE test1 (IN i int) asbegin select :i from dummy; select * from dummy;end;call test1(?); -- default valueCREATE OR REPLACE PROCEDURE test1 (IN i int default 1) asbegin select :i from dummy;end;call test1(); -- change the number of parameter and name of parameterALTER PROCEDURE test1 (j int, k int) asbegin select :j from dummy; select :k from dummy;end;call test1(?, ?); -- change the type of the parameter and name of parameterCREATE OR REPLACE PROCEDURE test1 (t1 TIMESTAMP, t2 TIMESTAMP) asbegin select :t1 from dummy; select :t2 from dummy;end;call test1(?, ?); -- support also ddl command 'ALTER'ALTER PROCEDURE test1 asbegin select * from dummy;end;call test1; -- table typecreate column table tab1 (a INT);create column table tab2 (a INT); CREATE OR REPLACE PROCEDURE test1(out ot1 table(a INT), out ot2 table(a INT)) as begin insert into tab1 values (1); select * from tab1; insert into tab2 values (2); select * from tab2; insert into tab1 values (1); insert into tab2 values (2); ot1 = select * from tab1; ot2 = select * from tab2;end;call test1(?, ?); -- change the number of parameterALTER PROCEDURE test1(out ot1 table(a INT)) as begin insert into tab1 values (1); select * from tab1; insert into tab2 values (2); select * from tab2; insert into tab1 values (1); insert into tab2 values (2); ot1 = select * from tab1;end;call test1(?); -- securityCREATE OR REPLACE PROCEDURE test1(out o table(a int))sql security invoker asbegin o = select 5 as a from dummy;end;call test1(?); -- change securityALTER PROCEDURE test1(out o table(a int))sql security definer asbegin o = select 8 as a from dummy;end;call test1(?); -- result viewALTER PROCEDURE test1(out o table(a int))reads sql data with result view rv1 asbegin o = select 0 as A from dummy;end;call test1(?); -- change result viewCREATE OR REPLACE PROCEDURE test1 (out o table(a int))reads sql data with result view rv2 asbegin o = select 1 as A from dummy;end;call test1(?); -- table functionCREATE TYPE TAB_T1 AS TABLE(a int); CREATE OR REPLACE FUNCTION func1()returns TAB_T1 LANGUAGE SQLSCRIPTas begin return select * from TAB1;end;select * from func1(); CREATE OR REPLACE FUNCTION func1(a int)returns table(a INT) LANGUAGE SQLSCRIPTas begin if a > 4 then return select * from TAB1; else return select * from TAB2; end if;end; select * from func1(1); -- scalar functionCREATE OR REPLACE FUNCTION sfunc_param returns a int asbegin A = 0;end;select sfunc_param() from dummy; CREATE OR REPLACE FUNCTION sfunc_param (x int) returns a int asbegin A = :x;end;select sfunc_param(3) from dummy;