ORACLE中建存储过程

--创建测试表
CREATE TABLE XIAOYAN
(
ID NUMBER PRIMARY KEY,
USERNAME VARCHAR2(24),
AGE VARCHAR2(3),
ADDRESS VARCHAR2(1024),
OTHER VARCHAR2(1024)
);

COMMENT ON TABLE XIAOYAN
IS 'XIAOYAN表';
COMMENT ON COLUMN XIAOYAN.ID
IS '主键ID';
COMMENT ON COLUMN XIAOYAN.USERNAME
IS '用户名';
COMMENT ON COLUMN XIAOYAN.AGE
IS '年龄';
COMMENT ON COLUMN XIAOYAN.ADDRESS
IS '地址';
COMMENT ON COLUMN XIAOYAN.OTHER
IS '其它';
--构造数据
INSERT INTO XIAOYAN(ID,USERNAME,AGE,ADDRESS,OTHER) VALUES (1, 'XIAOYAN', '24', 'CHINA', 'SUPERMAN');
INSERT INTO XIAOYAN(ID,USERNAME,AGE,ADDRESS,OTHER) VALUES (2, NULL, '24', 'CHINA', 'SUPERMAN');

--创建测试存储过程
CREATE OR REPLACE PROCEDURE PRO_XIAOYAN
(
T_ID IN NUMBER,
T_NAME OUT VARCHAR2
)
IS
V_USERNAME VARCHAR2(1024);
V_COUNT NUMBER(8);
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM XIAOYAN;
DBMS_OUTPUT.PUT_LINE('当前总数为：'||V_COUNT);
IF V_COUNT = 0
THEN DBMS_OUTPUT.PUT_LINE('等于：'||V_COUNT);
ELSIF V_COUNT >=1
THEN DBMS_OUTPUT.PUT_LINE('大于：'||V_COUNT);
--具体业务
SELECT nvl(USERNAME, 'NULL') INTO V_USERNAME FROM XIAOYAN WHERE ID = T_ID;
T_NAME := V_USERNAME;
DBMS_OUTPUT.PUT_LINE(T_NAME);

ElSE
DBMS_OUTPUT.PUT_LINE('其它：'||V_COUNT);
END IF;
EXCEPTION
WHEN OTHERS THEN
T_NAME := 'EXCEPTION';
ROLLBACK;

END PRO_XIAOYAN;

--回滚存储过程
DROP PROCEDURE PRO_XIAOYAN;