韩顺平玩转oracle--plsql语句.docxVIP

过程的创建createorreplaceprocedure sp_pro2 isbegindeletefrom mytest wherename=hanshun;end;使用包中的函数begindbms_output.put_line(hello,world);end;块的声明declarev_ename varchar2(5);v_sal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=aa;dbms_output.put_line(hello,world||v_ename||:||v_sal); exceptionwhen no_data_found thendbms_output.put_line(enpno is error);end;/过程的创建createprocedure sp_pro4(spName varchar2,newSal number) isbeginupdate emp set sal=newSal where ename=spName;end;函数createfunction sp_fun2(spName varchar2) returnnumberis yearSal number(7,2);beginselect sal*12+nvl(comm,0)*12into yearSal from emp where ename=spName;return yearSal;end;包createpackage sp_package isprocedure update_sal(namevarchar2,newsal number);function annual_income(namevarchar2) returnnumber;end;创建包体createpackagebody sp_package isprocedure update_sal(name1 varchar2,newsal number)isbeginupdate emp set sal=newsal where ename=name1;end;function annual_income(name1 varchar2)returnnumberisannual_salary number;beginselect sal*12+nvl(comm,0) into annual_salary from emp where ename=name1;return annual_salary;end;end;变量和标量v_ename varchar2(10);定义变长字符串v_salnumber(6,2);定义一个小数v_sal2number(6.2):=5.4;定义一个小数并初始化v_hiredatedate;定义一个日期数据类型v_valid boolean not null default false;定义布尔变量，不为空，初值false赋值是：=declare v_tax_rate number(3,2):=00.3;用户名v_enamevarchar2(5);v_ename2emp.ename%type;//数据类型和表中字段的类型保持一致v_sal number(7,2);beginselect ename,sal into v_ename,v_sal from emp whereenpno=no;v_tax_sal：=v_sal*v_tax_rate;dbms_output.put_line(‘姓名是：’||v_ename||’工资：’||v_sal||’);end;复合变量用于存放多个数值的变量，包括pl/sql记录、pl/sql表,类似结构体、数组declaretypeemp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.jop%type) ;sp_record emp_recod_type;beginselect ename,sal,job into sp_record from emp where empno=7788;dbms_output.put_line(‘员工名:’||sp_);end;declaretype sp_table_type is table of emp.enam%type index by binary_integer;-表示下标是正数sp_table sp_table_type;beginselect ename into sp_tabel(0) from emp where empno=7788;