21 - 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')。

✧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。

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)触发器是一段存储在数据库中的特殊程序，它在特定的数据库事件（例如插入、更新或删除操作）发生时自动执行。

可以在触发器中编写逻辑，用于在数据被修改之前或之后进行自定义操作，以确保数据满足约束条件。

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 存储过程写法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数据库查询存储过程英文版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结构内容在美国，参议员或众议员每年都会提出很多议案（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）。

sql server profiler中常用的跟踪事件一、Errors and Warnings: Exception 和 Errors and Warnings: User Error Message异常和用户错误信息一起出现，一般都一起跟踪这两个类。

当出现用户异常时，这两个事件就会被激发。

异常事件包含错误数、严重性和状态，而用户错误信息事件包含错误的实际文本。

二、Locks: Deadlock graph 和 Locks: Lock:Deadlock Chain在SQL Server 2005之前的版本，死锁只能通过Deadlock Chain事件识别出来。

SQL Server 2005之后引入了更多可用的Deadlock graph事件，这个事件生成标准XML，性能分析器可以将其呈现为非常清晰的图形输出。

三、Locks: Lock:Acquired 、 Locks: Lock:Released 和 Locks: Lock:Escalation主要在解决死锁的同时使用这些事件，使用户知道在一个事务期间SQL Server用了什么锁，以及这些锁被保持了多长时间。

如果用户对SQL Server各种隔离级别的运转感兴趣，可以监视这些事件。

使用这些事件时，要确保对特定的目标spid进行过滤，以免得到太多的信息而不方便处理。

四、Security Audit: Audit Login 和 Security Audit: Audit Logout1、这两个事件每天都会用到，尤其是在做性能调校时十分有用。

通过监视这两个事件及存储过程和T-SQL类别中的各种查询事件，用户可以更方便地在单个会话的基础上积聚信息。

2、要检测被激发的事件是否基于一个汇集连接，可以查看EventSubClass列的值是否为2。

五、Stored Procedures: SP:StmtStarting 和 Stored Procedures: SP:StmtCompleted1、在一个复杂的充满了流程控制语句的存储过程中，有时很难确定到底选择了哪条访问路径。

VALIDATION OF COMPENDIAL PROCEDURES 药典方法的验证Test procedures for assessment of the quality levels of pharmaceutical articles are subject to various requirements. According to Section 501 of the Federal Food, Drug, and Cosmetic Act, assays and specifications in monographs of the United States Pharmacopeia and the National Formulary constitute legal standards. The Current Good Manufacturing Practice regulations [21 CFR 211.194(a)] require that test methods, which are used for assessing compliance of pharmaceutical articles with established specifications, must meet proper standards of accuracy and reliability. Also, according to these regulations [21 CFR 211.194(a)(2)], users of analytical methods describedin USP–NF are not required to validate the accuracy and reliability of these methods, but merely verify their suitability under actual conditions of use. Recognizing the legal status of USP and NF standards, it is essential, therefore, that proposals for adoption of new or revised compendial analytical procedures be supported by sufficient laboratory data to document their validity.用于评估药品质量的检验方法需要满足不同的要求。

程序文件英语怎么说程序文件是在质量管理体系中质量手册的下一级文件层次，规定某项工作的一般过程。

那么你知道程序文件用英语怎么说吗?接下来跟着店铺来学习一下吧。

程序文件的英语说法：program file程序文件相关英语表达：可程序文件 executable file质量程序文件 Quality Program Files司法程序文件 judicial process民事法律程序文件 civil process原诉法律程序文件 originating process程序文件的英语例句：1. It is possible to attach executable program files to e-mail.可以在电子邮件里附上可执行程序文件。

2. This procedure is called program documentation.这个过程叫做程序文件.3. You should see the OpenOffice. org icon in the Applications folder.您应该在应用程序文件夹里看到OpenOffice. org图标.4. Receive special training from company, know BOS, and can operate in work.受过公司的专门培训, 了解BOS相关程序文件, 并能在工作中运用.5. SmartUpdate failed JAR archive has no installer file information.灵活更新失败JAR归档中没有安装程序文件信息.6. Update Procedure base on audit results if it is necessary.编制审核报告,并提出更新程序文件的建议.7. Responsible for ensuring all procedure documents andworking instruction being followed closely.负责确保所有程序文件和工作指导书被严格执行.8. MDCL has documented procedure to control the purchasing activity.本公司已制定程序文件对采购活动进行有效的控制.9. Factory has documented Quality procedures ( QP ) at each stage of operation.工厂是否在每一个生产操作阶段都有质量程序文件.10. In the Select Item in Project dialog box, double - click the Application Folder.在“选择项目中的项”对话框中, 双击“应用程序文件夹”.11. A resource is data that is stored in the program file.资源是储存在程序文件中的数据.12. Quick menu where you can even link outside programs, files, anything!快速菜单那里你甚至可以连结以外的程序, 文件, 什么!13. Installs the program files you need to use desktop themes.安装您需要的程序文件以便使用桌面主题.14. Responsible for implementing and administering IPMT HSE procedures applicable to the project.负责贯彻和执行烯烃项目一体化管理组编制的适用于本项目的HSE程序文件.15. Gets the file path to the generated primary bootstrapper file.获取生成的主引导程序文件的文件路径.。

sqlsugar procedure 参数顺序Topic: Understanding the Order of Parameters in SQL Sugar ProceduresIntroduction:SQLSugar is an open-source ORM (Object-Relational Mapping) framework for .NET that aims to simplify database programming. One of its key features is the ability to handle stored procedures that are widely used in database management systems. In this article, we will explore the order of parameters in SQLSugar procedures and understand how to work with them effectively.I. Importance of Parameters in SQLSugar ProceduresStored procedures in SQLSugar allow database administrators to define and execute complex routines in the database. These procedures can accept input parameters, which provide flexibility for customizing query results or modifying data. The order of parameters plays a crucial role in correctly passing values to these procedures.II. Syntax of SQLSugar ProceduresBefore diving into the parameter order, let's understand the basicsyntax of SQLSugar procedures:1. Creation of a SQLSugar procedure:C#SqlSugar.StoredProcedureQuery("procedure_name")2. Adding parameters to a SQLSugar procedure:C#.AddParameters(new { parameter1 = value1, parameter2 = value2, ... })3. Executing the SQLSugar procedure:C#.Insert();III. Correct Order of ParametersWhen passing parameters to a SQLSugar procedure, it is necessary to follow the correct order to ensure that the values are correctly assigned. The order is determined by the order in which the parameters are defined in the database procedure itself.To determine the order of parameters, you can refer to the stored procedure's definition in the database or consult the database administrator. If you are using an existing procedure, you can examine the stored procedure code or documentation.IV. Handling Parameters in SQLSugar ProceduresLet's now explore how to correctly handle parameters in SQLSugar procedures:1. Mapping C# objects to stored procedure parameters: SQLSugar allows us to map C# objects directly to the stored procedure parameters. Here's an example:C#class User{public int Id { get; set; }public string Name { get; set; }}User user = new User { Id = 1, Name = "John" };SqlSugar.StoredProcedureQuery("InsertUser").AddParameters(user).Insert();In this example, the properties of the `User` object are mapped to the parameters of the "InsertUser" stored procedure.2. Using anonymous objects for parameter mapping: Anonymous objects can also be used to map parameters. Here's an example:C#SqlSugar.StoredProcedureQuery("InsertUser").AddParameters(new { Id = 1, Name = "John" }).Insert();In this case, the anonymous object properties are mapped to the parameters of the "InsertUser" stored procedure.V. Importance of Parameter NamesWhile the order of parameters is essential in SQLSugarprocedures, the parameter names themselves hold significance. SQLSugar matches parameter names at runtime to assign values correctly. Therefore, it is crucial to ensure that the parameter names in the SQL Sugar procedure syntax match the names defined in the stored procedure.VI. ConclusionUnderstanding the order of parameters in SQLSugar procedures is essential for successfully working with stored procedures in database management systems. By following the correct order and mapping parameter values correctly, developers can leverage the full power of SQLSugar for efficient database programming.Remember to consult the stored procedure's definition or documentation to determine the accurate order of parameters. Utilize the mapping features of SQLSugar to conveniently pass parameters, either by mapping C# objects or using anonymous objects. Lastly, ensure that the parameter names match between the SQLSugar procedure syntax and the stored procedure for seamless execution.By adhering to these guidelines, developers can harness thecapabilities of SQLSugar and simplify the implementation of complex database routines.。

PART 211 211部分- CURRENT GOOD MANUFACTURING PRACTICE FOR—制剂药品的CGMPFINISHED PHARMACEUTICALSSubpart A--General Provisions A.总 则§211.1 - Scope.211∙1范围§211.3 - Definitions.211∙3定义Subpart B--Organization and Personnel B．组织与人员§211.22 - Responsibilities of quality control unit.211∙22质量控制部门的职责§211.25 - Personnel qualifications.211∙25人员资格§211.28 - Personnel responsibilities.211∙28人员职责§211.34 - Consultants.211∙34顾问Subpart C--Buildings and Facilities C．厂房和设施§211.42 - Design and construction features.211∙42设计与建造特征§211.44 - Lighting.211∙44照明§211.46 - Ventilation, air filtration, air heating and211∙46通风、空气过滤、空气加热与冷却§211.48 - Plumbing.211∙48管件§211.50 - Sewage and refuse.211∙50污水和废料§211.52 - Washing and toilet facilities.211∙52洗涤和盥洗设备§211.56 - Sanitation.211∙56卫生§211.58 - Maintenance.211∙58保养Subpart D--Equipment D．设备§211.63 - Equipment design, size, and location.211∙63设备的设计、尺寸及位置§211.65 - Equipment construction.211∙65设备构造§211.67 - Equipment cleaning and maintenance.211∙67设备清洁与保养§211.68 - Automatic, mechanical, and electronic211∙68自动化设备、机械化设备和电子设备equipment.§211.72 - Filters.211∙72过滤器Subpart E--Control of Components and DrugE．成分、药品容器和密封件控制Product Containers and Closures§211.80 - General requirements.211∙80总要求§211.82 - Receipt and storage of untested211∙82未检验的成份、药品容器和密封件的接收与贮存components, drug product containers, and closures.§211.84 - Testing and approval or rejection of211∙84成份、药品容器和密封件的试验、批准或拒收components, drug product containers, and closures.§211.86 - Use of approved components, drug product211∙86获准的成份、药品容器和密封件的使用containers, and closures.§211.87 - Retesting of approved components, drug211∙87获准的成份、药品容器和密封件的复检product containers, and closures.§ 211.89 - Rejected components, drug product211∙89拒收的成份、药品容器和密封件containers, and closures.§ 211.94 - Drug product containers and closures.211∙94药品密封容器和密封件Subpart F--Production and Process Controls F .生产和加工控制§211.100 - Written procedures; deviations.211∙100成文的规程、偏差§211.101 - Charge-in of components.211∙101成分的控制§211.103 - Calculation of yield.211∙103 产量计算§211.105 - Equipment identification.211∙105设备鉴别§211.110 - Sampling and testing of in-process211∙110中间体和药品的取样与检验materials and drug products.§211.111 - Time limitations on production.211∙111生产时间限制§211.113 - Control of microbiological contamination.211∙113微生物污染的控制§211.115 - Reprocessing.211∙115返工Subpart G--Packaging and Labeling Control G.包装和标签控制§211.122 - Materials examination and usage criteria.211∙122材料的检查和使用标准§211.125 - Labeling issuance.211∙125标签的发放§211.130 - Packaging and labeling operations.211∙130包装和贴签操作§211.132 - Tamper-evident packaging requirements211∙132人用非处方药（OTC）保险包装的要求for over-the-counter (OTC) human drug products.§211.134 - Drug product inspection.211∙134药品检查§ 211.137 - Expiration dating.211∙137有效期Subpart H--Holding and Distribution H．贮存和销售§ 211.142 - Warehousing procedures.211∙142入库程序§ 211.150 - Distribution procedures.211∙150销售程序Subpart I--Laboratory Controls I∙实验室控制§ 211.160 - General requirements.211∙160总要求§ 211.165 - Testing and release for distribution.211∙165销售前的检验与发放§ 211.166 - Stability testing.211∙166稳定性试验§211.167 - Special testing requirements.211∙167特别检验要求§ 211.170 - Reserve samples.211∙170留样§ 211.173 - Laboratory animals.211∙173实验动物§ 211.176 - Penicillin contamination.211∙176青霉素污染Subpart J--Records and Reports J．记录和报告§ 211.180 - General requirements.211∙180总要求§ 211.182 - Equipment cleaning and use log.211∙182设备清洁和使用记录§ 211.184 - Component, drug product container,211∙184成份、药品容器、密封件及标签记录closure, and labeling records.§ 211.186 - Master production and control records.211∙186主要生产和控制的记录§ 211.188 - Batch production and control records.211∙188批生产和控制记录§ 211.192 - Production record review.211∙192产品记录复查§ 211.194 - Laboratory records.211∙194实验室记录§ 211.196 - Distribution records.211∙196销售记录§ 211.198 - Complaint files.211∙198客户投诉档案Subpart K--Returned and Salvaged Drug Products K．退货的药品和回收处理§ 211.204 - Returned drug products.211∙204退货的药品§ 211.208 - Drug product salvaging.211∙208 药品的回收利用Subpart A-General Provisions A.总 则§ 211.1 Scope211∙1范围(a) The regulations in this part contain the minimum current good manufacturing practice for preparation of drug products for administration to humans or animals.（a）本部分的条例包含人用或兽用药品制备的现行最低限度的药品生产质量管理规范（GMP）(b) The current good manufacturing practice regulations in this chapter, as they pertain to drug products, and in parts 600 through 680 of thischapter, as they pertain to biological products for human use, shall be considered to supplement, not supersede, the regulations in this part unless the regulations explicitly provide otherwise. In the event it is impossible to comply with applicable regulations both in this part and in other parts of this chapter or in parts 600 through 680 of this chapter, the regulation specifically applicable to the drug product in question （b）在本章里的这些针对药品的现行GMP条例和本章600至800的所有部分针对人用生物制品的现行GMP条例，除非明确另有说明者外，应认为是对本部分条例的补充，而是不代替。

storedprocedureStored ProcedureA stored procedure is a set of pre-compiled SQL statements that are stored in a database and can be executed repeatedly when needed. It is a powerful feature of database management systems (DBMS) that allows for modularity, code reusability, and improved performance. In this document, we will explore the concept of stored procedures, their benefits, and how to create and use them.1. Introduction to Stored Procedures1.1 DefinitionA stored procedure is a named group of SQL statements that are stored in the database catalog. These statements are executed as a single unit, allowing for complex database operations to be carried out with greater efficiency. Stored procedures are commonly used for tasks such as data manipulation, business logic implementation, and security enforcement.1.2 Benefits of Stored Procedures1.2.1 Code ReusabilityOne of the primary benefits of stored procedures is code reusability. Once a stored procedure is created, it can be called from various parts of an application or other stored procedures. This eliminates the need to duplicate code, reducing the risk of errors and allowing for easier maintenance.1.2.2 Enhanced PerformanceStored procedures can significantly improve performance by reducing network traffic. Since the entire logic is executed on the server side, only the results are returned to the client, minimizing data transfer. Additionally, stored procedures can be optimized and fine-tuned to deliver faster query execution times.1.2.3 Improved SecurityStored procedures offer an added layer of security by preventing direct access to tables. Instead, users can interact with the database through stored procedures that have necessary permission levels. This allows for enforcement of data integrity rules, access controls, and prevents unauthorized modifications.2. Creating Stored Procedures2.1 SyntaxTo create a stored procedure, the SQL syntax follows a specific structure. Here is a sample syntax:```CREATE PROCEDURE procedure_name[parameter1, parameter2, ...]ASBEGINSQL statements;END;```2.2 ParametersStored procedures can accept input parameters that are used within the code block. These parameters make it possible to pass values or data into the stored procedure for furtherprocessing. Parameters can be of various types, such as integers, strings, dates, etc.2.3 ExampleLet's consider an example of creating a stored procedure that retrieves all customers from a database:```CREATE PROCEDURE GetCustomersASBEGINSELECT * FROM Customers;END;```In this example, the stored procedure \。

catch数据库 call语句用法1.我们需要通过call语句从数据库中取得用户的信息。

We need to retrieve the user's information from the database using a call statement.2.我们可以使用call语句来执行储存过程。

We can use a call statement to execute a stored procedure.3.请使用call语句查询数据库中的订单信息。

Please use a call statement to query the orderinformation in the database.4.我们需要通过call语句将数据插入到数据库中。

We need to insert data into the database using a call statement.5.你可以通过call语句来更新数据库中的记录。

You can update records in the database using a call statement.6. call语句允许我们从数据库中删除数据。

The call statement allows us to delete data from the database.7.请使用call语句检索数据库中的产品信息。

Please use a call statement to retrieve product information from the database.8. call语句使我们能够执行数据库中的存储过程。

The call statement allows us to execute stored procedures in the database.9.我们需要调用一个存储过程来计算销售额。

We need to call a stored procedure to calculate the sales amount.10.通过call语句，我们可以调用数据库中定义的函数。

PowerBuilder内置ORACLE数据库接口的使用方法PowerBuilder(PB)和Oracle分别是前端开发工具和RDBMS的主流产品。

PB提供了两种与Oracle连接的接口：PowerSoft内置的数据库接口(Native Database Interface)和ODBC接口。

本文介绍使用PB6.0内置Oracle接口的方法，包括数据描述文件的设置、存储过程的调用和存储过程作为数据窗口数据源的操作方法等内容，使用的RDBMS的Oracle 7.3。

PowerBuilder与Oracle的连接假定已安装Oracle客户端应用程序。

可用Sqlplus或Tnsping等是否能连接到Oracle数据库，确定在配置文件中使用的数据库别名(Database Alias，又称服务器名，Server Name)。

如有疑问，可在Oracle客户端程序目录下tnsname.or a文件中找到。

另外保证搜索路径已包括应用程序的安装目录(如C:\ORAWIN95\BIN)。

进入PB的Database Profiles画笔，可看到所有已安装的数据库接口(PB6.0缺省安装的是Oracle 7. 3版的接口，如使用低版本的Oracle，需在安装时指定)，选择“Oracle 7.3”，点击“New”按钮，进入Database Profile Setup对话框。

在“C onnection”页输入下列信息：Profile Name：为该Database Profile起一个有意义的名称；Server:@TNS:ServerName，其中ServerName为上述数据库别名(服务器名)，如@TNS:ORA73；Login ID：Oracle数据库中实际的用户名，由于PB初次连接到数据库时要自动建立五个系统表(Powe rBuilder CatalogTable:PBCATTBL,PBCATCOL,PBCATEDT,PBCATFMT, PBCATVLD,存储表的扩展属性)，因此第一个连接到Oracl e的用户必须具有建表、给PUBLIC角色授权等权限。

Special Review and Approval Procedure for Drug Registration of theState Food and Drug Administration(SFDA Decree No. 21)The Special Review and Approval Procedure for Drug Registration of the State Food and Drug Administration, adopted at the executive meeting of the State Food and Drug Administration (SFDA) on November 18, 2005, is hereby promulgated and shall go into effect as of the date of promulgation.Shao MingliCommissioner of SFDANovember 18, 2005Special Review and Approval Procedure for Drug Registration of the State Food andDrug AdministrationChapter 1 General ProvisionsArticle 1This Procedure is formulated for the purpose of effective prevention, timely control and elimination of the hazards of public health emergencies to ensure the health and safety of the public in accordance with the Drug Administration Law of the Pe ople’s Republic of China, Law of the People’s Republic of China on the Prevention and Treatment of Infectious Diseases, Regulations for Implementation of the Drug Administration Law of the People’s Republic of China, Regulations on Preparedness for and Response to Emergent Public Health Hazards, and other relevant laws and regulations.Article 2The special review and approval procedure for drug registration refers to the procedure and requirements of the State Food and Drug Administration to carry out special review and approval of drugs for handling public heath emergencies under the principle of unified leadership, early involvement, expeditiousness and efficiency, and scientific review and approval, in an effort to approve drugs for the prevention and treatment of public health emergencies as soon as possible under the threat or after the occurrence of public heath emergencies.Article 3The State Food and Drug Administration may, according to law, decide to follow the Procedure to conduct special review and approval of drugs for public health emergencies in any of the following circumstances:(1) Where the President of the People’s Republic of China declares a state of emergency or the State Council decides that certain areas within a province, autonomous region or municipality directly under the Central Government are in a state of emergency;(2) Where the contingency program for public health emergencies is initiated according to law;(3) Where the drug reserve department or the health administrative department of the State Council proposes a special review and approval for drugs having existing national drug standard;(4) Other circumstances applicable to special review and approval.Article 4The State Food and Drug Administration is responsible for the review and approval of clinical trials, production and importation of drugs for the prevention and treatment of public health emergencies.The (food and) drug regulatory department of a province, autonomous region, or municipality directly under the Central Government shall, upon entrustment of the State Food and Drug Administration, be responsible for on-site inspection and sampling of the pilot products of the drugs for the prevention and treatment of public health emergencies.Chapter 2 Application Acceptance and On-site InspectionArticle 5Where the special review and approval procedure for drug registration is initiated, the State Food and Drug Administration is responsible for the acceptance of registration applications for drugs for public health emergencies.Where a drug or a preventive biological product for public health emergencies has not been marketed in China, the applicant shall, before submission of the registration application, provide the State Food and Drug Administration with relevant research and development information.Article 6The applicant shall submit a registration application with relevant technical dossier to the State Food and Drug Administration according to the relevant provisions and requirements on administration of drug registration.The registration application for drug for public health emergencies may be submitted in electronic form.Article 7Before submitting an application for registration, the applicant may provide a feasibility assessment application with general information and relevant explanations. TheState Food and Drug Administration shall only comment on the scientific rationality and feasibility of the drug project, and respond within 24 hours.The response to a feasibility assessment application shall be neither deemed as the review and approval opinion nor legally binding on the review and approval result of a registration application.Article 8The State Food and Drug Administration shall organize an expert group to evaluate and review the registration application for drug for the prevention and treatment of public health emergencies, make a decision within 24 hours on whether or not to accept the application, and notify the applicant.Article 9Where a registration application is accepted, the State Food and Drug Administration shall organize technical review of submitted dossier within 24 hours and at the same time notify the (food and) drug regulatory department of the province, autonomous region or municipality directly under the Central Government where the applicant is located to conduct an on-site inspection on drug research and development conditions, and organize sampling and testing of pilot products.The (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government shall, within five days, submit the on-site inspection result and relevant opinion to the State Food and Drug Administration.Article 10The (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government shall organize staff from the departments of drug registration, drug safety and inspection, etc. to participate in the on-site inspection.The National Institute for the Control of Pharmaceutical and Biological Products shall be notified to send staff to participate in the on-site inspection and sampling of preventive biological products.Article 11For drugs that have existing national standards for the prevention and treatment of public health emergencies, the State Food and Drug Administration may directly review and approve the drug in accordance with the relevant provisions in Chapter 6 of the Procedure when it deems there is no need for a clinical trial in accordance with law.Article 12With respect to the registration application for a specific vaccine, where only the original virus strain used in the manufacturing is changed while the manufacturing processes or quality specifications are maintained, the State Food and Drug Administration shall make a decision on whether or not to grant the approval within three days after verifying the changed strain used in the manufacturing.Chapter 3 Testing for RegistrationArticle 13After receiving the samples taken by the (food and) drug regulatory department of a province, autonomous region, or municipality directly under the Central Government, the drug testing institution shall immediately organize verification of specifications and laboratory testing on the samples.The drug testing institution shall complete the testing within the time frame for the drug being applied.Article 14For a drug applied first time for marketing, the State Food and Drug Administration may, when necessary, make early involvement by appointing the National Institute for the Control of Pharmaceutical and Biological Products to communicate with the applicant prior to testing for registration so as to timely resolve technical problems which may arise in the process of quality specifications verification and laboratory testing.With respect to preventive biological products used to prevent and control the epidemic of serious infectious diseases, the State Food and Drug Administration may, depending on the situation, allow the testing for registration and the manufacturer’s self-testing to be performed in parallel.Article 15After completion of the specifications verification and laboratory testing on a drug, the drug testing institution shall, within two days, issue its verification opinion and submit it along with the drug test report to the State Food and Drug Administration.Chapter 4 Technical ReviewArticle 16The State Food and Drug Administration shall, within 15 days after acceptance of the registration application for drug for the prevention and treatment in public health emergencies, complete the first round of technical review.Article 17Where the State Food and Drug Administration considers that additional data are needed, it shall immediately inform the applicant of the content and timeline.Where the applicant submits the supplementary data within the specified timeline, the State Food and Drug Administration shall complete the technical review within three days, or, depending on the situations, organize another review meeting within five days, and then complete the review report within two days.Chapter 5 Clinical TrialArticle 18Where the technical review is completed, the State Food and DrugAdministration shall, within three days, complete the administrative examination, make a decision and inform the applicant.Where the State Food and Drug Administration decides to approve a clinical trial, it shall issue a Drug Clinical Trial Approval. Where it decides not to approve, it shall issue a Disapproval Notice with reasons.Article 19Where an applicant obtains a clinical trial approval, it shall conduct the trial strictly following the relevant requirements in the approval document, and comply with the Good Clinical Practice.Article 20Drug clinical trials shall be conducted in institutions legally certified for drug clinical trials. If a clinical trial has to be conducted by an institution not yet certified, a special approval by the State Food and Drug Administration should be obtained.The application for a clinical trial conducted by an institution not yet certified for drug clinical trials may be submitted along with the application for drug registration.Article 21The investigator responsible for a drug clinical trial shall, in accordance with the relevant provisions, timely report adverse events occurred in the process of the clinical trial to the State Food and Drug Administration. If no adverse event occurs, the investigator shall collect relevant information and report to the State Food and Drug Administration monthly.Article 22The State Food and Drug Administration shall, in accordance with law, supervise and inspect the clinical trials of drugs for public health emergencies.Chapter 6 Review, Approval and Monitoring of Drug ProductionArticle 23The applicant shall, after completion of the drug clinical trials, submit the relevant data to the State Food and Drug Administration in accordance with the Provisions for Drug Registration.Article 24The State Food and Drug Administration shall, within 24 hours after receiving data submitted by the applicant, organize technical review, and at the same time notify the (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government where the applicant is located to conduct the on-site inspection on the drug manufacturing conditions, and organize the sampling and testing of pilot products.The (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government shall, within five days, submit the result and opinion of the on-site inspection to the State Food and Drug Administration.Article 25Any newly-established drug manufacturer or manufacturer with newly-built manufacturing workshops or newly-added dosage forms for production may apply to the State Food and Drug Administration for a certificate of the Good Manufacturing Practice along with the application for drug registration. The State Food and Drug Administration shall, when conducting the drug registration review, immediately carry out an inspection on the Good Manufacturing Practice certification.Article 26The drug testing institution shall, after receiving the samples of three batches taken by the (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government, immediately arrange the testing.Upon completion of testing, the drug testing institution shall, within two days, complete the test report and submit it to the State Food and Drug Administration.Article 27The State Food and Drug Administration shall carry out technical review in accordance with Chapter 4 of the Procedure, and shall, within three days after the completion of the technical review, complete the administrative examination, make a decision and inform the applicant.Where the State Food and Drug Administration decides to approve the drug, it shall issue the Letter of Approval for Drug Registration, and may grant an approval number for the drug at the same time if the applicant has appropriate manufacturing conditions; if it decides to disapprove the manufacturing, it shall issue a Disapproval Notice with reasons.Article 28Where a drug manufacturer or distributor, or a medical and health institution finds any new or serious adverse drug reaction or cluster adverse reaction relating to a specially approved drug for public health emergencies, it shall immediately report the case to the local (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government, the health administrative department at the provincial level and the professional institution for adverse drug reactions monitoring.The professional institution for adverse drug reactions monitoring shall regard drugs specially approved for public health emergencies as critically monitored products, analyze the collected case reports in accordance with the relevant provisions and timely report to the (food and) drug regulatory department of the province, autonomous region, or municipality directly under the Central Government and the State Food and Drug Administration.The State Food and Drug Administration shall reinforce the reevaluation of approved drugs for public health emergencies after they are marketed.Chapter 7 Supplementary ProvisionsArticle 29The measures for special review and approval of the medical devices for public health emergencies shall be separately formulated by the State Food and DrugAdministration with reference to the relevant provisions of the Procedure. Article 30The Procedure shall go into effect as of the date of promulgation.。

FDA 21 CFR part 11译文21 CFR Part 11是针对电子记录和电子签名的FDA法规，对于药厂和医疗器械使用的众多电子记录和电子签名提供了详尽的要求和规范。

Subpart A--General ProvisionsA部分—通用规定11.1 Scope.11.1 范围(a) 本部分的法规制定了接受标准，用于机构评估电子记录、电子签名、电子记录加手写签名的可信性、可靠性，以及通常等同于纸质记录和手写签名的形式。

(a) The regulations in this part set forth the criteria under which the agency considers electronic records, electronic signatures, and handwritten signatures executed to electronic records to be trustworthy, reliable, and generally equivalent to paper record sand handwritten signatures executed on paper.(b) 本部分适用于根据法规需求制定的，以电子形式生成、修改、维护、存档、恢复或传输的任何记录。

还适用于提交给监管机构的关于联邦食品、药品和化妆品以及公共健康服务法案需求的电子记录，即使此类记录不是法规中特别提到的。

但是，本部分不适用于以电子形式传输的纸质记录。

(b) This part applies to records in electronic form that are created, modified, maintained, archived, retrieved, or transmitted, under any records requirements set forth in agency regulations. This part also applies to electronic records submitted to the agency under requirements of the Federal Food, Drug, and Cosmetic Act and the Public Health Service Act, even if such records are not specifically identified in agency regulations. However, this part does not apply to paper records that are, or have been, transmitted by electronic means.(c) 当电子签名和相关的电子记录符合本部分要求时，机构应认可电子签名等同于手写签名、缩写和其他法规中要求常用的签名形式，除非是法规自1997年8月20日以来特别强调的情况。

专利名称：METHOD FOR MONITORING STOREDPROCEDURES发明人：Amichai SHULMAN,Tal RYTERSKI申请号：US11854641申请日：20070913公开号：US20080065640A1公开日：20080313专利内容由知识产权出版社提供专利附图：摘要：A method for monitoring stored procedures is disclosed. The method performs on-line and inline monitoring of stored procedures for detecting table access operations performed by the procedures. This allows the enforcing of access control policies,correlation rules and audit rules on stored procedures. The monitoring is performed using mapping information gathered about each stored procedure that can be executed by a database server. The method comprises parsing an incoming transaction submitted by a client; determining whether the incoming transaction includes an invocation of a stored procedure; obtaining a query group corresponding to the stored procedure; applying an access control policy on the query group; and asserting an unauthorized event if the query group is not compliant with the access control policy.申请人：Amichai SHULMAN,Tal RYTERSKI地址：Tel Aviv IL,Ramat-Gan IL国籍：IL,IL更多信息请下载全文后查看。