build_api.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Build_API.sql
--* Author       : DR Timothy S Hall
--* Description  : Generates a basic API package for the specific table.
--* Requirements : USER_% and ALL_% views.
--* Call Syntax  : @Build_API (table-name) (schema)
--* Last Modified: 08/01/2002
SET SERVEROUTPUT ON
SET VERIFY OFF
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF

SPOOL Package.pkh

DECLARE
  
  v_table_name VARCHAR2(30)  := Upper('&1');
  v_owner      VARCHAR2(30)  := Upper('&2');
  
  CURSOR c_pk_columns IS
    SELECT a.position,
           a.column_name
    FROM   all_cons_columns a,
           all_constraints b
    WHERE  a.owner           = v_owner
    AND    a.table_name      = v_table_name
    AND    a.constraint_name = b.constraint_name
    AND    b.constraint_type = 'P'
    AND    b.owner           = a.owner
    AND    b.table_name      = a.table_name
    ORDER BY position;

  CURSOR c_columns IS
    SELECT atc.column_name
    FROM   all_tab_columns atc
    WHERE  atc.owner      = v_owner
    AND    atc.table_name = v_table_name; 
    
  CURSOR c_non_pk_columns (p_nullable  IN  VARCHAR2) IS
    SELECT atc.column_name
    FROM   all_tab_columns atc
    WHERE  atc.owner      = v_owner
    AND    atc.table_name = v_table_name
    AND    atc.nullable   = p_nullable
    AND    atc.column_name NOT IN (SELECT a.column_name
                                   FROM   all_cons_columns a,
                                          all_constraints b
                                   WHERE  a. owner          = v_owner
                                   AND    a.table_name      = v_table_name
                                   AND    a.constraint_name = b.constraint_name
                                   AND    b.constraint_type = 'P'
                                   AND    b.owner           = a.owner
                                   AND    b.table_name      = a.table_name); 
    
  PROCEDURE GetParameterList IS
  BEGIN
    FOR cur_col IN c_pk_columns LOOP
      DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || '  IN  ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE,');
    END LOOP;
    FOR cur_col IN c_non_pk_columns('N') LOOP
      DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || '  IN  ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE,');
    END LOOP;
    FOR cur_col IN c_non_pk_columns('Y') LOOP
      DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || '  IN  ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE DEFAULT NULL,');
    END LOOP;
    DBMS_Output.Put(Chr(9) || 'p_' || RPad('commit', 30, ' ') || '  IN  VARCHAR2 DEFAULT ''Y''');
  END;
  
  PROCEDURE GetPKParameterList IS
  BEGIN
    FOR cur_col IN c_pk_columns LOOP
      DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || '  IN  ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE,');
    END LOOP;
    DBMS_Output.Put(Chr(9) || 'p_' || RPad('commit', 30, ' ') || '  IN  VARCHAR2 DEFAULT ''Y''');
  END;
  
  PROCEDURE GetInsertColumnList IS
  BEGIN
    FOR cur_col IN c_columns LOOP
      IF c_columns%ROWCOUNT != 1 THEN
        DBMS_Output.Put_Line(',');
      END IF;
      DBMS_Output.Put(Chr(9) || Chr(9) || Lower(cur_col.column_name));
    END LOOP;
    DBMS_Output.New_Line;
  END;

  PROCEDURE GetInsertValueList IS
  BEGIN
    FOR cur_col IN c_columns LOOP
      IF c_columns%ROWCOUNT != 1 THEN
        DBMS_Output.Put_Line(',');
      END IF;
      DBMS_Output.Put(Chr(9) || Chr(9) || 'p_' || Lower(cur_col.column_name));
    END LOOP;
    DBMS_Output.New_Line;
  END;

  PROCEDURE GetUpdateSetList IS
  BEGIN
    FOR cur_col IN c_columns LOOP
      IF c_columns%ROWCOUNT != 1 THEN
        DBMS_Output.Put_Line(',');
        DBMS_Output.Put(Chr(9) || Chr(9) || Chr(9) || Chr(9));
      ELSE
        DBMS_Output.Put(Chr(9) || 'SET    ');
      END IF;
      DBMS_Output.Put(RPad(Lower(cur_col.column_name), 30, ' ') || ' = p_' || Lower(cur_col.column_name));
    END LOOP;
    DBMS_Output.New_Line;
  END;
  
  PROCEDURE GetPKWhere (p_for_update  IN  VARCHAR2 DEFAULT NULL) IS
  BEGIN
    FOR cur_col IN c_pk_columns LOOP
      IF c_pk_columns%ROWCOUNT = 1 THEN
        DBMS_Output.Put(Chr(9) || 'WHERE  ');
      ELSE
        DBMS_Output.New_Line;
        DBMS_Output.Put(Chr(9) || 'AND    ');
      END IF;
      DBMS_Output.Put(RPad(Lower(cur_col.column_name), 30, ' ') || ' = p_' || Lower(cur_col.column_name));
    END LOOP;
    
    IF p_for_update = 'Y' THEN
      DBMS_Output.New_Line;
      DBMS_Output.Put(Chr(9) || 'FOR UPDATE');
    END IF;
    DBMS_Output.Put_Line(';');
  END;
  
  PROCEDURE GetCommit IS
  BEGIN
    DBMS_Output.Put_Line(Chr(9) || 'IF p_commit = ''Y'' THEN');
    DBMS_Output.Put_Line(Chr(9) || Chr(9) || 'COMMIT;');
    DBMS_Output.Put_Line(Chr(9) || 'END IF;');
    DBMS_Output.New_Line;
  END;

  PROCEDURE GetSeparator IS
  BEGIN
    DBMS_Output.Put_Line('-- -----------------------------------------------------------------------');
  END;
  
BEGIN

  DBMS_Output.Enable(1000000);
  
  -- ---------------------
  -- Package Specification
  -- ---------------------
  DBMS_Output.Put_Line('-- -----------------------------------------------------------------------');
  DBMS_Output.Put_Line('-- Name        : ' || Lower(v_table_name) || '_api.pkh');
  DBMS_Output.Put_Line('-- Created By  : DR Timothy S Hall');
  DBMS_Output.Put_Line('-- Created Date: ' || To_Char(Sysdate, 'DD-Mon-YYYY'));
  DBMS_Output.Put_Line('-- Description : API procedures for the ' || v_table_name || ' table.');
  DBMS_Output.Put_Line('-- Ammendments :');
  DBMS_Output.Put_Line('--   ' || To_Char(Sysdate, 'DD-Mon-YYYY') || '  TSH  Initial Creation');
  DBMS_Output.Put_Line('-- -----------------------------------------------------------------------');
  DBMS_Output.Put_Line('CREATE OR REPLACE PACKAGE ' || Lower(v_table_name) || '_api AS');
  DBMS_Output.Put_Line(Chr(9));
  DBMS_Output.Put_Line('TYPE cursor_type IS REF CURSOR;');
  DBMS_Output.Put_Line(Chr(9));
  
  DBMS_Output.Put_Line('PROCEDURE Sel (');
  GetPKParameterList;
  DBMS_Output.New_Line;
  DBMS_Output.Put_Line(Chr(9) || RPad('p_recordset', 32, ' ') || '  OUT cursor_type');
  DBMS_Output.Put_Line(');');
  DBMS_Output.Put_Line(Chr(9));

  DBMS_Output.Put_Line('PROCEDURE Ins (');
  GetParameterList;
  DBMS_Output.Put_Line(');');
  DBMS_Output.Put_Line(Chr(9));

  DBMS_Output.Put_Line('PROCEDURE Upd (');
  GetParameterList;
  DBMS_Output.Put_Line(');');
  DBMS_Output.Put_Line(Chr(9));

  DBMS_Output.Put_Line('PROCEDURE Del (');
  GetPKParameterList;
  DBMS_Output.Put_Line(');');
  DBMS_Output.Put_Line(Chr(9));

  DBMS_Output.Put_Line('END ' || Lower(v_table_name) || '_api;');
  DBMS_Output.Put_Line('/');

  -- ------------
  -- Package Body
  -- ------------
  DBMS_Output.Put_Line('-- -----------------------------------------------------------------------');
  DBMS_Output.Put_Line('-- Name        : ' || Lower(v_table_name) || '_api.pkg');
  DBMS_Output.Put_Line('-- Created By  : DR Timothy S Hall');
  DBMS_Output.Put_Line('-- Created Date: ' || To_Char(Sysdate, 'DD-Mon-YYYY'));
  DBMS_Output.Put_Line('-- Description : API procedures for the ' || v_table_name || ' table.');
  DBMS_Output.Put_Line('-- Ammendments :');
  DBMS_Output.Put_Line('--   ' || To_Char(Sysdate, 'DD-Mon-YYYY') || '  TSH  Initial Creation');
  DBMS_Output.Put_Line('-- -----------------------------------------------------------------------');
  DBMS_Output.Put_Line('CREATE OR REPLACE PACKAGE BODY ' || Lower(v_table_name) || '_api AS');
  DBMS_Output.Put_Line(Chr(9));

  -- Select
  GetSeparator;
  DBMS_Output.Put_Line('PROCEDURE Sel (');
  GetPKParameterList;
  DBMS_Output.New_Line;
  DBMS_Output.Put_Line(Chr(9) || RPad('p_recordset', 32, ' ') || '  OUT cursor_type');
  DBMS_Output.Put_Line(') IS');
  GetSeparator;

  DBMS_Output.Put_Line('BEGIN');
  DBMS_Output.Put_Line(Chr(9));

  DBMS_Output.Put_Line(Chr(9) || 'OPEN p_recordset FOR');
  DBMS_Output.Put_Line(Chr(9) || 'SELECT'); 
  GetInsertColumnList;
  DBMS_Output.Put_Line(Chr(9) || 'FROM ' || Lower(v_table_name)); 
  GetPKWhere;

  DBMS_Output.Put_Line(Chr(9));
  DBMS_Output.Put_Line('END Sel;');
  GetSeparator;
  DBMS_Output.Put_Line(Chr(9));


  -- Insert
  GetSeparator;
  DBMS_Output.Put_Line('PROCEDURE Ins (');
  GetParameterList;
  DBMS_Output.Put_Line(') IS');
  GetSeparator;
  
  DBMS_Output.Put_Line('BEGIN');
  DBMS_Output.Put_Line(Chr(9));
  
  DBMS_Output.Put_Line(Chr(9) || 'INSERT INTO ' || Lower(v_table_name));
  DBMS_Output.Put_Line(Chr(9) || '(');
  GetInsertColumnList;
  DBMS_Output.Put_Line(Chr(9) || ')');
  DBMS_Output.Put_Line(Chr(9) || 'VALUES');
  DBMS_Output.Put_Line(Chr(9) || '(');
  GetInsertValueList;
  DBMS_Output.Put_Line(Chr(9) || ');');
  DBMS_Output.Put_Line(Chr(9));
  
  GetCommit;
  DBMS_Output.Put_Line('END Ins;');
  GetSeparator;
  DBMS_Output.Put_Line(Chr(9));

  -- Update
  GetSeparator;
  DBMS_Output.Put_Line('PROCEDURE Upd (');
  GetParameterList;
  DBMS_Output.Put_Line(') IS');
  GetSeparator;
  
  DBMS_Output.Put_Line('BEGIN');
  DBMS_Output.Put_Line(Chr(9));
  DBMS_Output.Put_Line(Chr(9) || 'UPDATE ' || Lower(v_table_name));
  GetUpdateSetList;
  GetPKWhere;
  DBMS_Output.Put_Line(Chr(9));
  
  GetCommit;
  DBMS_Output.Put_Line('END Upd;');
  GetSeparator;
  DBMS_Output.Put_Line(Chr(9));

  -- Delete
  GetSeparator;
  DBMS_Output.Put_Line('PROCEDURE Del (');
  GetPKParameterList;
  DBMS_Output.Put_Line(') IS');
  GetSeparator;
  
  DBMS_Output.Put_Line('BEGIN');
  DBMS_Output.Put_Line(Chr(9));
  DBMS_Output.Put_Line(Chr(9) || 'DELETE FROM ' || Lower(v_table_name));
  GetPKWhere;
  DBMS_Output.Put_Line(Chr(9));

  GetCommit;
  DBMS_Output.Put_Line('END Del;');
  GetSeparator;
  DBMS_Output.Put_Line(Chr(9));

  DBMS_Output.Put_Line('END ' || Lower(v_table_name) || '_api;');
  DBMS_Output.Put_Line('/');

END;
/

SPOOL OFF

SET ECHO ON
SET TERMOUT ON
SET FEEDBACK ON