Oracle学习资料.

/*PL/SQL编程*/ CREATE DIRECTORY BOOK_TEXT AS 'C:\BOOKS'; CREATE TABLE my_book ( file_descr VARCHAR2(40), book_file BFILE ); INSERT INTO my_book VALUES ('第一章', BFILENAME ('BOOK_TEXT', 'chapter01.txt')); COMMIT; CREATE DIRECTORY IMAGES AS 'C:\IMAGES'; GRANT READ ON DIRECTORY IMAGES TO SCOTT; CREATE TABLE my_diagrams ( chapter_descr VARCHAR2(40), diagram_no INTEGER, diagram BLOB ); DECLARE l_bfile BFILE; l_blob BLOB; BEGIN INSERT INTO my_diagrams (diagram) VALUES (EMPTY_BLOB()) RETURN diagram INTO l_blob; l_bfile := BFILENAME('IMAGES', '\MYIMAGE.JPG'); DBMS_LOB.OPEN(l_bfile, DBMS_LOB.FILE_READONLY); DBMS_LOB.LOADFROMFILE(l_blob, l_bfile, DBMS_LOB.GETLENGTH(l_bfile)); DBMS_LOB.CLOSE(l_bfile); COMMIT; END; / CREATE TABLE my_book_text ( chapter_id NUMBER(3), chapter_descr VARCHAR2(40), chapter_text CLOB ); INSERT INTO my_book_text VALUES(5, '第五章 PL/SQL简介', 'PL/SQL可用于创建存储过程、触发器和程序包等，用来处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑。'); COMMIT; SET SERVEROUTPUT ON DECLARE clob_var CLOB; amount INTEGER; offset INTEGER; output_var VARCHAR2(100); BEGIN SELECT chapter_text INTO clob_var FROM my_book_text WHERE chapter_id=5; amount := 24; -- 要读取的字符数 offset := 1; -- 起始位置 DBMS_LOB.READ (clob_var,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var); END; / SET SERVEROUTPUT ON DECLARE icode VARCHAR2(4); irate NUMBER; BEGIN SELECT itemcode, itemrate INTO icode, irate FROM itemfile WHERE itemcode = 'i203'; IF irate > 200 THEN UPDATE itemfile SET itemrate = itemrate - 200 WHERE itemcode = 'i203'; ELSE UPDATE itemfile SET itemrate = itemrate - 50 WHERE itemcode = 'i203'; END IF; DBMS_OUTPUT.PUT_LINE('itemcode'|| ' ' ||'itemrate'); DBMS_OUTPUT.PUT_LINE(icode || ' ' || irate); END; / BEGIN CASE '&grade' WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('优异'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE ('优秀'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('良好'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE ('一般'); WHEN 'E' THEN DBMS_OUTPUT.PUT_LINE ('较差'); ELSE DBMS_OUTPUT.PUT_LINE ('没有此成绩'); END CASE; END; / DECLARE grade VARCHAR2(2); BEGIN grade := '&grade'; CASE WHEN grade='A' THEN DBMS_OUTPUT.PUT_LINE('优异'); WHEN grade='B' THEN DBMS_OUTPUT.PUT_LINE ('优秀'); WHEN grade='C' THEN DBMS_OUTPUT.PUT_LINE ('良好'); WHEN grade='D' THEN DBMS_OUTPUT.PUT_LINE ('一般'); WHEN grade='E' THEN DBMS_OUTPUT.PUT_LINE ('较差'); ELSE DBMS_OUTPUT.PUT_LINE ('没有此成绩'); END CASE; END; / BEGIN LOOP IF &marks > 60 THEN DBMS_OUTPUT.PUT_LINE('该生已通过'); EXIT; END IF; END LOOP; END; / DECLARE MONTHLY_VALUE NUMBER := 0; DAILY_VALUE NUMBER:=0; BEGIN WHILE monthly_value <= 4000 LOOP monthly_value := daily_value * 31; daily_value := daily_value + 10; dbms_output.put_line('每日销量：' || daily_value); END LOOP; dbms_output.put_line('每月销量：' || monthly_value); END; / BEGIN FOR even_number IN 1..25 LOOP DBMS_OUTPUT.PUT_LINE(even_number*2); END LOOP; END; / DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type; BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = 'i201'; IF qtyhand < relevel THEN GOTO updation; ELSE GOTO quit; END IF; <<updation>> UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = 'i201'; <<quit>> NULL; END; / DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)'; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; END; / SET SERVEROUTPUT ON DECLARE ordernum VARCHAR2(5); BEGIN SELECT orderno INTO ordernum FROM order_master; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('返回多行'); END; / SET SERVEROUTPUT ON DECLARE invalidCATEGORY EXCEPTION; category varchar2(10); BEGIN category := '&Category'; IF category NOT IN ('附件','顶盖','备件') THEN RAISE invalidCATEGORY; ELSE DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category); END IF; EXCEPTION WHEN invalidCATEGORY THEN DBMS_OUTPUT.PUT_LINE('无法识别该类别'); END; / DECLARE rate itemfile.itemrate%TYPE; rate_exception EXCEPTION; BEGIN SELECT NVL(itemrate,0) INTO rate FROM itemfile WHERE itemcode = 'i207'; IF rate = 0 THEN RAISE rate_exception; ELSE DBMS_OUTPUT.PUT_LINE('项费率为：' || rate); END IF; EXCEPTION WHEN rate_exception THEN RAISE_APPLICATION_ERROR(-20001, '未指定项费率'); END; / ----------if...then...elsif...then...else...end if;-------------- declare i number := -10; begin if i>0 then dbms_output.put_line('正数'); elsif i<0 then dbms_output.put_line('负数'); else dbms_output.put_line('零'); end if; end; ----------case when...then...else....end case-------------- declare score number; begin score := &score; case when score>60 then dbms_output.put_line('及格'); when score>80 then dbms_output.put_line('良好'); when score>90 then dbms_output.put_line('优秀'); else dbms_output.put_line('不及格'); end case; end; ---------execute immediate strsql into row_rec using id----------------- declare strsql varchar2(200); emp_rec emp%rowtype; begin execute immediate 'drop table abc'; strsql :='select * from emp where empno=:id'; execute immediate strsql into emp_rec using &id; dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename); end; ----------系统异常-------- declare empname varchar2(200); begin select ename into empname from emp; exception when too_many_rows then dbms_output.put_line('数据太多'); end; -----------自定义异常-------------- declare usersex varchar2(200); myex exception; begin usersex := '&sex'; if usersex<>'男' or usersex <>'女' then raise myex; end if; exception when myex then dbms_output.put_line('错误的性别'); end; -----------自定义异常---------------------- declare usersex varchar2(200); myex exception; begin usersex := '&sex'; if usersex<>'男' or usersex <>'女' then raise myex; end if; exception when myex then raise_application_error(-20001,'错误的性别'); end;