crtproc.sql

来自「实例4学院信息管理系统 - RAR 档案文件」· SQL 代码 · 共 66 行

/*
 *  Create Procedure for Interbase STUDENT.GDB
 */

CONNECT "D:\InterBaseDB\STUDENT.GDB"
USER "SYSDBA" PASSWORD "masterkey";

SET TERM ^ ;

DROP PROCEDURE NEWWKS ^


/*
** 返回学号
*/
create procedure newWKS ( Student_name varchar(12) ) /*输入参数*/
RETURNS( Student_id VARCHAR(8) ) /*返回参数*/
AS
DECLARE VARIABLE v_index VARCHAR(4);  /* 变量 */
DECLARE VARIABLE v_grade VARCHAR(4);
DECLARE VARIABLE v_tmp   INTEGER;
DECLARE VARIABLE v_str   VARCHAR(4);
Begin


  /*取学号序号*/
  SELECT pama FROM SysPramTB WHERE pmky = "student" AND cdky = "index" INTO :v_index;

  /*取学号前缀*/
  SELECT pama FROM SysPramTB WHERE pmky = "student" AND cdky = "grade" INTO :v_grade;

  /*学号=前缀+序号*/
  Student_id = v_grade || v_index;

  /*序号+1*/
  v_tmp = v_index;
  v_tmp = v_tmp + 1 ;

  /*如果序号超过4位数则归0*/
  if (v_tmp > 9999) then
    v_tmp =0;

  v_str = v_tmp;

  /*将数字变成4位字符串，不足则前补0*/
  if (v_tmp < 10) then
    v_index = "000" || v_str;
  else if (v_tmp<100 ) then
    v_index = "00" || v_str;
  else if (v_tmp<1000) then
    v_index = "0" || v_str;
  else
    v_index = v_str;

  /*更新系统参数表的学号序号值*/
  UPDATE SysPramTB  SET pama = :v_index WHERE pmky = "student" AND cdky = "index" ;

End ^


SET TERM ; ^


COMMIT WORK ;