SQL> CREATE OR REPLACE FUNCTION CHECK_ID
2 (departmentid IN char(3))
3 RETURNS number
4 AS
5 num number;
6 BEGIN
7 IF EXISTS(SELECT departmentID from departments
8 WHERE DepartmentID=departmentid)THEN
9 Num:=0;
10 ELSE
11 Num:=-1;
12 END IF
13 RETURN Num;
14 END CHECK_ID;
15 /警告: 创建的函数带有编译错误。
SQL> show error
FUNCTION CHECK_ID 出现错误:LINE/COL ERROR
-------- -------------------------------------------------------------
2/22 PLS-00103: 出现符号 "("在需要下列之一时:
:= ) , default varying
character large
符号 ":=" 被替换为 "(" 后继续。3/1 PLS-00103: 出现符号 "RETURNS"在需要下列之一时:
return
符号 "return在 "RETURNS" 继续之前已插入。13/1 PLS-00103: 出现符号 "RETURN"在需要下列之一时:
;LINE/COL ERROR
-------- -------------------------------------------------------------14/13 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin function
package pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor在下基础薄弱!各路高手不要见笑!此题是这样的:
给定一个departmentid值,查询该值在departments表中是否存在,若存在返回0,否则返回-1;
2 (departmentid IN char(3))
3 RETURNS number
4 AS
5 num number;
6 BEGIN
7 IF EXISTS(SELECT departmentID from departments
8 WHERE DepartmentID=departmentid)THEN
9 Num:=0;
10 ELSE
11 Num:=-1;
12 END IF
13 RETURN Num;
14 END CHECK_ID;
15 /警告: 创建的函数带有编译错误。
SQL> show error
FUNCTION CHECK_ID 出现错误:LINE/COL ERROR
-------- -------------------------------------------------------------
2/22 PLS-00103: 出现符号 "("在需要下列之一时:
:= ) , default varying
character large
符号 ":=" 被替换为 "(" 后继续。3/1 PLS-00103: 出现符号 "RETURNS"在需要下列之一时:
return
符号 "return在 "RETURNS" 继续之前已插入。13/1 PLS-00103: 出现符号 "RETURN"在需要下列之一时:
;LINE/COL ERROR
-------- -------------------------------------------------------------14/13 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin function
package pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor在下基础薄弱!各路高手不要见笑!此题是这样的:
给定一个departmentid值,查询该值在departments表中是否存在,若存在返回0,否则返回-1;
--oracle 没有if exists
CREATE OR REPLACE FUNCTION CHECK_ID
(v_departmentid IN char(3)) --变量的名字不要跟字段一样
RETURNS number
AS
num number;
flag number;
BEGIN
SELECT count(*) into flag from departments WHERE DepartmentID=v_departmentid;
if flag>=1 then
num:=0;
ELSE
num:=-1;
END IF
RETURN num;
END CHECK_ID;
2 (v_departmentid IN char(3))
3 RETURNS number
4 AS
5 num number;
6 flag number;
7 BEGIN
8 SELECT count(*) into flag from departments WHERE DepartmentID=v_depa
ntid;
9 if flag>=1 then
10 num:=0;
11 ELSE
12 num:=-1;
13 END IF;
14 RETURN num;
15 END CHECK_ID;
16 /警告: 创建的函数带有编译错误。SQL> show error
FUNCTION CHECK_ID 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
2/26 PLS-00103: 出现符号 "("在需要下列之一时:
:= . ) , @ % default
character
符号 ":=" 被替换为 "(" 后继续。3/1 PLS-00103: 出现符号 "RETURNS"在需要下列之一时:
return
符号 "return在 "RETURNS" 继续之前已插入。
还是无法通过啊!麻烦您在看看
CREATE OR REPLACE FUNCTION CHECK_ID
(v_departmentid IN char(3)) --变量的名字不要跟字段一样
RETURN number --这里不是RETURNS
AS
num number;
flag number;
BEGIN
SELECT count(*) into flag from departments WHERE DepartmentID=v_departmentid;
if flag>=1 then
num:=0;
ELSE
num:=-1;
END IF
RETURN num;
END CHECK_ID;
CREATE OR REPLACE FUNCTION CHECK_ID(departmentid in varchar2)
RETURN number
AS
num number;
v_sign number;
BEGIN
select 1 into v_sign
from departments
where DepartmentID=departmentid;
IF v_sign=1 THEN
num:=0;
ELSE
num:=-1;
END IF;
RETURN num;
END CHECK_ID;
(v_departmentid IN char) --char后面不要带括号
RETURN number --这里不是RETURNS
AS
num number;
flag number;
BEGIN
SELECT count(*) into flag from products WHERE product_id=v_departmentid;
if flag>=1 then
num:=0;
ELSE
num:=-1;
END IF;-----------这个要加分号
RETURN num;
END CHECK_ID;