一个存储过程编写

问答首页 → 移动开发技术

0 0

一个存储过程编写0

create table view_configid_wagename (ID bigint(18),NAME varchar2(20));

insert into view_configid_wagename (NAME, ID)values ('JOB_WAGE', 1);

insert into view_configid_wagename (NAME, ID)values ('LEVEL_WAGE', 2);

insert into view_configid_wagename (NAME, ID)values ('LIVE_ALLOWANCE', 3);

insert into view_configid_wagename (NAME, ID)values ('WORK_ALLOWANCE', 4);

insert into view_configid_wagename (NAME, ID)values ('POST_WAGE', 5);

insert into view_configid_wagename (NAME, ID)values ('TECHNICAL_GRADE_WAGE', 6);

insert into view_configid_wagename (NAME, ID)values ('LIVE_ALLOWANCE', 7);

insert into view_configid_wagename (NAME, ID)values ('WORK_ALLOWANCE', 8);

insert into view_configid_wagename (NAME, ID)values ('NULL', 21);

insert into view_configid_wagename (NAME, ID)values ('NULL', 22);

insert into view_configid_wagename (NAME, ID)values ('NULL', 23);

insert into view_configid_wagename (NAME, ID)values ('NULL', 24);

insert into view_configid_wagename (NAME, ID)values ('NULL', 25);

[code="java"] CREATE OR REPLACE PROCEDURE "GOVHR"."DEMO" authid current_user IS V_SQL VARCHAR2(10000) ; CURSOR CURSOR_1 IS SELECT T.ID,T.NAME FROM VIEW_CONFIGID_WAGENAME T ORDER BY ID; BEGIN V_SQL := 'SELECT ROWNUM '; FOR V_ID IN CURSOR_1 LOOP V_SQL := V_SQL|| ',' || V_ID.NAME||' AS ' || V_ID.ID; END LOOP; V_SQL := V_SQL || ' FROM VIEW_CONFIGID_WAGENAME '; --DBMS_OUTPUT.PUT_LINE(V_SQL); V_SQL := 'CREATE OR REPLACE VIEW DEMOVIEW AS '|| V_SQL; --DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL; END; ----------------执行存储过程，生成视图 BEGIN "GOVHR"."DEMO"; END; [/code]