Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jan 27th, 2012  |  syntax: PL/SQL  |  size: 2.81 KB  |  hits: 79  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1.  
  2. DROP TABLE k4_ob;
  3. DROP TABLE k4_rs;
  4.  
  5. CREATE TABLE k4_ob (id INTEGER, name VARCHAR2(30));
  6. CREATE TABLE k4_rs (id INTEGER, name VARCHAR2(30), id_ob INTEGER);
  7.  
  8. INSERT INTO k4_ob (id, name) VALUES (1, 'ob1');
  9. INSERT INTO k4_ob (id, name) VALUES (2, 'ob2');
  10. INSERT INTO k4_ob (id, name) VALUES (3, 'ob3');
  11.  
  12. INSERT INTO k4_rs (id, name, id_ob) VALUES (1, 'rs1', 1);
  13. INSERT INTO k4_rs (id, name, id_ob) VALUES (2, 'rs2', 1);
  14. INSERT INTO k4_rs (id, name, id_ob) VALUES (3, 'rs3', 2);
  15. INSERT INTO k4_rs (id, name, id_ob) VALUES (4, 'rs4', 1);
  16. INSERT INTO k4_rs (id, name, id_ob) VALUES (5, 'rs5', 3);
  17.  
  18. CREATE OR REPLACE PROCEDURE k4_set_ob (id_rs IN INTEGER, id_oba IN INTEGER) AS
  19. BEGIN
  20.     UPDATE k4_rs SET id_ob = id_oba WHERE id = id_rs;
  21. END;
  22. /
  23.  
  24. CREATE OR REPLACE PROCEDURE k4_del_ob (id_rs IN INTEGER) AS
  25. BEGIN
  26.     UPDATE k4_rs SET id_ob = NULL WHERE id = id_rs;
  27. END;
  28. /
  29.  
  30. CREATE OR REPLACE TRIGGER k4_on_update_rs
  31. before UPDATE ON k4_rs
  32. FOR each ROW
  33. DECLARE
  34.     self_user VARCHAR2(30);
  35.     invalid_user EXCEPTION;
  36.     after_day15 EXCEPTION;
  37. BEGIN
  38.     IF :NEW.id_ob IS NULL THEN
  39.         DBMS_OUTPUT.put_line('Deleting rs from ob');
  40.         SELECT owner INTO self_user FROM all_tables WHERE table_name = 'K3_VS';
  41.    
  42.         IF self_user <> USER THEN
  43.             DBMS_OUTPUT.put_line('fail');
  44.             RAISE invalid_user;
  45.         ELSE
  46.             DBMS_OUTPUT.put_line('ok');
  47.         END IF;
  48.     ELSE
  49.         DBMS_OUTPUT.put_line('Add rs to ob');
  50.        
  51.         IF EXTRACT(DAY FROM SYSDATE) > 15 THEN
  52.             DBMS_OUTPUT.put_line('fail');
  53.             RAISE after_day15;
  54.         ELSE
  55.             DBMS_OUTPUT.put_line('ok');
  56.         END IF;
  57.     END IF;
  58. EXCEPTION
  59.     WHEN invalid_user THEN                                                      
  60.         DBMS_OUTPUT.put_line('Invalid user');
  61.     WHEN after_day15 THEN                                                      
  62.         DBMS_OUTPUT.put_line('Deletin after 15 day is denied');
  63. END;
  64. /
  65.  
  66. CREATE OR REPLACE PACKAGE k4_pak IS
  67.     PROCEDURE show_rs (id_oba IN INTEGER);
  68. END k4_pak;
  69. /
  70.  
  71. CREATE OR REPLACE PACKAGE BODY k4_pak IS
  72.    
  73.     PROCEDURE show_rs (id_oba IN INTEGER) IS
  74.         TYPE type_sub_tbl_rs IS TABLE OF VARCHAR2(30);
  75.         rss type_sub_tbl_rs := type_sub_tbl_rs();
  76.         CURSOR cur (id_obb INTEGER) IS
  77.             SELECT ROWNUM AS it, name AS name
  78.             FROM k4_rs
  79.             WHERE id_ob = id_obb;
  80.     BEGIN
  81.         FOR cc IN cur(id_oba) LOOP
  82.             rss.extend;
  83.             rss(cc.it) := cc.name;
  84.         END LOOP;
  85.        
  86.         DBMS_OUTPUT.put_line('Extracted rss:');
  87.         FOR it IN rss.FIRST .. rss.LAST LOOP
  88.             DBMS_OUTPUT.put_line(rss(it));
  89.         END LOOP;
  90.     END show_rs;
  91.    
  92. END k4_pak;
  93. /
  94.  
  95. BEGIN
  96.     k4_del_ob(4);
  97.     k4_set_ob(4, 1);
  98.     k4_pak.show_rs(1);
  99. END;
  100. /
create a new version of this paste RAW Paste Data