我参照书上写个存储过程 但是SQLPLUS不执行 晕啊 大家帮忙看一下 哪里有问题 谢谢了
CREATE or REPLACE PROCEDURE TJ( )
AS DECLARE
maxage INT; 最大年龄
minage INT; 最小年龄
age INT; 临时变量
num INT; 人数
sum INT; 总年龄
cur CURSOR FOR SELECT Sage from dangan;
BEGIN
SELECT max(Sage) INTO maxage from dangan;
SELECT min(Sage) INTO minage From dangan;OPEN cur;
LOOP
FETCH cur INTO age;
num=num+1;
sum=sum+age;
END LOOP;
CLOSE cur;
COMMIT;
END;
CREATE or REPLACE PROCEDURE TJ( )
AS DECLARE
maxage INT; 最大年龄
minage INT; 最小年龄
age INT; 临时变量
num INT; 人数
sum INT; 总年龄
cur CURSOR FOR SELECT Sage from dangan;
BEGIN
SELECT max(Sage) INTO maxage from dangan;
SELECT min(Sage) INTO minage From dangan;OPEN cur;
LOOP
FETCH cur INTO age;
num=num+1;
sum=sum+age;
END LOOP;
CLOSE cur;
COMMIT;
END;
解决方案 »
- 在plsql中create tablesapce时,提示权限不够
- escape wildcard in between and operator
- SQL语句优化
- 如何锁定一个表,在我更新数据期间,不允许其他用户读取其中数据?请高人指点一下,万分感谢!!!
- 如何实现前N行数据的查询???急急?在线等!!!!!
- select * from FOR UPDATE 和 select * from FOR UPDATE NOWAIT 有什么区别?
- 还是关于system和sys登录的权限问题
- win2000下imp/exp出现乱码?
- 嵌套表的存储表问题??
- 我在表中设置了一个自增字段REC_ID,我在插入的时候用了表名.nextval,怎么不对??
- 错误:名称已由现有对象使用
- 非常疑惑~~实在搞不懂这是为什么
cur CURSOR FOR SELECT Sage from dangan;没见过这么写游标的cursor cursor_name is
select sage from dangan
还有你的循环没跳出循环的条件?
看看http://blog.csdn.net/wuchunyu002/archive/2008/04/30/2348174.aspx
DECLARE
CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno = 7788;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor
INTO emp_record;
dbms_output.put_line('名前:'||emp_record.ename||' 職業:'||emp_record.job||' 薪水:'||emp_record.sal);
CLOSE emp_cursor;
END;
-------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
dbms_output.put_line(emp_record.empno||emp_record.ename);
END LOOP;
END;
-------------------------------------------------------------------------------------------------------------
--引数
DECLARE
v_empno NUMBER(5);
v_ename VARCHAR2(10);
CURSOR emp_cursor(p_deptno NUMBER,p_job VARCHAR2) IS
SELECT empno,ename FROM emp WHERE deptno = p_deptno AND job = p_job;
BEGIN
OPEN emp_cursor(10,UPPER('clerk') );
LOOP
FETCH emp_cursor
INTO v_empno,v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
END LOOP;
END;
----------------------------------------------------------------------------------------------------------------
動態SELECT語句
DECLARE
str VARCHAR2(100);
v_ename VARCHAR2(10);
BEGIN
str := 'select ename from scott.emp where empno = 7788';
EXECUTE IMMEDIATE str INTO v_ename;
dbms_output.put_line(v_ename);
END;DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
dbms_output.PUT_LINE(emp_record.ename);
END LOOP;
END;
--------------------------------------------------
BEGIN
FOR emp_record IN
(SELECT ename,sal FROM emp)
LOOP
dbms_output.PUT_LINE(emp_record.ename);
END LOOP;
END;
--------------------------------------------------