ORACLE(!)

Connect sys/change_on_install@Oracle AS SYSDBASET SERVEROUTPUT ONDECLAREMYSAL SCOTT.EMP.SAL%TYPE;CURSOR MYCUR IS SELECT * FROM SCOTT.EMPWHERE SAL > MYSAL;RECORD1 MYCUR%ROWTYPE;BEGINMYSAL:=500;OPEN MYCUR;FETCH MYCUR INTO RECORD1;DBMS_OUTPUT.PUT_LINE('工作证件：'||RECORD1.EMPNO); DBMS_OUTPUT.PUT_LINE('姓名：'||RECORD1.ENAME); DBMS_OUTPUT.PUT_LINE('职业：'||RECORD1.JOB);DBMS_OUTPUT.PUT_LINE('销售额：'||RECORD1.SAL); END;DECLAREabc NUMBER(3) :=0;STR1 V ARCHAR2(20);FLAG BOOLEAN;BEGINCOMMIT;END;Connect sys/change_on_install@Oracle AS SYSDBASET SERVEROUTPUT ONDECLARETYPE RECORD1 IS RECORD(SAL NUMBER,ENAME V ARCHAR2(10)); SRECORD RECORD1;BEGINSELECT SAL,ENAME INTO SRECORDFROM SCOTT.EMPWHERE SAL =800;DBMS_OUTPUT.PUT_LINE(SRECORD.ENAME); END;Connect sys/change_on_install@Oracle AS SYSDBASET SERVEROUTPUT ONDECLAREX1 INTEGER:=10;BEGINIF X1>0THENDBMS_OUTPUT.PUT_LINE('ASDFASDF');END IF;END;SET SERVEROUTPUT ONDECLAREX1 INTEGER:=1;X2 INTEGER:=5;X3 INTEGER:=-5;BEGINIF X1>X2 AND X1<X3THENDBMS_OUTPUT.PUT_LINE('AAAA');ELSIF X2>X3 AND X2<X1THENDBMS_OUTPUT.PUT_LINE('BBBB');ELSEDBMS_OUTPUT.PUT_LINE('CCCC');END IF;END;SET SERVEROUTPUT ONDECLAREX1 INTEGER:=0;X2 INTEGER:=10;X3 INTEGER:=0;BEGINLOOPX1:=X1+2;X3:=X3+1;IF X1=X2 THENEXIT;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('CCCC:'||TO_CHAR(X3)); END;SET SERVEROUTPUT ONDECLAREX1 INTEGER:=0;X2 INTEGER:=10;X3 INTEGER:=0;BEGINWHILE X1<X2 LOOPX1:=X1+2;X3:=X3+1;END LOOP;DBMS_OUTPUT.PUT_LINE('CCCC:'||TO_CHAR(X3)); END;SET SERVEROUTPUT ONDECLAREX1 INTEGER:=0;X2 INTEGER:=10;X3 INTEGER:=0;BEGINFOR I IN 1..5 LOOPX1:=X1+2;END LOOP;DBMS_OUTPUT.PUT_LINE('CCCC:'||TO_CHAR(X1)); END;游标：DECLAREX1 SCOTT.EMP.SAL%TYPE;CURSOR MYCUR IS SELECT * FROM SCOTT.EMP WHERE SAL<X1;BEGINX1:=200;OPEN MYCUR;END;SET SERVEROUTPUT ONDECLAREX1 SCOTT.EMP.SAL%TYPE;CURSOR MYCUR IS SELECT * FROM SCOTT.EMP WHERE SAL<X1;RECORD1 MYCUR%ROWTYPE;BEGINX1:=1000;OPEN MYCUR;FETCH MYCUR INTO RECORD1;DBMS_OUTPUT.PUT_LINE(RECORD1.EMPNO); END;Connect SYS/****@ORACLE AS SYSDBASET SERVEROUTPUT ONDECLAREX1 SCOTT.EMP.SAL%TYPE;CURSOR MYCUR IS SELECT * FROM SCOTT.EMP WHERE SAL<X1;RECORD1 MYCUR%ROWTYPE;BEGINX1:=10;OPEN MYCUR;FETCH MYCUR INTO RECORD1;IF MYCUR%FOUND THENDBMS_OUTPUT.PUT_LINE(RECORD1.EMPNO); ELSEDBMS_OUTPUT.PUT_LINE('没有数据');END IF;END;游标提取循环SET SERVEROUTPUT ONDECLARECURSOR MYCUR IS SELECT SAL FROM SCOTT.EMP WHERE SAL<1000;BEGINFOR I IN MYCUR LOOPDBMS_OUTPUT.PUT_LINE(I.SAL);END LOOP;EXCEPTION WHEN OTHERS THENNULL;END;过程：CREA TE OR REPLACE PROCEDURE PROCEDURE1 AS TEM SCOTT.EMP.SAL%TYPE;BEGINSELECT SAL INTO TEMFROM SCOTT.EMPWHERE EMPNO=7369;DBMS_OUTPUT.PUT_LINE(TO_CHAR(TEM)); END;SET SERVEROUTPUT ONBEGINSYS.PROCEDURE1;END;SET SERVEROUTPUT ONDECLAREA V ARCHAR2(30);B NV ARCHAR2(30):='CORNER? WHA T CORNER?'; BEGINA:=TO_CHAR(B);DBMS_OUTPUT.PUT_LINE(A);END;CREA TE OR REPLACE PROCEDURE PROCEDURE2( TEMPNO IN SCOTT.EMP.EMPNO%TYPE,NAME1 OUT SCOTT.EMP.ENAME%TYPE,JOB1 IN OUT SCOTT.EMP.JOB%TYPE)ASMYJOB SCOTT.EMP.JOB%TYPE;MYNAME SCOTT.EMP.ENAME%TYPE;BEGINSELECT JOB INTO MYJOBFROM SCOTT.EMPWHERE EMPNO=TEMPNO;SELECT ENAME INTO MYNAMEFROM SCOTT.EMPWHERE EMPNO=TEMPNO;NAME1:='姓名'||MYNAME;JOB1:='职业'||MYJOB;END;SET SERVEROUTPUT ONDECLAREMYNO SCOTT.EMP.EMPNO%TYPE;MYNAME1 SCOTT.EMP.ENAME%TYPE;MYJOB1 SCOTT.EMP.JOB%TYPE;BEGINMYNO:=7369;MYNAME1:='';MYJOB1:='';PROCEDURE2(MYNO,MYNAME1,MYJOB1);DBMS_OUTPUT.PUT_LINE(MYNO);DBMS_OUTPUT.PUT_LINE(MYNAME1);DBMS_OUTPUT.PUT_LINE(MYJOB1);END;SET SERVEROUTPUT ONDECLARESALARY_CODE V ARCHAR2(1);INV ALID_SALARY_CODE EXCEPTION; BEGINSALARY_CODE:='X';IF SALARY_CODE NOT IN('A','B','C') THEN RAISE INV ALID_SALARY_CODE;END IF;EXCEPTIONWHEN INV ALID_SALARY_CODE THEN DBMS_OUTPUT.PUT_LINE('无效无效无效'); END;PL/SQL举例：功能设计：数据表设计：create tablespace AAAdatafile 'e:\aaa.dbf'size 20m创建数据表CREA TE TABLE SYS.XSCJ( XH NUMBER(6) NOT NULL, XM V ARCHAR2(9) NOT NULL, XB V ARCHAR2(4) NOT NULL, GS NUMBER(3) NOT NULL, WY NUMBER(3) NOT NULL, ZZ NUMBER(3) NOT NULL, JSJ NUMBER(3) NOT NULL, TY NUMBER(3) NOT NULL, JS NUMBER(3) NOT NULL) TABLESPACE AAA;CREA TE TABLE SYS.RESULTT (XH NUMBER(6) NOT NULL,XM V ARCHAR2(9) NOT NULL,XB V ARCHAR2(4) NOT NULL,GS NUMBER(3) NOT NULL,WY NUMBER(3) NOT NULL,ZZ NUMBER(3) NOT NULL,JSJ NUMBER(3) NOT NULL,TY NUMBER(3) NOT NULL,JS NUMBER(3) NOT NULL,ZCJ NUMBER(3) NOT NULL,XSJB V ARCHAR2(4) NOT NULL)TABLESPACE AAA;录入数据INSERT INTO SYS.XSCJV ALUES(60001,'林冲','男',85,78,86,77,80,83);程序设计（1）过程CREA TE OR REPLACE PROCEDURE SYS.XSCJPROCESS( TEMPGS IN SYS.XSCJ.GS%TYPE,TEMPWY IN SYS.XSCJ.WY%TYPE,TEMPZZ IN SYS.XSCJ.ZZ%TYPE,TEMPJSJ IN SYS.XSCJ.JSJ%TYPE,TEMPTY IN SYS.XSCJ.TY%TYPE,TEMPJS IN SYS.XSCJ.JS%TYPE,TEMPZCJ1 IN SYS.RESULTT.ZCJ%TYPE,TEMPZCJ2 IN SYS.RESULTT.ZCJ%TYPE,TEMPZCJ3 IN SYS.RESULTT.ZCJ%TYPE,TEMPZCJ4 IN SYS.RESULTT.ZCJ%TYPE)AS XSCJRECORD SYS.XSCJ%ROWTYPE;XSZCJ SYS.RESULTT.ZCJ%TYPE;XSCJJB V ARCHAR2(4);CURSOR XSCURSOR IS SELECT * FROM SYS.XSCJ; ERRORMESSAGE EXCEPTION;BEGINOPEN XSCURSOR;IF XSCURSOR%NOTFOUND THENRAISE ERRORMESSAGE;END IF;LOOPFETCH XSCURSOR INTO XSCJRECORD;XSZCJ:=XSCJRECORD.GS+XSCJRECORD.WY+XSCJRECORD.ZZ+XSCJRECORD.JSJ+XS CJRECORD.TY+XSCJRECORD.JS;IF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ1) THENXSCJJB:='优';ELSEIF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ2) THENXSCJJB:='良';ELSEIF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ3) THENXSCJJB:='中';ELSEIF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ4) THENXSCJJB:='及格';ELSE XSCJJB:='差';END IF;EXIT WHEN XSCURSOR%NOTFOUND;INSERT INTO SYS.RESULTTV ALUES(XSCJRECORD.XH,XSCJRECORD.XM,XSCJRECORD.XB,XSCJRECORD.GS,XSC JRECORD.WY,XSCJRECORD.ZZ,XSCJRECORD.JSJ,XSCJRECORD.TY,XSCJRECORD.JS,XSZCJ,XSCJJB); END LOOP;CLOSE XSCURSOR;COMMIT;EXCEPTIONWHEN ERRORMESSAGE THENDBMS_OUTPUT.PUT_LINE(A);END;主程序：SET SERVEROUTPUT ONDECLAREKC1 NUMBER(3);KC2 NUMBER(3);KC3 NUMBER(3);KC4 NUMBER(3);KC5 NUMBER(3);KC6 NUMBER(3);ZF1 NUMBER(3);ZF2 NUMBER(3);ZF3 NUMBER(3);ZF4 NUMBER(3);BEGINKC1:=60;KC2:=60;KC3:=60;KC4:=60;KC5:=60;KC6:=60;ZF1:=510;ZF2:=460;ZF3:=410;ZF4:=360;SYS.XSCJPROCESS(KC1,KC2,KC3,KC4,KC5,KC6,ZF1,ZF2,ZF3,ZF4); END;CREA TE OR REPLACE PROCEDURE SYS.XSCJPROCESS(TEMPGS IN SYS.XSCJ.GS%TYPE,TEMPWY IN SYS.XSCJ.WY%TYPE,TEMPZZ IN SYS.XSCJ.ZZ%TYPE,TEMPJSJ IN SYS.XSCJ.JSJ%TYPE,TEMPTY IN SYS.XSCJ.TY%TYPE,TEMPJS IN SYS.XSCJ.JS%TYPE,TEMPZCJ1 IN SYS.RESULTT.ZCJ%TYPE,TEMPZCJ2 IN SYS.RESULTT.ZCJ%TYPE,TEMPZCJ3 IN SYS.RESULTT.ZCJ%TYPE,TEMPZCJ4 IN SYS.RESULTT.ZCJ%TYPE)ASXSCJRECORD SYS.XSCJ%ROWTYPE;XSZCJ SYS.RESULTT.ZCJ%TYPE;XSCJJB varchar2(4);CURSOR XSCURSOR ISSELECT * FROM SYS.XSCJ;ERRORMESSAGE EXCEPTION;BEGINOPEN XSCURSOR;IF XSCURSOR%NOTFOUND THENRAISE ERRORMESSAGE;END IF;LOOPFETCH XSCURSOR INTO XSCJRECORD;XSZCJ:=XSCJRECORD.GS+XSCJRECORD.WY+XSCJRECORD.ZZ+XSCJRECORD.JSJ+XS CJRECORD.TY+XSCJRECORD.JS;IF (XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ1)THENXSCJJB:='优';ELSIF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ2)THENXSCJJB:='';ELSIF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJgJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ3)THENXSCJJB:='';ELSIF(XSCJRECORD.GS>=TEMPGS ANDXSCJRECORD.WY>=TEMPWY ANDXSCJRECORD.ZZ>=TEMPZZ ANDXSCJRECORD.JSJ>=TEMPJSJ ANDXSCJRECORD.TY>=TEMPTY ANDXSCJRECORD.JS>=TEMPJS ANDXSZCJ>=TEMPZCJ4)THENXSCJJB:='';ELSE XSCJJB:='';END IF;EXIT WHEN XSCURSOR%NOTFOUND;INSERT INTO SYS.RESULTT(XH,XM,XB,GS,WY,ZZ,JSJ,TY,JS,ZCJ,XSJB)V ALUES(XSCJRECORD.XH,XSCJRECORD.XM,XSCJRECORD.XB,XSCJRECORD.GS,XSC JRECORD.WY,XSCJRECORD.ZZ,XSCJRECORD.JSJ,XSCJRECORD.TY,XSCJRECORDJS,XSZCJJB);END LOOP;CLOSE XSCURSOR;COMMIT;EXCEPTIONWHEN ERRORMESSAGE THENDBMS_OUTPUT.PUT.LINE( ) ; END;。