序列:
CREATE SEQUENCE SEQ_a
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
增删改查:
PROCEDURE a_insert ( --a
p_name IN VARCHAR2,
p_cur OUT aref,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
);
PROCEDURE a_update ( --a
p_id IN NUMBER, --主键值
p_name IN VARCHAR2,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
);
PROCEDURE a_getbyid ( --a
p_id IN NUMBER ,--主键值
p_cur OUT aref,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
);
PROCEDURE a_getbyname ( --a
p_name IN VARCHAR2 ,
p_pagesize IN NUMBER,
p_pageindex IN NUMBER,
p_count OUT aref, --返回列表总个数
p_cur OUT aref, --记录集合
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
);
PROCEDURE a_deletebyid ( --a
p_id IN NUMBER ,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
);
PROCEDURE a_insert ( --a
p_name IN VARCHAR2,
p_cur OUT aref,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
)
IS
t_id NUMBER;
BEGIN
--初值设置
p_err_common_code := 0;
p_err_logic_code := 0;
p_err_code := 0;
p_err_msg := '';
SELECT SEQ_A.NEXTVAL
INTO t_id
FROM DUAL;
INSERT INTO a
(id , name )
VALUES
(t_id , p_name );
--返回主健值
OPEN p_cur FOR
SELECT t_id FROM dual;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
p_err_code := SQLCODE;
p_err_msg := SQLERRM;
IF p_cur%ISOPEN THEN
CLOSE p_cur;
END IF;
END a_insert;
PROCEDURE a_update ( --a
p_id IN NUMBER,
p_name IN VARCHAR2,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
)
IS
BEGIN
--初值设置
p_err_common_code := 0;
p_err_logic_code := 0;
p_err_code := 0;
p_err_msg := '';
UPDATE a SET
name = nvl( p_name,name)
WHERE id = p_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
p_err_code := SQLCODE;
p_err_msg := SQLERRM;
END a_update;
PROCEDURE a_getbyid ( --a
p_id IN NUMBER ,
p_cur OUT aref,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
)
IS
BEGIN
--初值设置
p_err_common_code := 0;
p_err_logic_code := 0;
p_err_code := 0;
p_err_msg := '';
OPEN p_cur FOR
SELECT * FROM a WHERE id = p_id;
EXCEPTION
WHEN OTHERS
THEN
p_err_code := SQLCODE;
p_err_msg := SQLERRM;
IF p_cur%ISOPEN
THEN
CLOSE p_cur;
END IF;
END a_getbyid;
PROCEDURE a_getbyname ( --a
p_name IN VARCHAR2 ,
p_pagesize IN NUMBER,
p_pageindex IN NUMBER,
p_count OUT aref, --返回列表总个数
p_cur OUT aref, --记录集合
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
)
IS
BEGIN
--初值设置
p_err_common_code := 0;
p_err_logic_code := 0;
p_err_code := 0;
p_err_msg := '';
OPEN p_count FOR
SELECT COUNT(*) AS COUNT FROM a WHERE name = p_name;
IF p_pagesize <= 0 OR p_pageindex < 0 THEN
OPEN p_cur FOR
SELECT *
FROM a WHERE name = p_name /* ORDER BY id DESC */;
ELSE
OPEN p_cur FOR
SELECT * FROM (
SELECT ROWNUM AS tempid , t1.* FROM
(
SELECT *
FROM a WHERE name = p_name /* ORDER BY id DESC */)t1
)
WHERE tempid > p_pageindex * p_pagesize AND tempid <= ( p_pageindex + 1 ) * p_pagesize ;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_err_code := SQLCODE;
p_err_msg := SQLERRM;
IF p_count%ISOPEN
THEN
CLOSE p_count;
END IF;
IF p_cur%ISOPEN
THEN
CLOSE p_cur;
END IF;
END a_getbyname;
PROCEDURE a_deletebyid ( --a
p_id IN NUMBER ,
p_err_common_code OUT NUMBER,
p_err_logic_code OUT NUMBER,
p_err_code OUT NUMBER,
p_err_msg OUT VARCHAR2
)
IS
BEGIN
--初值设置
p_err_common_code := 0;
p_err_logic_code := 0;
p_err_code := 0;
p_err_msg := '';
DELETE a WHERE id = p_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
p_err_code := SQLCODE;
p_err_msg := SQLERRM;
END a_deletebyid;
脚本:
DECLARE
t_count NUMBER;
BEGIN
FOR r IN (SELECT 'a' AS a
FROM DUAL)
LOOP
SELECT 1
INTO t_count
FROM DUAL;
DBMS_OUTPUT.put_line (r.a);
DBMS_OUTPUT.put_line (t_count);
END LOOP;
END;
索引主键:
ALTER TABLE a ADD CONSTRAINT pk_a PRIMARY KEY (id)USING INDEX TABLESPACE indx ;
CREATE [UNIQUE] INDEX u_a_NAME ON a
(name)
LOGGING
TABLESPACE INDX ;