oracle添加主键脚本,oracle存储过程 序列 脚本 索引 主键

序列：

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)LOGGINGTABLESPACE INDX ;