《课程4pl-sql》由会员分享，可在线阅读，更多相关《课程4pl-sql（13页珍藏版）》请在金锄头文库上搜索。
1、课程课程 4PL-SQL4PL-SQLPL/SQL 绋嬪簭璁捐Procedural Language/Structured Query Language杩囩璇/缁撴瀯鍖栨煡璇瑷鐢?Oracle 鍏徃鍒跺畾锛屽湪 SQL 鍩虹涓婂鍔犱簡涓浜涜繃绋嬫 殑缁撴瀯锛堝彉閲忋佺被鍨嬨佹潯浠躲佸惊鐜瓑锛夛紝鍙兘璁块棶 Oracle 鏁版嵁搴?begindbms_output.put_line(Hello Everyone!);end;/锛婏紛set serveroutput on-鎵撳紑缁堢锛屾樉绀簆l/sql 鐨勮緭鍑鸿鍙?鎵 杩囩锛? 灏囨暣涓唬鐮佸潡鍙戠粰 plsql 寮曟搸瑙瀽鍒嗙sql 寮曟搸鎵
2、 sql 璇彞锛岃繃绋嬩唬鐮佷篃plsql 鐩稿叧澶勭悊鍣鐞?begindbms_output.put_line();end;/杈撳嚭锛?銆傚叾涓袱杈圭殑灞炰簬鍗曞紩鍙锋嫭璧锋潵锛岄噷闈殑涓釜鎵撳嵃杈撳嚭涓涓?,鍍忚浆涔夊瓧绗?1 銆傚彉閲忕殑澹版槑 variable 锛氬彉閲? 鍩烘湰澹版槑锛?鍙橀噺鍚?constant 鍙橀噺绫诲瀷 not null default value :=value锛婏細甯搁噺蹇呴璧嬪垵鍊笺?锛婏細濡傛灉涓涓瓧娈甸檺瀹歯 ot null 锛岄偅涔堝畠蹇呴鎷湁涓涓垵濮嬪笺? 锛婏細鎵鏈夋病鏈夊垵濮嬪寲鐨勫瓧娈甸兘浼氬垵濮嬪寲涓篘 ULL锛婏細蹇樺姞;杩藉姞鐨勬椂鍊
3、欙細 a ;鐢袱涓?鎵嶈兘鍔犱笂锛婏細 default 0鍚庨潰蹇呴鏈夐粯璁?declare澹版槑num number;name varchar2(10) default larry;salary constant number:=123;beginnum:=12;dbms_output.put_line(num: |num);dbms_output.put_line(num: |name);dbms_output.put_line(num: |salary);end;/2 銆傛暟鎹簱涓暟瀛楃被鍨? 1)number(p)number(p,s)p:绮惧害,浠庡乏寰鍙崇涓涓潪 0 鐨勬暟瀛楃畻璧
4、风殑浣嶆暟s:鍒诲害,灏忔暟鐐瑰悗鐨勪綅鏁?2)%type:琛鍜屾暟鎹簱涓煇寮犺鐨勬煇涓鍒楀叿鏈夌浉鍚岀殑绫诲瀷闄愬埗declarevar number := 10;var1 s_emp.dept_id%type;var2 var%type;beginvar2 := 10;null;end;/3)record 璁板綍鍚岀被鍨嬬殑 record 鍙橀噺鍙互鐩镐簰璧嬪? -record 鏍煎紡-type record_name is record (field1 type1 NOT NULL :=expr1,field2 type2 NOT NULL :=expr2,鈥?fieldn typen
5、NOT NULL :=exprn);濡傛灉涓涓瓧娈甸檺瀹歂 OT NULL, 閭箞瀹冨繀椤绘嫢鏈変竴涓垵濮嬪? 鎵鏈夋病鏈夊垵濮嬪寲鐨勫瓧娈甸兘浼氬垵濮嬪寲涓篘 ULLdeclaretype myrec is record(name varchar(20),last_name s_emp.last_name%type,salary s_emp.salary%type);var myrec;beginselect first_name,last_name,salary-into varinto var.name,var.last_name,var.salaryfrom s_empwhere id
6、=1;dbms_output.put_line(var.name|var.salary);end;/4)%rowtype 锛氫娇鐢凡鏈夌殑琛垨鑰呭彉閲忕殑绫诲瀷鏉畾涔変竴涓猺 ecord 銆?鏍煎紡锛? 鍙橀噺 琛悕%rowtype鍜宺 ecord 鍖哄埆:1)rowtype 浣跨敤鏂逛究銆備絾鏄彉閲忎腑鍒楃殑鍊煎拰琛竴鑷?涓嶇伒娲? 2)rowtype 鍙互鐩存帴澹版槑鍙橀噺,record 鍏堝畾涔夌被鍨?鍐嶅鏄庡彉閲?declarevemp s_emp%rowtype;var vemp%rowtype;beginselect *into varfrom s_empwhere rownum
7、= 1;dbms_output.put_line(var.last_name| |var.salary);end;/5)table绫讳技 java 鈥滄暟缁勨濈粨鏋勭殑涓绉嶇被鍨?浣嗘槸涓嶉渶瑕佸鏄庨暱搴?鑰屼笖涓鑸笉鐢冭檻绱紩瓒婄晫闂浣跨敤鐨勬椂鍊欏彧闇瑕佹敞鎰忓搴斿叧绯诲嵆鍙?鏍煎紡锛? TYPE tabletypes IS TABLE OF type INDEX BY binary_integer;-declaretype vrec is record(id number,name varchar2(20);type vtbl is tableof vrec index by binar
8、y_integer;vdept vtbl;beginselect id,nameinto vdept(100)from s_deptwhere id = 42;dbms_output.put_line(vdept(100).id| |vdept(100).name);end;/-DECLARETYPE Info IS TABLE OF student%rowtype INDEX BY BINARY_INTEGER;v_info Info;BEGINselect * into v_info(100) FROM student where rownum = 1;DBMS_OUTPUT.PUT_LI
9、NE(v_info(101).ID | | v_info(101).NAME);EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( - No Data Found ! - );END;/-table-浣跨敤鍩烘湰绫诲瀷 varchar2 澹版槑涓涓猼 able,浠巗_dept涓嬁鍑轰竴涓猲 ame 鏀惧叆鍏朵腑銆? declaretype vtbl is table of varchar2(20) index by binary_integer;var vtbl;beginselect nameinto var(100)from s_d
10、eptwhere rownum = 1;dbms_output.put_line(var(100);end;/-%rowtype-DROP Table student;CREATE Table student (id numberprimary key,namevarchar2(20),sexnumber(1);insert into student values(100,Tom,1);DECLAREv_Student student%ROWTYPE;BEGINSELECT * INTO v_Student FROM student WHERE rownum = 1;DBMS_OUTPUT.P
11、UT_LINE(v_Student.id);DBMS_OUTPUT.PUT_LINE(v_Student.name);DBMS_OUTPUT.PUT_LINE(v_Student.sex);END;/-DECLARETYPE r_StudentType IS RECORD(v_Idstudent.id%TYPE,v_Namestudent.name%TYPE,v_Sex student.sex%TYPE);v_Student r_StudentType;BEGINSELECT id,name,sex INTO v_Student FROM student WHERE id = 100;DBMS
12、_OUTPUT.PUT_LINE(v_Student.v_Id);DBMS_OUTPUT.PUT_LINE(v_Student.v_Name);DBMS_OUTPUT.PUT_LINE(v_Student.v_Sex);END;/-record-DECLARETYPE t_Rec1Type IS RECORD(Field1 NUMBER,Field2 VARCHAR2(5);TYPE t_Rec2Type IS RECORD(Field1 NUMBER,Field2 VARCHAR2(5);v_Rec1 t_Rec1Type;v_Rec11 t_Rec1Type;v_Rec2 t_Rec2Ty
13、pe;BEGINv_Rec1 := v_Rec11;-(1)鍚岀被鍨嬬殑 rocord 鍙橀噺鍙互鐩镐簰璧嬪?/* This is an illegal assignment which raises PLS-382.expression is of wrong typev_Rec1 := v_Rec2;*/* However, the fields are the same type, so the following are legal assianments.*/v_Rec1.Field1 := v_Rec2.Field1;v_Rec1.Field2 := v_Rec2.Field2;-
14、(2)鎸塺 ecord 鍙橀噺鐨勫瓧娈佃祴鍊?END;/-(3)璁板綍涔熷彲浠祴鍊肩粰 select 璇彞锛岃褰曚腑瀛楁搴旇涓庢煡璇夋嫨缁撴灉鍒楄涓瓧娈电浉鍖归厤-declaretype myRec is record(last_name s_emp.last_name%type,salary number not null :=0);v_emp myRec;beginselect last_name,salaryinto v_empfrom s_empwhere id=20;dbms_output.put_line(v_emp.last_name|v_emp.salary);end;/-鎵鹃敊锛屾瘡棰樺潎鏈変袱涓敊-1. DECLAREv_TempVar NUMBER NOT NULL;BEGINEND;2. DECLAREv_TempVar NUMBER NOT NULL := 2;BEGINv_TempVart := NULL;END;3. DECLAREv_TempVar CONSTANT NUMBER;BEGINEND;4. DECLAREv_FirstName, v_LastName VARCHAR2(20);BEGINEND;-姝鐨?-1. DECLAREv_TempVar NUMBER NOT NU
