{"id":1785,"date":"2010-09-01T09:46:10","date_gmt":"2010-09-01T14:46:10","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=1785"},"modified":"2016-03-04T11:34:32","modified_gmt":"2016-03-04T16:34:32","slug":"oracle-plsql-102","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=1785","title":{"rendered":"Oracle PL\/SQL (10.2)"},"content":{"rendered":"

Block Structure<\/h2><\/span>\n
\r\n[DECLARE\r\n  -- declarations]\r\nBEGIN\r\n[EXCEPTION\r\n  -- handlers]\r\nEND;\r\n<\/pre>\n
Variables<\/h2><\/span>\n
Declaring Variables<\/h3><\/span>\n
* SQL data types<\/p>\n
\r\nDECLARE\r\n  part_no NUMBER(6);\r\n  part_name VARCHAR2(20);\r\n  in_stock BOOLEAN;\r\n  part_price NUMBER(6,2);\r\n  part_desc VARCHAR2(50);\r\n<\/pre>\n
* %TYPE, %ROWTYPE<\/p>\n
\r\nv_last_name employees.last_name%TYPE;\r\ndept_rec departments%ROWTYPE;\r\n<\/pre>\n
\r\nSET SERVEROUT ON\r\nDECLARE\r\n  CURSOR c1 IS\r\n  SELECT last_name, salary, hire_date, job_id FROM employees\r\n  WHERE employee_id = 120;\r\n  -- declare record variable that represents a row fetched from the employees table\r\n  employee_rec c1%ROWTYPE;\r\nBEGIN\r\n  -- open the explicit cursor and use it to fetch data into employee_rec\r\n  OPEN c1;\r\n  LOOP\r\n    FETCH c1 INTO employee_rec;\r\n    EXIT WHEN c1%NOTFOUND;\r\n    DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);\r\n  END LOOP;\r\n  CLOSE c1;\r\nEND;\r\n\/\r\n<\/pre>\n
Declaring Constants<\/h3><\/span>\n
Use ‘CONSTANT’ qualifier.<\/p>\n
\r\ncredit_limit CONSTANT NUMBER := 5000.00;\r\n<\/pre>\n
Assign Values to Variables<\/h3><\/span>\n
* Use assignment operator ‘:=’<\/p>\n
\r\nDECLARE\r\n  wages NUMBER;\r\n  hours_worked NUMBER := 40;\r\n  hourly_salary NUMBER := 22.50;\r\n  bonus NUMBER := 150;\r\n  country VARCHAR2(128);\r\n  counter NUMBER := 0;\r\n  done BOOLEAN;\r\n  valid_id BOOLEAN;\r\n  emp_rec1 employees%ROWTYPE;\r\n  emp_rec2 employees%ROWTYPE;\r\n  TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;\r\n  comm_tab commissions;\r\n<\/pre>\n
* Use select into<\/p>\n
\r\nDECLARE\r\n  bonus NUMBER(8,2);\r\n  emp_id NUMBER(6) := 100;\r\nBEGIN\r\n  SELECT salary * 0.10 INTO bonus FROM employees\r\n  WHERE employee_id = emp_id;\r\nEND;\r\n<\/pre>\n
* Pass values into PL\/SQL as OUT or IN OUT parameters<\/p>\n
\r\nDECLARE\r\n  new_sal NUMBER(8,2);\r\n  emp_id NUMBER(6) := 126;\r\nPROCEDURE adjust_salary(emp_id NUMBER, sal IN OUT NUMBER) IS\r\n  emp_job VARCHAR2(10);\r\n  avg_sal NUMBER(8,2);<\/pre>\n
Bind Variables<\/h3><\/span>\n
Use bind variables to reuse SQL statements.<\/p>\n
\r\n'DELETE FROM employees WHERE employee_id = :id' USING emp_id;\r\n<\/pre>\n
Control Structures<\/h2><\/span>\n
IF THEN ELSE And CASE<\/h3><\/span>\n
\r\nDECLARE\r\n  jobid employees.job_id%TYPE;\r\n  empid employees.employee_id%TYPE := 115;\r\n  sal employees.salary%TYPE;\r\n  sal_raise NUMBER(3,2);\r\nBEGIN\r\n  SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid;\r\n  CASE\r\n    WHEN jobid = 'PU_CLERK' THEN\r\n      IF sal < 3000 THEN sal_raise := .12;\r\n      ELSE sal_raise := .09;\r\n      END IF;\r\n    WHEN jobid = 'SH_CLERK' THEN\r\n      IF sal < 4000 THEN sal_raise := .11;\r\n      ELSE sal_raise := .08;\r\n      END IF;\r\n    WHEN jobid = 'ST_CLERK' THEN\r\n      IF sal < 3500 THEN sal_raise := .10;\r\n      ELSE sal_raise := .07;\r\n      END IF;\r\n    ELSE\r\n      BEGIN\r\n        DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);\r\n      END;\r\n  END CASE;\r\n  UPDATE employees SET salary = salary + salary * sal_raise\r\n    WHERE employee_id = empid;\r\n  COMMIT;\r\nEND;<\/pre>\n
FOR LOOP<\/h3><\/span>\n
\r\nCREATE TABLE sqr_root_sum (num NUMBER, sq_root NUMBER(6,2),\r\n  sqr NUMBER, sum_sqrs NUMBER);\r\nDECLARE\r\n  s PLS_INTEGER;\r\nBEGIN\r\n  FOR i in 1..100 LOOP\r\n    s := (i * (i + 1) * (2*i +1)) \/ 6; -- sum of squares\r\n    INSERT INTO sqr_root_sum VALUES (i, SQRT(i), i*i, s );\r\n  END LOOP;\r\nEND;<\/pre>\n
WHILE LOOP<\/h3><\/span>\n
\r\nWHILE sal <= 15000 LOOP -- loop until sal > 15000\r\n  SELECT salary, manager_id, last_name INTO sal, mgr_id, lname\r\n  FROM employees WHERE employee_id = mgr_id;\r\nEND LOOP;<\/pre>\n
EXIT WHEN<\/h3><\/span>\n
\r\nLOOP\r\n  counter := counter + 1;\r\n  total := total + counter * counter;\r\n  -- exit loop when condition is true\r\n  EXIT WHEN total > 25000;\r\nEND LOOP;<\/pre>\n
Subprograms: Procedures and Functions<\/h2><\/span>\n
* Subprograms can be procedures and functions.
\n- Functions can return a value, procedure not. Procedures can only have IN parameters.
\n- Function can be used in a select statement, procedures not.<\/p>\n
Packages<\/h3><\/span>\n
* Packages related types and procedures together.
\n* Define package<\/p>\n
\r\nCREATE OR REPLACE PACKAGE emp_actions AS -- package specification\r\n  PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2,\r\n    first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2,\r\n    hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER,\r\n    manager_id NUMBER, department_id NUMBER);\r\n  PROCEDURE fire_employee (emp_id NUMBER);\r\n  FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;\r\nEND emp_actions;\r\n\/\r\n\r\nCREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body\r\n  -- code for procedure hire_employee\r\n  PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2,\r\n    first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE,\r\n    job_id VARCHAR2, salary NUMBER, commission_pct NUMBER,\r\n    manager_id NUMBER, department_id NUMBER) IS\r\n  BEGIN\r\n    INSERT INTO employees VALUES (employee_id, last_name, first_name, email,\r\n    phone_number, hire_date, job_id, salary, commission_pct, manager_id,\r\n    department_id);\r\n  END hire_employee;\r\n\r\n  -- code for procedure fire_employee\r\n  PROCEDURE fire_employee (emp_id NUMBER) IS\r\n    BEGIN\r\n    DELETE FROM employees WHERE employee_id = emp_id;\r\n  END fire_employee;\r\n\r\n  -- code for function num_above salary\r\n  FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS\r\n    emp_sal NUMBER(8,2);\r\n    num_count NUMBER;\r\n  BEGIN\r\n    SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;\r\n    SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal;\r\n    RETURN num_count;\r\n  END num_above_salary;\r\nEND emp_actions;<\/pre>\n
* Use package<\/p>\n
\r\nCALL emp_actions.hire_employee(300, 'Belden', 'Enrique', 'EBELDEN',\r\n  '555.111.2222', '31-AUG-04', 'AC_MGR', 9000, .1, 101, 110);\r\nBEGIN\r\n  DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' ||\r\n  TO_CHAR(emp_actions.num_above_salary(120)));\r\n  emp_actions.fire_employee(300);\r\nEND;<\/pre>\n
Data Abstraction<\/h2><\/span>\n
Cursors<\/h3><\/span>\n
* Points to a private SQL area.<\/p>\n
\r\nDECLARE\r\n  CURSOR c1 IS\r\n    SELECT last_name, salary, hire_date, job_id FROM employees\r\n    WHERE employee_id = 120;\r\n  -- declare record variable that represents a row fetched from the employees table\r\n  employee_rec c1%ROWTYPE;\r\nBEGIN\r\n  -- open the explicit cursor and use it to fetch data into employee_rec\r\n  OPEN c1;\r\n  FETCH c1 INTO employee_rec;\r\n  DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);\r\nEND;<\/pre>\n
Collections<\/h2><\/span>\n
* Ordered group of elements, all of the same type.
\n* Declare using TYPE definition.<\/p>\n
VARRAY<\/h3><\/span>\n
* PL\/SQL equivalent of an Array<\/strong>
\n* Can not have gaps in subscripts, i.e. is dense.<\/p>\n
\r\nTYPE Calendar IS VARRAY(366) OF DATE;<\/pre>\n
Nested Table<\/h3><\/span>\n
* PL\/SQL equivalent of a Set<\/strong>
\n* One dimension array with no <\/strong>declared number of elements.
\n* Can have gaps in subscripts.
\n* Can be stored in database column.<\/p>\n
\r\nTYPE nested_type IS TABLE OF VARCHAR2(30);<\/pre>\n
Associative Array<\/h3><\/span>\n
* aka index-by tables
\n* PL\/SQL equivalent of Hashtable<\/strong>
\n* Can not be stored in database column.<\/p>\n
\r\nDECLARE\r\n  TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE\r\n    INDEX BY PLS_INTEGER;\r\n  emp_tab EmpTabTyp;\r\nBEGIN\r\n  \/* Retrieve employee record. *\/\r\n  SELECT * INTO emp_tab(100) FROM employees WHERE employee_id = 100;\r\nEND;\r\n\/<\/pre>\n
\r\nDECLARE\r\n  TYPE staff_list IS TABLE OF employees.employee_id%TYPE;\r\n  staff staff_list;\r\n  lname employees.last_name%TYPE;\r\n  fname employees.first_name%TYPE;\r\nBEGIN\r\n  staff := staff_list(100, 114, 115, 120, 122);\r\n  FOR i IN staff.FIRST..staff.LAST LOOP\r\n    SELECT last_name, first_name INTO lname, fname FROM employees\r\n    WHERE employees.employee_id = staff(i);\r\n    DBMS_OUTPUT.PUT_LINE ( TO_CHAR(staff(i)) || ': ' || lname || ', ' || fname );\r\n  END LOOP;\r\nEND;\r\n\/<\/pre>\n
\r\nDECLARE\r\n  TYPE nested_type IS TABLE OF VARCHAR2(30);\r\n  TYPE varray_type IS VARRAY(5) OF INTEGER;\r\n  TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;\r\n  TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;\r\n  TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);\r\n  v1 nested_type;\r\n  v2 varray_type;\r\n  v3 assoc_array_num_type;\r\n  v4 assoc_array_str_type;\r\n  v5 assoc_array_str_type2;\r\nBEGIN\r\n  -- an arbitrary number of strings can be inserted v1\r\n  v1 := nested_type('Shipping','Sales','Finance','Payroll');\r\n  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers\r\n  v3(99) := 10; -- Just start assigning to elements\r\n  Defining Collection Types and Declaring Collection Variables\r\n  5-8 Oracle Database PL\/SQL User\u2019s Guide and Reference\r\n  v3(7) := 100; -- Subscripts can be any integer values\r\n  v4(42) := 'Smith'; -- Just start assigning to elements\r\n  v4(54) := 'Jones'; -- Subscripts can be any integer values\r\n  v5('Canada') := 'North America'; -- Just start assigning to elements\r\n  v5('Greece') := 'Europe'; -- Subscripts can be string values\r\nEND;<\/pre>\n
Records<\/h3><\/span>\n
* Composite data structures (like C struct)
\n* Fields can have different data types.
\n* Use %ROWTYPE to declare a record that represents a row in a table.<\/p>\n
\r\nDECLARE\r\n  TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);\r\n  TYPE meetin_typ IS RECORD (\r\n    date_held DATE,\r\n    duration timerec, -- nested record\r\n    location VARCHAR2(20),\r\n    purpose VARCHAR2(50));\r\nBEGIN\r\n  -- NULL does nothing but allows unit to be compiled and tested\r\n  NULL;\r\nEND;\r\n\/<\/pre>\n
Object Types<\/h3><\/span>\n
* Contains both data (attributes) and functions (methods).<\/p>\n
\r\nCREATE TYPE bank_account AS OBJECT (\r\n  acct_number NUMBER(5),\r\n  balance NUMBER,\r\n  status VARCHAR2(10),\r\n  \r\n  MEMBER PROCEDURE open (SELF IN OUT NOCOPY bank_account, amount IN NUMBER),\r\n  MEMBER PROCEDURE close (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount OUT NUMBER),\r\n  MEMBER PROCEDURE deposit (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount IN NUMBER),\r\n  MEMBER PROCEDURE withdraw (SELF IN OUT NOCOPY bank_account, num IN NUMBER, amount IN NUMBER),\r\n  MEMBER FUNCTION curr_bal (num IN NUMBER) RETURN NUMBER );\r\n\/<\/pre>\n
Exceptions<\/h3><\/span>\n
* Use RAISE statement to throw user defined exceptions.
\n* Use EXCEPTION to catch the exceptions.<\/p>\n
\r\nEXCEPTION -- exception-handling part starts here\r\n  WHEN comm_missing THEN\r\n    DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');\r\n    commission := 0;\r\n  WHEN OTHERS THEN\r\n    err_num := SQLCODE;\r\n    err_msg := SUBSTR(SQLERRM, 1, 100);\r\n    INSERT INTO errors VALUES (err_num, err_msg);\r\n    NULL; -- for other exceptions do nothing<\/pre>\n
References<\/h2><\/span>\n
* PL\/SQL User's Guide and Reference 10g Release 2 (10.2)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Block Structure [DECLARE — declarations] BEGIN [EXCEPTION — handlers] END; Variables Declaring Variables * SQL data types DECLARE part_no NUMBER(6); part_name VARCHAR2(20); in_stock BOOLEAN; part_price NUMBER(6,2); part_desc VARCHAR2(50); * %TYPE, %ROWTYPE v_last_name employees.last_name%TYPE; dept_rec departments%ROWTYPE; SET SERVEROUT ON DECLARE CURSOR … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[74],"tags":[],"class_list":["post-1785","post","type-post","status-publish","format-standard","hentry","category-plsql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-sN","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1785","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1785"}],"version-history":[{"count":7,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1785\/revisions"}],"predecessor-version":[{"id":2689,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1785\/revisions\/2689"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1785"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1785"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1785"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}