|
|
Posted on 2009-12-09 14:13 咖啡企鵝 閱讀(316) 評(píng)論(0) 編輯 收藏 所屬分類: SQL
 --塊（編程）：過(guò)程（存儲(chǔ)過(guò)程）、函數(shù)、觸發(fā)器、包
--塊結(jié)構(gòu)：DECLEARR定義（可選）、BEGIN執(zhí)行（必須）、EXCEPTIONN例外（可選）
--建立測(cè)試表
SQL> CREATE TABLE TPT (
2 tid NUMBER(6) PRIMARY KEY,
3 name CHAR(8) NOT NULL UNIQUE,
4 code CHAR(8));
SQL> DESC empx;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2)
---------------分割線------------------
--建立過(guò)程
SQL> CREATE OR REPLACE PROCEDURE tp01 IS
2 BEGIN
3 INSERT INTO tpt VALUES(00001,'T01','p01');
4 END;
5 /
--查看錯(cuò)誤
SQL> SHOW ERROR;
No errors for PROCEDURE SCOTT.TP01
--打開(kāi)頁(yè)面輸出顯示功能
SET SERVEROUTPUT ON;
--執(zhí)行過(guò)程
SQL> EXEC tp01;
PL/SQL procedure successfully completed
---------------分割線------------------
--簡(jiǎn)單塊
BEGIN
DBMS_OUTPUT.put_line('hello,oracle');
END;
--帶定義塊
SQL> DECLARE
2 v_ename VARCHAR2(5);
3 v_empno NUMBER(7);
4 BEGIN
--& 符號(hào)提示控制臺(tái)輸入
5 SELECT ename,empno INTO v_ename,v_empno FROM emp WHERE empno=&NO;
6 DBMS_OUTPUT.PUT_LINE('ename is:'||v_ename||';and empno is:'||v_empno);
7 EXCEPTION --聲明異常
8 WHEN no_data_found THEN
9 DBMS_OUTPUT.PUT_LINE('no this empno');--異常處理
10 END;
11 /
no this empno
PL/SQL procedure successfully completed
ename is:SMITH
PL/SQL procedure successfully completed
---------------分割線------------------
--函數(shù)創(chuàng)建及調(diào)用 關(guān)鍵詞 FUNCTION
SQL> CREATE FUNCTION tf01(tfname VARCHAR2) RETURN
2 NUMBER IS annusal NUMBER(7,2);
3 BEGIN
4 SELECT (sal+nvl(comm,0))*12 INTO annusal FROM empx WHERE ename=tfname;
5 RETURN annusal;
6 END;
7 /
Function created
SQL> VAR annusal NUMBER;
SQL> CALL tf01('SCOTT') INTO:annusal; --INTO:有把結(jié)果注入的味道
Method called
annusal
---------
39996
---------------分割線------------------
--包的建立
SQL> CREATE PACKAGE test_package IS --僅是對(duì)包做一個(gè)聲明
2 PROCEDURE update_sal(uname VARCHAR2,usal NUMBER);
3 FUNCTION annual_income(uname VARCHAR2) RETURN NUMBER;
4 END;
5 /
Package created
--包體的實(shí)現(xiàn)
SQL> CREATE OR REPLACE PACKAGE BODY test_package IS
2 PROCEDURE update_sal(uname VARCHAR2,usal NUMBER) IS
3 BEGIN
4 UPDATE empx SET sal=usal WHERE ename=uname;
5 END;
6 FUNCTION annual_income(uname VARCHAR2) RETURN NUMBER
7 IS annusal NUMBER;
8 BEGIN
9 SELECT (sal+nvl(comm,0))*12 INTO annusal FROM empx WHERE ename=uname;
10 RETURN annusal;
11 END;
12 END;
13 /
Package body created
--包的調(diào)用 相當(dāng)于java方法的調(diào)用
SQL> CALL test_package.update_sal('SCOTT',3500);
Method called
SQL> var annusal NUMBER;
SQL> CALL test_package.annual_income('SCOTT') INTO:annusal;
Method called
annusal
---------
42000
---------------分割線------------------
--PL/SQL記錄（相當(dāng)于單行數(shù)組）
SQL> DECLARE
2 TYPE empx_tp1 IS RECORD(tname empx.ename%TYPE,tsal empx.sal%TYPE,tjob empx.job%
TYPE);
3 tm_record empx_tp1;
4 BEGIN
5 SELECT ename,sal,job INTO tm_record FROM empx WHERE empno=7788;
6 dbms_output.put_line('name:'||tm_record.tname);
7 END;
8 /
name:SCOTT
PL/SQL procedure successfully completed
--PL/SQL 表類型（相當(dāng)于單列數(shù)組）
SQL> DECLARE
2 TYPE test_table IS TABLE OF empx.ename%TYPE INDEX BY BINARY_INTEGER;
3 tm_name test_table;
4 BEGIN
5 SELECT ename INTO tm_name(0) FROM empx WHERE empno=7788;
6 dbms_output.put_line('name:'||tm_name(0));
7 END;
8 /
name:SCOTT
PL/SQL procedure successfully completed
---------------分割線------------------
--游標(biāo)的建立與使用 引用多行多列數(shù)據(jù)
SQL> DECLARE
2 TYPE test_cursor IS REF CURSOR;
3 tc test_cursor;
4 v_name empx.ename%type;
5 v_sal empx.sal%type;
6 BEGIN
7 OPEN tc FOR SELECT ename,sal FROM empx WHERE deptno=&dno;
8 LOOP
9 FETCH tc INTO v_name,v_sal;
10 EXIT WHEN tc%NOTFOUND; --tc%NOTFOUND tc取到空
11 dbms_output.put_line('name:'||v_name ||',sal:'|| v_sal);
12 END LOOP;
13 END;
14 /
name:JONES,sal:2975
name:FORD,sal:3000
name:SMITH,sal:1200
name:SCOTT,sal:3500
name:ADAMS,sal:1100
PL/SQL procedure successfully completed
---------------分割線------------------
--IF、WHILEE及GOTO
SQL> CREATE OR REPLACE PROCEDURE tp06 IS
2 v_num NUMBER:=1;
3 BEGIN
4 WHILE v_num<=10 LOOP
5 dbms_output.put_line('The Number is '||v_num);
6 v_num:=v_num+1;
7 IF v_num>8 THEN GOTO end_loop; --轉(zhuǎn)到標(biāo)簽
8 END IF;
9 END LOOP;
10 <<end_loop>> --標(biāo)簽
11 dbms_output.put_line('End.');
12 END;
13 /
Procedure created
SQL> exec tp06;
The Number is 1
The Number is 2
The Number is 3
The Number is 4
The Number is 5
The Number is 6
The Number is 7
The Number is 8
End.
PL/SQL procedure successfully completed
---------------分割線------------------
SQL> CREATE TABLE bookcase(
2 bid number,
3 bname varchar2(20),
4 author varchar2(20),
5 price number(5,2),
6 publisher varchar2(20)
7 );
Table created
SQL>
SQL> CREATE OR REPLACE PROCEDURE savebook(bid IN NUMBER,bname IN VARCHAR2,author IN
VARCHAR2,price IN NUMBER,publisher IN VARCHAR2) IS --IN關(guān)鍵詞 要導(dǎo)入的參數(shù) 省卻默認(rèn)
2 BEGIN
3 INSERT INTO bookcase VALUES(bid,bname,author,price,publisher);
4 END;
5 /
Procedure created
SQL> CREATE OR REPLACE PROCEDURE getBnameByBid(gbid IN NUMBER,gbname OUT VARCHAR2) IS
2 BEGIN --OUT關(guān)鍵詞 要導(dǎo)出的參數(shù) 必須用OUTT聲明
3 SELECT bname INTO gbname FROM bookcase WHERE bid=gbid;
4 END;
5 /
Procedure created
--用 PACKAGE包 保存多行多列數(shù)據(jù)
SQL> CREATE OR REPLACE PACKAGE quesult AS TYPE result_cursor IS REF CURSOR;
2 END quesult;
3 /
Package created
SQL> CREATE OR REPLACE PROCEDURE tp08(dno IN NUMBER,rc OUT quesult.result_cursor) IS
2 BEGIN
3 OPEN rc FOR SELECT * FROM empx WHERE deptno=dno;
4 END;
5 /
Procedure created
CREATE OR REPLACE PROCEDURE queble --查詢分頁(yè)過(guò)程
(tame IN VARCHAR2,
psize IN NUMBER,
cno IN NUMBER,
rcount OUT NUMBER,
pcount OUT NUMBER,
rc OUT quesult.result_cursor) IS
v_pd NUMBER(5):= psize*(cno-1)+1;
v_pu NUMBER(5):= psize*cno;
v_sql VARCHAR(500);
BEGIN
--v_sql:='SELECT * FROM (SELECT t.*,ROWNUM rn FROM (SELECT * FROM '||tame||') t WHERE ROWNUM<='||v_pu||') WHERE rn>='||v_pd;
v_sql:='SELECT * FROM '||tame||' WHERE ROWID IN (SELECT B.rid FROM (SELECT ROWNUM rn,A.rid FROM (SELECT ROWID rid FROM '||tame||') A WHERE ROWNUM<='||v_pu||') B WHERE B.rn>='||v_pd||')';
--以上兩種方法均可實(shí)現(xiàn)分頁(yè)
OPEN rc FOR v_sql;
v_sql:='SELECT COUNT(*) FROM '||tame;
EXECUTE IMMEDIATE v_sql INTO rcount;
v_sql:='SELECT CEIL(COUNT(*)/'|| psize ||')FROM '||tame;
EXECUTE IMMEDIATE v_sql INTO pcount;
--CLOSE qs; --java調(diào)用的時(shí)候不支持關(guān)閉
END;
1//分頁(yè)查詢代碼的java調(diào)用
2  public void fenye(String tame, int ps, int cp)  {
3 Connection conn = null;
4 CallableStatement cs = null;
5 ResultSet rs = null;
6  try {
7 conn = OracleUtil.getConnection();
8 String sql = "{call queble(?,?,?,?,?,?)}";
9 cs = conn.prepareCall(sql);
10 cs.setString(1, tame);
11 cs.setInt(2, ps);
12 cs.setInt(3, cp);
13 cs.registerOutParameter(4, OracleTypes.NUMBER);
14 cs.registerOutParameter(5, OracleTypes.NUMBER);
15 cs.registerOutParameter(6, OracleTypes.CURSOR);
16 cs.execute();
17 rs = (ResultSet) cs.getObject(6);
18 while(rs.next()){
19 System.out.printf("rc:%5d,pc:%5s\t",cs.getInt(4),cs.getInt(5));
20 System.out.printf("name:%10s,\tjob:%10s,\tsal:%6.2f\n", rs.getString(2), rs.getString(3), rs.getFloat(6));
21 }
22 } catch (Exception e) {
23 try {
24 conn.rollback();
25 } catch (SQLException se) {
26 se.printStackTrace();
27 }
28 e.printStackTrace();
29 } finally {
30 colse(rs,null,cs,conn);
31 }
32 }
|