plsq笔记l.doc

替换已存在的过程名创建存储过程create (or replace) procedure my_pl isbegin/执行部分/end;/ 带参SQL create procedure sp_test2(spname varchar2,newclassid number)is 2 begin 3 update student set classid=newclassid where studentname=spname; 4 end; 5 /show error查看错误调用过程1、 exec 过程名(参数值1，)2、 call过程名(参数值1，)命名规范：变量：v_变量名；常量：c_常量名；游标：名字_cursor；例外：e_名字；/块结构set serveroutput on（默认为off）declared -定义部分（可选）begin-执行部分（必须）exception-例外处理end;-结束/ 例：begindbms_output.put_line(hello); end;/输出结果：hello当.put_line换成.put是不能输出！？变量命名规则字母、数字、特殊符号 字母开头，不区分大小写；最大30个字符不能有空格例：set serveroutput on;declarev_student varchar2(20);beginselect studentname into v_student from student where xh=&xh;dbms_output.put_line(姓名：|v_student); exception when no_data_found then dbms_output.put_line(查无此人);异常处理end;/ 2012/2/211、函数的创建；create function sp_fun1(spname varchar2)returnnumber is spage number(3);begin select age+1 into spage from student where studentname=spname; return spage;end;2、调用函数 var 变量名 类型；exec 函数名(参数) into：变量名；创建包- 创建包名create package sp_pa is-声明过程procedure update_age(name varchar2,newage number);-声明函数function fun_age(name varchar2) return number;end;创建包体2012/2/22使用标量declarec_tax_rate number(3,2):=0.03;v_ename varchar2(5);v_sal number(7,2);v_tax_sal number(7,2);begin select ename ,sal into v_ename,v_sal from emp where empno=&no; v_tax_sal:=v_sal*c_tax_rate; dbms_output.put_line(姓名：|v_ename|工资：|v_sal|税款：|v_tax_sal);end;表名.列名%type 会引用原表属性解决缓冲区小的问题，防止溢出定义记录类型记录多个变量declaretype emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);sp_record emp_record_type;beginselect ename,sal,job into sp_record from emp where empno=7788;dbms_output.put_line(员工：|sp_|工资|sp_record.salary);end;游标declare -定义游标类型为 sp_emp_cursortype sp_emp_cursor is ref cursor;test_cursor（游标变量） sp_emp_cursor; v_ename emp.ename%type;v_sal emp.sal%type; begin open test_cursor for select ename,sal from emp where deptno=&no; loop fetch test_cursor into v_ename,v_sal;（循环） exit when test_cursor%notfound; dbms_output.put_line(员工|v_ename|工资|v_sal); end loop; close test_cursor; （关闭游标）end; 2012/2/23if语句create or replace procedure pro_if(proName varchar2) isv_sal emp.sal%type;beginselect sal into v_sal from emp where ename=proName;if v_sal2000 thenupdate emp set sal=sal+sal*0.1 where ename=proName;end if;end;if-else语句if v_comm0 thenupdate emp set comm=comm+100 where ename=proName;elseupdate emp set comm=200 where ename=proName;end if;if-elsif-else 注：plsql为elsif ! ! !select job into v_job from emp where empno=proNo; if v_job=presidene then update emp set sal=sal+1000 where empno=proNo; elsif v_job=manager then update emp set sal=sal+500 where empno=proNo; else update emp set sal=sal+200 where empno=proNo; end if;loop 循环create or replace procedure pro_loop(proName varchar2) isv_userid number:=5;begin loop insert into user1 values(v_userid,proName); exit when v_userid=7; v_userid:=v_userid+1; end loop