Oracle calls the stored procedure to execute a small DEMO of CRUD

Oracle calls the stored procedure to execute a small DEMO of CRUD

------- Modify (Table Name, primary key ID, column to be modified) create or replace procedure pro_code_edit (p_tbname in varchar2, p_ct_id in varchar2, p_codename in varchar2) as p_str varchar2 (400 ); begin p_str: = 'update' | p_tbname | 'set codename = ''' | p_codeName | ''' where ct_id = ''' | p_ct_id | ''''; execute immediate p_str; end; -- Query all records and directly return the cursor for the next step at the call. create or replace procedure pro_code_queryall (p_tbname in varchar2, p_list out sys_refcursor) asbegin open p_list for 'select * from '| p_tbname | ''; end; -- query a single record (Table Name, primary key, field to be queried) create or replace procedure tables (p_tbname in varchar2, p_ct_id in varchar2, p_code out varchar2, p_codename out varchar2, p_fathercode out varchar2) as v_code varchar2 (40); v_codename varchar2 (40 ); vanderbilt fathercode varchar2 (40); v_str varchar2 (400); begin v_str: = 'select code, codename, fathercode from '| p_tbname | 'where ct_id =: P '; execute immediate v_str into v_code, v_codename, v_fathercode using p_ct_id; p_code: = v_code; p_codename: = v_codename; p_fathercode: = v_fathercode; end; -- delete records (Table Name, primary key value) -- The primary key name here is the create or replace procedure pro_code_remove (p_tbname in varchar2, p_ct_id in varchar2) as v_str varchar2 (400); v_con_name varchar2 (40 ); v_pid varchar2 (40): = trim (p_ct_id); v_pk_column_name varchar2 (40); v_tbname varchar2 (400): = upper (trim (p_tbname )); begin select constraint_name into v_con_name from all_constraints where owner = upper ('daily ') and constraint_type = 'p' and table_name = v_tbname; select column_name into v_pk_column_name from all_cons_columns where owner = upper ('daily ') and table_name = v_tbname and constraint_name = v_con_name; v_str: = 'delete from doorkind where' | v_pk_column_name | '=: P'; execute immediate v_str using v_pid; end; -- save records (because of the special characteristics of data design, the primary key is automatically generated according to the rules.) create or replace procedure pro_code_save (p_tbname in varchar2, p_codename in varchar2) as v_codetype varchar2 (100); p_id varchar2 (400 ); p_str varchar2 (400); v_len number (10); v_left varchar2 (100); v_right varchar2 (100); begin v_codetype: = p_tbname; p_str: = 'select max (ct_id) from '| v_codetype; execute immediate p_str into p_id; select codetype, codelength into v_codetype, v_len from codenavigation where upper (codetype) = upper (v_codetype ); if p_id is not null then v_left: = regexp_substr (p_id, '[A-Za-z] +', 1, 1); v_right: = regexp_substr (p_id, '[0-9] +', 1, 1); v_right: = lpad (v_right + 1, v_len, '0'); else v_left: = v_codetype; v_right: = lpad (1, v_len, '0'); end if; p_id: = v_left | v_right; dbms_output.put_line (p_id); p_str: = 'insert' | v_codetype | ''| 'values (''' | p_id | ''', ''' | v_right | ''', ''' | p_codeName | ''', ''' | v_left | ''')'; execute immediate p_str; dbms_output.put_line (p_str); end;