Oracle 数据库基本概念理解
- /*===========================================================| PL/SQL编程============================================================*/
- --声明变量DECLARE V_NAME VARCHAR2(20); --一般变量V_AGE NUMBER(2);
- v_id SUN.TUSER.USERID % TYPE: =14; --属性变量v_user SUN.TUSER % ROWTYPE; --行属性变量BEGIN--赋常量值V_NAME: ='Tom';
- V_AGE: =20;
- DBMS_OUTPUT.put_line('姓名:' || V_NAME || '年龄:' || V_AGE); --SELECT * FROM SUN.TUSER; --通过SELECT INTO给变量赋值SELECT USERNAME,
- USERID INTO V_NAME,
- V_AGE FROM SUN.TUSER; --WHERE USERID = v_id;
- DBMS_OUTPUT.put_line('姓名:' || V_NAME || '年龄:' || V_AGE); --给行属性变量赋值select * into v_user from sun.tuser where userid = v_id;
- dbms_output.put_line('----------------------------');
- dbms_output.put_line(v_user.username);
- dbms_output.put_line('----------------------------');
- EXCEPTION--异常处理WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('用户编号不正确!');
- WHEN OTHERS THEN DBMS_OUTPUT.put_line('错误:' || SQLERRM);
- END; --循环
- /*===========================================================| 循环语句============================================================*/
- declare v_count number: =1; --声明变量并赋值v_sum number: =0;
- begin
- /*loop --loop循环 v_sum:=v_sum+v_count; v_count:=v_count+1; exit when v_count>10; end loop; */
- /* while v_count<=10 loop --while 循环 v_sum:=v_sum+v_count; v_count:=v_count+1; end loop;*/
- for i in 1..10 loop--
- for循环v_sum: =v_sum + i;
- end loop;
- dbms_output.put_line(v_sum);
- end;
- /*===========================================================| 游标============================================================*/
- DECLARE--声明游标CURSOR USER_CUR IS SELECT USERID,
- USERNAME FROM SUN.TUSER;
- V_ID SUN.TUSER.USERID % TYPE;
- V_NAME SUN.TUSER.USERNAME % TYPE;
- BEGIN--第一种使用游标方式
- /* OPEN USER_CUR;--打开游标 LOOP --遍历游标 FETCH USER_CUR INTO V_ID,V_NAME; EXIT WHEN USER_CUR%NOTFOUND; DBMS_OUTPUT.put_line('第'||USER_CUR%ROWCOUNT||'个学生:'||V_ID||' '||V_NAME); END LOOP; close user_cur; --关闭游标 */
- --第二种使用方式
- for遍历游标这种方式不用先打开游标
- for v_user in USER_CUR LOOP DBMS_OUTPUT.put_line('第' || USER_CUR % ROWCOUNT || '个学生:' || v_user.USERID || ' ' || V_USER.USERNAME);
- END LOOP;
- END;
- /*===========================================================| 存储过程============================================================*/
- --创建存储过程CREATE or replace PROCEDURE INSERT_USER(V_USERID NUMBER, V_NAME VARCHAR2, V_PWD VARCHAR2) IS BEGIN INSERT INTO SUN.TUSER(USERID, USERNAME, PWD) VALUES(V_USERID, V_NAME, V_PWD);
- END;
- /*===========================================================| 存储过程调用 ============================================================*/
- --存储过程调用begin--按位置调用--INSERT_USER(14, '111', '111'); --按名称INSERT_USER(V_USERID = >15, V_PWD = >'222', V_NAME = >'222');
- end; --符合规则的存储过程CREATE PROCEDURE INSERT_USER2(V_USERID SUN.TUSER.USERID % TYPE, --使用属性变量V_NAME SUN.TUSER.USERNAME % TYPE, V_PWD SUN.TUSER.PWD % TYPE, ON_FLAG OUT NUMBER, --输出参数ON_MSG OUT VARCHAR2) IS BEGIN ON_FLAG: =1;
- ON_MSG: ='添加成功!';
- INSERT INTO SUN.TUSER(USERID, USERNAME, PWD) VALUES(V_USERID, V_NAME, V_PWD);
- EXCEPTION--异常处理WHEN OTHERS THEN ON_FLAG: =SQLCODE; --异常代码ON_MSG: =SQLERRM; --异常信息输出变量rollback;
- END; --调用DECLARE V_FLAG NUMBER;
- V_MSG VARCHAR2(100);
- BEGIN INSERT_USER2(16, '555', '555', V_FLAG, V_MSG);
- DBMS_OUTPUT.put_line(V_FLAG || V_MSG); --select * from sun.tuser;
- END;
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: