不同数据库管理系统的程序变体

### 不同数据库管理系统的程序变体 在数据库操作中，不同的数据库管理系统（DBMS）在程序扩展方面存在显著差异。本文将详细介绍在不同DBMS中创建简单函数和存储过程的方法，包括PostgreSQL、MySQL、DB2、SQL Server和Oracle。 #### 简单函数：格式化姓名 不同的DBMS在创建格式化姓名的函数时，语法和要求各有不同。 ##### PostgreSQL PostgreSQL存储用户定义函数的语法与Oracle相近，但也有一些区别。例如，在定义返回数据类型时，PostgreSQL使用“returns <数据类型>”，而在实际返回值时使用“return”。函数体被一对相同的标签（通常是$$）包裹，标签内的分号不会被视为语句结束的标志。 ```sql create function full_name(p_fname varchar, p_sname varchar) -- 参数 returns varchar -- 函数返回的数据类型 as $$ -- 函数体包装开始 begin return case -- 返回表达式 when p_fname is null then '' else p_fname || ' ' end || case position('(' in p_sname) when 0 then p_sname else trim(')' from substr(p_sname, position('(' in p_sname) + 1)) || ' ' || trim(substr(p_sname, 1, position('(' in p_sname) - 1)) end; -- 'case'子句结束 end; -- 函数结束 $$ language plpgsql; -- 函数体包装结束 ``` 在PostgreSQL中，参数需要有特殊名称，且不需要指定长度。 ##### MySQL和DB2 DB2和MySQL创建函数的语法相似。与Oracle和PostgreSQL一样，函数中的参数由名称和数据类型定义，但需要指定varchar的长度。返回值的数据类型使用“returns”指定，后面紧跟函数体。 **MySQL示例**： ```sql delimiter $$ create function full_name(p_fname varchar(30), p_sname varchar(30)) -- 参数 returns varchar(61) -- 函数返回的数据类型 return concat(case -- 返回表达式 when p_fname is null then '' else concat(p_fname, ' ') end, case position('(' in p_sname) when 0 then p_sname else concat(trim(')' from substr(p_sname, position('(' in p_sname) + 1)), ' ', trim(substr(p_sname, 1, position('(' in p_sname) - 1))) end); $$ ``` 在MySQL中，如果使用mysql客户端创建函数，需要先更改语句结束的分隔符，创建完成后再改回分号。 **DB2示例**： ```sql create function full_name(p_fname varchar(30), p_sname varchar(30)) returns varchar(61) return case when length(coalesce(p_fname, '')) = 0 then '' else p_fname || ' ' end || case locate('(', p_sname) when 0 then p_sname else trim(')' from substr(p_sname, locate('(', p_sname) + 1)) || ' ' || trim(substr(p_sname, 1, locate('(', p_sname) - 1)) end; / -- 用clpplus时，斜杠将函数发送到服务器 ``` DB2在脚本中不需要更改命令终止符，使用clpplus时，输入斜杠并回车表示函数创建完成。 ##### SQL Server SQL Server的特点是，过程中的参数和变量名必须以@开头。同时，必须指定varchar列的长度，即使是简单的函数也需要使用begin和end。 ```sql create function full_name(@fname varchar(30), @sname varchar(30)) -- 参数 returns varchar(61) -- 函数返回的数据类型 begin return case when @fname is null then '' else @fname + ' ' end + case charindex('(', @sname, 1) when 0 then @sname else replace(substring(@sname, 1 + charindex('(', @sname , 1), len(@sname)), ')', ' ') + rtrim(substring(@sname, 1, charindex('(', @sname, 1) - 1)) end; end; ``` 在SQL Server中，参数和变量名以@开头，确保在SQL语句中安全使用。同时，需要指定传入参数和返回值的长度。 #### 简单存储过程：注册电影 不同的DBMS在创建存储电影注册信息的过程时，也有各自的特点。 ##### Oracle 在Oracle中，存储过程需要声明变量来存储相关信息。例如，使用“select ... into ...”语句将查询结果存储到变量中。 ```sql create procedure movie_registration (p_title varchar2, p_country_name varchar2, p_year number, p_director_fn varchar2, p_director_sn varchar2, p_actor1_fn varchar2, p_actor1_sn varchar2, p_actor2_fn varchar2, p_actor2_sn varchar2) as n_movieid number; -- 变量声明 n_people number; begin insert into movies(movieid, title, country, year_released) -- 插入电影信息 select movies_seq.nextval, p_title, country_code, p_year from countries where country_name = p_country_name; if sql%rowcount = 0 -- 检查是否插入成功 then raise_application_error(-20000, 'country not found in table COUNTRIES'); end if; n_movieid := movies_seq.currval; -- 获取电影标识符 select count(surname) -- 统计要插入的人员数量 into n_people from (select p_director_sn as surname from dual union all ```