21 - Stored Procedures
Stored Procedures
Stored ProceduresAdvantages •Pre-compilation = faster execution•Less Data transferred across network–EXAMPLE:–exec sp_getproducts 12,234–vs.–SELECT Employees.Country, stName, Employees.FirstName, Orders.ShippedDate,–Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount–FROM Employees INNER JOIN–(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) –ON Employees.EmployeeID = Orders.EmployeeID•Re-usable execution plans used by the database server for each proc•More Secure (for inputted data)Disadvantages •More skill required to write•May find it to be less flexible than simple CFQUERY execution.•More planning needed for proper implementation (not always a bad thing!)Dynamic Order by Statement •You need to define all possible columns that will be ordered by in your procedure•Your Ordered by columns must be grouped based on data type!•Your Order Direction must be accounted for as well (i.e. ascending or descending)Sample Order By •Demonstration Example: Northwind Database –Employee Sales By CountryCOLD FUSION Implementation •Simple as passing the ‘parameters’through the url or through a form•i.e. ?bdate=1/1/1900&edate=1/1/2005&Orderby =Company&SortDirection=ascCFSTOREDPROC •The call to the Procedure:Using the SQL 2K Web Assistant •Using the WebAssistant to createsome great simplereports leveragingyour Database Serverto Create the reports,in a great paginatedwayWhy?•Let the Database create some great ‘offline’pages with pagination with NO Cold fusion coding needed•Take some load off the Application Server•Give your customers / clients their data by creating web assistants reports for stored procs you’ve already createdKeys to Success•Need a flexible way to change your report without having to go through the Setup Wizard Each time •Need a way to manually re-generate them, or schedule their execution (Job).•Use a stored procedure that will execute the report (if report changes you can make the easy change w/in the proc.•Use some specially crafted SQL to create usable HTML for your reportsWelcome to the Web Assistant WizardAdding Functionality•Use SQL to create usable HTML elements–Select ‘<input type=checkbox name=orderid value=‘ + convert(varchar(10),orders.orderid) +‘ onclick=document.location.href=“ordersummary.cfm?orderid=‘ + convert(varchar(10),orders.orderid) +”>Live Example •Adding the SQL to the Stored Procedure that will give the reports some added usability and function•Simple Checkbox with a link to a cfm page •Ability to ‘regenerate’the Job via a link (EXEC sp_runwebtask @procname = N'Northwind Web Page')。
List of Subjects in 21 CFR Food and Drugs
✧21 CFR Part 1 Cosmetics Drugs Exports Food labeling Imports Labeling Reporting and recordkeepingrequirements✧21 CFR Part 2 Administrative practice and procedure Cosmetics Drugs Foods✧21 CFR Part 3 Administrative practice and procedure Biologics Drugs Medical devices✧21 CFR Part 5 Authority delegations (Government agencies) Imports Organization and functions (Governmentagencies)✧21 CFR Part 7 Administrative practice and procedure Consumer protection Reporting and recordkeepingrequirements✧21 CFR Part 10 Administrative practice and procedure News media✧21 CFR Part 11 Administrative practice and procedure Computer technology Reporting and recordkeepingrequirements✧21 CFR Part 12 Administrative practice and procedure✧21 CFR Part 13 Administrative practice and procedure✧21 CFR Part 14 Administrative practice and procedure Advisory committees Color additives Drugs Radiationprotection✧21 CFR Part 15 Administrative practice and procedure✧21 CFR Part 16 Administrative practice and procedure✧21 CFR Part 17 Administrative practice and procedure Penalties✧21 CFR Part 19 Conflict of interests✧21 CFR Part 20 Confidential business information Courts Freedom of information Government employees✧21 CFR Part 21 Privacy✧21 CFR Part 25 Environmental impact statements Foreign relations Reporting and recordkeeping requirements ✧21 CFR Part 26 Animal drugs Biologics Drugs Exports Imports✧21 CFR Part 50 Human research subjects Prisoners Reporting and recordkeeping requirements Safety✧21 CFR Part 54 Biologics Drugs Medical devices Reporting and recordkeeping requirements✧21 CFR Part 56 Human research subjects Reporting and recordkeeping requirements Safety✧21 CFR Part 58 Laboratories Reporting and recordkeeping requirements✧21 CFR Part 60 Administrative practice and procedure Drugs Food additives Inventions and patents Medicaldevices Reporting and recordkeeping requirements✧21 CFR Part 70 Color additives Cosmetics Drugs Labeling Packaging and containers✧21 CFR Part 71 Administrative practice and procedure Color additives Confidential business informationCosmetics Drugs Reporting and recordkeeping requirements✧21 CFR Part 73 Color additives Cosmetics Drugs Medical devices✧21 CFR Part 74 Color additives Cosmetics Drugs✧21 CFR Part 80 Color additives Cosmetics Drugs Reporting and recordkeeping requirements✧21 CFR Part 81 Color additives Cosmetics Drugs✧21 CFR Part 82 Color additives Cosmetics Drugs✧21 CFR Part 99 Administrative practice and procedure Biologics Drugs Medical devices Reporting andrecordkeeping requirements✧21 CFR Part 100 Administrative practice and procedure Food labeling Food packaging Foods Intergovernmentalrelations✧21 CFR Part 101 Food labeling Nutrition Reporting and recordkeeping requirements✧21 CFR Part 102 Beverages Food grades and standards Food labeling Frozen foods Oils and fats Onions PotatoesSeafood✧21 CFR Part 104 Food grades and standards Frozen foods Nutrition✧21 CFR Part 105 Dietary foods Food grades and standards Food labeling Infants and children✧21 CFR Part 106 Food grades and standards Infants and children Nutrition Reporting and recordkeepingrequirements✧21 CFR Part 107 Food labeling Infants and children Nutrition Reporting and recordkeeping requirements Signsand symbols✧21 CFR Part 108 Administrative practice and procedure Foods Reporting and recordkeeping requirements✧21 CFR Part 109 Food packaging Foods Polychlorinated biphenyls (PCB's)✧21 CFR Part 110 Food packaging Foods✧21 CFR Part 113 Food packaging Foods Reporting and recordkeeping requirements✧21 CFR Part 114 Food packaging Foods Reporting and recordkeeping requirements✧21 CFR Part 115 Eggs and egg products Foods✧21 CFR Part 119 Dietary foods Food additives Foods✧21 CFR Part 120 Foods Fruit juices Imports Reporting and recordkeeping requirements Vegetable juices✧21 CFR Part 129 Beverages Bottled water Food packaging Reporting and recordkeeping requirements✧21 CFR Part 130 Food additives Food grades and standards✧21 CFR Part 131 Cream Food grades and standards Milk Yogurt✧21 CFR Part 133 Cheese Food grades and standards Food labeling✧21 CFR Part 135 Food grades and standards Food labeling Frozen foods Ice cream✧21 CFR Part 136 Bakery products Food grades and standards✧21 CFR Part 137 Cereals (food) Food grades and standards✧21 CFR Part 139 Food grades and standards✧21 CFR Part 145 Food grades and standards Fruits✧21 CFR Part 146 Food grades and standards Fruit juices✧21 CFR Part 150 Food grades and standards Fruits✧21 CFR Part 152 Bakery products Food grades and standards Frozen foods Fruits✧21 CFR Part 155 Food grades and standards Vegetables✧21 CFR Part 156 Food grades and standards Vegetable juices✧21 CFR Part 158 Food grades and standards Frozen foods Vegetables✧21 CFR Part 160 Eggs and egg products Food grades and standards✧21 CFR Part 161 Food grades and standards Frozen foods Seafood✧21 CFR Part 163 Cacao products Food grades and standards✧21 CFR Part 164 Food grades and standards Nuts Peanuts✧21 CFR Part 165 Beverages Bottled water Food grades and standards✧21 CFR Part 166 Food grades and standards Food labeling Margarine✧21 CFR Part 168 Food grades and standards Sugar✧21 CFR Part 169 Food grades and standards Oils and fats Spices and flavorings✧21 CFR Part 170 Administrative practice and procedure Food additives Reporting and recordkeepingrequirements✧21 CFR Part 171 Administrative practice and procedure Food additives✧21 CFR Part 172 Food additives Reporting and recordkeeping requirements✧21 CFR Part 173 Food additives✧21 CFR Part 174 Food additives Food packaging✧21 CFR Part 175 Adhesives Food additives Food packaging✧21 CFR Part 176 Food additives Food packaging✧21 CFR Part 177 Food additives Food packaging✧21 CFR Part 178 Food additives Food packaging✧21 CFR Part 179 Food additives Food labeling Food packaging Radiation protection Reporting and recordkeepingrequirements Signs and symbols✧21 CFR Part 180 Food additives✧21 CFR Part 181 Food additives Food packaging✧21 CFR Part 182 Food additives Food packaging Spices and flavorings✧21 CFR Part 184 Food additives✧21 CFR Part 186 Food additives Food packaging✧21 CFR Part 189 Food additives Food packaging✧21 CFR Part 190 Food additives Reporting and recordkeeping requirements✧21 CFR Part 200 Drugs Prescription drugs✧21 CFR Part 201 Drugs Labeling Reporting and recordkeeping requirements✧21 CFR Part 202 Advertising Prescription drugs✧21 CFR Part 203 Labeling Prescription drugs Reporting and recordkeeping requirements Warehouses✧21 CFR Part 205 Intergovernmental relations Prescription drugs Reporting and recordkeeping requirementsSecurity measures Warehouses✧21 CFR Part 206 Drugs✧21 CFR Part 207 Drugs Reporting and recordkeeping requirements✧21 CFR Part 208 Labeling Prescription drugs Reporting and recordkeeping requirements✧21 CFR Part 210 Drugs Packaging and containers✧21 CFR Part 211 Drugs Labeling Laboratories Packaging and containers Prescription drugs Reporting andrecordkeeping requirements Warehouses✧21 CFR Part 216 Drugs Prescription drugs✧21 CFR Part 225 Animal drugs Animal feeds Labeling Packaging and containers Reporting and recordkeepingrequirements✧21 CFR Part 226 Animal drugs Animal feeds Labeling Packaging and containers Reporting and recordkeepingrequirements✧21 CFR Part 250 Drugs✧21 CFR Part 290 Drugs Labeling✧21 CFR Part 299 Drugs✧21 CFR Part 300 Drugs Prescription drugs✧21 CFR Part 310 Administrative practice and procedure Drugs Labeling Medical devices Reporting andrecordkeeping requirements✧21 CFR Part 312 Drugs Exports Imports Investigations Labeling Medical research Reporting and recordkeepingrequirements Safety✧21 CFR Part 314 Administrative practice and procedure Confidential business information Drugs Reporting andrecordkeeping requirements✧21 CFR Part 315 Biologics Drugs✧21 CFR Part 316 Administrative practice and procedure Drugs Reporting and recordkeeping requirements✧21 CFR Part 320 Drugs Reporting and recordkeeping requirements✧21 CFR Part 328 Alcohol and alcoholic beverages Labeling Over-the-counter drugs✧21 CFR Part 330 Over-the-counter drugs✧21 CFR Part 331 Labeling Over-the-counter drugs✧21 CFR Part 332 Labeling Over-the-counter drugs✧21 CFR Part 333 Labeling Over-the-counter drugs✧21 CFR Part 335 Labeling Over-the-counter drugs✧21 CFR Part 336 Labeling Over-the-counter drugs✧21 CFR Part 338 Labeling Over-the-counter drugs✧21 CFR Part 340 Labeling Over-the-counter drugs✧21 CFR Part 341 Labeling Over-the-counter drugs✧21 CFR Part 343 Labeling Over-the-counter drugs✧21 CFR Part 344 Labeling Over-the-counter drugs✧21 CFR Part 346 Labeling Over-the-counter drugs✧21 CFR Part 347 Labeling Over-the-counter drugs✧21 CFR Part 348 Labeling Over-the-counter drugs✧21 CFR Part 349 Labeling Ophthalmic goods and services Over-the-counter drugs✧21 CFR Part 350 Labeling Over-the-counter drugs✧21 CFR Part 352 Labeling Over-the-counter drugs✧21 CFR Part 355 Labeling Over-the-counter drugs✧21 CFR Part 357 Labeling Over-the-counter drugs Reporting and recordkeeping requirements✧21 CFR Part 358 Labeling Over-the-counter drugs✧21 CFR Part 361 Medical research Prescription drugs Radiation protection✧21 CFR Part 369 Labeling Medical devices Over-the-counter drugs✧21 CFR Part 429 Administrative practice and procedure Drugs Labeling Packaging and containers Reporting andrecordkeeping requirements✧21 CFR Part 500 Animal drugs Animal feeds Cancer Labeling Packaging and containers Polychlorinated biphenyls(PCB's)✧21 CFR Part 501 Animal foods Labeling Packaging and containers Reporting and recordkeeping requirements ✧21 CFR Part 502 Animal foods Labeling✧21 CFR Part 509 Animal foods Packaging and containers Polychlorinated biphenyls (PCB's)✧21 CFR Part 510 Administrative practice and procedure Animal drugs Labeling Reporting and recordkeepingrequirements✧21 CFR Part 511 Animal drugs Medical research Reporting and recordkeeping requirements✧21 CFR Part 514 Administrative practice and procedure Animal drugs Confidential business information Reportingand recordkeeping requirements✧21 CFR Part 515 Administrative practice and procedure Animal drugs Confidential business information Reportingand recordkeeping requirements✧21 CFR Part 520 Animal drugs✧21 CFR Part 522 Animal drugs✧21 CFR Part 524 Animal drugs✧21 CFR Part 526 Animal drugs✧21 CFR Part 529 Animal drugs✧21 CFR Part 530 Administrative practice and procedure Advertising Animal drugs Labeling Reporting andrecordkeeping requirements✧21 CFR Part 556 Animal drugs Foods✧21 CFR Part 558 Animal drugs Animal feeds✧21 CFR Part 570 Animal feeds Animal foods Food additives✧21 CFR Part 571 Administrative practice and procedure Animal feeds Animal foods Food additives✧21 CFR Part 573 Animal feeds Food additives✧21 CFR Part 579 Animal feeds Animal foods Radiation protection✧21 CFR Part 582 Animal feeds Animal foods Food additives✧21 CFR Part 584 Animal feeds Food additives✧21 CFR Part 589 Animal feeds Animal foods Food additives✧21 CFR Part 600 Biologics Reporting and recordkeeping requirements✧21 CFR Part 601 Administrative practice and procedure Biologics Confidential business information✧21 CFR Part 606 Blood Labeling Laboratories Reporting and recordkeeping requirements✧21 CFR Part 607 Blood✧21 CFR Part 610 Biologics Labeling Reporting and recordkeeping requirements✧21 CFR Part 630 Blood Reporting and recordkeeping requirements✧21 CFR Part 640 Blood Labeling Reporting and recordkeeping requirements✧21 CFR Part 660 Biologics Labeling Reporting and recordkeeping requirements✧21 CFR Part 680 Biologics Blood Reporting and recordkeeping requirements✧21 CFR Part 700 Cosmetics Packaging and containers✧21 CFR Part 701 Cosmetics Labeling Reporting and recordkeeping requirements✧21 CFR Part 710 Cosmetics✧21 CFR Part 720 Confidential business information Cosmetics✧21 CFR Part 740 Cosmetics Labeling✧21 CFR Part 800 Administrative practice and procedure Medical devices Ophthalmic goods and servicesPackaging and containers Reporting and recordkeeping requirements✧21 CFR Part 801 Labeling Medical devices Reporting and recordkeeping requirements✧21 CFR Part 803 Imports Medical devices Reporting and recordkeeping requirements✧21 CFR Part 806 Imports Medical devices Reporting and recordkeeping requirements✧21 CFR Part 807 Confidential business information Imports Medical devices Reporting and recordkeepingrequirements✧21 CFR Part 808 Intergovernmental relations Medical devices✧21 CFR Part 809 Labeling Medical devices✧21 CFR Part 810 Administrative practice and procedure Medical devices Reporting and recordkeepingrequirements✧21 CFR Part 812 Health records Medical devices Medical research Reporting and recordkeeping requirements ✧21 CFR Part 814 Administrative practice and procedure Confidential business information Medical devices Medicalresearch Reporting and recordkeeping requirements✧21 CFR Part 820 Medical devices Reporting and recordkeeping requirements✧21 CFR Part 821 Imports Medical devices Reporting and recordkeeping requirements✧21 CFR Part 822 Medical devices Reporting and recordkeeping requirements✧21 CFR Part 860 Administrative practice and procedure Medical devices✧21 CFR Part 861 Administrative practice and procedure Medical devices Reporting and recordkeepingrequirements✧21 CFR Part 862 Medical devices✧21 CFR Part 864 Blood Medical devices Packaging and containers✧21 CFR Part 866 Biologics Laboratories Medical devices✧21 CFR Part 868 Medical devices✧21 CFR Part 870 Medical devices✧21 CFR Part 872 Medical devices✧21 CFR Part 874 Medical devices✧21 CFR Part 876 Medical devices✧21 CFR Part 878 Medical devices✧21 CFR Part 880 Medical devices✧21 CFR Part 882 Medical devices✧21 CFR Part 884 Medical devices✧21 CFR Part 886 Medical devices Ophthalmic goods and services✧21 CFR Part 888 Medical devices✧21 CFR Part 890 Medical devices✧21 CFR Part 892 Medical devices Radiation protection X-rays✧21 CFR Part 895 Administrative practice and procedure Labeling Medical devices✧21 CFR Part 898 Administrative practice and procedure Medical devices✧21 CFR Part 900 Electronic products Health facilities Medical devices Radiation protection Reporting andrecordkeeping requirements X-rays✧21 CFR Part 1000 Electronic products Radiation protection Reporting and recordkeeping requirements X-rays ✧21 CFR Part 1002 Electronic products Radiation protection Reporting and recordkeeping requirements✧21 CFR Part 1003 Administrative practice and procedure Electronic products Radiation protection✧21 CFR Part 1004 Electronic products Radiation protection✧21 CFR Part 1005 Administrative practice and procedure Electronic products Imports Radiation protection Suretybonds✧21 CFR Part 1010 Administrative practice and procedure Electronic products Exports Radiation protection✧21 CFR Part 1020 Electronic products Medical devices Radiation protection Reporting and recordkeepingrequirements Television X-rays✧21 CFR Part 1030 Electronic products Microwave ovens Radiation protection✧21 CFR Part 1040 Electronic products Labeling Lasers Medical devices Radiation protection Reporting andrecordkeeping requirements✧21 CFR Part 1050 Electronic products Medical devices Radiation protection✧21 CFR Part 1210 Administrative practice and procedure Imports Milk Public health Reporting and recordkeepingrequirements✧21 CFR Part 1220 Administrative practice and procedure Customs duties and inspection Imports Public healthTea✧21 CFR Part Administrative practice and procedure Imports Labeling Packaging and containers Poison prevention ✧21 CFR Part 1240 Communicable diseases Public health Travel restrictions Water supply✧21 CFR Part 1250 Air carriers Foods Maritime carriers Motor carriers Public health Railroads Water supply✧21 CFR Part 1270 Communicable diseases HIV/AIDS Reporting and recordkeeping requirements✧21 CFR Part 1271 Biologics Drugs Human cells and tissue-based products Medical devices Reporting andrecordkeeping requirements✧21 CFR Part 1300 Chemicals Drug traffic control✧21 CFR Part 1301 Administrative practice and procedure Drug traffic control Security measures✧21 CFR Part 1302 Drug traffic control Exports Imports Labeling Packaging and containers✧21 CFR Part 1303 Administrative practice and procedure Drug traffic control✧21 CFR Part 1304 Drug traffic control Reporting and recordkeeping requirements✧21 CFR Part 1305 Drug traffic control✧21 CFR Part 1306 Drug traffic control Prescription drugs✧21 CFR Part 1307 Drug traffic control。
21cfr211.72
Food and Drug Administration, HHS§211.72shall not come into contact with com-ponents, drug product containers, clo-sures, in-process materials, or drug products so as to alter the safety, iden-tity, strength, quality, or purity of the drug product beyond the official or other established requirements.§211.67Equipment cleaning and main-tenance.(a) Equipment and utensils shall be cleaned, maintained, and sanitized at appropriate intervals to prevent mal-functions or contamination that would alter the safety, identity, strength, quality, or purity of the drug product beyond the official or other established requirements.(b) Written procedures shall be estab-lished and followed for cleaning and maintenance of equipment, including utensils, used in the manufacture, processing, packing, or holding of a drug product. These procedures shall include, but are not necessarily limited to, the following:(1) Assignment of responsibility for cleaning and maintaining equipment; (2) Maintenance and cleaning sched-ules, including, where appropriate, sanitizing schedules;(3) A description in sufficient detail of the methods, equipment, and mate-rials used in cleaning and maintenance operations, and the methods of dis-assembling and reassembling equip-ment as necessary to assure proper cleaning and maintenance;(4) Removal or obliteration of pre-vious batch identification;(5) Protection of clean equipment from contamination prior to use;(6) Inspection of equipment for clean-liness immediately before use.(c) Records shall be kept of mainte-nance, cleaning, sanitizing, and inspec-tion as specified in §§211.180 and 211.182.§211.68Automatic, mechanical, and electronic equipment.(a) Automatic, mechanical, or elec-tronic equipment or other types of equipment, including computers, or re-lated systems that will perform a func-tion satisfactorily, may be used in the manufacture, processing, packing, and holding of a drug product. If such equipment is so used, it shall be rou-tinely calibrated, inspected, or checked according to a written program de-signed to assure proper performance. Written records of those calibration checks and inspections shall be main-tained.(b) Appropriate controls shall be ex-ercised over computer or related sys-tems to assure that changes in master production and control records or other records are instituted only by author-ized personnel. Input to and output from the computer or related system of formulas or other records or data shallbe checked for accuracy. The degree and frequency of input/output verification shall be based on the com-plexity and reliability of the computeror related system. A backup file of data entered into the computer or related system shall be maintained except where certain data, such as calcula-tions performed in connection with lab-oratory analysis, are eliminated by computerization or other automated processes. In such instances a written record of the program shall be main-tained along with appropriate valida-tion data. Hard copy or alternative sys-tems, such as duplicates, tapes, or microfilm, designed to assure that backup data are exact and complete and that it is secure from alteration, inadvertent erasures, or loss shall be maintained.[43 FR 45077, Sept. 29, 1978, as amended at 60FR 4091, Jan. 20, 1995]§211.72Filters.Filters for liquid filtration used in the manufacture, processing, or pack-ing of injectable drug products in-tended for human use shall not release fibers into such products. Fiber-releas-ing filters may not be used in the man-ufacture, processing, or packing of these injectable drug products unless itis not possible to manufacture such drug products without the use of such filters. If use of a fiber-releasing filteris necessary, an additional non-fiber-releasing filter of 0.22 micron max-imum mean porosity (0.45 micron if the manufacturing conditions so dictate) shall subsequently be used to reduce the content of particles in the injectable drug product. Use of an as-bestos-containing filter, with or with-out subsequent use of a specific non-fiber-releasing filter, is permissible21 CFR Ch. I (4–1–03 Edition)§211.80only upon submission of proof to the appropriate bureau of the Food and Drug Administration that use of a non-fiber-releasing filter will, or is likely to, compromise the safety or effective-ness of the injectable drug product. Subpart E—Control of Compo-nents and Drug Product Con-tainers and Closures§211.80General requirements.(a) There shall be written procedures describing in sufficient detail the re-ceipt, identification, storage, handling, sampling, testing, and approval or re-jection of components and drug prod-uct containers and closures; such writ-ten procedures shall be followed.(b) Components and drug product containers and closures shall at all times be handled and stored in a man-ner to prevent contamination.(c) Bagged or boxed components of drug product containers, or closures shall be stored off the floor and suit-ably spaced to permit cleaning and in-spection.(d) Each container or grouping of containers for components or drug product containers, or closures shall be identified with a distinctive code for each lot in each shipment received. This code shall be used in recording the disposition of each lot. Each lot shall be appropriately identified as to its status (i.e., quarantined, approved, or rejected).§211.82Receipt and storage of untest-ed components, drug product con-tainers, and closures.(a) Upon receipt and before accept-ance, each container or grouping of containers of components, drug prod-uct containers, and closures shall be examined visually for appropriate la-beling as to contents, container dam-age or broken seals, and contamina-tion.(b) Components, drug product con-tainers, and closures shall be stored under quarantine until they have been tested or examined, as appropriate, and released. Storage within the area shall conform to the requirements of §211.80.§211.84Testing and approval or rejec-tion of components, drug productcontainers, and closures.(a) Each lot of components, drug product containers, and closures shall be withheld from use until the lot has been sampled, tested, or examined, as appropriate, and released for use by the quality control unit.(b) Representative samples of each shipment of each lot shall be collected for testing or examination. The num-ber of containers to be sampled, and the amount of material to be taken from each container, shall be based upon appropriate criteria such as sta-tistical criteria for component varia-bility, confidence levels, and degree of precision desired, the past quality his-tory of the supplier, and the quantity needed for analysis and reserve where required by §211.170.(c) Samples shall be collected in ac-cordance with the following proce-dures:(1) The containers of components se-lected shall be cleaned where nec-essary, by appropriate means.(2) The containers shall be opened, sampled, and resealed in a manner de-signed to prevent contamination of their contents and contamination of other components, drug product con-tainers, or closures.(3) Sterile equipment and aseptic sampling techniques shall be used when necessary.(4) If it is necessary to sample a com-ponent from the top, middle, and bot-tom of its container, such sample sub-divisions shall not be composited for testing.(5) Sample containers shall be identi-fied so that the following information can be determined: name of the mate-rial sampled, the lot number, the con-tainer from which the sample was taken, the date on which the sample was taken, and the name of the person who collected the sample.(6) Containers from which samples have been taken shall be marked to show that samples have been removed from them.(d) Samples shall be examined and tested as follows:(1) At least one test shall be con-ducted to verify the identity of each component of a drug product. Specific。
数据库中的数据完整性约束与控制
数据库中的数据完整性约束与控制在数据库管理系统中，数据的完整性是非常重要的一种特性。
它确保数据库中存储的数据的准确性、有效性和一致性。
为了实现数据的完整性，数据库管理系统提供了一系列的数据完整性约束与控制。
一、数据完整性约束的定义数据完整性约束是一种规则或限制，用于确保数据库中的数据满足指定的条件。
数据完整性约束可以应用于表、列、或者整个数据库。
1. 实体完整性约束(entity integrity constraint)实体完整性约束是保证主键的一致性和唯一性的约束条件。
它规定了每个实体（表）必须具有一个主键，并且该主键值不能为NULL或为空值。
实体完整性约束可以通过主键、唯一索引和非为空值约束来实现。
2. 参照完整性约束(referential integrity constraint)参照完整性约束用于确保数据库中关系表之间的参照一致性。
通过参照完整性约束，确保了外键的一致性和有效性。
当我们在一个表中创建外键时，参照完整性约束确保外键的值在被引用表（主表）中必须存在，否则将引发错误。
3. 域完整性约束(domain integrity constraint)域完整性约束用于限制数据类型和范围。
通常，它们被应用于表的列上，以确保只有指定的数据类型可以存储在这些列中。
例如，我们可以将域完整性约束应用于限制日期列的取值范围或限制字符串列的长度。
4. 用户定义完整性约束(user-defined integrity constraint)用户定义完整性约束允许用户根据具体的需求定义其他的约束条件，以保证数据的一致性和正确性。
这些约束可以通过触发器、存储过程等方式来实现。
二、数据完整性约束的控制方法为了确保数据库中的数据完整性，我们需要采取一些控制方法。
1. 触发器(triggers)触发器是一段存储在数据库中的特殊程序，它在特定的数据库事件（例如插入、更新或删除操作）发生时自动执行。
可以在触发器中编写逻辑，用于在数据被修改之前或之后进行自定义操作，以确保数据满足约束条件。
fda21cfrqsr820中英文对照版本
21 CFR § 820Quality System Regulation质量体系手册Subpart A—General ProvisionsA部分——总则Scope.范围Definitions.定义Quality system.质量体系Subpart B—Quality System Requirements B部分——质量体系要求Management responsibility.管理者职责Quality audit.质量审核Personnel.职员Subpart C—Design ControlsC部分——设计控制Design controls.设计控制Subpart D—Document ControlsD部分——文件控制Document controls.文件控制Subpart E—Purchasing ControlsE部分——采购控制 Purchasing controls.采购控制Subpart F—Identification and TraceabilityF部分——标识和可追溯性Identification.标识Traceability.可追溯性Subpart G—Production and Process ControlsG部分——生产和过程控制Production and process controls.产品和过程控制,measuring, and test equipment.检查、测量，测试仪器Process validation.过程确认Subpart H—Acceptance ActivitiesH部分—接收活动Receiving, in-process, and finished device acceptance.接收设备准则，过程设备准则，最终设备准则Acceptance status.接收状态Subpart I—Nonconforming ProductI部分——不合格产品Nonconforming product.不合格产品Subpart J—Corrective and Preventive ActionJ部分——纠正预防措施Corrective and preventive action.纠正预防措施Subpart K—Labeling and Packaging ControlK部分——标签与包装控制Device labeling.产品标识Device packaging.产品包装Subpart L—Handling, Storage, Distribution, and InstallationL部分——操作、存储、分配以及安装Handling.操作Storage.存储Distribution.分配 Installation.安装Subpart M—RecordsM部分——记录General requirements.总要求Device master record.产品控制记录Device history record.产品历史记录Quality system record.质量体系记录Complaint files.客户抱怨文件Subpart N—ServicingServicing.Subpart O—Statistical TechniquesStatistical techniques.Authority: Secs. 501, 502, 510, 513, 514, 515, 518, 519, 520, 522, 701, 704, 801, 803 of the Federal Food, Drug, and Cosmetic Act (21 351, 352, 360, 360c, 360d, 360e, 360h, 360i, 360j, 360l, 371, 374, 381, 383).Subpart A—General Provisions................................... 错误!未定义书签。
goldendb 存储过程写法
goldendb 存储过程写法English Answer:Goldendb is a high-performance distributed databasethat supports stored procedures. Stored procedures are a set of SQL statements that are stored in the database and can be executed as a single unit. They can be used to perform complex operations, such as data manipulation, data retrieval, and data validation.Creating a Stored Procedure.To create a stored procedure in Goldendb, you can use the following syntax:sql.CREATE PROCEDURE [schema_name].[procedure_name]AS.BEGIN.-SQL statements.END;The `schema_name` is the name of the schema in which the stored procedure will be created. The `procedure_name` is the name of the stored procedure. The `SQL statements` are the SQL statements that will be executed when the stored procedure is called.Calling a Stored Procedure.To call a stored procedure, you can use the following syntax:sql.EXEC [schema_name].[procedure_name];The `schema_name` is the name of the schema in which the stored procedure is stored. The `procedure_name` is the name of the stored procedure.Example.The following is an example of a stored procedure that can be used to get the current time:sql.CREATE PROCEDURE [dbo].[GetTime]AS.BEGIN.SELECT GETDATE();END;To call this stored procedure, you can use thefollowing syntax:sql.EXEC [dbo].[GetTime];Advantages of Stored Procedures.Stored procedures offer a number of advantages, including:Improved performance: Stored procedures can be compiled and cached, which can improve performance.Increased security: Stored procedures can be used to restrict access to data and operations.Reduced code duplication: Stored procedures can be used to reduce code duplication by encapsulating common tasks.Improved maintainability: Stored procedures can beeasier to maintain than SQL statements that are spread across multiple locations.中文回答：Goldendb 存储过程。
sqlserver数据库查询存储过程
sqlserver数据库查询存储过程英文版SQL Server Database Querying with Stored ProceduresIn the realm of database management systems, SQL Server stands tall as a reliable and powerful tool. Among its numerous features, stored procedures are a noteworthy aspect that enhances the efficiency and organization of database operations. Stored procedures are pre-compiled sets of SQL statements that can be stored in the database and called upon when needed, much like functions in programming languages.Advantages of Stored Procedures:Performance Boost: Since stored procedures are pre-compiled, they execute much faster than ad-hoc SQL queries.Code Reusability: They can be reused across multiple applications or even within the same application, reducing redundant code.Security: By limiting access to the underlying data, stored procedures provide a layer of security.Maintenance: Changes made to a stored procedure affect all its invocations, making maintenance easier.Querying with Stored Procedures in SQL Server:Querying a SQL Server database using stored procedures involves several steps:Step 1: Creating a Stored ProcedureTo create a stored procedure, you need to use the CREATE PROCEDURE statement followed by the procedure name and the SQL statements that define the procedure. For example: sqlCopy CREATE PROCEDURE GetEmployeeDetails@EmployeeID INTASBEGINSELECT * FROM Employees WHERE ID = @EmployeeIDENDCREATE PROCEDURE GetEmployeeDetails@EmployeeID INTASBEGINSELECT * FROM Employees WHERE ID = @EmployeeID ENDIn this example, GetEmployeeDetails is the name of the stored procedure, and @EmployeeID is a parameter that accepts an integer value. The SELECT statement fetches the details of an employee based on the provided EmployeeID.Step 2: Executing the Stored ProcedureTo execute the stored procedure, you use the EXEC command followed by the procedure name and any required parameters. For the above example:sqlCopy EXEC GetEmployeeDetails @EmployeeID = 1EXEC GetEmployeeDetails @EmployeeID = 1This command will execute the GetEmployeeDetails stored procedure and return the details of the employee with an ID of 1.Conclusion:Stored procedures in SQL Server are a powerful tool for organizing and optimizing database queries. They provide better performance, code reusability, and security, making them an integral part of any database-driven application. By understanding how to create and execute stored procedures, database administrators and developers can leverage the full potential of SQL Server and ensure efficient and secure data access.中文版SQL Server 数据库使用存储过程进行查询在数据库管理系统的领域中，SQL Server 作为一个可靠且强大的工具备受推崇。
美国法典简介-21 CFR结构内容
21 CFR结构内容在美国，参议员或众议员每年都会提出很多议案（bill）。
议案交由委员会审议修订，然后在国会议院投票，一院投票通过后，送交另一院，两院通过后交总统签署；若总统不否决，或虽否决但经两院2/3议员重新通过，即正式成为法律（law）。
法律规定了基本的准则，并由联邦政府执行机构根据具体的法规（Regulation）来执行。
《美国联邦法规》（Code of FederalRegulations, CFR）是将发表在“联邦公报（Federal Register, FR）”的一般性和永久性法规集合成册的法规典籍。
CFR的法规涵盖各方面主题，其中第21篇“食品与药品”就是美国食品药品监督管理局（Food and DrugAdministration, FDA）管理食品和药品的主要法规依据。
所谓知己知彼，百战不殆。
做好国际药品注册，需要从其法规源头入手。
本文带大家了解并熟悉CFR，尤其是第21篇“食品与药品”的结构以及内容。
1. CFR的结构与分类CFR共50篇（title），代表联邦法规涉及的领域和调整对象，有些篇有分篇（subtitle）；按前后顺序排列分别是：总则、保留、总统、会计、行政人事、保留、农业、外国人与公民、动物与动物产品、能源、联邦选举、银行金融、商业信用与资助、航空与航天、商业与外贸、商业实践、商品与证券交易、电力，水力资源保护、关税、雇员利益、食品与药品、对外关系、公路、住宅与城市发展、印第安人、国内收入、烟，酒产品与军火、司法行政、劳动、矿产资源、财政金融、国家防御、航运与可航水域、教育、巴拿马运河、公园，森林和公共财产、专利，商标与版权、抚恤金，津贴和老兵救助、邮政服务、环境保护、公共合同与财产管理、公共卫生、公共土地、抢险救灾、公共福利、航运、电讯、联邦收购规则系统、交通、野生动物与渔业。
每篇分为若干章（chapter），通常以法规颁发机构的名称为标题，有的章还分为分章（subchapter）；每章包含特定的法规领域的若干部分（part），有的部分由于内容多，又分为分部（subpart）。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
others can be declared inside of a stored procedure
these can either be used inside of the SP only, or
returned back to the user
DECLARE @someVar INTEGER
CREATE PROCEDURE dbo.AddToOrder (@partNum AS VARCHAR(20),
@serialNum AS VARCHAR(20),
@orderNumber AS INTEGER)
EXEC dbo.AddToOrder ‘myPart’, ‘mySerial’, 20; EXEC dbo.AddToOrder @partNum = ‘myPart’, @serialNum = ‘mySerial’, @orderNumber = 20;
CREATE PROCEDURE dbo.AddToOrder (@partNum AS VARCHAR(20),
@serialNum AS VARCHAR(20),
@orderNumber AS INTEGER) AS DECLARE @currentCount INTEGER SELECT @currentCount = (SELECT availableNumberOfParts FROM myInventory WHERE partNum = @partNum) IF @currentCount > 0 BEGIN UPDATE myInventory SET availableNumberOfParts = @currentCount – 1 INSERT INTO myOrder VALUES (@partNum, @serialNum, @orderNumber) END;
IF @year = 2011 BEGIN IF @quarter = 4 BEGIN -- DO SOMETHING END ELSE BEGIN -- DO SOMETHING ELSE END END ELSE BEGIN -- DO SOMETHING ELSE END
SELECT @year = CASE WHEN (@quarter + 1) > 4 THEN @year + 1 ELSE @year END
SELECT @month = CASE WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt WHEN @monthInt END
error handling
any of these is a valid output for SPs:
nothing
out parameters (none, one or multiple) a table
implementation and execution SQL varies between
DROP PROCEDURE dbo.someProc;
GRANT EXECUTE ON dbo.myPON dbo.myTableProc TO myUser;
we’ll cover loops when talking about cursors
is coming up shortly
CREATE PROCEDURE dbo.GetPeopleByLastName
(@LastName VARCHAR(50)) AS SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName ORDER BY ContactID;
faster, because they are precompiled less network traffic
example – you can use them to
SELECT/INSERT/UPDATE/DELETE data
user does not get direct access to tables you can validate data, and only proceed if your conditions are
platforms… debugging them is not as straightforward as a .NET app in the real-world, most of the business logic is stored in the application itself, not in the DB; if you store some logic in the DB as well, it becomes harder to debug
stored in DB data dictionary
security
better access control better protection from SQL injection attacks, if implemented
correctly
performance:
met preventing SQL injection improving performance
Stored Procs can include any of the following:
parameters (incoming/outgoing/local)
decision logic (if/then/case) loops (while, loop, repeat)
up to this point, the DB was only used to store data
stored procedures allow us to store data processing as
well
procedures/subroutines/… chunks of SQL code
SET @year = 2011 SELECT @year = 2011 SELECT @year = someFunction()
use data
SELECT * FROM myTable WHERE userId = @userID
IF @year = 2011 BEGIN -- DO SOMETHING END ELSE BEGIN -- DO SOMETHING ELSE END
= = = = = = = = = = = =
1 THEN ‘Jan’ 2 THEN ‘Feb’ 3 THEN ‘Mar’ 4 THEN ‘Apr’ 5 THEN ‘May’ 6 THEN ‘Jun’ 7 THEN ‘Jul’ 8 THEN ‘Aug’ 9 THEN ‘Sep’ 10 THEN ‘Oct’ 11 THEN ‘Nov’ 12 THEN ‘Dec’
assign data
SELECT @someParam = (SELECT COUNT(*) FROM
myTable) SELECT @someParam = COUNT(*) FROM myTable SELECT TOP 1 @result = myColumn FROM myTable
they can be classified as IN (default) or OUT
CREATE PROCEDURE dbo.AddToOrder (@partNum AS VARCHAR(20) IN, @serialNum AS VARCHAR(20) IN, @orderNumber AS INTEGER IN, @success AS INTEGER OUT, @remainingQuantity AS INTEGER OUT)
some parameters are included in a stored procedure
signature:
CREATE PROCEDURE dbo.AddToOrder (@partNum AS VARCHAR(20), @serialNum AS VARCHAR(20),
@orderNumber AS INTEGER)