orcle编程查询.doc

例子：用学号查信息declarev_sname varchar2(8);v_dept test_user.TEST_DEPT%type;beginselect test_name,test_dept into v_sname,v_dept from test_user where test_no=200601010002;dbms_output.PUT_line(v_sname|v_dept);end;改进-灵活declarev_sname varchar2(8);v_dept test_user.TEST_DEPT%type;beginselect test_name,test_dept into v_sname,v_dept from test_user where test_no=&a;dbms_output.PUT_line(v_sname|v_dept);end;任务1：创建PL/SQL块，用来显示“信息工程系”的考生人数信息。declarev_num int;beginselect count(*) into v_num from test_userwhere test_dept=信息工程系;dbms_output.PUT_LINE(信息工程系人数为：|v_num);end;任务1-1：创建PL/SQL块，显示指定系的考生人数信息。方法1：declarev_num int;v_dept test_user.TEST_DEPT%type;beginv_dept:=&所在系;select count(*) into v_num from test_userwhere test_dept=v_dept;dbms_output.PUT_LINE(v_dept|人数为：|v_num);end;方法2：declarev_dept test_user.TEST_DEPT%type;v_num int;beginselect test_dept,count(*) into v_dept,v_numfrom test_userwhere test_dept=&所在系 -此处输入信息要注意类型，字符要有group by test_dept;dbms_output.PUT_LINE(v_dept|有考生人数为：|v_num);end;任务1-2：创建PL/SQL块，显示指定系的考生人数信息。若输入的信息出错，则提示出错。declarev_dept test_user.TEST_DEPT%type;v_num int;beginselect test_dept,count(*) into v_dept,v_numfrom test_userwhere test_dept=&所在系 -此处输入信息要注意类型，字符要有group by test_dept;dbms_output.PUT_LINE(v_dept|有考生人数为：|v_num);exception when no_data_found thendbms_output.PUT_LINE(输入信息有误);end;根据学号查姓名：declareV_sno test_user.TEST_no%type;v_name test_user.TEST_NAME%type;beginv_sno:=&a;select test_name into v_name from test_user where test_no=V_sno;dbms_output.PUT_LINE(该同学的姓名为： |v_name);exceptionwhen no_data_found thendbms_output.PUT_LINE(您输入的信息不存在！);end;CREATE OR REPLACEPROCEDURE P1(p_sno in varchar2) ASv_name test_user.TEST_NAME%type;BEGIN-p_sno:=200601010001;select test_name into v_name from test_user where test_no=p_sno;dbms_output.PUT_LINE(他的名字是:|v_name);END P1;-根据学号编程查名字CREATE OR REPLACEPROCEDURE XH_XM_PROC2( sp_xh IN varchar2, sp_zxf OUT number, sp_zf OUT number) ASBEGINselect zxf,zf into sp_zxf,sp_zf from sum_avg_view where sno=sp_xh;Dbms_output.put_line(总学分| sp_zxf |总成绩 | sp_zf);END XH_XM_PROC2;-根据学号编程查分数create or replace PROCEDURE XH_XM_PROC( p_xh IN VARCHAR2, p_zxf OUT number, p_zf OUT number) ASBEGIN select zxf,zf into p_zxf,p_zf from sum_avg_view where sno=p_xh; dbms_output.PUT_LINE(学号：|p_xh| 总学分:|p_zxf|总成绩:|p_zf);END XH_XM_PROC;CR