|
|
Posted on 2009-12-09 14:13 咖啡企鹅 阅读(302) 评论(0) 编辑 收藏 所属分类: SQL
 --块（编程）：过程（存储过程）、函数、触发器、包
--块结构：DECLEARR定义（可选）、BEGIN执行（必须）、EXCEPTIONN例外（可选）
--建立测试表
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)
---------------分割线------------------
--建立过程
SQL> CREATE OR REPLACE PROCEDURE tp01 IS
2 BEGIN
3 INSERT INTO tpt VALUES(00001,'T01','p01');
4 END;
5 /
--查看错误
SQL> SHOW ERROR;
No errors for PROCEDURE SCOTT.TP01
--打开页面输出显示功能
SET SERVEROUTPUT ON;
--执行过程
SQL> EXEC tp01;
PL/SQL procedure successfully completed
---------------分割线------------------
--简单块
BEGIN
DBMS_OUTPUT.put_line('hello,oracle');
END;
--带定义块
SQL> DECLARE
2 v_ename VARCHAR2(5);
3 v_empno NUMBER(7);
4 BEGIN
--& 符号提示控制台输入
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
---------------分割线------------------
--函数创建及调用 关键词 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:有把结果注入的味道
Method called
annusal
---------
39996
---------------分割线------------------
--包的建立
SQL> CREATE PACKAGE test_package IS --仅是对包做一个声明
2 PROCEDURE update_sal(uname VARCHAR2,usal NUMBER);
3 FUNCTION annual_income(uname VARCHAR2) RETURN NUMBER;
4 END;
5 /
Package created
--包体的实现
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
--包的调用 相当于java方法的调用
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记录（相当于单行数组）
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 表类型（相当于单列数组）
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
---------------分割线------------------
--游标的建立与使用 引用多行多列数据
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; --转到标签
8 END IF;
9 END LOOP;
10 <<end_loop>> --标签
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关键词 要导入的参数 省却默认
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关键词 要导出的参数 必须用OUTT声明
3 SELECT bname INTO gbname FROM bookcase WHERE bid=gbid;
4 END;
5 /
Procedure created
--用 PACKAGE包 保存多行多列数据
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 --查询分页过程
(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||')';
--以上两种方法均可实现分页
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调用的时候不支持关闭
END;
1//分页查询代码的java调用
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 }
|