Guest User

Untitled

a guest
Feb 25th, 2018
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.09 KB | None | 0 0
  1. CREATE OR REPLACE PROCEDURE insert_class(
  2. Teacher_name_param class.teacher_name%TYPE,
  3. class_name_param class.class_name%TYPE,
  4. Start_Date_param class.start_date%TYPE,
  5. End_date_param class.end_date%TYPE,
  6. class_category_param class.class_category%TYPE,
  7. Topic_param class.topic%TYPE,
  8. Teacher_credential_param class.teacher_credential%TYPE)
  9. AS
  10. class_number_var class.class_number%TYPE;
  11. --start_date_var class.start_date%TYPE;
  12. end_date_var class.end_date%TYPE;
  13.  
  14. BEGIN
  15. -- Creates the next ID
  16. SELECT class_number_seq.NEXTVAL
  17. INTO class_number_var
  18. FROM dual;
  19.  
  20. IF end_date_param < start_date_param THEN
  21. SELECT end_date_param INTO end_date_var
  22. FROM class
  23. WHERE class_number_var = class.class_number;
  24. end_date_var := start_date_param + 14;
  25. ELSE
  26. end_date_var := end_date_param;
  27. END IF;
  28.  
  29. INSERT INTO class (class_number, teacher_name, class_name, start_date, end_date,
  30. class_category, topic, teacher_credential)
  31. VALUES (class_number_var, teacher_name_param, class_name_param, start_date_param, end_date_param,
  32. class_category_param, topic_param, teacher_credential_param);
  33.  
  34. COMMIT;
  35. END;
  36. /
  37.  
  38. CALL insert_class('Collin Hannah', 'Intro to psychology', 01-OCT-15, 01-SEP-15, 'Psychology', 'Intro Classes', 'BS psychology');
  39.  
  40. BEGIN
  41. insert_class('Collin Hannah', 'Intro to psychology', TO_DATE('01-OCT-15','DD-MON-YY'), TO_DATE('01-SEP-15','DD-MON-YY'), 'Psychology', 'Intro Classes', 'BS psychology');
  42. END;
  43.  
  44. --OR
  45.  
  46. EXEC insert_class('Collin Hannah', 'Intro to psychology', TO_DATE('01-OCT-15','DD-MON-YY'), TO_DATE('01-SEP-15','DD-MON-YY'), 'Psychology', 'Intro Classes', 'BS psychology');
  47.  
  48. SQL> create or replace procedure p
  49. 2 as
  50. 3 begin
  51. 4 null;
  52. 5 end;
  53. 6 /
  54.  
  55. Procedure created.
  56.  
  57. SQL> sho err
  58. No errors.
  59.  
  60. SQL> CALL p;
  61. CALL p
  62. *
  63. ERROR at line 1:
  64. ORA-06576: not a valid function or procedure name
  65.  
  66. SQL> EXEC p;
  67.  
  68. PL/SQL procedure successfully completed.
  69.  
  70. TO_DATE('01-OCT-2015', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
  71.  
  72. DATE '2015-10-01'
Add Comment
Please, Sign In to add comment